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

Question

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

Andrej Kesely · Accepted Answer · 2020-01-31 21:15:53Z

3

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

answered Jan 31, 2020 at 21:15

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

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

Comments

rajendra · Accepted Answer · 2020-06-25 01:31:51Z

3

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

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

edited Jun 25, 2020 at 1:31

answered Jan 31, 2020 at 21:28

rajendra

5023 silver badges20 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related