Pandas - Strip col1 values from column2 values if exists match (regex match with dynamic value)

user_11334852 0
Viewed 27

I have a task to update values in column_1 IF it has a full match to value from column_2. Like so

name              city
Danny London      London
Tim Detroit       Detroit
Keith New Orleans The city of New Orleans
Mary Jane         London

=>

name              city
Danny             London                   <- updated
Tim               Detroit                  <- updated
Keith New Orleans The city of New Orleans  
Mary Jane         London

So far I've tried this

condlidt = [df.apply(lambda x: x.name_cleaned.endwith(f"{x.city}"), axis=1)]
choicelist = [df.name_cleaned.str.replace(rf'{df.city}$', '', regex=True]
fd['name_cleaned'] = np.select(condlist, choicelist, default=df.name_cleaned)

But it returns the same df. I've checked and condlist works as expected - returns True/False for values, the problem is in choicelist - not sure how to pass regex with dynamic value. Would really appreciate any help.

pandas regex
1 Answers

Instead test by endswith you can add value $ for end of string and also \s+ for match spaces and repalce these values to empty string in re.sub:

import re

df['name'] = df.apply(lambda x: re.sub(rf"\s+{x.city}$",'',x['name']), axis=1)
print (df)
                name                     city
0              Danny                   London
1                Tim                  Detroit
2  Keith New Orleans  The city of New Orleans
3          Mary Jane                   London
user_29010020
Related