Pandas多通道数据重采样问题：多索引还是分组问题？

需求回顾 #

• 按设备分组，将数据重采样为5分钟间隔
• 起始位置同时做前向填充（ffill）和后向填充（bfill）
• 所有设备统一从午夜00:00开始，同步到相同的5分钟时间戳，覆盖完整24小时
• 可使用Python Pandas或PostgreSQL 9.3实现（无法使用TimescaleDB的time_bucket）

示例数据集 #

'device','time','data'
1,2021-07-03 00:00:04,299
1,2021-07-03 00:02:34,300
1,2021-07-03 00:11:09,299
1,2021-07-03 00:13:38,299
1,2021-07-03 00:14:27,300
1,2021-07-03 00:19:25,300
1,2021-07-03 00:20:15,299
1,2021-07-03 00:20:23,300

2,2021-07-03 00:00:53,353
2,2021-07-03 00:07:34,352
2,2021-07-03 00:08:10,353
2,2021-07-03 00:12:27,352
2,2021-07-03 00:14:56,353
2,2021-07-03 00:17:00,352
2,2021-07-03 00:18:10,353
2,2021-07-03 00:19:27,352
2,2021-07-03 00:20:25,353

3,2021-07-03 00:07:44,336
3,2021-07-03 00:21:05,335
3,2021-07-03 00:21:54,336

4,2021-07-03 00:00:38,342
4,2021-07-03 00:02:19,343
4,2021-07-03 00:03:09,342
4,2021-07-03 00:22:46,343

你尝试的Python代码方向验证 #

你之前的几种写法思路正确，但缺少强制对齐统一时间序列和双向填充顺序的关键步骤：

• df = df.set_index('ts').groupby('device').resample('5T').ffill()：未指定统一起始时间，仅按设备自身数据范围重采样
• df = df.groupby('device').apply(lambda x: x.set_index('ts').value.resample('5T').asfreq())：仅生成空值，未做填充
• df = df.set_index('ts').groupby('device').resample('5T').ffill().reset_index('ts')：同样未对齐全局时间范围

Python Pandas 实现方案 #

1. 数据预处理 #

import pandas as pd

# 读取数据，转换时间列格式
df = pd.read_csv('your_data.csv')
df['time'] = pd.to_datetime(df['time'])

2. 生成全局统一时间序列 #

创建覆盖目标日期24小时的5分钟间隔时间戳：

target_date = '2021-07-03'
full_time_index = pd.date_range(
    start=f"{target_date} 00:00:00",
    end=f"{target_date} 23:55:00",
    freq='5T'
)

3. 分组重采样与双向填充 #

定义函数处理单设备数据，确保对齐全局时间并完成填充：

def process_device(group):
    # 将设备数据对齐到全局时间序列
    resampled = group.set_index('time').reindex(full_time_index)
    # 先后向填充（补起始段缺值），再前向填充（补后续段缺值）
    resampled['data'] = resampled['data'].bfill().ffill()
    # 恢复设备ID列并重置索引
    resampled['device'] = group['device'].iloc[0]
    return resampled.reset_index().rename(columns={'index': 'time'})

# 应用到所有设备
result_df = df.groupby('device').apply(process_device).reset_index(drop=True)

关键说明 #

• reindex强制所有设备使用相同的时间戳序列，满足热力图的同步展示需求
• 先bfill再ffill：确保设备第一个数据点之前的午夜时段用第一个已知值填充，之后的时段用最新值延续

PostgreSQL 9.3 实现方案 #

由于9.3版本无time_bucket，需手动生成时间桶并关联数据：

1. 创建时间桶临时表 #

生成覆盖24小时的5分钟间隔时间戳：

CREATE TEMP TABLE time_buckets AS
SELECT generate_series(
    '2021-07-03 00:00:00'::timestamp,
    '2021-07-03 23:55:00'::timestamp,
    '5 minutes'::interval
) AS bucket_time;

2. 生成设备-时间桶笛卡尔积 #

确保每个设备对应所有时间戳：

CREATE TEMP TABLE device_buckets AS
SELECT d.device, tb.bucket_time
FROM (SELECT DISTINCT device FROM your_data_table) d
CROSS JOIN time_buckets tb;

3. 关联原始数据并双向填充 #

使用窗口函数匹配最近的原始数据，实现填充：

WITH ranked_data AS (
    SELECT
        db.device,
        db.bucket_time,
        ydt.data,
        -- 计算时间差绝对值，用于匹配最近数据
        ABS(EXTRACT(EPOCH FROM (ydt.time - db.bucket_time))) AS time_diff,
        -- 按时间差排序，取最近的前向值
        ROW_NUMBER() OVER (PARTITION BY db.device, db.bucket_time ORDER BY time_diff ASC) AS rn_forward,
        -- 按时间倒序排序，取最近的后向值（早于桶时间的最新数据）
        ROW_NUMBER() OVER (PARTITION BY db.device, db.bucket_time ORDER BY ydt.time DESC, time_diff ASC) AS rn_backward
    FROM device_buckets db
    LEFT JOIN your_data_table ydt ON db.device = ydt.device
)
SELECT
    device,
    bucket_time AS time,
    -- 优先用后向值填充起始段，再用前向值填充后续段
    COALESCE(
        MAX(CASE WHEN rn_backward = 1 THEN data END),
        MAX(CASE WHEN rn_forward = 1 THEN data END)
    ) AS data
FROM ranked_data
GROUP BY device, bucket_time
ORDER BY device, bucket_time;

关键说明 #

• CROSS JOIN确保每个设备都拥有完整的24小时时间戳
• COALESCE结合窗口函数的排序结果，实现和Python一致的双向填充逻辑

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