Python merge insert duplicate column values instead of creating _x & _y columns

Hi I have 2 data sets:

Data A:

Column A  Column B  Column C    
Hello      NaN      John    
Bye        NaN      Mike

Data B:

Column A  Column B    
Hello      123

Raw data:

a = pd.DataFrame([['Hello', np.nan,'John'],['Bye',np.nan,'Mike']], columns=['Column A','Column B','Column C'])
b = pd.DataFrame([['Hello', 123]], columns=['Column A','Column B'])

I want to merge Data A & B using left join (as Data A should be the main data and only bring in if they have matching Column A on Data B), and want to bring in Data B's Column B's numeric onto Data A's Column B.

The columns match but my script below results in two Column B's.

df=a.merge(b, on ='Column A', how='left')

df:

Column A  Column B_x  Column C  Column B_y
Hello      NaN        John       123
Bye        NaN        Mike

I want the following result:

Column A  Column B  Column C
Hello       123      John
Bye         NaN      Mike

Solution 1:

You don't need a merge for this as a merge will bring the columns of the two dataframes together. Since your dataframes follow the same structure, fillna or update:

a.fillna(b, inplace = True) # not in place unless you specify inplace=True 
a.update(b) # modifies NA in place using non-NA values from another DataFrame

print(a)

  Column A  Column B Column C
0    Hello     123.0     John
1      Bye       NaN     Mike