如何基于重复事件生成按周划分的ID行动序列？

原始数据与需求 #

原始数据表格：

ID          TARGET_ACTION              TARGET_DATE 

366                    0                21.04.2021      
186                    1                03.04.2021
929                    0                14.04.2021
366                    1                17.04.2021               

需求说明：每个ID会重复出现，对应不同日期有不同的TARGET_ACTION值。需要按每月7天为一个区间划分日期，将数据转换为宽表格式——每个ID一行，每周区间作为列，单元格填充对应区间内的TARGET_ACTION值。

实现方案 #

方法1：SQL（以MySQL为例） #

先将日期转换为标准格式，计算每个日期所属的7天区间，再通过条件聚合生成宽表：

SELECT
    ID,
    MAX(CASE WHEN week_interval = '01.04.2021-07.04.2021' THEN TARGET_ACTION ELSE 0 END) AS `01.04.2021-07.04.2021`,
    MAX(CASE WHEN week_interval = '08.04.2021-14.04.2021' THEN TARGET_ACTION ELSE 0 END) AS `08.04.2021-14.04.2021`,
    MAX(CASE WHEN week_interval = '15.04.2021-21.04.2021' THEN TARGET_ACTION ELSE 0 END) AS `15.04.2021-21.04.2021`
FROM (
    SELECT
        ID,
        TARGET_ACTION,
        CONCAT(
            DATE_FORMAT(DATE_ADD(STR_TO_DATE(TARGET_DATE, '%d.%m.%Y'), INTERVAL -(DAY(STR_TO_DATE(TARGET_DATE, '%d.%m.%Y'))-1) DIV 7 *7 DAY), '%d.%m.%Y'),
            '-',
            DATE_FORMAT(DATE_ADD(STR_TO_DATE(TARGET_DATE, '%d.%m.%Y'), INTERVAL 6 - (DAY(STR_TO_DATE(TARGET_DATE, '%d.%m.%Y'))-1) %7 DAY), '%d.%m.%Y')
        ) AS week_interval
    FROM your_table
) AS weekly_data
GROUP BY ID;

• 若需要动态生成所有存在的周区间（而非手动指定），可使用动态SQL拼接列名；
• 若需延续ID最近的TARGET_ACTION值填充空区间，需额外用窗口函数做缺失值填充后再透视。

方法2：Python Pandas #

通过日期预处理+透视表实现：

1. 数据预处理

import pandas as pd

# 加载数据（实际场景可替换为pd.read_csv等读取方式）
data = pd.DataFrame({
    'ID': [366, 186, 929, 366],
    'TARGET_ACTION': [0, 1, 0, 1],
    'TARGET_DATE': ['21.04.2021', '03.04.2021', '14.04.2021', '17.04.2021']
})

# 转换日期格式
data['TARGET_DATE'] = pd.to_datetime(data['TARGET_DATE'], format='%d.%m.%Y')

# 生成7天区间字符串
data['week_start'] = data['TARGET_DATE'] - pd.to_timedelta(data['TARGET_DATE'].dt.day -1, unit='D') + pd.to_timedelta(((data['TARGET_DATE'].dt.day -1) //7)*7, unit='D')
data['week_end'] = data['week_start'] + pd.Timedelta(days=6)
data['week_interval'] = data['week_start'].dt.strftime('%d.%m.%Y') + '-' + data['week_end'].dt.strftime('%d.%m.%Y')

2. 生成目标宽表

# 透视表：按ID分组，周区间为列，取区间内ACTION值（若一周多值可改用last()取最后一条）
pivot_df = data.pivot_table(
    index='ID', 
    columns='week_interval', 
    values='TARGET_ACTION', 
    aggfunc='max', 
    fill_value=0
).reset_index()

# 按周区间顺序排列列
pivot_df = pivot_df.reindex(columns=['ID'] + sorted(pivot_df.columns[1:]))

# 输出结果
print(pivot_df.to_string(index=False))

输出结果将与示例格式一致，若需填充最近的TARGET_ACTION值而非0，可先对每个ID按日期排序，用ffill()填充缺失值后再透视。

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