基于参考DataFrame匹配员工数据并更新Business字段的实现方案

现有两个Pandas DataFrame：

• employees：存储员工基础信息，其中Business字段存在错误值或空值
• business：按国家分类的业务参考表，包含标准业务名称映射

需要完成以下数据修正操作：

1. 给employees添加Legacy Business列
2. 匹配逻辑：
   • 当employees的Country与Business组合能在business表中找到对应记录时，将employees的Business替换为参考表的Business Name，Legacy Business设为空值
   • 当匹配失败时，将对应国家在business表中的第一条Business Name赋值给employees的Business，同时把原Business值填入Legacy Business（原Business为空则填nan）

输入数据定义 #

import pandas as pd

# 员工信息表
employees = {
    "Country": ["AE", "AE", "CZ", "FR"],
    "ID": [1, 1, 3, 4],
    "Employee": ["John", "John", "Paulo", "Peter"],
    "Valid": ["ok", "ok", "ok", "ok"],
    "Business": ["ADMIN", "*", "", "PATHO"],
}
df_employees = pd.DataFrame(employees)

# 业务参考表
business = {
    "Country": ["AE", "AE", "AE", "CH", "CH", "CH", "CZ", "CZ", "DK", "ES", "ES", "FR", "FR"],
    "Business": ["ADMIN", "COVID 19", "LAB IVD", "ADMIN", "COVID 19", "GENETICS", "PATHO", "ADMIN", "PHARMA", "ADMIN", "COVID 19", "LAB IVD", "PATHO"],
    "Business Name": ["AE ADMIN", "AE COVID 19", "AE LAB IVD", "CH ADMIN", "CH COVID 19", "CH GENETICS", "CZ PATHO", "DK ADMIN", "DK PHARMA", "ES ADMIN", "ES COVID 19", "FR LAB IVD", "FR PATHO"],
}
df_business = pd.DataFrame(business)

实现代码 #

# 1. 构建(国家,业务)到标准业务名称的映射字典，用于快速匹配
business_map = df_business.set_index(["Country", "Business"])["Business Name"].to_dict()

# 2. 提取每个国家的首个标准业务名称，作为匹配失败时的默认值
country_default = df_business.groupby("Country")["Business Name"].first().to_dict()

# 3. 初始化Legacy Business列，将空字符串转为缺失值
df_employees["Legacy Business"] = df_employees["Business"].replace({"": pd.NA})

# 4. 定义逐行处理的匹配逻辑
def process_business(row):
    match_key = (row["Country"], row["Business"])
    if match_key in business_map:
        # 匹配成功：替换为标准名称，清空Legacy字段
        return pd.Series([business_map[match_key], pd.NA])
    else:
        # 匹配失败：使用国家默认名称，保留原业务值到Legacy字段
        return pd.Series([country_default[row["Country"]], row["Legacy Business"]])

# 应用逻辑并更新字段
df_employees[["Business", "Legacy Business"]] = df_employees.apply(process_business, axis=1)

# 查看最终结果
print(df_employees)

输出结果 #

Country  ID Employee Valid     Business Legacy Business
0      AE   1     John    ok     AE ADMIN            <NA>
1      AE   1     John    ok  AE COVID 19               *
2      CZ   3    Paulo    ok     CZ PATHO            <NA>
3      FR   4    Peter    ok     FR PATHO            <NA>

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