如何更优雅地合并小时级与15分钟级时间序列数据？

AI 小北

2026-8-12

优雅合并小时级与15分钟级K线数据

我目前通过低效的字符串操作（将分钟部分替换为0，例如把'06:15:00'转换为'06:00:00'）实现小时级数据与15分钟级数据的合并，现寻求更优雅的实现方案。以下是当前的代码及输出：

当前实现代码

import ccxt
import pandas as pd

ex = ccxt.binance({'enableRateLimit': True})

df_15m = pd.DataFrame(ex.fetch_ohlcv(symbol='BTC/USDT', timeframe='15m', limit=9), columns=['timestamp', 'open', 'high', 'low', 'close', 'volume'])
df_1h = pd.DataFrame(ex.fetch_ohlcv(symbol='BTC/USDT', timeframe='1h', limit=3), columns=['timestamp', 'open', 'high', 'low', 'close', 'volume'])

df_15m = df_15m.loc[:, ['timestamp', 'close']]
df_1h = df_1h.loc[:, ['timestamp', 'close']]

df_15m['timestamp'] = pd.to_datetime(df_15m['timestamp'], unit='ms')
df_1h['timestamp'] = pd.to_datetime(df_1h['timestamp'], unit='ms')

df_15m['timestamp_h'] = df_15m['timestamp'].astype("string").str[:14] + '00:00'
df_1h.rename(columns={"timestamp": "timestamp_h"}, inplace=True)
df_1h['timestamp_h'] = df_1h['timestamp_h'].astype("string")

df_15m.rename(columns={"close": "close_15m"}, inplace=True)
df_1h.rename(columns={"close": "close_h"}, inplace=True)

print('Hourly Data:\n', df_1h, '\n')
print('15m Data:\n', df_15m, '\n')

df_merged = pd.merge(left=df_15m, right=df_1h, how='left', on=['timestamp_h'])

print('Merged Data:\n', df_merged, '\n')

当前输出

Hourly Data:
            timestamp_h   close_h
0  2022-11-13 05:00:00  16853.68
1  2022-11-13 06:00:00  16684.45
2  2022-11-13 07:00:00  16731.94 

15m Data:
             timestamp  close_15m          timestamp_h
0 2022-11-13 05:00:00   16857.53  2022-11-13 05:00:00
1 2022-11-13 05:15:00   16849.16  2022-11-13 05:00:00
2 2022-11-13 05:30:00   16856.41  2022-11-13 05:00:00
3 2022-11-13 05:45:00   16853.68  2022-11-13 05:00:00
4 2022-11-13 06:00:00   16862.98  2022-11-13 06:00:00
5 2022-11-13 06:15:00   16807.98  2022-11-13 06:00:00
6 2022-11-13 06:30:00   16806.79  2022-11-13 06:00:00
7 2022-11-13 06:45:00   16684.45  2022-11-13 06:00:00
8 2022-11-13 07:00:00   16731.94  2022-11-13 07:00:00 

Merged Data:
             timestamp  close_15m          timestamp_h   close_h
0 2022-11-13 05:00:00   16857.53  2022-11-13 05:00:00  16853.68
1 2022-11-13 05:15:00   16849.16  2022-11-13 05:00:00  16853.68
2 2022-11-13 05:30:00   16856.41  2022-11-13 05:00:00  16853.68
3 2022-11-13 05:45:00   16853.68  2022-11-13 05:00:00  16853.68
4 2022-11-13 06:00:00   16862.98  2022-11-13 06:00:00  16684.45
5 2022-11-13 06:15:00   16807.98  2022-11-13 06:00:00  16684.45
6 2022-11-13 06:30:00   16806.79  2022-11-13 06:00:00  16684.45
7 2022-11-13 06:45:00   16684.45  2022-11-13 06:00:00  16684.45
8 2022-11-13 07:00:00   16731.94  2022-11-13 07:00:00  16731.94

优化方案：利用Pandas时间序列原生能力

不需要依赖字符串操作，直接用Pandas的dt.floor('H')方法对时间戳做小时级向下取整，实现更高效、更鲁棒的时间对齐。

优化后代码

import ccxt
import pandas as pd

ex = ccxt.binance({'enableRateLimit': True})

# 获取并筛选数据
df_15m = pd.DataFrame(
    ex.fetch_ohlcv(symbol='BTC/USDT', timeframe='15m', limit=9),
    columns=['timestamp', 'open', 'high', 'low', 'close', 'volume']
)[['timestamp', 'close']].rename(columns={'close': 'close_15m'})

df_1h = pd.DataFrame(
    ex.fetch_ohlcv(symbol='BTC/USDT', timeframe='1h', limit=3),
    columns=['timestamp', 'open', 'high', 'low', 'close', 'volume']
)[['timestamp', 'close']].rename(columns={'close': 'close_h'})

# 转换时间戳为datetime类型
df_15m['timestamp'] = pd.to_datetime(df_15m['timestamp'], unit='ms')
df_1h['timestamp'] = pd.to_datetime(df_1h['timestamp'], unit='ms')

# 核心优化：小时级时间对齐，无需字符串操作
df_15m['hour_timestamp'] = df_15m['timestamp'].dt.floor('H')
df_1h['hour_timestamp'] = df_1h['timestamp']  # 小时级数据本身是整点，直接复用

# 合并数据
df_merged = pd.merge(df_15m, df_1h, on='hour_timestamp', how='left')

# 打印结果
print('Hourly Data:\n', df_1h, '\n')
print('15m Data:\n', df_15m, '\n')
print('Merged Data:\n', df_merged, '\n')

优化优势

性能更优：Pandas时间序列方法基于底层优化，处理大量数据时比字符串操作效率高得多。
鲁棒性更强：避免了字符串格式变化（如时区差异、特殊时间格式）带来的潜在错误。
代码更简洁：省去多次类型转换步骤，逻辑清晰易维护。

优化后输出（效果一致）

Hourly Data:
           timestamp   close_h       hour_timestamp
0 2022-11-13 05:00:00  16853.68 2022-11-13 05:00:00
1 2022-11-13 06:00:00  16684.45 2022-11-13 06:00:00
2 2022-11-13 07:00:00  16731.94 2022-11-13 07:00:00 

15m Data:
             timestamp  close_15m       hour_timestamp
0 2022-11-13 05:00:00   16857.53 2022-11-13 05:00:00
1 2022-11-13 05:15:00   16849.16 2022-11-13 05:00:00
2 2022-11-13 05:30:00   16856.41 2022-11-13 05:00:00
3 2022-11-13 05:45:00   16853.68 2022-11-13 05:00:00
4 2022-11-13 06:00:00   16862.98 2022-11-13 06:00:00
5 2022-11-13 06:15:00   16807.98 2022-11-13 06:00:00
6 2022-11-13 06:30:00   16806.79 2022-11-13 06:00:00
7 2022-11-13 06:45:00   16684.45 2022-11-13 06:00:00
8 2022-11-13 07:00:00   16731.94 2022-11-13 07:00:00 

Merged Data:
             timestamp  close_15m       hour_timestamp         timestamp_y   close_h
0 2022-11-13 05:00:00   16857.53 2022-11-13 05:00:00 2022-11-13 05:00:00  16853.68
1 2022-11-13 05:15:00   16849.16 2022-11-13 05:00:00 2022-11-13 05:00:00  16853.68
2 2022-11-13 05:30:00   16856.41 2022-11-13 05:00:00 2022-11-13 05:00:00  16853.68
3 2022-11-13 05:45:00   16853.68 2022-11-13 05:00:00 2022-11-13 05:00:00  16853.68
4 2022-11-13 06:00:00   16862.98 2022-11-13 06:00:00 2022-11-13 06:00:00  16684.45
5 2022-11-13 06:15:00   16807.98 2022-11-13 06:00:00 2022-11-13 06:00:00  16684.45
6 2022-11-13 06:30:00   16806.79 2022-11-13 06:00:00 2022-11-13 06:00:00  16684.45
7 2022-11-13 06:45:00   16684.45 2022-11-13 06:00:00 2022-11-13 06:00:00  16684.45
8 2022-11-13 07:00:00   16731.94 2022-11-13 07:00:00 2022-11-13 07:00:00  16731.94

内容的提问来源于stack exchange，提问作者Will-NotGiveUp

如何更优雅地合并小时级与15分钟级时间序列数据？

当前实现代码 #

当前输出 #

优化方案：利用Pandas时间序列原生能力 #

优化后代码 #

优化优势 #