如何按5天聚合Datetime DataFrame并计算平均值？

问题描述 #

现有包含dates（Datetime类型）和Values列的DataFrame，需按每5天为一组聚合，计算每组Values的平均值，生成两种格式的结果。

DataFrame构造代码：

import numpy as np
import pandas as pd
from pandas import Timestamp

a = [[Timestamp('2014-06-17 00:00:00'), 0.023088847378082145],
 [Timestamp('2014-06-18 00:00:00'), -0.02137513226556209],
 [Timestamp('2014-06-19 00:00:00'), -0.023107608748262454],
 [Timestamp('2014-06-20 00:00:00'), -0.005373831609931101],
 [Timestamp('2014-06-23 00:00:00'), 0.0013989552359290336],
 [Timestamp('2014-06-24 00:00:00'), 0.02109937927428618],
 [Timestamp('2014-06-25 00:00:00'), -0.008350303722982733],
 [Timestamp('2014-06-26 00:00:00'), -0.037202662556428456],
 [Timestamp('2014-06-27 00:00:00'), 0.00019764611153205713],
 [Timestamp('2014-06-30 00:00:00'), 0.003260577288983324],
 [Timestamp('2014-07-01 00:00:00'), -0.0072877596184343085],
 [Timestamp('2014-07-02 00:00:00'), 0.010168645518006336],
 [Timestamp('2014-07-03 00:00:00'), -0.011539447143668391],
 [Timestamp('2014-07-04 00:00:00'), 0.025285678867997374],
 [Timestamp('2014-07-07 00:00:00'), -0.004602922207492033],
 [Timestamp('2014-07-08 00:00:00'), -0.031298707413768834],
 [Timestamp('2014-07-09 00:00:00'), 0.005929355847110296],
 [Timestamp('2014-07-10 00:00:00'), -0.0037464360290646592],
 [Timestamp('2014-07-11 00:00:00'), -0.030786217361942203],
 [Timestamp('2014-07-14 00:00:00'), -0.004914625647469917],
 [Timestamp('2014-07-15 00:00:00'), 0.010865602291856957],
 [Timestamp('2014-07-16 00:00:00'), 0.018000430446729165],
 [Timestamp('2014-07-17 00:00:00'), -0.007274924758687407],
 [Timestamp('2014-07-18 00:00:00'), -0.005852455583728933],
 [Timestamp('2014-07-21 00:00:00'), 0.021397540863909104],
 [Timestamp('2014-07-22 00:00:00'), 0.03337842963821558],
 [Timestamp('2014-07-23 00:00:00'), 0.0022309307682939483],
 [Timestamp('2014-07-24 00:00:00'), 0.007548983718178803],
 [Timestamp('2014-07-25 00:00:00'), -0.018442920569716525],
 [Timestamp('2014-07-28 00:00:00'), -0.015902529445214975]]

df = pd.DataFrame(a, columns=['dates', 'Values'])

解决方案 #

步骤1：生成分组标识 #

由于数据按日期顺序排列且每天一条记录，用整数除法为每5条记录分配组号：

# 生成从0开始的组号
df['Group Days'] = df.index // 5

格式1：仅保留平均值列 #

分组计算平均值后，丢弃组号列，只保留结果列：

result_format1 = df.groupby('Group Days')['Values'].mean().reset_index(drop=True).rename('Average value')
result_format1 = pd.DataFrame(result_format1)
print(result_format1)

输出示例：

Average value
0      -0.005072
1      -0.004359
2       0.004165
3      -0.012893
4       0.003169
5       0.009182
6      -0.015903

格式2：保留组号与平均值列 #

直接保留分组后的组号和平均值列：

result_format2 = df.groupby('Group Days')['Values'].mean().reset_index().rename(columns={'Values': 'Average value'})
print(result_format2)

输出示例：

Group Days  Average value
0           0      -0.005072
1           1      -0.004359
2           2       0.004165
3           3      -0.012893
4           4       0.003169
5           5       0.009182
6           6      -0.015903

注：若数据存在日期不连续的情况，需基于dates列按实际日期间隔分组，可使用pd.Grouper：

# 从第一条记录的日期开始，按每5天实际日期分组
result_format_date = df.groupby(pd.Grouper(key='dates', freq='5D'))['Values'].mean().reset_index()
result_format_date = result_format_date.rename(columns={'dates': 'Group Start Date', 'Values': 'Average value'})

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