python - 如何使用levenshtein函数删除pandas中的相似值

Question

我有一个看起来像这样的数据框 -

   ML_ENTITY_NAME        EDT_ENTITY_NAME
1  ABC BANK              HABIB METROPOLITAN BANK
2  ABC BANK              HABIB METROPOLITIAN BANK
3  BANK OF AMERICA       HSBC BANK MALAYSIA BHD
4  BANK OF AMERICA       HSBC BANK MALAYSIA SDN BHD
5  BANK OF NEW ZEALAND   HUA NAN COMMERCIAL BANK
6  BANK OF NEW ZEALAND   HUA NAN COMMERCIAL BANK LTD
7  CITIBANK N.A.         CHINA GUANGFA BANK CO LTD
8  CITIBANK N.A.         CHINA GUANGFA BANK CO.,LTD
9  SECURITY BANK CORP.   SECURITY BANK CORP
10 SIAM COMMERCIAL BANK  THE SIAM COMMERCIAL BANK PCL
11 TEMU                  ANZ BANK SAMOA LTD

我写了一个 levenshtein 函数，看起来像 -

def fm(s1, s2):
    score = Levenshtein.distance(s1,s2)
    if score == 0.0:
        score = 1.0
    else:
        score = 1 - (score / len(s1))
    return score

我想编写一段代码，如果两个 EDT_ENTITY_NAME 值的 levenstein 分数大于 0.75，那么我们会删除长度较小的一个值并保留该值长度更长。用于比较的 ML_ENTITY_NAME 也应该相同。

我的最终输出应该是这样的 -

   ML_ENTITY_NAME        EDT_ENTITY_NAME
1  ABC BANK              HABIB METROPOLITIAN BANK
2  BANK OF AMERICA       HSBC BANK MALAYSIA SDN BHD
3  BANK OF NEW ZEALAND   HUA NAN COMMERCIAL BANK LTD
4  CITIBANK N.A.         CHINA GUANGFA BANK CO.,LTD
5  SECURITY BANK CORP.   SECURITY BANK CORP
6  SIAM COMMERCIAL BANK  THE SIAM COMMERCIAL BANK PCL
7  TEMU                  ANZ BANK SAMOA LTD

目前我的方法是对 df 进行排序并迭代循环并检查 ML_ENTITY_NAME 值是否相同，然后计算 EDT_ENTITY_NAME 的 levenshtein。我添加了一个新列删除，如果满足上述条件并且一个 ML_ENTITY_NAME 的长度小于其他 ML_ENTITY_NAME，我会将删除列更新为 1。

我的代码看起来像 -

df.sort_values(by=['ML_ENTITY_NAME','EDT_ENTITY_NAME'],inplace=True)
df['delete']=0
for row1 in df.itertuples():
    for row2 in df.itertuples():
        if (str(row1.ML_ENTITY_NAME) == str(row2.ML_ENTITY_NAME)) and (1>fm(str(row1.EDT_ENTITY_NAME),str(row2.EDT_ENTITY_NAME))>.74):

            if(len(row1.EDT_ENTITY_NAME)>len(row2.EDT_ENTITY_NAME)):
                df.loc[row2.Index,row2[2]]=1
print(df)

目前它给出了错误的输出。

有人可以帮我提供一些答案/提示/建议吗？

Answer 1

我相信你需要:

#cross join by ML_ENTITY_NAME column
df1 = df.merge(df, on='ML_ENTITY_NAME', how='outer')
#remove same values per rows (distance 1)
df1 = df1[df1['EDT_ENTITY_NAME_x'] != df1['EDT_ENTITY_NAME_y']]
#apply function and compare
m1 = df1.apply(lambda x: fm(x['EDT_ENTITY_NAME_x'], x['EDT_ENTITY_NAME_y']), axis=1) > .75
m2 = df1['EDT_ENTITY_NAME_x'].str.len() > df1['EDT_ENTITY_NAME_y'].str.len()

#filtering
df2 = df1.loc[m1 & m2, ['ML_ENTITY_NAME','EDT_ENTITY_NAME_x']]
#remove  `_x`
df2.columns = df2.columns.str.replace('_x$', '')
#add unique rows per ML_ENTITY_NAME
df2 = df2.append(df[~df['ML_ENTITY_NAME'].duplicated(keep=False)]).reset_index(drop=True)
print (df2)
         ML_ENTITY_NAME               EDT_ENTITY_NAME
0              ABC BANK      HABIB METROPOLITIAN BANK
1       BANK OF AMERICA    HSBC BANK MALAYSIA SDN BHD
2   BANK OF NEW ZEALAND   HUA NAN COMMERCIAL BANK LTD
3         CITIBANK N.A.    CHINA GUANGFA BANK CO.,LTD
4   SECURITY BANK CORP.            SECURITY BANK CORP
5  SIAM COMMERCIAL BANK  THE SIAM COMMERCIAL BANK PCL
6                  TEMU            ANZ BANK SAMOA LTD