Pandas小时级时间序列分策略插值咨询：小缺口线性大缺口取历史均值
2026-10-3
Pandas小时级时序数据分场景缺失值填充实现
我有一个索引为datetime类型的Pandas小时级数据集，需要对其中的nans值做插值处理，规则如下：
- 若连续缺失时长不超过3小时，直接使用线性插值填充
- 若为连续超过3小时的大缺口，参考过去7天同时段的有效数据均值填充，例如缺失值
2017-07-02 04:00:00属于大缺口，就用2017-06-25到2017-07-02区间内每天4点的数值均值填充。
目前仅使用df.interpolate(method="linear")实现了线性插值，无法满足大缺口的填充需求，因此需要实现上述分场景的填充逻辑。
示例数据集
D31 D32 time 2017-07-01 00:00:00 118.0 118.0 2017-07-01 01:00:00 126.0 126.0 2017-07-01 02:00:00 96.0 np.nan 2017-07-01 03:00:00 88.0 88.0 2017-07-01 04:00:00 76.0 76.0 2017-07-01 05:00:00 60.0 60.0 2017-07-01 06:00:00 59.0 59.0 2017-07-01 07:00:00 53.0 53.0 2017-07-01 08:00:00 54.0 54.0 2017-07-01 09:00:00 47.0 47.0 2017-07-01 10:00:00 48.0 48.0 2017-07-01 11:00:00 56.0 56.0 2017-07-01 12:00:00 65.0 65.0 2017-07-01 13:00:00 57.0 57.0 2017-07-01 14:00:00 46.0 46.0 2017-07-01 15:00:00 39.0 39.0 2017-07-01 16:00:00 24.0 24.0 2017-07-01 17:00:00 22.0 22.0 2017-07-01 18:00:00 np.nan 28.0 2017-07-01 19:00:00 np.nan 25.0 2017-07-01 20:00:00 38.0 38.0 2017-07-01 21:00:00 52.0 52.0 2017-07-01 22:00:00 123.0 123.0 2017-07-01 23:00:00 np.nan np.nan 2017-07-02 00:00:00 np.nan np.nan 2017-07-02 01:00:00 np.nan np.nan 2017-07-02 02:00:00 np.nan np.nan 2017-07-02 03:00:00 np.nan np.nan 2017-07-02 04:00:00 np.nan np.nan 2017-07-02 05:00:00 np.nan np.nan 2017-07-02 06:00:00 np.nan np.nan 2017-07-02 07:00:00 np.nan np.nan 2017-07-02 08:00:00 np.nan np.nan 2017-07-02 09:00:00 np.nan np.nan 2017-07-02 10:00:00 np.nan np.nan 2017-07-02 11:00:00 np.nan np.nan 2017-07-02 12:00:00 np.nan np.nan 2017-07-02 13:00:00 np.nan np.nan 2017-07-02 14:00:00 np.nan np.nan 2017-07-02 15:00:00 np.nan np.nan 2017-07-02 16:00:00 np.nan np.nan 2017-07-02 17:00:00 np.nan np.nan 2017-07-02 18:00:00 np.nan 28.0 2017-07-02 19:00:00 np.nan 25.0 2017-07-02 20:00:00 38.0 38.0 2017-07-02 21:00:00 52.0 52.0 2017-07-02 22:00:00 123.0 123.0 2017-07-02 23:00:00 130.0 131.0 2017-07-03 00:00:00 115.0 118.0 2017-07-03 01:00:00 126.0 128.0 2017-07-03 02:00:00 96.0 np.nan 2017-07-03 03:00:00 86.0 88.0 2017-07-03 04:00:00 77.0 75.0 2017-07-03 05:00:00 60.0 60.0 2017-07-03 06:00:00 61.0 59.0 2017-07-03 07:00:00 57.0 53.0 2017-07-03 08:00:00 55.0 52.0 2017-07-03 09:00:00 47.0 48.0 2017-07-03 10:00:00 42.0 43.0 2017-07-03 11:00:00 56.0 57.0 2017-07-03 12:00:00 68.0 62.0 2017-07-03 13:00:00 56.0 57.0 2017-07-03 14:00:00 47.0 42.0 2017-07-03 15:00:00 33.0 37.0 2017-07-03 16:00:00 27.0 25.0 2017-07-03 17:00:00 24.0 20.0 2017-07-03 18:00:00 np.nan 28.0 2017-07-03 19:00:00 42.0 42.0 2017-07-03 20:00:00 42.0 42.0 2017-07-03 21:00:00 33.0 33.0 2017-07-03 22:00:00 35.0 35.0 2017-07-03 23:00:00 59.0 59.0
可用实现代码
以下是符合需求的最终实现代码，已修正原代码中的笔误：
import pandas as pd import numpy as np def interpolate_obs(df): def long_nan_series(series): # 判断当前分组是否全为空值 all_nans = series.isnull().all() # 判断缺失时长是否超过3小时 too_long = series.index[-1] - series.index[0] > pd.Timedelta("3 hours") return too_long & all_nans def get_average_value(series, mean_value, date): result = np.nan days = 0 days_mean = -1 while np.isnan(result): days += 3 # 超过15天范围仍无有效数据则返回空 if days > 15: return np.nan # 取前后days天同时段的原始数据求均值 timedelta = pd.Timedelta(f"{days} days") working_data = series.loc[date - timedelta : date + timedelta] working_data = working_data[working_data.index.hour == date.hour] result = working_data.mean() if not np.isnan(result): return result # 若原始数据无有效值，取全列同时段的均值数据计算 days_mean += 2 timedelta = pd.Timedelta(f"{days_mean} days") working_data = mean_value.loc[date - timedelta : date + timedelta] working_data = working_data[working_data.index.hour == date.hour] result = working_data.mean() if not np.isnan(result): return result # 计算所有列同时刻的均值作为兜底参考 mean_value = df.mean(axis=1) for col in df.columns: series = df[col] # 对连续空值做分组 df_nan_group_keys = series.isnull().diff().ne(0).cumsum() # 标记时长超过3小时的连续空值组 series_long_nans = series.groupby(df_nan_group_keys).transform(long_nan_series) # 短缺口做线性插值 series_small_gaps = series[~series_long_nans] series_small_interp = series_small_gaps.interpolate(method="linear") # 对长缺口分组处理 series_long_gaps = series[series_long_nans] time_dif = series_long_gaps.index.to_series().diff() time_dif[time_dif > pd.Timedelta("1H")] = np.nan time_dif = time_dif.replace(pd.Timedelta("1H"), 0).replace(np.nan, 1) time_dif = time_dif.astype(int).cumsum() # 为每个长缺口的时间点匹配历史同时段均值 both = pd.concat([series_long_gaps, time_dif], axis=1) both.columns = [col, "group"] series_long_interp = [] for group, df_group in both.groupby("group"): series_long_interp.append(df_group.apply(lambda x: get_average_value(series, mean_value, x.name), axis=1)) series_long_interp = pd.concat(series_long_interp) # 合并两种填充结果 df[col] = pd.concat([series_small_interp, series_long_interp]).sort_index() return df
内容的提问来源于stack exchange，提问作者M.O.
相关产品推荐
相关产品推荐
