基于日期匹配合并Pandas DataFrame，映射协作人员累计工作量

原始数据 #

第一个DataFrame（负责人累计工作记录） #

import pandas as pd

df1 = pd.DataFrame({
    'Work_unit': ['unit1', 'unit2', 'unit3', 'unit4', 'unit5'],
    'Date': ['11/12/2017', '12/14/2018', '12/13/2019', '1/1/2019', '12/13/2019'],
    'leader_name': ['Bob', 'David', 'Ada', 'Bob', 'Ada'],
    'Cum_work': [2, 1, 3, 3, 4]
})
# 转换日期列为datetime类型，便于后续比较
df1['Date'] = pd.to_datetime(df1['Date'], format='%m/%d/%Y')

第二个DataFrame（协作任务记录） #

df2 = pd.DataFrame({
    'Work_unit': ['unit2', 'unit2', 'unit3', 'unit3', 'unit4', 'unit4'],
    'Date': ['12/13/2018', '12/30/2018', '1/10/2019', '2/3/2019', '12/30/2020', '12/31/2020'],
    'collaborators': ['Bob', 'Ada', 'Bob', 'Casey', 'Ada', 'David']
})
# 转换日期列为datetime类型
df2['Date'] = pd.to_datetime(df2['Date'], format='%m/%d/%Y')

需求说明 #

将df1中负责人的累计工作数，按协作日期前该人员完成的最大累计工作数映射到df2的对应协作人员，无记录则填充0，最终得到包含Cumworks_sofar列的合并结果。

实现步骤 #

1. 预处理排序：对两个DataFrame分别按人员、日期排序，满足后续匹配的格式要求

# 按负责人分组后按日期升序排列，确保累计工作数随时间递增
df1_sorted = df1.sort_values(by=['leader_name', 'Date'])
# 按协作人员分组后按日期升序排列
df2_sorted = df2.sort_values(by=['collaborators', 'Date'])

2. 按日期匹配数据：使用merge_asof实现"取协作日期前最近有效累计数"的匹配逻辑，该方法会自动匹配左表中不大于当前日期的最近记录

merged = pd.merge_asof(
    df2_sorted,
    df1_sorted,
    left_on='Date',
    right_on='Date',
    left_by='collaborators',
    right_by='leader_name',
    direction='backward'  # 指定取小于等于当前日期的最近记录
)

3. 整理最终结果：填充无匹配记录的数值为0，重命名并保留需要的列，恢复原始顺序

# 缺失值填充为0，转换为整数类型
merged['Cumworks_sofar'] = merged['Cum_work'].fillna(0).astype(int)

# 保留目标列并重命名，恢复原始df2的行顺序
final_result = merged[['Work_unit_x', 'Date', 'collaborators', 'Cumworks_sofar']].rename(
    columns={'Work_unit_x': 'Work_unit'}
).loc[df2.index]

最终输出 #

运行代码后得到的结果与期望一致：

Work_unit       Date collaborators  Cumworks_sofar
0     unit2 2018-12-13           Bob               2
1     unit2 2018-12-30           Ada               3
2     unit3 2019-01-10           Bob               3
3     unit3 2019-02-03         Casey               0
4     unit4 2020-12-30           Ada               4
5     unit4 2020-12-31         David               1

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