python - Fuzzywuzzy 在 Python 中匹配来自不同数据帧的多列

Question

假设我有以下 3 个数据框:

import numpy as np
from fuzzywuzzy import fuzz
from fuzzywuzzy import process
import pandas as pd
import io
import csv
import itertools
import xlsxwriter

df1 = pd.DataFrame(np.array([
    [1010667747, 'Suzhou', 'Suzhou IFS'],
    [1010667356, 'Shenzhen', 'Kingkey 100'],
    [1010667289, 'Wuhan', 'Wuhan Center']]),
    columns=['id', 'city', 'name']
)    
df2 = pd.DataFrame(np.array([
    [190010, 'Shenzhen', 'Ping An Finance Centre'],
    [190012, 'Guangzhou', 'Guangzhou CTF Finance Centre'],
    [190015, 'Beijing', 'China Zun']]),
    columns=['id', 'city', 'name']
)    
df3 = pd.DataFrame(np.array([
    ['ZY-13', 'Shanghai', 'Shanghai World Financial Center'],
    ['ZY-15', 'Hong Kong', 'International Commerce Centre'],
    ['ZY-16', 'Changsha', 'Changsha IFS Tower T1']]),
    columns=['id', 'city', 'name']
)

我想通过使用 fuzzywuzzy 包计算它们的相似度来找到相似的建筑物名称，这是我需要改进的解决方案:

首先，我将所有三个数据帧连接到一列作为 full_name。实际上，在这一步，我不应该将 id 添加到 full_name 中，但为了更好地区分来自不同数据帧的建筑物名称，我添加了它:

df1['full_name'] = df1['id'].apply(str) + '_' + df1['city'] + '_' + df1['name']
df2['full_name'] = df2['id'].apply(str) + '_' + df2['city'] + '_' + df2['name']
df3['full_name'] = df3['id'].apply(str) + '_' + df3['city'] + '_' + df3['name']

df4 = df1['full_name'] 
df5 = df2['full_name'] 
df6 = df3['full_name'] 

frames = [df4, df5, df6]
df = pd.concat(frames)

df.columns = ["full_name"]
df.to_excel('concated_names.xlsx', index = False)

其次，我遍历所有 full_names 并相互比较以获得每对建筑物名称的 similarity_ratio:

df = pd.read_excel('concated_names.xlsx')
projects = df.full_name.tolist()

processedProjects = []
matchers = []

threshold_ratio = 10

for project in projects:
    if project:
        processedProject = fuzz._process_and_sort(project, True, True)
        processedProjects.append(processedProject)
        matchers.append(fuzz.SequenceMatcher(None, processedProject))

with open('output10.csv', 'w', encoding = 'utf_8_sig') as f1:
    writer = csv.writer(f1, delimiter=',', lineterminator='\n', )
    writer.writerow(('name', 'matched_name', 'similarity_ratio'))

    for project1, project2 in itertools.combinations(enumerate(processedProjects), 2):
        matcher = matchers[project1[0]]
        matcher.set_seq2(project2[1])
        ratio = int(round(100 * matcher.ratio()))
        if ratio >= threshold_ratio:
            #print(projects[project1[0]], projects[project2[0]])
            my_list = projects[project1[0]], projects[project2[0]], ratio
            print(my_list)
            writer.writerow(my_list)

my_list 结果:

('1010667747_Suzhou_Suzhou IFS', '1010667356_Shenzhen_Kingkey 100', 44)
('1010667747_Suzhou_Suzhou IFS', '1010667289_Wuhan_Wuhan Center', 49)
('1010667747_Suzhou_Suzhou IFS', '190010_Shenzhen_Ping An Finance Centre', 33)
('1010667747_Suzhou_Suzhou IFS', '190012_Guangzhou_Guangzhou CTF Finance Centre', 47)
......

在最后一步，我在 Excel 中手动拆分 output10.csv 并得到我最终的预期结果(如果我有每个建筑物的数据帧源会更好):

           id    city        name  matched_id matched_name  \
0  1010667747  Suzhou  Suzhou IFS  1010667356     Shenzhen   
1  1010667747  Suzhou  Suzhou IFS  1010667289        Wuhan   
2  1010667747  Suzhou  Suzhou IFS      190010     Shenzhen   
3  1010667747  Suzhou  Suzhou IFS      190012    Guangzhou   
4  1010667747  Suzhou  Suzhou IFS      190015      Beijing   

                 matched_name.1  similarity_ratio  
0                   Kingkey 100                44  
1                  Wuhan Center                49  
2        Ping An Finance Centre                33  
3  Guangzhou CTF Finance Centre                47  
4                     China Zun                27  

如何在 Python 中以更高效的方式获得最终预期结果？谢谢。

Answer 1

试试这个解决方案:我正在使用 numpy 和 itertools 来加速和简化编码，不需要使用 excel 文件...

import numpy as np
from fuzzywuzzy import fuzz
from itertools import product
import pandas as pd

   :
   :

frames = [pd.DataFrame(df4), pd.DataFrame(df5), pd.DataFrame(df6)]
df = pd.concat(frames).reset_index(drop=True)

dist = [fuzz.ratio(*x) for x in product(df.full_name, repeat=2)]
df1 = pd.DataFrame(np.array(dist).reshape(df.shape[0], df.shape[0]), columns=df.full_name.values.tolist())

#create of list of dataframes (each row id dataframe)
listOfDfs = [df1.loc[idx] for idx in np.split(df1.index, df.shape[0])]

#in dictionary, you have a Dataframe by name wich contains all ratios from other names
DataFrameDict = {df['full_name'][i]: listOfDfs[i] for i in range(df1.shape[0])}

for name in DataFrameDict.keys():
    print(name)
    #print(DataFrameDict[name]