Replace rows in a dataframe with rows in another dataframe given multiple columns as keys

Question

There are questions similar to this on SO but I am yet to see one exactly like this - replacing with multiple columns as keys. I have two dataframes. Example below:

df1 = pd.DataFrame([["X",Monday,1,0],
              ["Y",Tuesday,1,0],
              ["Z",Wednesday,0,0],
              ["Y",Thursday,0,0]],columns=["First","Second","Third", "Fourth"])

and

df2 = pd.DataFrame([["X",Monday,3,0],
              ["Y",Tuesday,7,1],
              ["Z",Wednesday,6,2]],columns=["First","Second","Third", "Fourth"])

What I want is for df2 to replace every 'Third' and 'Fourth' columns in df1 when 'First' and 'Second' columns match. So result will be

df3 = pd.DataFrame([["X",Monday,3,0],
              ["Y",Tuesday,7,1],
              ["Z",Wednesday,6,2],
              ["Y",Thursday,0,0]],columns=["First","Second","Third", "Fourth"])

I have tried the update and is.in but they work with index and one column only as far as I know. Can anyone help please?

Shubham Sharma · Accepted Answer · 2020-12-06 15:24:21Z

2

You can still use DataFrame.update to update the values in place using non-nan values from another DataFrame but before using it you have to align the indices in both the dataframes:

df1 = df1.set_index(['First', 'Second'])
df1.update(df2.set_index(['First', 'Second']))
df1 = df1.reset_index()

Result:

print(df1)

  First     Second  Third  Fourth
0     X     Monday    3.0     0.0
1     Y    Tuesday    7.0     1.0
2     Z  Wednesday    6.0     2.0
3     Y   Thursday    0.0     0.0

answered Dec 6, 2020 at 15:24

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TheDataGuy Over a year ago

This also worked and it's cleaner. I didn't know you could use multiple indices for .update

Shubham Sharma Over a year ago

@TheDataGuy Happy coding :)

piterbarg · Accepted Answer · 2020-12-06 15:18:50Z

Try this

(df1.merge(df2, on=['First', 'Second'], how='left', suffixes = ('_old','') )
    .fillna(df1)
    .drop(columns = ['Third_old','Fourth_old'])
)

we merge on two columns, then replace NaNs where needed with the original results, and drop unused columns

output


   First    Second      Third   Fourth
0   X       Monday      3.0     0.0
1   Y       Tuesday     7.0     1.0
2   Z       Wednesday   6.0     2.0
3   Y       Thursday    0.0     0.0

Collectives™ on Stack Overflow

Replace rows in a dataframe with rows in another dataframe given multiple columns as keys

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related