Duplicate columns with Pandas merge?

Question

I have a data frame a:

ID    value1
1     nan
2     nan
3     nan
4     nan
5     nan

and then two other data frames, b and c:

ID     value1
2      20
3      10

ID     value1
1      58
4      20

When I do a.merge(b, on='ID').merge(c, on='ID'), I get duplicate columns of value. My end result has the columns:

ID     value1_x    value1_y    value1

but I want to end up with:

ID    value1
1     58
2     20
3     10
4     20
5     nan

How do I use b and c to populate the values in a without duplicate columns?

Answer 1

You can use concat and then merge with drop old column value1 in a:

df1 = pd.concat([b,c])
print (df1)
   ID  value1
0   2      20
1   3      10
0   1      58
1   4      20

df2 = pd.merge(a ,df1, on='ID', how='left', suffixes=('_',''))
df2.drop('value1_', axis=1, inplace=True)
print (df2)
   ID  value1
0   1    58.0
1   2    20.0
2   3    10.0
3   4    20.0
4   5     NaN