CODEWITHSUNDEEP
pythonpandas
sectechguy
sectechguy

Reputation: 2117

Pandas use dataframe name to replace data anywhere in second dataframe with id

I have 2 data frames. df1 has an id and a name, and df2 has names in columns d1,d2,n1, and f1. I am looking to match the name column in both dataframes and replace the names in df2 with the id in df1.

df1
  id  name
0 A1  Ricky
1 A2  Cal
2 A3  Jean
4 A4  Charley

df2
  cal_date    d1     d2       n1      f1
0 2020-01-01  Ricky  Cal      NaN     Charley
1 2020-01-02  Ricky  Jean     Cal     Charley
2 2020-01-03  Cal    Charley  Ricky   NaN
3 2020-01-04  Jean   Cal      Ricky   Charley

Desired output:

df3
  cal_date    d1   d2   n1    f1
0 2020-01-01  A1   A2   NaN   A4
1 2020-01-02  A1   A3   A2    A4
2 2020-01-03  A2   A4   A1    NaN
3 2020-01-04  A3   A2   A1    A4

Upvotes: 0

Views: 45

Answers (2)

rajendra
rajendra

Reputation: 502

You don't need to use a dict; you can just pass the lists.

df2.replace(df1.name.values, df1.id.values, inplace=True)

Upvotes: 3

Andrej Kesely
Andrej Kesely

Reputation: 195438

You can use .replace() with custom dict:

df3 = df2.replace(dict(zip(df1['name'], df1['id'])))
print(df3)

Prints:

     cal_date  d1  d2   n1   f1
0  2020-01-01  A1  A2  NaN   A4
1  2020-01-02  A1  A3   A2   A4
2  2020-01-03  A2  A4   A1  NaN
3  2020-01-04  A3  A2   A1   A4

Upvotes: 3

Related Questions