Search and replace matching values across data frames in Python

Question

I have the following Pandas Dataframes:

df1:

C   D   E   F   G
111 222 333 444 555
666 777

df2:

I need to look up in df2 to find matching value from df1.A Then replace that value in df1 with the paired valued in df2.B

So the required output would be:

C   D   E   F   G
3   4   3   3   4
4   3

I tried a left merge and thought to try and reshape the values across but thought there must be a simpler / cleaner direct search and replace method. Any help much appreciated.

score 2 · Answer 1 · answered Jun 20 '18 at 11:34

2

First create a series mapping:

s = df2.set_index('A')['B']

Then apply this to each value:

df1 = df1.applymap(s.get)

answered Jun 20 '18 at 11:34

jpp

159,742
34
281
339

Mohamed Thasin ah · Accepted Answer · 2018-06-20T12:03:55.807

1

try this,

temp=df2.set_index('A')['B']
print df1.replace(temp)

Output:

   C  D    E    F    G
0  3  4  3.0  3.0  4.0
1  4  3  NaN  NaN  NaN

edited Jun 20 '18 at 12:03

answered Jun 20 '18 at 11:36

Mohamed Thasin ah

10,754
11
52
111

I'm using python 2.x :) – Mohamed Thasin ah Jun 20 '18 at 11:49
A couple of notes: (1) `pd.DataFrame.replace` accepts a series, so no `dict` conversion required, (2) `map` [usually outperforms](https://stackoverflow.com/questions/49259580/replace-values-in-a-pandas-series-via-dictionary-efficiently) `replace`. – jpp Jun 20 '18 at 12:00
1

@jpp - thanks for your help updated to the solution :) – Mohamed Thasin ah Jun 20 '18 at 12:04

Search and replace matching values across data frames in Python

2 Answers2