如何按年/月拆分时间序列DataFrame，支持闰年补NaN

我有一个包含2015至2020年小时级数据的时间序列DataFrame，想要创建新的DataFrame，把每年的时间序列值作为单独列来分别分析。因为存在闰年，要求各列共享同一索引，非闰年的2月29日位置自动填充NaN。

我之前尝试通过创建month和day_of_month列，用merge来实现，但结果索引混乱，数据量从约4万条膨胀到数百万条，内存占用超20GB后程序崩溃，代码及错误结果如下：

years = pd.DataFrame(index=pd.date_range('2016-01-01', '2017-01-01', freq='1H'))
years['month'] = years.index.month
years['day_of_month'] = years.index.day
gp = data_md[['value', 'month', 'day_of_month']].groupby(pd.Grouper(freq='1Y'))
for name, group in gp:
    years = years.merge(group, right_on=['month', 'day_of_month'], left_on=['month', 'day_of_month'])

运行结果：

month   day_of_month    value
0   1   1   0
1   1   1   6
2   1   1   2
3   1   1   0
4   1   1   1
...     ...     ...     ...
210259  12  31  6
210260  12  31  2
210261  12  31  4
210262  12  31  5
210263  12  31  1

原始DataFrame示例 #

核心仅需value列，其余为附加信息：

value    month  day_of_month    week    day_name    year    hour    season  dailyp  day_of_week     ...     hourly_no_noise     daily_trend     daily_seasonal  daily_residuals     daily_no_noise  daily_trend_h   daily_seasonal_h    daily_residuals_h   daily_no_noise_h    Total
date                                                                                    
2015-01-01 00:00:00     0   1   1   1   Thursday    2015    0   Invierno    165.0   3   ...     NaN     NaN     -9.053524   NaN     NaN     NaN     -3.456929   NaN     NaN     6436996.0
2015-01-01 01:00:00     6   1   1   1   Thursday    2015    1   Invierno    NaN     3   ...     NaN     NaN     -9.053524   NaN     NaN     NaN     -4.879983   NaN     NaN     NaN
2015-01-01 02:00:00     2   1   1   1   Thursday    2015    2   Invierno    NaN     3   ...     NaN     NaN     -9.053524   NaN     NaN     NaN     -5.895367   NaN     NaN     NaN
2015-01-01 03:00:00     0   1   1   1   Thursday    2015    3   Invierno    NaN     3   ...     NaN     NaN     -9.053524   NaN     NaN     NaN     -6.468616   NaN     NaN     NaN
2015-01-01 04:00:00     1   1   1   1   Thursday    2015    4   Invierno    NaN     3   ...     NaN     NaN     -9.053524   NaN     NaN     NaN     -6.441830   NaN     NaN     NaN
...     ...     ...     ...     ...     ...     ...     ...     ...     ...     ...     ...     ...     ...     ...     ...     ...     ...     ...     ...     ...     ...
2019-12-31 19:00:00     6   12  31  1   Tuesday     2019    19  Invierno    NaN     1   ...     11.529465   230.571429  -4.997480   -11.299166  237.299166  9.613095    2.805720    1.176491    17.823509   NaN
2019-12-31 20:00:00     3   12  31  1   Tuesday     2019    20  Invierno    NaN     1   ...     11.314857   230.571429  -4.997480   -11.299166  237.299166  9.613095    2.928751    1.176491    17.823509   NaN
2019-12-31 21:00:00     3   12  31  1   Tuesday     2019    21  Invierno    NaN     1   ...     10.141139   230.571429  -4.997480   -11.299166  237.299166  9.613095    1.774848    1.176491    17.823509   NaN
2019-12-31 22:00:00     3   12  31  1   Tuesday     2019    22  Invierno    NaN     1   ...     8.823152    230.571429  -4.997480   -11.299166  237.299166  9.613095    0.663344    1.176491    17.823509   NaN
2019-12-31 23:00:00     6   12  31  1   Tuesday     2019    23  Invierno    NaN     1   ...     6.884636    230.571429  -4.997480   -11.299166  237.299166  9.613095    -1.624980   1.176491    17.823509   NaN

期望结果示例 #

按年份拆分后的DataFrame：

2015      2016      2017      2018      2019
2016-01-01 00:00:00  0.074053  0.218161  0.606810  0.687365  0.352672
2016-01-01 01:00:00  0.465167  0.210297  0.722825  0.683341  0.885175
2016-01-01 02:00:00  0.175964  0.610560  0.722479  0.016842  0.205916
2016-01-01 03:00:00  0.945955  0.807490  0.627525  0.187677  0.535116
2016-01-01 04:00:00  0.757608  0.797835  0.639215  0.455989  0.042285
...                       ...       ...       ...       ...       ...
2016-12-30 20:00:00  0.046138  0.139100  0.397547  0.738687  0.335306
2016-12-30 21:00:00  0.672800  0.802090  0.617625  0.787601  0.007535
2016-12-30 22:00:00  0.698141  0.776686  0.423712  0.667808  0.298338
2016-12-30 23:00:00  0.198089  0.642073  0.586527  0.106567  0.514569
2016-12-31 00:00:00  0.367572  0.390791  0.105193  0.592167  0.007365

非闰年的2月29日位置自动填充NaN：

df['2016-02']
                         2015      2016      2017      2018      2019
2016-02-01 00:00:00  0.656703  0.348784  0.383639  0.208786  0.183642
2016-02-01 01:00:00  0.488729  0.909498  0.873642  0.122028  0.547563
2016-02-01 02:00:00  0.210427  0.912393  0.505873  0.085149  0.358841
2016-02-01 03:00:00  0.281107  0.534750  0.622473  0.643611  0.258437
2016-02-01 04:00:00  0.187434  0.327459  0.701008  0.887041  0.385816
...                       ...       ...       ...       ...       ...
2016-02-29 19:00:00       NaN  0.742402       NaN       NaN       NaN
2016-02-29 20:00:00       NaN  0.013419       NaN       NaN       NaN
2016-02-29 21:00:00       NaN  0.517194       NaN       NaN       NaN
2016-02-29 22:00:00       NaN  0.003136       NaN       NaN       NaN
2016-02-29 23:00:00       NaN  0.128406       NaN       NaN       NaN

解决方案 #

方法一：利用pivot_table快速转换 #

核心思路是给每条数据标记“年中时间戳”（将原日期年份替换为基准闰年2016），再通过pivot_table将年份转为列，自动对齐索引并填充NaN：

import pandas as pd

# 1. 生成年中时间戳：保留月日时分，年份替换为2016（闰年）
data_md['doy_hour'] = data_md.index.map(lambda x: x.replace(year=2016))

# 2. 透视转换：年中时间戳为索引，年份为列，提取value值
result = data_md.pivot_table(index='doy_hour', columns='year', values='value', aggfunc='first')

# 3. 清理索引和列名称（可选）
result.index.name = None
result.columns.name = None

方法二：分组+重索引实现 #

如果原始数据没有year列，可先提取年份，再按年份分组后逐个重索引到基准闰年的小时序列：

# 1. 定义基准索引：2016年全年小时级时间序列（包含2月29日）
base_index = pd.date_range('2016-01-01', '2017-01-01', freq='1H')

# 2. 按年份分组，每个分组重索引到基准索引
groups = []
for year, group in data_md.groupby(data_md.index.year):
    # 将分组索引替换为基准年份的对应日期
    group.index = group.index.map(lambda x: x.replace(year=2016))
    # 重索引到基准序列，缺失值自动填充NaN
    groups.append(group['value'].reindex(base_index).rename(year))

# 3. 合并所有分组为一个DataFrame
result = pd.concat(groups, axis=1)

关键说明 #

• 两种方法都避免了merge导致的笛卡尔积问题，内存占用与原数据量级相当
• 自动处理闰年：非闰年无2月29日数据，重索引后对应位置自动填充NaN
• 如需按月拆分，只需将基准索引改为对应月份的小时序列，分组时按(year, month)分组即可

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