spiff spiff - 3 months ago 17
Python Question

Pandas: Adding two dataframes on the common columns

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

Answer

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