如何在Pandas中跨DataFrame匹配替换字段并生成历史列？

原始数据 #

主DataFrame #

Country Employee ID Location
CZ        1        WAREHOUSE
CZ        2        Warehouse
CZ        3        PlaNt
CZ        4        Car
DK        5        Car
DK        7 
ES        7        *
ES        8       Técnico
ES        3       Rádio

查找DataFrame #

Country Location name
CZ      Warehouse
CZ      Plant
DK      Car
DK      Plant
DK      Warehouse
ES      Tecnico
ES      Rádio

需求说明 #

需要按国家匹配Location列的值（不区分大小写，忽略非英文特殊字符/标点），执行以下操作：

• 若该国家对应的查找DataFrame中存在匹配值，则将Location替换为该匹配值；
• 若不存在匹配值，则替换为该国家在查找DataFrame中的第一个值；
• 创建Legacy Location列：
  • 若Location被替换，则填入原Location值；
  • 若未被替换，则留空；
  • 若原Location为空，则填入字符串"blank"。

预期输出 #

Country Employee ID Location    Legacy Location
CZ         1        Warehouse   WAREHOUSE
CZ         2        Warehouse   
CZ         3        Plant       PlaNt
CZ         4        Warehouse   Car
DK         5        Car 
DK         7        Car         Blank
ES         7        Tecnico     *
ES         8        Tecnico     Técnico
ES         3        Rádio   

请问实现该需求的最优方法是什么？

用Pandas结合字符串标准化处理就能高效实现需求，步骤如下：

1. 准备数据 #

先加载两个DataFrame：

import pandas as pd
import unicodedata

# 主数据
main_df = pd.DataFrame({
    'Country': ['CZ', 'CZ', 'CZ', 'CZ', 'DK', 'DK', 'ES', 'ES', 'ES'],
    'Employee ID': [1, 2, 3, 4, 5, 7, 7, 8, 3],
    'Location': ['WAREHOUSE', 'Warehouse', 'PlaNt', 'Car', 'Car', None, '*', 'Técnico', 'Rádio']
})

# 查找表
lookup_df = pd.DataFrame({
    'Country': ['CZ', 'CZ', 'DK', 'DK', 'DK', 'ES', 'ES'],
    'Location name': ['Warehouse', 'Plant', 'Car', 'Plant', 'Warehouse', 'Tecnico', 'Rádio']
})

2. 编写字符串标准化函数 #

统一处理大小写、移除非英文字符，保证匹配规则一致：

def normalize_str(s):
    if pd.isna(s):
        return ''
    # 转ASCII移除特殊字符，转小写并去除首尾空格
    normalized = unicodedata.normalize('NFKD', s).encode('ascii', 'ignore').decode('utf-8').lower().strip()
    return normalized

3. 预处理查找表 #

为每个国家构建匹配映射关系和默认值：

# 给查找表添加标准化列
lookup_df['norm_location'] = lookup_df['Location name'].apply(normalize_str)

# 构建每个国家的「标准化值→原始查找值」映射字典
country_maps = lookup_df.groupby('Country').apply(
    lambda x: dict(zip(x['norm_location'], x['Location name']))
).to_dict()

# 提取每个国家的默认值（查找表中第一个Location name）
country_defaults = lookup_df.groupby('Country')['Location name'].first().to_dict()

4. 处理主数据 #

# 初始化Legacy Location列：空值填'blank'，其他先保留原始值
main_df['Legacy Location'] = main_df['Location'].apply(lambda x: 'blank' if pd.isna(x) else x)

# 给主数据添加标准化列
main_df['norm_location'] = main_df['Location'].apply(normalize_str)

# 定义匹配逻辑
def get_matched_location(row):
    country = row['Country']
    norm_loc = row['norm_location']
    
    # 优先从映射字典匹配
    if norm_loc in country_maps.get(country, {}):
        matched = country_maps[country][norm_loc]
    else:
        # 无匹配则用国家默认值
        matched = country_defaults.get(country, '')
    
    # 如果原始值和匹配值标准化后一致，Legacy列置空
    if normalize_str(row['Location']) == normalize_str(matched) and not pd.isna(row['Location']):
        row['Legacy Location'] = ''
    return matched

# 生成新的Location列
main_df['Location'] = main_df.apply(get_matched_location, axis=1)

# 删除临时列，调整列顺序与预期输出对齐
main_df.drop('norm_location', axis=1, inplace=True)
main_df = main_df[['Country', 'Employee ID', 'Location', 'Legacy Location']]

方案优势 #

• 分组构建映射字典，匹配效率高，适合大数据量场景；
• 标准化逻辑统一，避免匹配规则混乱；
• 每一步逻辑清晰，后续修改需求时便于调整。

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