final_2nd (4th copy).ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script>requirejs.config({paths: { 'plotly': ['https://cdn.plot.ly/plotly-latest.min']},});if(!window.Plotly) {{require(['plotly'],function(plotly) {window.Plotly=plotly;});}}</script>"
      ],
      "text/vnd.plotly.v1+html": [
       "<script>requirejs.config({paths: { 'plotly': ['https://cdn.plot.ly/plotly-latest.min']},});if(!window.Plotly) {{require(['plotly'],function(plotly) {window.Plotly=plotly;});}}</script>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/dist-packages/sklearn/cross_validation.py:41: DeprecationWarning:\n",
      "\n",
      "This module was deprecated in version 0.18 in favor of the model_selection module into which all the refactored classes and functions are moved. Also note that the interface of the new CV iterators are different from that of this module. This module will be removed in 0.20.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "import numpy as np\n",
    "import mysql.connector\n",
    "import pandas as pd\n",
    "import getpass\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import plotly\n",
    "plotly.tools.set_credentials_file(username='mainakchain', api_key='B9m4DVrYYmsoW3jDSiok')\n",
    "import plotly.plotly as py\n",
    "import plotly.graph_objs as go\n",
    "from plotly import __version__\n",
    "from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot\n",
    "import cufflinks as cf\n",
    "init_notebook_mode(connected=True)\n",
    "import plotly.figure_factory as ff\n",
    "from scipy import stats\n",
    "from sklearn.utils import shuffle\n",
    "from sklearn.neural_network import MLPRegressor\n",
    "from sklearn.neural_network import MLPClassifier\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.model_selection import train_test_split\n",
    "from datetime import timedelta\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "import datetime\n",
    "import holidays\n",
    "from sklearn.model_selection import cross_val_score\n",
    "from sklearn.metrics import median_absolute_error\n",
    "from sklearn.model_selection import GridSearchCV\n",
    "from sklearn.cross_validation import StratifiedKFold\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.preprocessing import MinMaxScaler\n",
    "from sklearn.svm import LinearSVR\n",
    "from sklearn.svm import SVR\n",
    "import operator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [],
   "source": [
    "time = pd.read_csv('time_between_two_tolls_new.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/dist-packages/ipykernel_launcher.py:2: SettingWithCopyWarning:\n",
      "\n",
      "\n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "\n",
      "/usr/local/lib/python2.7/dist-packages/ipykernel_launcher.py:3: SettingWithCopyWarning:\n",
      "\n",
      "\n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "\n"
     ]
    }
   ],
   "source": [
    "toll_freq = dict(time['toll_both'].value_counts())\n",
    "time['toll_freq'] = time['toll_both']\n",
    "time['toll_freq'] = time['toll_freq'].apply(lambda x: toll_freq[x] )\n",
    "m = int(0.3 * time['toll_freq'].median())\n",
    "time = time[(time['toll_freq'] > m)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3582, 14)"
      ]
     },
     "execution_count": 247,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Gurapalli                                   183\n",
       "Madapam                                     181\n",
       "Manguli                                     180\n",
       "Gudipada (Old Gangapada)                    179\n",
       "Chilakapalem                                179\n",
       "Sergrarh                                    179\n",
       "Nathavalasa/ Vizianagaram                   178\n",
       "Laxmipuram                                  178\n",
       "Panikholi                                   178\n",
       "Krishnavaram                                175\n",
       "Budanam                                     175\n",
       "Vempadu                                     174\n",
       "Kalaparru                                   174\n",
       "Kaza                                        174\n",
       "Pottipadu                                   171\n",
       "Sunambatti (Musunur)                        171\n",
       "Bolapalli                                   171\n",
       "Tangutur                                    167\n",
       "Bellupada                                   156\n",
       "Gaddurur                                    131\n",
       "Mulbagal                                    128\n",
       "Krishnagiri                                  40\n",
       "Hoskote                                      27\n",
       "Eethakota                                    12\n",
       "Vaniyambadi                                  12\n",
       "Unguturu                                     11\n",
       "Main Toll (Gosthani Gate) (Sheela Nagar)      5\n",
       "Aganampudi                                    5\n",
       "Palempalli                                    4\n",
       "Hasanpur (Sainkul)                            3\n",
       "Pallikonda                                    3\n",
       "Attibele (BETL)                               1\n",
       "Name: toll_both, dtype: int64"
      ]
     },
     "execution_count": 227,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time.toll_both.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "metadata": {},
   "outputs": [],
   "source": [
    "toll_time = pd.read_csv('toll_time.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/dist-packages/ipykernel_launcher.py:1: UserWarning:\n",
      "\n",
      "Boolean Series key will be reindexed to match DataFrame index.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "toll_time = toll_time[toll_time['origin'] == 'Jamshedpur'][toll_time['destination'] == 'HOSUR']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Manguli                                     47\n",
       "Vempadu                                     47\n",
       "Gurapalli                                   46\n",
       "Panikholi                                   46\n",
       "Krishnavaram                                45\n",
       "Gudipada (Old Gangapada)                    45\n",
       "Kalaparru                                   45\n",
       "Madapam                                     45\n",
       "Pottipadu                                   44\n",
       "Sunambatti (Musunur)                        44\n",
       "Laxmipuram                                  44\n",
       "Chilakapalem                                44\n",
       "Nathavalasa/ Vizianagaram                   44\n",
       "Bolapalli                                   44\n",
       "Budanam                                     43\n",
       "Venkatachallam &#160;(Old Nellore)          43\n",
       "Sergrarh                                    43\n",
       "Kaza                                        43\n",
       "Tangutur                                    40\n",
       "Mulbagal                                    35\n",
       "Gaddurur                                    35\n",
       "Bellupada                                   34\n",
       "Hoskote                                     18\n",
       "Attibele (BETL)                              7\n",
       "Krishnagiri                                  6\n",
       "Palempalli                                   3\n",
       "Vaniyambadi                                  2\n",
       "Aganampudi                                   2\n",
       "Main Toll (Gosthani Gate) (Sheela Nagar)     2\n",
       "Eethakota                                    1\n",
       "Hasanpur (Sainkul)                           1\n",
       "Unguturu                                     1\n",
       "Pallikonda                                   1\n",
       "Name: toll_both, dtype: int64"
      ]
     },
     "execution_count": 224,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toll_time.toll_both.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "33"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toll_time.toll_both.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# toll_time = time\n",
    "# toll_time.drop(['Unnamed: 0'], axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "      <th>toll_both</th>\n",
       "      <th>vehicle_no</th>\n",
       "      <th>loading_out_time</th>\n",
       "      <th>odometer_km</th>\n",
       "      <th>ist_timestamp</th>\n",
       "      <th>distance_from_toll</th>\n",
       "      <th>loading_in_time</th>\n",
       "      <th>start_date</th>\n",
       "      <th>eta</th>\n",
       "      <th>unloading_in_time</th>\n",
       "      <th>slug</th>\n",
       "      <th>delivery_status</th>\n",
       "      <th>distance_travelled</th>\n",
       "      <th>late_by_hrs</th>\n",
       "      <th>time_taken</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3904</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Aganampudi</td>\n",
       "      <td>JH01AE6027</td>\n",
       "      <td>2017-11-06 00:16:58</td>\n",
       "      <td>7915.73</td>\n",
       "      <td>2017-11-10 22:14:15</td>\n",
       "      <td>0.898483</td>\n",
       "      <td>2017-11-04 20:05:00</td>\n",
       "      <td>2017-11-04 00:00:00</td>\n",
       "      <td>2017-11-13 00:00:00</td>\n",
       "      <td>2017-11-13 01:39:39</td>\n",
       "      <td>ETJ0E000</td>\n",
       "      <td>early</td>\n",
       "      <td>624.41</td>\n",
       "      <td>-18.422500</td>\n",
       "      <td>117.954722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3905</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Aganampudi</td>\n",
       "      <td>TN36Y9906</td>\n",
       "      <td>2017-11-04 23:23:59</td>\n",
       "      <td>6810.38</td>\n",
       "      <td>2017-11-07 01:37:32</td>\n",
       "      <td>0.805438</td>\n",
       "      <td>2017-11-04 05:45:21</td>\n",
       "      <td>2017-11-04 00:00:00</td>\n",
       "      <td>2017-11-16 00:00:00</td>\n",
       "      <td>2017-11-10 03:37:24</td>\n",
       "      <td>ETOYD000</td>\n",
       "      <td>early</td>\n",
       "      <td>627.52</td>\n",
       "      <td>-146.132500</td>\n",
       "      <td>50.225833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3906</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Attibele (BETL)</td>\n",
       "      <td>BR02GA3943</td>\n",
       "      <td>2017-12-01 00:18:48</td>\n",
       "      <td>9133.44</td>\n",
       "      <td>2017-12-04 21:51:11</td>\n",
       "      <td>0.977303</td>\n",
       "      <td>2017-11-30 05:35:54</td>\n",
       "      <td>2017-11-30 00:00:00</td>\n",
       "      <td>2017-12-08 00:00:00</td>\n",
       "      <td>2017-12-05 18:44:15</td>\n",
       "      <td>ETF9G000</td>\n",
       "      <td>early</td>\n",
       "      <td>1610.77</td>\n",
       "      <td>-58.860833</td>\n",
       "      <td>93.539722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3907</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Attibele (BETL)</td>\n",
       "      <td>OD07M1151</td>\n",
       "      <td>2017-11-30 04:58:30</td>\n",
       "      <td>9912.20</td>\n",
       "      <td>2017-12-03 23:40:14</td>\n",
       "      <td>0.672070</td>\n",
       "      <td>2017-11-29 05:00:12</td>\n",
       "      <td>2017-11-29 00:00:00</td>\n",
       "      <td>2017-12-07 00:00:00</td>\n",
       "      <td>2017-12-04 00:00:14</td>\n",
       "      <td>ET46G000</td>\n",
       "      <td>early</td>\n",
       "      <td>1641.28</td>\n",
       "      <td>-76.999444</td>\n",
       "      <td>90.695556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3908</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Attibele (BETL)</td>\n",
       "      <td>OR04M5329</td>\n",
       "      <td>2017-11-27 03:32:37</td>\n",
       "      <td>7302.32</td>\n",
       "      <td>2017-11-30 23:16:39</td>\n",
       "      <td>0.902463</td>\n",
       "      <td>2017-11-25 05:57:26</td>\n",
       "      <td>2017-11-25 00:00:00</td>\n",
       "      <td>2017-12-07 00:00:00</td>\n",
       "      <td>2017-12-04 00:00:53</td>\n",
       "      <td>ETXSF000</td>\n",
       "      <td>early</td>\n",
       "      <td>1620.49</td>\n",
       "      <td>-77.942500</td>\n",
       "      <td>91.733889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3909</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Attibele (BETL)</td>\n",
       "      <td>OR07V0091</td>\n",
       "      <td>2017-11-27 03:16:12</td>\n",
       "      <td>11154.36</td>\n",
       "      <td>2017-11-30 22:57:04</td>\n",
       "      <td>0.861116</td>\n",
       "      <td>2017-11-25 06:03:58</td>\n",
       "      <td>2017-11-25 00:00:00</td>\n",
       "      <td>2017-12-07 00:00:00</td>\n",
       "      <td>2017-12-04 00:14:03</td>\n",
       "      <td>ETZSF000</td>\n",
       "      <td>early</td>\n",
       "      <td>1611.30</td>\n",
       "      <td>-77.831944</td>\n",
       "      <td>91.681111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3910</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Attibele (BETL)</td>\n",
       "      <td>OR07W2988</td>\n",
       "      <td>2017-11-26 22:59:12</td>\n",
       "      <td>5659.67</td>\n",
       "      <td>2017-12-02 00:41:59</td>\n",
       "      <td>0.957378</td>\n",
       "      <td>2017-11-25 14:02:01</td>\n",
       "      <td>2017-11-25 00:00:00</td>\n",
       "      <td>2017-12-04 00:00:00</td>\n",
       "      <td>2017-12-04 08:19:42</td>\n",
       "      <td>ETHUF000</td>\n",
       "      <td>on-time</td>\n",
       "      <td>1615.10</td>\n",
       "      <td>-5.705278</td>\n",
       "      <td>121.713056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3911</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Attibele (BETL)</td>\n",
       "      <td>OR11J1747</td>\n",
       "      <td>2017-11-24 23:00:25</td>\n",
       "      <td>5947.85</td>\n",
       "      <td>2017-12-02 22:12:30</td>\n",
       "      <td>0.896532</td>\n",
       "      <td>2017-11-24 06:20:40</td>\n",
       "      <td>2017-11-24 00:00:00</td>\n",
       "      <td>2017-12-02 00:00:00</td>\n",
       "      <td>2017-12-03 00:00:42</td>\n",
       "      <td>ETDPF000</td>\n",
       "      <td>late</td>\n",
       "      <td>1625.30</td>\n",
       "      <td>17.667222</td>\n",
       "      <td>191.201389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3912</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Attibele (BETL)</td>\n",
       "      <td>WB23B7298</td>\n",
       "      <td>2017-11-30 14:58:09</td>\n",
       "      <td>6820.49</td>\n",
       "      <td>2017-12-05 01:34:25</td>\n",
       "      <td>0.931364</td>\n",
       "      <td>2017-11-28 11:02:52</td>\n",
       "      <td>2017-11-28 00:00:00</td>\n",
       "      <td>2017-12-06 00:00:00</td>\n",
       "      <td>2017-12-05 12:52:17</td>\n",
       "      <td>ETU3G000</td>\n",
       "      <td>early</td>\n",
       "      <td>1509.52</td>\n",
       "      <td>-22.176389</td>\n",
       "      <td>106.604444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3913</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>AP04TX2171</td>\n",
       "      <td>2017-11-22 19:30:49</td>\n",
       "      <td>8130.89</td>\n",
       "      <td>2017-11-24 03:29:09</td>\n",
       "      <td>0.250613</td>\n",
       "      <td>2017-11-22 14:50:16</td>\n",
       "      <td>2017-11-22 00:00:00</td>\n",
       "      <td>2017-12-04 00:00:00</td>\n",
       "      <td>2017-11-28 05:08:28</td>\n",
       "      <td>ETAHF000</td>\n",
       "      <td>early</td>\n",
       "      <td>376.19</td>\n",
       "      <td>-153.696667</td>\n",
       "      <td>31.972222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3914</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>AP05TF2187</td>\n",
       "      <td>2017-11-02 23:14:24</td>\n",
       "      <td>10629.83</td>\n",
       "      <td>2017-11-03 23:10:37</td>\n",
       "      <td>0.019795</td>\n",
       "      <td>2017-11-02 03:55:34</td>\n",
       "      <td>2017-11-02 00:00:00</td>\n",
       "      <td>2017-11-14 00:00:00</td>\n",
       "      <td>2017-11-07 14:22:19</td>\n",
       "      <td>ETOSD000</td>\n",
       "      <td>early</td>\n",
       "      <td>375.88</td>\n",
       "      <td>-157.554167</td>\n",
       "      <td>23.936944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3915</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>AP07TU2566</td>\n",
       "      <td>2017-11-19 14:40:40</td>\n",
       "      <td>5239.46</td>\n",
       "      <td>2017-11-24 03:34:53</td>\n",
       "      <td>0.586977</td>\n",
       "      <td>2017-11-19 03:36:54</td>\n",
       "      <td>2017-11-19 00:00:00</td>\n",
       "      <td>2017-11-28 00:00:00</td>\n",
       "      <td>2017-11-28 05:11:51</td>\n",
       "      <td>ETD6F000</td>\n",
       "      <td>on-time</td>\n",
       "      <td>374.88</td>\n",
       "      <td>1.582500</td>\n",
       "      <td>108.903611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3916</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>BR02GA3943</td>\n",
       "      <td>2017-12-01 00:18:48</td>\n",
       "      <td>7898.50</td>\n",
       "      <td>2017-12-03 05:35:54</td>\n",
       "      <td>0.651159</td>\n",
       "      <td>2017-11-30 05:35:54</td>\n",
       "      <td>2017-11-30 00:00:00</td>\n",
       "      <td>2017-12-08 00:00:00</td>\n",
       "      <td>2017-12-05 18:44:15</td>\n",
       "      <td>ETF9G000</td>\n",
       "      <td>early</td>\n",
       "      <td>375.83</td>\n",
       "      <td>-58.860833</td>\n",
       "      <td>53.285000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3917</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>CG04DK4870</td>\n",
       "      <td>2017-11-16 04:16:28</td>\n",
       "      <td>8004.95</td>\n",
       "      <td>2017-11-20 04:23:23</td>\n",
       "      <td>0.969638</td>\n",
       "      <td>2017-11-15 04:41:42</td>\n",
       "      <td>2017-11-15 00:00:00</td>\n",
       "      <td>2017-11-24 00:00:00</td>\n",
       "      <td>2017-11-22 06:14:30</td>\n",
       "      <td>ETNUE000</td>\n",
       "      <td>early</td>\n",
       "      <td>374.49</td>\n",
       "      <td>-46.453333</td>\n",
       "      <td>96.115278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3918</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>CG04FB5095</td>\n",
       "      <td>2017-11-29 04:48:49</td>\n",
       "      <td>5580.21</td>\n",
       "      <td>2017-12-02 07:35:20</td>\n",
       "      <td>0.639690</td>\n",
       "      <td>2017-11-28 04:23:30</td>\n",
       "      <td>2017-11-28 00:00:00</td>\n",
       "      <td>2017-12-10 00:00:00</td>\n",
       "      <td>2017-12-05 19:24:21</td>\n",
       "      <td>ETV2G000</td>\n",
       "      <td>early</td>\n",
       "      <td>375.67</td>\n",
       "      <td>-104.985833</td>\n",
       "      <td>74.775278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3919</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>CG08L3445</td>\n",
       "      <td>2017-11-25 04:00:27</td>\n",
       "      <td>6095.94</td>\n",
       "      <td>2017-11-26 16:31:20</td>\n",
       "      <td>0.309588</td>\n",
       "      <td>2017-11-23 20:14:09</td>\n",
       "      <td>2017-11-23 00:00:00</td>\n",
       "      <td>2017-12-02 00:00:00</td>\n",
       "      <td>2017-11-29 00:04:35</td>\n",
       "      <td>ETHOF000</td>\n",
       "      <td>early</td>\n",
       "      <td>375.35</td>\n",
       "      <td>-92.159444</td>\n",
       "      <td>36.514722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3920</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>JH01AE6027</td>\n",
       "      <td>2017-11-06 00:16:58</td>\n",
       "      <td>7665.39</td>\n",
       "      <td>2017-11-10 06:52:59</td>\n",
       "      <td>0.865285</td>\n",
       "      <td>2017-11-04 20:05:00</td>\n",
       "      <td>2017-11-04 00:00:00</td>\n",
       "      <td>2017-11-13 00:00:00</td>\n",
       "      <td>2017-11-13 01:39:39</td>\n",
       "      <td>ETJ0E000</td>\n",
       "      <td>early</td>\n",
       "      <td>374.07</td>\n",
       "      <td>-18.422500</td>\n",
       "      <td>102.600278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3921</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>JH01AE6027</td>\n",
       "      <td>2017-11-29 01:25:09</td>\n",
       "      <td>4964.13</td>\n",
       "      <td>2017-12-01 06:22:44</td>\n",
       "      <td>0.492805</td>\n",
       "      <td>2017-11-28 04:45:02</td>\n",
       "      <td>2017-11-28 00:00:00</td>\n",
       "      <td>2017-12-10 00:00:00</td>\n",
       "      <td>2017-12-04 00:07:24</td>\n",
       "      <td>ET93G000</td>\n",
       "      <td>early</td>\n",
       "      <td>309.55</td>\n",
       "      <td>-148.627222</td>\n",
       "      <td>52.959722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3922</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>JH02M1289</td>\n",
       "      <td>2017-11-29 23:09:26</td>\n",
       "      <td>7439.05</td>\n",
       "      <td>2017-12-02 21:19:22</td>\n",
       "      <td>0.862849</td>\n",
       "      <td>2017-11-28 12:44:22</td>\n",
       "      <td>2017-11-28 00:00:00</td>\n",
       "      <td>2017-12-04 00:00:00</td>\n",
       "      <td>2017-12-06 02:38:16</td>\n",
       "      <td>ET04G000</td>\n",
       "      <td>late</td>\n",
       "      <td>374.88</td>\n",
       "      <td>37.898333</td>\n",
       "      <td>70.165556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3923</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>JH05AE4540</td>\n",
       "      <td>2017-11-25 22:40:42</td>\n",
       "      <td>8857.59</td>\n",
       "      <td>2017-11-28 06:06:16</td>\n",
       "      <td>0.706134</td>\n",
       "      <td>2017-11-24 06:15:14</td>\n",
       "      <td>2017-11-24 00:00:00</td>\n",
       "      <td>2017-12-03 00:00:00</td>\n",
       "      <td>2017-12-01 00:11:55</td>\n",
       "      <td>ET9PF000</td>\n",
       "      <td>early</td>\n",
       "      <td>340.90</td>\n",
       "      <td>-54.055278</td>\n",
       "      <td>55.426111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3924</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>JH05BQ1509</td>\n",
       "      <td>2017-11-14 22:58:28</td>\n",
       "      <td>5354.79</td>\n",
       "      <td>2017-11-18 01:36:53</td>\n",
       "      <td>0.420274</td>\n",
       "      <td>2017-11-12 16:55:44</td>\n",
       "      <td>2017-11-12 00:00:00</td>\n",
       "      <td>2017-11-21 00:00:00</td>\n",
       "      <td>2017-11-20 03:36:34</td>\n",
       "      <td>ET9NE000</td>\n",
       "      <td>early</td>\n",
       "      <td>317.47</td>\n",
       "      <td>-37.319444</td>\n",
       "      <td>74.640278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3925</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>JH05V5242</td>\n",
       "      <td>2017-11-03 02:33:06</td>\n",
       "      <td>4399.99</td>\n",
       "      <td>2017-11-04 05:45:54</td>\n",
       "      <td>0.336488</td>\n",
       "      <td>2017-11-02 05:29:47</td>\n",
       "      <td>2017-11-02 00:00:00</td>\n",
       "      <td>2017-11-14 00:00:00</td>\n",
       "      <td>2017-11-06 19:16:19</td>\n",
       "      <td>ETUSD000</td>\n",
       "      <td>early</td>\n",
       "      <td>374.92</td>\n",
       "      <td>-178.224444</td>\n",
       "      <td>27.213333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3926</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>JH10BR9134</td>\n",
       "      <td>2017-11-16 02:42:03</td>\n",
       "      <td>3576.00</td>\n",
       "      <td>2017-11-17 07:35:59</td>\n",
       "      <td>0.579414</td>\n",
       "      <td>2017-11-12 04:19:43</td>\n",
       "      <td>2017-11-12 00:00:00</td>\n",
       "      <td>2017-11-21 00:00:00</td>\n",
       "      <td>2017-11-19 17:35:02</td>\n",
       "      <td>ETCME000</td>\n",
       "      <td>early</td>\n",
       "      <td>281.54</td>\n",
       "      <td>-34.744722</td>\n",
       "      <td>28.898889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3927</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>NL01N5129</td>\n",
       "      <td>2017-11-08 01:40:00</td>\n",
       "      <td>2292.00</td>\n",
       "      <td>2017-11-11 02:40:22</td>\n",
       "      <td>0.726055</td>\n",
       "      <td>2017-11-07 11:34:36</td>\n",
       "      <td>2017-11-06 00:00:00</td>\n",
       "      <td>2017-11-18 00:00:00</td>\n",
       "      <td>2017-11-14 00:13:18</td>\n",
       "      <td>ETR2E000</td>\n",
       "      <td>early</td>\n",
       "      <td>374.83</td>\n",
       "      <td>-131.355000</td>\n",
       "      <td>73.006111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3928</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>OD04D0605</td>\n",
       "      <td>2017-11-17 23:55:19</td>\n",
       "      <td>6260.34</td>\n",
       "      <td>2017-11-21 01:06:56</td>\n",
       "      <td>0.943187</td>\n",
       "      <td>2017-11-17 03:30:32</td>\n",
       "      <td>2017-11-17 00:00:00</td>\n",
       "      <td>2017-11-24 00:00:00</td>\n",
       "      <td>2017-11-23 19:17:38</td>\n",
       "      <td>ETQ1F000</td>\n",
       "      <td>on-time</td>\n",
       "      <td>375.27</td>\n",
       "      <td>-8.215000</td>\n",
       "      <td>73.193611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3929</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>OD04D5891</td>\n",
       "      <td>2017-11-22 04:08:15</td>\n",
       "      <td>10211.63</td>\n",
       "      <td>2017-11-25 17:19:43</td>\n",
       "      <td>0.404579</td>\n",
       "      <td>2017-11-21 12:35:07</td>\n",
       "      <td>2017-11-21 00:00:00</td>\n",
       "      <td>2017-11-30 00:00:00</td>\n",
       "      <td>2017-11-27 21:19:27</td>\n",
       "      <td>ETVAF000</td>\n",
       "      <td>early</td>\n",
       "      <td>375.65</td>\n",
       "      <td>-63.261111</td>\n",
       "      <td>85.191111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3930</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>OD04F2036</td>\n",
       "      <td>2017-11-17 00:23:36</td>\n",
       "      <td>7918.89</td>\n",
       "      <td>2017-11-19 09:20:30</td>\n",
       "      <td>0.195008</td>\n",
       "      <td>2017-11-15 04:28:47</td>\n",
       "      <td>2017-11-15 00:00:00</td>\n",
       "      <td>2017-11-24 00:00:00</td>\n",
       "      <td>2017-11-22 00:05:58</td>\n",
       "      <td>ETBUE000</td>\n",
       "      <td>early</td>\n",
       "      <td>375.33</td>\n",
       "      <td>-52.380278</td>\n",
       "      <td>56.948333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3931</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>OR01S7415</td>\n",
       "      <td>2017-11-21 23:28:00</td>\n",
       "      <td>6177.34</td>\n",
       "      <td>2017-11-24 13:24:38</td>\n",
       "      <td>0.388891</td>\n",
       "      <td>2017-11-20 15:38:34</td>\n",
       "      <td>2017-11-19 00:00:00</td>\n",
       "      <td>2017-11-27 00:00:00</td>\n",
       "      <td>2017-11-27 03:47:21</td>\n",
       "      <td>ETF6F000</td>\n",
       "      <td>early</td>\n",
       "      <td>377.35</td>\n",
       "      <td>-35.853611</td>\n",
       "      <td>61.943889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3932</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>OR04M5329</td>\n",
       "      <td>2017-11-05 08:50:35</td>\n",
       "      <td>3828.23</td>\n",
       "      <td>2017-11-08 08:46:18</td>\n",
       "      <td>0.772920</td>\n",
       "      <td>2017-11-05 03:29:14</td>\n",
       "      <td>2017-11-05 00:00:00</td>\n",
       "      <td>2017-11-18 00:00:00</td>\n",
       "      <td>2017-11-10 23:00:01</td>\n",
       "      <td>ETC1E000</td>\n",
       "      <td>early</td>\n",
       "      <td>375.60</td>\n",
       "      <td>-172.486944</td>\n",
       "      <td>71.928611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3933</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Bellupada</td>\n",
       "      <td>OR04M5329</td>\n",
       "      <td>2017-11-27 03:32:37</td>\n",
       "      <td>6057.61</td>\n",
       "      <td>2017-11-28 09:39:14</td>\n",
       "      <td>0.305959</td>\n",
       "      <td>2017-11-25 05:57:26</td>\n",
       "      <td>2017-11-25 00:00:00</td>\n",
       "      <td>2017-12-07 00:00:00</td>\n",
       "      <td>2017-12-04 00:00:53</td>\n",
       "      <td>ETXSF000</td>\n",
       "      <td>early</td>\n",
       "      <td>375.78</td>\n",
       "      <td>-77.942500</td>\n",
       "      <td>30.110278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4864</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>JH10BR9134</td>\n",
       "      <td>2017-11-16 02:42:03</td>\n",
       "      <td>4478.67</td>\n",
       "      <td>2017-11-18 22:02:15</td>\n",
       "      <td>0.625615</td>\n",
       "      <td>2017-11-12 04:19:43</td>\n",
       "      <td>2017-11-12 00:00:00</td>\n",
       "      <td>2017-11-21 00:00:00</td>\n",
       "      <td>2017-11-19 17:35:02</td>\n",
       "      <td>ETCME000</td>\n",
       "      <td>early</td>\n",
       "      <td>1184.21</td>\n",
       "      <td>-34.744722</td>\n",
       "      <td>67.336667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4865</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>NL01N5129</td>\n",
       "      <td>2017-11-08 01:40:00</td>\n",
       "      <td>3149.96</td>\n",
       "      <td>2017-11-13 05:43:40</td>\n",
       "      <td>0.753633</td>\n",
       "      <td>2017-11-07 11:34:36</td>\n",
       "      <td>2017-11-06 00:00:00</td>\n",
       "      <td>2017-11-18 00:00:00</td>\n",
       "      <td>2017-11-14 00:13:18</td>\n",
       "      <td>ETR2E000</td>\n",
       "      <td>early</td>\n",
       "      <td>1232.79</td>\n",
       "      <td>-131.355000</td>\n",
       "      <td>124.061111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4866</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OD04D0605</td>\n",
       "      <td>2017-11-17 23:55:19</td>\n",
       "      <td>7129.29</td>\n",
       "      <td>2017-11-23 06:21:42</td>\n",
       "      <td>0.633657</td>\n",
       "      <td>2017-11-17 03:30:32</td>\n",
       "      <td>2017-11-17 00:00:00</td>\n",
       "      <td>2017-11-24 00:00:00</td>\n",
       "      <td>2017-11-23 19:17:38</td>\n",
       "      <td>ETQ1F000</td>\n",
       "      <td>on-time</td>\n",
       "      <td>1244.22</td>\n",
       "      <td>-8.215000</td>\n",
       "      <td>126.439722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4867</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OD04D5891</td>\n",
       "      <td>2017-11-22 04:08:15</td>\n",
       "      <td>11077.49</td>\n",
       "      <td>2017-11-27 07:48:13</td>\n",
       "      <td>0.540692</td>\n",
       "      <td>2017-11-21 12:35:07</td>\n",
       "      <td>2017-11-21 00:00:00</td>\n",
       "      <td>2017-11-30 00:00:00</td>\n",
       "      <td>2017-11-27 21:19:27</td>\n",
       "      <td>ETVAF000</td>\n",
       "      <td>early</td>\n",
       "      <td>1241.51</td>\n",
       "      <td>-63.261111</td>\n",
       "      <td>123.666111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4868</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OD04F2036</td>\n",
       "      <td>2017-11-17 00:23:36</td>\n",
       "      <td>8786.77</td>\n",
       "      <td>2017-11-21 08:52:11</td>\n",
       "      <td>0.996598</td>\n",
       "      <td>2017-11-15 04:28:47</td>\n",
       "      <td>2017-11-15 00:00:00</td>\n",
       "      <td>2017-11-24 00:00:00</td>\n",
       "      <td>2017-11-22 00:05:58</td>\n",
       "      <td>ETBUE000</td>\n",
       "      <td>early</td>\n",
       "      <td>1243.21</td>\n",
       "      <td>-52.380278</td>\n",
       "      <td>104.476389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4869</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OD04G4791</td>\n",
       "      <td>2017-11-03 10:25:26</td>\n",
       "      <td>5165.14</td>\n",
       "      <td>2017-11-07 07:51:04</td>\n",
       "      <td>0.623074</td>\n",
       "      <td>2017-11-02 15:21:26</td>\n",
       "      <td>2017-11-02 00:00:00</td>\n",
       "      <td>2017-11-11 00:00:00</td>\n",
       "      <td>2017-11-07 19:25:07</td>\n",
       "      <td>ETFTD000</td>\n",
       "      <td>early</td>\n",
       "      <td>1239.02</td>\n",
       "      <td>-91.938611</td>\n",
       "      <td>93.427222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4870</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OD04G4791</td>\n",
       "      <td>2017-11-24 15:01:49</td>\n",
       "      <td>7680.15</td>\n",
       "      <td>2017-11-29 07:21:11</td>\n",
       "      <td>0.890431</td>\n",
       "      <td>2017-11-23 00:00:59</td>\n",
       "      <td>2017-11-23 00:00:00</td>\n",
       "      <td>2017-12-02 00:00:00</td>\n",
       "      <td>2017-11-30 04:29:29</td>\n",
       "      <td>ETBLF000</td>\n",
       "      <td>early</td>\n",
       "      <td>1243.67</td>\n",
       "      <td>-43.525000</td>\n",
       "      <td>112.322778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4871</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OD04H5921</td>\n",
       "      <td>2017-11-24 10:43:11</td>\n",
       "      <td>6967.66</td>\n",
       "      <td>2017-11-29 07:20:12</td>\n",
       "      <td>0.882570</td>\n",
       "      <td>2017-11-22 02:40:50</td>\n",
       "      <td>2017-11-22 00:00:00</td>\n",
       "      <td>2017-11-30 00:00:00</td>\n",
       "      <td>2017-11-29 20:25:17</td>\n",
       "      <td>ET3JF000</td>\n",
       "      <td>on-time</td>\n",
       "      <td>1241.19</td>\n",
       "      <td>-6.259167</td>\n",
       "      <td>116.616944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4872</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OD07M1151</td>\n",
       "      <td>2017-11-30 04:58:30</td>\n",
       "      <td>9529.49</td>\n",
       "      <td>2017-12-02 22:39:01</td>\n",
       "      <td>0.790957</td>\n",
       "      <td>2017-11-29 05:00:12</td>\n",
       "      <td>2017-11-29 00:00:00</td>\n",
       "      <td>2017-12-07 00:00:00</td>\n",
       "      <td>2017-12-04 00:00:14</td>\n",
       "      <td>ET46G000</td>\n",
       "      <td>early</td>\n",
       "      <td>1258.57</td>\n",
       "      <td>-76.999444</td>\n",
       "      <td>65.675278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR01S7415</td>\n",
       "      <td>2017-11-21 23:28:00</td>\n",
       "      <td>7062.03</td>\n",
       "      <td>2017-11-26 08:55:07</td>\n",
       "      <td>0.668996</td>\n",
       "      <td>2017-11-20 15:38:34</td>\n",
       "      <td>2017-11-19 00:00:00</td>\n",
       "      <td>2017-11-27 00:00:00</td>\n",
       "      <td>2017-11-27 03:47:21</td>\n",
       "      <td>ETF6F000</td>\n",
       "      <td>early</td>\n",
       "      <td>1262.04</td>\n",
       "      <td>-35.853611</td>\n",
       "      <td>105.451944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4874</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR04M5329</td>\n",
       "      <td>2017-11-05 08:50:35</td>\n",
       "      <td>4678.48</td>\n",
       "      <td>2017-11-09 22:21:41</td>\n",
       "      <td>0.014158</td>\n",
       "      <td>2017-11-05 03:29:14</td>\n",
       "      <td>2017-11-05 00:00:00</td>\n",
       "      <td>2017-11-18 00:00:00</td>\n",
       "      <td>2017-11-10 23:00:01</td>\n",
       "      <td>ETC1E000</td>\n",
       "      <td>early</td>\n",
       "      <td>1225.85</td>\n",
       "      <td>-172.486944</td>\n",
       "      <td>109.518333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4875</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR04M5329</td>\n",
       "      <td>2017-11-27 03:32:37</td>\n",
       "      <td>6925.91</td>\n",
       "      <td>2017-11-29 23:32:19</td>\n",
       "      <td>0.775342</td>\n",
       "      <td>2017-11-25 05:57:26</td>\n",
       "      <td>2017-11-25 00:00:00</td>\n",
       "      <td>2017-12-07 00:00:00</td>\n",
       "      <td>2017-12-04 00:00:53</td>\n",
       "      <td>ETXSF000</td>\n",
       "      <td>early</td>\n",
       "      <td>1244.08</td>\n",
       "      <td>-77.942500</td>\n",
       "      <td>67.995000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4876</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR04M6922</td>\n",
       "      <td>2017-12-01 04:19:37</td>\n",
       "      <td>10034.88</td>\n",
       "      <td>2017-12-04 04:54:18</td>\n",
       "      <td>0.964172</td>\n",
       "      <td>2017-11-30 13:22:01</td>\n",
       "      <td>2017-11-30 00:00:00</td>\n",
       "      <td>2017-12-12 00:00:00</td>\n",
       "      <td>2017-12-06 00:00:41</td>\n",
       "      <td>ETNAG000</td>\n",
       "      <td>early</td>\n",
       "      <td>1239.92</td>\n",
       "      <td>-157.355556</td>\n",
       "      <td>72.578056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4877</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR05AJ1525</td>\n",
       "      <td>2017-11-29 10:21:45</td>\n",
       "      <td>10038.21</td>\n",
       "      <td>2017-12-05 04:43:36</td>\n",
       "      <td>0.831852</td>\n",
       "      <td>2017-11-29 05:15:06</td>\n",
       "      <td>2017-11-29 00:00:00</td>\n",
       "      <td>2017-12-07 00:00:00</td>\n",
       "      <td>2017-12-05 19:24:28</td>\n",
       "      <td>ETD6G000</td>\n",
       "      <td>early</td>\n",
       "      <td>1235.53</td>\n",
       "      <td>-33.843889</td>\n",
       "      <td>138.364167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4878</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR05AJ4215</td>\n",
       "      <td>2017-11-17 01:37:52</td>\n",
       "      <td>8913.62</td>\n",
       "      <td>2017-11-20 22:00:29</td>\n",
       "      <td>0.273659</td>\n",
       "      <td>2017-11-16 10:35:39</td>\n",
       "      <td>2017-11-16 00:00:00</td>\n",
       "      <td>2017-11-25 00:00:00</td>\n",
       "      <td>2017-11-21 22:38:27</td>\n",
       "      <td>ETMYE000</td>\n",
       "      <td>early</td>\n",
       "      <td>1239.06</td>\n",
       "      <td>-83.953333</td>\n",
       "      <td>92.376944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4879</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR05AJ8915</td>\n",
       "      <td>2017-11-25 02:04:10</td>\n",
       "      <td>6387.49</td>\n",
       "      <td>2017-11-29 12:07:25</td>\n",
       "      <td>0.660231</td>\n",
       "      <td>2017-11-23 13:41:43</td>\n",
       "      <td>2017-11-23 00:00:00</td>\n",
       "      <td>2017-12-05 00:00:00</td>\n",
       "      <td>2017-11-30 05:37:34</td>\n",
       "      <td>ETOMF000</td>\n",
       "      <td>early</td>\n",
       "      <td>1243.76</td>\n",
       "      <td>-128.069167</td>\n",
       "      <td>106.054167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4880</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR05U6821</td>\n",
       "      <td>2017-11-17 03:04:44</td>\n",
       "      <td>7876.92</td>\n",
       "      <td>2017-11-21 12:24:06</td>\n",
       "      <td>0.492098</td>\n",
       "      <td>2017-11-15 19:16:02</td>\n",
       "      <td>2017-11-15 00:00:00</td>\n",
       "      <td>2017-11-19 00:00:00</td>\n",
       "      <td>2017-11-22 01:59:53</td>\n",
       "      <td>ETOWE000</td>\n",
       "      <td>late</td>\n",
       "      <td>1235.68</td>\n",
       "      <td>54.730833</td>\n",
       "      <td>105.322778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4881</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR07J9793</td>\n",
       "      <td>2017-11-16 10:55:02</td>\n",
       "      <td>6166.72</td>\n",
       "      <td>2017-11-20 18:24:37</td>\n",
       "      <td>0.992554</td>\n",
       "      <td>2017-11-12 20:03:47</td>\n",
       "      <td>2017-11-12 00:00:00</td>\n",
       "      <td>2017-11-21 00:00:00</td>\n",
       "      <td>2017-11-21 06:21:58</td>\n",
       "      <td>ETANE000</td>\n",
       "      <td>early</td>\n",
       "      <td>1231.91</td>\n",
       "      <td>-13.696944</td>\n",
       "      <td>103.493056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4882</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR07V0091</td>\n",
       "      <td>2017-11-27 03:16:12</td>\n",
       "      <td>10779.19</td>\n",
       "      <td>2017-11-30 08:13:50</td>\n",
       "      <td>0.543652</td>\n",
       "      <td>2017-11-25 06:03:58</td>\n",
       "      <td>2017-11-25 00:00:00</td>\n",
       "      <td>2017-12-07 00:00:00</td>\n",
       "      <td>2017-12-04 00:14:03</td>\n",
       "      <td>ETZSF000</td>\n",
       "      <td>early</td>\n",
       "      <td>1236.13</td>\n",
       "      <td>-77.831944</td>\n",
       "      <td>76.960556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4883</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR07V2855</td>\n",
       "      <td>2017-11-25 16:07:11</td>\n",
       "      <td>6921.81</td>\n",
       "      <td>2017-12-02 07:26:26</td>\n",
       "      <td>0.609538</td>\n",
       "      <td>2017-11-23 05:08:47</td>\n",
       "      <td>2017-11-23 00:00:00</td>\n",
       "      <td>2017-12-02 00:00:00</td>\n",
       "      <td>2017-12-02 18:17:05</td>\n",
       "      <td>ETDLF000</td>\n",
       "      <td>late</td>\n",
       "      <td>1237.24</td>\n",
       "      <td>13.138333</td>\n",
       "      <td>159.320833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4884</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR07V5231</td>\n",
       "      <td>2017-11-28 00:14:13</td>\n",
       "      <td>6489.94</td>\n",
       "      <td>2017-12-04 15:03:30</td>\n",
       "      <td>0.368364</td>\n",
       "      <td>2017-11-26 04:50:55</td>\n",
       "      <td>2017-11-26 00:00:00</td>\n",
       "      <td>2017-12-08 00:00:00</td>\n",
       "      <td>2017-12-05 22:53:58</td>\n",
       "      <td>ETBXF000</td>\n",
       "      <td>early</td>\n",
       "      <td>261.40</td>\n",
       "      <td>-53.949167</td>\n",
       "      <td>158.821389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4885</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR07W2988</td>\n",
       "      <td>2017-11-08 23:03:05</td>\n",
       "      <td>3569.31</td>\n",
       "      <td>2017-11-12 09:07:28</td>\n",
       "      <td>0.996979</td>\n",
       "      <td>2017-11-05 03:50:03</td>\n",
       "      <td>2017-11-05 00:00:00</td>\n",
       "      <td>2017-11-17 00:00:00</td>\n",
       "      <td>2017-11-12 21:21:39</td>\n",
       "      <td>ETB1E000</td>\n",
       "      <td>early</td>\n",
       "      <td>1128.01</td>\n",
       "      <td>-102.473333</td>\n",
       "      <td>82.073056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4886</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR07W2988</td>\n",
       "      <td>2017-11-26 22:59:12</td>\n",
       "      <td>5284.57</td>\n",
       "      <td>2017-11-30 17:08:40</td>\n",
       "      <td>0.545015</td>\n",
       "      <td>2017-11-25 14:02:01</td>\n",
       "      <td>2017-11-25 00:00:00</td>\n",
       "      <td>2017-12-04 00:00:00</td>\n",
       "      <td>2017-12-04 08:19:42</td>\n",
       "      <td>ETHUF000</td>\n",
       "      <td>on-time</td>\n",
       "      <td>1240.00</td>\n",
       "      <td>-5.705278</td>\n",
       "      <td>90.157778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4887</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR07Y7231</td>\n",
       "      <td>2017-11-23 23:58:17</td>\n",
       "      <td>11809.22</td>\n",
       "      <td>2017-11-27 19:29:10</td>\n",
       "      <td>0.406553</td>\n",
       "      <td>2017-11-18 02:42:47</td>\n",
       "      <td>2017-11-18 00:00:00</td>\n",
       "      <td>2017-11-26 00:00:00</td>\n",
       "      <td>2017-11-28 17:35:14</td>\n",
       "      <td>ETS4F000</td>\n",
       "      <td>late</td>\n",
       "      <td>1250.55</td>\n",
       "      <td>62.874167</td>\n",
       "      <td>91.514722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4888</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR07Y9451</td>\n",
       "      <td>2017-11-25 03:56:05</td>\n",
       "      <td>7896.21</td>\n",
       "      <td>2017-11-29 13:03:23</td>\n",
       "      <td>0.382273</td>\n",
       "      <td>2017-11-23 20:27:29</td>\n",
       "      <td>2017-11-23 00:00:00</td>\n",
       "      <td>2017-12-02 00:00:00</td>\n",
       "      <td>2017-11-30 14:02:15</td>\n",
       "      <td>ETIOF000</td>\n",
       "      <td>early</td>\n",
       "      <td>1233.81</td>\n",
       "      <td>-54.420556</td>\n",
       "      <td>105.121667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4889</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>OR11J1747</td>\n",
       "      <td>2017-11-24 23:00:25</td>\n",
       "      <td>5570.52</td>\n",
       "      <td>2017-12-01 15:05:09</td>\n",
       "      <td>0.419097</td>\n",
       "      <td>2017-11-24 06:20:40</td>\n",
       "      <td>2017-11-24 00:00:00</td>\n",
       "      <td>2017-12-02 00:00:00</td>\n",
       "      <td>2017-12-03 00:00:42</td>\n",
       "      <td>ETDPF000</td>\n",
       "      <td>late</td>\n",
       "      <td>1247.97</td>\n",
       "      <td>17.667222</td>\n",
       "      <td>160.078889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4890</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>TN30BC5699</td>\n",
       "      <td>2017-11-03 14:57:08</td>\n",
       "      <td>5179.15</td>\n",
       "      <td>2017-11-06 06:34:06</td>\n",
       "      <td>0.827115</td>\n",
       "      <td>2017-11-03 03:23:06</td>\n",
       "      <td>2017-11-03 00:00:00</td>\n",
       "      <td>2017-11-15 00:00:00</td>\n",
       "      <td>2017-11-06 18:29:09</td>\n",
       "      <td>ETXUD000</td>\n",
       "      <td>early</td>\n",
       "      <td>1244.73</td>\n",
       "      <td>-200.899167</td>\n",
       "      <td>63.616111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4891</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>TN36Y9906</td>\n",
       "      <td>2017-11-04 23:23:59</td>\n",
       "      <td>7417.07</td>\n",
       "      <td>2017-11-08 21:28:31</td>\n",
       "      <td>0.576954</td>\n",
       "      <td>2017-11-04 05:45:21</td>\n",
       "      <td>2017-11-04 00:00:00</td>\n",
       "      <td>2017-11-16 00:00:00</td>\n",
       "      <td>2017-11-10 03:37:24</td>\n",
       "      <td>ETOYD000</td>\n",
       "      <td>early</td>\n",
       "      <td>1234.21</td>\n",
       "      <td>-146.132500</td>\n",
       "      <td>94.075556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4892</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>WB23B7298</td>\n",
       "      <td>2017-11-30 14:58:09</td>\n",
       "      <td>6476.74</td>\n",
       "      <td>2017-12-04 09:39:59</td>\n",
       "      <td>0.369053</td>\n",
       "      <td>2017-11-28 11:02:52</td>\n",
       "      <td>2017-11-28 00:00:00</td>\n",
       "      <td>2017-12-06 00:00:00</td>\n",
       "      <td>2017-12-05 12:52:17</td>\n",
       "      <td>ETU3G000</td>\n",
       "      <td>early</td>\n",
       "      <td>1165.77</td>\n",
       "      <td>-22.176389</td>\n",
       "      <td>90.697222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4893</th>\n",
       "      <td>Jamshedpur</td>\n",
       "      <td>HOSUR</td>\n",
       "      <td>Venkatachallam &amp;#160;(Old Nellore)</td>\n",
       "      <td>WB23C9411</td>\n",
       "      <td>2017-11-09 23:01:14</td>\n",
       "      <td>2808.90</td>\n",
       "      <td>2017-11-17 20:28:53</td>\n",
       "      <td>0.870039</td>\n",
       "      <td>2017-11-05 14:17:59</td>\n",
       "      <td>2017-11-05 00:00:00</td>\n",
       "      <td>2017-11-13 00:00:00</td>\n",
       "      <td>2017-11-21 04:31:59</td>\n",
       "      <td>ET42E000</td>\n",
       "      <td>late</td>\n",
       "      <td>1242.01</td>\n",
       "      <td>182.233333</td>\n",
       "      <td>189.460833</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>990 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          origin destination                           toll_both  vehicle_no  \\\n",
       "3904  Jamshedpur       HOSUR                          Aganampudi  JH01AE6027   \n",
       "3905  Jamshedpur       HOSUR                          Aganampudi   TN36Y9906   \n",
       "3906  Jamshedpur       HOSUR                     Attibele (BETL)  BR02GA3943   \n",
       "3907  Jamshedpur       HOSUR                     Attibele (BETL)   OD07M1151   \n",
       "3908  Jamshedpur       HOSUR                     Attibele (BETL)   OR04M5329   \n",
       "3909  Jamshedpur       HOSUR                     Attibele (BETL)   OR07V0091   \n",
       "3910  Jamshedpur       HOSUR                     Attibele (BETL)   OR07W2988   \n",
       "3911  Jamshedpur       HOSUR                     Attibele (BETL)   OR11J1747   \n",
       "3912  Jamshedpur       HOSUR                     Attibele (BETL)   WB23B7298   \n",
       "3913  Jamshedpur       HOSUR                           Bellupada  AP04TX2171   \n",
       "3914  Jamshedpur       HOSUR                           Bellupada  AP05TF2187   \n",
       "3915  Jamshedpur       HOSUR                           Bellupada  AP07TU2566   \n",
       "3916  Jamshedpur       HOSUR                           Bellupada  BR02GA3943   \n",
       "3917  Jamshedpur       HOSUR                           Bellupada  CG04DK4870   \n",
       "3918  Jamshedpur       HOSUR                           Bellupada  CG04FB5095   \n",
       "3919  Jamshedpur       HOSUR                           Bellupada   CG08L3445   \n",
       "3920  Jamshedpur       HOSUR                           Bellupada  JH01AE6027   \n",
       "3921  Jamshedpur       HOSUR                           Bellupada  JH01AE6027   \n",
       "3922  Jamshedpur       HOSUR                           Bellupada   JH02M1289   \n",
       "3923  Jamshedpur       HOSUR                           Bellupada  JH05AE4540   \n",
       "3924  Jamshedpur       HOSUR                           Bellupada  JH05BQ1509   \n",
       "3925  Jamshedpur       HOSUR                           Bellupada   JH05V5242   \n",
       "3926  Jamshedpur       HOSUR                           Bellupada  JH10BR9134   \n",
       "3927  Jamshedpur       HOSUR                           Bellupada   NL01N5129   \n",
       "3928  Jamshedpur       HOSUR                           Bellupada   OD04D0605   \n",
       "3929  Jamshedpur       HOSUR                           Bellupada   OD04D5891   \n",
       "3930  Jamshedpur       HOSUR                           Bellupada   OD04F2036   \n",
       "3931  Jamshedpur       HOSUR                           Bellupada   OR01S7415   \n",
       "3932  Jamshedpur       HOSUR                           Bellupada   OR04M5329   \n",
       "3933  Jamshedpur       HOSUR                           Bellupada   OR04M5329   \n",
       "...          ...         ...                                 ...         ...   \n",
       "4864  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)  JH10BR9134   \n",
       "4865  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   NL01N5129   \n",
       "4866  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OD04D0605   \n",
       "4867  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OD04D5891   \n",
       "4868  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OD04F2036   \n",
       "4869  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OD04G4791   \n",
       "4870  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OD04G4791   \n",
       "4871  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OD04H5921   \n",
       "4872  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OD07M1151   \n",
       "4873  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OR01S7415   \n",
       "4874  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OR04M5329   \n",
       "4875  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OR04M5329   \n",
       "4876  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OR04M6922   \n",
       "4877  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)  OR05AJ1525   \n",
       "4878  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)  OR05AJ4215   \n",
       "4879  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)  OR05AJ8915   \n",
       "4880  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OR05U6821   \n",
       "4881  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OR07J9793   \n",
       "4882  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OR07V0091   \n",
       "4883  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OR07V2855   \n",
       "4884  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OR07V5231   \n",
       "4885  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OR07W2988   \n",
       "4886  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OR07W2988   \n",
       "4887  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OR07Y7231   \n",
       "4888  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OR07Y9451   \n",
       "4889  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   OR11J1747   \n",
       "4890  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)  TN30BC5699   \n",
       "4891  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   TN36Y9906   \n",
       "4892  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   WB23B7298   \n",
       "4893  Jamshedpur       HOSUR  Venkatachallam &#160;(Old Nellore)   WB23C9411   \n",
       "\n",
       "         loading_out_time  odometer_km        ist_timestamp  \\\n",
       "3904  2017-11-06 00:16:58      7915.73  2017-11-10 22:14:15   \n",
       "3905  2017-11-04 23:23:59      6810.38  2017-11-07 01:37:32   \n",
       "3906  2017-12-01 00:18:48      9133.44  2017-12-04 21:51:11   \n",
       "3907  2017-11-30 04:58:30      9912.20  2017-12-03 23:40:14   \n",
       "3908  2017-11-27 03:32:37      7302.32  2017-11-30 23:16:39   \n",
       "3909  2017-11-27 03:16:12     11154.36  2017-11-30 22:57:04   \n",
       "3910  2017-11-26 22:59:12      5659.67  2017-12-02 00:41:59   \n",
       "3911  2017-11-24 23:00:25      5947.85  2017-12-02 22:12:30   \n",
       "3912  2017-11-30 14:58:09      6820.49  2017-12-05 01:34:25   \n",
       "3913  2017-11-22 19:30:49      8130.89  2017-11-24 03:29:09   \n",
       "3914  2017-11-02 23:14:24     10629.83  2017-11-03 23:10:37   \n",
       "3915  2017-11-19 14:40:40      5239.46  2017-11-24 03:34:53   \n",
       "3916  2017-12-01 00:18:48      7898.50  2017-12-03 05:35:54   \n",
       "3917  2017-11-16 04:16:28      8004.95  2017-11-20 04:23:23   \n",
       "3918  2017-11-29 04:48:49      5580.21  2017-12-02 07:35:20   \n",
       "3919  2017-11-25 04:00:27      6095.94  2017-11-26 16:31:20   \n",
       "3920  2017-11-06 00:16:58      7665.39  2017-11-10 06:52:59   \n",
       "3921  2017-11-29 01:25:09      4964.13  2017-12-01 06:22:44   \n",
       "3922  2017-11-29 23:09:26      7439.05  2017-12-02 21:19:22   \n",
       "3923  2017-11-25 22:40:42      8857.59  2017-11-28 06:06:16   \n",
       "3924  2017-11-14 22:58:28      5354.79  2017-11-18 01:36:53   \n",
       "3925  2017-11-03 02:33:06      4399.99  2017-11-04 05:45:54   \n",
       "3926  2017-11-16 02:42:03      3576.00  2017-11-17 07:35:59   \n",
       "3927  2017-11-08 01:40:00      2292.00  2017-11-11 02:40:22   \n",
       "3928  2017-11-17 23:55:19      6260.34  2017-11-21 01:06:56   \n",
       "3929  2017-11-22 04:08:15     10211.63  2017-11-25 17:19:43   \n",
       "3930  2017-11-17 00:23:36      7918.89  2017-11-19 09:20:30   \n",
       "3931  2017-11-21 23:28:00      6177.34  2017-11-24 13:24:38   \n",
       "3932  2017-11-05 08:50:35      3828.23  2017-11-08 08:46:18   \n",
       "3933  2017-11-27 03:32:37      6057.61  2017-11-28 09:39:14   \n",
       "...                   ...          ...                  ...   \n",
       "4864  2017-11-16 02:42:03      4478.67  2017-11-18 22:02:15   \n",
       "4865  2017-11-08 01:40:00      3149.96  2017-11-13 05:43:40   \n",
       "4866  2017-11-17 23:55:19      7129.29  2017-11-23 06:21:42   \n",
       "4867  2017-11-22 04:08:15     11077.49  2017-11-27 07:48:13   \n",
       "4868  2017-11-17 00:23:36      8786.77  2017-11-21 08:52:11   \n",
       "4869  2017-11-03 10:25:26      5165.14  2017-11-07 07:51:04   \n",
       "4870  2017-11-24 15:01:49      7680.15  2017-11-29 07:21:11   \n",
       "4871  2017-11-24 10:43:11      6967.66  2017-11-29 07:20:12   \n",
       "4872  2017-11-30 04:58:30      9529.49  2017-12-02 22:39:01   \n",
       "4873  2017-11-21 23:28:00      7062.03  2017-11-26 08:55:07   \n",
       "4874  2017-11-05 08:50:35      4678.48  2017-11-09 22:21:41   \n",
       "4875  2017-11-27 03:32:37      6925.91  2017-11-29 23:32:19   \n",
       "4876  2017-12-01 04:19:37     10034.88  2017-12-04 04:54:18   \n",
       "4877  2017-11-29 10:21:45     10038.21  2017-12-05 04:43:36   \n",
       "4878  2017-11-17 01:37:52      8913.62  2017-11-20 22:00:29   \n",
       "4879  2017-11-25 02:04:10      6387.49  2017-11-29 12:07:25   \n",
       "4880  2017-11-17 03:04:44      7876.92  2017-11-21 12:24:06   \n",
       "4881  2017-11-16 10:55:02      6166.72  2017-11-20 18:24:37   \n",
       "4882  2017-11-27 03:16:12     10779.19  2017-11-30 08:13:50   \n",
       "4883  2017-11-25 16:07:11      6921.81  2017-12-02 07:26:26   \n",
       "4884  2017-11-28 00:14:13      6489.94  2017-12-04 15:03:30   \n",
       "4885  2017-11-08 23:03:05      3569.31  2017-11-12 09:07:28   \n",
       "4886  2017-11-26 22:59:12      5284.57  2017-11-30 17:08:40   \n",
       "4887  2017-11-23 23:58:17     11809.22  2017-11-27 19:29:10   \n",
       "4888  2017-11-25 03:56:05      7896.21  2017-11-29 13:03:23   \n",
       "4889  2017-11-24 23:00:25      5570.52  2017-12-01 15:05:09   \n",
       "4890  2017-11-03 14:57:08      5179.15  2017-11-06 06:34:06   \n",
       "4891  2017-11-04 23:23:59      7417.07  2017-11-08 21:28:31   \n",
       "4892  2017-11-30 14:58:09      6476.74  2017-12-04 09:39:59   \n",
       "4893  2017-11-09 23:01:14      2808.90  2017-11-17 20:28:53   \n",
       "\n",
       "      distance_from_toll      loading_in_time           start_date  \\\n",
       "3904            0.898483  2017-11-04 20:05:00  2017-11-04 00:00:00   \n",
       "3905            0.805438  2017-11-04 05:45:21  2017-11-04 00:00:00   \n",
       "3906            0.977303  2017-11-30 05:35:54  2017-11-30 00:00:00   \n",
       "3907            0.672070  2017-11-29 05:00:12  2017-11-29 00:00:00   \n",
       "3908            0.902463  2017-11-25 05:57:26  2017-11-25 00:00:00   \n",
       "3909            0.861116  2017-11-25 06:03:58  2017-11-25 00:00:00   \n",
       "3910            0.957378  2017-11-25 14:02:01  2017-11-25 00:00:00   \n",
       "3911            0.896532  2017-11-24 06:20:40  2017-11-24 00:00:00   \n",
       "3912            0.931364  2017-11-28 11:02:52  2017-11-28 00:00:00   \n",
       "3913            0.250613  2017-11-22 14:50:16  2017-11-22 00:00:00   \n",
       "3914            0.019795  2017-11-02 03:55:34  2017-11-02 00:00:00   \n",
       "3915            0.586977  2017-11-19 03:36:54  2017-11-19 00:00:00   \n",
       "3916            0.651159  2017-11-30 05:35:54  2017-11-30 00:00:00   \n",
       "3917            0.969638  2017-11-15 04:41:42  2017-11-15 00:00:00   \n",
       "3918            0.639690  2017-11-28 04:23:30  2017-11-28 00:00:00   \n",
       "3919            0.309588  2017-11-23 20:14:09  2017-11-23 00:00:00   \n",
       "3920            0.865285  2017-11-04 20:05:00  2017-11-04 00:00:00   \n",
       "3921            0.492805  2017-11-28 04:45:02  2017-11-28 00:00:00   \n",
       "3922            0.862849  2017-11-28 12:44:22  2017-11-28 00:00:00   \n",
       "3923            0.706134  2017-11-24 06:15:14  2017-11-24 00:00:00   \n",
       "3924            0.420274  2017-11-12 16:55:44  2017-11-12 00:00:00   \n",
       "3925            0.336488  2017-11-02 05:29:47  2017-11-02 00:00:00   \n",
       "3926            0.579414  2017-11-12 04:19:43  2017-11-12 00:00:00   \n",
       "3927            0.726055  2017-11-07 11:34:36  2017-11-06 00:00:00   \n",
       "3928            0.943187  2017-11-17 03:30:32  2017-11-17 00:00:00   \n",
       "3929            0.404579  2017-11-21 12:35:07  2017-11-21 00:00:00   \n",
       "3930            0.195008  2017-11-15 04:28:47  2017-11-15 00:00:00   \n",
       "3931            0.388891  2017-11-20 15:38:34  2017-11-19 00:00:00   \n",
       "3932            0.772920  2017-11-05 03:29:14  2017-11-05 00:00:00   \n",
       "3933            0.305959  2017-11-25 05:57:26  2017-11-25 00:00:00   \n",
       "...                  ...                  ...                  ...   \n",
       "4864            0.625615  2017-11-12 04:19:43  2017-11-12 00:00:00   \n",
       "4865            0.753633  2017-11-07 11:34:36  2017-11-06 00:00:00   \n",
       "4866            0.633657  2017-11-17 03:30:32  2017-11-17 00:00:00   \n",
       "4867            0.540692  2017-11-21 12:35:07  2017-11-21 00:00:00   \n",
       "4868            0.996598  2017-11-15 04:28:47  2017-11-15 00:00:00   \n",
       "4869            0.623074  2017-11-02 15:21:26  2017-11-02 00:00:00   \n",
       "4870            0.890431  2017-11-23 00:00:59  2017-11-23 00:00:00   \n",
       "4871            0.882570  2017-11-22 02:40:50  2017-11-22 00:00:00   \n",
       "4872            0.790957  2017-11-29 05:00:12  2017-11-29 00:00:00   \n",
       "4873            0.668996  2017-11-20 15:38:34  2017-11-19 00:00:00   \n",
       "4874            0.014158  2017-11-05 03:29:14  2017-11-05 00:00:00   \n",
       "4875            0.775342  2017-11-25 05:57:26  2017-11-25 00:00:00   \n",
       "4876            0.964172  2017-11-30 13:22:01  2017-11-30 00:00:00   \n",
       "4877            0.831852  2017-11-29 05:15:06  2017-11-29 00:00:00   \n",
       "4878            0.273659  2017-11-16 10:35:39  2017-11-16 00:00:00   \n",
       "4879            0.660231  2017-11-23 13:41:43  2017-11-23 00:00:00   \n",
       "4880            0.492098  2017-11-15 19:16:02  2017-11-15 00:00:00   \n",
       "4881            0.992554  2017-11-12 20:03:47  2017-11-12 00:00:00   \n",
       "4882            0.543652  2017-11-25 06:03:58  2017-11-25 00:00:00   \n",
       "4883            0.609538  2017-11-23 05:08:47  2017-11-23 00:00:00   \n",
       "4884            0.368364  2017-11-26 04:50:55  2017-11-26 00:00:00   \n",
       "4885            0.996979  2017-11-05 03:50:03  2017-11-05 00:00:00   \n",
       "4886            0.545015  2017-11-25 14:02:01  2017-11-25 00:00:00   \n",
       "4887            0.406553  2017-11-18 02:42:47  2017-11-18 00:00:00   \n",
       "4888            0.382273  2017-11-23 20:27:29  2017-11-23 00:00:00   \n",
       "4889            0.419097  2017-11-24 06:20:40  2017-11-24 00:00:00   \n",
       "4890            0.827115  2017-11-03 03:23:06  2017-11-03 00:00:00   \n",
       "4891            0.576954  2017-11-04 05:45:21  2017-11-04 00:00:00   \n",
       "4892            0.369053  2017-11-28 11:02:52  2017-11-28 00:00:00   \n",
       "4893            0.870039  2017-11-05 14:17:59  2017-11-05 00:00:00   \n",
       "\n",
       "                      eta    unloading_in_time      slug delivery_status  \\\n",
       "3904  2017-11-13 00:00:00  2017-11-13 01:39:39  ETJ0E000           early   \n",
       "3905  2017-11-16 00:00:00  2017-11-10 03:37:24  ETOYD000           early   \n",
       "3906  2017-12-08 00:00:00  2017-12-05 18:44:15  ETF9G000           early   \n",
       "3907  2017-12-07 00:00:00  2017-12-04 00:00:14  ET46G000           early   \n",
       "3908  2017-12-07 00:00:00  2017-12-04 00:00:53  ETXSF000           early   \n",
       "3909  2017-12-07 00:00:00  2017-12-04 00:14:03  ETZSF000           early   \n",
       "3910  2017-12-04 00:00:00  2017-12-04 08:19:42  ETHUF000         on-time   \n",
       "3911  2017-12-02 00:00:00  2017-12-03 00:00:42  ETDPF000            late   \n",
       "3912  2017-12-06 00:00:00  2017-12-05 12:52:17  ETU3G000           early   \n",
       "3913  2017-12-04 00:00:00  2017-11-28 05:08:28  ETAHF000           early   \n",
       "3914  2017-11-14 00:00:00  2017-11-07 14:22:19  ETOSD000           early   \n",
       "3915  2017-11-28 00:00:00  2017-11-28 05:11:51  ETD6F000         on-time   \n",
       "3916  2017-12-08 00:00:00  2017-12-05 18:44:15  ETF9G000           early   \n",
       "3917  2017-11-24 00:00:00  2017-11-22 06:14:30  ETNUE000           early   \n",
       "3918  2017-12-10 00:00:00  2017-12-05 19:24:21  ETV2G000           early   \n",
       "3919  2017-12-02 00:00:00  2017-11-29 00:04:35  ETHOF000           early   \n",
       "3920  2017-11-13 00:00:00  2017-11-13 01:39:39  ETJ0E000           early   \n",
       "3921  2017-12-10 00:00:00  2017-12-04 00:07:24  ET93G000           early   \n",
       "3922  2017-12-04 00:00:00  2017-12-06 02:38:16  ET04G000            late   \n",
       "3923  2017-12-03 00:00:00  2017-12-01 00:11:55  ET9PF000           early   \n",
       "3924  2017-11-21 00:00:00  2017-11-20 03:36:34  ET9NE000           early   \n",
       "3925  2017-11-14 00:00:00  2017-11-06 19:16:19  ETUSD000           early   \n",
       "3926  2017-11-21 00:00:00  2017-11-19 17:35:02  ETCME000           early   \n",
       "3927  2017-11-18 00:00:00  2017-11-14 00:13:18  ETR2E000           early   \n",
       "3928  2017-11-24 00:00:00  2017-11-23 19:17:38  ETQ1F000         on-time   \n",
       "3929  2017-11-30 00:00:00  2017-11-27 21:19:27  ETVAF000           early   \n",
       "3930  2017-11-24 00:00:00  2017-11-22 00:05:58  ETBUE000           early   \n",
       "3931  2017-11-27 00:00:00  2017-11-27 03:47:21  ETF6F000           early   \n",
       "3932  2017-11-18 00:00:00  2017-11-10 23:00:01  ETC1E000           early   \n",
       "3933  2017-12-07 00:00:00  2017-12-04 00:00:53  ETXSF000           early   \n",
       "...                   ...                  ...       ...             ...   \n",
       "4864  2017-11-21 00:00:00  2017-11-19 17:35:02  ETCME000           early   \n",
       "4865  2017-11-18 00:00:00  2017-11-14 00:13:18  ETR2E000           early   \n",
       "4866  2017-11-24 00:00:00  2017-11-23 19:17:38  ETQ1F000         on-time   \n",
       "4867  2017-11-30 00:00:00  2017-11-27 21:19:27  ETVAF000           early   \n",
       "4868  2017-11-24 00:00:00  2017-11-22 00:05:58  ETBUE000           early   \n",
       "4869  2017-11-11 00:00:00  2017-11-07 19:25:07  ETFTD000           early   \n",
       "4870  2017-12-02 00:00:00  2017-11-30 04:29:29  ETBLF000           early   \n",
       "4871  2017-11-30 00:00:00  2017-11-29 20:25:17  ET3JF000         on-time   \n",
       "4872  2017-12-07 00:00:00  2017-12-04 00:00:14  ET46G000           early   \n",
       "4873  2017-11-27 00:00:00  2017-11-27 03:47:21  ETF6F000           early   \n",
       "4874  2017-11-18 00:00:00  2017-11-10 23:00:01  ETC1E000           early   \n",
       "4875  2017-12-07 00:00:00  2017-12-04 00:00:53  ETXSF000           early   \n",
       "4876  2017-12-12 00:00:00  2017-12-06 00:00:41  ETNAG000           early   \n",
       "4877  2017-12-07 00:00:00  2017-12-05 19:24:28  ETD6G000           early   \n",
       "4878  2017-11-25 00:00:00  2017-11-21 22:38:27  ETMYE000           early   \n",
       "4879  2017-12-05 00:00:00  2017-11-30 05:37:34  ETOMF000           early   \n",
       "4880  2017-11-19 00:00:00  2017-11-22 01:59:53  ETOWE000            late   \n",
       "4881  2017-11-21 00:00:00  2017-11-21 06:21:58  ETANE000           early   \n",
       "4882  2017-12-07 00:00:00  2017-12-04 00:14:03  ETZSF000           early   \n",
       "4883  2017-12-02 00:00:00  2017-12-02 18:17:05  ETDLF000            late   \n",
       "4884  2017-12-08 00:00:00  2017-12-05 22:53:58  ETBXF000           early   \n",
       "4885  2017-11-17 00:00:00  2017-11-12 21:21:39  ETB1E000           early   \n",
       "4886  2017-12-04 00:00:00  2017-12-04 08:19:42  ETHUF000         on-time   \n",
       "4887  2017-11-26 00:00:00  2017-11-28 17:35:14  ETS4F000            late   \n",
       "4888  2017-12-02 00:00:00  2017-11-30 14:02:15  ETIOF000           early   \n",
       "4889  2017-12-02 00:00:00  2017-12-03 00:00:42  ETDPF000            late   \n",
       "4890  2017-11-15 00:00:00  2017-11-06 18:29:09  ETXUD000           early   \n",
       "4891  2017-11-16 00:00:00  2017-11-10 03:37:24  ETOYD000           early   \n",
       "4892  2017-12-06 00:00:00  2017-12-05 12:52:17  ETU3G000           early   \n",
       "4893  2017-11-13 00:00:00  2017-11-21 04:31:59  ET42E000            late   \n",
       "\n",
       "      distance_travelled  late_by_hrs  time_taken  \n",
       "3904              624.41   -18.422500  117.954722  \n",
       "3905              627.52  -146.132500   50.225833  \n",
       "3906             1610.77   -58.860833   93.539722  \n",
       "3907             1641.28   -76.999444   90.695556  \n",
       "3908             1620.49   -77.942500   91.733889  \n",
       "3909             1611.30   -77.831944   91.681111  \n",
       "3910             1615.10    -5.705278  121.713056  \n",
       "3911             1625.30    17.667222  191.201389  \n",
       "3912             1509.52   -22.176389  106.604444  \n",
       "3913              376.19  -153.696667   31.972222  \n",
       "3914              375.88  -157.554167   23.936944  \n",
       "3915              374.88     1.582500  108.903611  \n",
       "3916              375.83   -58.860833   53.285000  \n",
       "3917              374.49   -46.453333   96.115278  \n",
       "3918              375.67  -104.985833   74.775278  \n",
       "3919              375.35   -92.159444   36.514722  \n",
       "3920              374.07   -18.422500  102.600278  \n",
       "3921              309.55  -148.627222   52.959722  \n",
       "3922              374.88    37.898333   70.165556  \n",
       "3923              340.90   -54.055278   55.426111  \n",
       "3924              317.47   -37.319444   74.640278  \n",
       "3925              374.92  -178.224444   27.213333  \n",
       "3926              281.54   -34.744722   28.898889  \n",
       "3927              374.83  -131.355000   73.006111  \n",
       "3928              375.27    -8.215000   73.193611  \n",
       "3929              375.65   -63.261111   85.191111  \n",
       "3930              375.33   -52.380278   56.948333  \n",
       "3931              377.35   -35.853611   61.943889  \n",
       "3932              375.60  -172.486944   71.928611  \n",
       "3933              375.78   -77.942500   30.110278  \n",
       "...                  ...          ...         ...  \n",
       "4864             1184.21   -34.744722   67.336667  \n",
       "4865             1232.79  -131.355000  124.061111  \n",
       "4866             1244.22    -8.215000  126.439722  \n",
       "4867             1241.51   -63.261111  123.666111  \n",
       "4868             1243.21   -52.380278  104.476389  \n",
       "4869             1239.02   -91.938611   93.427222  \n",
       "4870             1243.67   -43.525000  112.322778  \n",
       "4871             1241.19    -6.259167  116.616944  \n",
       "4872             1258.57   -76.999444   65.675278  \n",
       "4873             1262.04   -35.853611  105.451944  \n",
       "4874             1225.85  -172.486944  109.518333  \n",
       "4875             1244.08   -77.942500   67.995000  \n",
       "4876             1239.92  -157.355556   72.578056  \n",
       "4877             1235.53   -33.843889  138.364167  \n",
       "4878             1239.06   -83.953333   92.376944  \n",
       "4879             1243.76  -128.069167  106.054167  \n",
       "4880             1235.68    54.730833  105.322778  \n",
       "4881             1231.91   -13.696944  103.493056  \n",
       "4882             1236.13   -77.831944   76.960556  \n",
       "4883             1237.24    13.138333  159.320833  \n",
       "4884              261.40   -53.949167  158.821389  \n",
       "4885             1128.01  -102.473333   82.073056  \n",
       "4886             1240.00    -5.705278   90.157778  \n",
       "4887             1250.55    62.874167   91.514722  \n",
       "4888             1233.81   -54.420556  105.121667  \n",
       "4889             1247.97    17.667222  160.078889  \n",
       "4890             1244.73  -200.899167   63.616111  \n",
       "4891             1234.21  -146.132500   94.075556  \n",
       "4892             1165.77   -22.176389   90.697222  \n",
       "4893             1242.01   182.233333  189.460833  \n",
       "\n",
       "[990 rows x 17 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toll_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# toll_time.drop(['Unnamed: 0'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_toll_time = toll_time.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "today_date = datetime.datetime.now()\n",
    "today_date.strftime(\"%Y-%m-%d %H:%M:%S\")\n",
    "today = today_date.strftime(\"%Y-%m-%d %H:%M:%S\")\n",
    "new_toll_time['today'] = datetime.datetime.strptime(today, '%Y-%m-%d %H:%M:%S')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_toll_time['start_date'] = new_toll_time['start_date'].apply(lambda x:datetime.datetime.strptime(x, '%Y-%m-%d %H:%M:%S') )\n",
    "new_toll_time['days_from_now'] = new_toll_time['today'] - new_toll_time['start_date'] \n",
    "new_toll_time['days_from_now'] = new_toll_time['days_from_now'].apply(lambda x: x.days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_toll_time = toll_time.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "today_date = datetime.datetime.now()\n",
    "today_date.strftime(\"%Y-%m-%d %H:%M:%S\")\n",
    "today = today_date.strftime(\"%Y-%m-%d %H:%M:%S\")\n",
    "new_toll_time['today'] = datetime.datetime.strptime(today, '%Y-%m-%d %H:%M:%S')\n",
    "\n",
    "new_toll_time['start_date'] = new_toll_time['start_date'].apply(lambda x:datetime.datetime.strptime(x, '%Y-%m-%d %H:%M:%S') )\n",
    "new_toll_time['days_from_now'] = new_toll_time['today'] - new_toll_time['start_date'] \n",
    "new_toll_time['days_from_now'] = new_toll_time['days_from_now'].apply(lambda x: x.days)\n",
    "\n",
    "new_toll_time['unloading_in_time'] = new_toll_time['unloading_in_time'].apply(lambda x:datetime.datetime.strptime(x, '%Y-%m-%d %H:%M:%S') )\n",
    "new_toll_time['avg_travel_time'] = new_toll_time['start_date'] + (new_toll_time['unloading_in_time'] - new_toll_time['start_date'])/2\n",
    "\n",
    "new_toll_time['month_of_travel'] = new_toll_time['avg_travel_time'].apply(lambda x: x.month)\n",
    "\n",
    "new_toll_time['ist_timestamp'] = new_toll_time['ist_timestamp'].apply(lambda x:datetime.datetime.strptime(x, '%Y-%m-%d %H:%M:%S'))\n",
    "new_toll_time['time_of_travel'] = new_toll_time.ist_timestamp.apply(lambda x: x.hour)\n",
    "\n",
    "new_toll_time['day_of_travel'] = new_toll_time['avg_travel_time'].apply(lambda x: x.weekday())\n",
    "\n",
    "new_toll_time['loading_out_time'] = new_toll_time['loading_out_time'].apply(lambda x:datetime.datetime.strptime(x, '%Y-%m-%d %H:%M:%S') )\n",
    "new_toll_time['loading_out_time'] = new_toll_time['loading_out_time'].apply(lambda x: x.hour)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_toll_time = new_toll_time[['toll_both','vehicle_no','loading_out_time','distance_travelled','time_of_travel','day_of_travel','month_of_travel','days_from_now','time_taken']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# toll_dict = {'Sergrarh':1,'Panikholi':2, 'Manguli':3, 'Gudipada (Old Gangapada)':4, 'Gurapalli':5, 'Bellupada':6, 'Madapam':7, 'Chilakapalem':8, 'Nathavalasa/ Vizianagaram':9, 'Laxmipuram':10, 'Vempadu':11, 'Krishnavaram':12, 'Kalaparru':13, 'Pottipadu':14, 'Kaza':15, 'Bolapalli':16, 'Sunambatti (Musunur)':17, 'Venkatachallam &#160;(Old Nellore)':18, 'Budanam':19, 'Gaddurur':20, 'Mulbagal':21, 'Hoskote':22, 'Attibele (BETL)':23, 'Aganampudi':24}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_toll_num(df):\n",
    "    toll_dict = {}\n",
    "    new_toll_dict = {}\n",
    "    num = 1\n",
    "    for toll in df.toll_both.unique().tolist():\n",
    "        d = df['distance_travelled'][df.toll_both == toll].mean()\n",
    "        toll_dict[toll] = d\n",
    "    \n",
    "    for key, value in sorted(toll_dict.iteritems(), key=lambda (k,v): (v,k)):\n",
    "        new_toll_dict[key] = num\n",
    "        num+=1\n",
    "        \n",
    "    return new_toll_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_toll_dict = find_toll_num(new_toll_time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter the vehicle no.: OR04M532M\n",
      "Enter the start date in %Y-%m-%d %H:%M:%S format: 2017-11-02 00:00:00\n",
      "Enter the loading out time in %Y-%m-%d %H:%M:%S format: 2017-11-03 12:02:43\n",
      "Enter the number of tolls vehicle crossed sequentitially: 3\n",
      "      \n",
      "Toll both: Sergrarh\n",
      "Distance travelled from 1st toll in km: 0\n",
      "IST Timestamp in %Y-%m-%d %H:%M:%S format: 2017-11-04 08:22:51\n",
      "Toll both: Panikholi\n",
      "Distance travelled from 1st toll in km: 95.1\n",
      "IST Timestamp in %Y-%m-%d %H:%M:%S format: 2017-11-04 10:42:12\n",
      "Toll both: Manguli\n",
      "Distance travelled from 1st toll in km: 146.8\n",
      "IST Timestamp in %Y-%m-%d %H:%M:%S format: 2017-11-04 12:10:12\n",
      "Enter the numbers of tolls the vehicle shall take in the journey sequentially: Gudipada (Old Gangapada),Gurapalli,Bellupada,Madapam,Chilakapalem,Nathavalasa/ Vizianagaram,Laxmipuram,Vempadu,Krishnavaram,Kalaparru,Pottipadu,Kaza,Bolapalli,Sunambatti (Musunur),Venkatachallam &#160;(Old Nellore),Budanam,Gaddurur,Mulbagal,Hoskote,Attibele (BETL)\n",
      "Want to enter more vehicles?(y/n)y\n",
      "Enter the vehicle no.: OR04M53MM\n",
      "Enter the start date in %Y-%m-%d %H:%M:%S format: 2017-10-20 00:00:00\n",
      "Enter the loading out time in %Y-%m-%d %H:%M:%S format: 2017-10-21 21:00:42\n",
      "Enter the number of tolls vehicle crossed sequentitially: 5\n",
      "      \n",
      "Toll both: Sergrarh\n",
      "Distance travelled from 1st toll in km: 0\n",
      "IST Timestamp in %Y-%m-%d %H:%M:%S format: 2017-10-22 17:11:43\n",
      "Toll both: Panikholi\n",
      "Distance travelled from 1st toll in km: 94.6\n",
      "IST Timestamp in %Y-%m-%d %H:%M:%S format: 2017-10-22 19:35:13\n",
      "Toll both: Manguli\n",
      "Distance travelled from 1st toll in km: 146.5\n",
      "IST Timestamp in %Y-%m-%d %H:%M:%S format: 2017-10-22 21:10:53\n",
      "Toll both: Gudipada (Old Gangapada)\n",
      "Distance travelled from 1st toll in km: 204.6\n",
      "IST Timestamp in %Y-%m-%d %H:%M:%S format: 2017-10-22 23:20:17\n",
      "Toll both: Gurapalli\n",
      "Distance travelled from 1st toll in km: 293.59\n",
      "IST Timestamp in %Y-%m-%d %H:%M:%S format: 2017-10-23 01:35:49\n",
      "Enter the numbers of tolls the vehicle shall take in the journey sequentially: Bellupada,Madapam,Chilakapalem,Nathavalasa/ Vizianagaram,Laxmipuram,Vempadu,Krishnavaram,Kalaparru,Pottipadu,Kaza,Bolapalli,Sunambatti (Musunur),Venkatachallam &#160;(Old Nellore),Budanam,Gaddurur,Mulbagal,Hoskote,Attibele (BETL)\n",
      "Want to enter more vehicles?(y/n)n\n",
      "bbye\n"
     ]
    }
   ],
   "source": [
    "train_df = new_toll_time.copy()\n",
    "test_df= pd.DataFrame(columns=['toll_both','vehicle_no','loading_out_time', 'distance_travelled', 'time_of_travel',\n",
    "                                         'day_of_travel', 'month_of_travel', 'days_from_now', 'time_taken'])\n",
    "choice = True\n",
    "while(choice):\n",
    "    today_date = datetime.datetime.now()\n",
    "    vehicle = raw_input('Enter the vehicle no.: ')\n",
    "    start = raw_input('Enter the start date in %Y-%m-%d %H:%M:%S format: ')\n",
    "    start_delta = datetime.datetime.strptime(start, '%Y-%m-%d %H:%M:%S')\n",
    "    \n",
    "    loading_out = raw_input('Enter the loading out time in %Y-%m-%d %H:%M:%S format: ')\n",
    "    loading_out_delta = datetime.datetime.strptime(loading_out, '%Y-%m-%d %H:%M:%S')\n",
    "    \n",
    "    a = []\n",
    "    loop = input('Enter the number of tolls vehicle crossed sequentitially: ')\n",
    "    count = 0\n",
    "    print '      '\n",
    "    while (count != loop):\n",
    "        b = []\n",
    "        toll = raw_input('Toll both: ')\n",
    "        distance = input('Distance travelled from 1st toll in km: ')\n",
    "        ist = raw_input('IST Timestamp in %Y-%m-%d %H:%M:%S format: ')\n",
    "        ist_delta = datetime.datetime.strptime(ist, '%Y-%m-%d %H:%M:%S')\n",
    "        b.extend([toll, vehicle, loading_out_delta.hour, distance, ist_delta.hour, ist_delta.weekday(), ist_delta.month,\n",
    "                      (today_date - loading_out_delta).days, (ist_delta - loading_out_delta).total_seconds()/3600])\n",
    "        a.append(b)\n",
    "        count+=1\n",
    "    \n",
    "    add_df = pd.DataFrame(a, columns= ['toll_both','vehicle_no','loading_out_time', 'distance_travelled', 'time_of_travel',\n",
    "                                         'day_of_travel', 'month_of_travel', 'days_from_now', 'time_btn_tolls'])\n",
    "    train_df = train_df.append(add_df)\n",
    "    \n",
    "    test_toll = raw_input('Enter the numbers of tolls the vehicle shall take in the journey sequentially: ')\n",
    "    test_toll_list = test_toll.split(',')\n",
    "    test_toll_list = [str(a) for a in test_toll_list]\n",
    "\n",
    "    a = []\n",
    "\n",
    "    for toll_val in test_toll_list:\n",
    "        b = []\n",
    "        distance = toll_time['distance_travelled'][toll_time.toll_both == toll_val].median()\n",
    "        ist_delta = loading_out_delta + timedelta(hours=toll_time['time_taken'][toll_time.toll_both == toll_val].median())\n",
    "        b.extend([toll_val, vehicle, loading_out_delta.hour, distance, ist_delta.hour, ist_delta.weekday(), ist_delta.month,\n",
    "                      (today_date - loading_out_delta).days, (ist_delta - loading_out_delta).total_seconds()/3600])\n",
    "\n",
    "        a.append(b)\n",
    "    add_test_df = pd.DataFrame(a, columns= ['toll_both','vehicle_no','loading_out_time', 'distance_travelled', 'time_of_travel',\n",
    "                                         'day_of_travel', 'month_of_travel', 'days_from_now','time_btn_tolls'])\n",
    "    \n",
    "    test_df = test_df.append(add_test_df)\n",
    "    \n",
    "    c = raw_input('Want to enter more vehicles?(y/n)')\n",
    "    \n",
    "    if (c == 'y' or c == 'Y'):\n",
    "        continue\n",
    "    elif (c == 'n' or c == 'N'):\n",
    "        choice = False\n",
    "        print 'bbye'\n",
    "    else:\n",
    "        print 'Wrong Choice! Exiting'\n",
    "        choice = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = train_df.reset_index(drop=True)\n",
    "test_df = test_df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df.to_csv('final_train_df_14Dec2017.csv')\n",
    "test_df.to_csv('final_test_df_14Dec2017.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((998, 9), (38, 9))"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.shape, test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "# test_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "# def change_toll_booth_to_dummy(df):\n",
    "#     num = 1\n",
    "#     dict_toll_both = {}\n",
    "#     for toll in df['toll_both'].unique().tolist():\n",
    "#         df['toll_both'] = df['toll_both'].replace({toll: num})\n",
    "#         dict_toll_both[num] = toll\n",
    "#         num += 1\n",
    "#     return dict_toll_both"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dict_toll_both_train_df = change_toll_booth_to_dummy(train_df)\n",
    "train_df.toll_both = train_df.toll_both.replace(new_toll_dict)\n",
    "test_df.toll_both = test_df.toll_both.replace(new_toll_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "def change_vehicle_no_to_dummy(df):\n",
    "    num = 1\n",
    "    dict_vehicle_no = {}\n",
    "    for truck in df['vehicle_no'].unique().tolist():\n",
    "        df['vehicle_no'] = df['vehicle_no'].replace({truck: num})\n",
    "        dict_vehicle_no[num] = truck\n",
    "        num += 1\n",
    "    return dict_vehicle_no"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_vehicle_no_train_df = change_vehicle_no_to_dummy(train_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "rev_dict_vehicle_no_test_df = dict((v,k) for k,v in dict_vehicle_no_train_df.iteritems())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_df.vehicle_no = test_df.vehicle_no.replace(rev_dict_vehicle_no_test_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df['virtual_avg_speed'] = train_df['distance_travelled'] / train_df['time_taken']\n",
    "test_df['virtual_avg_speed'] = test_df['distance_travelled'] / test_df['time_taken']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_df_copy = test_df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_df.drop(['time_taken'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "# MODEL BUILDING AND APPLICATION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_X = train_df.drop(['time_taken'], axis=1)\n",
    "train_y = train_df.time_taken"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(998, 9)"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_X.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "X_train, X_test, y_train, y_test = train_test_split(train_X,train_y, test_size=0.3, random_state=42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "reg_mlp = MLPRegressor(hidden_layer_sizes=(100,50), activation='logistic', alpha=8, solver='lbfgs', random_state=42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "scale= MinMaxScaler()\n",
    "X_train_scaled = scale.fit_transform(X_train)\n",
    "X_test_scaled = scale.fit_transform(X_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "88.360236166104116"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reg_mlp.fit(X_train_scaled, y_train)\n",
    "reg_mlp.score(X_test_scaled, y_test) * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Accuracy: 0.97 (+/- 0.03)\n"
     ]
    }
   ],
   "source": [
    "scores = cross_val_score(reg_mlp, X_train_scaled, y_train, scoring='r2', n_jobs=-1)\n",
    "print(\"Accuracy: %0.2f (+/- %0.2f)\" % (scores.mean(), scores.std() * 2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "scale = MinMaxScaler()\n",
    "X_predict_scaled = scale.fit_transform(test_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "predictions = reg_mlp.predict(X_predict_scaled)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "show_df = test_df.copy()\n",
    "show_df['predicted'] = predictions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "show_df['predict_diff'] = show_df['predicted'].diff()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "def med_toll_time_taken(toll):\n",
    "    return train_df['time_taken'][train_df['toll_both'] == toll ].median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "def mean_toll_time_taken(toll):\n",
    "    return train_df['time_taken'][train_df['toll_both'] == toll ].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "show_df['med_time'] = show_df['toll_both']\n",
    "show_df['med_time'] = show_df.med_time.apply(lambda x:med_toll_time_taken(x))\n",
    "show_df['mean_time'] = show_df['toll_both']\n",
    "show_df['mean_time'] = show_df.mean_time.apply(lambda x:mean_toll_time_taken(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "show_df['avg_pred_med'] = (show_df['predicted'] + show_df['med_time']) / 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>toll_both</th>\n",
       "      <th>vehicle_no</th>\n",
       "      <th>loading_out_time</th>\n",
       "      <th>distance_travelled</th>\n",
       "      <th>time_of_travel</th>\n",
       "      <th>day_of_travel</th>\n",
       "      <th>month_of_travel</th>\n",
       "      <th>days_from_now</th>\n",
       "      <th>time_taken</th>\n",
       "      <th>virtual_avg_speed</th>\n",
       "      <th>avg_speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>793</th>\n",
       "      <td>2</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>0.00</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>40.885833</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>704</th>\n",
       "      <td>3</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>90.70</td>\n",
       "      <td>21</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>45.974444</td>\n",
       "      <td>1.972835</td>\n",
       "      <td>17.803708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>576</th>\n",
       "      <td>4</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>147.17</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>53.906389</td>\n",
       "      <td>2.730103</td>\n",
       "      <td>11.297836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>204.06</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>55.247222</td>\n",
       "      <td>3.693579</td>\n",
       "      <td>14.203163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>6</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>292.31</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>58.063611</td>\n",
       "      <td>5.034306</td>\n",
       "      <td>17.010976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>7</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>377.35</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>61.943889</td>\n",
       "      <td>6.091804</td>\n",
       "      <td>17.914546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>528</th>\n",
       "      <td>8</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>493.02</td>\n",
       "      <td>17</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>66.076667</td>\n",
       "      <td>7.461333</td>\n",
       "      <td>19.566874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>9</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>520.71</td>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>66.876667</td>\n",
       "      <td>7.786124</td>\n",
       "      <td>20.029876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>657</th>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>560.93</td>\n",
       "      <td>19</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>67.979444</td>\n",
       "      <td>8.251465</td>\n",
       "      <td>20.698948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>484</th>\n",
       "      <td>10</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>560.93</td>\n",
       "      <td>19</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>67.979444</td>\n",
       "      <td>8.251465</td>\n",
       "      <td>20.698948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>926</th>\n",
       "      <td>14</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>692.98</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>78.736667</td>\n",
       "      <td>8.801236</td>\n",
       "      <td>18.305362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439</th>\n",
       "      <td>15</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>763.54</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>80.906944</td>\n",
       "      <td>9.437262</td>\n",
       "      <td>19.075650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>344</th>\n",
       "      <td>18</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>913.49</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>85.330278</td>\n",
       "      <td>10.705344</td>\n",
       "      <td>20.550828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>750</th>\n",
       "      <td>19</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>935.46</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>85.913611</td>\n",
       "      <td>10.888379</td>\n",
       "      <td>20.772485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388</th>\n",
       "      <td>20</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>982.27</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>87.833333</td>\n",
       "      <td>11.183340</td>\n",
       "      <td>20.920133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>21</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>1065.71</td>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>90.722500</td>\n",
       "      <td>11.746921</td>\n",
       "      <td>21.381552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>879</th>\n",
       "      <td>22</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>1130.22</td>\n",
       "      <td>21</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>93.809167</td>\n",
       "      <td>12.048076</td>\n",
       "      <td>21.353444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>836</th>\n",
       "      <td>24</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>1194.00</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>102.798333</td>\n",
       "      <td>11.614974</td>\n",
       "      <td>19.283465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>969</th>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>1262.04</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>105.451944</td>\n",
       "      <td>11.967916</td>\n",
       "      <td>19.544711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>26</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>1292.76</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>106.454444</td>\n",
       "      <td>12.143786</td>\n",
       "      <td>19.714387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>27</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>1517.79</td>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>115.140000</td>\n",
       "      <td>13.182126</td>\n",
       "      <td>20.438863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>616</th>\n",
       "      <td>28</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>1549.00</td>\n",
       "      <td>19</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>116.326389</td>\n",
       "      <td>13.315981</td>\n",
       "      <td>20.531135</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     toll_both  vehicle_no  loading_out_time  distance_travelled  \\\n",
       "793          2          25                23                0.00   \n",
       "704          3          25                23               90.70   \n",
       "576          4          25                23              147.17   \n",
       "235          5          25                23              204.06   \n",
       "280          6          25                23              292.31   \n",
       "27           7          25                23              377.35   \n",
       "528          8          25                23              493.02   \n",
       "154          9          25                23              520.71   \n",
       "657         11          25                23              560.93   \n",
       "484         10          25                23              560.93   \n",
       "926         14          25                23              692.98   \n",
       "439         15          25                23              763.54   \n",
       "344         18          25                23              913.49   \n",
       "750         19          25                23              935.46   \n",
       "388         20          25                23              982.27   \n",
       "66          21          25                23             1065.71   \n",
       "879         22          25                23             1130.22   \n",
       "836         24          25                23             1194.00   \n",
       "969         25          25                23             1262.04   \n",
       "109         26          25                23             1292.76   \n",
       "194         27          25                23             1517.79   \n",
       "616         28          25                23             1549.00   \n",
       "\n",
       "     time_of_travel  day_of_travel  month_of_travel  days_from_now  \\\n",
       "793              16              3               11             25   \n",
       "704              21              3               11             25   \n",
       "576               5              3               11             25   \n",
       "235               6              3               11             25   \n",
       "280               9              3               11             25   \n",
       "27               13              3               11             25   \n",
       "528              17              3               11             25   \n",
       "154              18              3               11             25   \n",
       "657              19              3               11             25   \n",
       "484              19              3               11             25   \n",
       "926               6              3               11             25   \n",
       "439               8              3               11             25   \n",
       "344              12              3               11             25   \n",
       "750              13              3               11             25   \n",
       "388              15              3               11             25   \n",
       "66               18              3               11             25   \n",
       "879              21              3               11             25   \n",
       "836               6              3               11             25   \n",
       "969               8              3               11             25   \n",
       "109               9              3               11             25   \n",
       "194              18              3               11             25   \n",
       "616              19              3               11             25   \n",
       "\n",
       "     time_taken  virtual_avg_speed  avg_speed  \n",
       "793   40.885833           0.000000   0.000000  \n",
       "704   45.974444           1.972835  17.803708  \n",
       "576   53.906389           2.730103  11.297836  \n",
       "235   55.247222           3.693579  14.203163  \n",
       "280   58.063611           5.034306  17.010976  \n",
       "27    61.943889           6.091804  17.914546  \n",
       "528   66.076667           7.461333  19.566874  \n",
       "154   66.876667           7.786124  20.029876  \n",
       "657   67.979444           8.251465  20.698948  \n",
       "484   67.979444           8.251465  20.698948  \n",
       "926   78.736667           8.801236  18.305362  \n",
       "439   80.906944           9.437262  19.075650  \n",
       "344   85.330278          10.705344  20.550828  \n",
       "750   85.913611          10.888379  20.772485  \n",
       "388   87.833333          11.183340  20.920133  \n",
       "66    90.722500          11.746921  21.381552  \n",
       "879   93.809167          12.048076  21.353444  \n",
       "836  102.798333          11.614974  19.283465  \n",
       "969  105.451944          11.967916  19.544711  \n",
       "109  106.454444          12.143786  19.714387  \n",
       "194  115.140000          13.182126  20.438863  \n",
       "616  116.326389          13.315981  20.531135  "
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = train_df\n",
    "x['avg_speed'] = x.distance_travelled/ x.time_taken\n",
    "x = x[x.vehicle_no == 25].sort_values('distance_travelled')\n",
    "x['avg_speed'] = x.distance_travelled/(x.time_taken - 40.88)\n",
    "x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>toll_both</th>\n",
       "      <th>vehicle_no</th>\n",
       "      <th>loading_out_time</th>\n",
       "      <th>distance_travelled</th>\n",
       "      <th>time_of_travel</th>\n",
       "      <th>day_of_travel</th>\n",
       "      <th>month_of_travel</th>\n",
       "      <th>days_from_now</th>\n",
       "      <th>time_taken</th>\n",
       "      <th>virtual_avg_speed</th>\n",
       "      <th>avg_speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>990</th>\n",
       "      <td>2</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>20.335556</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>991</th>\n",
       "      <td>3</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>95.1</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>22.658056</td>\n",
       "      <td>4.197183</td>\n",
       "      <td>4.197183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>992</th>\n",
       "      <td>4</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>146.8</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>24.124722</td>\n",
       "      <td>6.085044</td>\n",
       "      <td>6.085044</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     toll_both  vehicle_no  loading_out_time  distance_travelled  \\\n",
       "990          2          45                12                 0.0   \n",
       "991          3          45                12                95.1   \n",
       "992          4          45                12               146.8   \n",
       "\n",
       "     time_of_travel  day_of_travel  month_of_travel  days_from_now  \\\n",
       "990               8              5               11             40   \n",
       "991              10              5               11             40   \n",
       "992              12              5               11             40   \n",
       "\n",
       "     time_taken  virtual_avg_speed  avg_speed  \n",
       "990   20.335556           0.000000   0.000000  \n",
       "991   22.658056           4.197183   4.197183  \n",
       "992   24.124722           6.085044   6.085044  "
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df[train_df['vehicle_no'] == 45]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "show_df['avg_speed_pred'] = show_df['distance_travelled']/show_df['predicted']\n",
    "show_df['avg_speed_med'] = show_df['distance_travelled']/show_df['med_time']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "show_df['test_pred'] = show_df['predicted'] + show_df.avg_speed_pred"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "show_df['round_pred'] = show_df['predicted'].round()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "show_df['median_input'] =  test_df_copy['time_taken']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [],
   "source": [
    "show_df.drop(['virtual_avg_speed','predict_diff','mean_time','avg_pred_med','avg_speed_med','avg_speed_pred'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "show_df['mean_pred_med'] = (show_df['round_pred'] + show_df['median_input'] )/2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>toll_both</th>\n",
       "      <th>vehicle_no</th>\n",
       "      <th>loading_out_time</th>\n",
       "      <th>distance_travelled</th>\n",
       "      <th>time_of_travel</th>\n",
       "      <th>day_of_travel</th>\n",
       "      <th>month_of_travel</th>\n",
       "      <th>days_from_now</th>\n",
       "      <th>time_taken</th>\n",
       "      <th>virtual_avg_speed</th>\n",
       "      <th>avg_speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>33</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1610.77</td>\n",
       "      <td>21</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "      <td>93.539722</td>\n",
       "      <td>17.220171</td>\n",
       "      <td>17.220171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>33</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1641.28</td>\n",
       "      <td>23</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>90.695556</td>\n",
       "      <td>18.096587</td>\n",
       "      <td>18.096587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>33</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>1620.49</td>\n",
       "      <td>23</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>91.733889</td>\n",
       "      <td>17.665118</td>\n",
       "      <td>17.665118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>33</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1611.30</td>\n",
       "      <td>22</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>91.681111</td>\n",
       "      <td>17.575049</td>\n",
       "      <td>17.575049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>33</td>\n",
       "      <td>7</td>\n",
       "      <td>22</td>\n",
       "      <td>1615.10</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>121.713056</td>\n",
       "      <td>13.269735</td>\n",
       "      <td>13.269735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>33</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>1625.30</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>20</td>\n",
       "      <td>191.201389</td>\n",
       "      <td>8.500461</td>\n",
       "      <td>8.500461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>33</td>\n",
       "      <td>9</td>\n",
       "      <td>14</td>\n",
       "      <td>1509.52</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>16</td>\n",
       "      <td>106.604444</td>\n",
       "      <td>14.160010</td>\n",
       "      <td>14.160010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>302</th>\n",
       "      <td>32</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1574.09</td>\n",
       "      <td>18</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "      <td>90.166111</td>\n",
       "      <td>17.457668</td>\n",
       "      <td>17.457668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>32</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>1578.05</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>29</td>\n",
       "      <td>144.914167</td>\n",
       "      <td>10.889550</td>\n",
       "      <td>10.889550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1584.41</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>168.228056</td>\n",
       "      <td>9.418227</td>\n",
       "      <td>9.418227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>305</th>\n",
       "      <td>32</td>\n",
       "      <td>16</td>\n",
       "      <td>23</td>\n",
       "      <td>1574.79</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>16</td>\n",
       "      <td>146.097222</td>\n",
       "      <td>10.779055</td>\n",
       "      <td>10.779055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306</th>\n",
       "      <td>32</td>\n",
       "      <td>17</td>\n",
       "      <td>22</td>\n",
       "      <td>1546.99</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>20</td>\n",
       "      <td>114.960000</td>\n",
       "      <td>13.456768</td>\n",
       "      <td>13.456768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>307</th>\n",
       "      <td>32</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>1571.04</td>\n",
       "      <td>19</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>38</td>\n",
       "      <td>137.944722</td>\n",
       "      <td>11.388910</td>\n",
       "      <td>11.388910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>308</th>\n",
       "      <td>32</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>1582.97</td>\n",
       "      <td>22</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>29</td>\n",
       "      <td>118.489444</td>\n",
       "      <td>13.359587</td>\n",
       "      <td>13.359587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>309</th>\n",
       "      <td>32</td>\n",
       "      <td>38</td>\n",
       "      <td>15</td>\n",
       "      <td>1584.11</td>\n",
       "      <td>20</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>21</td>\n",
       "      <td>125.666389</td>\n",
       "      <td>12.605678</td>\n",
       "      <td>12.605678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>310</th>\n",
       "      <td>32</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1600.56</td>\n",
       "      <td>18</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>85.666389</td>\n",
       "      <td>18.683640</td>\n",
       "      <td>18.683640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>311</th>\n",
       "      <td>32</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>1559.10</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>39</td>\n",
       "      <td>128.515833</td>\n",
       "      <td>12.131579</td>\n",
       "      <td>12.131579</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312</th>\n",
       "      <td>32</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>1584.69</td>\n",
       "      <td>21</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>90.010833</td>\n",
       "      <td>17.605547</td>\n",
       "      <td>17.605547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>313</th>\n",
       "      <td>32</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>1576.51</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>28</td>\n",
       "      <td>110.215833</td>\n",
       "      <td>14.303843</td>\n",
       "      <td>14.303843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>314</th>\n",
       "      <td>32</td>\n",
       "      <td>28</td>\n",
       "      <td>2</td>\n",
       "      <td>1584.85</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>21</td>\n",
       "      <td>118.666944</td>\n",
       "      <td>13.355446</td>\n",
       "      <td>13.355446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315</th>\n",
       "      <td>32</td>\n",
       "      <td>41</td>\n",
       "      <td>3</td>\n",
       "      <td>1569.80</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>29</td>\n",
       "      <td>117.335556</td>\n",
       "      <td>13.378724</td>\n",
       "      <td>13.378724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>316</th>\n",
       "      <td>32</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1575.17</td>\n",
       "      <td>21</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>90.211667</td>\n",
       "      <td>17.460824</td>\n",
       "      <td>17.460824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>317</th>\n",
       "      <td>32</td>\n",
       "      <td>7</td>\n",
       "      <td>22</td>\n",
       "      <td>1578.99</td>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>112.044167</td>\n",
       "      <td>14.092568</td>\n",
       "      <td>14.092568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>318</th>\n",
       "      <td>32</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>1588.94</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>20</td>\n",
       "      <td>184.901667</td>\n",
       "      <td>8.593433</td>\n",
       "      <td>8.593433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>319</th>\n",
       "      <td>32</td>\n",
       "      <td>9</td>\n",
       "      <td>14</td>\n",
       "      <td>1474.41</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>16</td>\n",
       "      <td>105.486111</td>\n",
       "      <td>13.977290</td>\n",
       "      <td>13.977290</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     toll_both  vehicle_no  loading_out_time  distance_travelled  \\\n",
       "2           33           3                 0             1610.77   \n",
       "3           33           4                 4             1641.28   \n",
       "4           33           5                 3             1620.49   \n",
       "5           33           6                 3             1611.30   \n",
       "6           33           7                22             1615.10   \n",
       "7           33           8                23             1625.30   \n",
       "8           33           9                14             1509.52   \n",
       "302         32           3                 0             1574.09   \n",
       "303         32          13                 4             1578.05   \n",
       "304         32           1                 0             1584.41   \n",
       "305         32          16                23             1574.79   \n",
       "306         32          17                22             1546.99   \n",
       "307         32          21                 1             1571.04   \n",
       "308         32          24                 0             1582.97   \n",
       "309         32          38                15             1584.11   \n",
       "310         32           4                 4             1600.56   \n",
       "311         32           5                 8             1559.10   \n",
       "312         32           5                 3             1584.69   \n",
       "313         32          27                 1             1576.51   \n",
       "314         32          28                 2             1584.85   \n",
       "315         32          41                 3             1569.80   \n",
       "316         32           6                 3             1575.17   \n",
       "317         32           7                22             1578.99   \n",
       "318         32           8                23             1588.94   \n",
       "319         32           9                14             1474.41   \n",
       "\n",
       "     time_of_travel  day_of_travel  month_of_travel  days_from_now  \\\n",
       "2                21              5               12             14   \n",
       "3                23              4               12             15   \n",
       "4                23              2               11             19   \n",
       "5                22              2               11             19   \n",
       "6                 0              2               11             19   \n",
       "7                22              1               11             20   \n",
       "8                 1              4               12             16   \n",
       "302              18              5               12             14   \n",
       "303               5              5               11             29   \n",
       "304               0              2               11             40   \n",
       "305               1              5               12             16   \n",
       "306              17              0               11             20   \n",
       "307              19              4               11             38   \n",
       "308              22              5               11             29   \n",
       "309              20              6               11             21   \n",
       "310              18              4               12             15   \n",
       "311              17              1               11             39   \n",
       "312              21              2               11             19   \n",
       "313              15              5               11             28   \n",
       "314               0              6               11             21   \n",
       "315               0              5               11             29   \n",
       "316              21              2               11             19   \n",
       "317              15              2               11             19   \n",
       "318              15              1               11             20   \n",
       "319               0              4               12             16   \n",
       "\n",
       "     time_taken  virtual_avg_speed  avg_speed  \n",
       "2     93.539722          17.220171  17.220171  \n",
       "3     90.695556          18.096587  18.096587  \n",
       "4     91.733889          17.665118  17.665118  \n",
       "5     91.681111          17.575049  17.575049  \n",
       "6    121.713056          13.269735  13.269735  \n",
       "7    191.201389           8.500461   8.500461  \n",
       "8    106.604444          14.160010  14.160010  \n",
       "302   90.166111          17.457668  17.457668  \n",
       "303  144.914167          10.889550  10.889550  \n",
       "304  168.228056           9.418227   9.418227  \n",
       "305  146.097222          10.779055  10.779055  \n",
       "306  114.960000          13.456768  13.456768  \n",
       "307  137.944722          11.388910  11.388910  \n",
       "308  118.489444          13.359587  13.359587  \n",
       "309  125.666389          12.605678  12.605678  \n",
       "310   85.666389          18.683640  18.683640  \n",
       "311  128.515833          12.131579  12.131579  \n",
       "312   90.010833          17.605547  17.605547  \n",
       "313  110.215833          14.303843  14.303843  \n",
       "314  118.666944          13.355446  13.355446  \n",
       "315  117.335556          13.378724  13.378724  \n",
       "316   90.211667          17.460824  17.460824  \n",
       "317  112.044167          14.092568  14.092568  \n",
       "318  184.901667           8.593433   8.593433  \n",
       "319  105.486111          13.977290  13.977290  "
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df[(train_df['toll_both'] == 33) | (train_df['toll_both'] == 32)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Aganampudi': 13,\n",
       " 'Attibele (BETL)': 33,\n",
       " 'Bellupada': 7,\n",
       " 'Bolapalli': 21,\n",
       " 'Budanam': 26,\n",
       " 'Chilakapalem': 9,\n",
       " 'Eethakota': 16,\n",
       " 'Gaddurur': 27,\n",
       " 'Gudipada (Old Gangapada)': 5,\n",
       " 'Gurapalli': 6,\n",
       " 'Hasanpur (Sainkul)': 1,\n",
       " 'Hoskote': 32,\n",
       " 'Kalaparru': 18,\n",
       " 'Kaza': 20,\n",
       " 'Krishnagiri': 31,\n",
       " 'Krishnavaram': 15,\n",
       " 'Laxmipuram': 10,\n",
       " 'Madapam': 8,\n",
       " 'Main Toll (Gosthani Gate) (Sheela Nagar)': 12,\n",
       " 'Manguli': 4,\n",
       " 'Mulbagal': 28,\n",
       " 'Nathavalasa/ Vizianagaram': 11,\n",
       " 'Palempalli': 23,\n",
       " 'Pallikonda': 29,\n",
       " 'Panikholi': 3,\n",
       " 'Pottipadu': 19,\n",
       " 'Sergrarh': 2,\n",
       " 'Sunambatti (Musunur)': 24,\n",
       " 'Tangutur': 22,\n",
       " 'Unguturu': 17,\n",
       " 'Vaniyambadi': 30,\n",
       " 'Vempadu': 14,\n",
       " 'Venkatachallam &#160;(Old Nellore)': 25}"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_toll_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>toll_both</th>\n",
       "      <th>vehicle_no</th>\n",
       "      <th>loading_out_time</th>\n",
       "      <th>distance_travelled</th>\n",
       "      <th>time_of_travel</th>\n",
       "      <th>day_of_travel</th>\n",
       "      <th>month_of_travel</th>\n",
       "      <th>days_from_now</th>\n",
       "      <th>predicted</th>\n",
       "      <th>med_time</th>\n",
       "      <th>test_pred</th>\n",
       "      <th>round_pred</th>\n",
       "      <th>median_input</th>\n",
       "      <th>mean_pred_med</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>203.320</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>40.966541</td>\n",
       "      <td>45.996250</td>\n",
       "      <td>45.929616</td>\n",
       "      <td>41.0</td>\n",
       "      <td>46.387500</td>\n",
       "      <td>43.693750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>291.250</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>37.942090</td>\n",
       "      <td>49.686667</td>\n",
       "      <td>45.618262</td>\n",
       "      <td>38.0</td>\n",
       "      <td>49.882639</td>\n",
       "      <td>43.941319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>375.245</td>\n",
       "      <td>20</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>51.406044</td>\n",
       "      <td>56.755556</td>\n",
       "      <td>58.705672</td>\n",
       "      <td>51.0</td>\n",
       "      <td>56.755556</td>\n",
       "      <td>53.877778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>490.530</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>55.744775</td>\n",
       "      <td>60.303056</td>\n",
       "      <td>64.544344</td>\n",
       "      <td>56.0</td>\n",
       "      <td>60.303056</td>\n",
       "      <td>58.151528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>517.415</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>61.819924</td>\n",
       "      <td>63.638889</td>\n",
       "      <td>70.189637</td>\n",
       "      <td>62.0</td>\n",
       "      <td>63.638889</td>\n",
       "      <td>62.819444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>11</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>556.620</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>64.102620</td>\n",
       "      <td>65.382917</td>\n",
       "      <td>72.785885</td>\n",
       "      <td>64.0</td>\n",
       "      <td>65.382917</td>\n",
       "      <td>64.691458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>556.620</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>63.573588</td>\n",
       "      <td>65.382917</td>\n",
       "      <td>72.329111</td>\n",
       "      <td>64.0</td>\n",
       "      <td>65.382917</td>\n",
       "      <td>64.691458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>14</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>684.270</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>68.525055</td>\n",
       "      <td>71.403333</td>\n",
       "      <td>78.510745</td>\n",
       "      <td>69.0</td>\n",
       "      <td>71.403333</td>\n",
       "      <td>70.201667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>15</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>753.930</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>74.526918</td>\n",
       "      <td>76.884444</td>\n",
       "      <td>84.643129</td>\n",
       "      <td>75.0</td>\n",
       "      <td>76.884444</td>\n",
       "      <td>75.942222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>18</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>897.510</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>84.239283</td>\n",
       "      <td>86.089444</td>\n",
       "      <td>94.893576</td>\n",
       "      <td>84.0</td>\n",
       "      <td>86.089444</td>\n",
       "      <td>85.044722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>19</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>918.860</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>83.769129</td>\n",
       "      <td>86.291944</td>\n",
       "      <td>94.738086</td>\n",
       "      <td>84.0</td>\n",
       "      <td>86.291944</td>\n",
       "      <td>85.145972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>20</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>964.230</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>84.842131</td>\n",
       "      <td>88.036944</td>\n",
       "      <td>96.207121</td>\n",
       "      <td>85.0</td>\n",
       "      <td>88.036944</td>\n",
       "      <td>86.518472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>21</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>1046.730</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>88.609056</td>\n",
       "      <td>92.291944</td>\n",
       "      <td>100.421957</td>\n",
       "      <td>89.0</td>\n",
       "      <td>92.291944</td>\n",
       "      <td>90.645972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>24</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>1171.885</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>99.691590</td>\n",
       "      <td>102.037083</td>\n",
       "      <td>111.446694</td>\n",
       "      <td>100.0</td>\n",
       "      <td>102.037083</td>\n",
       "      <td>101.018542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>25</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>1239.060</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>102.589618</td>\n",
       "      <td>105.121667</td>\n",
       "      <td>114.667449</td>\n",
       "      <td>103.0</td>\n",
       "      <td>105.121667</td>\n",
       "      <td>104.060833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>26</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>1269.870</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>103.205140</td>\n",
       "      <td>106.137500</td>\n",
       "      <td>115.509469</td>\n",
       "      <td>103.0</td>\n",
       "      <td>106.137500</td>\n",
       "      <td>104.568750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>27</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>1487.190</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>119.689833</td>\n",
       "      <td>122.277500</td>\n",
       "      <td>132.115199</td>\n",
       "      <td>120.0</td>\n",
       "      <td>122.277500</td>\n",
       "      <td>121.138750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>28</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>1517.610</td>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>119.884345</td>\n",
       "      <td>123.210833</td>\n",
       "      <td>132.543295</td>\n",
       "      <td>120.0</td>\n",
       "      <td>123.210833</td>\n",
       "      <td>121.605417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>32</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>1577.280</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>109.081285</td>\n",
       "      <td>117.912500</td>\n",
       "      <td>123.540960</td>\n",
       "      <td>109.0</td>\n",
       "      <td>117.912500</td>\n",
       "      <td>113.456250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>33</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>1615.100</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>77.443029</td>\n",
       "      <td>93.539722</td>\n",
       "      <td>98.298360</td>\n",
       "      <td>77.0</td>\n",
       "      <td>93.539722</td>\n",
       "      <td>85.269861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>7</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>375.245</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>37.469293</td>\n",
       "      <td>56.755556</td>\n",
       "      <td>47.484027</td>\n",
       "      <td>37.0</td>\n",
       "      <td>56.755556</td>\n",
       "      <td>46.877778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>8</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>490.530</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>45.088403</td>\n",
       "      <td>60.303056</td>\n",
       "      <td>55.967697</td>\n",
       "      <td>45.0</td>\n",
       "      <td>60.303056</td>\n",
       "      <td>52.651528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>9</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>517.415</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>49.703280</td>\n",
       "      <td>63.638889</td>\n",
       "      <td>60.113358</td>\n",
       "      <td>50.0</td>\n",
       "      <td>63.638889</td>\n",
       "      <td>56.819444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>11</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>556.620</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>53.163981</td>\n",
       "      <td>65.382917</td>\n",
       "      <td>63.633851</td>\n",
       "      <td>53.0</td>\n",
       "      <td>65.382917</td>\n",
       "      <td>59.191458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>556.620</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>52.391013</td>\n",
       "      <td>65.382917</td>\n",
       "      <td>63.015354</td>\n",
       "      <td>52.0</td>\n",
       "      <td>65.382917</td>\n",
       "      <td>58.691458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>14</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>684.270</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>60.596905</td>\n",
       "      <td>71.403333</td>\n",
       "      <td>71.889066</td>\n",
       "      <td>61.0</td>\n",
       "      <td>71.403333</td>\n",
       "      <td>66.201667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>15</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>753.930</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>69.416095</td>\n",
       "      <td>76.884444</td>\n",
       "      <td>80.277121</td>\n",
       "      <td>69.0</td>\n",
       "      <td>76.884444</td>\n",
       "      <td>72.942222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>18</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>897.510</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>80.124872</td>\n",
       "      <td>86.089444</td>\n",
       "      <td>91.326262</td>\n",
       "      <td>80.0</td>\n",
       "      <td>86.089444</td>\n",
       "      <td>83.044722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>19</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>918.860</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>80.293545</td>\n",
       "      <td>86.291944</td>\n",
       "      <td>91.737304</td>\n",
       "      <td>80.0</td>\n",
       "      <td>86.291944</td>\n",
       "      <td>83.145972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>20</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>964.230</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>82.083613</td>\n",
       "      <td>88.036944</td>\n",
       "      <td>93.830537</td>\n",
       "      <td>82.0</td>\n",
       "      <td>88.036944</td>\n",
       "      <td>85.018472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>21</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>1046.730</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>86.621978</td>\n",
       "      <td>92.291944</td>\n",
       "      <td>98.705862</td>\n",
       "      <td>87.0</td>\n",
       "      <td>92.291944</td>\n",
       "      <td>89.645972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>24</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>1171.885</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>101.519782</td>\n",
       "      <td>102.037083</td>\n",
       "      <td>113.063197</td>\n",
       "      <td>102.0</td>\n",
       "      <td>102.037083</td>\n",
       "      <td>102.018542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>25</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>1239.060</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>104.491036</td>\n",
       "      <td>105.121667</td>\n",
       "      <td>116.349087</td>\n",
       "      <td>104.0</td>\n",
       "      <td>105.121667</td>\n",
       "      <td>104.560833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>26</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>1269.870</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>105.007304</td>\n",
       "      <td>106.137500</td>\n",
       "      <td>117.100463</td>\n",
       "      <td>105.0</td>\n",
       "      <td>106.137500</td>\n",
       "      <td>105.568750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>27</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>1487.190</td>\n",
       "      <td>23</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>122.168266</td>\n",
       "      <td>122.277500</td>\n",
       "      <td>134.341558</td>\n",
       "      <td>122.0</td>\n",
       "      <td>122.277500</td>\n",
       "      <td>122.138750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>28</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>1517.610</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>132.418813</td>\n",
       "      <td>123.210833</td>\n",
       "      <td>143.879495</td>\n",
       "      <td>132.0</td>\n",
       "      <td>123.210833</td>\n",
       "      <td>127.605417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>32</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>1577.280</td>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>111.384487</td>\n",
       "      <td>117.912500</td>\n",
       "      <td>125.545167</td>\n",
       "      <td>111.0</td>\n",
       "      <td>117.912500</td>\n",
       "      <td>114.456250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>33</td>\n",
       "      <td>46</td>\n",
       "      <td>21</td>\n",
       "      <td>1615.100</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>81.571867</td>\n",
       "      <td>93.539722</td>\n",
       "      <td>101.371586</td>\n",
       "      <td>82.0</td>\n",
       "      <td>93.539722</td>\n",
       "      <td>87.769861</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    toll_both  vehicle_no loading_out_time  distance_travelled time_of_travel  \\\n",
       "0           5          45               12             203.320             10   \n",
       "1           6          45               12             291.250             13   \n",
       "2           7          45               12             375.245             20   \n",
       "3           8          45               12             490.530              0   \n",
       "4           9          45               12             517.415              3   \n",
       "5          11          45               12             556.620              5   \n",
       "6          10          45               12             556.620              5   \n",
       "7          14          45               12             684.270             11   \n",
       "8          15          45               12             753.930             16   \n",
       "9          18          45               12             897.510              2   \n",
       "10         19          45               12             918.860              2   \n",
       "11         20          45               12             964.230              4   \n",
       "12         21          45               12            1046.730              8   \n",
       "13         24          45               12            1171.885             18   \n",
       "14         25          45               12            1239.060             21   \n",
       "15         26          45               12            1269.870             22   \n",
       "16         27          45               12            1487.190             14   \n",
       "17         28          45               12            1517.610             15   \n",
       "18         32          45               12            1577.280              9   \n",
       "19         33          45               12            1615.100              9   \n",
       "20          7          46               21             375.245              5   \n",
       "21          8          46               21             490.530              9   \n",
       "22          9          46               21             517.415             12   \n",
       "23         11          46               21             556.620             14   \n",
       "24         10          46               21             556.620             14   \n",
       "25         14          46               21             684.270             20   \n",
       "26         15          46               21             753.930              1   \n",
       "27         18          46               21             897.510             11   \n",
       "28         19          46               21             918.860             11   \n",
       "29         20          46               21             964.230             13   \n",
       "30         21          46               21            1046.730             17   \n",
       "31         24          46               21            1171.885              3   \n",
       "32         25          46               21            1239.060              6   \n",
       "33         26          46               21            1269.870              7   \n",
       "34         27          46               21            1487.190             23   \n",
       "35         28          46               21            1517.610              0   \n",
       "36         32          46               21            1577.280             18   \n",
       "37         33          46               21            1615.100             18   \n",
       "\n",
       "   day_of_travel month_of_travel days_from_now   predicted    med_time  \\\n",
       "0              6              11            40   40.966541   45.996250   \n",
       "1              6              11            40   37.942090   49.686667   \n",
       "2              6              11            40   51.406044   56.755556   \n",
       "3              0              11            40   55.744775   60.303056   \n",
       "4              0              11            40   61.819924   63.638889   \n",
       "5              0              11            40   64.102620   65.382917   \n",
       "6              0              11            40   63.573588   65.382917   \n",
       "7              0              11            40   68.525055   71.403333   \n",
       "8              0              11            40   74.526918   76.884444   \n",
       "9              1              11            40   84.239283   86.089444   \n",
       "10             1              11            40   83.769129   86.291944   \n",
       "11             1              11            40   84.842131   88.036944   \n",
       "12             1              11            40   88.609056   92.291944   \n",
       "13             1              11            40   99.691590  102.037083   \n",
       "14             1              11            40  102.589618  105.121667   \n",
       "15             1              11            40  103.205140  106.137500   \n",
       "16             2              11            40  119.689833  122.277500   \n",
       "17             2              11            40  119.884345  123.210833   \n",
       "18             2              11            40  109.081285  117.912500   \n",
       "19             1              11            40   77.443029   93.539722   \n",
       "20             1              10            53   37.469293   56.755556   \n",
       "21             1              10            53   45.088403   60.303056   \n",
       "22             1              10            53   49.703280   63.638889   \n",
       "23             1              10            53   53.163981   65.382917   \n",
       "24             1              10            53   52.391013   65.382917   \n",
       "25             1              10            53   60.596905   71.403333   \n",
       "26             2              10            53   69.416095   76.884444   \n",
       "27             2              10            53   80.124872   86.089444   \n",
       "28             2              10            53   80.293545   86.291944   \n",
       "29             2              10            53   82.083613   88.036944   \n",
       "30             2              10            53   86.621978   92.291944   \n",
       "31             3              10            53  101.519782  102.037083   \n",
       "32             3              10            53  104.491036  105.121667   \n",
       "33             3              10            53  105.007304  106.137500   \n",
       "34             3              10            53  122.168266  122.277500   \n",
       "35             4              10            53  132.418813  123.210833   \n",
       "36             3              10            53  111.384487  117.912500   \n",
       "37             2              10            53   81.571867   93.539722   \n",
       "\n",
       "     test_pred  round_pred  median_input  mean_pred_med  \n",
       "0    45.929616        41.0     46.387500      43.693750  \n",
       "1    45.618262        38.0     49.882639      43.941319  \n",
       "2    58.705672        51.0     56.755556      53.877778  \n",
       "3    64.544344        56.0     60.303056      58.151528  \n",
       "4    70.189637        62.0     63.638889      62.819444  \n",
       "5    72.785885        64.0     65.382917      64.691458  \n",
       "6    72.329111        64.0     65.382917      64.691458  \n",
       "7    78.510745        69.0     71.403333      70.201667  \n",
       "8    84.643129        75.0     76.884444      75.942222  \n",
       "9    94.893576        84.0     86.089444      85.044722  \n",
       "10   94.738086        84.0     86.291944      85.145972  \n",
       "11   96.207121        85.0     88.036944      86.518472  \n",
       "12  100.421957        89.0     92.291944      90.645972  \n",
       "13  111.446694       100.0    102.037083     101.018542  \n",
       "14  114.667449       103.0    105.121667     104.060833  \n",
       "15  115.509469       103.0    106.137500     104.568750  \n",
       "16  132.115199       120.0    122.277500     121.138750  \n",
       "17  132.543295       120.0    123.210833     121.605417  \n",
       "18  123.540960       109.0    117.912500     113.456250  \n",
       "19   98.298360        77.0     93.539722      85.269861  \n",
       "20   47.484027        37.0     56.755556      46.877778  \n",
       "21   55.967697        45.0     60.303056      52.651528  \n",
       "22   60.113358        50.0     63.638889      56.819444  \n",
       "23   63.633851        53.0     65.382917      59.191458  \n",
       "24   63.015354        52.0     65.382917      58.691458  \n",
       "25   71.889066        61.0     71.403333      66.201667  \n",
       "26   80.277121        69.0     76.884444      72.942222  \n",
       "27   91.326262        80.0     86.089444      83.044722  \n",
       "28   91.737304        80.0     86.291944      83.145972  \n",
       "29   93.830537        82.0     88.036944      85.018472  \n",
       "30   98.705862        87.0     92.291944      89.645972  \n",
       "31  113.063197       102.0    102.037083     102.018542  \n",
       "32  116.349087       104.0    105.121667     104.560833  \n",
       "33  117.100463       105.0    106.137500     105.568750  \n",
       "34  134.341558       122.0    122.277500     122.138750  \n",
       "35  143.879495       132.0    123.210833     127.605417  \n",
       "36  125.545167       111.0    117.912500     114.456250  \n",
       "37  101.371586        82.0     93.539722      87.769861  "
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "show_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}