如何在含重复DateTimeIndex的Pandas DataFrame中用ffill补全缺失数据？

问题背景 #

输入的CSV数据格式如下（每15分钟采集一次服务器CPU功耗数据，部分集群/服务器可能因故障缺失时间窗口）：

date,server_cluster,server,cpu,watt
2023-03-29 12:00:00,cluster1,server1,cpu1,104
2023-03-29 12:00:00,cluster1,server1,cpu2,105
2023-03-29 12:00:00,cluster1,server2,cpu1,122
2023-03-29 12:00:00,cluster1,server2,cpu2,103
2023-03-29 12:00:00,cluster2,server1,cpu1,105
2023-03-29 12:00:00,cluster2,server1,cpu2,154
2023-03-29 12:00:00,cluster2,server2,cpu1,122
2023-03-29 12:00:00,cluster2,server2,cpu2,112
2023-03-29 12:15:00,cluster1,server1,cpu1,134
2023-03-29 12:15:00,cluster1,server1,cpu2,145
2023-03-29 12:15:00,cluster1,server2,cpu1,121
2023-03-29 12:15:00,cluster1,server2,cpu2,107
2023-03-29 12:15:00,cluster2,server1,cpu1,167
2023-03-29 12:15:00,cluster2,server1,cpu2,103
2023-03-29 12:15:00,cluster2,server2,cpu1,122
2023-03-29 12:15:00,cluster2,server2,cpu2,177

现有实现代码较为繁琐：

df.groupby(["server_cluster", "server", "cpu"]).resample("15min").last().ffill().reset_index("date").reset_index(drop=True).set_index("date")

需要更简洁的解决方案来补全每个server_cluster-server-cpu分组下缺失的15分钟时间窗口，并填充缺失值。

简洁解决方案 #

方案1：简化索引操作的resample实现 #

先确保时间列为datetime类型，再通过链式调用简化索引重置流程：

import pandas as pd

# 转换时间列格式
df['date'] = pd.to_datetime(df['date'])

# 分组补全时间窗口并填充
df = (df.set_index('date')
        .groupby(["server_cluster", "server", "cpu"])
        .resample("15min")['watt']
        .last()
        .ffill()
        .reset_index())

方案2：使用asfreq更直观实现 #

asfreq专为时间序列补全场景设计，代码更短且语义明确：

import pandas as pd

df['date'] = pd.to_datetime(df['date'])

df = (df.set_index('date')
        .groupby(["server_cluster", "server", "cpu"])
        .asfreq("15min", method='ffill')
        .reset_index())

扩展说明 #

• 两种方案默认覆盖原始数据的最早到最晚时间范围，若需指定自定义时间区间，可添加时间索引扩展逻辑：

  def extend_time_range(group):
      # 自定义起止时间，例如覆盖全天
      full_idx = pd.date_range(start='2023-03-29 00:00:00', end='2023-03-29 23:45:00', freq='15min')
      return group.reindex(full_idx)
  
  df = (df.set_index('date')
          .groupby(["server_cluster", "server", "cpu"])
          .pipe(extend_time_range)
          .ffill()
          .reset_index())
  

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