使用Pandas按自定义周维度计算多ID的value列周求和

原始数据集 #

需求说明 #

给定起始日期（示例：2022-01-01），自定义周范围为每周六00:00至下周五23:59（示例：第1周为2022-01-01 00:00至2022-01-07 23:59），计算每个ID在各周的value总和，输出如下格式的表格：

解决方案 #

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

通过计算每条记录所属的周数，再用条件聚合实现行转列：

-- 设置起始日期
SET @start_date = '2022-01-01';

WITH weekly_data AS (
    SELECT 
        ID,
        value,
        -- 计算当前记录所属周数：天数差除以7取整后加1
        FLOOR(DATEDIFF(date, @start_date) / 7) + 1 AS week_num
    FROM your_table
)
SELECT 
    ID,
    -- 对每一周的value求和，无数据则显示'--'
    IFNULL(CAST(SUM(CASE WHEN week_num = 1 THEN value END) AS CHAR), '--') AS `Week 1 sum`,
    IFNULL(CAST(SUM(CASE WHEN week_num = 2 THEN value END) AS CHAR), '--') AS `Week 2 sum`,
    IFNULL(CAST(SUM(CASE WHEN week_num = 3 THEN value END) AS CHAR), '--') AS `Week 3 sum`
    -- 可根据实际数据继续扩展更多周的计算
FROM weekly_data
GROUP BY ID
ORDER BY ID;

方法2：Python（使用Pandas） #

利用Pandas的日期处理和透视表功能快速实现：

import pandas as pd

# 加载原始数据（实际使用时可替换为读取文件）
data = {
    'ID': [1,2,1,1,2,3,1],
    'value': [20,25,10,150,200,40,75],
    'date': ['2022-01-01 12:20', '2022-01-04 18:20', '2022-01-04 11:20',
             '2022-01-06 16:20', '2022-01-08 13:20', '2022-01-04 21:20',
             '2022-01-09 08:20']
}
df = pd.DataFrame(data)
df['date'] = pd.to_datetime(df['date'])

# 定义起始日期
start_date = pd.to_datetime('2022-01-01')

# 计算每条记录所属周数
df['week_num'] = ((df['date'] - start_date).dt.days // 7) + 1

# 生成透视表，计算各ID各周总和，空值替换为'--'
pivot_result = df.pivot_table(
    index='ID',
    columns='week_num',
    values='value',
    aggfunc='sum'
).fillna('--')

# 重命名列名
pivot_result.columns = [f'Week {col} sum' for col in pivot_result.columns]

# 重置索引并排序
final_result = pivot_result.reset_index().sort_values('ID')

print(final_result)

执行后输出结果：

ID Week 1 sum Week 2 sum
0   1        180         75
1   2         25        200
2   3         40         --

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