Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to merge two dataframes and sum the values of columns

I have two dataframes

df1
Name class value
Sri   1     5
Ram   2     8
viv   3     4

df2
Name class value
Sri   1     5
viv   4     4

My desired output is,

df,

Name class value
Sri   2     10
Ram   2     8
viv   7     8

Please help, thanks in advance!

like image 867
chandru Avatar asked Mar 05 '18 13:03

chandru


People also ask

How do you sum values in two data frames?

Use DataFrame. sum() to get sum/total of a DataFrame for both rows and columns, to get the total sum of columns use axis=1 param. By default, this method takes axis=0 which means summing of rows.

How do I merge two DataFrames in all columns?

You can pass two DataFrame to be merged to the pandas. merge() method. This collects all common columns in both DataFrames and replaces each common column in both DataFrame with a single one.

Which are the 3 main ways of combining DataFrames together?

Joining two DataFrames can be done in multiple ways (left, right, and inner) depending on what data must be in the final DataFrame. to_csv can be used to write out DataFrames in CSV format.


2 Answers

I think need set_index for both DataFrames, add and last reset_index:

df = df1.set_index('Name').add(df2.set_index('Name'), fill_value=0).reset_index()
print (df)
  Name  class  value
0  Ram    2.0    8.0
1  Sri    2.0   10.0
2  viv    7.0    8.0

If values in Name are not unique use groupby and aggregate sum:

df = df1.groupby('Name').sum().add(df2.groupby('Name').sum(), fill_value=0).reset_index()
like image 156
jezrael Avatar answered Oct 11 '22 11:10

jezrael


pd.concat + groupby + sum

You can concatenate your individual dataframes and then group by your key column:

df = pd.concat([df1, df2])\
       .groupby('Name')['class', 'value']\
       .sum().reset_index()

print(df)

  Name  class  value
0  Ram      2      8
1  Sri      2     10
2  viv      7      8
like image 41
jpp Avatar answered Oct 11 '22 10:10

jpp