基于Pandas映射表替换DataFrame列值的技术实现需求

需求说明 #

需要对员工合同脏数据df1，基于LOV映射表df2实现列值替换，规则如下：

• 按**国家（Country）+ 字段（Field）**匹配映射规则
• 若df1中对应字段的值存在于df2的Values列，则替换为对应的Code值
• 无对应映射规则或值不在映射范围内时，保留原数值

输入数据（df1） #

ID  Country  Name     Job        Date         Grade
1   CZ       John     Office     2021-01-01   Senior
1   SK       John     .          2021-01-01   Assistant
2   AE       Peter    Carpinter  2000-05-03 
3   PE       Marcia   Cleaner    1989-11-11   ERROR!
3   FR       Marcia   Assistant  1978-01-05   High
3   FR       Marcia              1999-01-01   Senior

LOV映射表（df2） #

Country Field   Values      Code
US      Job     Back        BA
US      Job     Front       FR
US      Job     Office      OFF
CZ      Job     Office      CZ_OFF
CZ      Job     Field       CZ_Fil
SK      Job     All         ALL
FR      Job     Assistant   AST
AE      Job     Carpinter   CAR
AE      Job     Carpinter   CAR
CZ      Grade   Senior      S
CZ      Grade   Junior      J
SK      Grade   M1          M1
FR      Grade   Low         L
FR      Grade   Mid         M1
FR      Grade   High        H

预期输出 #

ID  Country     Name    Job     Date        Grade
1   CZ          John    CZ_OFF  2021-01-01  S
1   SK          John    .       2021-01-01  M1
2   AE          Peter   CAR     2000-05-03  
3   PE          Marcia  Cleaner 1989-11-11  ERROR!
3   FR          Marcia  AST     1978-01-05  H
3   FR          Marcia          1999-01-01  Senior

实现方案（Python Pandas） #

步骤说明 #

1. 清理映射表：先去除df2中重复的映射规则行，避免同一规则多次匹配导致冲突
2. 构建映射字典：将映射表转换为嵌套字典结构{Country: {Field: {Values: Code}}}，提升匹配效率
3. 批量替换字段：遍历需要映射的字段（如Job、Grade），逐行匹配规则并替换值

代码实现 #

import pandas as pd

# 构造输入数据df1
df1 = pd.DataFrame({
    'ID': [1, 1, 2, 3, 3, 3],
    'Country': ['CZ', 'SK', 'AE', 'PE', 'FR', 'FR'],
    'Name': ['John', 'John', 'Peter', 'Marcia', 'Marcia', 'Marcia'],
    'Job': ['Office', '.', 'Carpinter', 'Cleaner', 'Assistant', ''],
    'Date': ['2021-01-01', '2021-01-01', '2000-05-03', '1989-11-11', '1978-01-05', '1999-01-01'],
    'Grade': ['Senior', 'Assistant', '', 'ERROR!', 'High', 'Senior']
})

# 构造LOV映射表df2
df2 = pd.DataFrame({
    'Country': ['US', 'US', 'US', 'CZ', 'CZ', 'SK', 'FR', 'AE', 'AE', 'CZ', 'CZ', 'SK', 'FR', 'FR', 'FR'],
    'Field': ['Job', 'Job', 'Job', 'Job', 'Job', 'Job', 'Job', 'Job', 'Job', 'Grade', 'Grade', 'Grade', 'Grade', 'Grade', 'Grade'],
    'Values': ['Back', 'Front', 'Office', 'Office', 'Field', 'All', 'Assistant', 'Carpinter', 'Carpinter', 'Senior', 'Junior', 'M1', 'Low', 'Mid', 'High'],
    'Code': ['BA', 'FR', 'OFF', 'CZ_OFF', 'CZ_Fil', 'ALL', 'AST', 'CAR', 'CAR', 'S', 'J', 'M1', 'L', 'M1', 'H']
})

# 1. 清理映射表：去除重复的规则行
df2_clean = df2.drop_duplicates(subset=['Country', 'Field', 'Values'])

# 2. 构建嵌套映射字典
mapping_dict = df2_clean.groupby(['Country', 'Field'])\
                        .apply(lambda g: dict(zip(g['Values'], g['Code'])))\
                        .unstack().to_dict(orient='index')

# 3. 定义替换函数
def map_field_value(row, field):
    country = row['Country']
    # 检查当前国家和字段是否有映射规则
    if country in mapping_dict and field in mapping_dict[country]:
        value_map = mapping_dict[country][field]
        # 匹配到则替换，否则返回原值
        return value_map.get(row[field], row[field])
    # 无规则则返回原值
    return row[field]

# 4. 对目标字段执行替换
target_fields = ['Job', 'Grade']
for field in target_fields:
    df1[field] = df1.apply(lambda row: map_field_value(row, field), axis=1)

# 打印结果
print(df1.to_string(index=False))

方案优势 #

• 映射字典的结构让规则查找更高效，避免重复遍历整个映射表
• 去重步骤保证了映射规则的唯一性，不会出现同一值对应多个编码的冲突
• 替换逻辑清晰，严格遵循“有规则则替换，无规则则保留”的需求

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