基于Pandas实现多分组含缺失日期聚合数据的滞后滚动平均

示例数据集生成代码 #

import numpy as np
import pandas as pd

np.random.seed(101)

fruit = ['apples', 'apples', 'apples', 'oranges', 'apples', 'oranges', 'oranges',
         'oranges', 'apples', 'oranges', 'apples', 'apples']
people = ['alice']*6+['bob']*6
date = ['2022-01-01', '2022-01-03', '2022-01-04', '2022-01-04', '2022-01-11', '2022-01-11',
         '2022-01-04', '2022-01-05', '2022-01-05', '2022-01-20', '2022-01-20', '2022-01-25']
count = np.random.poisson(4,size=12)
weight_per = np.round(np.random.uniform(1,3,size=12),2)

df = pd.DataFrame({'date':date, 'people':people, 'fruit':fruit,
                   'count':count, 'weight':weight_per*count})
df['date'] = pd.to_datetime(df.date)

生成的DataFrame如下：

date        people  fruit   count   weight
0   2022-01-01  alice   apples  2       2.72
1   2022-01-03  alice   apples  6       11.28
2   2022-01-04  alice   apples  5       13.80
3   2022-01-04  alice   oranges 3       8.70
4   2022-01-11  alice   apples  2       3.92
5   2022-01-11  alice   oranges 3       5.76
6   2022-01-04  bob     oranges 8       18.16
7   2022-01-05  bob     oranges 5       8.25
8   2022-01-05  bob     apples  5       6.20
9   2022-01-20  bob     oranges 4       4.40
10  2022-01-20  bob     apples  2       4.56
11  2022-01-25  bob     apples  2       5.24

核心需求：添加wgt_per_frt_prev_7d列 #

需要新增一列wgt_per_frt_prev_7d，表示过去7天（不含当日）的单果平均权重，计算规则为：所有符合时间范围的水果的weight总和除以count总和。要求实现方法具备良好的时间复杂度，避免暴力遍历。

手动计算示例及预期结果 #

df['wgt_per_frt_prev_7d'] = np.nan

df.loc[1, 'wgt_per_frt_prev_7d'] = 2.72/2 # row 0

df.loc[2, 'wgt_per_frt_prev_7d'] = (2.72+11.28)/(2+6) # row 0 and 1
df.loc[3, 'wgt_per_frt_prev_7d'] = (2.72+11.28)/(2+6)

df.loc[4, 'wgt_per_frt_prev_7d'] = (8.70+13.80+6.20+8.25+18.16)/(3+5+5+5+8) # row 2,3,6,7,8
df.loc[5, 'wgt_per_frt_prev_7d'] = (8.70+13.80+6.20+8.25+18.16)/(3+5+5+5+8)

df.loc[6, 'wgt_per_frt_prev_7d'] = (2.72+11.28)/(2+6) # row 0,1

df.loc[7, 'wgt_per_frt_prev_7d'] = (8.70+13.80+2.72+11.28+18.16)/(3+5+6+2+8) # row 0,1,2,3,6
df.loc[8, 'wgt_per_frt_prev_7d'] = (8.70+13.80+2.72+11.28+18.16)/(3+5+6+2+8)

df.loc[11, 'wgt_per_frt_prev_7d'] = (4.40+4.56)/(2+4) # row 9,10

添加该列后的最终DataFrame：

date        people  fruit   count   weight  wgt_per_frt_prev_7d
0   2022-01-01  alice   apples  2       2.72    NaN
1   2022-01-03  alice   apples  6       11.28   1.360000
2   2022-01-04  alice   apples  5       13.80   1.750000
3   2022-01-04  alice   oranges 3       8.70    1.750000
4   2022-01-11  alice   apples  2       3.92    2.119615
5   2022-01-11  alice   oranges 3       5.76    2.119615
6   2022-01-04  bob     oranges 8       18.16   1.750000
7   2022-01-05  bob     oranges 5       8.25    2.277500
8   2022-01-05  bob     apples  5       6.20    2.277500
9   2022-01-20  bob     oranges 4       4.40    NaN
10  2022-01-20  bob     apples  2       4.56    NaN
11  2022-01-25  bob     apples  2       5.24    1.493333

补充需求：添加wgt_per_apl_prev_7d列 #

需新增一列wgt_per_apl_prev_7d，计算规则为过去7天（不含当日）仅考虑苹果的weight总和除以count总和，该列需应用到所有行（包括橙子行）。

预期输出 #

date        people  fruit   count   weight  wgt_per_frt_prev_7d wgt_per_apl_prev_7d
0   2022-01-01  alice   apples  2       2.72    NaN                NaN
1   2022-01-03  alice   apples  6       11.28   1.360000      1.360000
2   2022-01-04  alice   apples  5       13.80   1.750000      1.750000
3   2022-01-04  alice   oranges 3       8.70    1.750000      1.750000
4   2022-01-11  alice   apples  2       3.92    2.119615      2.000000
5   2022-01-11  alice   oranges 3       5.76    2.119615      2.000000
6   2022-01-04  bob     oranges 8       18.16   1.750000      1.750000
7   2022-01-05  bob     oranges 5       8.25    2.277500      2.138462
8   2022-01-05  bob     apples  5       6.20    2.277500      2.138462
9   2022-01-20  bob     oranges 4       4.40    NaN                NaN
10  2022-01-20  bob     apples  2       4.56    NaN                NaN
11  2022-01-25  bob     apples  2       5.24    1.493333      2.280000

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