Python pandas: Replacing value based on Text matching

Question

I got two dataframe: df1 has around 20000 rows and df2 has 7226 rows as shown in picture:

df1:

df2:

I want to copy the 'Final Coding' value of df2 to 'Final Coding' value of df1 if both dataframes have same Review_Text values. I used following command:

df1.loc[df1['Review_Text'].isin(df2['Review_Text'],'Final Coding'] = df2['Final Coding']

The problem with above statement is that it does not replace all values of df1. It only replaces values until the index number of 7226 of df1 and after that it does not replace any value. I tried another command as follow:

mask = df1['Review_Text'].isin(df2['Review_Text'])==True
df1['Final Coding'] = df1['Final Coding'].where(~mask,df2['Final Coding'],axis=0)

But ended up with same problem. I searched the exisiting threads of stackoverflow and found another way. So I changed the command as follow:

df1.loc[df1['Review_Text'].isin(df2.set_index('Review_Text').index),'Final Coding'] = df2.set_index('Review_Text')['Final Coding']

However, above command threw following error:

ValueError: cannot reindex from a duplicate axis

There are some text in 'Review_Text' column which is duplicated that's why it threw this error. I am not sure how to overcome this issue. Could anyone help me in fixing the issue?

sneaky joke. sounded like you wanted to turn df2 into a lookup but it had multiple occurrences of the key, value pairs you wanted to use. could make a df of your keys (and values) followed by running unique and pull pertinent ones to be used for lookup. or could do something like stackoverflow.com/a/67343461/14237276 to reduce it by grouping. — Abel
– Abel, Commented Dec 23, 2021 at 1:56

BENY · Accepted Answer · 2021-12-23 01:28:02Z

1

You can do

Update before below :

df1.reset_index(inplace=True, drop=True)
df2.reset_index(inplace=True, drop=True)

#==================================
m = df1['Review_Text'].isin(df2['Review_Text'])
v = df1['Review_Text'].map(df2.set_index('Review_Text')['Final Coding'])
df1.loc[m,'Final Coding'] = v

Or we can do

df1.update(df1['Review_Text'].map(df2.set_index('Review_Text')['Final Coding']).to_frame('Review_Text'))

edited Dec 23, 2021 at 1:28

answered Dec 23, 2021 at 1:09

BENY

324k22 gold badges176 silver badges250 bronze badges

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

2 Comments

user2293224 Over a year ago

thanks for posting. When I ran the df1.update command or second line of first solution, I got the error message "InvalidIndexError: Reindexing only valid with uniquely valued Index objects"

BENY Over a year ago

@user2293224 you have duplicated index , try reset_index(), I will update

Collectives™ on Stack Overflow

Python pandas: Replacing value based on Text matching

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related