CODEWITHSUNDEEP
pythonpandasdataframe
Stuber
Stuber

Reputation: 477

Merge two DataFrame but update the original columns

I would like to merge two dataframes on 'key'. When the right contains the same key as left I would like left to update with what's in right's matching column ('A' column).

left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'], 'A': ['A0', 'A1', 'A2', 'A3']})
right = pd.DataFrame({'key': ['K0', 'K2'], 'A': ['new', 'new']})

left.merge(right, on="key", how="outer")

outputs:

  key A_x  A_y
0  K0  A0  new
1  K1  A1  NaN
2  K2  A2  new
3  K3  A3  NaN

placing suffixes: 'A_x' and 'A_y'

however desired output is:

  key A
0  K0  new
1  K1  A1
2  K2  new
3  K3  A3

What is needed for column A to merge on key values that are the same in left and right dataframes?

Upvotes: 1

Views: 68

Answers (1)

cs95
cs95

Reputation: 402513

One painless way is using update:

u = left.set_index('key')
u.update(right.set_index('key'))


u.reset_index()

  key    A
0  K0  new
1  K1   A1
2  K2  new
3  K3   A3

If the "key" column is unique, you can also concat and drop duplicates:

(pd.concat([left, right])
   .drop_duplicates('key', keep='last')
   .sort_index()
   .reset_index(drop=True))

  key    A
0  K0  new
1  K1   A1
2  K2  new
3  K3   A3

Upvotes: 1

Related Questions