ML EXPLAINED

Tag: Fuzze merge

  • Fuzzy Match DataFrames Using RapidFuzz and Pandas

    Today, we will be going over how you can match two DataFrames using RapidFuzz and Pandas.

    Suppose that you’ve two DataFrames, one having the product_id and the other having the product_price, with the key being the name. Since, names can be written differently, you’ve to match them.

    nameproduct_id
    M.D. LuffyA
    R. ZoroB
    SanjiC
    NamiD
    NarutoE
    name and product_id table

    nameproduct_price
    Monkey D. Luffy100
    Roronoa Zoro10
    Sannnji500
    Nami Chain1000
    Jiraiya300
    name and product_price table

    Since we can see that the name is written differently in the tables, we can’t do a direct left join using name as the key. We will be using fuzzy join using rapidfuzz library.

    # Importing the libraries
import pandas as pd
from rapidfuzz import fuzz
from rapidfuzz.process import cdist, extract

    We will be using the extract process to find the closest matching key from the second table to use as merge in the first table.

    df1 = pd.DataFrame({"name": ["M.D. Luffy",
"R. Zoro",
"Sanji",
"Nami", 
"Naruto"],
"product_id":["A", "B", "C", "D", "E"]})

df2 = pd.DataFrame({"name": ["Monkey D. Luffy",
"Roronoa Zoro",
"Sannnji",
"Nami Chan", 
                            "Jiraiya"],
"product_price":[100,10,500,1000,300]})

df1['join_key_tuple'] = df1['name'].apply(lambda x: extract(query = x, choices =df2['name'], score_cutoff=80))

    Here the query is the string you want to match, choices are all the choices to match against. You can pass a custom scorer as well, but here we are using the defualt scorer, and lastly we’re passing a cutoff which we will use to determine a successful match.

             name	    product_id	           join_key_tuple
0	M.D. Luffy	A	        [(Monkey D. Luffy, 85.5, 0)]
1	R. Zoro	        B	        [(Roronoa Zoro, 85.5, 1)]
2	Sanji	        C	        [(Sannnji, 83.33333333333334, 2)]
3	Nami	        D	        [(Nami Chan, 90.0, 3)]
4	Naruto	        E	        []

    We can now extract the key to join from the returned tuple and make the join to have the price of the product against the product_id. 

    df1["join_key"] = df1["join_key_tuple"].apply(lambda x: x[0][0] if x else np.nan)

df1.merge(df2, how = "left", left_on = "join_key", right_on ="name")

    This way you can do fuzz join on two pandas dataframe.