[FIXED] Python Pandas - How to Efficiently Merge DataFrames with Partial String Matching?

Issue

I have two Pandas DataFrames, df1 and df2, both containing a column named 'name'. The 'name' column in df1 contains full names, while the 'name' column in df2 contains partial names. I want to merge these DataFrames based on partial string matching of names and create a new DataFrame, merged_df.

For example:

python
df1:
   id      name
0   1   John Doe
1   2  Jane Smith
2   3  Bob Johnson

df2:
   value      name
0    10       John
1    20        Jan
2    30        Bob

# After merging based on partial string matching:
merged_df:
   id      name  value
0   1   John Doe     10
1   2  Jane Smith     20
2   3  Bob Johnson     30

Any help or guidance on optimizing the merge operation would be greatly appreciated!

I've tried using merge and str.contains functions, but the results are not as expected, and it seems inefficient for large datasets. Can someone suggest an efficient way to achieve this partial string matching merge in Pandas?

Solution

Your Jan should match with Jane, requires some form of similarity matching. For your needs, a package called fuzzywuzzy could do the trick.

Using .apply() can scale to larger datasets however you'll need more features to match on, as first name will start creating duplicates very quicky.

Nevertheless, here is the code to simulate your dataframe and return the dataframe matching your success criteria.

For your debugging and visual aid, I have retained the series with which side the match is on, so you can see the inner workings. You can drop or rename, according to your production requirements.

import pandas as pd
from fuzzywuzzy import process

data1 = {'id': [1, 2, 3],
         'name': ['John Doe', 'Jane Smith', 'Bob Johnson']}
df1 = pd.DataFrame(data1)

data2 = {'value': [10, 20, 30],
         'name': ['John', 'Jan', 'Bob']}
df2 = pd.DataFrame(data2)

# Function to find the best match using fuzzywuzzy
def find_best_match(full_name, choices):
    first_name = full_name.split()[0]
    result = process.extractOne(first_name, choices)
    return result[0] if result[1] >= 80 else None

# Apply fuzzy matching to find best matches
df1['best_match'] = df1['name'].apply(lambda x: find_best_match(x, df2['name']))

# Merge based on best matches with suffixes
merged_df = pd.merge(df1, df2, how='left', left_on='best_match', right_on='name', suffixes=('_left', '_right'))
merged_df.set_index('id', inplace=True)
merged_df.head()

Answered By - dimButTries