python - OHLC 抽样正在创建错误的时间戳蜡烛

Question

采样所需的数据来自 SQLite。它已在此处提供:https://pastebin.com/LU7YApkX

代码:

import sqlite3
import pandas as pd

conn = sqlite3.connect('sqlite_database.db')
query = "SELECT * FROM XXXX WHERE timestamp BETWEEN  '2019-01-24 09:15:00' AND '2019-01-24 09:59:59'"

df = pd.read_sql_query(query, conn, index_col=[
    'timestamp'], parse_dates=['timestamp'])

candles = df['ltp'].resample('5min').ohlc().bfill()
print(candles)

输出良好 (重采样周期 = 3 分钟):

$ python3 why_ohlc_failing.py
                       open    high     low   close
timestamp
2019-01-24 09:15:00  286.55  286.70  285.85  286.20
2019-01-24 09:18:00  286.10  286.30  285.50  285.90
2019-01-24 09:21:00  285.90  286.25  285.65  285.85
2019-01-24 09:24:00  285.80  286.90  285.75  286.65
2019-01-24 09:27:00  286.65  286.85  286.35  286.60
2019-01-24 09:30:00  286.70  286.70  286.20  286.25
2019-01-24 09:33:00  286.25  286.95  286.20  286.95
2019-01-24 09:36:00  287.00  287.50  286.95  287.40
2019-01-24 09:39:00  287.45  287.50  287.00  287.45
2019-01-24 09:42:00  287.35  287.50  287.00  287.50
2019-01-24 09:45:00  287.40  288.15  287.40  288.05
2019-01-24 09:48:00  288.40  288.45  288.30  288.35
2019-01-24 09:51:00  288.40  288.45  288.30  288.35
2019-01-24 09:54:00  288.40  288.45  288.30  288.35
2019-01-24 09:57:00  288.40  288.45  288.30  288.35

输出良好 (重采样周期 = 5 分钟):

$ python3 why_ohlc_failing.py
                       open    high    low   close
timestamp
2019-01-24 09:15:00  286.55  286.70  285.5  285.65
2019-01-24 09:20:00  285.65  286.25  285.6  285.95
2019-01-24 09:25:00  285.95  286.90  285.9  286.60
2019-01-24 09:30:00  286.70  286.70  286.2  286.60
2019-01-24 09:35:00  286.70  287.50  286.6  287.15
2019-01-24 09:40:00  287.15  287.50  287.0  287.50
2019-01-24 09:45:00  287.40  288.15  287.4  288.05
2019-01-24 09:50:00  288.40  288.45  288.3  288.35
2019-01-24 09:55:00  288.40  288.45  288.3  288.35

OUTPUT BAD (重采样周期 = 10 分钟):

$ python3 why_ohlc_failing.py
                       open    high    low   close
timestamp
2019-01-24 09:10:00  286.55  286.70  285.5  285.65
2019-01-24 09:20:00  285.65  286.90  285.6  286.60
2019-01-24 09:30:00  286.70  287.50  286.2  287.15
2019-01-24 09:40:00  287.15  288.15  287.0  288.05
2019-01-24 09:50:00  288.40  288.45  288.3  288.35

输出良好 (重采样周期 = 15 分钟):

$ python3 why_ohlc_failing.py
                       open    high    low   close
timestamp
2019-01-24 09:15:00  286.55  286.90  285.5  286.60
2019-01-24 09:30:00  286.70  287.50  286.2  287.50
2019-01-24 09:45:00  287.40  288.45  287.4  288.35

OUTPUT BAD (重采样周期 = 20 分钟):

$ python3 why_ohlc_failing.py
                       open    high    low   close
timestamp
2019-01-24 09:00:00  286.55  286.70  285.5  285.65
2019-01-24 09:20:00  285.65  287.50  285.6  287.15
2019-01-24 09:40:00  287.15  288.45  287.0  288.35

问题:

如果您查看以上所有 BAD 输出，采样周期为 10min 和 20min，它从 2019-01 开始-24 09:10:00 和 2019-01-24 09:00:00。这是错误的，因为我什至没有 2019-01-24 09:15:01 之前的数据。但是，相同的代码对于 3min、5min 和 15min 的采样周期也能正常工作。

你能帮我弄清楚这里出了什么问题吗？我的理解是与采样周期无关，重新采样的数据应始终以 2019-01-24 09:15:00 开头，否则它没有任何意义，因为在此之前没有可用的股票报价。

Answer 1

以下适用于所有时间间隔:

data = df['ltp'].resample('5min', base=15).ohlc().bfill()

我不得不添加 base=15 尽管我仍在尝试了解这里发生了什么。

我进一步发现，为了在不同的采样周期获得所需的结果，我需要添加各种 base 值，如下所示:

resample('1min', base=15)
resample('2min', base=15)
resample('3min', base=15)
resample('4min', base=15)
resample('5min', base=15)
resample('6min', base=15)
resample('7min', base=16)
resample('8min', base=19)
resample('9min', base=15)
resample('10min', base=15)
resample('11min', base=16)
resample('12min', base=15)
resample('13min', base=22)
resample('14min', base=23)
resample('15min', base=15)
resample('16min', base=27)
resample('17min', base=28)
resample('18min', base=33)
resample('19min', base=42)
resample('20min', base=15)

然而，1min、3min、5min 和 15min 不需要任何base 工作如下:

resample('1min')
resample('3min')
resample('5min')
resample('15min')

还在努力理解base的意义