use fuzzywuzzy to compare 2 dataframe columns and change values based on matching ratio

Question

I have 2 dataframes:

df1 = pd.DataFrame(columns=['name', 'age'], data=[['Sam', 20], ['Dani', 21]])
df2 = pd.DataFrame(columns=['name', 'age'], data=[['Sami', 25], ['Danielle', 22]])

I want to check if the name column in df1 exists in df2 using the fuzzywuzzy library and change it according to df2['name'] value.

Here is my code:

from fuzzywuzzy import fuzz
df1.loc[fuzz.ratio(df1['name'],df2['name']) >= 90, 'name'] = df2['name']

so basically if the matching ratio is equal or above 90 percent, I want to change Sam to Sami. I am getting this error:

jezrael · Answer 1 · 2022-02-28T07:25:18.400

1

If index of both DataFrames is same, is possible use:

m = [fuzz.ratio(a, b) >= 90 for a, b in zip(df1['name'],df2['name'])]
df1.loc[m, 'name'] = df2['name']

More general solution with any length is join both DatFrames by cross join, count ratio and create mapping Series by maximal ratio:

from fuzzywuzzy import fuzz

df = df1.merge(df2, how='cross')
df['r'] = df.apply(lambda x: fuzz.ratio(x['name_x'],x['name_y']), axis=1)

s = df[df['r'] >= 90].sort_values('r', ascending=False).drop_duplicates(subset=['name_x']).set_index('name_x')['name_y']

df1['name'] = df1['name'].map(s).fillna(df1['name'])

For improve performance use rapidfuzz:

#https://stackoverflow.com/a/63884750/2901002
from rapidfuzz import process, utils

actual_comp = []
similarity = []

company_mapping = {company: utils.default_process(company) for company in df2.name}

for customer in df1.name:
    _, score, comp = process.extractOne(
        utils.default_process(customer),
        company_mapping,
        processor=None)
    actual_comp.append(comp)
    similarity.append(score)
    
df1['new'] = pd.Series(actual_comp, index=df1.index)
df1['similarity'] = pd.Series(similarity, index=df1.index) 
        
#set values by similarity
df1.loc[df1['similarity'] >= 90, 'name'] = df1['new']

edited Feb 28 '22 at 07:25

answered Feb 28 '22 at 06:17

jezrael

822,522
95
1,334
1,252

they are not the same index – newuser1628 Feb 28 '22 at 06:26
I think your method will take a very long time, since I have 5 million records in dataframe 1 – newuser1628 Feb 28 '22 at 06:29
@newuser1628 - yes, it is expected. `fuzzywuzzy` in large Dataframe is slow. – jezrael Feb 28 '22 at 06:36
Is there another library similar to fuzzywuzzy that works more efficiently on dataframes? – newuser1628 Feb 28 '22 at 06:37
1

@newuser1628 - You can try [this](https://stackoverflow.com/a/61371170/2901002) – jezrael Feb 28 '22 at 06:38
@newuser1628 - added solution, you can check if working for you. – jezrael Feb 28 '22 at 07:26

score 0 · Answer 2 · answered Feb 28 '22 at 06:55

You could try this. Using a method compare and applying it on column name

def compare(name_from_df1):
    for name in df2['name'].to_list():
        if fuzz.ratio(name_from_df1,name) >= 90:
            return name    
    return name_from_df1

df1['name'] = df1['name'].apply(lambda x: compare(x))

use fuzzywuzzy to compare 2 dataframe columns and change values based on matching ratio

2 Answers2