How do I add together two pandas dataframes while joining on a column-CodePudding

I have two dataframes. They hold data from two different data collections, for the same items.

df1:
index1 index2 data1 data2
foo    25     15    40
bar    18     25    7
df2:
index1 index2 data1 data2
foo    25     56    76
bar    18     44    18

I wish to add them together such that I get

index1 index2 data1 data2
foo    25     71    116
bar    18     69    25

I tried doing add, but got

index1 index2 data1 data2
foofoo 50     71    116
barbar 36     69    25

I could do a join, but that would give me:

index1 index2 data1 data2 data1 data2
foo    25     15    40    56    76
bar    18     25    7     44    18

Which is not what I'm looking for

What the most efficient way to get the joined add that I want?

CodePudding user response：

aggreagtion of dataframes is premised on index/indices, so set index, add and reset index

(df1.set_index(['index1','index2']).add(df2.set_index(['index1','index2']))).reset_index()

CodePudding user response：

Let us try concat then groupby with sum

out = pd.concat([df1,df2]).groupby('index1').sum()
Out[34]: 
        index2  data1  data2
index1                      
bar         36     69     25
foo         50     71    116