基于另一DataFrame时间中心的Pandas DataFrame滚动平均实现

问题描述 #

我有两个DataFrame：

• df1：数据采集间隔约15秒，时间范围为2022-10-04 00:00:24至00:23:14
• df2：采集间隔约5分钟，时间范围为2022-10-03 00:03:23至00:22:25

需求：将df2的每个时间戳匹配到df1中最接近的时间戳，然后以该匹配到的时间戳为中心，对df1中前后5分钟（即中心时间±2.5分钟）的数据计算速度平均值。

df1数据示例 #

time  speed
0  2022-10-04 00:00:24  4.590
1  2022-10-04 00:00:41  4.389
2  2022-10-04 00:00:57  4.367
...
78 2022-10-04 00:22:40  3.659
79 2022-10-04 00:23:14  4.056

df2数据示例 #

time     speed
0 2022-10-03 00:03:23  4.646689
1 2022-10-03 00:08:24  5.328516
2 2022-10-03 00:13:24  5.895778
3 2022-10-03 00:18:24  5.665014
4 2022-10-03 00:22:25  6.313763

解决方案 #

步骤1：预处理时间列 #

先将两个DataFrame的time列转换为pandas的datetime类型，才能进行时间运算：

import pandas as pd

# 转换时间列为datetime类型
df1['time'] = pd.to_datetime(df1['time'])
df2['time'] = pd.to_datetime(df2['time'])

步骤2：匹配df2时间到df1的最近时间戳 #

merge_asof仅支持向前/向后匹配最近时间，无法直接找绝对最接近的时间戳。我们可以通过计算时间差绝对值的最小值来实现匹配：

# 计算每个df2时间与df1所有时间的时间差绝对值
time_diff = abs(df2['time'].values[:, None] - df1['time'].values)
# 找到每个df2时间对应的最小时间差索引
closest_indices = time_diff.argmin(axis=1)
# 将匹配到的df1时间戳添加到df2中
df2['matched_time'] = df1.loc[closest_indices, 'time'].values

步骤3：计算中心5分钟窗口的平均速度 #

以匹配到的时间为中心，筛选前后2.5分钟（150秒）内的df1数据，计算速度平均值：

# 定义时间窗口：±2.5分钟（150秒）
window = pd.Timedelta('150s')

# 遍历每个匹配时间，计算窗口内的平均速度
results = []
for _, row in df2.iterrows():
    # 筛选时间范围内的df1数据
    time_mask = (df1['time'] >= row['matched_time'] - window) & (df1['time'] <= row['matched_time'] + window)
    window_data = df1[time_mask]
    # 计算平均速度并记录
    results.append({
        'df2_time': row['time'],
        'matched_df1_time': row['matched_time'],
        'avg_speed_5min': window_data['speed'].mean(),
        'window_data_count': len(window_data)
    })

# 转换为结果DataFrame
result_df = pd.DataFrame(results)

结果说明 #

运行后result_df会输出每个df2时间对应的匹配时间、5分钟窗口的平均速度，以及窗口内的数据量。注意：由于df2的时间均为2022-10-03，而df1的时间为2022-10-04，所有df2时间都会匹配到df1的第一个时间戳2022-10-04 00:00:24（时间差最小），如果实际数据时间范围有重叠，会自动匹配到对应最近的时间。

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