主页 / coding / 问题 / 77606396
Accepted
Serge de Gosson de Varennes
Asked: 2023-12-05 21:01:54 +0800 CST

计算多个 ID 每个时间间隔的累积列中的事件数

  • 772

假设我有一个像这样的数据框

import pandas as pd

data = {
    'id1': [1]*12 + [1]*12 + [2]*12 + [2]*12,
    'id2': ['a']*12 + ['b']*12 + ['c']*12 + ['d']*12,
    'date': (['2023-11-20', '2023-11-21', '2023-11-22', '2023-11-23', '2023-11-24', '2023-11-25', '2023-11-26', '2023-11-27', '2023-11-28', '2023-11-29', '2023-11-30', '2023-12-01']*4)[:48],
    'event1': [10, 23, 36, 49, 62, 75, 88, 101, 114, 127, 140, 153, 12, 25, 38, 51, 64, 77, 90, 103, 116, 129, 142, 155, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 567, 785, 1003, 1221, 1439, 1657, 1875, 2093, 2311, 2529, 2747, 2965],
    'event2': ([0]*12 + [3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25] + [45, 50, 55, 60, 65, 70, 75, 80, 85, 90, 95, 100, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]*2)[:48]
}

df = pd.DataFrame(data)

print(df)

正如您所看到的，对于每个 和的id2值都是累积的。也就是说，两个连续日期之间的值差是两个日期中较大日期的事件计数。event1event2

我想要为每个id2计算过去一天（此处为 2023-11-30）的事件数、过去三天的事件总数和过去 7 天的事件总数。

我尝试过以下类型的东西：

df.set_index('date', inplace=True)

target_date = pd.to_datetime('2023-11-30')

events_on_target_date = df.loc[target_date, 'event1'] - df.loc[target_date - pd.DateOffset(days=1), 'event1']
events_3_days_before = df.loc[target_date - pd.DateOffset(days=3):target_date, 'event1'].iloc[-1] - df.loc[target_date - pd.DateOffset(days=4), 'event1']
events_5_days_before = df.loc[target_date - pd.DateOffset(days=5):target_date, 'event1'].iloc[-1] - df.loc[target_date - pd.DateOffset(days=6), 'event1']

print(f"Number of events on {target_date}: {events_on_target_date}")
print(f"Total number of events in the 3 days before {target_date}: {events_3_days_before}")
print(f"Total number of events in the 5 days before {target_date}: {events_5_days_before}")

它适用于一个独立的 id1 和 id2，但我遇到了困难：

  • 将其应用到每个id2
  • 应用此包括计算event1和event2

期望的结果应该是这样的

id1 id2 count_event1_yesterday  count_event1_past3Days  count_event1_past5Days  count_event2_yesterday  count_event2_past3Days  count_event2_past5Days
1   a   13  39  52  0   0   0
1   b   13  39  52  4   6   8
2   c                       
2   d
pandas

2 个回答

  1. 代码

    分组依据 + 差异 + 最后

    grp = [df['id1'], df['id2']]
g = df.groupby(grp)[['event1', 'event2']]
out = g.diff().groupby(grp).last().add_prefix('count_').add_suffix('_yesterday')\
       .join(g.diff(3).groupby(grp).last().add_prefix('count_').add_suffix('_past3Days'))\
       .join(g.diff(5).groupby(grp).last().add_prefix('count_').add_suffix('_past5Days'))\
       .sort_index(axis=1, key=lambda x: x.str.split('_').str[1]).reset_index()

    出去

    在此输入图像描述

    • 2
  2. Best Answer

    您可以创建自定义聚合函数并按两者进行分组idX：

    def count(df, target_date, delta):
    diff = target_date - df.index
    return df[(delta >= diff) & (diff >= '0D')].diff().sum()

grp = df.set_index('date').groupby(['id1', 'id2'])[['event1', 'event2']]

params = {'yesterday': '1D', 'past3Days': '3D', 'past5Days': '5D'}
out = pd.concat([grp.agg(count, target_date, delta).add_suffix(f'_{suffix}')
                 for suffix, delta in params.items()],
                axis=1).add_prefix('count_').astype(int).reset_index()

    输出：

    >>> out
   id1 id2  count_event1_yesterday  count_event2_yesterday  count_event1_past3Days  count_event2_past3Days  count_event1_past5Days  count_event2_past5Days
0    1   a                      13                       0                      39                       0                      65                       0
1    1   b                      13                       2                      39                       6                      65                      10
2    2   c                       2                       5                       6                      15                      10                      25
3    2   d                     218                       1                     654                       3                    1090                       5
    • 1

相关问题