Fuzzy Match columns of Different Dataframe

Question

Background

I have 2 data frames which has no common key to which I can merge them. Both df have a column that contains "entity name". One df contains 8000+ entities and the other close to 2000 entities.

Sample Data:

vendor_df=
     Name of Vendor                             City         State  ZIP
     FREDDIE LEES AMERICAN GOURMET SAUCE       St. Louis    MO     63101
     CITYARCHRIVER 2015 FOUNDATION             St. Louis    MO     63102
     GLAXOSMITHKLINE CONSUMER HEALTHCARE       St. Louis    MO     63102
     LACKEY SHEET METAL                        St. Louis    MO     63102

regulator_df = 
     Name of Entity                    Committies
     LACKEY SHEET METAL                 Private
     PRIMUS STERILIZER COMPANY LLC      Private  
     HELGET GAS PRODUCTS INC            Autonomous
     ORTHOQUEST LLC                     Governmant

Problem Stmt:

I have to fuzzy match the entities of these two(Name of vendor & Name of Entity) columns and get a score. So, need to know if 1st value of dataframe 1(vendor_df) is matching with any of the 2000 entities of dataframe2(regulator_df).

StackOverflow Links I checked:

fuzzy match between 2 columns (Python)

create new column in dataframe using fuzzywuzzy

Apply fuzzy matching across a dataframe column and save results in a new column

Code

import pandas as pd
from fuzzywuzzy import fuzz
from fuzzywuzzy import process

vendor_df = pd.read_excel('C:\\Users\\40101584\\Desktop\\AUS CUB AML\\Vendors_Sheet.xlsx', sheet_name=0)

regulator_df = pd.read_excel('C:\\Users\\40101584\\Desktop\\AUS CUB AML\\Regulated_Vendors_Sheet.xlsx', sheet_name=0)

compare = pd.MultiIndex.from_product([vendor_df['Name of vendor'],
                                      regulator_df['Name of Entity']]).to_series()


def metrics(tup):
    return pd.Series([fuzz.ratio(*tup),
                      fuzz.token_sort_ratio(*tup)],
                     ['ratio', 'token'])

#compare.apply(metrics) -- Either this works or the below line

result = compare.apply(metrics).unstack().idxmax().unstack(0)

Problems with Above Code:

The code works if the two dataframes are small but it is taking forever when I give the complete dataset. Above code is taken from 3rd link.

Any solution if the same thing can work fast or can work with large dataset?

UPDATE 1

Can the above code be made faster if we pass or hard-code a score say 80 which will filter series/dataframe only with fuzzyscore > 80 ?

i faced the same problem but here you are running `compare.apply(metrics)` twice, it takes a long time to apply the ratio and token, may be you'd be better of commenting the second last line — anky, Sep 10 '18 at 11:15
Actually I have tried both things...both of them is taking forever for me — Rahul Agarwal, Sep 10 '18 at 11:16

score 3 · Accepted Answer · answered Sep 11 '18 at 08:24

3

Below solution is faster than what I posted but if someone has a more faster approach please tell:

matched_vendors = []

for row in vendor_df.index:
    vendor_name = vendor_df.get_value(row,"Name of vendor")
    for columns in regulator_df.index:
        regulated_vendor_name=regulator_df.get_value(columns,"Name of Entity")
        matched_token=fuzz.partial_ratio(vendor_name,regulated_vendor_name)
        if matched_token> 80:
            matched_vendors.append([vendor_name,regulated_vendor_name,matched_token])

answered Sep 11 '18 at 08:24

Rahul Agarwal

4,034
7
27
51

Aarwal I get AttributeError: 'collections.OrderedDict' object has no attribute 'index'... thoughts plz – stack user Mar 29 '21 at 13:40
need to convert your dict values to list – Rahul Agarwal Mar 30 '21 at 06:12
thank you. So mine is running like 24 hrs and going on.. here is my 2 Q ANY HELP PLZ https://stackoverflow.com/questions/66856883/fuzzy-matching-two-dataframes-columns-without-a-key-column-and-different-rows?noredirect=1#comment118183672_66856883 – stack user Mar 30 '21 at 15:42
Posted a link at your question...check that out...I implemented that and it worked great!! – Rahul Agarwal Mar 31 '21 at 07:05
I get the error on the 7th column "matched_token=fuzz.partial_ratio(vendor_name,regulated_vendor_name)" object of type 'float' has no len() My dataframes dont have any float objects in them, only string objects. Do you know where the issue may be? – Sebastian ten Berge Nov 30 '21 at 12:11
there can be blanks in your columns, check the type of column also. Google this error, there are lot of solutions for this error – Rahul Agarwal Nov 30 '21 at 13:39

Ankit Kamboj · Answer 2 · 2021-12-18T10:00:44.413

I've implemented the code in Python with parallel processing, which will be much faster than serial computation. Furthermore, where a fuzzy metric score exceeds a threshold, only those computations are performed in parallel. Please see the link below for the code:

https://github.com/ankitcoder123/Important-Python-Codes/blob/main/Faster%20Fuzzy%20Match%20between%20two%20columns/Fuzzy_match.py

Vesrion Compatibility:

pandas version :: 1.1.5 ,
numpy vesrion:: 1.19.5,
fuzzywuzzy version :: 1.1.0 ,
joblib version :: 0.18.0

Fuzzywuzzy metric explanation: link text

Output from code:

score 1 · Answer 3 · answered Feb 18 '20 at 14:47

1

in my case also i need to look for only above 80. i modified your code as per my use case.hope it helps.

compare = compare.apply(metrics)
compare_80=compare[(compare['ratio'] >80) & (compare['token'] >80)]

answered Feb 18 '20 at 14:47

Sathish Kothandam

1,530
3
16
34

Fuzzy Match columns of Different Dataframe

3 Answers3