Replacing/joining names based on ID, and check and replace all names that have been changed based on ID

Protonios 0
Viewed 40

I have two different dataframes, with information about "Name" and "id" Df1 is larger than df2. I would like to "join" on ID, so that the brand name from df2 will be considered as correct in df1. However, I would also want to find all the names that have been replaced in df1, and replace them with the relevant name from df2. Sample: df1

index    id         name
0        123        Del Monte
154047   124        Pokmon
171696   125        Pokmon
69264    126        Pokmon

df2
index   name     id
79376   Pokémon  124        
135487  Pokémon  125

result:

df1
index    id         name
0        123        Del Monte
154047   124        Pokémon
171696   125        Pokémon
69264    126        Pokémon
replace pandas python
1 Answers

Use Series.map with Series.fillna with helper Series created by left join with DataFrame.dropna and DataFrame.drop_duplicates:

s = (df1.merge(df2, on='id', how='left')
         .dropna(subset=['name_y'])
         .drop_duplicates(['name_x','name_y'])
         .set_index('name_x')['name_y'])
df1['name'] = df1['name'].map(s).fillna(df1['name'])
print (df1)
    index   id       name
0       0  123  Del Monte
1  154047  124    Pokémon
2  171696  125    Pokémon
3   69264  126    Pokémon
jezrael0
Related