Data_Filtering.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Using TensorFlow backend.\n"
     ]
    }
   ],
   "source": [
    "import keras\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data available: snapshot, ticker and trades\n",
    "\n",
    "Snapshot (100ms and 10s): gives the state of the orderbook at specified time interval. Includes 100 non-zero levels, 50 ask  50 bid, and their quantities.\n",
    "\n",
    "Ticker: gives the bid and ask price at each timestamp for which a trade has been made\n",
    "\n",
    "Trades: gives the time, side, price and quantity of every trade"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_snapshot = pd.read_parquet(r\"Data\\coinbase_btc_usd\\coinbase\\btc_usd\\l2_snapshots\\100ms\\coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_0000_0100.parquet\")\n",
    "df_ticker = pd.read_parquet(r'Data\\coinbase_btc_usd\\coinbase\\btc_usd\\ticker\\coinbase_btc_usd_ticker_2019_11_12.parquet')\n",
    "df_trades = pd.read_parquet(r'Data\\coinbase_btc_usd\\coinbase\\btc_usd\\trades\\coinbase_btc_usd_trades_2019_11_12.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "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>b1</th>\n",
       "      <th>b2</th>\n",
       "      <th>b3</th>\n",
       "      <th>b4</th>\n",
       "      <th>b5</th>\n",
       "      <th>b6</th>\n",
       "      <th>b7</th>\n",
       "      <th>b8</th>\n",
       "      <th>b9</th>\n",
       "      <th>b10</th>\n",
       "      <th>...</th>\n",
       "      <th>aq41</th>\n",
       "      <th>aq42</th>\n",
       "      <th>aq43</th>\n",
       "      <th>aq44</th>\n",
       "      <th>aq45</th>\n",
       "      <th>aq46</th>\n",
       "      <th>aq47</th>\n",
       "      <th>aq48</th>\n",
       "      <th>aq49</th>\n",
       "      <th>aq50</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>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",
       "      <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-11-12 00:00:00.000</th>\n",
       "      <td>8721.53</td>\n",
       "      <td>8720.59</td>\n",
       "      <td>8719.55</td>\n",
       "      <td>8719.50</td>\n",
       "      <td>8719.0</td>\n",
       "      <td>8718.02</td>\n",
       "      <td>8717.87</td>\n",
       "      <td>8717.85</td>\n",
       "      <td>8717.56</td>\n",
       "      <td>8716.06</td>\n",
       "      <td>...</td>\n",
       "      <td>8.200000</td>\n",
       "      <td>2.203</td>\n",
       "      <td>2.600</td>\n",
       "      <td>0.005</td>\n",
       "      <td>2.500</td>\n",
       "      <td>0.896321</td>\n",
       "      <td>0.766000</td>\n",
       "      <td>0.001737</td>\n",
       "      <td>1.820000</td>\n",
       "      <td>0.933419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-12 00:00:00.100</th>\n",
       "      <td>8721.53</td>\n",
       "      <td>8720.59</td>\n",
       "      <td>8719.56</td>\n",
       "      <td>8719.55</td>\n",
       "      <td>8719.0</td>\n",
       "      <td>8718.02</td>\n",
       "      <td>8717.87</td>\n",
       "      <td>8717.85</td>\n",
       "      <td>8717.56</td>\n",
       "      <td>8716.06</td>\n",
       "      <td>...</td>\n",
       "      <td>8.200000</td>\n",
       "      <td>2.203</td>\n",
       "      <td>2.600</td>\n",
       "      <td>0.005</td>\n",
       "      <td>2.500</td>\n",
       "      <td>0.896321</td>\n",
       "      <td>0.766000</td>\n",
       "      <td>0.001737</td>\n",
       "      <td>1.820000</td>\n",
       "      <td>0.933419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-12 00:00:00.200</th>\n",
       "      <td>8721.53</td>\n",
       "      <td>8720.59</td>\n",
       "      <td>8719.61</td>\n",
       "      <td>8719.56</td>\n",
       "      <td>8719.0</td>\n",
       "      <td>8718.02</td>\n",
       "      <td>8717.87</td>\n",
       "      <td>8717.85</td>\n",
       "      <td>8716.06</td>\n",
       "      <td>8716.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.009135</td>\n",
       "      <td>8.200</td>\n",
       "      <td>2.203</td>\n",
       "      <td>2.600</td>\n",
       "      <td>0.005</td>\n",
       "      <td>2.500000</td>\n",
       "      <td>0.896321</td>\n",
       "      <td>0.766000</td>\n",
       "      <td>0.001737</td>\n",
       "      <td>1.820000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-12 00:00:00.300</th>\n",
       "      <td>8721.53</td>\n",
       "      <td>8720.59</td>\n",
       "      <td>8719.61</td>\n",
       "      <td>8719.56</td>\n",
       "      <td>8719.0</td>\n",
       "      <td>8718.02</td>\n",
       "      <td>8718.00</td>\n",
       "      <td>8717.87</td>\n",
       "      <td>8717.85</td>\n",
       "      <td>8716.60</td>\n",
       "      <td>...</td>\n",
       "      <td>0.009135</td>\n",
       "      <td>8.200</td>\n",
       "      <td>2.203</td>\n",
       "      <td>2.600</td>\n",
       "      <td>0.005</td>\n",
       "      <td>2.500000</td>\n",
       "      <td>0.896321</td>\n",
       "      <td>0.766000</td>\n",
       "      <td>0.001737</td>\n",
       "      <td>1.820000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-12 00:00:00.400</th>\n",
       "      <td>8721.53</td>\n",
       "      <td>8720.59</td>\n",
       "      <td>8719.61</td>\n",
       "      <td>8719.56</td>\n",
       "      <td>8719.0</td>\n",
       "      <td>8718.99</td>\n",
       "      <td>8718.02</td>\n",
       "      <td>8718.00</td>\n",
       "      <td>8717.87</td>\n",
       "      <td>8717.85</td>\n",
       "      <td>...</td>\n",
       "      <td>0.009135</td>\n",
       "      <td>8.200</td>\n",
       "      <td>2.203</td>\n",
       "      <td>2.600</td>\n",
       "      <td>0.005</td>\n",
       "      <td>2.500000</td>\n",
       "      <td>0.896321</td>\n",
       "      <td>0.766000</td>\n",
       "      <td>0.001737</td>\n",
       "      <td>1.820000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 200 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                              b1       b2       b3       b4      b5       b6  \\\n",
       "timestamp                                                                      \n",
       "2019-11-12 00:00:00.000  8721.53  8720.59  8719.55  8719.50  8719.0  8718.02   \n",
       "2019-11-12 00:00:00.100  8721.53  8720.59  8719.56  8719.55  8719.0  8718.02   \n",
       "2019-11-12 00:00:00.200  8721.53  8720.59  8719.61  8719.56  8719.0  8718.02   \n",
       "2019-11-12 00:00:00.300  8721.53  8720.59  8719.61  8719.56  8719.0  8718.02   \n",
       "2019-11-12 00:00:00.400  8721.53  8720.59  8719.61  8719.56  8719.0  8718.99   \n",
       "\n",
       "                              b7       b8       b9      b10  ...      aq41  \\\n",
       "timestamp                                                    ...             \n",
       "2019-11-12 00:00:00.000  8717.87  8717.85  8717.56  8716.06  ...  8.200000   \n",
       "2019-11-12 00:00:00.100  8717.87  8717.85  8717.56  8716.06  ...  8.200000   \n",
       "2019-11-12 00:00:00.200  8717.87  8717.85  8716.06  8716.00  ...  0.009135   \n",
       "2019-11-12 00:00:00.300  8718.00  8717.87  8717.85  8716.60  ...  0.009135   \n",
       "2019-11-12 00:00:00.400  8718.02  8718.00  8717.87  8717.85  ...  0.009135   \n",
       "\n",
       "                          aq42   aq43   aq44   aq45      aq46      aq47  \\\n",
       "timestamp                                                                 \n",
       "2019-11-12 00:00:00.000  2.203  2.600  0.005  2.500  0.896321  0.766000   \n",
       "2019-11-12 00:00:00.100  2.203  2.600  0.005  2.500  0.896321  0.766000   \n",
       "2019-11-12 00:00:00.200  8.200  2.203  2.600  0.005  2.500000  0.896321   \n",
       "2019-11-12 00:00:00.300  8.200  2.203  2.600  0.005  2.500000  0.896321   \n",
       "2019-11-12 00:00:00.400  8.200  2.203  2.600  0.005  2.500000  0.896321   \n",
       "\n",
       "                             aq48      aq49      aq50  \n",
       "timestamp                                              \n",
       "2019-11-12 00:00:00.000  0.001737  1.820000  0.933419  \n",
       "2019-11-12 00:00:00.100  0.001737  1.820000  0.933419  \n",
       "2019-11-12 00:00:00.200  0.766000  0.001737  1.820000  \n",
       "2019-11-12 00:00:00.300  0.766000  0.001737  1.820000  \n",
       "2019-11-12 00:00:00.400  0.766000  0.001737  1.820000  \n",
       "\n",
       "[5 rows x 200 columns]"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_snapshot.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example Bid Price, Moving Up"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Neural network modelling p(Y = y1 |Y >= y1, X=x)\n",
    "\n",
    "There can be zero levels both in the spread as well as in the bid and ask sides. Furthermore, many changes are happening within our 100ms snapshot window. I thus see two ways by which to calculate the price level change.\n",
    "\n",
    "#### 1.\n",
    "As the price is to 0.01 accuracy one can simply take the difference in the best bid price and multiply by 100 to get it in the terms of how many $0.01$ levels it has increased by.\n",
    "\n",
    "#### 2.\n",
    "Or one can approximate the price level change by taking that which is closest to the current non-zero levels present.\n",
    " e.g. if the bid price moves up in the next step to 5.20 and at the current time step there are ask price levels at 5.15, 5.18, 5.22, ... then there will be a price level increase of 2 levels. This seems somewhat more arbitrary and heavily dependent on the current state of the orderbook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each snapshot file contains one hour of data at 100ms time interval. We will combine all this data into four separate dataframes (one to demonstrate the principles). One for where the bid price has increased, one where it has decreased and the same for the ask price. Thus for the example case of bid price increasing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_0000_0100.parquet\n",
      "Non na entries: 28547\n",
      "Entries left: 3589\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_0100_0200.parquet\n",
      "Non na entries: 29365\n",
      "Entries left: 4635\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_0200_0300.parquet\n",
      "Non na entries: 28656\n",
      "Entries left: 3228\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_0300_0400.parquet\n",
      "Non na entries: 30871\n",
      "Entries left: 4663\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_0400_0500.parquet\n",
      "Non na entries: 29187\n",
      "Entries left: 3622\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_0500_0600.parquet\n",
      "Non na entries: 31956\n",
      "Entries left: 5930\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_0600_0700.parquet\n",
      "Non na entries: 23169\n",
      "Entries left: 3822\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_0700_0800.parquet\n",
      "Non na entries: 22659\n",
      "Entries left: 1894\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_0800_0900.parquet\n",
      "Non na entries: 19565\n",
      "Entries left: 1055\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_0900_1000.parquet\n",
      "Non na entries: 22605\n",
      "Entries left: 1782\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_1000_1100.parquet\n",
      "Non na entries: 17245\n",
      "Entries left: 1046\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_1100_1200.parquet\n",
      "Non na entries: 20052\n",
      "Entries left: 2119\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_1200_1300.parquet\n",
      "Non na entries: 31358\n",
      "Entries left: 4601\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_1300_1400.parquet\n",
      "Non na entries: 26075\n",
      "Entries left: 2978\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_1400_1500.parquet\n",
      "Non na entries: 22544\n",
      "Entries left: 1559\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_1500_1600.parquet\n",
      "Non na entries: 28214\n",
      "Entries left: 4909\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_1600_1700.parquet\n",
      "Non na entries: 25146\n",
      "Entries left: 2304\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_1700_1800.parquet\n",
      "Non na entries: 20224\n",
      "Entries left: 1736\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_1800_1900.parquet\n",
      "Non na entries: 24429\n",
      "Entries left: 1598\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_1900_2000.parquet\n",
      "Non na entries: 27789\n",
      "Entries left: 2517\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_2000_2100.parquet\n",
      "Non na entries: 27963\n",
      "Entries left: 3174\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_2100_2200.parquet\n",
      "Non na entries: 27390\n",
      "Entries left: 3614\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_2200_2300.parquet\n",
      "Non na entries: 26435\n",
      "Entries left: 2435\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_12_2300_0000.parquet\n",
      "Non na entries: 27631\n",
      "Entries left: 3604\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_0000_0100.parquet\n",
      "Non na entries: 28699\n",
      "Entries left: 5762\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_0100_0200.parquet\n",
      "Non na entries: 27539\n",
      "Entries left: 1893\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_0200_0300.parquet\n",
      "Non na entries: 22991\n",
      "Entries left: 2141\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_0300_0400.parquet\n",
      "Non na entries: 26243\n",
      "Entries left: 1950\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_0400_0500.parquet\n",
      "Non na entries: 30195\n",
      "Entries left: 1472\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_0500_0600.parquet\n",
      "Non na entries: 23088\n",
      "Entries left: 3635\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_0600_0700.parquet\n",
      "Non na entries: 25195\n",
      "Entries left: 2649\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_0700_0800.parquet\n",
      "Non na entries: 28168\n",
      "Entries left: 872\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_0800_0900.parquet\n",
      "Non na entries: 28398\n",
      "Entries left: 451\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_0900_1000.parquet\n",
      "Non na entries: 22992\n",
      "Entries left: 464\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_1000_1100.parquet\n",
      "Non na entries: 26048\n",
      "Entries left: 855\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_1100_1200.parquet\n",
      "Non na entries: 27290\n",
      "Entries left: 1647\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_1200_1300.parquet\n",
      "Non na entries: 25123\n",
      "Entries left: 1035\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_1300_1400.parquet\n",
      "Non na entries: 22972\n",
      "Entries left: 1174\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_1400_1500.parquet\n",
      "Non na entries: 26767\n",
      "Entries left: 872\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_1500_1600.parquet\n",
      "Non na entries: 26669\n",
      "Entries left: 527\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_1600_1700.parquet\n",
      "Non na entries: 23612\n",
      "Entries left: 492\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_1700_1800.parquet\n",
      "Non na entries: 27038\n",
      "Entries left: 838\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_1800_1900.parquet\n",
      "Non na entries: 22585\n",
      "Entries left: 1282\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_1900_2000.parquet\n",
      "Non na entries: 25196\n",
      "Entries left: 848\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_2000_2100.parquet\n",
      "Non na entries: 20146\n",
      "Entries left: 740\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_2100_2200.parquet\n",
      "Non na entries: 16088\n",
      "Entries left: 608\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_2200_2300.parquet\n",
      "Non na entries: 17470\n",
      "Entries left: 1683\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_13_2300_0000.parquet\n",
      "Non na entries: 21760\n",
      "Entries left: 2280\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_0000_0100.parquet\n",
      "Non na entries: 26976\n",
      "Entries left: 1131\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_0100_0200.parquet\n",
      "Non na entries: 24938\n",
      "Entries left: 1166\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_0200_0300.parquet\n",
      "Non na entries: 27933\n",
      "Entries left: 1484\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_0300_0400.parquet\n",
      "Non na entries: 25346\n",
      "Entries left: 1344\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_0400_0500.parquet\n",
      "Non na entries: 32060\n",
      "Entries left: 2933\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_0500_0600.parquet\n",
      "Non na entries: 30437\n",
      "Entries left: 5557\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_0600_0700.parquet\n",
      "Non na entries: 26509\n",
      "Entries left: 2764\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_0700_0800.parquet\n",
      "Non na entries: 30204\n",
      "Entries left: 2073\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_0800_0900.parquet\n",
      "Non na entries: 30901\n",
      "Entries left: 4342\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_0900_1000.parquet\n",
      "Non na entries: 22159\n",
      "Entries left: 1763\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_1000_1100.parquet\n",
      "Non na entries: 21288\n",
      "Entries left: 1621\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_1100_1200.parquet\n",
      "Non na entries: 21730\n",
      "Entries left: 2560\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_1200_1300.parquet\n",
      "Non na entries: 23288\n",
      "Entries left: 2786\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_1300_1400.parquet\n",
      "Non na entries: 27118\n",
      "Entries left: 4641\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_1400_1500.parquet\n",
      "Non na entries: 23464\n",
      "Entries left: 3516\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_1500_1600.parquet\n",
      "Non na entries: 23731\n",
      "Entries left: 3475\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_1600_1700.parquet\n",
      "Non na entries: 24287\n",
      "Entries left: 4047\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_1700_1800.parquet\n",
      "Non na entries: 17221\n",
      "Entries left: 1142\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_1800_1900.parquet\n",
      "Non na entries: 19754\n",
      "Entries left: 1411\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_1900_2000.parquet\n",
      "Non na entries: 17295\n",
      "Entries left: 1696\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_2000_2100.parquet\n",
      "Non na entries: 23238\n",
      "Entries left: 1227\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_2100_2200.parquet\n",
      "Non na entries: 27619\n",
      "Entries left: 3305\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_2200_2300.parquet\n",
      "Non na entries: 27030\n",
      "Entries left: 1587\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_14_2300_0000.parquet\n",
      "Non na entries: 26169\n",
      "Entries left: 1979\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_0000_0100.parquet\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Non na entries: 27521\n",
      "Entries left: 1087\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_0100_0200.parquet\n",
      "Non na entries: 24930\n",
      "Entries left: 2105\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_0200_0300.parquet\n",
      "Non na entries: 23995\n",
      "Entries left: 3622\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_0300_0400.parquet\n",
      "Non na entries: 23348\n",
      "Entries left: 3409\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_0400_0500.parquet\n",
      "Non na entries: 23337\n",
      "Entries left: 3592\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_0500_0600.parquet\n",
      "Non na entries: 20327\n",
      "Entries left: 1931\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_0600_0700.parquet\n",
      "Non na entries: 19324\n",
      "Entries left: 2648\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_0700_0800.parquet\n",
      "Non na entries: 19165\n",
      "Entries left: 1062\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_0800_0900.parquet\n",
      "Non na entries: 19953\n",
      "Entries left: 2897\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_0900_1000.parquet\n",
      "Non na entries: 28001\n",
      "Entries left: 4639\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_1000_1100.parquet\n",
      "Non na entries: 22787\n",
      "Entries left: 2844\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_1100_1200.parquet\n",
      "Non na entries: 23247\n",
      "Entries left: 4012\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_1200_1300.parquet\n",
      "Non na entries: 25178\n",
      "Entries left: 4377\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_1300_1400.parquet\n",
      "Non na entries: 23804\n",
      "Entries left: 5390\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_1400_1500.parquet\n",
      "Non na entries: 31365\n",
      "Entries left: 4904\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_1500_1600.parquet\n",
      "Non na entries: 28866\n",
      "Entries left: 4270\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_1600_1700.parquet\n",
      "Non na entries: 30040\n",
      "Entries left: 6297\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_1700_1800.parquet\n",
      "Non na entries: 22322\n",
      "Entries left: 2077\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_1800_1900.parquet\n",
      "Non na entries: 24886\n",
      "Entries left: 4068\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_1900_2000.parquet\n",
      "Non na entries: 24876\n",
      "Entries left: 3511\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_2000_2100.parquet\n",
      "Non na entries: 23560\n",
      "Entries left: 4554\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_2100_2200.parquet\n",
      "Non na entries: 22572\n",
      "Entries left: 3836\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_2200_2300.parquet\n",
      "Non na entries: 20768\n",
      "Entries left: 4600\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_15_2300_0000.parquet\n",
      "Non na entries: 26129\n",
      "Entries left: 4102\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_0000_0100.parquet\n",
      "Non na entries: 23843\n",
      "Entries left: 4284\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_0100_0200.parquet\n",
      "Non na entries: 22470\n",
      "Entries left: 4241\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_0200_0300.parquet\n",
      "Non na entries: 25021\n",
      "Entries left: 6903\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_0300_0400.parquet\n",
      "Non na entries: 20527\n",
      "Entries left: 5106\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_0400_0500.parquet\n",
      "Non na entries: 21365\n",
      "Entries left: 4574\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_0500_0600.parquet\n",
      "Non na entries: 21585\n",
      "Entries left: 5946\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_0600_0700.parquet\n",
      "Non na entries: 20304\n",
      "Entries left: 5802\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_0700_0800.parquet\n",
      "Non na entries: 22604\n",
      "Entries left: 4469\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_0800_0900.parquet\n",
      "Non na entries: 19245\n",
      "Entries left: 3182\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_0900_1000.parquet\n",
      "Non na entries: 22239\n",
      "Entries left: 4182\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_1000_1100.parquet\n",
      "Non na entries: 26071\n",
      "Entries left: 3290\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_1100_1200.parquet\n",
      "Non na entries: 21796\n",
      "Entries left: 5027\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_1200_1300.parquet\n",
      "Non na entries: 16273\n",
      "Entries left: 589\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_1300_1400.parquet\n",
      "Non na entries: 20885\n",
      "Entries left: 2690\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_1400_1500.parquet\n",
      "Non na entries: 24523\n",
      "Entries left: 4340\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_1500_1600.parquet\n",
      "Non na entries: 18443\n",
      "Entries left: 1550\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_1600_1700.parquet\n",
      "Non na entries: 18488\n",
      "Entries left: 1762\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_1700_1800.parquet\n",
      "Non na entries: 13384\n",
      "Entries left: 329\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_1800_1900.parquet\n",
      "Non na entries: 12533\n",
      "Entries left: 307\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_1900_2000.parquet\n",
      "Non na entries: 13175\n",
      "Entries left: 1285\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_2000_2100.parquet\n",
      "Non na entries: 12884\n",
      "Entries left: 729\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_2100_2200.parquet\n",
      "Non na entries: 12538\n",
      "Entries left: 600\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_2200_2300.parquet\n",
      "Non na entries: 15634\n",
      "Entries left: 1547\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_16_2300_0000.parquet\n",
      "Non na entries: 10401\n",
      "Entries left: 373\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_0000_0100.parquet\n",
      "Non na entries: 17868\n",
      "Entries left: 2419\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_0100_0200.parquet\n",
      "Non na entries: 15572\n",
      "Entries left: 1242\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_0200_0300.parquet\n",
      "Non na entries: 21304\n",
      "Entries left: 7847\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_0300_0400.parquet\n",
      "Non na entries: 22867\n",
      "Entries left: 4924\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_0400_0500.parquet\n",
      "Non na entries: 18906\n",
      "Entries left: 4928\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_0500_0600.parquet\n",
      "Non na entries: 20459\n",
      "Entries left: 5253\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_0600_0700.parquet\n",
      "Non na entries: 28776\n",
      "Entries left: 1699\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_0700_0800.parquet\n",
      "Non na entries: 27138\n",
      "Entries left: 2122\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_0800_0900.parquet\n",
      "Non na entries: 26465\n",
      "Entries left: 5014\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_0900_1000.parquet\n",
      "Non na entries: 24802\n",
      "Entries left: 6052\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_1000_1100.parquet\n",
      "Non na entries: 15558\n",
      "Entries left: 975\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_1100_1200.parquet\n",
      "Non na entries: 16872\n",
      "Entries left: 1729\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_1200_1300.parquet\n",
      "Non na entries: 20356\n",
      "Entries left: 2549\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_1300_1400.parquet\n",
      "Non na entries: 28232\n",
      "Entries left: 3189\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_1400_1500.parquet\n",
      "Non na entries: 19224\n",
      "Entries left: 3984\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_1500_1600.parquet\n",
      "Non na entries: 14991\n",
      "Entries left: 1114\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_1600_1700.parquet\n",
      "Non na entries: 19174\n",
      "Entries left: 1791\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_1700_1800.parquet\n",
      "Non na entries: 16804\n",
      "Entries left: 432\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_1800_1900.parquet\n",
      "Non na entries: 17178\n",
      "Entries left: 1167\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_1900_2000.parquet\n",
      "Non na entries: 17797\n",
      "Entries left: 638\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_2000_2100.parquet\n",
      "Non na entries: 22174\n",
      "Entries left: 1353\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_2100_2200.parquet\n",
      "Non na entries: 19309\n",
      "Entries left: 1007\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_2200_2300.parquet\n",
      "Non na entries: 16829\n",
      "Entries left: 1665\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_17_2300_0000.parquet\n",
      "Non na entries: 22010\n",
      "Entries left: 2108\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_0000_0100.parquet\n",
      "Non na entries: 23182\n",
      "Entries left: 1040\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_0100_0200.parquet\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Non na entries: 18296\n",
      "Entries left: 485\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_0200_0300.parquet\n",
      "Non na entries: 22012\n",
      "Entries left: 770\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_0300_0400.parquet\n",
      "Non na entries: 22133\n",
      "Entries left: 1118\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_0400_0500.parquet\n",
      "Non na entries: 21448\n",
      "Entries left: 330\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_0500_0600.parquet\n",
      "Non na entries: 19851\n",
      "Entries left: 579\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_0600_0700.parquet\n",
      "Non na entries: 19670\n",
      "Entries left: 521\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_0700_0800.parquet\n",
      "Non na entries: 18773\n",
      "Entries left: 302\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_0800_0900.parquet\n",
      "Non na entries: 19211\n",
      "Entries left: 741\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_0900_1000.parquet\n",
      "Non na entries: 15352\n",
      "Entries left: 137\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_1000_1100.parquet\n",
      "Non na entries: 19082\n",
      "Entries left: 184\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_1100_1200.parquet\n",
      "Non na entries: 20401\n",
      "Entries left: 298\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_1200_1300.parquet\n",
      "Non na entries: 24121\n",
      "Entries left: 925\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_1300_1400.parquet\n",
      "Non na entries: 21732\n",
      "Entries left: 1097\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_1400_1500.parquet\n",
      "Non na entries: 16748\n",
      "Entries left: 406\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_1500_1600.parquet\n",
      "Non na entries: 19776\n",
      "Entries left: 516\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_1600_1700.parquet\n",
      "Non na entries: 16940\n",
      "Entries left: 225\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_1700_1800.parquet\n",
      "Non na entries: 26566\n",
      "Entries left: 2181\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_1800_1900.parquet\n",
      "Non na entries: 31398\n",
      "Entries left: 5146\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_1900_2000.parquet\n",
      "Non na entries: 25755\n",
      "Entries left: 3526\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_2000_2100.parquet\n",
      "Non na entries: 21328\n",
      "Entries left: 794\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_2100_2200.parquet\n",
      "Non na entries: 22571\n",
      "Entries left: 866\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_2200_2300.parquet\n",
      "Non na entries: 21180\n",
      "Entries left: 1593\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_18_2300_0000.parquet\n",
      "Non na entries: 18567\n",
      "Entries left: 445\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_0000_0100.parquet\n",
      "Non na entries: 21472\n",
      "Entries left: 464\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_0100_0200.parquet\n",
      "Non na entries: 22268\n",
      "Entries left: 601\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_0200_0300.parquet\n",
      "Non na entries: 18284\n",
      "Entries left: 713\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_0300_0400.parquet\n",
      "Non na entries: 21178\n",
      "Entries left: 703\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_0400_0500.parquet\n",
      "Non na entries: 24032\n",
      "Entries left: 407\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_0500_0600.parquet\n",
      "Non na entries: 21356\n",
      "Entries left: 713\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_0600_0700.parquet\n",
      "Non na entries: 21864\n",
      "Entries left: 281\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_0700_0800.parquet\n",
      "Non na entries: 22011\n",
      "Entries left: 271\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_0800_0900.parquet\n",
      "Non na entries: 23630\n",
      "Entries left: 348\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_0900_1000.parquet\n",
      "Non na entries: 19436\n",
      "Entries left: 155\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_1000_1100.parquet\n",
      "Non na entries: 18938\n",
      "Entries left: 348\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_1100_1200.parquet\n",
      "Non na entries: 28521\n",
      "Entries left: 1142\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_1200_1300.parquet\n",
      "Non na entries: 26141\n",
      "Entries left: 975\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_1300_1400.parquet\n",
      "Non na entries: 26839\n",
      "Entries left: 1325\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_1400_1500.parquet\n",
      "Non na entries: 25620\n",
      "Entries left: 1884\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_1500_1600.parquet\n",
      "Non na entries: 21827\n",
      "Entries left: 1053\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_1600_1700.parquet\n",
      "Non na entries: 23805\n",
      "Entries left: 1587\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_1700_1800.parquet\n",
      "Non na entries: 22752\n",
      "Entries left: 1485\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_1800_1900.parquet\n",
      "Non na entries: 21289\n",
      "Entries left: 851\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_1900_2000.parquet\n",
      "Non na entries: 20266\n",
      "Entries left: 1935\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_2000_2100.parquet\n",
      "Non na entries: 22559\n",
      "Entries left: 3647\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_2100_2200.parquet\n",
      "Non na entries: 17410\n",
      "Entries left: 2039\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_2200_2300.parquet\n",
      "Non na entries: 20513\n",
      "Entries left: 2285\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_19_2300_0000.parquet\n",
      "Non na entries: 16284\n",
      "Entries left: 386\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_0000_0100.parquet\n",
      "Non na entries: 20526\n",
      "Entries left: 725\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_0100_0200.parquet\n",
      "Non na entries: 19294\n",
      "Entries left: 547\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_0200_0300.parquet\n",
      "Non na entries: 16827\n",
      "Entries left: 597\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_0300_0400.parquet\n",
      "Non na entries: 16628\n",
      "Entries left: 325\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_0400_0500.parquet\n",
      "Non na entries: 17306\n",
      "Entries left: 350\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_0500_0600.parquet\n",
      "Non na entries: 18860\n",
      "Entries left: 399\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_0600_0700.parquet\n",
      "Non na entries: 16651\n",
      "Entries left: 376\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_0700_0800.parquet\n",
      "Non na entries: 15018\n",
      "Entries left: 110\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_0800_0900.parquet\n",
      "Non na entries: 19297\n",
      "Entries left: 143\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_0900_1000.parquet\n",
      "Non na entries: 18229\n",
      "Entries left: 173\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_1000_1100.parquet\n",
      "Non na entries: 21159\n",
      "Entries left: 65\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_1100_1200.parquet\n",
      "Non na entries: 14720\n",
      "Entries left: 102\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_1200_1300.parquet\n",
      "Non na entries: 20426\n",
      "Entries left: 281\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_1300_1400.parquet\n",
      "Non na entries: 16789\n",
      "Entries left: 182\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_1400_1500.parquet\n",
      "Non na entries: 19182\n",
      "Entries left: 364\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_1500_1600.parquet\n",
      "Non na entries: 26219\n",
      "Entries left: 1640\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_1600_1700.parquet\n",
      "Non na entries: 18598\n",
      "Entries left: 304\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_1700_1800.parquet\n",
      "Non na entries: 16120\n",
      "Entries left: 305\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_1800_1900.parquet\n",
      "Non na entries: 29157\n",
      "Entries left: 1472\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_1900_2000.parquet\n",
      "Non na entries: 21949\n",
      "Entries left: 494\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_2000_2100.parquet\n",
      "Non na entries: 21123\n",
      "Entries left: 558\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_2100_2200.parquet\n",
      "Non na entries: 17445\n",
      "Entries left: 365\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_2200_2300.parquet\n",
      "Non na entries: 22824\n",
      "Entries left: 777\n",
      "coinbase_btc_usd_l2_book_snapshots_depth50_2019_11_20_2300_0000.parquet\n",
      "Non na entries: 21173\n",
      "Entries left: 1124\n"
     ]
    }
   ],
   "source": [
    "i = 0\n",
    "for filename in os.listdir(r\"Data\\coinbase_btc_usd\\coinbase\\btc_usd\\l2_snapshots\\100ms\"):\n",
    "    print(filename)\n",
    "    \n",
    "    df_snapshot = pd.read_parquet(os.path.join(r\"Data\\coinbase_btc_usd\\coinbase\\btc_usd\\l2_snapshots\\100ms\", filename))\n",
    "    \n",
    "    df_snapshot.dropna(inplace=True) #Drop na values\n",
    "    print('Non na entries:', np.shape(df_snapshot)[0])\n",
    "\n",
    "    # Check no price levels with zero quantities:\n",
    "    assert np.count_nonzero(df_snapshot.values) == np.shape(df_snapshot)[0] * np.shape(df_snapshot)[1]\n",
    "\n",
    "    df_snapshot['future_bid'] = df_snapshot['b1'].shift(-1)\n",
    "\n",
    "    # Calculate how many levels the bid/ask price changed by\n",
    "    df_snapshot['bid_change_n'] = df_snapshot.apply(lambda x: 100*(x['future_bid'] - x['b1']), axis=1)\n",
    "\n",
    "    #Delete last one as don't know how much it has changed by\n",
    "    df_snapshot.drop(df_snapshot.index[-1], axis=0, inplace=True)\n",
    "\n",
    "    # Ignore the prices columns\n",
    "    df_snapshot_cut = df_snapshot[df_snapshot.columns[100:]]\n",
    "\n",
    "    # Take only the values where the bid price increases\n",
    "    df_bid_up = df_snapshot_cut[df_snapshot_cut['bid_change_n'] > 0].drop(['future_bid'], axis=1)\n",
    "\n",
    "    # How many entries in this hour left?\n",
    "    print('Entries left:', np.shape(df_bid_up)[0])\n",
    "    \n",
    "    if i==0:\n",
    "        df = df_bid_up\n",
    "        i += 1\n",
    "    else:\n",
    "        df = df.append(df_bid_up)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"df_snapshot_100ms_bid_up.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array([   1,    2,    3,    4,    5,    6,    7,    8,    9,   10,   11,\n",
       "          12,   13,   14,   15,   16,   17,   18,   19,   20,   21,   22,\n",
       "          23,   24,   25,   26,   27,   28,   29,   30,   31,   32,   33,\n",
       "          34,   35,   36,   37,   38,   39,   40,   41,   42,   43,   44,\n",
       "          45,   46,   47,   48,   49,   50,   51,   52,   53,   54,   55,\n",
       "          56,   57,   58,   59,   60,   61,   62,   63,   64,   65,   66,\n",
       "          67,   68,   69,   70,   71,   72,   73,   74,   75,   76,   77,\n",
       "          78,   79,   80,   81,   82,   83,   84,   85,   86,   87,   88,\n",
       "          89,   90,   91,   92,   93,   94,   95,   96,   97,   98,   99,\n",
       "         100,  101,  102,  103,  104,  105,  106,  107,  108,  109,  110,\n",
       "         111,  112,  113,  114,  115,  116,  117,  118,  119,  120,  121,\n",
       "         122,  123,  124,  125,  126,  127,  128,  129,  130,  131,  132,\n",
       "         133,  134,  135,  136,  137,  138,  139,  140,  141,  142,  143,\n",
       "         144,  145,  146,  147,  148,  149,  150,  151,  152,  153,  154,\n",
       "         155,  156,  157,  158,  159,  160,  161,  162,  163,  164,  165,\n",
       "         166,  167,  168,  169,  170,  171,  172,  173,  174,  175,  176,\n",
       "         177,  178,  179,  180,  181,  182,  183,  184,  185,  186,  187,\n",
       "         188,  189,  190,  191,  192,  193,  194,  195,  196,  197,  198,\n",
       "         199,  200,  201,  202,  203,  204,  205,  206,  207,  208,  209,\n",
       "         210,  211,  212,  213,  214,  215,  216,  217,  218,  219,  220,\n",
       "         221,  222,  223,  224,  225,  226,  227,  228,  229,  230,  231,\n",
       "         232,  233,  234,  235,  236,  237,  238,  239,  240,  241,  242,\n",
       "         243,  244,  245,  246,  247,  248,  249,  250,  251,  252,  253,\n",
       "         254,  255,  256,  257,  258,  259,  260,  261,  262,  263,  264,\n",
       "         265,  266,  267,  268,  269,  270,  271,  272,  273,  274,  275,\n",
       "         276,  277,  278,  279,  280,  281,  282,  283,  284,  285,  286,\n",
       "         287,  288,  289,  290,  291,  292,  293,  294,  295,  296,  297,\n",
       "         298,  299,  300,  301,  302,  303,  304,  305,  306,  307,  308,\n",
       "         309,  310,  311,  312,  313,  314,  315,  316,  317,  318,  319,\n",
       "         320,  321,  322,  323,  324,  325,  326,  327,  328,  329,  330,\n",
       "         331,  332,  333,  334,  335,  336,  337,  338,  339,  340,  341,\n",
       "         342,  343,  344,  345,  346,  347,  348,  349,  350,  351,  352,\n",
       "         353,  354,  355,  356,  357,  358,  359,  360,  361,  362,  363,\n",
       "         364,  365,  366,  367,  368,  369,  370,  371,  372,  373,  374,\n",
       "         375,  376,  377,  378,  379,  380,  381,  382,  383,  384,  385,\n",
       "         386,  387,  388,  389,  390,  391,  392,  393,  394,  395,  396,\n",
       "         397,  398,  399,  400,  401,  402,  403,  404,  405,  406,  407,\n",
       "         408,  409,  410,  411,  412,  413,  414,  415,  416,  417,  418,\n",
       "         419,  420,  421,  422,  423,  424,  425,  426,  427,  428,  429,\n",
       "         430,  431,  432,  433,  434,  435,  436,  437,  438,  439,  440,\n",
       "         441,  442,  443,  444,  445,  446,  447,  448,  449,  450,  451,\n",
       "         452,  453,  454,  455,  456,  457,  458,  459,  460,  461,  462,\n",
       "         463,  464,  465,  466,  467,  468,  469,  472,  473,  474,  475,\n",
       "         476,  477,  478,  479,  480,  481,  482,  483,  485,  486,  487,\n",
       "         488,  489,  490,  491,  492,  493,  494,  495,  496,  497,  498,\n",
       "         499,  500,  501,  502,  503,  504,  505,  506,  507,  508,  509,\n",
       "         510,  511,  512,  513,  514,  515,  516,  517,  518,  519,  520,\n",
       "         521,  522,  523,  525,  528,  530,  531,  532,  536,  537,  538,\n",
       "         539,  540,  542,  543,  544,  546,  547,  549,  550,  551,  552,\n",
       "         553,  554,  556,  557,  558,  559,  560,  566,  568,  569,  570,\n",
       "         571,  572,  573,  574,  575,  576,  577,  578,  579,  582,  584,\n",
       "         586,  587,  588,  589,  591,  594,  597,  598,  600,  601,  603,\n",
       "         604,  607,  610,  611,  613,  615,  616,  620,  621,  622,  623,\n",
       "         625,  630,  632,  634,  635,  642,  643,  645,  650,  651,  652,\n",
       "         653,  655,  656,  663,  664,  667,  671,  673,  674,  676,  677,\n",
       "         678,  684,  688,  691,  693,  696,  699,  700,  708,  714,  715,\n",
       "         716,  718,  720,  722,  723,  727,  728,  733,  735,  736,  742,\n",
       "         748,  749,  750,  751,  752,  762,  765,  768,  772,  774,  775,\n",
       "         776,  777,  786,  787,  788,  790,  797,  799,  800,  801,  817,\n",
       "         824,  828,  832,  837,  854,  857,  858,  860,  867,  874,  895,\n",
       "         896,  901,  905,  913,  918,  929,  950,  953,  960,  979, 1002,\n",
       "        1023, 1052, 1088, 1103, 1148, 1162, 1185, 1197, 1218, 1227, 1232,\n",
       "        1234, 1264, 1330, 1364, 1373, 1495, 1499, 1586, 2564, 2733]),\n",
       " array([105912, 106103,  92412,  69731,  13148,   8756,   3700,   1877,\n",
       "          1493,   1745,   1480,   1163,   1050,    814,    731,    636,\n",
       "           575,    542,    555,    603,    523,    479,    438,    449,\n",
       "           474,    444,    450,    413,    420,    380,    375,    397,\n",
       "           376,    351,    375,    360,    358,    362,    372,    382,\n",
       "           349,    334,    363,    341,    360,    355,    376,    372,\n",
       "           419,    572,    475,    373,    295,    303,    278,    298,\n",
       "           306,    309,    289,    306,    333,    256,    266,    278,\n",
       "           284,    239,    286,    250,    264,    250,    266,    280,\n",
       "           250,    264,    243,    266,    377,    432,    335,    292,\n",
       "           234,    244,    253,    237,    248,    250,    263,    269,\n",
       "           293,    295,    288,    306,    357,    330,    294,    371,\n",
       "           429,    490,    651,   1268,    237,    189,    140,    142,\n",
       "           102,    116,    173,    118,    118,    108,    129,    101,\n",
       "           101,    101,    106,    111,    109,     89,     85,    105,\n",
       "           109,     99,     92,     90,     87,     78,     76,     82,\n",
       "            87,     88,     88,    102,     86,     74,     90,     83,\n",
       "            75,     86,     86,     76,     68,     73,     81,     75,\n",
       "            66,     72,     76,     73,     79,     89,     91,     77,\n",
       "            88,     63,     71,     77,     68,     83,     68,     53,\n",
       "            57,     54,     59,     68,     62,     56,     79,     59,\n",
       "            82,     81,     67,     54,     49,     63,     72,     71,\n",
       "            52,     53,     64,     61,     64,     50,     46,     49,\n",
       "            51,     54,     63,     52,     63,     47,     42,     48,\n",
       "            43,     56,     51,     53,     62,     63,     77,     84,\n",
       "            67,     53,     44,     39,     35,     50,     38,     42,\n",
       "            30,     44,     35,     34,     48,     44,     43,     36,\n",
       "            38,     33,     30,     39,     37,     33,     29,     24,\n",
       "            31,     44,     49,     38,     40,     33,     34,     37,\n",
       "            30,     26,     48,     28,     25,     31,     25,     27,\n",
       "            17,     18,     27,     23,     26,     32,     21,     27,\n",
       "            23,     29,     15,     34,     19,     28,     31,     18,\n",
       "            25,     35,     21,     21,     26,     20,     21,     22,\n",
       "            27,     30,     21,     22,     31,     17,     28,     17,\n",
       "            25,     19,     23,     20,     10,     19,     11,     14,\n",
       "            20,     21,     18,     29,     10,     21,     13,     16,\n",
       "            20,     22,     16,     20,     18,     12,     21,     18,\n",
       "            17,     28,      9,     16,     22,     15,     20,     16,\n",
       "            15,     11,     18,     18,      6,     18,     13,     15,\n",
       "            22,      7,     16,      8,     14,     11,     10,     27,\n",
       "            12,     21,     23,      8,     13,     20,     14,     13,\n",
       "             9,     11,     19,      8,     14,     12,     17,     31,\n",
       "            23,     13,     21,     14,     14,     14,     19,     11,\n",
       "            11,     12,     11,     13,     10,      4,      6,     11,\n",
       "             6,     10,     10,      3,      8,     10,      6,      8,\n",
       "             9,      9,      4,      7,      7,      4,      7,      6,\n",
       "             4,      6,      1,      5,      6,      3,      7,      5,\n",
       "            14,     10,      7,      4,      6,      4,      7,      6,\n",
       "             7,      8,      6,      9,      5,      3,      4,      2,\n",
       "             5,     12,      8,      6,     14,      7,      1,      9,\n",
       "             1,      4,      4,      4,      2,      3,      3,      6,\n",
       "             3,      7,      4,      6,      7,      6,      2,      6,\n",
       "             6,      6,      5,      3,      2,      4,      4,      5,\n",
       "             4,      6,      3,      6,      4,      1,      2,      7,\n",
       "             8,      5,      5,      6,      6,      2,      4,      1,\n",
       "             5,      4,      4,      1,      3,      2,      2,      3,\n",
       "             1,      2,      4,      7,      2,      2,      5,      3,\n",
       "             2,      3,      4,      2,      5,      3,      2,      3,\n",
       "             3,      5,      2,      1,      1,      4,      1,      2,\n",
       "             2,      3,      1,      3,      2,      1,      5,      3,\n",
       "             2,      3,      1,      2,      3,      2,      2,      2,\n",
       "             3,      2,      1,      1,      2,      1,      4,      2,\n",
       "             4,      1,      5,      3,      1,      1,      2,      3,\n",
       "             1,      4,      3,      1,      1,      3,      4,      1,\n",
       "             1,      1,      2,      1,      2,      1,      1,      1,\n",
       "             1,      3,      2,      1,      3,      2,      1,      2,\n",
       "             2,      2,      1,      1,      2,      2,      1,      2,\n",
       "             1,      1,      1,      1,      3,      4,      1,      2,\n",
       "             1,      1,      3,      2,      3,      1,      2,      1,\n",
       "             1,      1,      3,      1,      2,      2,      1,      1,\n",
       "             1,      1,      2,      1,      1,      3,      1,      1,\n",
       "             2,      3,      3,      1,      1,      1,      3,      1,\n",
       "             1,      1,      1,      2,      1,      1,      2,      1,\n",
       "             1,      1,      1,      2,      3,      1,      1,      3,\n",
       "             2,      1,      1,      1,      2,      1,      1,      1,\n",
       "             1,      1,      1,      1,      1,      2,      2,      1,\n",
       "             2,      2,      1,      1,      1,      1,      1,      2,\n",
       "             1,      1,      1,      1,      1,      1,      2,      1,\n",
       "             2,      1,      1,      1,      1,      1,      1,      1,\n",
       "             1,      1,      1,      1,      1,      1,      1,      1,\n",
       "             1,      1,      1,      1,      1,      1,      1,      1,\n",
       "             1,      1,      1,      1,      1,      1,      1,      2,\n",
       "             1,      1,      1,      1,      1,      1,      1,      1,\n",
       "             2,      1,      1,      1,      1,      1,      1,      1,\n",
       "             1,      1,      1,      1,      1,      1,      1,      1,\n",
       "             1,      1,      1,      1,      1,      1,      1,      1,\n",
       "             1,      1,      1,      1], dtype=int64))"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "value_counts = np.unique(np.round(df_bid_up['bid_change_n'].values).astype(int), return_counts=True)\n",
    "value_counts"
   ]
  },
  {
   "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.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}