How can I replace values in a pandas dataframe based on shared column values?

Question

I have two dataframes, one with timetable information including room numbers (df1), and another one with a mapping of room numbers (df2). In df1, I have rooms that have changed name, from VR 2.09 to VR 275K for example. df2 contains a mapping of this, with the column new containing the new name (VR 275K) and old containing the old name (VR 2.09).

I want to replace all occurances of the old room number in df1 with their new, current name. How can I do this?

df1 format example:

    room       day            etc
0   VR 275K    11/04/2019
1   VR 2.09    09/04/2017
2   VR 1.07    09/04/2017
3   VR 193B    19/03/2019

df2 example:

    new       old
0   VR 275K   VR 2.09
1   VR 258    VR 2.D
2   VR 248    VR 2.E
3   VR 193B   VR 1.07

I am not very familiar with formatting questions in StackOverflow, but hopefully this makes my question clear enough :)

score 1 · Accepted Answer · answered Apr 11 '22 at 12:15

You can use map combined with fillna (or combine_first):

df1['room'] = df1['room'].map(df2.set_index('old')['new']).fillna(df1['room'])

output:

      room         day
0  VR 275K  11/04/2019
1  VR 275K  09/04/2017
2  VR 193B  09/04/2017
3  VR 193B  19/03/2019

How can I replace values in a pandas dataframe based on shared column values?

1 Answers1