tardis-process.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import gzip\n",
    "import datetime\n",
    "import pandas as pd\n",
    "from BTrees.OOBTree import OOBTree\n",
    "\n",
    "def replay_L2(filename, target_exchange, target_symbol, fn, fn_timestamps):\n",
    "    with gzip.open(filename) as f:\n",
    "        bid = OOBTree()\n",
    "        ask = OOBTree()\n",
    "        processing_snapshot = False\n",
    "        prev_timestamp = None\n",
    "        i = 0\n",
    "        # Skip header\n",
    "        f.readline()\n",
    "        while True:\n",
    "            line = f.readline()\n",
    "            if not line:\n",
    "                break\n",
    "            exchange, symbol, timestamp, local_timestamp, is_snapshot, side, price, amount = line.decode('utf-8').strip().split(',')\n",
    "            if exchange != target_exchange or symbol != target_symbol:\n",
    "                continue\n",
    "            \n",
    "            local_timestamp = int(local_timestamp)\n",
    "            price = float(price)\n",
    "            if prev_timestamp != local_timestamp:\n",
    "                while local_timestamp > fn_timestamps[i]:\n",
    "                    fn(fn_timestamps[i], prev_timestamp, bid, ask)\n",
    "                    i += 1\n",
    "            prev_timestamp = local_timestamp\n",
    "            \n",
    "            # Reconstruct the market depth\n",
    "            if is_snapshot and not processing_snapshot:\n",
    "                processing_snapshot = True\n",
    "                bid.clear()\n",
    "                ask.clear()\n",
    "            elif not is_snapshot and processing_snapshot:\n",
    "                processing_snapshot = False\n",
    "\n",
    "            if amount != '0':\n",
    "                if side == 'bid':\n",
    "                    bid[price] = int(amount)\n",
    "                else:\n",
    "                    ask[price] = int(amount)\n",
    "            else:\n",
    "                if side == 'bid':\n",
    "                    del bid[price]\n",
    "                else:\n",
    "                    del ask[price]\n",
    "        \n",
    "        for j in range(i, len(fn_timestamps)):\n",
    "            fn(fn_timestamps[j], prev_timestamp, bid, ask)\n",
    "\n",
    "def make_timestamps(since, to, interval):\n",
    "    index = [since.timestamp() * 1000000]\n",
    "    while since < to:\n",
    "        since += datetime.timedelta(seconds=interval)\n",
    "        index.append(since.timestamp() * 1000000)\n",
    "    return index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "since = datetime.datetime(2019, 7, 1, tzinfo=datetime.timezone.utc)\n",
    "to = datetime.datetime(2019, 7, 2, tzinfo=datetime.timezone.utc)\n",
    "timestamps = make_timestamps(since, to, 5)\n",
    "\n",
    "df_imbalance = pd.DataFrame(float('nan'), index=timestamps, columns=['best_bid', 'best_ask', '0.5%', '1%', '2.5%', '5%', '7.5%', '10%', '15%', '20%', '25%'])        \n",
    "def process_L2(callback_timestamp, timestamp, bid, ask):\n",
    "    if bid and ask:\n",
    "        best_bid = bid.maxKey()\n",
    "        best_ask = ask.minKey()\n",
    "        mid = (best_bid + best_ask) / 2\n",
    "        row = [best_bid, best_ask]\n",
    "        for p in [0.005, 0.01, 0.025, 0.05, 0.075, 0.1, 0.15, 0.2, 0.25]:\n",
    "            buy = sum(map(lambda x: x[1], filter(lambda x: x[0] > mid * (1 - p), bid.iteritems())))\n",
    "            sell = sum(map(lambda x: x[1], filter(lambda x: x[0] < mid * (1 + p), ask.iteritems())))\n",
    "            row.append(buy - sell)\n",
    "        df_imbalance.loc[callback_timestamp] = row\n",
    "    \n",
    "#df_imbalance = pd.DataFrame(float('nan'), index=timestamps, columns=['best_bid', 'best_ask', '0.5%', '1%', '2.5%', '5%', '7.5%', '10%', '15%', '20%', '25%'])\n",
    "def process_L2_normalization(callback_timestamp, timestamp, bid, ask):\n",
    "    if bid and ask:\n",
    "        best_bid = bid.maxKey()\n",
    "        best_ask = ask.minKey()\n",
    "        mid = (best_bid + best_ask) / 2\n",
    "        row = [best_bid, best_ask]\n",
    "        for p in [0.005, 0.01, 0.025, 0.05, 0.075, 0.1, 0.15, 0.2, 0.25]:\n",
    "            buy = sum(map(lambda x: x[1], filter(lambda x: x[0] > mid * (1 - p), bid.iteritems())))\n",
    "            sell = sum(map(lambda x: x[1], filter(lambda x: x[0] < mid * (1 + p), ask.iteritems())))\n",
    "            row.append((buy - sell) / (buy + sell))\n",
    "        df_imbalance.loc[callback_timestamp] = row\n",
    "\n",
    "#df_imbalance = pd.DataFrame(float('nan'), index=timestamps, columns=['best_bid', 'best_ask', '50M', '60M', '70M', '80M', '90M', '100M', '110M', '120M', '130M', '140M', '150M'])\n",
    "def process_L2_weighted_price(callback_timestamp, timestamp, bid, ask):\n",
    "    if bid and ask:\n",
    "        best_bid = bid.maxKey()\n",
    "        best_ask = ask.minKey()\n",
    "        mid = (best_bid + best_ask) / 2\n",
    "        row = [best_bid, best_ask]\n",
    "        for q in [50000000, 60000000, 70000000, 80000000, 90000000, 100000000, 110000000, 120000000, 130000000, 140000000, 150000000]:\n",
    "            map_bid = map(lambda x: (x[0], x[1]), sorted(bid.iteritems(), key=lambda x: -x[0]))\n",
    "            map_ask = map(lambda x: (x[0], x[1]), sorted(ask.iteritems(), key=lambda x: x[0]))\n",
    "\n",
    "            df_bid = pd.DataFrame(map_bid, columns=['price', 'size'])\n",
    "            df_bid = df_bid[df_bid['size'].cumsum() <= q]\n",
    "\n",
    "            df_ask = pd.DataFrame(map_ask, columns=['price', 'size'])\n",
    "            df_ask = df_ask[df_ask['size'].cumsum() <= q]\n",
    "\n",
    "            row.append(((df_bid['price'] * df_bid['size']).sum() + (df_ask['price'] * df_ask['size']).sum()) \\\n",
    "                       / (df_bid['size'].sum() + df_ask['size'].sum()))\n",
    "\n",
    "        df_imbalance.loc[callback_timestamp] = row\n",
    "\n",
    "# Download CSV files from https://docs.tardis.dev/historical-data-details/\n",
    "replay_L2('bitmex_incremental_book_L2_2019-07-01_XBTUSD.csv.gz', 'bitmex', 'XBTUSD', process_L2, timestamps)\n",
    "# replay_L2('bitmex_incremental_book_L2_2019-07-01_XBTUSD.csv.gz', 'bitmex', 'XBTUSD', process_L2_normalization, timestamps)\n",
    "# replay_L2('bitmex_incremental_book_L2_2019-07-01_XBTUSD.csv.gz', 'bitmex', 'XBTUSD', process_L2_weighted_price, timestamps)\n",
    "\n",
    "df_trades = pd.read_csv('bitmex_trades_2019-07-01_XBTUSD.csv.gz', compression='gzip', header=0, sep=',')\n",
    "df_trades = df_trades[(df_trades['exchange'] == 'bitmex') & (df_trades['symbol'] == 'XBTUSD')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_imbalance.index = pd.to_datetime(df_imbalance.index, unit='us')\n",
    "df_imbalance.index.name = 'local_timestamp'\n",
    "\n",
    "df_trades.index = pd.to_datetime(df_trades['local_timestamp'], unit='us')\n",
    "ohlcv = df_trades.resample('5s', closed='right', label='right').agg({'price': 'ohlc', 'amount': 'sum'})\n",
    "ohlcv.columns = ['open', 'high', 'low', 'close', 'volume']\n",
    "closes = ohlcv['close'].fillna(method='pad')\n",
    "ohlcv = ohlcv.apply(lambda x: x.fillna(closes))\n",
    "\n",
    "df = ohlcv.merge(df_imbalance, left_on='local_timestamp', right_on='local_timestamp', how='outer', sort=True)\n",
    "\n",
    "df = df.iloc[2:]\n",
    "\n",
    "df.to_pickle('imbalance')\n",
    "df.to_csv('imbalance.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>best_bid</th>\n",
       "      <th>best_ask</th>\n",
       "      <th>0.5%</th>\n",
       "      <th>1%</th>\n",
       "      <th>2.5%</th>\n",
       "      <th>5%</th>\n",
       "      <th>7.5%</th>\n",
       "      <th>10%</th>\n",
       "      <th>15%</th>\n",
       "      <th>20%</th>\n",
       "      <th>25%</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>local_timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-07-01 00:00:10</th>\n",
       "      <td>10760.5</td>\n",
       "      <td>10761.0</td>\n",
       "      <td>10760.5</td>\n",
       "      <td>10760.5</td>\n",
       "      <td>56023.0</td>\n",
       "      <td>10760.5</td>\n",
       "      <td>10761.0</td>\n",
       "      <td>-1389441.0</td>\n",
       "      <td>-999943.0</td>\n",
       "      <td>48096826.0</td>\n",
       "      <td>100604222.0</td>\n",
       "      <td>108973952.0</td>\n",
       "      <td>117865096.0</td>\n",
       "      <td>152406830.0</td>\n",
       "      <td>162300964.0</td>\n",
       "      <td>167728898.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 00:00:15</th>\n",
       "      <td>10761.0</td>\n",
       "      <td>10764.5</td>\n",
       "      <td>10760.5</td>\n",
       "      <td>10762.5</td>\n",
       "      <td>926323.0</td>\n",
       "      <td>10762.0</td>\n",
       "      <td>10762.5</td>\n",
       "      <td>1571316.0</td>\n",
       "      <td>1703027.0</td>\n",
       "      <td>48454239.0</td>\n",
       "      <td>100633691.0</td>\n",
       "      <td>109541316.0</td>\n",
       "      <td>118348535.0</td>\n",
       "      <td>153029937.0</td>\n",
       "      <td>162910907.0</td>\n",
       "      <td>168437137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 00:00:20</th>\n",
       "      <td>10762.5</td>\n",
       "      <td>10772.0</td>\n",
       "      <td>10762.0</td>\n",
       "      <td>10770.0</td>\n",
       "      <td>1244732.0</td>\n",
       "      <td>10767.0</td>\n",
       "      <td>10769.0</td>\n",
       "      <td>224234.0</td>\n",
       "      <td>-1414694.0</td>\n",
       "      <td>46353982.0</td>\n",
       "      <td>98992727.0</td>\n",
       "      <td>107035431.0</td>\n",
       "      <td>116391257.0</td>\n",
       "      <td>151149733.0</td>\n",
       "      <td>160812336.0</td>\n",
       "      <td>167064389.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 00:00:25</th>\n",
       "      <td>10770.0</td>\n",
       "      <td>10770.5</td>\n",
       "      <td>10760.5</td>\n",
       "      <td>10760.5</td>\n",
       "      <td>1091694.0</td>\n",
       "      <td>10760.5</td>\n",
       "      <td>10761.0</td>\n",
       "      <td>-4498538.0</td>\n",
       "      <td>-3595487.0</td>\n",
       "      <td>42191754.0</td>\n",
       "      <td>94831371.0</td>\n",
       "      <td>103322263.0</td>\n",
       "      <td>112142075.0</td>\n",
       "      <td>146712372.0</td>\n",
       "      <td>156624290.0</td>\n",
       "      <td>161995052.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 00:00:30</th>\n",
       "      <td>10761.5</td>\n",
       "      <td>10761.5</td>\n",
       "      <td>10750.0</td>\n",
       "      <td>10750.5</td>\n",
       "      <td>1451809.0</td>\n",
       "      <td>10750.0</td>\n",
       "      <td>10750.5</td>\n",
       "      <td>-726613.0</td>\n",
       "      <td>2568195.0</td>\n",
       "      <td>47327022.0</td>\n",
       "      <td>97825154.0</td>\n",
       "      <td>108057793.0</td>\n",
       "      <td>117547053.0</td>\n",
       "      <td>149456366.0</td>\n",
       "      <td>159482185.0</td>\n",
       "      <td>164839505.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 23:59:40</th>\n",
       "      <td>10571.0</td>\n",
       "      <td>10571.0</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>68201.0</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>10571.0</td>\n",
       "      <td>83610.0</td>\n",
       "      <td>-3948019.0</td>\n",
       "      <td>-30913821.0</td>\n",
       "      <td>-62871067.0</td>\n",
       "      <td>-29889465.0</td>\n",
       "      <td>17574724.0</td>\n",
       "      <td>83354457.0</td>\n",
       "      <td>114446177.0</td>\n",
       "      <td>120950296.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 23:59:45</th>\n",
       "      <td>10570.5</td>\n",
       "      <td>10571.0</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>54755.0</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>10571.0</td>\n",
       "      <td>180408.0</td>\n",
       "      <td>-3625694.0</td>\n",
       "      <td>-30847722.0</td>\n",
       "      <td>-62804968.0</td>\n",
       "      <td>-29755246.0</td>\n",
       "      <td>17640914.0</td>\n",
       "      <td>83440647.0</td>\n",
       "      <td>114532367.0</td>\n",
       "      <td>121036486.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 23:59:50</th>\n",
       "      <td>10570.5</td>\n",
       "      <td>10571.0</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>11704.0</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>10571.0</td>\n",
       "      <td>222568.0</td>\n",
       "      <td>-3743971.0</td>\n",
       "      <td>-30961426.0</td>\n",
       "      <td>-62851820.0</td>\n",
       "      <td>-29869566.0</td>\n",
       "      <td>17529659.0</td>\n",
       "      <td>83329442.0</td>\n",
       "      <td>114421162.0</td>\n",
       "      <td>120925281.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 23:59:55</th>\n",
       "      <td>10571.0</td>\n",
       "      <td>10571.0</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>7107.0</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>10571.0</td>\n",
       "      <td>39758.0</td>\n",
       "      <td>-3951059.0</td>\n",
       "      <td>-31297108.0</td>\n",
       "      <td>-63188036.0</td>\n",
       "      <td>-30205079.0</td>\n",
       "      <td>17191607.0</td>\n",
       "      <td>82991390.0</td>\n",
       "      <td>114088310.0</td>\n",
       "      <td>120592429.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-02 00:00:00</th>\n",
       "      <td>10571.0</td>\n",
       "      <td>10571.0</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>43120.0</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>10571.0</td>\n",
       "      <td>-1200977.0</td>\n",
       "      <td>-5164632.0</td>\n",
       "      <td>-32452235.0</td>\n",
       "      <td>-64343049.0</td>\n",
       "      <td>-31360128.0</td>\n",
       "      <td>16037020.0</td>\n",
       "      <td>81836753.0</td>\n",
       "      <td>112933673.0</td>\n",
       "      <td>119437792.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17279 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        open     high      low    close     volume  best_bid  \\\n",
       "local_timestamp                                                                \n",
       "2019-07-01 00:00:10  10760.5  10761.0  10760.5  10760.5    56023.0   10760.5   \n",
       "2019-07-01 00:00:15  10761.0  10764.5  10760.5  10762.5   926323.0   10762.0   \n",
       "2019-07-01 00:00:20  10762.5  10772.0  10762.0  10770.0  1244732.0   10767.0   \n",
       "2019-07-01 00:00:25  10770.0  10770.5  10760.5  10760.5  1091694.0   10760.5   \n",
       "2019-07-01 00:00:30  10761.5  10761.5  10750.0  10750.5  1451809.0   10750.0   \n",
       "...                      ...      ...      ...      ...        ...       ...   \n",
       "2019-07-01 23:59:40  10571.0  10571.0  10570.5  10570.5    68201.0   10570.5   \n",
       "2019-07-01 23:59:45  10570.5  10571.0  10570.5  10570.5    54755.0   10570.5   \n",
       "2019-07-01 23:59:50  10570.5  10571.0  10570.5  10570.5    11704.0   10570.5   \n",
       "2019-07-01 23:59:55  10571.0  10571.0  10570.5  10570.5     7107.0   10570.5   \n",
       "2019-07-02 00:00:00  10571.0  10571.0  10570.5  10570.5    43120.0   10570.5   \n",
       "\n",
       "                     best_ask       0.5%         1%        2.5%           5%  \\\n",
       "local_timestamp                                                                \n",
       "2019-07-01 00:00:10   10761.0 -1389441.0  -999943.0  48096826.0  100604222.0   \n",
       "2019-07-01 00:00:15   10762.5  1571316.0  1703027.0  48454239.0  100633691.0   \n",
       "2019-07-01 00:00:20   10769.0   224234.0 -1414694.0  46353982.0   98992727.0   \n",
       "2019-07-01 00:00:25   10761.0 -4498538.0 -3595487.0  42191754.0   94831371.0   \n",
       "2019-07-01 00:00:30   10750.5  -726613.0  2568195.0  47327022.0   97825154.0   \n",
       "...                       ...        ...        ...         ...          ...   \n",
       "2019-07-01 23:59:40   10571.0    83610.0 -3948019.0 -30913821.0  -62871067.0   \n",
       "2019-07-01 23:59:45   10571.0   180408.0 -3625694.0 -30847722.0  -62804968.0   \n",
       "2019-07-01 23:59:50   10571.0   222568.0 -3743971.0 -30961426.0  -62851820.0   \n",
       "2019-07-01 23:59:55   10571.0    39758.0 -3951059.0 -31297108.0  -63188036.0   \n",
       "2019-07-02 00:00:00   10571.0 -1200977.0 -5164632.0 -32452235.0  -64343049.0   \n",
       "\n",
       "                            7.5%          10%          15%          20%  \\\n",
       "local_timestamp                                                           \n",
       "2019-07-01 00:00:10  108973952.0  117865096.0  152406830.0  162300964.0   \n",
       "2019-07-01 00:00:15  109541316.0  118348535.0  153029937.0  162910907.0   \n",
       "2019-07-01 00:00:20  107035431.0  116391257.0  151149733.0  160812336.0   \n",
       "2019-07-01 00:00:25  103322263.0  112142075.0  146712372.0  156624290.0   \n",
       "2019-07-01 00:00:30  108057793.0  117547053.0  149456366.0  159482185.0   \n",
       "...                          ...          ...          ...          ...   \n",
       "2019-07-01 23:59:40  -29889465.0   17574724.0   83354457.0  114446177.0   \n",
       "2019-07-01 23:59:45  -29755246.0   17640914.0   83440647.0  114532367.0   \n",
       "2019-07-01 23:59:50  -29869566.0   17529659.0   83329442.0  114421162.0   \n",
       "2019-07-01 23:59:55  -30205079.0   17191607.0   82991390.0  114088310.0   \n",
       "2019-07-02 00:00:00  -31360128.0   16037020.0   81836753.0  112933673.0   \n",
       "\n",
       "                             25%  \n",
       "local_timestamp                   \n",
       "2019-07-01 00:00:10  167728898.0  \n",
       "2019-07-01 00:00:15  168437137.0  \n",
       "2019-07-01 00:00:20  167064389.0  \n",
       "2019-07-01 00:00:25  161995052.0  \n",
       "2019-07-01 00:00:30  164839505.0  \n",
       "...                          ...  \n",
       "2019-07-01 23:59:40  120950296.0  \n",
       "2019-07-01 23:59:45  121036486.0  \n",
       "2019-07-01 23:59:50  120925281.0  \n",
       "2019-07-01 23:59:55  120592429.0  \n",
       "2019-07-02 00:00:00  119437792.0  \n",
       "\n",
       "[17279 rows x 16 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>exchange</th>\n",
       "      <th>symbol</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>local_timestamp</th>\n",
       "      <th>id</th>\n",
       "      <th>side</th>\n",
       "      <th>price</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>local_timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-07-01 00:00:05.934605</th>\n",
       "      <td>bitmex</td>\n",
       "      <td>XBTUSD</td>\n",
       "      <td>1561939203820000</td>\n",
       "      <td>1561939205934605</td>\n",
       "      <td>95e052c9-d8ed-1b02-2ea1-a47eb3337432</td>\n",
       "      <td>sell</td>\n",
       "      <td>10760.5</td>\n",
       "      <td>17089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 00:00:05.950839</th>\n",
       "      <td>bitmex</td>\n",
       "      <td>XBTUSD</td>\n",
       "      <td>1561939203850000</td>\n",
       "      <td>1561939205950839</td>\n",
       "      <td>d6cc5a30-d0b0-44f0-4259-2ae73021c2f7</td>\n",
       "      <td>buy</td>\n",
       "      <td>10761.0</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 00:00:05.964131</th>\n",
       "      <td>bitmex</td>\n",
       "      <td>XBTUSD</td>\n",
       "      <td>1561939203880000</td>\n",
       "      <td>1561939205964131</td>\n",
       "      <td>5a794de6-c89a-36d9-d42f-0ff9c314ae11</td>\n",
       "      <td>buy</td>\n",
       "      <td>10761.0</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 00:00:05.981308</th>\n",
       "      <td>bitmex</td>\n",
       "      <td>XBTUSD</td>\n",
       "      <td>1561939203916000</td>\n",
       "      <td>1561939205981308</td>\n",
       "      <td>c7a917d7-b584-e269-3125-0707c3299a0d</td>\n",
       "      <td>buy</td>\n",
       "      <td>10761.0</td>\n",
       "      <td>1076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 00:00:06.003553</th>\n",
       "      <td>bitmex</td>\n",
       "      <td>XBTUSD</td>\n",
       "      <td>1561939203962000</td>\n",
       "      <td>1561939206003553</td>\n",
       "      <td>896a073d-6cf8-437d-0247-e832db00d1a7</td>\n",
       "      <td>sell</td>\n",
       "      <td>10760.5</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 23:59:59.213046</th>\n",
       "      <td>bitmex</td>\n",
       "      <td>XBTUSD</td>\n",
       "      <td>1562025599200000</td>\n",
       "      <td>1562025599213046</td>\n",
       "      <td>3abf4dd3-6cac-b474-459e-0695eb824bc1</td>\n",
       "      <td>sell</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 23:59:59.213046</th>\n",
       "      <td>bitmex</td>\n",
       "      <td>XBTUSD</td>\n",
       "      <td>1562025599200000</td>\n",
       "      <td>1562025599213046</td>\n",
       "      <td>e856d3cb-75bc-94b6-6efa-ae95228a5cab</td>\n",
       "      <td>sell</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 23:59:59.213046</th>\n",
       "      <td>bitmex</td>\n",
       "      <td>XBTUSD</td>\n",
       "      <td>1562025599200000</td>\n",
       "      <td>1562025599213046</td>\n",
       "      <td>68bb2b5d-7026-4dc7-79d6-23435e26c8a9</td>\n",
       "      <td>sell</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>1832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 23:59:59.222025</th>\n",
       "      <td>bitmex</td>\n",
       "      <td>XBTUSD</td>\n",
       "      <td>1562025599204000</td>\n",
       "      <td>1562025599222025</td>\n",
       "      <td>d8370369-91a8-e349-a662-58392c66c62e</td>\n",
       "      <td>sell</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01 23:59:59.883881</th>\n",
       "      <td>bitmex</td>\n",
       "      <td>XBTUSD</td>\n",
       "      <td>1562025599864000</td>\n",
       "      <td>1562025599883881</td>\n",
       "      <td>00f6ba74-beee-3b97-f0d2-0b7992ccd22a</td>\n",
       "      <td>sell</td>\n",
       "      <td>10570.5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1460963 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           exchange  symbol         timestamp  \\\n",
       "local_timestamp                                                 \n",
       "2019-07-01 00:00:05.934605   bitmex  XBTUSD  1561939203820000   \n",
       "2019-07-01 00:00:05.950839   bitmex  XBTUSD  1561939203850000   \n",
       "2019-07-01 00:00:05.964131   bitmex  XBTUSD  1561939203880000   \n",
       "2019-07-01 00:00:05.981308   bitmex  XBTUSD  1561939203916000   \n",
       "2019-07-01 00:00:06.003553   bitmex  XBTUSD  1561939203962000   \n",
       "...                             ...     ...               ...   \n",
       "2019-07-01 23:59:59.213046   bitmex  XBTUSD  1562025599200000   \n",
       "2019-07-01 23:59:59.213046   bitmex  XBTUSD  1562025599200000   \n",
       "2019-07-01 23:59:59.213046   bitmex  XBTUSD  1562025599200000   \n",
       "2019-07-01 23:59:59.222025   bitmex  XBTUSD  1562025599204000   \n",
       "2019-07-01 23:59:59.883881   bitmex  XBTUSD  1562025599864000   \n",
       "\n",
       "                             local_timestamp  \\\n",
       "local_timestamp                                \n",
       "2019-07-01 00:00:05.934605  1561939205934605   \n",
       "2019-07-01 00:00:05.950839  1561939205950839   \n",
       "2019-07-01 00:00:05.964131  1561939205964131   \n",
       "2019-07-01 00:00:05.981308  1561939205981308   \n",
       "2019-07-01 00:00:06.003553  1561939206003553   \n",
       "...                                      ...   \n",
       "2019-07-01 23:59:59.213046  1562025599213046   \n",
       "2019-07-01 23:59:59.213046  1562025599213046   \n",
       "2019-07-01 23:59:59.213046  1562025599213046   \n",
       "2019-07-01 23:59:59.222025  1562025599222025   \n",
       "2019-07-01 23:59:59.883881  1562025599883881   \n",
       "\n",
       "                                                              id  side  \\\n",
       "local_timestamp                                                          \n",
       "2019-07-01 00:00:05.934605  95e052c9-d8ed-1b02-2ea1-a47eb3337432  sell   \n",
       "2019-07-01 00:00:05.950839  d6cc5a30-d0b0-44f0-4259-2ae73021c2f7   buy   \n",
       "2019-07-01 00:00:05.964131  5a794de6-c89a-36d9-d42f-0ff9c314ae11   buy   \n",
       "2019-07-01 00:00:05.981308  c7a917d7-b584-e269-3125-0707c3299a0d   buy   \n",
       "2019-07-01 00:00:06.003553  896a073d-6cf8-437d-0247-e832db00d1a7  sell   \n",
       "...                                                          ...   ...   \n",
       "2019-07-01 23:59:59.213046  3abf4dd3-6cac-b474-459e-0695eb824bc1  sell   \n",
       "2019-07-01 23:59:59.213046  e856d3cb-75bc-94b6-6efa-ae95228a5cab  sell   \n",
       "2019-07-01 23:59:59.213046  68bb2b5d-7026-4dc7-79d6-23435e26c8a9  sell   \n",
       "2019-07-01 23:59:59.222025  d8370369-91a8-e349-a662-58392c66c62e  sell   \n",
       "2019-07-01 23:59:59.883881  00f6ba74-beee-3b97-f0d2-0b7992ccd22a  sell   \n",
       "\n",
       "                              price  amount  \n",
       "local_timestamp                              \n",
       "2019-07-01 00:00:05.934605  10760.5   17089  \n",
       "2019-07-01 00:00:05.950839  10761.0    1000  \n",
       "2019-07-01 00:00:05.964131  10761.0    2000  \n",
       "2019-07-01 00:00:05.981308  10761.0    1076  \n",
       "2019-07-01 00:00:06.003553  10760.5    1000  \n",
       "...                             ...     ...  \n",
       "2019-07-01 23:59:59.213046  10570.5      25  \n",
       "2019-07-01 23:59:59.213046  10570.5      33  \n",
       "2019-07-01 23:59:59.213046  10570.5    1832  \n",
       "2019-07-01 23:59:59.222025  10570.5       1  \n",
       "2019-07-01 23:59:59.883881  10570.5       1  \n",
       "\n",
       "[1460963 rows x 8 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}