pandas.DataFrame.interpolate()时间序列插值/外插异常问题咨询

问题描述 #

现有间隔约2分钟的时间序列温湿度数据，需重采样至精确2分钟间隔以实现数据同步，但使用interpolate(method='time')、interpolate(method='linear')、interpolate(method='index')等方法后，插值结果存在大量重复行，且与手动计算值差异较大。

测试代码 #

import pandas as pd
import numpy as np

# Generating random data
np.random.seed(0)
num_rows = 20
data = {
    'temperature': np.random.randint(20, 30, num_rows),
    'humidity': np.random.randint(40, 60, num_rows)
}
print(data)
# Generating random time indices
# Generating random time offsets for each row
time_offsets = np.random.randint(0, 120, num_rows)
time_offsets = pd.to_timedelta(time_offsets, unit='s')

# Generating random start and end times
start_time = pd.Timestamp('2024-02-24 9:55:37')
end_time = pd.Timestamp('2024-02-24 11:00:00')

# Generating time indices for each row
time_indices = [start_time + pd.Timedelta(minutes=2*i) + offset for i, offset in enumerate(time_offsets)]

print(time_indices)
# Creating DataFrame
combined_data = pd.DataFrame(data, index=time_indices)

print("Random DataFrame:")
print(combined_data)


# Resample the data to 2-minute frequency
resampled_data = combined_data.resample('2min').interpolate(method='time')


print("\nResampled DataFrame:")
print(resampled_data)

运行结果 #

Random DataFrame:
                     temperature  humidity
2024-02-24 09:56:00           25        45
2024-02-24 09:57:46           20        53
2024-02-24 10:00:34           23        48
2024-02-24 10:02:09           23        49
2024-02-24 10:04:08           27        59
2024-02-24 10:06:51           29        56
2024-02-24 10:09:33           23        59
2024-02-24 10:10:00           25        45
2024-02-24 10:12:12           22        55
2024-02-24 10:14:52           24        55
2024-02-24 10:17:31           27        40
2024-02-24 10:18:32           26        58
2024-02-24 10:20:05           28        43
2024-02-24 10:22:11           28        57
2024-02-24 10:23:37           21        59
2024-02-24 10:25:37           26        59
2024-02-24 10:28:13           27        59
2024-02-24 10:30:30           27        54
2024-02-24 10:31:42           28        47
2024-02-24 10:34:00           21        40

Resampled DataFrame:
                     temperature   humidity
2024-02-24 09:56:00    25.000000  45.000000
2024-02-24 09:58:00    25.000000  45.000000
2024-02-24 10:00:00    25.000000  45.000000
2024-02-24 10:02:00    25.000000  45.000000
2024-02-24 10:04:00    25.000000  45.000000
2024-02-24 10:06:00    25.000000  45.000000
2024-02-24 10:08:00    25.000000  45.000000
2024-02-24 10:10:00    25.000000  45.000000
2024-02-24 10:12:00    24.666667  44.583333
2024-02-24 10:14:00    24.333333  44.166667
2024-02-24 10:16:00    24.000000  43.750000
2024-02-24 10:18:00    23.666667  43.333333
2024-02-24 10:20:00    23.333333  42.916667
2024-02-24 10:22:00    23.000000  42.500000
2024-02-24 10:24:00    22.666667  42.083333
2024-02-24 10:26:00    22.333333  41.666667
2024-02-24 10:28:00    22.000000  41.250000
2024-02-24 10:30:00    21.666667  40.833333
2024-02-24 10:32:00    21.333333  40.416667
2024-02-24 10:34:00    21.000000  40.000000

错误原因 #

• 重采样与插值逻辑误用：直接链式调用resample('2min').interpolate(method='time')时，resample会先将数据按2分钟窗口分组，每个窗口仅保留原始数据中落在该窗口的点。由于原始数据时间戳大多不落在窗口起始点，大部分窗口只有单个数据点甚至没有，此时interpolate只能做向前填充，导致大量重复值。
• 未保证时间索引有序：原始生成的时间索引因随机偏移可能出现乱序，打乱了插值算法对相邻数据的判断逻辑，进一步导致结果异常。

修正方案 #

正确流程是先构建精确的2分钟目标时间索引，再将原始数据对齐到该索引后进行时间插值：

修正后代码 #

import pandas as pd
import numpy as np

# 生成随机数据
np.random.seed(0)
num_rows = 20
data = {
    'temperature': np.random.randint(20, 30, num_rows),
    'humidity': np.random.randint(40, 60, num_rows)
}

# 生成随机时间索引
time_offsets = np.random.randint(0, 120, num_rows)
time_offsets = pd.to_timedelta(time_offsets, unit='s')
start_time = pd.Timestamp('2024-02-24 9:55:37')
time_indices = [start_time + pd.Timedelta(minutes=2*i) + offset for i, offset in enumerate(time_offsets)]

# 创建DataFrame并确保时间索引有序
combined_data = pd.DataFrame(data, index=time_indices).sort_index()

# 生成目标2分钟间隔时间索引
target_start = combined_data.index.floor('2min')[0]
target_end = combined_data.index.ceil('2min')[-1]
target_index = pd.date_range(start=target_start, end=target_end, freq='2min')

# 重新索引并基于时间插值
resampled_data = combined_data.reindex(target_index).interpolate(method='time')

print("原始有序数据：")
print(combined_data)
print("\n重采样插值后数据：")
print(resampled_data)

修正说明 #

• 排序时间索引：确保原始数据按时间顺序排列，让插值算法能正确识别相邻的时间点和数值。
• 构建目标索引：基于原始数据的起止时间生成精确的2分钟间隔序列，保证覆盖所有需要同步的时间点。
• reindex + interpolate：reindex先将原始数据对齐到目标索引（缺失位置生成NaN），再用method='time'根据相邻原始数据的时间戳和数值，计算出目标时间点的线性插值结果，与手动计算逻辑一致。

内容的提问来源于stack exchange，提问作者Hoàng Trung Lê