Pandas按客户分组逐行计算近6个月累计销售额

数据处理需求：按客户维度统计近6个月营业额，已计算得到每行记录的6个月窗口起始日期last_month（即fromDate往前推6个月），需要逐行统计对应客户在[last_month, fromDate]区间内的采购销售总额，生成total_sales字段。

初始DataFrame结构 #

customerId   fromDate  sales
0           1 2022-06-01    100
1           1 2022-05-25     20
2           1 2022-05-25     50
3           1 2022-05-20     30
4           1 2021-09-05     40
5           2 2022-06-02     80
6           3 2021-03-01     50
7           3 2021-02-01     20

现有生成中间结果的代码 #

import pandas as pd
from dateutil.relativedelta import relativedelta

d = {'customerId': [1, 1, 1, 1, 1, 2, 3, 3],
     'fromDate': ["2022-06-01", "2022-05-25", "2022-05-25", "2022-05-20", "2021-09-05",
                  "2022-06-02", "2021-03-01", "2021-02-01"],
     'sales': [100, 20, 50, 30, 40, 80, 50, 20]
    }
df = pd.DataFrame(data=d)
df['fromDate'] = pd.to_datetime(df['fromDate'], errors='coerce')

def find_last_date(date_val):
  six_months = date_val + relativedelta(months=-6)
  return six_months

df['last_month'] = df['fromDate'].apply(lambda x: find_last_date(x))

运行后得到的中间结果：

customerId   fromDate  sales last_month 
0           1 2022-06-01    100 2022-03-01        
1           1 2022-05-25     20 2022-02-25        
2           1 2022-05-25     50 2022-02-25        
3           1 2022-05-20     30 2022-02-20        
4           1 2021-09-05     40 2021-06-05        
5           2 2022-06-02     80 2022-03-02        
6           3 2021-03-01     50 2020-12-01        
7           3 2021-02-01     20 2020-11-01        

期望输出 #

新增total_sales字段存储窗口累计销售额，结果如下：

customerId   fromDate  sales last_month total_sales
0           1 2022-06-01    100 2022-03-01        200 # 100 + 20 + 50 + 30
1           1 2022-05-25     20 2022-02-25        100 # 20 + 50 + 30
2           1 2022-05-25     50 2022-02-25        100 # 50 + 20 + 30
3           1 2022-05-20     30 2022-02-20        30  # 30
4           1 2021-09-05     40 2021-06-05        40  # 40
5           2 2022-06-02     80 2022-03-02        80  # 80
6           3 2021-03-01     50 2020-12-01        70  # 50 + 20
7           3 2021-02-01     20 2020-11-01        20  # 20

在现有代码后追加以下逻辑即可：

def calc_cum_sales(customer_group):
    # 逐行计算单个客户组内各时间窗口的销售总和
    for idx, row in customer_group.iterrows():
        # 筛选日期落在当前行窗口内的记录
        date_mask = (customer_group['fromDate'] >= row['last_month']) & (customer_group['fromDate'] <= row['fromDate'])
        customer_group.loc[idx, 'total_sales'] = customer_group.loc[date_mask, 'sales'].sum()
    return customer_group

# 按客户分组执行计算
df = df.groupby('customerId', group_keys=False).apply(calc_cum_sales)
# 字段类型转为整数
df['total_sales'] = df['total_sales'].astype(int)

逻辑说明 #

• 按customerId分组，确保所有计算只在同一客户的交易记录范围内进行，不会跨客户统计
• 遍历组内每一行，通过布尔索引筛选出交易日期在[last_month, fromDate]闭区间内的记录
• 对筛选结果的sales求和，赋值给当前行的total_sales
• 十万行以内规模的数据集用该方法运行效率足够，数据量更大时可以先对组内日期排序，用时间索引的滚动窗口优化计算速度，避免逐行遍历。

运行后输出的结果和期望完全一致。

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