data_process.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **High Frequency Trading Strategies Design using ML and DL**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "\"\"\"\n",
    "Created on Sun Mar 13 22:40:00 2022\n",
    "\n",
    "@author: Bradley\n",
    "\n",
    "HFT: Machine Learning Techniques on Full Orderbook Tick Data\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import statsmodels.api as sm\n",
    "from datetime import datetime\n",
    "import csv\n",
    "\n",
    "plt.rcParams['font.family'] = ['sans-serif']\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "plt.rcParams['figure.figsize'] = (8,5) #提前设置图片形状大小\n",
    "\n",
    "%config InlineBackend.figure_format = 'svg'\n",
    "\n",
    "%matplotlib inline\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')  # 忽略一些warnings\n",
    "\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\n",
    "\n",
    "from IPython.display import display\n",
    "pd.set_option('expand_frame_repr', False)\n",
    "pd.set_option('display.unicode.ambiguous_as_wide', True)\n",
    "pd.set_option('display.unicode.east_asian_width', True)\n",
    "pd.set_option('display.width', 180)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Data Pre-processing**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Goal: Transform from full orderbook data (`CN_Futures_2014.01.02.csv`) to organized tick data (`order_book_3_2014_1_2.csv`)**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**我们分析的对象是SGX China A50 Futures在2014年1月2日的高频订单簿数据。目标是根据其订单簿数据挖掘因子并实现使用机器学习算法滚动预测买卖时点。**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def order_book_tranform(year,month,day,path,best_price_number,series):\n",
    "    ## read file\n",
    "    def read_file(year,month,day,path,series):\n",
    "        data = []\n",
    "        if len(str(month)) == 1:\n",
    "            month_ = '0' + str(month)\n",
    "        else:\n",
    "            month_ = str(month)\n",
    "        if len(str(day)) == 1:\n",
    "            day_ = '0' + str(day)\n",
    "        else:\n",
    "            day_ = str(day)\n",
    "        datapath = str(path) + str(year) + '.' + str(month_) + '.' + str(day_) + '.csv'\n",
    "        data = pd.read_csv(datapath)\n",
    "        data = data[data.Series == series]\n",
    "        return data.reset_index(drop = True)\n",
    "\n",
    "    def insert(order_book_data,data_to_insert,ob_position):\n",
    "        top = order_book_data[0:ob_position]\n",
    "        bottom = order_book_data[ob_position:]\n",
    "        return pd.concat((top,data_to_insert,bottom)).reset_index(drop = True)\n",
    "\n",
    "    def draw_out(order_book_data,ob_position):\n",
    "        top = order_book_data[0:ob_position]\n",
    "        bottom = order_book_data[ob_position + 1:]\n",
    "        return pd.concat((top,bottom)).reset_index(drop = True)\n",
    "\n",
    "    def order_book_to_csv(order_book_bid,order_book_ask,data,i):\n",
    "        \n",
    "        order_book_bid_sum = order_book_bid[['Price','QuantityDifference']].groupby(by = ['Price'],as_index = False,sort = False).sum()\n",
    "        order_book_ask_sum = order_book_ask[['Price','QuantityDifference']].groupby(by = ['Price'],as_index = False).sum()\n",
    "        order_book_bid_sum = order_book_bid_sum[order_book_bid_sum.QuantityDifference != 0.0].reset_index(drop = True)\n",
    "        order_book_ask_sum = order_book_ask_sum[order_book_ask_sum.QuantityDifference != 0.0].reset_index(drop = True)\n",
    "        order_book_bid_ask = pd.concat([order_book_bid_sum[['Price','QuantityDifference']],order_book_ask_sum[['Price','QuantityDifference']]],axis = 1)    \n",
    "       \n",
    "        with open('order_book_'+str(best_price_number)+'_'+str(year)+'_'+str(month)+'_'+str(day)+'.csv','a') as f:\n",
    "            order_book = csv.writer(f)\n",
    "            order_book.writerow([\"TimeStamp\",data.TimeStamp[i-1:i].iloc[0]])\n",
    "            order_book = csv.writer(f,delimiter=',')\n",
    "            for i in range(0,min(len(order_book_bid_ask),best_price_number),1):\n",
    "                order_book.writerow(order_book_bid_ask[i:i+1].values.tolist()[0])\n",
    "        return order_book_bid_sum,order_book_ask_sum\n",
    "\n",
    "    data = read_file(year,month,day,path,series)\n",
    "    \n",
    "    with open('order_book_' + str(best_price_number) + '_' + str(year) + '_' + str(month) + '_' + str(day) + '.csv', 'wb') as csvfile:\n",
    "        f = csv.writer(csvfile) \n",
    "\n",
    "    data[['QuantityDifference']] = data[['QuantityDifference']].astype(float)\n",
    "    data['QuantityDifference_'] = data['QuantityDifference']\n",
    "    data_ask = data[(data.BidOrAsk == 'A')].reset_index(drop=True)\n",
    "    data_bid = data[(data.BidOrAsk == 'B')].reset_index(drop=True)\n",
    "    order_book_bid = []\n",
    "    order_book_ask = []\n",
    "    x1 = data[(data.BidOrAsk == 'A')].TimeStamp.unique()\n",
    "    x2 = data[(data.BidOrAsk == 'B')].TimeStamp.unique()\n",
    "    temp_ask = 0\n",
    "    temp_bid = 0\n",
    "    \n",
    "    def first_order_create(index_,data):\n",
    "        timestamp = data.TimeStamp.unique()[index_]\n",
    "        print('timestamp = %s'%(timestamp)) \n",
    "        bid = []\n",
    "        ask = []\n",
    "        timestamp_ = []\n",
    "        index_find = data[data['TimeStamp'].str.contains(timestamp)].index[-1]\n",
    "        y = data[:index_find + 1]\n",
    "        bid.append(y[(y.BidOrAsk == 'B')][[\"Price\",\"OrderNumber\",\"QuantityDifference\",\"QuantityDifference_\"]]) # bid\n",
    "        ask.append(y[(y.BidOrAsk == 'A')][[\"Price\",\"OrderNumber\",\"QuantityDifference\",\"QuantityDifference_\"]]) # ask\n",
    "        a = bid[0].sort_values(by=['Price'], ascending = [False])\n",
    "        b = ask[0].sort_values(by=['Price'], ascending = [True])\n",
    "        # a = bid[0].sort(['Price'],ascending = [False])\n",
    "        # b = ask[0].sort(['Price'],ascending = [True])\n",
    "        order_book_bid = a[a.QuantityDifference != 0].reset_index(drop = True)\n",
    "        order_book_ask = b[b.QuantityDifference != 0].reset_index(drop = True)\n",
    "        order_book_bid_sum = order_book_bid[['Price','QuantityDifference']].groupby(by = ['Price'],as_index = False,sort = False).sum()\n",
    "        order_book_ask_sum = order_book_ask[['Price','QuantityDifference']].groupby(by = ['Price'],as_index = False).sum()\n",
    "        \n",
    "        if len(order_book_bid_sum[order_book_bid_sum.QuantityDifference == 0.0]) != 0 and len(order_book_ask_sum[order_book_ask_sum.QuantityDifference == 0.0]) != 0:\n",
    "            \n",
    "            print('Exist Bid Ask Order Book Price = Zero')\n",
    "            price_bid_zero = order_book_bid_sum[order_book_bid_sum.QuantityDifference == 0.0]['Price'][0]\n",
    "            price_ask_zero = order_book_ask_sum[order_book_ask_sum.QuantityDifference == 0.0]['Price'][0]\n",
    "            order_book_bid = order_book_bid[order_book_bid.Price != price_bid_zero]\n",
    "            order_book_ask = order_book_ask[order_book_ask.Price != price_ask_zero]\n",
    "        elif len(order_book_bid_sum[order_book_bid_sum.QuantityDifference == 0.0]) != 0 and len(order_book_ask_sum[order_book_ask_sum.QuantityDifference == 0.0]) == 0:\n",
    "            \n",
    "            print('Exist Bid Order Book Price = Zero')\n",
    "            price_bid_zero = order_book_bid_sum[order_book_bid_sum.QuantityDifference == 0.0]['Price'][0]\n",
    "            order_book_bid = order_book_bid[order_book_bid.Price != price_bid_zero]\n",
    "\n",
    "        elif len(order_book_bid_sum[order_book_bid_sum.QuantityDifference == 0.0]) == 0 and len(order_book_ask_sum[order_book_ask_sum.QuantityDifference == 0.0]) != 0:\n",
    "            \n",
    "            print('Exist Ask Order Book Price = Zero')\n",
    "            price_ask_zero = order_book_ask_sum[order_book_ask_sum.QuantityDifference == 0.0]['Price'][0]\n",
    "            order_book_ask = order_book_ask[order_book_ask.Price != price_ask_zero]\n",
    "\n",
    "        order_book_bid_sum = order_book_bid_sum[order_book_bid_sum.QuantityDifference != 0].reset_index(drop = True)\n",
    "        order_book_ask_sum = order_book_ask_sum[order_book_ask_sum.QuantityDifference != 0].reset_index(drop = True)\n",
    "        order_book_bid_ask = pd.concat([order_book_bid_sum[['Price','QuantityDifference']],order_book_ask_sum[['Price','QuantityDifference']]],axis = 1)        \n",
    "        \n",
    "        return order_book_bid, order_book_ask, order_book_bid_ask, timestamp, y, index_find\n",
    "    \n",
    "    def with_first_order_book(best_price_number,year,month,day,timestamp,order_book_bid_ask,index_):\n",
    "        with open('order_book_'+str(best_price_number)+'_'+str(year)+'_'+str(month)+'_'+str(day)+'.csv','a') as f:  \n",
    "            order_book = csv.writer(f)\n",
    "            if index_ == 0:\n",
    "                order_book.writerow([\"Bid\",\"Bid_Quantity\",\"Ask\",\"Ask_Quantity\"])\n",
    "            order_book.writerow([\"TimeStamp\",timestamp])\n",
    "            order_book = csv.writer(f,delimiter=',')\n",
    "            for i in range(0,min(len(order_book_bid_ask),best_price_number),1):\n",
    "                order_book.writerow(order_book_bid_ask[i:i+1].values.tolist()[0])     \n",
    "    \n",
    "    # 建立初始委託簿\n",
    "    first_order_book_data_lenth = 0\n",
    "    order_book_bid_time = 0\n",
    "    order_book_ask_time = 0\n",
    "    \n",
    "    for time in range(0,1000,1):\n",
    "        index_ = time\n",
    "        order_book_bid, order_book_ask, order_book_bid_ask,\\\n",
    "        timestamp, y, index_find = first_order_create(index_, data)  \n",
    "\n",
    "        if len(order_book_bid) != 0 and len(order_book_ask) != 0:\n",
    "            with_first_order_book(best_price_number,year,month,day,timestamp,order_book_bid_ask,index_)  \n",
    "            break\n",
    "        elif len(order_book_bid) == 0 and len(order_book_ask) != 0: \n",
    "            with_first_order_book(best_price_number,year,month,day,timestamp,order_book_bid_ask,index_)\n",
    "            temp_ask +=1\n",
    "        elif len(order_book_bid) != 0 and len(order_book_ask) == 0:\n",
    "            with_first_order_book(best_price_number,year,month,day,timestamp,order_book_bid_ask,index_)\n",
    "            temp_bid +=1\n",
    "            \n",
    "    print('-------------------------------------------')\n",
    "    print('index_find = %s'%(index_find))\n",
    "    \n",
    "    # 这里作为举例，只取了100条，可以取全部length的data，生成的结果就是\"order_book_3_2014_1_2.csv\"文件\n",
    "    for i in range(index_find + 1,100,1):#len(data), 1):\n",
    "        print('---------------------------------')\n",
    "        print(data[['Price','QuantityDifference','BidOrAsk','TimeStamp']][i:i+1])\n",
    "        print(i ,temp_bid, temp_ask)\n",
    "        print(data.TimeStamp[i], x2[temp_bid], x1[temp_ask])\n",
    "        time_second = int(data[i:i+1].TimeStamp.iloc[0][18]) + int(data[i:i+1].TimeStamp.iloc[0][17])*10 +\\\n",
    "                      int(data[i:i+1].TimeStamp.iloc[0][15])*60 + int(data[i:i+1].TimeStamp.iloc[0][14])*600 +\\\n",
    "                      int(data[i:i+1].TimeStamp.iloc[0][12])*3600 + int(data[i:i+1].TimeStamp.iloc[0][11])*36000\n",
    "\n",
    "        if time_second > 57600:\n",
    "            break\n",
    "        if time_second == 32400 and time_second >= 57300:\n",
    "            order_book_bid = order_book_bid.sort(['Price'],ascending = [False]).reset_index(drop = True)\n",
    "            order_book_ask = order_book_ask.sort(['Price'],ascending = [True]).reset_index(drop = True)\n",
    "            pass \n",
    "        \n",
    "        if data.BidOrAsk[i] == 'A':\n",
    "            data_ask_Quantity = data.BestQuantity[i]\n",
    "            if int(data[['QuantityDifference']][i:i+1].values) > 0 :\n",
    "                if order_book_bid.Price[0] >= data[i:i+1].Price.iloc[0] and time_second < 32400:\n",
    "                    for k in range(0,len(order_book_bid)):\n",
    "                        diff = order_book_bid.QuantityDifference_[k] - data[i:i+1].QuantityDifference_.iloc[0] \n",
    "                        if order_book_bid.Price[k] >= data[i:i+1].Price.iloc[0] and diff >= 0:\n",
    "                            order_book_bid.QuantityDifference_[k] = diff\n",
    "                            data[i:i+1].QuantityDifference_.iloc[0] = 0\n",
    "                            break\n",
    "                        elif order_book_bid.Price[k] >= data[i:i+1].Price.iloc[0] and diff < 0: \n",
    "                            order_book_bid.QuantityDifference_[k] = 0\n",
    "                            data[i:i+1].QuantityDifference_.iloc[0] = -diff\n",
    "                            pass\n",
    "                        else:\n",
    "                            break\n",
    "                if data.TimeStamp[i] == x1[temp_ask]:\n",
    "                    \n",
    "                    position_ = int(data[['OrderBookPosition']][i:i+1].iloc[0]) - 1 \n",
    "                    order_book_ask = insert(order_book_ask,data[['Price','OrderNumber','QuantityDifference','QuantityDifference_']][i:i+1],position_)\n",
    "                    if time_second > 32400 and time_second < 57300: \n",
    "                        if position_ == 0 and len(order_book_ask) > 1:   \n",
    "                            if order_book_ask[position_ + 1:position_ + 1 + 1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:  \n",
    "                                print('Some error1(Ask & Q>0 & timestamp not change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                break\n",
    "                            else:\n",
    "                                pass\n",
    "                        elif 0 < position_< (len(order_book_ask)-1):\n",
    "                            if order_book_ask[position_ + 1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_ask[position_ - 1:position_ - 1 + 1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:\n",
    "                                print('Some error1(Ask & Q>0 & timestamp not change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                break\n",
    "                            else:\n",
    "                                pass\n",
    "                        elif position_ == len(order_book_ask)-1:    \n",
    "                            if order_book_ask[position_ - 1:position_ - 1 + 1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:        \n",
    "                                print('Some error1(Ask & Q>0 & timestamp not change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                break\n",
    "                            else:\n",
    "                                pass\n",
    "                        elif position_ == 0 and len(order_book_ask) == 1:\n",
    "                            pass\n",
    "                    else:\n",
    "                        pass\n",
    "                elif data.TimeStamp[i] != x1[temp_ask]:\n",
    "                    if temp_ask == 0:\n",
    "                        temp_ask = temp_ask + 1\n",
    "                        best_price = data[i:(i+1)]['BestPrice']\n",
    "                        position_ = int(data[['OrderBookPosition']][i:i+1].iloc[0]) - 1 \n",
    "                        order_book_ask = insert(order_book_ask,data[['Price','OrderNumber','QuantityDifference','QuantityDifference_']][i:i+1],position_)                  \n",
    "\n",
    "                        if time_second > 32400 and time_second < 57300:\n",
    "                            if position_ == 0 and len(order_book_ask) > 1:\n",
    "                                if order_book_ask[position_+1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:\n",
    "                                    print('Some error2(Ask & Q>0 & timestamp change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                    break\n",
    "                                else:\n",
    "                                    pass\n",
    "                            elif 0 < position_< len(order_book_ask)-1:\n",
    "                                if order_book_ask[position_+ 1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_ask[position_-1:position_-1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]: \n",
    "                                    print('Some error2(Ask & Q>0 & timestamp change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))    \n",
    "                                    break\n",
    "                                else:\n",
    "                                    pass\n",
    "                            elif position_ == len(order_book_ask)-1: \n",
    "                                if order_book_ask[position_-1:position_-1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]: \n",
    "                                    print('Some error2(Ask & Q>0 & timestamp change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))     \n",
    "                                    break\n",
    "                                else:\n",
    "                                    pass\n",
    "                            elif position_ == 0 and len(order_book_ask) == 1:\n",
    "                                pass\n",
    "                        else:\n",
    "                            pass\n",
    "                    else:\n",
    "                        order_book_bid_sum,order_book_ask_sum = order_book_to_csv(order_book_bid,order_book_ask,data,i)\n",
    "                        if time_second > 32400 and time_second < 57300:\n",
    "                            if round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) > 0.03 or\\\n",
    "                            round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) < 0:\n",
    "                                if data[i-1:i].BidOrAsk.iloc[0] == 'A':\n",
    "                                    if order_book_ask_sum[0:1].values.tolist()[0][1] == data[i-1:i].BestQuantity.iloc[0]:\n",
    "                                        pass\n",
    "                                    else:\n",
    "                                        # print 'Best ask quantity is false'\n",
    "                                        pass\n",
    "                                        #break \n",
    "                                else:\n",
    "                                    j = i - 1\n",
    "                                    while j >= 1:\n",
    "                                        if data[j-1:j].BidOrAsk.iloc[0] == 'A':\n",
    "                                            if order_book_ask_sum[0:1].values.tolist()[0][1] == data[j-1:j].BestQuantity.iloc[0]:\n",
    "                                                break\n",
    "                                        else:\n",
    "                                            j = j - 1\n",
    "                                            pass\n",
    "                            else:\n",
    "                                pass\n",
    "                        else:\n",
    "                            pass\n",
    "                        position_ = int(data[['OrderBookPosition']][i:i+1].iloc[0]) - 1 \n",
    "                        temp_ask = temp_ask + 1\n",
    "                       \n",
    "                        order_book_ask = insert(order_book_ask,data[['Price','OrderNumber','QuantityDifference','QuantityDifference_']][i:i+1],position_)\n",
    "                        if time_second > 32400 and time_second < 57300:\n",
    "                            if position_ == 0:\n",
    "                                 \n",
    "                                if order_book_ask[position_+1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]: \n",
    "                                    print('Some error3(Ask & Q>0 & timestamp change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                    break\n",
    "                                else:\n",
    "                                    pass\n",
    "                            elif 0 < position_< len(order_book_ask)-1:\n",
    "                                if order_book_ask[position_+1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]: \n",
    "                                    print('Some error3(Ask & Q>0 & timestamp change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                    break\n",
    "                                else:\n",
    "                                    pass\n",
    "                            elif position_ == len(order_book_ask)-1: \n",
    "                                if order_book_ask[position_-1:position_-1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]: \n",
    "                                    print('Some error3(Ask & Q>0 & timestamp change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))      \n",
    "                                    break\n",
    "                                else:\n",
    "                                    pass\n",
    "                            elif position_ == 0 and len(order_book_ask[0]) == 1:\n",
    "                                pass\n",
    "                        else:\n",
    "                            pass\n",
    "            elif int(data[['QuantityDifference']][i:i+1].values) < 0:    \n",
    "                if data.TimeStamp[i] == x1[temp_ask]: \n",
    "                    order_number_ =  data['OrderNumber'][i:i + 1].iloc[0]\n",
    "                    position_ = order_book_ask[order_book_ask.OrderNumber == order_number_].index[0]                \n",
    "                    price_ = data[i:i+1]['Price'].iloc[0]\n",
    "                    if time_second > 32400 and time_second < 57300:\n",
    "                        if position_ == 0 and len(order_book_ask) > 1:\n",
    "                            if order_book_ask[position_+1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:    \n",
    "                                print('Some error4(Ask & Q<0 & timestamp not change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                break\n",
    "                            else:                  \n",
    "                                pass\n",
    "                        elif 0 < position_< len(order_book_ask)-1:\n",
    "                            if order_book_ask[position_+1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:                             \n",
    "                                print('Some error4(Ask & Q<0 & timestamp not change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                break\n",
    "                            else:  \n",
    "                                pass \n",
    "                        elif position_ == len(order_book_ask)-1:\n",
    "                            if position_ > 0 and order_book_ask[position_-1:position_-1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]: \n",
    "                                print('Some error4(Ask & Q<0 & timestamp not change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))  \n",
    "                                break\n",
    "                            elif position_ == 0:\n",
    "                                pass                        \n",
    "                            else:                     \n",
    "                                pass\n",
    "                        elif position_ == 0 and len(order_book_ask) == 1:    \n",
    "                            pass\n",
    "                    else:\n",
    "                        pass\n",
    "                    if order_book_ask[(order_book_ask.OrderNumber == order_number_)&(order_book_ask.Price == price_)]['QuantityDifference'].iloc[0] == abs(data[i:i+1]['QuantityDifference'].iloc[0]):\n",
    "                        order_book_ask = order_book_ask.drop(order_book_ask.index[[position_]]).reset_index(drop = True)                \n",
    "                    else:\n",
    "                        order_book_ask['QuantityDifference'][order_book_ask.OrderNumber == order_number_] = order_book_ask['QuantityDifference'][order_book_ask.OrderNumber == order_number_] + data[i:i+1]['QuantityDifference'].iloc[0]\n",
    "\n",
    "                elif data.TimeStamp[i] != x1[temp_ask]:\n",
    "                    \n",
    "                    order_book_bid_sum,order_book_ask_sum = order_book_to_csv(order_book_bid,order_book_ask,data,i)\n",
    "                    if time_second > 32400 and time_second < 57300:\n",
    "                        if round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) > 0.03 or\\\n",
    "                        round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) < 0:\n",
    "                            if data[i-1:i].BidOrAsk.iloc[0] == 'A':\n",
    "                                if order_book_ask_sum[0:1].values.tolist()[0][1] == data[i-1:i].BestQuantity.iloc[0]:\n",
    "                                    pass\n",
    "                                else:\n",
    "                                    print('Best ask quantity is false')\n",
    "                                    #break\n",
    "                                    pass\n",
    "                            else:\n",
    "                                j = i - 1\n",
    "                                while j >= 1:\n",
    "                                    if data[j-1:j].BidOrAsk.iloc[0] == 'A':\n",
    "                                        if order_book_ask_sum[0:1].values.tolist()[0][1] == data[j-1:j].BestQuantity.iloc[0]:\n",
    "                                            break\n",
    "                                    else:\n",
    "                                        j = j - 1\n",
    "                                        pass\n",
    "                    else:\n",
    "                        pass\n",
    "                    order_number_ =  data['OrderNumber'][i : i + 1].iloc[0]\n",
    "                    position_ = order_book_ask[order_book_ask.OrderNumber == order_number_].index[0]\n",
    "                    price_ = data[i:i+1]['Price'].iloc[0]\n",
    "                    temp_ask = temp_ask + 1\n",
    "                    if time_second > 32400 and time_second < 57300:\n",
    "                        if position_ == 0 and len(order_book_ask) > 1:\n",
    "                            if order_book_ask[position_ + 1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:\n",
    "                                print('Some error5(Ask & Q<0 & timestamp change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))   \n",
    "                                break\n",
    "                            else:\n",
    "                                pass\n",
    "                        elif 0 < position_< len(order_book_ask)-1:    \n",
    "                            if order_book_ask[position_ + 1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:                             \n",
    "                                print('Some error5(Ask & Q<0 & timestamp change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))     \n",
    "                                break\n",
    "                            else:\n",
    "                                pass\n",
    "                        elif position_ == len(order_book_ask)-1:\n",
    "                            if position_ > 0 and order_book_ask[position_-1:position_-1 + 1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]: \n",
    "                                print('Some error5(Ask & Q<0 & timestamp change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))     \n",
    "                                break\n",
    "                            elif position_ == 0:\n",
    "                                pass                       \n",
    "                            else:                       \n",
    "                                pass\n",
    "                        elif position_ == 0 and len(order_book_ask) == 1:\n",
    "                            pass      \n",
    "                    else:\n",
    "                        pass\n",
    "                    if order_book_ask[(order_book_ask.OrderNumber == order_number_)&(order_book_ask.Price == price_)]['QuantityDifference'].iloc[0] == abs(data[i:i+1]['QuantityDifference'].iloc[0]):                    \n",
    "                        order_book_ask = order_book_ask.drop(order_book_ask.index[[position_]]).reset_index(drop = True)\n",
    "                    else:\n",
    "                        order_book_ask['QuantityDifference'][order_book_ask.OrderNumber == order_number_] = order_book_ask['QuantityDifference'][order_book_ask.OrderNumber == order_number_] + data[i:i+1]['QuantityDifference'].iloc[0]\n",
    "\n",
    "        elif data.BidOrAsk[i] == 'B':\n",
    "            data_bid_Quantity = data.BestQuantity[i]\n",
    "            if int(data[['QuantityDifference']][i:i+1].values) > 0: \n",
    "               \n",
    "                if order_book_ask.Price[0] <= data[i:i+1].Price.iloc[0] and time_second < 32400:\n",
    "                    for k in range(0,len(order_book_ask)):\n",
    "                        diff = order_book_ask.QuantityDifference_[k] - data[i:i+1].QuantityDifference_.iloc[0]\n",
    "                        if order_book_ask.Price[k] <= data[i:i+1].Price.iloc[0] and diff >= 0:\n",
    "                            order_book_ask.QuantityDifference_[k] = diff\n",
    "                            data[i:i+1].QuantityDifference_.iloc[0] = 0\n",
    "                            break\n",
    "                        elif order_book_ask.Price[k] <= data[i:i+1].Price.iloc[0] and diff < 0:\n",
    "                            order_book_ask.QuantityDifference_[k] = 0\n",
    "                            data[i:i+1].QuantityDifference_.iloc[0] = - diff\n",
    "                            pass\n",
    "                        else:\n",
    "                            break\n",
    "                if data.TimeStamp[i] == x2[temp_bid]:\n",
    "                    position_ = int(data[['OrderBookPosition']][i:i+1].iloc[0]) - 1 \n",
    "                    order_book_bid = insert(order_book_bid,data[['Price','OrderNumber','QuantityDifference','QuantityDifference_']][i:i+1],position_)                \n",
    "                    if time_second > 32400 and time_second < 57300:\n",
    "                        if position_ == 0 and len(order_book_bid) > 1:              \n",
    "                            if order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                                            \n",
    "                                print('Some error1(Bid & Q>0 & timestamp not change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                break\n",
    "                            else:                    \n",
    "                                pass \n",
    "                        elif 0 < position_< len(order_book_bid)-1: \n",
    "                            if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                             \n",
    "                                print('Some error1(Bid & Q>0 & timestamp not change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                break    \n",
    "                            else:   \n",
    "                                pass   \n",
    "                        elif position_ == len(order_book_bid)-1 and len(order_book_bid) > 1:   \n",
    "                            if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:                        \n",
    "                                print('Some error1(Bid & Q>0 & timestamp not change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                break\n",
    "                            else:                \n",
    "                                pass\n",
    "                        elif position_ == 0 and len(order_book_bid[temp_bid]) == 1:\n",
    "                            pass \n",
    "                    else:\n",
    "                        pass\n",
    "                elif data.TimeStamp[i] != x2[temp_bid]:\n",
    "                    if temp_bid == 0:\n",
    "                        best_price = data[i:(i+1)]['BestPrice']             \n",
    "                        position_ = int(data[['OrderBookPosition']][i:i+1].iloc[0]) - 1 \n",
    "                        temp_bid = temp_bid + 1                 \n",
    "                        order_book_bid = insert(order_book_bid,data[['Price','OrderNumber','QuantityDifference','QuantityDifference_']][i:i+1],position_)                                     \n",
    "                        if time_second > 32400 and time_second < 57300:\n",
    "                            if position_ == 0  and len(order_book_bid) > 1:\n",
    "                                if order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0] or order_book_bid['Price'][0:1].iloc[0] != data['BestPrice'][i]:                                \n",
    "                                    print('Some error2(Bid & Q>0 & timestamp change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0]) + data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                    break\n",
    "                                else:                   \n",
    "                                    pass      \n",
    "                            elif 0 < position_< len(order_book_bid)-1:    \n",
    "                                if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                                \n",
    "                                    print('Some error2(Bid & Q>0 & timestamp change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                    break\n",
    "                                else: \n",
    "                                    pass\n",
    "                            elif position_ == len(order_book_bid)-1:\n",
    "                                if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_bid['Price'][0:1].iloc[0] != data['BestPrice'][i]:                                \n",
    "                                    print('Some error2(Bid & Q>0 & timestamp change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))                                        \n",
    "                                    pass\n",
    "                                else:\n",
    "                                    pass\n",
    "                            elif position_ == 0 and len(order_book_bid) == 1: \n",
    "                                    pass\n",
    "                        else:\n",
    "                            pass\n",
    "                    else:\n",
    "                        if time_second > 32400 and time_second < 57300:\n",
    "                            if round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) > 0.03 or\\\n",
    "                            round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) < 0:\n",
    "                                order_book_bid_sum,order_book_ask_sum = order_book_to_csv(order_book_bid,order_book_ask,data,i)                    \n",
    "                                if data[i-1:i].BidOrAsk.iloc[0] == 'B':\n",
    "                                    if order_book_bid_sum[0:1].values.tolist()[0][1] == data[i-1:i].BestQuantity.iloc[0]:\n",
    "                                        pass\n",
    "                                    else:\n",
    "                                        print('Best bid quantity is false')\n",
    "                                        #break\n",
    "                                        pass\n",
    "                                else:\n",
    "                                    j = i - 1\n",
    "                                    while j >= 1:\n",
    "                                        if data[j-1:j].BidOrAsk.iloc[0] == 'B':\n",
    "                                            if order_book_bid_sum[0:1].values.tolist()[0][1] == data[j-1:j].BestQuantity.iloc[0]:\n",
    "                                                break\n",
    "                                            else:\n",
    "                                                print('Best bid quantity is false')\n",
    "                                                #break\n",
    "                                                pass\n",
    "                                        else:\n",
    "                                            j = j - 1\n",
    "                                            pass\n",
    "                        else:\n",
    "                            pass\n",
    "                        position_ = int(data[['OrderBookPosition']][i:i+1].iloc[0]) - 1 \n",
    "                        temp_bid = temp_bid + 1 \n",
    "                        order_book_bid = insert(order_book_bid,data[['Price','OrderNumber','QuantityDifference','QuantityDifference_']][i:i+1],position_)                       \n",
    "                        if time_second > 32400 and time_second < 57300:\n",
    "                            if position_ == 0 and len(order_book_bid) > 1:\n",
    "                                if order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0] or order_book_bid['Price'][0:1].iloc[0] != data['BestPrice'][i]:                                \n",
    "                                    print('Some error3(Bid & Q>0 & timestamp change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))     \n",
    "                                    break\n",
    "                                else:                    \n",
    "                                    pass\n",
    "                            elif 0 < position_< len(order_book_bid)-1:    \n",
    "                                if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                                \n",
    "                                    print('Some error3(Bid & Q>0 & timestamp change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                    break\n",
    "                                else: \n",
    "                                    pass\n",
    "                            elif position_ == len(order_book_bid)-1:   \n",
    "                                if order_book_bid[position_-1:position_-1 + 1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_bid['Price'][0:1].iloc[0] != data['BestPrice'][i]:                                \n",
    "                                    print('Some error3(Bid & Q>0 & timestamp change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))      \n",
    "                                    break\n",
    "                                else:                      \n",
    "                                    pass\n",
    "                            elif position_ == 0 and len(order_book_bid[0]) == 1:\n",
    "                                pass\n",
    "                        else:\n",
    "                            pass\n",
    "            elif int(data[['QuantityDifference']][i:i+1].values) < 0:    \n",
    "                if data.TimeStamp[i] == x2[temp_bid]: \n",
    "                    order_number_ =  data['OrderNumber'][i : i + 1].iloc[0]\n",
    "                    position_ = order_book_bid[order_book_bid.OrderNumber == order_number_].index[0]                \n",
    "                    price_ = data[i:i+1]['Price'].iloc[0]\n",
    "                    if time_second > 32400 and time_second < 57300:\n",
    "                        if position_ == 0 and len(order_book_bid) > 1:    \n",
    "                            if order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                            \n",
    "                                print('Some error4(Bid & Q<0 & timestamp not change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                break\n",
    "                            else:                     \n",
    "                                pass               \n",
    "                        elif 0 < position_< len(order_book_bid)-1:      \n",
    "                            if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                            \n",
    "                                print('Some error4(Bid & Q<0 & timestamp not change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                break\n",
    "                            else: \n",
    "                                pass\n",
    "                        elif position_ == len(order_book_bid)-1:    \n",
    "                            if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:                            \n",
    "                                print('Some error4(Bid & Q<0 & timestamp not change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))    \n",
    "                                break\n",
    "                            elif position_ == 0:\n",
    "                                pass\n",
    "                            else:                        \n",
    "                                pass\n",
    "                        elif position_ == 0 and len(order_book_bid) == 1:\n",
    "                            pass\n",
    "                    else:\n",
    "                        pass\n",
    "                    if order_book_bid[(order_book_bid.OrderNumber == order_number_)&(order_book_bid.Price == price_)]['QuantityDifference'].iloc[0] == abs(data[i:i+1]['QuantityDifference'].iloc[0]):                    \n",
    "                        order_book_bid = order_book_bid.drop(order_book_bid.index[[position_]]).reset_index(drop = True)                \n",
    "                    else:\n",
    "                        order_book_bid['QuantityDifference'][order_book_bid.OrderNumber == order_number_] = order_book_bid['QuantityDifference'][order_book_bid.OrderNumber == order_number_] + data[i:i+1]['QuantityDifference'].iloc[0]                            \n",
    "                elif data.TimeStamp[i] != x2[temp_bid]:\n",
    "                    if time_second > 32400 and time_second < 57300:\n",
    "                        if round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) > 0.03 or\\\n",
    "                        round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) < 0:\n",
    "                            order_book_bid_sum,order_book_ask_sum = order_book_to_csv(order_book_bid,order_book_ask,data,i)\n",
    "                            if data[i-1:i].BidOrAsk.iloc[0] == 'B':\n",
    "                                if order_book_bid_sum[0:1].values.tolist()[0][1] == data[i-1:i].BestQuantity.iloc[0]:\n",
    "                                    pass\n",
    "                                else:\n",
    "                                    print('Best bid quantity is false')\n",
    "                                    #break\n",
    "                                    pass\n",
    "                            else:\n",
    "                                j = i - 1\n",
    "                                while j >= 1:\n",
    "                                    if data[j-1:j].BidOrAsk.iloc[0] == 'B':\n",
    "                                        if order_book_bid_sum[0:1].values.tolist()[0][1] == data[j-1:j].BestQuantity.iloc[0]:\n",
    "                                            break\n",
    "                                        else:\n",
    "                                            print('Best bid quantity is false')\n",
    "                                            #break\n",
    "                                            pass\n",
    "                                    else:\n",
    "                                        j = j - 1\n",
    "                                        pass\n",
    "                    else:\n",
    "                        pass\n",
    "                    order_number_ =  data['OrderNumber'][i:i+1].iloc[0]\n",
    "                    position_ = order_book_bid[order_book_bid.OrderNumber == order_number_].index[0]\n",
    "                    price_ = data[i:i+1]['Price'].iloc[0]\n",
    "                    temp_bid = temp_bid + 1\n",
    "                    if time_second > 32400 and time_second < 57300:\n",
    "                        if position_ == 0 and len(order_book_bid) > 1:   \n",
    "                            if order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                            \n",
    "                                print('Some error5(Bid & Q<0 & timestamp change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                break\n",
    "                            else:                   \n",
    "                                pass   \n",
    "                        elif 0 < position_< len(order_book_bid)-1:    \n",
    "                            if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                            \n",
    "                                print('Some error5(Bid & Q<0 & timestamp change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                break\n",
    "                            else:  \n",
    "                                pass\n",
    "                        elif position_ == len(order_book_bid)-1:    \n",
    "                            if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:                            \n",
    "                                print('Some error5(Bid & Q<0 & timestamp change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0]))\n",
    "                                break\n",
    "                            elif position_ == 0:\n",
    "                                pass\n",
    "                            else:                      \n",
    "                                pass    \n",
    "                        elif position_ == 0 and len(order_book_bid) == 1:\n",
    "                            pass  \n",
    "                    else:\n",
    "                        pass\n",
    "                    if order_book_bid[(order_book_bid.OrderNumber == order_number_)&(order_book_bid.Price == price_)]['QuantityDifference'].iloc[0] == abs(data[i:i+1]['QuantityDifference'].iloc[0]):\n",
    "                        order_book_bid = order_book_bid.drop(order_book_bid.index[[position_]]).reset_index(drop = True)                \n",
    "                    else:\n",
    "                        order_book_bid['QuantityDifference'][order_book_bid.OrderNumber == order_number_] = order_book_bid['QuantityDifference'][order_book_bid.OrderNumber == order_number_] + data[i:i+1]['QuantityDifference'].iloc[0]\n",
    "    return data, order_book_bid, order_book_ask, order_book_bid_sum, order_book_ask_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2\n",
      "timestamp = 2014-01-02D04:19:51.857166800\n",
      "-------------------------------------------\n",
      "index_find = 16\n",
      "---------------------------------\n",
      "    Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "17      0               210.0        A  2014-01-02D08:45:00.130582600\n",
      "17 0 0\n",
      "2014-01-02D08:45:00.130582600 2014-01-02D04:19:51.857166800 2014-01-02D04:19:51.857166800\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "18  700500                -2.0        B  2014-01-02D08:45:00.131582600\n",
      "18 0 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D04:19:51.857166800 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "19  682000                 2.0        B  2014-01-02D08:45:00.131582600\n",
      "19 1 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "20  699000                -2.0        B  2014-01-02D08:45:00.131582600\n",
      "20 1 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "21  682000                 2.0        B  2014-01-02D08:45:00.131582600\n",
      "21 1 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "22  697500                -1.0        B  2014-01-02D08:45:00.131582600\n",
      "22 1 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "23  682000                 1.0        B  2014-01-02D08:45:00.131582600\n",
      "23 1 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "24  690500                -1.0        B  2014-01-02D08:45:00.131582600\n",
      "24 1 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "25  682000                 1.0        B  2014-01-02D08:45:00.131582600\n",
      "25 1 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "26  690000                -2.0        B  2014-01-02D08:45:00.131582600\n",
      "26 1 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "27  682000                 2.0        B  2014-01-02D08:45:00.131582600\n",
      "27 1 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "28  685000                -2.0        B  2014-01-02D08:45:00.131582600\n",
      "28 1 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "29  682000                 2.0        B  2014-01-02D08:45:00.131582600\n",
      "29 1 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "30  683500                -1.0        B  2014-01-02D08:45:00.131582600\n",
      "30 1 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "31  682000                 1.0        B  2014-01-02D08:45:00.131582600\n",
      "31 1 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "    Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "32      0              -210.0        A  2014-01-02D08:45:00.131582600\n",
      "32 1 1\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.130582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "33  682000               210.0        A  2014-01-02D08:45:00.131582600\n",
      "33 1 2\n",
      "2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "34  682000               210.0        B  2014-01-02D08:45:00.296582600\n",
      "34 1 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "35  682000              -210.0        B  2014-01-02D08:45:00.296582600\n",
      "35 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "36  708000               210.0        B  2014-01-02D08:45:00.296582600\n",
      "36 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "37  682000                -2.0        B  2014-01-02D08:45:00.296582600\n",
      "37 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "38  700500                 2.0        B  2014-01-02D08:45:00.296582600\n",
      "38 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "39  682000                -2.0        B  2014-01-02D08:45:00.296582600\n",
      "39 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "40  699000                 2.0        B  2014-01-02D08:45:00.296582600\n",
      "40 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "41  682000                -1.0        B  2014-01-02D08:45:00.296582600\n",
      "41 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "42  697500                 1.0        B  2014-01-02D08:45:00.296582600\n",
      "42 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "43  682000                -1.0        B  2014-01-02D08:45:00.296582600\n",
      "43 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "44  690500                 1.0        B  2014-01-02D08:45:00.296582600\n",
      "44 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "45  682000                -2.0        B  2014-01-02D08:45:00.296582600\n",
      "45 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "46  690000                 2.0        B  2014-01-02D08:45:00.296582600\n",
      "46 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "47  682000                -2.0        B  2014-01-02D08:45:00.296582600\n",
      "47 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "48  685000                 2.0        B  2014-01-02D08:45:00.296582600\n",
      "48 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "49  682000                -1.0        B  2014-01-02D08:45:00.296582600\n",
      "49 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "50  683500                 1.0        B  2014-01-02D08:45:00.296582600\n",
      "50 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "51  682000              -210.0        A  2014-01-02D08:45:00.296582600\n",
      "51 2 2\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.131582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "52  708000               210.0        A  2014-01-02D08:45:00.296582600\n",
      "52 2 3\n",
      "2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "53  695500                 1.0        B  2014-01-02D08:45:00.398582300\n",
      "53 2 3\n",
      "2014-01-02D08:45:00.398582300 2014-01-02D08:45:00.296582600 2014-01-02D08:45:00.296582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "54  748000                 5.0        A  2014-01-02D08:45:13.857414600\n",
      "54 3 3\n",
      "2014-01-02D08:45:13.857414600 2014-01-02D08:45:00.398582300 2014-01-02D08:45:00.296582600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "55  738000                 4.0        A  2014-01-02D08:45:14.337414600\n",
      "55 3 4\n",
      "2014-01-02D08:45:14.337414600 2014-01-02D08:45:00.398582300 2014-01-02D08:45:13.857414600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "56  683500                 3.0        B  2014-01-02D08:45:14.977402600\n",
      "56 3 5\n",
      "2014-01-02D08:45:14.977402600 2014-01-02D08:45:00.398582300 2014-01-02D08:45:14.337414600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "57  759000                 5.0        A  2014-01-02D08:45:15.557390600\n",
      "57 4 5\n",
      "2014-01-02D08:45:15.557390600 2014-01-02D08:45:14.977402600 2014-01-02D08:45:14.337414600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "58  653500                 3.0        B  2014-01-02D08:45:17.727366600\n",
      "58 4 6\n",
      "2014-01-02D08:45:17.727366600 2014-01-02D08:45:14.977402600 2014-01-02D08:45:15.557390600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "59  663500                 3.0        B  2014-01-02D08:45:18.317366600\n",
      "59 5 6\n",
      "2014-01-02D08:45:18.317366600 2014-01-02D08:45:17.727366600 2014-01-02D08:45:15.557390600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "60  725000                 4.0        A  2014-01-02D08:45:30.059222600\n",
      "60 6 6\n",
      "2014-01-02D08:45:30.059222600 2014-01-02D08:45:18.317366600 2014-01-02D08:45:15.557390600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "61  731500                 1.0        A  2014-01-02D08:45:36.809138600\n",
      "61 6 7\n",
      "2014-01-02D08:45:36.809138600 2014-01-02D08:45:18.317366600 2014-01-02D08:45:30.059222600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "62  736000                 1.0        A  2014-01-02D08:45:52.185958600\n",
      "62 6 8\n",
      "2014-01-02D08:45:52.185958600 2014-01-02D08:45:18.317366600 2014-01-02D08:45:36.809138600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "63  690000                75.0        B  2014-01-02D08:46:00.014862600\n",
      "63 6 9\n",
      "2014-01-02D08:46:00.014862600 2014-01-02D08:45:18.317366600 2014-01-02D08:45:52.185958600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "64  712000                 2.0        A  2014-01-02D08:46:00.018862600\n",
      "64 7 9\n",
      "2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.014862600 2014-01-02D08:45:52.185958600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "65  708000              -210.0        B  2014-01-02D08:46:00.018862600\n",
      "65 7 10\n",
      "2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.014862600 2014-01-02D08:46:00.018862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "66  706500               210.0        B  2014-01-02D08:46:00.018862600\n",
      "66 8 10\n",
      "2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.018862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "67  708000              -210.0        A  2014-01-02D08:46:00.018862600\n",
      "67 8 10\n",
      "2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.018862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "68  706500               210.0        A  2014-01-02D08:46:00.018862600\n",
      "68 8 10\n",
      "2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.018862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "69  770000                 6.0        A  2014-01-02D08:46:00.018862600\n",
      "69 8 10\n",
      "2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.018862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "70  754000                 3.0        A  2014-01-02D08:46:00.021862600\n",
      "70 8 10\n",
      "2014-01-02D08:46:00.021862600 2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.018862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "71  760000                15.0        A  2014-01-02D08:46:00.022862600\n",
      "71 8 11\n",
      "2014-01-02D08:46:00.022862600 2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.021862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "72  770000                 6.0        A  2014-01-02D08:46:00.024862600\n",
      "72 8 12\n",
      "2014-01-02D08:46:00.024862600 2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.022862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "73  770000                 2.0        A  2014-01-02D08:46:00.025862600\n",
      "73 8 13\n",
      "2014-01-02D08:46:00.025862600 2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.024862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "74  770000                 6.0        A  2014-01-02D08:46:00.029862600\n",
      "74 8 14\n",
      "2014-01-02D08:46:00.029862600 2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.025862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "75  770000                 6.0        A  2014-01-02D08:46:00.029862600\n",
      "75 8 15\n",
      "2014-01-02D08:46:00.029862600 2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.029862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "76  702000                 1.0        B  2014-01-02D08:46:00.033862600\n",
      "76 8 15\n",
      "2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.018862600 2014-01-02D08:46:00.029862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "77  706500              -210.0        B  2014-01-02D08:46:00.033862600\n",
      "77 9 15\n",
      "2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.029862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "78  707000               210.0        B  2014-01-02D08:46:00.033862600\n",
      "78 9 15\n",
      "2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.029862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "79  706500              -210.0        A  2014-01-02D08:46:00.033862600\n",
      "79 9 15\n",
      "2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.029862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "80  707000               210.0        A  2014-01-02D08:46:00.033862600\n",
      "80 9 16\n",
      "2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.033862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "81  673000                 8.0        B  2014-01-02D08:46:00.033862600\n",
      "81 9 16\n",
      "2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.033862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "82  748000                 5.0        A  2014-01-02D08:46:00.036862600\n",
      "82 9 16\n",
      "2014-01-02D08:46:00.036862600 2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.033862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "83  725000                 1.0        A  2014-01-02D08:46:00.037862600\n",
      "83 9 17\n",
      "2014-01-02D08:46:00.037862600 2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.036862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "84  744000                 1.0        A  2014-01-02D08:46:00.039862600\n",
      "84 9 18\n",
      "2014-01-02D08:46:00.039862600 2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.037862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "85  701000                 1.0        B  2014-01-02D08:46:00.040862600\n",
      "85 9 19\n",
      "2014-01-02D08:46:00.040862600 2014-01-02D08:46:00.033862600 2014-01-02D08:46:00.039862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "86  707000                 1.0        B  2014-01-02D08:46:00.043862600\n",
      "86 10 19\n",
      "2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.040862600 2014-01-02D08:46:00.039862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "87  707000              -210.0        B  2014-01-02D08:46:00.043862600\n",
      "87 11 19\n",
      "2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.039862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "88  709500               210.0        B  2014-01-02D08:46:00.043862600\n",
      "88 11 19\n",
      "2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.039862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "89  707000              -210.0        A  2014-01-02D08:46:00.043862600\n",
      "89 11 19\n",
      "2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.039862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "90  709500               210.0        A  2014-01-02D08:46:00.043862600\n",
      "90 11 20\n",
      "2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.043862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "91  747000                 2.0        A  2014-01-02D08:46:00.045862600\n",
      "91 11 20\n",
      "2014-01-02D08:46:00.045862600 2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.043862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "92  712000                 2.0        A  2014-01-02D08:46:00.048862600\n",
      "92 11 21\n",
      "2014-01-02D08:46:00.048862600 2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.045862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "93  736000                 1.0        A  2014-01-02D08:46:00.048862600\n",
      "93 11 22\n",
      "2014-01-02D08:46:00.048862600 2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.048862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "94  756000                 1.0        A  2014-01-02D08:46:00.052862600\n",
      "94 11 22\n",
      "2014-01-02D08:46:00.052862600 2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.048862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "95  746000                 1.0        A  2014-01-02D08:46:00.056862600\n",
      "95 11 23\n",
      "2014-01-02D08:46:00.056862600 2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.052862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "96  720000                 2.0        A  2014-01-02D08:46:00.061862600\n",
      "96 11 24\n",
      "2014-01-02D08:46:00.061862600 2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.056862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "97  760000                 4.0        A  2014-01-02D08:46:00.065862600\n",
      "97 11 25\n",
      "2014-01-02D08:46:00.065862600 2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.061862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "98  680000                 2.0        B  2014-01-02D08:46:00.069862600\n",
      "98 11 26\n",
      "2014-01-02D08:46:00.069862600 2014-01-02D08:46:00.043862600 2014-01-02D08:46:00.065862600\n",
      "---------------------------------\n",
      "     Price  QuantityDifference BidOrAsk                      TimeStamp\n",
      "99  675000                 2.0        B  2014-01-02D08:46:00.069862600\n",
      "99 12 26\n",
      "2014-01-02D08:46:00.069862600 2014-01-02D08:46:00.069862600 2014-01-02D08:46:00.065862600\n",
      "Total time = 1.131267\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    path = './data/CN_Futures_'\n",
    "    year = 2014\n",
    "    # 决定生成的每一个tick中有多少组bid和ask\n",
    "    best_price_number = 3\n",
    "    \n",
    "    # Future Delivery Months 近月合約代碼 : series\n",
    "    # 2014 :CNF14:1月, CNG14:2月, CNH14:3月, CNJ14:4月, CNK14:5月, CNM14:6月,\n",
    "    #       CNN14:7月, CNQ14:8月, CNU14:9月, CNV14:10月, CNX14:11月, CNZ14:12月\n",
    "    # 2015 :CNF15:1月, CNG15:2月, CNH15:3月, CNJ15:4月, CNK15:5月, CNM15:6月,\n",
    "    #       CNN15:7月, CNQ15:8月, CNU15:9月, CNV15:10月, CNX15:11月, CNZ15:12月\n",
    "        \n",
    "    series = 'CNF14' \n",
    "    month = 1\n",
    "    day_ = [2]\n",
    "    for i in day_:\n",
    "        print(i)\n",
    "        start = time.time()\n",
    "        data, order_book_bid, order_book_ask, order_book_bid_sum, order_book_ask_sum = order_book_tranform(year,month,i,path,best_price_number,series)\n",
    "        end = time.time()\n",
    "        print(\"Total time = %f\"%(end - start))   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Series</th>\n",
       "      <th>SequenceNumber</th>\n",
       "      <th>TimeStamp</th>\n",
       "      <th>OrderNumber</th>\n",
       "      <th>OrderBookPosition</th>\n",
       "      <th>Price</th>\n",
       "      <th>QuantityDifference</th>\n",
       "      <th>Trade</th>\n",
       "      <th>BidOrAsk</th>\n",
       "      <th>BestPrice</th>\n",
       "      <th>BestQuantity</th>\n",
       "      <th>QuantityDifference_</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>1</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>642F9A8039E51EE5</td>\n",
       "      <td>1</td>\n",
       "      <td>757000</td>\n",
       "      <td>3.0</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>715500</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>2</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>490D725B88E56DE5</td>\n",
       "      <td>1</td>\n",
       "      <td>755000</td>\n",
       "      <td>10.0</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>715500</td>\n",
       "      <td>2</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>3</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>E1C9F25394A679A6</td>\n",
       "      <td>1</td>\n",
       "      <td>719500</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>715500</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>4</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>ECA71A88142AF92B</td>\n",
       "      <td>4</td>\n",
       "      <td>768500</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>715500</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>5</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>E325782FEB07CE67</td>\n",
       "      <td>1</td>\n",
       "      <td>700500</td>\n",
       "      <td>2.0</td>\n",
       "      <td></td>\n",
       "      <td>B</td>\n",
       "      <td>700500</td>\n",
       "      <td>2</td>\n",
       "      <td>2.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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97475</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>97476</td>\n",
       "      <td>2014-01-03D02:20:00.060617300</td>\n",
       "      <td>E3B7552341112551</td>\n",
       "      <td>13</td>\n",
       "      <td>770000</td>\n",
       "      <td>-6.0</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>708500</td>\n",
       "      <td>2</td>\n",
       "      <td>-6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97476</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>97477</td>\n",
       "      <td>2014-01-03D02:20:00.060617300</td>\n",
       "      <td>473C5923A5D088F0</td>\n",
       "      <td>13</td>\n",
       "      <td>770000</td>\n",
       "      <td>-2.0</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>708500</td>\n",
       "      <td>2</td>\n",
       "      <td>-2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97477</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>97478</td>\n",
       "      <td>2014-01-03D02:20:00.060617300</td>\n",
       "      <td>633F552361CA460A</td>\n",
       "      <td>13</td>\n",
       "      <td>770000</td>\n",
       "      <td>-6.0</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>708500</td>\n",
       "      <td>2</td>\n",
       "      <td>-6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97478</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>97479</td>\n",
       "      <td>2014-01-03D02:20:00.060617300</td>\n",
       "      <td>47BC592368224B42</td>\n",
       "      <td>13</td>\n",
       "      <td>770000</td>\n",
       "      <td>-6.0</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>708500</td>\n",
       "      <td>2</td>\n",
       "      <td>-6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97479</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>97480</td>\n",
       "      <td>2014-01-03D02:20:00.060617300</td>\n",
       "      <td>E3BFD52B096BEDAC</td>\n",
       "      <td>13</td>\n",
       "      <td>770000</td>\n",
       "      <td>-6.0</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>708500</td>\n",
       "      <td>2</td>\n",
       "      <td>-6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>97480 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Series  SequenceNumber                      TimeStamp       OrderNumber  OrderBookPosition   Price  QuantityDifference Trade BidOrAsk  BestPrice  BestQuantity  QuantityDifference_\n",
       "0      CNF14               1  2014-01-02D04:19:51.857166800  642F9A8039E51EE5                  1  757000                 3.0              A     715500             2                  3.0\n",
       "1      CNF14               2  2014-01-02D04:19:51.857166800  490D725B88E56DE5                  1  755000                10.0              A     715500             2                 10.0\n",
       "2      CNF14               3  2014-01-02D04:19:51.857166800  E1C9F25394A679A6                  1  719500                 1.0              A     715500             2                  1.0\n",
       "3      CNF14               4  2014-01-02D04:19:51.857166800  ECA71A88142AF92B                  4  768500                 1.0              A     715500             2                  1.0\n",
       "4      CNF14               5  2014-01-02D04:19:51.857166800  E325782FEB07CE67                  1  700500                 2.0              B     700500             2                  2.0\n",
       "...      ...             ...                            ...               ...                ...     ...                 ...   ...      ...        ...           ...                  ...\n",
       "97475  CNF14           97476  2014-01-03D02:20:00.060617300  E3B7552341112551                 13  770000                -6.0              A     708500             2                 -6.0\n",
       "97476  CNF14           97477  2014-01-03D02:20:00.060617300  473C5923A5D088F0                 13  770000                -2.0              A     708500             2                 -2.0\n",
       "97477  CNF14           97478  2014-01-03D02:20:00.060617300  633F552361CA460A                 13  770000                -6.0              A     708500             2                 -6.0\n",
       "97478  CNF14           97479  2014-01-03D02:20:00.060617300  47BC592368224B42                 13  770000                -6.0              A     708500             2                 -6.0\n",
       "97479  CNF14           97480  2014-01-03D02:20:00.060617300  E3BFD52B096BEDAC                 13  770000                -6.0              A     708500             2                 -6.0\n",
       "\n",
       "[97480 rows x 12 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Price</th>\n",
       "      <th>OrderNumber</th>\n",
       "      <th>QuantityDifference</th>\n",
       "      <th>QuantityDifference_</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>709500</td>\n",
       "      <td>C7C3FA1363164816</td>\n",
       "      <td>210.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>707000</td>\n",
       "      <td>43E3747FD245B565</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>702000</td>\n",
       "      <td>C36B74779D8980A9</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>701000</td>\n",
       "      <td>EF3D516B3FA423E4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>700500</td>\n",
       "      <td>E325782FEB07CE67</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>699000</td>\n",
       "      <td>4959765357F03CF0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>697500</td>\n",
       "      <td>43A57467BFB59F55</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>695500</td>\n",
       "      <td>E70A765B175EFC1F</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>690500</td>\n",
       "      <td>B3F5144E612645E6</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>690000</td>\n",
       "      <td>E27C9AD83BDC20DC</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>690000</td>\n",
       "      <td>43E3747730341354</td>\n",
       "      <td>75.0</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>685000</td>\n",
       "      <td>CACB3B94B2C19781</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>683500</td>\n",
       "      <td>336D9C4E88C46D84</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>683500</td>\n",
       "      <td>E14CDA075D093F49</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>682000</td>\n",
       "      <td>1335141E47F92CF9</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>680000</td>\n",
       "      <td>6FBDD1633B411F81</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>675000</td>\n",
       "      <td>C3EB747F49582C78</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>673000</td>\n",
       "      <td>6F3D516B2DF71237</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>663500</td>\n",
       "      <td>69C45A07D1B9B3F9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>653500</td>\n",
       "      <td>E1CCDA0FA8338A73</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Price       OrderNumber  QuantityDifference  QuantityDifference_\n",
       "0   709500  C7C3FA1363164816               210.0                  0.0\n",
       "1   707000  43E3747FD245B565                 1.0                  1.0\n",
       "2   702000  C36B74779D8980A9                 1.0                  1.0\n",
       "3   701000  EF3D516B3FA423E4                 1.0                  1.0\n",
       "4   700500  E325782FEB07CE67                 2.0                  2.0\n",
       "5   699000  4959765357F03CF0                 2.0                  2.0\n",
       "6   697500  43A57467BFB59F55                 1.0                  1.0\n",
       "7   695500  E70A765B175EFC1F                 1.0                  1.0\n",
       "8   690500  B3F5144E612645E6                 1.0                  1.0\n",
       "9   690000  E27C9AD83BDC20DC                 2.0                  2.0\n",
       "10  690000  43E3747730341354                75.0                 75.0\n",
       "11  685000  CACB3B94B2C19781                 2.0                  2.0\n",
       "12  683500  336D9C4E88C46D84                 1.0                  1.0\n",
       "13  683500  E14CDA075D093F49                 3.0                  3.0\n",
       "14  682000  1335141E47F92CF9               100.0                  0.0\n",
       "15  680000  6FBDD1633B411F81                 2.0                  2.0\n",
       "16  675000  C3EB747F49582C78                 2.0                  2.0\n",
       "17  673000  6F3D516B2DF71237                 8.0                  8.0\n",
       "18  663500  69C45A07D1B9B3F9                 3.0                  3.0\n",
       "19  653500  E1CCDA0FA8338A73                 3.0                  3.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_book_bid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Price</th>\n",
       "      <th>OrderNumber</th>\n",
       "      <th>QuantityDifference</th>\n",
       "      <th>QuantityDifference_</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>709500</td>\n",
       "      <td>47C3FA130EB2F3B3</td>\n",
       "      <td>210.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>712000</td>\n",
       "      <td>6F35516BC930AD70</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>712000</td>\n",
       "      <td>C363747FE0C9C3E9</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>715500</td>\n",
       "      <td>C365FC27C2C8A628</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>717500</td>\n",
       "      <td>CBADF86F77EA578A</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>719500</td>\n",
       "      <td>E1C9F25394A679A6</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>720000</td>\n",
       "      <td>EFB5D16396077A47</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>725000</td>\n",
       "      <td>6BF7787F751753F7</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>725000</td>\n",
       "      <td>6FBD516B9E438283</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>731500</td>\n",
       "      <td>E14C560FA31A855A</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>736000</td>\n",
       "      <td>6144D60F9AB47CF4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>736000</td>\n",
       "      <td>6F35D1636FCB540B</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>738000</td>\n",
       "      <td>61C4DA0743E52625</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>740000</td>\n",
       "      <td>E8ABBA801F3C015C</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>744000</td>\n",
       "      <td>4363747FF224D544</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>745000</td>\n",
       "      <td>C90D7253BB41A041</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>746000</td>\n",
       "      <td>436B747F53EA370A</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>747000</td>\n",
       "      <td>EFBD516BFFA9E3E9</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>748000</td>\n",
       "      <td>E14C5A0F8D8C6FCC</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>748000</td>\n",
       "      <td>C3EB747740052325</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>754000</td>\n",
       "      <td>6FB5516B20B104F1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>755000</td>\n",
       "      <td>490D725B88E56DE5</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>756000</td>\n",
       "      <td>6FB5D1635CBF40FF</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>757000</td>\n",
       "      <td>642F9A8039E51EE5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>757000</td>\n",
       "      <td>64A79A88F278D778</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>759000</td>\n",
       "      <td>61C4DA0FFA35DC75</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>760000</td>\n",
       "      <td>C3E3747718A3FBC4</td>\n",
       "      <td>15.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>760000</td>\n",
       "      <td>6F3DD163510F354F</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>768500</td>\n",
       "      <td>ECA71A88142AF92B</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>770000</td>\n",
       "      <td>C3637477EF36D256</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>770000</td>\n",
       "      <td>EF35516B374E1B8E</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>770000</td>\n",
       "      <td>436B747707D5EAF6</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>770000</td>\n",
       "      <td>EFB5516B13DFF7E0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>770000</td>\n",
       "      <td>43EB7477305D137D</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Price       OrderNumber  QuantityDifference  QuantityDifference_\n",
       "0   709500  47C3FA130EB2F3B3               210.0                  0.0\n",
       "1   712000  6F35516BC930AD70                 2.0                  2.0\n",
       "2   712000  C363747FE0C9C3E9                 2.0                  2.0\n",
       "3   715500  C365FC27C2C8A628                 2.0                  2.0\n",
       "4   717500  CBADF86F77EA578A                 1.0                  1.0\n",
       "5   719500  E1C9F25394A679A6                 1.0                  1.0\n",
       "6   720000  EFB5D16396077A47                 2.0                  2.0\n",
       "7   725000  6BF7787F751753F7                 4.0                  4.0\n",
       "8   725000  6FBD516B9E438283                 1.0                  1.0\n",
       "9   731500  E14C560FA31A855A                 1.0                  1.0\n",
       "10  736000  6144D60F9AB47CF4                 1.0                  1.0\n",
       "11  736000  6F35D1636FCB540B                 1.0                  1.0\n",
       "12  738000  61C4DA0743E52625                 4.0                  4.0\n",
       "13  740000  E8ABBA801F3C015C                 2.0                  2.0\n",
       "14  744000  4363747FF224D544                 1.0                  1.0\n",
       "15  745000  C90D7253BB41A041                10.0                 10.0\n",
       "16  746000  436B747F53EA370A                 1.0                  1.0\n",
       "17  747000  EFBD516BFFA9E3E9                 2.0                  2.0\n",
       "18  748000  E14C5A0F8D8C6FCC                 5.0                  5.0\n",
       "19  748000  C3EB747740052325                 5.0                  5.0\n",
       "20  754000  6FB5516B20B104F1                 3.0                  3.0\n",
       "21  755000  490D725B88E56DE5                10.0                 10.0\n",
       "22  756000  6FB5D1635CBF40FF                 1.0                  1.0\n",
       "23  757000  642F9A8039E51EE5                 3.0                  3.0\n",
       "24  757000  64A79A88F278D778                 1.0                  1.0\n",
       "25  759000  61C4DA0FFA35DC75                 5.0                  5.0\n",
       "26  760000  C3E3747718A3FBC4                15.0                 15.0\n",
       "27  760000  6F3DD163510F354F                 4.0                  4.0\n",
       "28  768500  ECA71A88142AF92B                 1.0                  1.0\n",
       "29  770000  C3637477EF36D256                 6.0                  6.0\n",
       "30  770000  EF35516B374E1B8E                 6.0                  6.0\n",
       "31  770000  436B747707D5EAF6                 2.0                  2.0\n",
       "32  770000  EFB5516B13DFF7E0                 6.0                  6.0\n",
       "33  770000  43EB7477305D137D                 6.0                  6.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_book_ask"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Price</th>\n",
       "      <th>QuantityDifference</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>709500</td>\n",
       "      <td>210.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>712000</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>715500</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>717500</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>719500</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>720000</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>725000</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>731500</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>736000</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>738000</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>740000</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>744000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>745000</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>746000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>747000</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>748000</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>754000</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>755000</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>756000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>757000</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>759000</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>760000</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>768500</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>770000</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Price  QuantityDifference\n",
       "0   709500               210.0\n",
       "1   712000                 4.0\n",
       "2   715500                 2.0\n",
       "3   717500                 1.0\n",
       "4   719500                 1.0\n",
       "5   720000                 2.0\n",
       "6   725000                 5.0\n",
       "7   731500                 1.0\n",
       "8   736000                 2.0\n",
       "9   738000                 4.0\n",
       "10  740000                 2.0\n",
       "11  744000                 1.0\n",
       "12  745000                10.0\n",
       "13  746000                 1.0\n",
       "14  747000                 2.0\n",
       "15  748000                10.0\n",
       "16  754000                 3.0\n",
       "17  755000                10.0\n",
       "18  756000                 1.0\n",
       "19  757000                 4.0\n",
       "20  759000                 5.0\n",
       "21  760000                15.0\n",
       "22  768500                 1.0\n",
       "23  770000                26.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_book_ask_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Price</th>\n",
       "      <th>QuantityDifference</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>709500</td>\n",
       "      <td>210.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>707000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>702000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>701000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>700500</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>699000</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>697500</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>695500</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>690500</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>690000</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>685000</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>683500</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>682000</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>673000</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>663500</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>653500</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Price  QuantityDifference\n",
       "0   709500               210.0\n",
       "1   707000                 1.0\n",
       "2   702000                 1.0\n",
       "3   701000                 1.0\n",
       "4   700500                 2.0\n",
       "5   699000                 2.0\n",
       "6   697500                 1.0\n",
       "7   695500                 1.0\n",
       "8   690500                 1.0\n",
       "9   690000                77.0\n",
       "10  685000                 2.0\n",
       "11  683500                 4.0\n",
       "12  682000               100.0\n",
       "13  673000                 8.0\n",
       "14  663500                 3.0\n",
       "15  653500                 3.0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_book_bid_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "ed225720166559e7176d3793db16a2fd8d295f725007103b21ac3099d2a89ee8"
  },
  "kernelspec": {
   "display_name": "Python 3.9.7 ('base')",
   "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.9.7"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}