如何结合groupby与cumcount按ID分别统计w和l的累计计数

给定如下DataFrame：

import pandas as pd

df = pd.DataFrame({
    'id': ['a', 'a', 'a', 'a', 'a', 'a', 'a', 'b', 'b', 'b', 'b'],
    'value': ['w', 'l', 'l', 'w', 'w', 'w', 'l', 'l', 'w', 'l', 'w'],
    'Date': ['2022-09-30', '2022-08-01', '2022-07-15', '2022-07-11', '2022-06-12', '2022-06-11', '2022-06-10', '2022-07-18', '2022-07-14', '2022-07-11', '2022-07-01']
})

直接使用df.groupby("id").cumcount(ascending=False)只能得到按ID分组后的整体反向计数，但我们需要按每个ID分别统计w和l在当前日期**之前（更早日期）**的累计数量，期望输出如下：

id value        Date  cumcount_w  cumcount_l
0   a     w  2022-09-30           3           3
1   a     l  2022-08-01           3           2
2   a     l  2022-07-15           3           1
3   a     w  2022-07-11           2           1
4   a     w  2022-06-12           1           1
5   a     w  2022-06-11           0           1
6   a     l  2022-06-10           0           0
7   b     l  2022-07-18           2           1
8   b     w  2022-07-14           1           1
9   b     l  2022-07-11           1           0
10  b     w  2022-07-01           0           0

基于cumcount的实现步骤 #

核心思路：按ID分组并按日期降序排列，通过cumcount标记同Value的反向序号，结合反向填充和总数计算得到目标累计值，最后还原原顺序。

1. 保留原始索引，用于后续还原顺序

df['original_idx'] = df.index

2. 按ID分组并降序排序日期，用cumcount标记同Value的反向序号（最新的同Value行序号为0）

# 组内按日期降序排列，保证最新记录在前
df_sorted = df.groupby('id', group_keys=False).apply(lambda x: x.sort_values('Date', ascending=False))

# 仅对w/l行标记cumcount，其余行留空
df_sorted['w_cum'] = df_sorted.groupby(['id', 'value']).cumcount().where(df_sorted['value'] == 'w', pd.NA)
df_sorted['l_cum'] = df_sorted.groupby(['id', 'value']).cumcount().where(df_sorted['value'] == 'l', pd.NA)

3. 计算每个ID下w/l的总数，反向填充序号列并补全空值

# 统计每个ID下w和l的总数量
df_sorted['total_w'] = df_sorted.groupby('id')['value'].transform(lambda x: (x == 'w').sum())
df_sorted['total_l'] = df_sorted.groupby('id')['value'].transform(lambda x: (x == 'l').sum())

# 反向填充空值，无后续记录的行用总数填充
df_sorted['w_cum_filled'] = df_sorted['w_cum'].bfill().fillna(df_sorted['total_w'])
df_sorted['l_cum_filled'] = df_sorted['l_cum'].bfill().fillna(df_sorted['total_l'])

4. 计算目标累计值：总数减去填充后的序号，得到当前日期之前的累计数量

df_sorted['cumcount_w'] = df_sorted['total_w'] - df_sorted['w_cum_filled']
df_sorted['cumcount_l'] = df_sorted['total_l'] - df_sorted['l_cum_filled']

5. 还原原始顺序并清理中间列

result = df_sorted.sort_values('original_idx').drop(columns=['original_idx', 'w_cum', 'l_cum', 'total_w', 'total_l', 'w_cum_filled', 'l_cum_filled'])

验证结果 #

执行print(result)即可得到与期望完全一致的输出。

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