Pandas - 计算过去 x 天数的值频率

我发现了一些意想不到的结果。我想做的是创建一个列来查看 ID 号和日期，并计算该 ID 号在过去 7 天内出现的次数（我还想让该列动态化 x 数量）天，但只是尝试 7 天）。

所以给定这个数据框：

import pandas as pd



df = pd.DataFrame(
        [['A', '2020-02-02 20:31:00'],
        ['A', '2020-02-03 00:52:00'],
        ['A', '2020-02-07 23:45:00'],
        ['A', '2020-02-08 13:19:00'],
        ['A', '2020-02-18 13:16:00'],
        ['A', '2020-02-27 12:16:00'],
        ['A', '2020-02-28 12:16:00'],
        ['B', '2020-02-07 18:57:00'],
        ['B', '2020-02-07 21:50:00'],
        ['B', '2020-02-12 19:03:00'],
        ['C', '2020-02-01 13:50:00'],
        ['C', '2020-02-11 15:50:00'],
        ['C', '2020-02-21 10:50:00']],
        columns = ['ID', 'Date'])

用于计算每个实例在过去 7 天内出现次数的代码：

df['Date'] = pd.to_datetime(df['Date'])

delta = 7
df['count_in_last_%s_days' %(delta)] = df.groupby(['ID', pd.Grouper(freq='%sD' %delta, key='Date')]).cumcount()

Output:

   ID                Date  count_in_last_7_days
0   A 2020-02-02 20:31:00                     0
1   A 2020-02-03 00:52:00                     1
2   A 2020-02-07 23:45:00                     2
3   A 2020-02-08 13:19:00                     0 #<---- This should output 3
4   A 2020-02-18 13:16:00                     0
5   A 2020-02-27 12:16:00                     0
6   A 2020-02-28 12:16:00                     1
7   B 2020-02-07 18:57:00                     0
8   B 2020-02-07 21:50:00                     1
9   B 2020-02-12 19:03:00                     0 #<---- THIS SHOULD OUTPUT 2
10  C 2020-02-01 13:50:00                     0
11  C 2020-02-11 15:50:00                     0
12  C 2020-02-21 10:50:00                     0

你不想使用Grouper on Date but a rolling窗户。石斑鱼会将数据帧分割成所需持续时间的单独连续块。由于您希望每次约会后有 7 天，因此这就是rolling:

delta = 7
df['count_in_last_%s_days' %(delta)] = df.assign(count=1).groupby(
    ['ID']).apply(lambda x: x.rolling('%sD' %delta, on='Date').sum(
        ))['count'].astype(int) - 1

它按预期给出：

   ID                Date  count_in_last_7_days
0   A 2020-02-02 20:31:00                     0
1   A 2020-02-03 00:52:00                     1
2   A 2020-02-07 23:45:00                     2
3   A 2020-02-08 13:19:00                     3
4   A 2020-02-18 13:16:00                     0
5   A 2020-02-27 12:16:00                     0
6   A 2020-02-28 12:16:00                     1
7   B 2020-02-07 18:57:00                     0
8   B 2020-02-07 21:50:00                     1
9   B 2020-02-12 19:03:00                     2
10  C 2020-02-01 13:50:00                     0
11  C 2020-02-11 15:50:00                     0
12  C 2020-02-21 10:50:00                     0