Pandas merge two frame with addition-CodePudding

need to merge two data-frame and sum columns,two excel sheets df1 and df2, df1 is my database where I am saving the values from df2, If df1['B'] = df2['B'] got key error, If i set df1['B'] = None I am resenting the value of df1['B'] every time. How to merge the and keep persistent columns from df1 where I can continue add the value from df2?

df1 = pd.DataFrame({
    'A' : ['a','b','c','m', 'o'],
    'B' : [None, 1, None, 1, None]
})

df2 = pd.DataFrame({
    'A' : ['a','c','o'],
    'B' : [1,1,None]
})
df1 = df1.merge(df2, how="left", on = 'A')
df1['Total'] = df1['B_x'].add(df1['B_y'], fill_value= 0)
df1 = df1.drop(columns=['B_x', 'B_y'])
df1

CodePudding user response：

Use Series.map with column A for new Series - another coumns are not changed:

df1 = pd.DataFrame({
    'A' : ['a','b','c','m', 'o'],
    'B' : [None, 1, None, 1, None],
    'C':list('ABCDE')
})

df2 = pd.DataFrame({
    'A' : ['a','c','o'],
    'B' : [1,1,None]
})

df1['B'] = df1.B.add(df1.A.map(df2.set_index('A').B), fill_value= 0)

print (df1)
   A    B  C
0  a  1.0  A
1  b  1.0  B
2  c  1.0  C
3  m  1.0  D
4  o  NaN  E