Optimaze comparing two dataframes with fuzzywuzzy

Kate Mosolova 0
Viewed 22

I've two dataframe (xlsx-file).

df_source contains information about books that have already been loaded (66,000 rows).

df_sort contains information about books that need to be sorted for loaded (36,000 rows).

I need to compare each row in df_sort with each in df_source.

The strings can be slightly different, for example: In df_source: "Stephen Edwin King it"

But at the same time in df_sort: "S. King it"

Even though they are different, they are the same book, so I use fuzzywuzzy

I wrote code that works. But due to the large volume of files, gradually the code is running slower and slower. Checking the first 15 lines of the df_sort took 2 hours.

I would appreciate any ideas on how to make the code faster, maybe I chose the wrong path initially.

My code:

import pandas as pd
from fuzzywuzzy import fuzz
from tqdm import tqdm

df_sort     = pd.read_excel('data/sort.xlsx', sheet_name='Sheet 1',   names=["author", "name"],         dtype="string")
df_source   = pd.read_excel('data/list.xlsx', sheet_name='Sheet 1', names=["link", "name", "author"], dtype="string")

result    = pd.DataFrame(columns=["author", "name"])
duplicate = pd.DataFrame(columns=["ratio", "author source", "name source", "author sort", "name sort"])


for row in tqdm(range(11, 13)):
    source_author = df_source['author'].iloc[row]
    source_name   = df_source['name'].iloc[row]
    for row_sort in range(0, len(df_sort)):
        sort_author = df_sort['author'].iloc[row_sort]
        sort_name   = df_sort['name'].iloc[row_sort]
        ratio = fuzz.ratio(f"{source_author} {source_name}", f"{sort_author} {sort_name}")
        if ratio > 70:
            duplicate.loc[len(duplicate.index)] = [ratio, source_author, source_name, sort_author, sort_name]
        else:
            result.loc[len(result.index)] = [sort_author, sort_name]


duplicate.to_csv('out/duplicate.csv', index=False)
result.to_csv('out/result.csv', index=False)
fuzzywuzzy sorting dataframe pandas python
1 Answers

You try to compare each element in two relative large sequence which results in a total of more than 2e+09 string comparision. This is always going to take a while. However there are a couple of things you can do to improve the performance over your current implementation

use rapidfuzz for string matching

fuzzywuzzy has a very slow implementation of these string comparision algorithms. You should be able to improve the performance significantly by simply replacing your usage of fuzzywuzzy with rapidfuzz (disclaimer: I am the author)

calculate similarity matrixes

rapidfuzz provides a way to calculate the similarity for each element in two sequences. This is a lot faster, since it can:

  • cache parts of the calculation
  • save the overhead of calling into Python
  • use multithreading

You can find the full documentation for cdist here.

reduce memory usage

Your code is currently storing all results:

if ratio > 70:
    duplicate.loc[len(duplicate.index)] = [ratio, source_author, source_name, sort_author, sort_name]
else:
    result.loc[len(result.index)] = [sort_author, sort_name]

which means you are going to store more than 2e+09 elements. You are going to run out of memory with this at some point. You should probably compare the data in chunks and store the results in between

consider handling similarity for author / book title independently

you calculate the similarity for author + book title in one step:

fuzz.ratio(f"{source_author} {source_name}", f"{sort_author} {sort_name}")

you might want to split this into two comparisons:

fuzz.ratio(source_author, sort_author)
fuzz.ratio(source_name, sort_name)

since at least from my understanding this data does not really belong together.

Example

Here is an example how this could be used in your case. Note that the example only calculates fuzz.ratio(source_author, sort_author) for simplicity

from rapidfuzz import fuzz, process

process.cdist(df_source['author'], df_sort['author'], scorer=fuzz.ratio, workers=-1)

Note that you are going to have to call this with smaller chunks of your data to avoid it from running out of memory.

maxbachmann0
Related