Combine two pandas dataframes adding corresponding values

Question

I have two dataframes like these:

df1 = pd.DataFrame({'A': [1,0,3], 'B':[0,0,1], 'C':[0,2,2]}, index =['a','b','c'])
df2 = pd.DataFrame({'A': [0,0], 'B':[2,1]}, index =['a','c'])

df1 and df2:

   | A | B | C |          | A | B |    
---|---|---|---|       ---|---|---|
 a | 1 | 0 | 0 |        a | 0 | 2 |   
 b | 0 | 0 | 2 |        c | 0 | 1 |
 c | 3 | 1 | 2 |

And the expected result is:

   | A | B | C |
---|---|---|---|
 a | 1 | 2 | 0 |
 b | 0 | 0 | 2 |
 c | 3 | 2 | 2 |

I'm having problems with this because there may be missing columns/rows in any of the dataframes (df1 may not have all columns and rows df2 has)

Check out this post: stackoverflow.com/questions/16583668/… — Charlie Haley
– Charlie Haley, Commented Sep 30, 2015 at 14:22
I already checked that, but my problem is that there is not a predefined set of columns both dataframes share. So, I can't use them as keys to concatenate the dataframes. — kiril
– kiril, Commented Sep 30, 2015 at 15:19

Community · Accepted Answer · 2017-05-23 12:14:13Z

8

Going by the idea in the answer for this question - merge 2 dataframes in Pandas: join on some columns, sum up others

Since in your case, the indexes are the ones that are common, you can use pandas.concat() for the two DataFrames, then DataFrame.groupby based on the index, and then take sum on it. Example -

In [27]: df1
Out[27]:
   A  B  C
a  1  0  0
b  0  0  2
c  3  1  2

In [28]: df2
Out[28]:
   A  B
a  0  2
c  0  1

In [29]: pd.concat([df1,df2]).groupby(level=0).sum()
Out[29]:
   A  B  C
a  1  2  0
b  0  0  2
c  3  2  2

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Sep 30, 2015 at 14:27

Anand S Kumar

91.4k18 gold badges196 silver badges179 bronze badges

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

1 Comment

kiril Over a year ago

Thanks. I would only add .fillna(0) at the end of the In[29] command to remove nans

Collectives™ on Stack Overflow

Combine two pandas dataframes adding corresponding values

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related