# coding: utf-8
from datetime import datetime, timedelta
import pytz, bitmex
import pandas as pd
import numpy as np
# 取得開始時刻を1時間前に設定
start = datetime.now(pytz.utc) - timedelta(hours=1)
# BitMEX約定履歴取得(REST API)
api = bitmex.bitmex()
trades = api.Trade.Trade_get(symbol="XBTUSD", count=500, startTime=start).result()[0]
# 約定履歴DataFrame生成
df_trades = pd.DataFrame(trades)
display(df_trades.head())
/usr/local/lib/python3.6/site-packages/bravado_core/spec.py:271: Warning: guid format is not registered with bravado-core! category=Warning,
foreignNotional | grossValue | homeNotional | price | side | size | symbol | tickDirection | timestamp | trdMatchID | |
---|---|---|---|---|---|---|---|---|---|---|
0 | 1819.0 | 27312285 | 0.273123 | 6660.0 | Buy | 1819 | XBTUSD | ZeroPlusTick | 2018-09-27 21:06:34.013000+00:00 | 027f613a-4364-3ae2-d2c9-b88a299915e5 |
1 | 27.0 | 405405 | 0.004054 | 6660.0 | Buy | 27 | XBTUSD | ZeroPlusTick | 2018-09-27 21:06:35.200000+00:00 | 3070a09b-d840-c050-5072-8a76cfaa276c |
2 | 17.0 | 255272 | 0.002553 | 6659.5 | Sell | 17 | XBTUSD | MinusTick | 2018-09-27 21:06:36.064000+00:00 | f02930cf-ccd7-15fc-4443-7ca985d80fd2 |
3 | 10.0 | 150170 | 0.001502 | 6659.0 | Sell | 10 | XBTUSD | MinusTick | 2018-09-27 21:06:36.064000+00:00 | 091ac2f7-48d4-1035-a60d-534f2ee2fa56 |
4 | 25.0 | 375400 | 0.003754 | 6659.5 | Buy | 25 | XBTUSD | PlusTick | 2018-09-27 21:06:36.564000+00:00 | ece94782-16ad-5fc8-1f5b-74e83a239b08 |
# DataFrameをcsv保存
df_trades.to_csv("trades.csv")
# 行・列見出し出力指定(行:OFF, 列:ON) ※未指定:いずれもTrue
df_trades.to_csv("trades1.csv", header=True, index=False)
# 出力列を指定
df_trades.to_csv("trades2.csv", columns=["timestamp", "price", "side", "size"])
# データ区切り文字指定(タブ区切り) ※未指定:\n
df_trades.to_csv("trades3.csv", sep="\t")
# 書き込みモード(上書き:"w", 追記:"a") ※未指定:"w"
df_trades.to_csv("trades4.csv", mode="a", header=False)
print("csvをDataFrameに読み込み")
df = pd.read_csv("trades1.csv")
display(df.head())
print("ヘッダ行指定(ヘッダなしは連番列名自動設定) ※未指定:0(先頭行がヘッダ)")
df = pd.read_csv("trades4.csv", header=None)
display(df.head())
print("index列設定 ※未指定:indexは連番自動設定")
df = pd.read_csv("trades.csv", index_col=0)
display(df.head())
print("読み込む列を指定")
df = pd.read_csv("trades.csv", usecols=[4, 5, 6, 9])
df = pd.read_csv("trades.csv", usecols=["price", "side", "size", "timestamp"])
display(df.head())
print("型を指定")
df = pd.read_csv("trades.csv", dtype={"price":float, "side":str, "size":int})
display(df.dtypes)
print("日付文字列をdatetime型変換")
df = pd.read_csv("trades.csv", parse_dates=[9])
display(df.dtypes)
print("日付文字列をdatetimeindexにして必要な列を型指定して読み込み\n" + \
"(dtypeのint, floatのbit数は扱う値範囲、計算精度に適したサイズを設定)")
df = pd.read_csv("trades.csv", usecols=["price", "side", "size", "timestamp"],
dtype={"price":"float32", "side":"str", "size":"int32"},
index_col=3, parse_dates=["timestamp"])
display(df.head())
display(df.dtypes)
csvをDataFrameに読み込み
foreignNotional | grossValue | homeNotional | price | side | size | symbol | tickDirection | timestamp | trdMatchID | |
---|---|---|---|---|---|---|---|---|---|---|
0 | 1819.0 | 27312285 | 0.273123 | 6660.0 | Buy | 1819 | XBTUSD | ZeroPlusTick | 2018-09-27 21:06:34.013000+00:00 | 027f613a-4364-3ae2-d2c9-b88a299915e5 |
1 | 27.0 | 405405 | 0.004054 | 6660.0 | Buy | 27 | XBTUSD | ZeroPlusTick | 2018-09-27 21:06:35.200000+00:00 | 3070a09b-d840-c050-5072-8a76cfaa276c |
2 | 17.0 | 255272 | 0.002553 | 6659.5 | Sell | 17 | XBTUSD | MinusTick | 2018-09-27 21:06:36.064000+00:00 | f02930cf-ccd7-15fc-4443-7ca985d80fd2 |
3 | 10.0 | 150170 | 0.001502 | 6659.0 | Sell | 10 | XBTUSD | MinusTick | 2018-09-27 21:06:36.064000+00:00 | 091ac2f7-48d4-1035-a60d-534f2ee2fa56 |
4 | 25.0 | 375400 | 0.003754 | 6659.5 | Buy | 25 | XBTUSD | PlusTick | 2018-09-27 21:06:36.564000+00:00 | ece94782-16ad-5fc8-1f5b-74e83a239b08 |
ヘッダ行指定(ヘッダなしは連番列名自動設定) ※未指定:0(先頭行がヘッダ)
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | 1819.0 | 27312285 | 0.273123 | 6660.0 | Buy | 1819 | XBTUSD | ZeroPlusTick | 2018-09-27 21:06:34.013000+00:00 | 027f613a-4364-3ae2-d2c9-b88a299915e5 |
1 | 1 | 27.0 | 405405 | 0.004054 | 6660.0 | Buy | 27 | XBTUSD | ZeroPlusTick | 2018-09-27 21:06:35.200000+00:00 | 3070a09b-d840-c050-5072-8a76cfaa276c |
2 | 2 | 17.0 | 255272 | 0.002553 | 6659.5 | Sell | 17 | XBTUSD | MinusTick | 2018-09-27 21:06:36.064000+00:00 | f02930cf-ccd7-15fc-4443-7ca985d80fd2 |
3 | 3 | 10.0 | 150170 | 0.001502 | 6659.0 | Sell | 10 | XBTUSD | MinusTick | 2018-09-27 21:06:36.064000+00:00 | 091ac2f7-48d4-1035-a60d-534f2ee2fa56 |
4 | 4 | 25.0 | 375400 | 0.003754 | 6659.5 | Buy | 25 | XBTUSD | PlusTick | 2018-09-27 21:06:36.564000+00:00 | ece94782-16ad-5fc8-1f5b-74e83a239b08 |
index列設定 ※未指定:indexは連番自動設定
foreignNotional | grossValue | homeNotional | price | side | size | symbol | tickDirection | timestamp | trdMatchID | |
---|---|---|---|---|---|---|---|---|---|---|
0 | 1819.0 | 27312285 | 0.273123 | 6660.0 | Buy | 1819 | XBTUSD | ZeroPlusTick | 2018-09-27 21:06:34.013000+00:00 | 027f613a-4364-3ae2-d2c9-b88a299915e5 |
1 | 27.0 | 405405 | 0.004054 | 6660.0 | Buy | 27 | XBTUSD | ZeroPlusTick | 2018-09-27 21:06:35.200000+00:00 | 3070a09b-d840-c050-5072-8a76cfaa276c |
2 | 17.0 | 255272 | 0.002553 | 6659.5 | Sell | 17 | XBTUSD | MinusTick | 2018-09-27 21:06:36.064000+00:00 | f02930cf-ccd7-15fc-4443-7ca985d80fd2 |
3 | 10.0 | 150170 | 0.001502 | 6659.0 | Sell | 10 | XBTUSD | MinusTick | 2018-09-27 21:06:36.064000+00:00 | 091ac2f7-48d4-1035-a60d-534f2ee2fa56 |
4 | 25.0 | 375400 | 0.003754 | 6659.5 | Buy | 25 | XBTUSD | PlusTick | 2018-09-27 21:06:36.564000+00:00 | ece94782-16ad-5fc8-1f5b-74e83a239b08 |
読み込む列を指定
price | side | size | timestamp | |
---|---|---|---|---|
0 | 6660.0 | Buy | 1819 | 2018-09-27 21:06:34.013000+00:00 |
1 | 6660.0 | Buy | 27 | 2018-09-27 21:06:35.200000+00:00 |
2 | 6659.5 | Sell | 17 | 2018-09-27 21:06:36.064000+00:00 |
3 | 6659.0 | Sell | 10 | 2018-09-27 21:06:36.064000+00:00 |
4 | 6659.5 | Buy | 25 | 2018-09-27 21:06:36.564000+00:00 |
型を指定
Unnamed: 0 int64 foreignNotional float64 grossValue int64 homeNotional float64 price float64 side object size int64 symbol object tickDirection object timestamp object trdMatchID object dtype: object
日付文字列をdatetime型変換
Unnamed: 0 int64 foreignNotional float64 grossValue int64 homeNotional float64 price float64 side object size int64 symbol object tickDirection object timestamp datetime64[ns] trdMatchID object dtype: object
日付文字列をdatetimeindexにして必要な列を型指定して読み込み (dtypeのint, floatのbit数は扱う値範囲、計算精度に適したサイズを設定)
price | side | size | |
---|---|---|---|
timestamp | |||
2018-09-27 21:06:34.013 | 6660.0 | Buy | 1819 |
2018-09-27 21:06:35.200 | 6660.0 | Buy | 27 |
2018-09-27 21:06:36.064 | 6659.5 | Sell | 17 |
2018-09-27 21:06:36.064 | 6659.0 | Sell | 10 |
2018-09-27 21:06:36.564 | 6659.5 | Buy | 25 |
price float32 side object size int32 dtype: object
print("datetimeindexにタイムゾーン設定(UTC)")
df.index = df.index.tz_localize("UTC")
display(df.head())
print("datetimeindexにタイムゾーン変換(JST)")
df.index = df.index.tz_convert("Asia/Tokyo")
display(df.head())
print("datetimeからunixtime変換")
print("ミリ秒")
df["msec_unixtime"] = df.index.astype(np.int64) // 10**6
display(df.head())
print("秒")
df["unixtime"] = df.index.astype(np.int64) // 10**9
display(df.head())
datetimeindexにタイムゾーン設定(UTC)
price | side | size | |
---|---|---|---|
timestamp | |||
2018-09-27 21:06:34.013000+00:00 | 6660.0 | Buy | 1819 |
2018-09-27 21:06:35.200000+00:00 | 6660.0 | Buy | 27 |
2018-09-27 21:06:36.064000+00:00 | 6659.5 | Sell | 17 |
2018-09-27 21:06:36.064000+00:00 | 6659.0 | Sell | 10 |
2018-09-27 21:06:36.564000+00:00 | 6659.5 | Buy | 25 |
datetimeindexにタイムゾーン変換(JST)
price | side | size | |
---|---|---|---|
timestamp | |||
2018-09-28 06:06:34.013000+09:00 | 6660.0 | Buy | 1819 |
2018-09-28 06:06:35.200000+09:00 | 6660.0 | Buy | 27 |
2018-09-28 06:06:36.064000+09:00 | 6659.5 | Sell | 17 |
2018-09-28 06:06:36.064000+09:00 | 6659.0 | Sell | 10 |
2018-09-28 06:06:36.564000+09:00 | 6659.5 | Buy | 25 |
datetimeからunixtime変換 ミリ秒
price | side | size | msec_unixtime | |
---|---|---|---|---|
timestamp | ||||
2018-09-28 06:06:34.013000+09:00 | 6660.0 | Buy | 1819 | 1538082394013 |
2018-09-28 06:06:35.200000+09:00 | 6660.0 | Buy | 27 | 1538082395200 |
2018-09-28 06:06:36.064000+09:00 | 6659.5 | Sell | 17 | 1538082396064 |
2018-09-28 06:06:36.064000+09:00 | 6659.0 | Sell | 10 | 1538082396064 |
2018-09-28 06:06:36.564000+09:00 | 6659.5 | Buy | 25 | 1538082396564 |
秒
price | side | size | msec_unixtime | unixtime | |
---|---|---|---|---|---|
timestamp | |||||
2018-09-28 06:06:34.013000+09:00 | 6660.0 | Buy | 1819 | 1538082394013 | 1538082394 |
2018-09-28 06:06:35.200000+09:00 | 6660.0 | Buy | 27 | 1538082395200 | 1538082395 |
2018-09-28 06:06:36.064000+09:00 | 6659.5 | Sell | 17 | 1538082396064 | 1538082396 |
2018-09-28 06:06:36.064000+09:00 | 6659.0 | Sell | 10 | 1538082396064 | 1538082396 |
2018-09-28 06:06:36.564000+09:00 | 6659.5 | Buy | 25 | 1538082396564 | 1538082396 |
print("UNIX TIME列を削除")
df.drop("msec_unixtime", axis=1, inplace=True)
df.drop("unixtime", axis=1, inplace=True)
display(df.head())
print("出来高(size)をBuy/Sellに分ける")
df["buy_size"] = df["size"].where(df["side"] == "Buy", 0)
df["buy_flag"] = df["side"] == "Buy"
df["sell_size"] = df["size"].where(df["side"] == "Sell", 0)
df["sell_flag"] = df["side"] == "Sell"
display(df.head())
UNIX TIME列を削除
price | side | size | |
---|---|---|---|
timestamp | |||
2018-09-28 06:06:34.013000+09:00 | 6660.0 | Buy | 1819 |
2018-09-28 06:06:35.200000+09:00 | 6660.0 | Buy | 27 |
2018-09-28 06:06:36.064000+09:00 | 6659.5 | Sell | 17 |
2018-09-28 06:06:36.064000+09:00 | 6659.0 | Sell | 10 |
2018-09-28 06:06:36.564000+09:00 | 6659.5 | Buy | 25 |
出来高(size)をBuy/Sellに分ける
price | side | size | buy_size | buy_flag | sell_size | sell_flag | |
---|---|---|---|---|---|---|---|
timestamp | |||||||
2018-09-28 06:06:34.013000+09:00 | 6660.0 | Buy | 1819 | 1819 | True | 0 | False |
2018-09-28 06:06:35.200000+09:00 | 6660.0 | Buy | 27 | 27 | True | 0 | False |
2018-09-28 06:06:36.064000+09:00 | 6659.5 | Sell | 17 | 0 | False | 17 | True |
2018-09-28 06:06:36.064000+09:00 | 6659.0 | Sell | 10 | 0 | False | 10 | True |
2018-09-28 06:06:36.564000+09:00 | 6659.5 | Buy | 25 | 25 | True | 0 | False |
print("1秒足(1S)OHLCVリサンプリング")
df_ohlcv = df.resample("1S").agg({
"price" : "ohlc",
"size" : "sum",
"buy_size" : "sum",
"buy_flag" : "sum",
"sell_size" : "sum",
"sell_flag" : "sum",})
df_ohlcv.columns = \
["open", "high", "low", "close", "volume", "buy_vol", "buy_num", "sell_vol", "sell_num"]
display(df_ohlcv.head())
print("集計した件数をintに変換")
df_ohlcv["buy_num"] = df_ohlcv["buy_num"].astype(int)
df_ohlcv["sell_num"] = df_ohlcv["sell_num"].astype(int)
print("データ欠損を直前価格で補間")
df_ohlcv.ffill(inplace=True)
display(df_ohlcv.head())
1秒足(1S)OHLCVリサンプリング
open | high | low | close | volume | buy_vol | buy_num | sell_vol | sell_num | |
---|---|---|---|---|---|---|---|---|---|
timestamp | |||||||||
2018-09-28 06:06:34+09:00 | 6660.0 | 6660.0 | 6660.0 | 6660.0 | 1819 | 1819 | 1.0 | 0 | 0.0 |
2018-09-28 06:06:35+09:00 | 6660.0 | 6660.0 | 6660.0 | 6660.0 | 27 | 27 | 1.0 | 0 | 0.0 |
2018-09-28 06:06:36+09:00 | 6659.5 | 6660.0 | 6659.0 | 6660.0 | 204443 | 204416 | 8.0 | 27 | 2.0 |
2018-09-28 06:06:37+09:00 | NaN | NaN | NaN | NaN | 0 | 0 | 0.0 | 0 | 0.0 |
2018-09-28 06:06:38+09:00 | NaN | NaN | NaN | NaN | 0 | 0 | 0.0 | 0 | 0.0 |
集計した件数をintに変換 データ欠損を直前価格で補間
open | high | low | close | volume | buy_vol | buy_num | sell_vol | sell_num | |
---|---|---|---|---|---|---|---|---|---|
timestamp | |||||||||
2018-09-28 06:06:34+09:00 | 6660.0 | 6660.0 | 6660.0 | 6660.0 | 1819 | 1819 | 1 | 0 | 0 |
2018-09-28 06:06:35+09:00 | 6660.0 | 6660.0 | 6660.0 | 6660.0 | 27 | 27 | 1 | 0 | 0 |
2018-09-28 06:06:36+09:00 | 6659.5 | 6660.0 | 6659.0 | 6660.0 | 204443 | 204416 | 8 | 27 | 2 |
2018-09-28 06:06:37+09:00 | 6659.5 | 6660.0 | 6659.0 | 6660.0 | 0 | 0 | 0 | 0 | 0 |
2018-09-28 06:06:38+09:00 | 6659.5 | 6660.0 | 6659.0 | 6660.0 | 0 | 0 | 0 | 0 | 0 |
print("集計したBuy / Sell出来高、約定履歴件数から1件あたりの平均出来高を算出")
df_ohlcv["buy_avr"] = np.where(df_ohlcv["buy_num"] == 0, \
df_ohlcv["buy_vol"], df_ohlcv["buy_vol"] / df_ohlcv["buy_num"])
df_ohlcv["sell_avr"] = np.where(df_ohlcv["sell_num"] == 0, \
df_ohlcv["sell_vol"], df_ohlcv["sell_vol"] / df_ohlcv["sell_num"])
print("列並び替え")
df_ohlcv = df_ohlcv[["open", "high", "low", "close", "volume", \
"buy_num", "buy_vol", "buy_avr", "sell_num", "sell_vol", "sell_avr"]]
display(df_ohlcv.head())
集計したBuy / Sell出来高、約定履歴件数から1件あたりの平均出来高を算出 列並び替え
open | high | low | close | volume | buy_num | buy_vol | buy_avr | sell_num | sell_vol | sell_avr | |
---|---|---|---|---|---|---|---|---|---|---|---|
timestamp | |||||||||||
2018-09-28 06:06:34+09:00 | 6660.0 | 6660.0 | 6660.0 | 6660.0 | 1819 | 1 | 1819 | 1819.0 | 0 | 0 | 0.0 |
2018-09-28 06:06:35+09:00 | 6660.0 | 6660.0 | 6660.0 | 6660.0 | 27 | 1 | 27 | 27.0 | 0 | 0 | 0.0 |
2018-09-28 06:06:36+09:00 | 6659.5 | 6660.0 | 6659.0 | 6660.0 | 204443 | 8 | 204416 | 25552.0 | 2 | 27 | 13.5 |
2018-09-28 06:06:37+09:00 | 6659.5 | 6660.0 | 6659.0 | 6660.0 | 0 | 0 | 0 | 0.0 | 0 | 0 | 0.0 |
2018-09-28 06:06:38+09:00 | 6659.5 | 6660.0 | 6659.0 | 6660.0 | 0 | 0 | 0 | 0.0 | 0 | 0 | 0.0 |
print("列名変更")
df_ohlcv = df_ohlcv.rename(columns={
"open": "始値", "high": "高値", "low": "安値", "close": "終値", "volume": "出来高",
"buy_num": "[買]約定数", "buy_vol": "[買]出来高", "buy_avr": "[買]平均約定出来高",
"sell_num": "[売]約定数", "sell_vol": "[売]出来高", "sell_avr": "[売]平均約定出来高"})
display(df_ohlcv.head())
列名変更
始値 | 高値 | 安値 | 終値 | 出来高 | [買]約定数 | [買]出来高 | [買]平均約定出来高 | [売]約定数 | [売]出来高 | [売]平均約定出来高 | |
---|---|---|---|---|---|---|---|---|---|---|---|
timestamp | |||||||||||
2018-09-28 06:06:34+09:00 | 6660.0 | 6660.0 | 6660.0 | 6660.0 | 1819 | 1 | 1819 | 1819.0 | 0 | 0 | 0.0 |
2018-09-28 06:06:35+09:00 | 6660.0 | 6660.0 | 6660.0 | 6660.0 | 27 | 1 | 27 | 27.0 | 0 | 0 | 0.0 |
2018-09-28 06:06:36+09:00 | 6659.5 | 6660.0 | 6659.0 | 6660.0 | 204443 | 8 | 204416 | 25552.0 | 2 | 27 | 13.5 |
2018-09-28 06:06:37+09:00 | 6659.5 | 6660.0 | 6659.0 | 6660.0 | 0 | 0 | 0 | 0.0 | 0 | 0 | 0.0 |
2018-09-28 06:06:38+09:00 | 6659.5 | 6660.0 | 6659.0 | 6660.0 | 0 | 0 | 0 | 0.0 | 0 | 0 | 0.0 |