如何在Pandas DataFrame中按ID统计行指定日期前的事件数

原始DataFrame #

预期结果 #

已尝试方案 #

• 使用groupby+pivot结果不符合预期；
• 尝试DuckDB窗口函数SQL方式，但未达到预期效果，代码如下：

d = {'Date': ["23.01.23", "19.01.23", "23.12.22", "23.01.23", "19.01.23", "23.12.22"],'ID': [1,1,1,2,2,2], "Event": ["AA","AB","AA","AA","AA","AB"]}
test_df = pd.DataFrame(data = d)
import duckdb
duckdb.query("SELECT Date, Id, Event, COUNT() OVER(PARTITION BY ID, event ) as 'count' FROM test_df").df()

解决方案 #

Pandas 实现 #

先将日期转为可比较的datetime类型，对每个ID的每行记录，统计当前日期及之前的各类事件数量，最后合并结果：

import pandas as pd

# 构造原始数据
d = {'Date': ["23.01.23", "19.01.23", "23.12.22", "23.01.23", "19.01.23", "23.12.22"],
     'ID': [1,1,1,2,2,2], 
     "Event": ["AA","AB","AA","AA","AA","AB"]}
test_df = pd.DataFrame(data=d)

# 转换日期格式为datetime，方便比较
test_df['Date'] = pd.to_datetime(test_df['Date'], format='%d.%m.%y')

# 生成各类事件的计数列
event_types = test_df['Event'].unique()
for event in event_types:
    test_df[f'Count of {event}'] = test_df.apply(
        lambda row: test_df[(test_df['ID'] == row['ID']) & (test_df['Date'] <= row['Date']) & (test_df['Event'] == event)].shape[0],
        axis=1
    )

# 恢复日期的原始字符串格式
test_df['Date'] = test_df['Date'].dt.strftime('%d.%m.%y')

print(test_df)

DuckDB SQL 实现 #

用窗口函数按ID分区、日期排序做累计计数，再通过PIVOT转成列，最后关联原表保留原始Event列：

import pandas as pd
import duckdb

d = {'Date': ["23.01.23", "19.01.23", "23.12.22", "23.01.23", "19.01.23", "23.12.22"],
     'ID': [1,1,1,2,2,2], 
     "Event": ["AA","AB","AA","AA","AA","AB"]}
test_df = pd.DataFrame(data=d)

# 注册临时表
duckdb.register('test_df', test_df)

# 执行SQL查询
result_df = duckdb.query("""
WITH ranked_data AS (
    SELECT 
        Date,
        ID,
        Event,
        COUNT(*) OVER (PARTITION BY ID, Event ORDER BY STRPTIME(Date, '%d.%m.%y') ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS event_count
    FROM test_df
),
pivoted AS (
    SELECT 
        Date,
        ID,
        MAX(CASE WHEN Event = 'AA' THEN event_count ELSE 0 END) AS "Count of AA",
        MAX(CASE WHEN Event = 'AB' THEN event_count ELSE 0 END) AS "Count of AB"
    FROM ranked_data
    GROUP BY Date, ID
)
SELECT t.Date, t.ID, t.Event, p."Count of AA", p."Count of AB"
FROM test_df t
JOIN pivoted p ON t.Date = p.Date AND t.ID = p.ID
ORDER BY t.ID, STRPTIME(t.Date, '%d.%m.%y') DESC
""").df()

print(result_df)

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