CODEWITHSUNDEEP
pythonpandas
Greg Dougherty
Greg Dougherty

Reputation: 3461

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

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?

Upvotes: 1

Views: 35

Answers (2)

BENY
BENY

Reputation: 323226

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

Upvotes: 1

wwnde
wwnde

Reputation: 26676

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()

Upvotes: 3

Related Questions