[Python] What is the most efficient way to compare to Dataframe columns and match the similar...

Assuming we have two Dataframes each containing a column of somewhat similar string-based values. What is the most efficient and/or effective way to match the rows with similar columns, based on a comparison function — like textdistance's implementation of Jaro-Winkler?

Example DataFrames:

first_df = pd.DataFrame( ['Cars and cats', 'Spaceship', 'Captain Marvel', 'Dune','Bucks in 6'], columns=['Title'])

second_df = pd.DataFrame( ['Captain Harlock', 'Cats and dogs', 'Buccuneers', 'Dune buggy','Milwaukee Bucks'], columns=['Title'])


What I'm thinking is:

• Creating a Cartesian Product based on each DataFrame's column of interest
• Apply the comparison function and store the result in a new column. Let's call it similarity_score
• Sort the new DataFrame by best value to worst (depending on the algorithm)
• Drop the duplicates of the column we are mostly interested in

Implementation:

comparison_df = first_df.merge(second_df, how='cross')

comparison_df['similarity_score'] = comparison_df.apply(lambda row: textdistance.jaro_winkler.normalized_similarity(row['First DataFrame Titles'], row['Second DataFrame Titles']), axis=1)
display(comparison_df)

comparison_df = comparison_df.sort_values('similarity_score', ascending=False).drop_duplicates(subset=['First DataFrame Titles'], keep='first')


Any suggestions are welcome. Thank you in advance.

Continue reading...