Make unique id based on text data column with similarity scoring

Question

I have the following dataframe:

d_test = {
    'name' : ['South Beach', 'Dog', 'Bird', 'Ant', 'Big Dog', 'Beach', 'Dear', 'Cat', 'Fish', 'Dry Fish'],
    'cluster_number' : [1, 2, 3, 3, 2, 1, 4, 2, 2, 2]
}
df_test = pd.DataFrame(d_test)

I want to identify similar names in name column if those names belong to one cluster number and create unique id for them. For example South Beach and Beach belong to cluster number 1 and their similarity score is pretty high. So we associate it with unique id, say 1. Next cluster is number 2 and three entities from name column belong to this cluster: Dog, Big Dog, Cat, 'Fish' and 'Dry Fish'. Dog and Big Dog have high similarity score and their unique id will be, say 2. For Cat unique id will be, say 3. Finally for 'Fish' and 'Dry Fish' unique id will be, say 4. And so on.

Here is my code:

# pip install thefuzz
from thefuzz import fuzz

df_test = df_test.sort_values(['cluster_number', 'name'])
df_test.reset_index(drop=True, inplace=True)

df_test['id'] = 0

i = 1
for index, row in df_test.iterrows():
    row_ = row
    index_ = index
    
    while index_ < len(df_test) and df_test.loc[index, 'cluster_number'] == df_test.loc[index_, 'cluster_number'] and df_test.loc[index_, 'id'] == 0:
        if row['name'] == df_test.loc[index_, 'name'] or fuzz.ratio(row['name'], df_test.loc[index_, 'name']) > 50:
            df_test.loc[index_,'id'] = i
            is_i_used = True
        index_ += 1
        
    if is_i_used == True:
        i += 1
        is_i_used = False
                           

Code generates the following result:

    name         cluster_number  id
0   Beach               1        1
1   South Beach         1        1
2   Big Dog             2        2
3   Cat                 2        3
4   Dog                 2        2
5   Dry Fish            2        4
6   Fish                2        4
7   Ant                 3        5
8   Bird                3        6
9   Dear                4        7

Any advises in code improvement are much appreciated

Answer 1

This code looks like it is trying to create a new 'id' column in the DataFrame that groups names together if they belong to the same cluster and have a high similarity score.

There are a few issues with this code. First, the variable is_i_used is never defined. It is used in the code but never assigned a value. This will cause an error. Additionally, the code is not handling the case where two names belong to the same cluster but have a low similarity score. In this case, they would not be assigned the same 'id' value, but the code does not account for this.

Here is a modified version of the code that addresses these issues and should work as intended:

# pip install thefuzz
from thefuzz import fuzz

df_test = df_test.sort_values(['cluster_number', 'name'])
df_test.reset_index(drop=True, inplace=True)

df_test['id'] = 0

i = 1
for index, row in df_test.iterrows():
    row_ = row
    index_ = index
    
    # Check if the current name has already been assigned an 'id' value.
    # If it has, we can skip it and move on to the next name.
    if row['id'] != 0:
        continue
    
    # Iterate through the remaining rows in the DataFrame
    # and assign the current 'id' value to any names that belong
    # to the same cluster and have a high similarity score.
    while index_ < len(df_test) and df_test.loc[index_, 'cluster_number'] == df_test.loc[index, 'cluster_number']:
        if row['name'] == df_test.loc[index_, 'name'] or fuzz.ratio(row['name'], df_test.loc[index_, 'name']) > 50:
            df_test.loc[index_,'id'] = i
        index_ += 1
    
    # Increment the 'id' value for the next group of similar names.
    i += 1

This code should work as intended and assign unique 'id' values to groups of similar names within each cluster. Note that names that do not have a high enough similarity score to be grouped together will be assigned different 'id' values.