Pandas If duplicate on same row, lookup value from another dataframe

Question

I have this scenario in mind but I don't know how to go about it.

When a value in column changeme is equal to the one from the lookforme column on the same row in df1, I would like to lookup that value on the matchme column of df2, grab the corresponding grabme value and overwrite the one in changeme on df1.

In case of multiple occurrences of lookforme found on df2, match just the first one.

I tried a few methods but couldn't get anywhere, had complaints about the DataFrames being uneven when I tried factorize, etc.

          changeme       name   qty  lookforme
0          1296477  ItemName2  3.16    1296477
1            62328  ItemName1  7.94      62328
2           233201  ItemName3  5.56     233201
3      66074000105  ItemName4  6.88     233694

           matchme       grabme
0            62328  35422028768
1           162592  62673001137
2           163200  15422029895
3           164123  45492029154
4           166596  35422031737
5           232264  75372043938
6           232267  95462043655
7           233201  35422044775
8           233201  24613628530
9           233226  35412044812
10          233694  66074000105
11          235466  35422042863
12          272021  85422052947
13          272691  35422053340
14          272872  27536196938
15          273729  39456023149
16          284848  27536197195
17          284861  62672008054
18         1296477  35421853202
19         1296477  75841596436
20         2296518  97630194508

Expected result:

          changeme       name   qty  lookforme
0      35421853202  ItemName2  3.16    1296477
1      35422028768  ItemName1  7.94      62328
2      35422044775  ItemName3  5.56     233201
3      66074000105  ItemName4  6.88     233694

score 3 · Accepted Answer · 2022-04-01 06:06:45Z

3

Let's try the following:

Filter "lookforme" values that match "changeme" in df1 and find those values among "matchme" values in df2 using loc. Since some of the "lookforme" values occur multiple times in df2, drop the duplicates and keep the first.

Then update "changeme" values in df1 using map:

cond = df1['changeme'] == df1['lookforme']
new_values = df2.drop_duplicates('matchme').loc[df2['matchme'].isin(df1.loc[cond, 'changeme'])]
df1.loc[cond, 'changeme'] = df1.loc[cond, 'changeme'].map(new_values.set_index('matchme')['grabme'])

Output:

      changeme       name   qty  lookforme
0  35421853202  ItemName2  3.16    1296477
1  35422028768  ItemName1  7.94      62328
2  35422044775  ItemName3  5.56     233201
3  66074000105  ItemName4  6.88     233694

edited Apr 1, 2022 at 6:06

answered Mar 10, 2022 at 19:36

user7864386

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

Comments

kalligator · Accepted Answer · 2022-12-15 05:25:35Z

2

you can also try this:

res = pd.merge(df1, df2,
               left_on='lookforme', 
               right_on='matchme').drop_duplicates('lookforme').reset_index(drop=True)
# you have duplicates in df2 so we should-^^^^^^^^^^^^^^^

df1['changeme'] = res.apply(lambda x: x['grabme'] 
                                   if x['changeme']==x['lookforme'] 
                                   else x['changeme'], axis=1)
print(df1)

          changeme       name   qty  lookforme
0      35421853202  ItemName2  3.16    1296477
1      35422028768  ItemName1  7.94      62328
2      35422044775  ItemName3  5.56     233201
3      66074000105  ItemName4  6.88     233694

edited Dec 15, 2022 at 5:25

kalligator

1448 bronze badges

answered Mar 10, 2022 at 23:21

SergFSM

1,4991 gold badge6 silver badges9 bronze badges

1 Comment

kalligator Over a year ago

Always nice to have alternatives, thank you.

Collectives™ on Stack Overflow

Pandas If duplicate on same row, lookup value from another dataframe

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related