如何用Pandas按Id和小时分组聚合并补全缺失时段？

问题描述 #

我有一个包含Id、Date、Number列的DataFrame，需要按Id分组并按小时对Number进行聚合，同时要保留每个Id的最小与最大datetime之间无数据的时段。

示例数据集：

Id    Date                    Number
1     01-01-2022 00:00:00     1
1     01-01-2022 00:25:00     3
1     01-01-2022 01:00:10     1
2     01-01-2022 00:00:01     4
2     01-01-2022 03:01:01     2

期望结果：

Id    Date                    Number
1     01-01-2022 00:00:00     4
1     01-01-2022 01:00:00     1
2     01-01-2022 00:00:00     4
2     01-01-2022 01:00:00     NaN
2     01-01-2022 02:00:00     NaN
2     01-01-2022 03:00:00     2

尝试了以下代码但结果缺失空时段：

agg = {'Number': 'sum'} #example
data = data.groupby(['Id', pd.Grouper(key='Date', freq='1H')]).agg(agg)

问题原因 #

直接使用groupby + pd.Grouper只会保留原数据中存在数据的小时区间，不会自动填充每个Id的最小到最大时间范围内的所有小时节点。要保留空时段，必须先为每个Id生成完整的小时时间序列，再与聚合后的数据合并。

解决方案 #

步骤1：转换日期格式

首先确保Date列是datetime类型：

import pandas as pd

# 转换Date列为datetime类型
data['Date'] = pd.to_datetime(data['Date'], format='%d-%m-%Y %H:%M:%S')

步骤2：生成每个Id的完整小时时间序列

为每个Id生成从最小日期到最大日期的所有小时节点：

def generate_hour_range(group):
    # 取该Id的最小和最大日期，并向下取整到小时
    start = group['Date'].min().floor('H')
    end = group['Date'].max().floor('H')
    # 生成小时级时间序列
    hours = pd.date_range(start=start, end=end, freq='1H')
    return pd.DataFrame({'Date': hours, 'Id': group['Id'].iloc[0]})

# 按Id分组生成完整时间序列
full_hours = data.groupby('Id').apply(generate_hour_range).reset_index(drop=True)

步骤3：聚合原数据并合并

将原数据按小时聚合后，与完整时间序列合并，自动填充空时段的NaN：

# 按Id和小时聚合Number（求和）
agg_data = data.groupby(['Id', pd.Grouper(key='Date', freq='1H')])['Number'].sum().reset_index()

# 左连接完整时间序列和聚合数据，保留所有小时节点
result = pd.merge(full_hours, agg_data, on=['Id', 'Date'], how='left')

查看最终结果

print(result)

运行后即可得到包含空时段的期望结果，无数据的小时Number字段会显示为NaN。

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