match partial value between 2 columns from different dataframes using with spacy

user_12496869 0
Viewed 93

I have two data frames df1 and df2 as shown below:

Df1:

           movie_name correct_id  year
0             birdman        N/A  2010
1   avengers: endgame        N/A  2020
2            deadpool        N/A  2013
3        bla 208-2020        N/A  2018
4  once upon deadpool        N/A  2020

Df2: data frame of reference

               movie_ref     id
0               birdmans  19876
1          The bird King  20807
2  The avengers: endgame  13978
3   once upon a deadpool  49999
4           bla 207-2020  45678
5                bla 208  66097

Expected Result:

           movie_name correct_id  year  
0             birdman      19876  2010
1   avengers: endgame      13978  2020
2            deadpool        N/A  2013
3        bla 208-2020      66097  2018
4  once upon deadpool      49999  2020

Please how do I merge two dataframes based on partial string match using spacy?

NB: The movie's name not exactly the same/ and the two dataframes have different lengths.

pandas spacy dataframe
1 Answers

you can use the difflib library to find the closet match and then you can create a mapping dict to fetch the required values:

import difflib

df2.movie_ref = df2.movie_ref.apply(lambda x: difflib.get_close_matches(
    x, df1.movie_name.values, n=1, cutoff=.6)).explode()
df1['correct_id'] = df1.movie_name.map(dict(df2.dropna().values))

OUTPUT:

           movie_name  correct_id  year
0             birdman     19876.0  2010
1   avengers: endgame     13978.0  2020
2            deadpool         NaN  2013
3        bla 208-2020     66097.0  2018
4  once upon deadpool     49999.0  2020

NOTE: change the cutoff value according to your requirement.

user_154380330
Related