d_test = {
'name' : ['South Beach', 'Dog', 'Bird', 'Ant', 'Big Dog', 'Beach', 'Dear', 'Cat']'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
and, 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.
# pip install thefuzz
from thefuzz import fuzz
d_testdf_test = {
'name' : ['South Beach', 'Dog', 'Bird', 'Ant', 'Big Dog', 'Beach', 'Dear', 'Cat'],
'cluster_number' : [1, 2, 3, 3, 2, 1, 4df_test.sort_values(['cluster_number', 2]
}
'name'])
df_test = pd.DataFramereset_index(d_testdrop=True, inplace=True)
df_test['id'] = 0
i = 1
for index, row in df_test.iterrows():
forrow_ index_,= row_row
in df_test.iterrows(): index_ = index
while index_ < len(df_test) ifand row['cluster_number']df_test.loc[index, 'cluster_number'] == row_['cluster_number']df_test.loc[index_, 'cluster_number'] and row_['id']df_test.loc[index_, 'id'] == 0:
if row['name'] == df_test.loc[index_, if'name'] or fuzz.ratio(row['name'], row_['name']df_test.loc[index_, 'name']) > 50:
df_test.loc[index_,'id'] = int(i)
is_i_used = True
is_i_used = True index_ += 1
if is_i_used == True:
i += 1
is_i_used = False
name cluster_number id
0 South Beach 1 1
1 Dog 1
1 South 2Beach 1 2 1
2 BirdBig Dog 3 2 32
3 AntCat 3 2 4 3
4 Big Dog 2 2 2
5 BeachDry Fish 1 2 14
6 DearFish 2 4
7 Ant 3 5
78 CatBird 2 3 6
9 Dear 4 7
Note, for Cat
we got id
as 6
but it is fine because it is unique anyway.
While algorithm above worksComputation runs for test data I am not able to use it210 seconds for real data that I have (aboutdataframe with 1 million rows) where in average each cluster has about 10 rows and max cluster size is about 200 rows. I am trying to understand how to vectorize the code and get rid of two for-loops.