Pandas时间窗口计算后续1秒滚动平均值及on参数问题

问题描述 #

原始DataFrame #

event_timestamp列类型为datetime64[ns]：

event_timestamp                 value
2024-02-02 09:29:19.623481531   8
2024-02-02 09:29:19.907333355   9
2024-02-02 09:29:19.907373437   10
2024-02-02 09:29:21.366842178   11
2024-02-02 09:29:21.366886264   12
2024-02-02 09:29:21.512928275   13
2024-02-02 09:29:21.512968294   14
2024-02-02 09:29:23.050536162   15
2024-02-02 09:29:23.300983260   16
2024-02-02 09:29:23.318874509   17
2024-02-02 09:29:23.318916726   18

需求 #

为每一行计算后续1秒内value列的平均值，预期结果示例：

• 第1行：avg(8,9,10) = 9
• 第2行：avg(9,10) = 9.5
• 第3行：avg(10) = 10
• 第4行：avg(11,12,13,14) = 12.5
• 第5行：avg(12,13,14) = 13（注：原需求笔误，应为12而非2）

尝试的错误代码 #

用户尝试以下代码未得到正确结果：

data = {
    'event_timestamp': ['2024-02-02 09:29:19.623481531', '2024-02-02 09:29:19.907333355', '2024-02-02 09:29:19.907373437', '2024-02-02 09:29:21.366842178', '2024-02-02 09:29:21.366886264', '2024-02-02 09:29:21.512928275', '2024-02-02 09:29:21.512968294', '2024-02-02 09:29:23.050536162', '2024-02-02 09:29:23.300983260'],
    'value': [8, 9, 10, 11, 12, 13, 14, 15, 16]
}

# Create DataFrame
df = pd.DataFrame(data)

# Convert 'event_timestamp' column to timestamp object
df['event_timestamp'] = pd.to_datetime(df['event_timestamp'])

df = df.set_index("event_timestamp") 
df['avg_value_next_1s'] = df['value'].rolling('1s').mean()

补充问题 #

希望保留原索引，不将event_timestamp设为索引，尝试以下代码时报错：invalid on specified as event_timestamp, must be a column (of DataFrame), an Index or None

# Create DataFrame
df = pd.DataFrame(data)
    
# Convert 'event_timestamp' column to timestamp object
df['event_timestamp'] = pd.to_datetime(df['event_timestamp'])

df['avg_value_next_1s'] = df['value'].rolling('1s', on='event_timestamp').mean()

解决方案 #

1. 解决主问题：计算后续1秒内的平均值 #

默认的rolling是向前窗口（包含当前行及之前的行），而需求是向后窗口（包含当前行及之后1秒内的行）。可以通过反转数据实现：

import pandas as pd

data = {
    'event_timestamp': ['2024-02-02 09:29:19.623481531', '2024-02-02 09:29:19.907333355', '2024-02-02 09:29:19.907373437', '2024-02-02 09:29:21.366842178', '2024-02-02 09:29:21.366886264', '2024-02-02 09:29:21.512928275', '2024-02-02 09:29:21.512968294', '2024-02-02 09:29:23.050536162', '2024-02-02 09:29:23.300983260', '2024-02-02 09:29:23.318874509', '2024-02-02 09:29:23.318916726'],
    'value': [8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18]
}

df = pd.DataFrame(data)
df['event_timestamp'] = pd.to_datetime(df['event_timestamp'])

# 反转数据，计算向前窗口均值，再反转回来得到向后窗口结果
df_sorted = df.sort_values('event_timestamp', ascending=False)
df_sorted['avg_next_1s'] = df_sorted['value'].rolling('1s', on='event_timestamp').mean()
df = df.merge(df_sorted[['event_timestamp', 'avg_next_1s']], on='event_timestamp', how='left')

print(df[['event_timestamp', 'value', 'avg_next_1s']])

运行结果会符合预期：第1行avg_next_1s为9，第2行为9.5，第3行为10，以此类推。

2. 解决补充问题：正确使用rolling的on参数 #

报错原因是：Series的rolling方法不支持on参数，on是DataFrame的rolling方法专属参数。正确写法是调用DataFrame的rolling，指定on列，再选取value列计算均值：

import pandas as pd

data = {
    'event_timestamp': ['2024-02-02 09:29:19.623481531', '2024-02-02 09:29:19.907333355', '2024-02-02 09:29:19.907373437', '2024-02-02 09:29:21.366842178', '2024-02-02 09:29:21.366886264', '2024-02-02 09:29:21.512928275', '2024-02-02 09:29:21.512968294', '2024-02-02 09:29:23.050536162', '2024-02-02 09:29:23.300983260'],
    'value': [8, 9, 10, 11, 12, 13, 14, 15, 16]
}

df = pd.DataFrame(data)
df['event_timestamp'] = pd.to_datetime(df['event_timestamp'])

# 使用DataFrame的rolling方法，指定on参数
df['avg_value_next_1s'] = df.rolling('1s', on='event_timestamp')['value'].mean()

这样就能保留原索引，同时基于event_timestamp列进行时间窗口计算。

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