如何在含非一致秒级时间的Pandas DataFrame中按ID计算每日均价？

云上老周

2026-7-21

Pandas按selection_id分组计算每日均价的高效方案

直接使用Pandas的groupby同时按**日期（从datetime索引提取）**和selection_id字段分组，再对price列求均值即可，无需拆分多个DataFrame，是最高效的实现方式。

原始数据

索引（datetime）	selection_id	price
2023-05-13 05:57:07.554	1	1.50
2023-05-13 06:08:59.193	1	1.56
2023-05-13 06:08:59.085	1	1.61
2023-05-13 06:08:59.085	1	1.50
2023-05-13 06:08:59.085	1	1.51
2023-05-13 06:08:59.085	45	3.12
2023-05-13 05:57:07.554	45	3.16
2023-05-13 06:08:59.193	45	3.12
2023-05-13 06:08:59.085	45	3.16
2023-05-13 06:08:59.085	45	3.12
2023-05-13 06:08:59.085	98	7.05
2023-05-13 06:08:59.085	98	7.52
2023-05-13 05:57:07.554	98	7.11
2023-05-13 06:08:59.193	98	7.99
2023-05-13 06:08:59.085	98	7.50
2023-05-13 06:08:59.085	98	7.20
2023-05-13 06:08:59.085	98	7.65
2023-05-13 06:08:59.085	98	7.45
2023-05-14 05:57:07.554	1	2.50
2023-05-14 06:08:59.193	1	2.56
2023-05-14 06:08:59.085	1	2.61
2023-05-14 06:08:59.085	1	2.50
2023-05-14 06:08:59.085	1	2.51
2023-05-14 06:08:59.085	45	2.12
2023-05-14 05:57:07.554	45	2.16
2023-05-14 06:08:59.193	45	2.12
2023-05-14 06:08:59.085	45	2.16
2023-05-14 06:08:59.085	45	2.12
2023-05-14 06:08:59.085	98	7.05
2023-05-14 06:08:59.085	98	7.52
2023-05-14 05:57:07.554	98	7.11
2023-05-14 06:08:59.193	98	7.99
2023-05-14 06:08:59.085	98	7.50
2023-05-14 06:08:59.085	98	7.20
2023-05-14 06:08:59.085	98	7.65
2023-05-14 06:08:59.085	98	7.45

实现步骤

1. 确认索引类型（可选）

如果DataFrame索引还不是datetime类型，先转换：

import pandas as pd
df.index = pd.to_datetime(df.index)

2. 执行分组聚合

核心代码：

# 按索引日期 + selection_id 分组，计算price均值
daily_avg = df.groupby([df.index.date, 'selection_id'])['price'].mean().reset_index()

# 重命名列名匹配期望格式
daily_avg.columns = ['date', 'selection_id', 'price']

# 可选：将date列设为索引
daily_avg = daily_avg.set_index('date')