how to iterate over multiple dataframes and add values to new dataframe in python

Question

I have 4 data frames:

df1 = pd.read_csv('values1.csv')
df2 = pd.read_csv('values2.csv')
df3 = pd.read_csv('values3.csv')
df4 = pd.read_csv('values4.csv')

each of them have a structure as follows:

I want to create a new data frame such that it has aggregated values for each category in all the data frames. So, the new data frame should have values which are calculated using the formula :-

Total['values'][0] = df1['values'][0] / (df1['values'][0] + df2['values'][0]  + df3['values'][0]  + df4['values'][0] )

Like this it should generate values for all the rows. Can someone please help me out.

Please provide a Minimal, Complete, and Verifiable example

yatu
– yatu

2019-09-23 11:03:41 +00:00
Commented Sep 23, 2019 at 11:03 — yatu
– yatu, Commented Sep 23, 2019 at 11:03

jezrael · Accepted Answer · 2019-09-23 11:11:12Z

1

First join all DataFrames with concat and aggregate sum for Series and then convert column category to index for Series from df1 and divide by Series.div:

s = pd.concat([df1, df2, df3, df4]).groupby('category')['values'].sum()

out = df1.set_index('category')['values'].div(s).reset_index(name='total')

EDIT:

s = pd.concat([df1, df2, df3, df4]).groupby('category')['values'].sum()
s1 = pd.concat([df1, df2]).groupby('category')['values'].sum()

out = s1.div(s2).reset_index(name='new')

edited Sep 23, 2019 at 11:11

answered Sep 23, 2019 at 11:04

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user8306074 Over a year ago

what if I have to add df1 values and df2 values in the numerator and divide by the sum ?

user8306074 Over a year ago

Cool!!!!! Thanks alot! I thought I'll have to apply some very series for loops :p

user8306074 Over a year ago

Can you help me with one more thing.in each data frame I have 3 values columns, namely: value1, value2, value. I want to create a new data frame which has summed values row-wise for each category. I am not able to apply the above code for multiple columns

jezrael Over a year ago

@user8306074 - I think you need s = pd.concat([df1, df2, df3, df4]).groupby('category')['value1','value2','value'].sum().sum(axis=1)

user8306074 Over a year ago

I think I'll create different lists and append them into one data frame

|

Collectives™ on Stack Overflow

how to iterate over multiple dataframes and add values to new dataframe in python

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related