Pandas: Adding two dataframes on the common columns

Question

I have 2 tables which have the same columns and I want to add the numbers where the key matches, and if it doesn't then just add it as is in the output df.. I tried combine first, merge, concat and join.. They all create 2 seperate columnd for t1 and t2, but its the same key, so should just be together I know this would be something very basic.. pls could someone help? thanks vm!

 df1:
        t1  a         b
    0   USD 2,877   -2,418
    1   CNH 600     -593
    2   AUD 756     -106
    3   JPY 113     -173
    4   XAG 8          0

df2:
    t2  a        b
0   CNH 64      -44
1   USD 756     -774
2   JPY 1,127   -2,574
3   TWO 56      -58
4   TWD 38      -231

Output:
t   a          b
USD 3,634   -3,192
CNH 664     -637
AUD 756     -106
JPY 1,240   -2,747
XAG 8          0
TWO 56       -58
TWD 38      -231

jezrael · Accepted Answer · 2016-08-18 05:44:19Z

5

First set_index in both DataFrames by first columns and then use add with parameter fill_value=0:

print (df1.set_index('t1').add(df2.set_index('t2'), fill_value=0)
          .reset_index()
          .rename(columns={'index':'t'}))

     t       a       b
0  AUD   756.0  -106.0
1  CNH   664.0  -637.0
2  JPY  1240.0 -2747.0
3  TWD    38.0  -231.0
4  TWO    56.0   -58.0
5  USD  3633.0 -3192.0
6  XAG     8.0     0.0

If need convert output to int:

print (df1.set_index('t1').add(df2.set_index('t2'), fill_value=0)
          .astype(int) 
          .reset_index()
          .rename(columns={'index':'t'}))

     t     a     b
0  AUD   756  -106
1  CNH   664  -637
2  JPY  1240 -2747
3  TWD    38  -231
4  TWO    56   -58
5  USD  3633 -3192
6  XAG     8     0

edited Aug 18, 2016 at 5:44

answered Aug 18, 2016 at 5:38

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

3 Comments

spiff Over a year ago

After seeing your first answer, I started searching for renaming the columns back haha.. and your update solves that too! thank you very much..

jezrael Over a year ago

Yes, sometimes works in reset_index parameter name='t', but here is necessary rename columns.

spiff Over a year ago

gotcha - cheerios!

Collectives™ on Stack Overflow

Pandas: Adding two dataframes on the common columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related