join on index AND column

Question

I would like to join two dataframes based on two conditions: 1. Join via Index 2. If two column headers are in both dataframes, join them aswell

To give an example, lets imagine I have these two dataframes:

import pandas as pd
import numpy as np

df1 = pd.DataFrame({'date': [2010, 2011, 2012],
               'a': [np.NaN, 30, np.NaN],
               'b': [55, np.NaN, np.NaN],
               'c': [55, 40, 84]})
df1 = df1.set_index("date")

df2 = pd.DataFrame({'date': [2010, 2011, 2012],
               'a': [10, np.NaN, 30],
               'b': [np.NaN, 80, 84],
               'd': [55, 40, 84]})
df2 = df2.set_index("date")

if I now join the the two via pd.concat, I get columns such as "a" twice:

pd.concat([df1, df2], axis=1) 

         a      b      c    a       b       d
date                        
2010    NaN     55.0   55   10.0    NaN     55
2011    30.0    NaN    40   NaN     80.0    40
2012    NaN     NaN    84   30.0    84.0    84

But I would rather have:

         a      b      c     d
date                        
2010    10.0    55.0   55    55
2011    30.0    80.0   40    40
2012    30.0    84.0   84    84

Thanks in advance!

Answer 1

Try this, add

print(df1.set_index('date').add(df2.set_index("date"), fill_value=0))

         a     b     c     d
date                        
2010  10.0  55.0  55.0  55.0
2011  30.0  80.0  40.0  40.0
2012  30.0  84.0  84.0  84.0