如何用groupby结合时间序列计算周度销量差值？

原始DataFrame #

import pandas as pd

lst_1 = ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B']
lst_2 = [500, 600, 800, 900,700, 800,1000, 1200]
lst_3 = ['10/31/2022', '11/02/2022','11/07/2022', '11/14/2022', '10/31/2022', '11/02/2022','11/07/2022', '11/14/2022']
df1 = pd.DataFrame(list(zip(lst_1 , lst_2, lst_3)),
              columns =['SKU', 'Sum_Qty_Sold', 'Date_Updated'])

期望结果 #

lst_1 = ['A', 'A', 'B', 'B']
lst_2 = [300, 100, 300, 200]
lst_3 = ['10/31/2022-11/07/2022', '11/07/2022-11/14/2022', '10/31/2022-11/07/2022', '11/07/2022-11/14/2022']

result = pd.DataFrame(list(zip(lst_1 , lst_2, lst_3)),
              columns =['SKU', 'Qty_Sold_By_Week', 'Time_Series'])

计算公式 #

Qty_Sold_By_Week = -(当前Date_Updated对应的Sum_Qty_Sold - 7天后的Sum_Qty_Sold（若存在）)

解决方案 #

按以下步骤实现：

1. 转换日期格式：将字符串类型的日期转为datetime类型，方便时间运算
2. 分组处理每个SKU：对每个SKU的记录按日期排序，计算每个日期加7天后的日期，再匹配组内对应的7天后销量数据
3. 计算周期销量并生成时间区间：根据公式计算差值，同时拼接日期区间字符串
4. 整理结果格式：保留需要的列并重置索引

完整代码：

import pandas as pd

# 原始数据
lst_1 = ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B']
lst_2 = [500, 600, 800, 900,700, 800,1000, 1200]
lst_3 = ['10/31/2022', '11/02/2022','11/07/2022', '11/14/2022', '10/31/2022', '11/02/2022','11/07/2022', '11/14/2022']
df1 = pd.DataFrame(list(zip(lst_1 , lst_2, lst_3)),
              columns =['SKU', 'Sum_Qty_Sold', 'Date_Updated'])

# 转换日期为datetime类型
df1['Date_Updated'] = pd.to_datetime(df1['Date_Updated'], format='%m/%d/%Y')

def calculate_weekly_sales(group):
    # 按日期排序
    group_sorted = group.sort_values('Date_Updated')
    # 计算每个日期+7天后的日期
    group_sorted['7days_later'] = group_sorted['Date_Updated'] + pd.Timedelta(days=7)
    # 合并自身，找到7天后对应的销量记录
    merged = group_sorted.merge(
        group_sorted,
        left_on=['SKU', '7days_later'],
        right_on=['SKU', 'Date_Updated'],
        suffixes=('', '_7d')
    )
    # 按公式计算周期销量
    merged['Qty_Sold_By_Week'] = -(merged['Sum_Qty_Sold'] - merged['Sum_Qty_Sold_7d'])
    # 生成时间区间字符串
    merged['Time_Series'] = merged['Date_Updated'].dt.strftime('%m/%d/%Y') + '-' + merged['Date_Updated_7d'].dt.strftime('%m/%d/%Y')
    # 返回需要的列
    return merged[['SKU', 'Qty_Sold_By_Week', 'Time_Series']]

# 分组计算并整理结果
result = df1.groupby('SKU', group_keys=False).apply(calculate_weekly_sales).reset_index(drop=True)

print(result)

运行后输出的结果与期望一致：

SKU  Qty_Sold_By_Week           Time_Series
0   A               300  10/31/2022-11/07/2022
1   A               100  11/07/2022-11/14/2022
2   B               300  10/31/2022-11/07/2022
3   B               200  11/07/2022-11/14/2022

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