Merge Pandas with fill empty cells

Question

I am trying to merge/join 2 tables in pandas on the basis of a key, but some of which do not exist in the 2nd table . The 2 tables look somewhat like this:

ID ATTR1 ATTR2
A1 2000 30
A2 300 12
A3 45 22

ID ATT12SUMCOND
A1 2030 
A2 312 

Now the key value A3 does not exist in the second table, still I need to join these 2 tables somewhat like:

ID ATTR1 ATTR2 ATTR12COND
A1 2000 30   2030 
A2 300 12   312 
A3 45 22    0 

How do I achieve this?

Answer 1

You can use pandas' merge method and then replace NaN values with 0 as follows-

df = pd.merge(df1, df2, how='left', on='ID').fillna({'ATTR12COND':0})

If you need to replace all NaN columns with 0, you can change fillna as follows-

df = pd.merge(df1, df2, how='left', on='ID').fillna(0)

Answer 2

If no problem floats in new aded column replace missing values after DataFrame.merge by DataFrame.fillna:

df = df1.merge(df2, on='ID', how='left').fillna({'ATT12SUMCOND':0})
print (df)
   ID  ATTR1  ATTR2  ATT12SUMCOND
0  A1   2000     30        2030.0
1  A2    300     12         312.0
2  A3     45     22           0.0

Or use DataFrame.join with DataFrame.reindex for integer column:

df = df1.join(df2.set_index('ID').reindex(df1['ID'], fill_value=0), on='ID')
print (df)
   ID  ATTR1  ATTR2  ATT12SUMCOND
0  A1   2000     30          2030
1  A2    300     12           312
2  A3     45     22             0