gustas88 gustas88 -4 years ago 126
Python Question

pandas merge two data frames and summarize cell values

There are 2 DataFrames which I'm trying to merge by month column and sum the fullprice, actualprice and discount rows.

Here's example:

# First DataFrame
month fullprice actualprice discount
0 Jan 10 7 3
1 Feb 6 4 2


# Second DataFrame
month fullprice actualprice discount
0 Jan 11 5 6
1 Feb 6 4 2
2 Mar 100 50 50


# Desired result
month fullprice actualprice discount
0 Jan 21 12 9
1 Feb 12 8 4
2 Mar 100 50 50


Tried few methods, but it's not what I need:

df1 = pd.DataFrame([['Jan', 10, 7, 3], ['Feb', 6, 4, 2]], columns=['month', 'fullprice', 'actualprice', 'discount'])
df2 = pd.DataFrame([['Jan', 11, 5, 6], ['Feb', 6, 4, 2], ['Mar', 100, 50, 50]], columns=['month', 'fullprice', 'actualprice', 'discount'])

df2.add(df1)

month fullprice actualprice discount
0 JanJan 21.0 12.0 9.0
1 FebFeb 12.0 8.0 4.0
2 NaN NaN NaN NaN

df1.merge(df2, how='right')

month fullprice actualprice discount
0 Feb 6 4 2
1 Jan 11 5 6
2 Mar 100 50 50

df1.merge(df2, on='month', how='right')

month fullprice_x actualprice_x discount_x fullprice_y actualprice_y \
0 Jan 10.0 7.0 3.0 11 5
1 Feb 6.0 4.0 2.0 6 4
2 Mar NaN NaN NaN 100 50

discount_y
0 6
1 2
2 50


Any ideas how to merge it?

Answer Source

use append then groupby.

df1 = df1.set_index('month')
df2 = df2.set_index('month')
df1.append(df2).groupby(level=0).sum()

       fullprice  actualprice  discount
month                                  
Feb           12            8         4
Jan           21           12         9
Mar          100           50        50

or if no index:

df1.append(df2).groupby('month').sum()
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download