基于DataFrame任务角色与日期规则生成Origin Role列的技术问询

前置准备 #

首先确保已安装pandas库，先将日期列转换为可比较的datetime类型，避免字符串比较的误差：

import pandas as pd

# 构造示例数据
data = {
    'Task Start Date': ['01-01-2021', '01-04-2021', '01-04-2021'],
    'Task Finish Date': ['01-03-2021', '02-02-2021', '02-23-2021'],
    'Task Role': ['Lead', 'Team member', 'Unknown']
}
df1 = pd.DataFrame(data)

# 转换日期列为datetime类型
df1['Task Start Date'] = pd.to_datetime(df1['Task Start Date'], format='%d-%m-%Y')
df1['Task Finish Date'] = pd.to_datetime(df1['Task Finish Date'], format='%d-%m-%Y')

实现逻辑 #

1. 非Unknown角色直接赋值：先初始化Origin Role列，将Task Role非Unknown的值直接填充进去。
2. Unknown角色回溯匹配：对Task Role为Unknown的行，筛选出所有Task Finish Date ≤ 当前行Task Start Date的记录，取其中Task Finish Date最大（最新完成）的那条记录的Task Role作为值。

完整代码实现 #

# 初始化Origin Role列
df1['Origin Role'] = df1['Task Role'].copy()

# 处理Task Role为Unknown的行
unknown_rows = df1[df1['Task Role'] == 'Unknown']
for idx, row in unknown_rows.iterrows():
    # 筛选符合条件的记录：Finish Date ≤ 当前行的Start Date
    eligible_records = df1[df1['Task Finish Date'] <= row['Task Start Date']]
    # 取Finish Date最大的记录的Task Role
    if not eligible_records.empty:
        latest_role = eligible_records.sort_values('Task Finish Date', ascending=False).iloc[0]['Task Role']
        df1.loc[idx, 'Origin Role'] = latest_role

# 格式化为原示例的日期字符串格式（可选）
df1['Task Start Date'] = df1['Task Start Date'].dt.strftime('%d-%m-%Y')
df1['Task Finish Date'] = df1['Task Finish Date'].dt.strftime('%d-%m-%Y')

print(df1)

输出结果 #

Task Start Date Task Finish Date    Task Role Origin Role
0       01-01-2021       01-03-2021         Lead        Lead
1       01-04-2021       02-02-2021  Team member Team member
2       01-04-2021       02-23-2021      Unknown        Lead

大数据集优化方案 #

如果数据集较大，iterrows()效率较低，可以改用merge_asof方法提升性能：

# 先准备用于匹配的数据集：筛选非Unknown且按Finish Date排序
match_df = df1[df1['Task Role'] != 'Unknown'].sort_values('Task Finish Date')

# 使用merge_asof进行近似匹配，找小于等于Start Date的最大Finish Date的记录
df1_sorted = df1.sort_values('Task Start Date')
result = pd.merge_asof(
    df1_sorted,
    match_df[['Task Finish Date', 'Task Role']],
    left_on='Task Start Date',
    right_on='Task Finish Date',
    direction='backward'
)

# 合并结果，优先保留原非Unknown的Role
result['Origin Role'] = result['Task Role_x'].where(result['Task Role_x'] != 'Unknown', result['Task Role_y'])
# 恢复原列名和顺序
result = result.rename(columns={'Task Role_x': 'Task Role'}).drop('Task Role_y', axis=1)[df1.columns.tolist() + ['Origin Role']]

# 转换回日期字符串格式
result['Task Start Date'] = result['Task Start Date'].dt.strftime('%d-%m-%Y')
result['Task Finish Date'] = result['Task Finish Date'].dt.strftime('%d-%m-%Y')

print(result)

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