如何基于df2的自定义起止日期对df1分ID进行时间序列重采样

DevOps 小满

2026-9-25

问题描述

我有两个pandas DataFrame：

df1包含多组以不同ID标识的时间序列数据，字段包括Index、Timestamp、Data、ID
df2存储了每个ID对应的时间序列处理的开始、结束日期，字段包括End Date、Start Date、ID、Name等

我此前已经实现了所有ID统一使用2010-01-01到2010-01-11作为时间范围，按1分钟频率重采样的逻辑，实现代码如下：

start = '2010-01-01'
end = '2010-01-11'

def f(x):
    r = pd.date_range(start=start, end = end, freq='1min')
    return x.reindex(r, method='ffill').bfill()


df_sub = (df1
        .set_index('Timestamp')
        .groupby('ID', sort=False)['Data']
        .apply(f)
        .rename_axis(['ID','Timestamp'])
        .reset_index()
        )

但上述方案所有ID使用相同的起止时间，我希望改为调用df2中每个ID对应的自定义起止日期进行重采样：例如ID 1的处理时间范围为2010-11-23到2010-12-03，ID 26的处理时间范围为2010-03-28到2010-04-07，最终输出包含ID、Timestamp、Data三个字段的重采样结果。

测试数据构造

df1构造代码

from pandas import Timestamp
import pandas as pd

df1 = pd.DataFrame({'Index': {(2, 1): 2,
  (2, 6): 8,
  (2, 37): 47,
  (2, 81): 92,
  (2, 88): 101,
  (2, 132): 146,
  (2, 139): 155,
  (2, 436): 453,
  (2, 545): 564,
  (2, 816): 835,
  (10, 172): 188,
  (10, 450): 469,
  (10, 565): 584,
  (10, 830): 849,
  (10, 1000): 1019,
  (10, 271312): 271331,
  (10, 271313): 271332,
  (10, 271314): 271333,
  (10, 271315): 271334,
  (10, 271316): 271335,
  (120, 1614): 1633,
  (120, 1665): 1684,
  (120, 1666): 1685,
  (120, 1733): 1752,
  (120, 1734): 1753,
  (120, 1835): 1854,
  (120, 1836): 1855,
  (120, 1957): 1976,
  (120, 1958): 1977,
  (120, 2091): 2110},
 'Timestamp': {(2, 1): Timestamp('2014-03-04 13:16:44.310000'),
  (2, 6): Timestamp('2014-03-04 13:17:01.777000'),
  (2, 37): Timestamp('2014-04-17 11:59:57.470000'),
  (2, 81): Timestamp('2014-04-17 12:01:08.973000'),
  (2, 88): Timestamp('2014-04-17 12:05:55.153000'),
  (2, 132): Timestamp('2014-04-17 12:08:58.933000'),
  (2, 139): Timestamp('2014-04-17 12:35:58.290000'),
  (2, 436): Timestamp('2014-04-17 12:41:42.147000'),
  (2, 545): Timestamp('2014-04-17 12:46:14.450000'),
  (2, 816): Timestamp('2014-04-17 13:05:53.077000'),
  (10, 172): Timestamp('2014-04-17 12:35:58.633000'),
  (10, 450): Timestamp('2014-04-17 12:41:42.067000'),
  (10, 565): Timestamp('2014-04-17 12:46:14.747000'),
  (10, 830): Timestamp('2014-04-17 13:05:53.153000'),
  (10, 1000): Timestamp('2014-04-17 13:10:20.127000'),
  (10, 271312): Timestamp('2014-05-13 14:59:44.627000'),
  (10, 271313): Timestamp('2014-05-13 14:59:44.780000'),
  (10, 271314): Timestamp('2014-05-13 14:59:45.600000'),
  (10, 271315): Timestamp('2014-05-13 14:59:45.757000'),
  (10, 271316): Timestamp('2014-05-13 14:59:46.687000'),
  (120, 1614): Timestamp('2014-04-17 15:39:52.673000'),
  (120, 1665): Timestamp('2014-04-17 15:46:41.260000'),
  (120, 1666): Timestamp('2014-04-17 15:46:41.417000'),
  (120, 1733): Timestamp('2014-04-17 16:07:54.657000'),
  (120, 1734): Timestamp('2014-04-17 16:07:54.817000'),
  (120, 1835): Timestamp('2014-04-17 16:23:59.943000'),
  (120, 1836): Timestamp('2014-04-17 16:24:00.103000'),
  (120, 1957): Timestamp('2014-04-17 16:53:00.543000'),
  (120, 1958): Timestamp('2014-04-17 16:53:00.703000'),
  (120, 2091): Timestamp('2014-04-17 17:29:21.163000')},
 'Data': {(2, 1): 30.0,
  (2, 6): 30.0,
  (2, 37): 25.0,
  (2, 81): 25.0,
  (2, 88): 25.0,
  (2, 132): 25.0,
  (2, 139): 25.0,
  (2, 436): 25.0,
  (2, 545): 25.0,
  (2, 816): 25.0,
  (10, 172): 25.0,
  (10, 450): 25.0,
  (10, 565): 25.0,
  (10, 830): 25.0,
  (10, 1000): 25.0,
  (10, 271312): 25.0,
  (10, 271313): 27.5,
  (10, 271314): 27.5,
  (10, 271315): 30.5,
  (10, 271316): 30.5,
  (120, 1614): 31.0,
  (120, 1665): 30.5,
  (120, 1666): 30.0,
  (120, 1733): 29.5,
  (120, 1734): 29.0,
  (120, 1835): 28.5,
  (120, 1836): 28.0,
  (120, 1957): 27.5,
  (120, 1958): 27.0,
  (120, 2091): 26.5},
 'ID': {(2, 1): 2,
  (2, 6): 2,
  (2, 37): 2,
  (2, 81): 2,
  (2, 88): 2,
  (2, 132): 2,
  (2, 139): 2,
  (2, 436): 2,
  (2, 545): 2,
  (2, 816): 2,
  (10, 172): 10,
  (10, 450): 10,
  (10, 565): 10,
  (10, 830): 10,
  (10, 1000): 10,
  (10, 271312): 10,
  (10, 271313): 10,
  (10, 271314): 10,
  (10, 271315): 10,
  (10, 271316): 10,
  (120, 1614): 120,
  (120, 1665): 120,
  (120, 1666): 120,
  (120, 1733): 120,
  (120, 1734): 120,
  (120, 1835): 120,
  (120, 1836): 120,
  (120, 1957): 120,
  (120, 1958): 120,
  (120, 2091): 120}
  })

df2构造代码

df2 = pd.DataFrame({'ID': {8: 10, 9: 2, 116: 120},
 'Start Date': {8: Timestamp('2014-04-20 00:00:00'),
  9: Timestamp('2014-03-04 00:00:00'),
  116: Timestamp('2014-04-17 00:00:00')},
 'End Date': {8: Timestamp('2014-04-30 00:00:00'),
  9: Timestamp('2014-03-14 00:00:00'),
  116: Timestamp('2014-04-27 00:00:00')},
 'comment': {8: 'TBA', 9: 'TBA', 116: 'TBA'},
 'Name': {8: 'NN95', 9: 'AA01', 116: 'BB10'}})

实现方案

首先将df2转换为ID到起止日期的映射表，方便分组时快速查询对应时间范围，再修改重采样函数，根据当前分组的ID获取自定义的起止时间生成时间序列即可：

# 构造ID到起止日期的映射字典
id_date_map = df2.set_index('ID')[['Start Date', 'End Date']].to_dict('index')

def resample_by_id(x):
    # x.name即为当前分组的ID值
    id_cfg = id_date_map[x.name]
    start = id_cfg['Start Date']
    end = id_cfg['End Date']
    # 生成对应时间范围的1分钟频率序列
    r = pd.date_range(start=start, end=end, freq='1min')
    return x.reindex(r, method='ffill').bfill()

df_result = (df1
        .set_index('Timestamp')
        .groupby('ID', sort=False)['Data']
        .apply(resample_by_id)
        .rename_axis(['ID','Timestamp'])
        .reset_index()
        )

最终输出的df_result就包含了ID、Timestamp、Data三个字段，每个ID都按照df2中指定的时间范围完成了1分钟频率的重采样。

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

如何基于df2的自定义起止日期对df1分ID进行时间序列重采样

df1构造代码 #

df2构造代码 #

df1构造代码

df2构造代码