pandas left join and update existing column

Question

I am new to pandas and can't seem to get this to work with merge function:

>>> left       >>> right    a  b   c       a  c   d  0  1  4   9    0  1  7  13 1  2  5  10    1  2  8  14 2  3  6  11    2  3  9  15 3  4  7  12     

With a left join on column a, I would like to update common columns BY THE JOINED KEYS. Note last value in column c is from LEFT table since there is no match.

>>> final           a  b   c   d  0  1  4   7   13 1  2  5   8   14 2  3  6   9   15 3  4  7   12  NAN  

How should I do this with Pandas merge function? Thank you.

Answer 1

You can use merge() between left and right with how='left' on 'a' column.

In [74]: final = left.merge(right, on='a', how='left')  In [75]: final Out[75]:    a  b  c_x  c_y   d 0  1  4    9    7  13 1  2  5   10    8  14 2  3  6   11    9  15 3  4  7   12  NaN NaN 

Replace NaN value from c_y with c_x value

In [76]: final['c'] = final['c_y'].fillna(final['c_x'])  In [77]: final Out[77]:    a  b  c_x  c_y   d   c 0  1  4    9    7  13   7 1  2  5   10    8  14   8 2  3  6   11    9  15   9 3  4  7   12  NaN NaN  12 

Drop unwanted columns, and you have the result

In [79]: final.drop(['c_x', 'c_y'], axis=1) Out[79]:    a  b   d   c 0  1  4  13   7 1  2  5  14   8 2  3  6  15   9 3  4  7 NaN  12