请求基于起止周的Python Pandas任务分配解决方案

修正后的输入数据 #

原输入字典存在语法错误，以下是修正后的可运行代码：

import pandas as pd

dic = {
    'Test ID': ['100001','100002','100003','100004','100005','100006','100007','100008','100009','100010','100011','100012','100013','100014','100015'],
    'Wanted start': ['24W01','24W06','24W01','24W04','24W01','24W03','24W07','24W09','24W01','24W06','24W01','24W04','24W01','24W03','24W07'],
    'Wanted completed': ['24W05','22W10','24W03','22W10','24W02','24W06','24W08','24W10','24W05','22W11','24W03','24W10','24W02','22W06','24W10']
}
df = pd.DataFrame(dic)

需求说明 #

需将每个测试ID按其Wanted start和Wanted completed的周范围，分配到对应周列中：输出表格以测试ID为行，所有涉及的周为列，任务覆盖的周填入对应测试ID，未覆盖的周留空。

解决方案代码 #

1. 周格式转换工具函数 #

将YWXX格式的周字符串转为可计算的数值，同时支持反向转换：

def week_to_num(week_str):
    year, week = week_str.split('W')
    return int(year) * 100 + int(week)

def num_to_week(num):
    year = num // 100
    week = num % 100
    return f"{year}W{week:02d}"

2. 生成周范围与列名 #

提取所有涉及的周，整理为有序的列名：

# 转换起止周为数值，便于计算范围
df['start_num'] = df['Wanted start'].apply(week_to_num)
df['end_num'] = df['Wanted completed'].apply(week_to_num)

# 收集所有涉及的周并排序
all_week_nums = sorted(set(df['start_num'].tolist() + df['end_num'].tolist()))
week_columns = [num_to_week(num) for num in all_week_nums]

3. 构建任务分配表格 #

初始化结果表并填充每个测试ID的覆盖周：

# 初始化结果DataFrame
result_df = pd.DataFrame(index=df['Test ID'], columns=week_columns)

# 遍历每个测试ID，填充对应周的内容
for idx, row in df.iterrows():
    test_id = row['Test ID']
    # 生成当前测试ID覆盖的所有周
    covered_nums = range(row['start_num'], row['end_num'] + 1)
    covered_weeks = [num_to_week(num) for num in covered_nums]
    # 填充到对应列
    result_df.loc[test_id, covered_weeks] = test_id

# 空值替换为空字符串
result_df = result_df.fillna('')

示例输出（部分） #

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