基于Python的Excel表ID自动映射（含格式转换与年份校验）

需求说明 #

现有两个通过ID关联的Excel表格，但ID格式存在差异（例：Table A的ID为AA00-123-334，Table B的对应ID可能是AA-00123-334、B00123-335这类变体），需按以下逻辑完成数据匹配与填充：

• 移除Table B中ID的所有字母
• 移除ID中的连字符-
• 从ID右侧起数3位插入连字符，调整为类似00-123-334的统一格式
• 在Table A中查找匹配该统一格式的ID
• 匹配成功后，校验Table B的出生年份与Table A的出生日期年份是否一致，一致则将Table A的对应ID填充至Table B的expected_id字段

示例数据与完整实现代码 #

import pandas as pd
import numpy as np
import re

# 构造示例数据
data_A = [['AA00-123-334', '2011-10-10'], ['BB00-123-335', '2012-10-10'], ['CC00-123-336', '2013-10-10'], ['DD00-123-37', '2015-10-10']]
Table_A = pd.DataFrame(data_A, columns=['ID', 'DOB'])

data_B = [['AA-00123-334',2011, np.NaN], ['B00123-335', 2012, np.NaN], ['123336',  2013, np.NaN], ['00123-37',  2014, np.NaN]]
Table_B = pd.DataFrame(data_B, columns=['ID', 'Year_ofbirth', 'expected_id'])

# 步骤1-2：清理Table B的ID——移除字母和连字符
Table_B['cleaned_id'] = Table_B['ID'].apply(lambda x: re.sub(r'[A-Za-z\-]', '', str(x)))

# 步骤3：调整格式——从右侧起数3位插入连字符
def format_id(s):
    if len(s) <= 3:
        return s
    return f"{s[:-3]}-{s[-3:]}"

Table_B['formatted_id'] = Table_B['cleaned_id'].apply(format_id)

# 预处理Table A：提取ID中的数字部分（用于匹配），并提取出生年份
Table_A['id_num_part'] = Table_A['ID'].apply(lambda x: re.sub(r'[A-Za-z\-]', '', str(x)))
Table_A['formatted_id'] = Table_A['id_num_part'].apply(format_id)
Table_A['birth_year'] = pd.to_datetime(Table_A['DOB']).dt.year

# 步骤4-5：匹配ID并校验年份，填充expected_id
merged = Table_B.merge(Table_A[['ID', 'formatted_id', 'birth_year']], 
                       on='formatted_id', 
                       how='left')

# 仅当年份一致时填充，否则保留原空值
Table_B['expected_id'] = np.where(merged['Year_ofbirth'] == merged['birth_year'], 
                                  merged['ID'], 
                                  Table_B['expected_id'])

print(Table_B)

代码逻辑说明 #

1. ID清理：用正则表达式re.sub(r'[A-Za-z\-]', '', str(x))直接移除Table B ID中的所有字母和连字符，得到纯数字字符串。
2. 格式统一：自定义format_id函数，从纯数字字符串右侧截取最后3位，与前面部分用连字符拼接，确保格式统一。
3. Table A预处理：同样提取ID的纯数字部分并格式化，同时将DOB字段转为日期格式，提取出生年份用于后续校验。
4. 匹配与校验：通过merge按格式化后的ID关联两个表格，再用np.where判断年份是否一致，仅一致时填充expected_id字段。

该方案基于pandas矢量化操作实现，适合数千条数据的规模，避免了逐行循环的性能损耗。

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