如何在Pandas中对多Symbol时间序列按5分钟粒度重采样？

问题背景 #

我有一个包含Date、Symbol及相关行情数值的1分钟粒度时间序列DataFrame，需要将其重采样为5分钟粒度，且每个Symbol需单独处理。Symbol代表股票/期权标的代码，实际数据可能包含数百个不同的标的。

单个标的的重采样方法已掌握：

df = df.groupby(pd.Grouper(key='Date', freq='5Min')).agg({
    'Open': 'first', 
    'High': 'max', 
    'Low': 'min', 
    'Close': 'last'
}).reset_index()

但处理多标的时遇到阻碍，尝试先按Symbol分组再按时间分组，却无法实现链式调用，伪代码如下：

df.groupby('Symbol').groupby(pd.Grouper(key='Date',freq='5Min')).agg(...)

解决方案 #

方法1：多键分组（性能更优，推荐） #

直接将Symbol与时间Grouper作为分组键传入groupby，一次性完成分组与重采样：

# 第一步：确保Date列转换为datetime类型
df['Date'] = pd.to_datetime(df['Date'])

# 多键分组+重采样
resampled_df = df.groupby([
    'Symbol',
    pd.Grouper(key='Date', freq='5Min')
]).agg({
    'Open': 'first',  # 取区间第一条记录的Open
    'High': 'max',    # 取区间内High的最大值
    'Low': 'min',     # 取区间内Low的最小值
    'Close': 'last'   # 取区间最后一条记录的Close
}).reset_index()

方法2：分组后Apply重采样（灵活度更高） #

如果需要为不同标的自定义重采样逻辑，可以先按Symbol分组，再对每个分组应用单个标的的重采样逻辑：

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

# 定义单标的重采样函数
def resample_single_symbol(group):
    return group.groupby(pd.Grouper(key='Date', freq='5Min')).agg({
        'Open': 'first',
        'High': 'max',
        'Low': 'min',
        'Close': 'last'
    }).reset_index()

# 按Symbol分组后应用函数
resampled_df = df.groupby('Symbol').apply(resample_single_symbol).reset_index(drop=True)

示例验证 #

使用提供的示例数据测试：

import pandas as pd

# 示例数据
sample_data = {
    'Date': {0: '2018-01-01 09:15:00',
  1: '2018-01-01 09:16:00',
  2: '2018-01-01 09:17:00',
  3: '2018-01-01 09:18:00',
  4: '2018-01-01 09:19:00',
  5: '2018-01-01 09:20:00',
  6: '2018-01-01 09:21:00',
  7: '2018-01-01 09:22:00',
  8: '2018-01-01 09:23:00',
  9: '2018-01-01 09:24:00',
  10: '2018-01-01 09:25:00',
  11: '2018-01-01 09:15:00',
  12: '2018-01-01 09:16:00',
  13: '2018-01-01 09:17:00',
  14: '2018-01-01 09:18:00',
  15: '2018-01-01 09:19:00',
  16: '2018-01-01 09:20:00',
  17: '2018-01-01 09:21:00',
  18: '2018-01-01 09:22:00',
  19: '2018-01-01 09:23:00',
  20: '2018-01-01 09:24:00',
  21: '2018-01-01 09:25:00'},
 'Open': {0: 10531.7,
  1: 10523.0,
  2: 10525.8,
  3: 10522.6,
  4: 10521.05,
  5: 10524.2,
  6: 10526.85,
  7: 10529.95,
  8: 10533.2,
  9: 10530.95,
  10: 11528.4,
  11: 11531.7,
  12: 11523.0,
  13: 11525.8,
  14: 11522.6,
  15: 11521.05,
  16: 11524.2,
  17: 11526.85,
  18: 11529.95,
  19: 11533.2,
  20: 11530.95,
  21: 11528.4},
 'High': {0: 10533.7,
  1: 10527.5,
  2: 10526.15,
  3: 10522.95,
  4: 10523.8,
  5: 10530.25,
  6: 10530.3,
  7: 10533.55,
  8: 10534.8,
  9: 10531.25,
  10: 11529.1,
  11: 11533.7,
  12: 11527.5,
  13: 11526.15,
  14: 11522.95,
  15: 11523.8,
  16: 11530.25,
  17: 11530.3,
  18: 11533.55,
  19: 11534.8,
  20: 11531.25,
  21: 11529.1},
 'Low': {0: 10518.35,
  1: 10522.25,
  2: 10522.6,
  3: 10519.65,
  4: 10520.9,
  5: 10523.65,
  6: 10526.8,
  7: 10529.95,
  8: 10530.1,
  9: 10527.6,
  10: 11526.1,
  11: 11518.35,
  12: 11522.25,
  13: 11522.6,
  14: 11519.65,
  15: 11520.9,
  16: 11523.65,
  17: 11526.8,
  18: 11529.95,
  19: 11530.1,
  20: 11527.6,
  21: 11526.1},
 'Close': {0: 10523.15,
  1: 10525.95,
  2: 10522.6,
  3: 10521.35,
  4: 10523.7,
  5: 10526.45,
  6: 10530.3,
  7: 10533.3,
  8: 10530.5,
  9: 10528.45,
  10: 11527.45,
  11: 11523.15,
  12: 11525.95,
  13: 11522.6,
  14: 11521.35,
  15: 11523.7,
  16: 11526.45,
  17: 11530.3,
  18: 11533.3,
  19: 11530.5,
  20: 11528.45,
  21: 11527.45},
 'Symbol': {0: 'A',
  1: 'A',
  2: 'A',
  3: 'A',
  4: 'A',
  5: 'A',
  6: 'A',
  7: 'A',
  8: 'A',
  9: 'A',
  10: 'B',
  11: 'B',
  12: 'B',
  13: 'B',
  14: 'B',
  15: 'B',
  16: 'B',
  17: 'B',
  18: 'B',
  19: 'B',
  20: 'B',
  21: 'B'}
}

# 转换为DataFrame
df = pd.from_dict(sample_data)

# 使用方法1处理
df['Date'] = pd.to_datetime(df['Date'])
resampled_df = df.groupby([
    'Symbol',
    pd.Grouper(key='Date', freq='5Min')
]).agg({
    'Open': 'first',
    'High': 'max',
    'Low': 'min',
    'Close': 'last'
}).reset_index()

print(resampled_df)

输出结果会包含每个标的的5分钟聚合数据，例如：

• Symbol A的2018-01-01 09:15:00区间（09:15-09:19）：Open=10531.7，High=10533.7，Low=10518.35，Close=10523.7
• Symbol A的2018-01-01 09:20:00区间（09:20-09:24）：Open=10524.2，High=10534.8，Low=10523.65，Close=10528.45
• Symbol B的对应区间也会按相同规则单独计算

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