如何用Python基于唯一标识计算两个Excel工作表的字段匹配百分比？

核心实现逻辑 #

• 关联键处理原则：作为唯一值的identity_no必须做精确等值匹配，禁止纳入模糊匹配范围——这也是此前使用fuzzy match方法失败的核心常见误区
• 普通字段适配：针对存在拼写差异的地址类字段，采用字符串模糊相似度算法计算匹配度，避免硬匹配的过度严格判定
• 特殊场景兼容：对主表中无对应identity_no的条目，直接标记匹配准确率为0

落地实现步骤 #

1. 数据预处理：统一清洗identity_no的格式（转字符串、去除两端空格），避免因格式差异（如数值型/字符串型、带空格）导致的匹配失败
2. 关联合并：以清洗后的identity_no为关联键，对Sheet1（输入表）、Sheet2（主表）做左连接，保留输入表全量记录，给不同来源的同名字段加区分后缀
3. 逐字段计算相似度：自定义适配空值、拼写差异的字段相似度计算规则
4. 聚合计算匹配率：对每条记录的所有普通字段相似度取平均（可按需调整为加权计算），生成match_percent列
5. 结果导出：将带匹配率的结果输出为独立Excel文件

可直接运行的参考代码 #

import pandas as pd
from fuzzywuzzy import fuzz

# 1. 读取Excel源数据
excel_file = pd.ExcelFile("你的目标Excel文件路径.xlsx")
input_df = excel_file.parse("Sheet1")
master_df = excel_file.parse("Sheet2")

# 2. 清洗关联键，规避格式类匹配误差
input_df["identity_no"] = input_df["identity_no"].astype(str).str.strip()
master_df["identity_no"] = master_df["identity_no"].astype(str).str.strip()

# 3. 左连接合并两表，给同名字段加来源后缀
merged_df = pd.merge(
    input_df,
    master_df,
    on="identity_no",
    how="left",
    suffixes=("_input", "_master")
)

# 4. 定义单字段相似度计算函数
def calc_single_field_score(input_value, master_value):
    # 主表无对应记录的场景返回0分
    if pd.isna(master_value):
        return 0
    # 双空值视为完全匹配
    if pd.isna(input_value) and pd.isna(master_value):
        return 100
    # 单空值视为不匹配
    if pd.isna(input_value) or pd.isna(master_value):
        return 0
    # 普通字段用模糊算法计算相似度，适配地址拼写差异
    return fuzz.ratio(str(input_value), str(master_value))

# 5. 批量计算每条记录的match_percent
compare_cols = [col.replace("_input", "") for col in merged_df.columns if col.endswith("_input")]
match_percent_list = []

for _, row in merged_df.iterrows():
    # 无主表匹配的条目直接记0
    if pd.isna(row[f"{compare_cols[0]}_master"]):
        match_percent_list.append(0)
        continue
    # 逐字段计算得分
    field_scores = [calc_single_field_score(row[f"{col}_input"], row[f"{col}_master"]) for col in compare_cols]
    # 计算平均分（如需加权匹配率可在此处替换为加权计算逻辑）
    avg_score = round(sum(field_scores) / len(field_scores), 2)
    match_percent_list.append(avg_score)

# 新增匹配率列
merged_df["match_percent"] = match_percent_list

# 6. 导出结果
merged_df.to_excel("匹配准确率计算结果.xlsx", index=False)

自定义加权匹配的扩展说明 #

• 若对不同字段的匹配重要性有区分要求（如地址字段占比更高），可替换平均分计算逻辑为加权计算：

# 自定义字段权重配置
field_weight = {
    "name": 0.3,
    "phone": 0.2,
    "address": 0.5
}
# 替换平均分为加权得分的计算逻辑
weighted_score = sum([score * field_weight[col] for col, score in zip(compare_cols, field_scores)])
avg_score = round(weighted_score, 2)

适配场景说明 #

• 可自动识别地址类字段的拼写差异（如少字、顺序差异），不会因微小拼写差异判定为完全不匹配
• 对无主表对应记录的条目自动标记为0%匹配率，无需手动筛选
• 最终输出的结果表保留两表全部原始字段，直接新增match_percent列，满足后续统计需求

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