如何按逗号分隔ID与日期字段合并两个DataFrame
Pandas 逗号分隔多值键的DataFrame关联实现
针对df1的EmployeeID为逗号分隔多值、需与df2单个EmployeeID做匹配,同时校验workDate相等的关联场景,通过拆分展开多值列生成临时关联键即可实现,全程保留df1原始的逗号分隔EmployeeID字段。
步骤1:构造测试数据集
import pandas as pd # 构造给定的df1 df1 = pd.DataFrame({ "Employee Name": ["John", "John", "Kim"], "EmployeeID": ["2,22", "2,22", "3"], "workDate": ["2020-11-01", "2020-11-02", "2020-11-01"] }) # 构造给定的df2 df2 = pd.DataFrame({ "EmployeeID": ["2", "22", "3"], "workDate": ["2020-11-01", "2020-11-02", "2020-11-01"], "Hours": [8, 2, 10] })
步骤2:拆分df1的多值列生成临时关联键
- 按逗号拆分
EmployeeID列为列表格式 - 用
explode将列表展开为独立行,全程保留df1原有的所有字段(包括原始的逗号分隔EmployeeID列)
# 拆分多值ID为列表 df1["merge_id"] = df1["EmployeeID"].str.split(",") # 展开列表为独立行 df1_exploded = df1.explode("merge_id")
步骤3:双主键关联
使用拆分后的临时merge_id和workDate作为联合关联键,与df2做内连接,仅保留两边匹配的记录:
merged = df1_exploded.merge( df2, left_on=["merge_id", "workDate"], right_on=["EmployeeID", "workDate"], how="inner" )
步骤4:清洗结果列匹配预期格式
- 删除临时关联列
merge_id - 合并后同名列会自动添加
_x(来自df1)、_y(来自df2)后缀,删除df2侧重复的EmployeeID_y,将df1侧的EmployeeID_x重命名回原列名EmployeeID - 调整列顺序与预期输出对齐
result = merged.drop(columns=["merge_id", "EmployeeID_y"]) # 还原原始列名 result = result.rename(columns={"EmployeeID_x": "EmployeeID"}) # 调整列顺序 result = result[["Employee Name", "EmployeeID", "workDate", "Hours"]]
注:该方案使用Pandas内置的merge逻辑实现关联,相比逐行遍历做字符串匹配的自定义方法,大数据量下运行效率更高,且不会丢失df1原始的逗号分隔EmployeeID字段。
最终输出结果
打印result后得到的内容与预期完全一致:
| Employee Name | EmployeeID | workDate | Hours |
|---|---|---|---|
| John | 2,22 | 2020-11-01 | 8 |
| John | 2,22 | 2020-11-02 | 2 |
| Kim | 3 | 2020-11-01 | 10 |
内容的提问来源于stack exchange,提问作者programming_ocd
相关产品推荐
相关产品推荐

