merge two dataframes with common keys and adding unique columns-CodePudding

I have read through the pandas guide, especially merge and join sections, but still can not figure it out.

Basically, this is what I want to do: Let's say we have two data frames:

left = pd.DataFrame(
{   "key": ["K0", "K1", "K2", "K3"],
    "A": ["A0", "A1", "A2", "A3"],
    "C": ["B0", "B1", np.nan, np.nan]})

right = pd.DataFrame(
{   "key": ["K2"],
    "A": ["A8"], 
    "D": ["D3"]})

I want to merge them based off on "key" and update the values, filling where necessary and replacing old values if there are any. So it should look like this:

   key A    C    D
0  K0  A0   B0  NaN
1  K1  A1   B1  NaN
2  K2  A8  NaN  D3
3  K3  A3  NaN  NaN

CodePudding user response：

You can use combine_first with set_index to accomplish your goal here.

right.set_index('key').combine_first(left.set_index('key')).reset_index()

Output:

  key   A    C    D
0  K0  A0   B0  NaN
1  K1  A1   B1  NaN
2  K2  A8  NaN   D3
3  K3  A3  NaN  NaN