Python/pandas模糊匹配如何提取原Name字段及df2关联列值

基于Name列对df1、df2做模糊匹配跨表取数时，为提升准确率先清洗两表Name列生成Name2字段，移除无关词汇后基于Name2做匹配，现有实现存在两个问题：

• 匹配结果仅返回清洗后的Name2字段值，无法获取df2原始Name列内容
• 无法基于匹配结果拉取df2中其他对应字段

现有实现代码：

from fuzzywuzzy import process, fuzz
import pandas as pd
import numpy as np

df1 = pd.DataFrame({
   'Name': ['Testing and information 1', 'Categories and information 2', 'Money and information 3', 'Time and information 4'],
    'Category': ['Category 1', 'Category 2', 'Category 3', 'Category 4']
})

df2 = pd.DataFrame({
    'Name': ['Testing and information example', 'Categories and information example', 'Money and information example'],
    'Type': ['Type 1', 'Type 2', 'Type 3']
})

#Create Name2 and remove certain words

df1['Name2']  = df1['Name'].str.replace('example|and|information', "")
df2['Name2']  = df2['Name'].str.replace('example|and|information', "")

# empty lists for storing the matches later
match1 = []
match2 = []
k = []

# converting dataframe column to list of elements for fuzzy matching

myList1 = df1['Name2'].tolist()
myList2 = df2['Name2'].tolist()

threshold = 80

# iterating myList1 to extract closest match from myList2

for i in myList1:
   match1.append(process.extractOne(i, myList2, scorer=fuzz.ratio))
df1['Name from df2 Identified'] = match1
for j in df1['Name2']:
   if j[1] >= threshold:
      k.append(j[0])
   match2.append(",".join(k))
   k = []

# saving matches to df1
df1['Name from df2 Identified'] = match2
print("\nName from df2 Identified...")
print(df1)

现有代码运行结果示例：

现有代码将清洗后的Name2单独抽为列表传入匹配函数，没有保留Name2值和df2原始行索引的映射关系，匹配命中后无法回溯定位到df2的对应行，自然拿不到原始Name和其他字段。

核心改动是构造带索引映射的匹配候选集，匹配命中后直接通过索引读取df2对应行的所有需要的字段：

from fuzzywuzzy import process, fuzz
import pandas as pd
import numpy as np

df1 = pd.DataFrame({
   'Name': ['Testing and information 1', 'Categories and information 2', 'Money and information 3', 'Time and information 4'],
    'Category': ['Category 1', 'Category 2', 'Category 3', 'Category 4']
})

df2 = pd.DataFrame({
    'Name': ['Testing and information example', 'Categories and information example', 'Money and information example'],
    'Type': ['Type 1', 'Type 2', 'Type 3']
})

# 生成清洗后的Name2列，补充regex参数和strip清理多余空格
df1['Name2']  = df1['Name'].str.replace('example|and|information', "", regex=True).str.strip()
df2['Name2']  = df2['Name'].str.replace('example|and|information', "", regex=True).str.strip()

threshold = 80
# 构造匹配候选字典：key为df2行索引，value为清洗后的Name2值，匹配时会自动返回命中的索引
candidates = df2['Name2'].to_dict()

# 初始化结果列
df1['df2_原始Name'] = np.nan
df1['df2_对应Type'] = np.nan
df1['匹配得分'] = 0

# 遍历df1逐行匹配
for idx, row in df1.iterrows():
    match_result = process.extractOne(row['Name2'], candidates, scorer=fuzz.ratio)
    if match_result and match_result[1] >= threshold:
        matched_name2, score, df2_match_idx = match_result
        # 按命中的索引直接取df2对应字段
        df1.loc[idx, 'df2_原始Name'] = df2.loc[df2_match_idx, 'Name']
        df1.loc[idx, 'df2_对应Type'] = df2.loc[df2_match_idx, 'Type']
        df1.loc[idx, '匹配得分'] = score

print(df1)

运行修正后代码：

• 成功匹配的行会自动填充df2的原始Name值、对应Type值和匹配得分
• 未达到80分匹配阈值的行（示例中为Time and information 4对应的行），相关字段保留空值
• 如果需要拉取df2更多字段，直接在结果初始化阶段加列，匹配时按索引取值即可，逻辑不需要改动

内容的提问来源于stack exchange，提问作者Angela Carr