final_2nd (6th 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": 169,
   "metadata": {},
   "outputs": [],
   "source": [
    "time = pd.read_csv('time_between_two_tolls_new.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [],
   "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)]\n",
    "time.drop(['toll_freq'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [],
   "source": [
    "time['loading_out_time'] = time['loading_out_time'].apply(lambda x:datetime.datetime.strptime(x, '%Y-%m-%d %H:%M:%S') )\n",
    "time['ist_timestamp'] = time['ist_timestamp'].apply(lambda x:datetime.datetime.strptime(x, '%Y-%m-%d %H:%M:%S') )\n",
    "time['hours']  = time['ist_timestamp'] - time['loading_out_time']\n",
    "time['hours'] = time.hours.apply(lambda x:x.total_seconds()/3600)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [],
   "source": [
    "time['distance_travelled'] = 0.0 #NL01N5129 CG04FB5095\n",
    "time['time_taken'] = time['hours']\n",
    "time['time_btn_tolls'] = 0  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_time = pd.DataFrame(columns=time.columns.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [],
   "source": [
    "for truck in time.vehicle_no.unique().tolist():\n",
    "    for st_time in time.start_date.unique().tolist():\n",
    "        df = time[(time.vehicle_no == truck) & (time.start_date == st_time)].sort_values('odometer_km')\n",
    "        df.distance_travelled = df.odometer_km.diff()\n",
    "        df.time_btn_tolls = df.hours.diff()\n",
    "        new_time = new_time.append(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [],
   "source": [
    "# new_time[new_time['time_btn_tolls'] == 0].shape\n",
    "new_time = new_time.loc[new_time['distance_travelled'] != 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_time = new_time.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "new_time.to_csv('new_toll_time.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [],
   "source": [
    "toll_time = pd.read_csv('new_toll_time.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# toll_time.drop(['Unnamed: 0'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_toll_time = toll_time.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Gurapalli                    181\n",
       "Manguli                      179\n",
       "Madapam                      179\n",
       "Sergrarh                     178\n",
       "Gudipada (Old Gangapada)     178\n",
       "Chilakapalem                 177\n",
       "Panikholi                    177\n",
       "Kalaparru                    174\n",
       "Vempadu                      174\n",
       "Krishnavaram                 174\n",
       "Budanam                      174\n",
       "Kaza                         173\n",
       "Pottipadu                    171\n",
       "Bolapalli                    170\n",
       "Sunambatti (Musunur)         170\n",
       "Tangutur                     166\n",
       "Bellupada                    154\n",
       "Gaddurur                     130\n",
       "Mulbagal                     128\n",
       "Laxmipuram                   101\n",
       "Nathavalasa/ Vizianagaram     78\n",
       "Name: toll_both, dtype: int64"
      ]
     },
     "execution_count": 198,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_toll_time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "JH02M1289     78\n",
       "OR07Y7231     75\n",
       "OR05AJ4215    70\n",
       "OR05AJ8915    60\n",
       "OD04D0605     59\n",
       "OD04F1988     59\n",
       "CG04FB5095    59\n",
       "JH05AE4540    58\n",
       "OR04M5329     58\n",
       "OR07W2988     57\n",
       "CG08L3445     56\n",
       "OD07M1151     56\n",
       "OR07AA0311    56\n",
       "OR07V5231     48\n",
       "JH01AE6027    43\n",
       "BR01GB9171    42\n",
       "OD04F2036     40\n",
       "BR01GA9889    40\n",
       "WB23B7201     40\n",
       "OR11D4683     40\n",
       "KA51A0876     39\n",
       "OR04M9321     39\n",
       "JH05V5242     39\n",
       "AP05TF2187    39\n",
       "OR05AJ1525    38\n",
       "OR05U6821     38\n",
       "OD04J1422     38\n",
       "OD04H5921     38\n",
       "OR05AD5529    37\n",
       "OR07V0091     37\n",
       "              ..\n",
       "OD11H9307     17\n",
       "BR02AA8561    17\n",
       "TN90C2988     17\n",
       "BR01GF7235    17\n",
       "NL02N0151     17\n",
       "OD07J9793     16\n",
       "JH0BQ1509     16\n",
       "AP26TB0388    16\n",
       "OR07Z2395     16\n",
       "TN36Y9906     16\n",
       "CG04DM2391    15\n",
       "WB25F7344     15\n",
       "AP05TB7489    15\n",
       "AP03TE2588    15\n",
       "OD04D9687     15\n",
       "TN31BD2234    14\n",
       "OD04F1729     14\n",
       "BR01GD7712    14\n",
       "AP04TX2171    13\n",
       "OR07W5637     13\n",
       "AP07TU2566    13\n",
       "OR07U9441     13\n",
       "TN56H9904     13\n",
       "JH05Z9697     12\n",
       "WB03C9131     10\n",
       "KA01AB5627    10\n",
       "AP07TU1248     7\n",
       "OR05AJ1911     5\n",
       "AP07TB1989     4\n",
       "JH05W7748      4\n",
       "Name: vehicle_no, Length: 130, dtype: int64"
      ]
     },
     "execution_count": 197,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_toll_time.vehicle_no.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_toll_time = toll_time.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "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": 188,
   "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','time_btn_tolls']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "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": 147,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Sergrarh',\n",
       " 'Panikholi',\n",
       " 'Manguli',\n",
       " 'Gudipada (Old Gangapada)',\n",
       " 'Gurapalli',\n",
       " 'Bellupada',\n",
       " 'Madapam',\n",
       " 'Chilakapalem',\n",
       " 'Nathavalasa/ Vizianagaram',\n",
       " 'Vempadu',\n",
       " 'Krishnavaram',\n",
       " 'Kalaparru',\n",
       " 'Pottipadu',\n",
       " 'Kaza',\n",
       " 'Bolapalli',\n",
       " 'Tangutur',\n",
       " 'Sunambatti (Musunur)',\n",
       " 'Budanam',\n",
       " 'Gaddurur',\n",
       " 'Mulbagal',\n",
       " 'Laxmipuram']"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_toll_time[new_toll_time.vehicle_no == 'JH02M1289'].sort_values('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 344,
   "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 SEQUENTIALLY: 3\n",
      "      \n",
      "Toll both: Sergrarh\n",
      "Distance travelled from previous 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 previous 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 previous toll in km: 54.6\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,Vempadu,Krishnavaram,Kalaparru,Kaza,Bolapalli,Tangutur,Sunambatti (Musunur),Budanam,Gaddurur\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 SEQUENTIALLY: 5\n",
      "      \n",
      "Toll both: Sergrarh\n",
      "Distance travelled from previous 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 previous toll in km: 94.3\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 previous toll in km: 53.9\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 previous toll in km: 56.89\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 previous toll in km: 88.98\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,Vempadu,Krishnavaram,Kalaparru,Kaza,Bolapalli,Tangutur,Sunambatti (Musunur),Budanam,Gaddurur\n",
      "Want to enter more vehicles?(y/n)n\n",
      "bbye\n"
     ]
    }
   ],
   "source": [
    "dict_last_ist = {}\n",
    "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','time_btn_tolls'])\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 SEQUENTIALLY: ')\n",
    "    count = 0\n",
    "    print '      '\n",
    "    while (count != loop):\n",
    "        b = []\n",
    "        toll = raw_input('Toll both: ')\n",
    "        distance = input('Distance travelled from previous 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",
    "        dict_last_ist[vehicle] = ist_delta\n",
    "        count+=1\n",
    "    \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_taken'])\n",
    "    \n",
    "    add_df['time_btn_tolls'] = add_df['time_taken'].diff()\n",
    "    add_df = add_df.fillna(0)\n",
    "    \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_taken'])\n",
    "    \n",
    "    add_test_df['time_btn_tolls'] = add_test_df['time_taken'].diff()\n",
    "    add_test_df = add_test_df.fillna(0)\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": 301,
   "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": 203,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df.to_csv('Ffinal_train_df_14Dec2017.csv', index=False)\n",
    "test_df.to_csv('Ffinal_test_df_14Dec2017.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 304,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'OR04M532M': datetime.datetime(2017, 11, 4, 12, 10, 12),\n",
       " 'OR04M53MM': datetime.datetime(2017, 10, 23, 1, 35, 49)}"
      ]
     },
     "execution_count": 304,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_last_ist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 305,
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_toll_num(df):\n",
    "    toll_dict = {}\n",
    "    new_toll_dict = {}\n",
    "    x = {}\n",
    "    num = 1\n",
    "    for toll in df.toll_both.unique().tolist():\n",
    "        t = df['time_taken'][df.toll_both == toll].median()\n",
    "        toll_dict[toll] = t\n",
    "    \n",
    "    for key, value in sorted(toll_dict.iteritems(), key=lambda (k,v): (v,k)):\n",
    "        x[key] = value\n",
    "        new_toll_dict[key] = num\n",
    "        num+=1\n",
    "        \n",
    "    return new_toll_dict, x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 306,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_toll_dict,x = find_toll_num(new_toll_time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 307,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Bellupada': 6,\n",
       " 'Bolapalli': 16,\n",
       " 'Budanam': 19,\n",
       " 'Chilakapalem': 9,\n",
       " 'Gaddurur': 21,\n",
       " 'Gudipada (Old Gangapada)': 4,\n",
       " 'Gurapalli': 5,\n",
       " 'Kalaparru': 14,\n",
       " 'Kaza': 15,\n",
       " 'Krishnavaram': 12,\n",
       " 'Laxmipuram': 7,\n",
       " 'Madapam': 8,\n",
       " 'Manguli': 3,\n",
       " 'Mulbagal': 20,\n",
       " 'Nathavalasa/ Vizianagaram': 10,\n",
       " 'Panikholi': 2,\n",
       " 'Pottipadu': 13,\n",
       " 'Sergrarh': 1,\n",
       " 'Sunambatti (Musunur)': 18,\n",
       " 'Tangutur': 17,\n",
       " 'Vempadu': 11}"
      ]
     },
     "execution_count": 307,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_toll_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 308,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Bellupada': 54.63638888884999,\n",
       " 'Bolapalli': 85.19555555560001,\n",
       " 'Budanam': 94.75180555554999,\n",
       " 'Chilakapalem': 60.240833333299996,\n",
       " 'Gaddurur': 111.7175,\n",
       " 'Gudipada (Old Gangapada)': 44.325833333350005,\n",
       " 'Gurapalli': 47.752222222200004,\n",
       " 'Kalaparru': 79.36486111114999,\n",
       " 'Kaza': 80.6586111111,\n",
       " 'Krishnavaram': 72.6825,\n",
       " 'Laxmipuram': 58.4108333333,\n",
       " 'Madapam': 58.9116666667,\n",
       " 'Manguli': 42.618888888899995,\n",
       " 'Mulbagal': 111.321388889,\n",
       " 'Nathavalasa/ Vizianagaram': 64.30319444445,\n",
       " 'Panikholi': 41.0441666667,\n",
       " 'Pottipadu': 78.3761111111,\n",
       " 'Sergrarh': 36.28625,\n",
       " 'Sunambatti (Musunur)': 89.71486111109999,\n",
       " 'Tangutur': 87.4901388889,\n",
       " 'Vempadu': 68.81222222219999}"
      ]
     },
     "execution_count": 308,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((3394, 10), (28, 10))"
      ]
     },
     "execution_count": 309,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.shape, test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "metadata": {},
   "outputs": [],
   "source": [
    "# test_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 311,
   "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": 312,
   "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": 313,
   "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": 314,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_vehicle_no_train_df = change_vehicle_no_to_dummy(train_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 315,
   "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": 316,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_df.vehicle_no = test_df.vehicle_no.replace(rev_dict_vehicle_no_test_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 317,
   "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": 318,
   "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>time_btn_tolls</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>56.155</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>44.325833</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>88.000</td>\n",
       "      <td>11</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>47.752222</td>\n",
       "      <td>3.426389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>83.835</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>54.636389</td>\n",
       "      <td>6.884167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>115.690</td>\n",
       "      <td>22</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>58.911667</td>\n",
       "      <td>4.275278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>27.020</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>60.240833</td>\n",
       "      <td>1.329167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>39.330</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>64.303194</td>\n",
       "      <td>4.062361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>11</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>127.950</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>68.812222</td>\n",
       "      <td>4.509028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>12</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>69.660</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>72.682500</td>\n",
       "      <td>3.870278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>14</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>144.380</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>79.364861</td>\n",
       "      <td>6.682361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>15</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>45.510</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>80.658611</td>\n",
       "      <td>1.293750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>16</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>82.215</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>85.195556</td>\n",
       "      <td>4.536944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>17</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>63.975</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>87.490139</td>\n",
       "      <td>2.294583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>18</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>62.070</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>89.714861</td>\n",
       "      <td>2.224722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>19</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>97.575</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>94.751806</td>\n",
       "      <td>5.036944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>21</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>217.330</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>111.717500</td>\n",
       "      <td>16.965694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>6</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>83.835</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>54.636389</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>8</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>115.690</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>58.911667</td>\n",
       "      <td>4.275278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>9</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>27.020</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>60.240833</td>\n",
       "      <td>1.329167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>10</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>39.330</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>64.303194</td>\n",
       "      <td>4.062361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>11</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>127.950</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>68.812222</td>\n",
       "      <td>4.509028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>12</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>69.660</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>72.682500</td>\n",
       "      <td>3.870278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>14</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>144.380</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>79.364861</td>\n",
       "      <td>6.682361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>15</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>45.510</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>80.658611</td>\n",
       "      <td>1.293750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>16</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>82.215</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>85.195556</td>\n",
       "      <td>4.536944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>17</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>63.975</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>87.490139</td>\n",
       "      <td>2.294583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>18</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>62.070</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>89.714861</td>\n",
       "      <td>2.224722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>19</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>97.575</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>94.751806</td>\n",
       "      <td>5.036944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>21</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>217.330</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>111.717500</td>\n",
       "      <td>16.965694</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           4         131               12              56.155              8   \n",
       "1           5         131               12              88.000             11   \n",
       "2           6         131               12              83.835             18   \n",
       "3           8         131               12             115.690             22   \n",
       "4           9         131               12              27.020              0   \n",
       "5          10         131               12              39.330              4   \n",
       "6          11         131               12             127.950              8   \n",
       "7          12         131               12              69.660             12   \n",
       "8          14         131               12             144.380             19   \n",
       "9          15         131               12              45.510             20   \n",
       "10         16         131               12              82.215              1   \n",
       "11         17         131               12              63.975              3   \n",
       "12         18         131               12              62.070              5   \n",
       "13         19         131               12              97.575             10   \n",
       "14         21         131               12             217.330              3   \n",
       "15          6         132               21              83.835              3   \n",
       "16          8         132               21             115.690              7   \n",
       "17          9         132               21              27.020              9   \n",
       "18         10         132               21              39.330             13   \n",
       "19         11         132               21             127.950             17   \n",
       "20         12         132               21              69.660             21   \n",
       "21         14         132               21             144.380              4   \n",
       "22         15         132               21              45.510              5   \n",
       "23         16         132               21              82.215             10   \n",
       "24         17         132               21              63.975             12   \n",
       "25         18         132               21              62.070             14   \n",
       "26         19         132               21              97.575             19   \n",
       "27         21         132               21             217.330             12   \n",
       "\n",
       "   day_of_travel month_of_travel days_from_now  time_taken  time_btn_tolls  \n",
       "0              6              11            41   44.325833        0.000000  \n",
       "1              6              11            41   47.752222        3.426389  \n",
       "2              6              11            41   54.636389        6.884167  \n",
       "3              6              11            41   58.911667        4.275278  \n",
       "4              0              11            41   60.240833        1.329167  \n",
       "5              0              11            41   64.303194        4.062361  \n",
       "6              0              11            41   68.812222        4.509028  \n",
       "7              0              11            41   72.682500        3.870278  \n",
       "8              0              11            41   79.364861        6.682361  \n",
       "9              0              11            41   80.658611        1.293750  \n",
       "10             1              11            41   85.195556        4.536944  \n",
       "11             1              11            41   87.490139        2.294583  \n",
       "12             1              11            41   89.714861        2.224722  \n",
       "13             1              11            41   94.751806        5.036944  \n",
       "14             2              11            41  111.717500       16.965694  \n",
       "15             1              10            53   54.636389        0.000000  \n",
       "16             1              10            53   58.911667        4.275278  \n",
       "17             1              10            53   60.240833        1.329167  \n",
       "18             1              10            53   64.303194        4.062361  \n",
       "19             1              10            53   68.812222        4.509028  \n",
       "20             1              10            53   72.682500        3.870278  \n",
       "21             2              10            53   79.364861        6.682361  \n",
       "22             2              10            53   80.658611        1.293750  \n",
       "23             2              10            53   85.195556        4.536944  \n",
       "24             2              10            53   87.490139        2.294583  \n",
       "25             2              10            53   89.714861        2.224722  \n",
       "26             2              10            53   94.751806        5.036944  \n",
       "27             3              10            53  111.717500       16.965694  "
      ]
     },
     "execution_count": 318,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 319,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df.drop(['time_taken'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_df.drop(['time_taken','time_btn_tolls'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 321,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_df_copy = test_df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 322,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_X = train_df.drop(['time_btn_tolls'], axis=1)\n",
    "train_y = train_df.time_btn_tolls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 323,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3394, 8)"
      ]
     },
     "execution_count": 323,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_X.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 324,
   "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": 325,
   "metadata": {},
   "outputs": [],
   "source": [
    "reg_mlp = MLPRegressor(hidden_layer_sizes=(1000,50), activation='logistic', alpha=8, solver='lbfgs', random_state=42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 326,
   "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": 327,
   "metadata": {},
   "outputs": [],
   "source": [
    "parameters = {\"C\": [1e1, 1e2, 1e3, 1e4],\n",
    "                               \"gamma\": np.logspace(-2, 2, 5)}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 328,
   "metadata": {},
   "outputs": [],
   "source": [
    "reg_svr = SVR(kernel='rbf', gamma=0.1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 329,
   "metadata": {},
   "outputs": [],
   "source": [
    "# grid = GridSearchCV(reg_svr, param_grid=parameters, n_jobs=-1,\n",
    "#                    scoring='r2',\n",
    "#                    verbose=1)\n",
    "grid = GridSearchCV(reg_svr, cv=5,\n",
    "                   param_grid=parameters, verbose=1, n_jobs=-1, scoring='r2')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fitting 5 folds for each of 20 candidates, totalling 100 fits\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Parallel(n_jobs=-1)]: Done  42 tasks      | elapsed:   13.7s\n",
      "[Parallel(n_jobs=-1)]: Done 100 out of 100 | elapsed: 10.1min finished\n"
     ]
    }
   ],
   "source": [
    "grid_result = grid.fit(X_train_scaled, y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 276,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Best parameters set found on development set:\n",
      "SVR(C=100.0, cache_size=200, coef0=0.0, degree=3, epsilon=0.1, gamma=1.0,\n",
      "  kernel='rbf', max_iter=-1, shrinking=True, tol=0.001, verbose=False)\n",
      "Grid scores on development set:\n",
      "0.194 (+/-0.014) for {'C': 10.0, 'gamma': 0.01}\n",
      "0.275 (+/-0.015) for {'C': 10.0, 'gamma': 0.10000000000000001}\n",
      "0.307 (+/-0.012) for {'C': 10.0, 'gamma': 1.0}\n",
      "0.202 (+/-0.017) for {'C': 10.0, 'gamma': 10.0}\n",
      "0.041 (+/-0.017) for {'C': 10.0, 'gamma': 100.0}\n",
      "0.269 (+/-0.015) for {'C': 100.0, 'gamma': 0.01}\n",
      "0.288 (+/-0.016) for {'C': 100.0, 'gamma': 0.10000000000000001}\n",
      "0.321 (+/-0.014) for {'C': 100.0, 'gamma': 1.0}\n",
      "-0.037 (+/-0.036) for {'C': 100.0, 'gamma': 10.0}\n",
      "0.057 (+/-0.029) for {'C': 100.0, 'gamma': 100.0}\n",
      "0.283 (+/-0.016) for {'C': 1000.0, 'gamma': 0.01}\n",
      "0.292 (+/-0.015) for {'C': 1000.0, 'gamma': 0.10000000000000001}\n",
      "0.263 (+/-0.020) for {'C': 1000.0, 'gamma': 1.0}\n",
      "-0.309 (+/-0.088) for {'C': 1000.0, 'gamma': 10.0}\n",
      "0.057 (+/-0.029) for {'C': 1000.0, 'gamma': 100.0}\n",
      "0.285 (+/-0.016) for {'C': 10000.0, 'gamma': 0.01}\n",
      "0.302 (+/-0.014) for {'C': 10000.0, 'gamma': 0.10000000000000001}\n",
      "-0.104 (+/-0.093) for {'C': 10000.0, 'gamma': 1.0}\n",
      "-0.352 (+/-0.094) for {'C': 10000.0, 'gamma': 10.0}\n",
      "0.057 (+/-0.029) for {'C': 10000.0, 'gamma': 100.0}\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/dist-packages/sklearn/model_selection/_search.py:761: DeprecationWarning:\n",
      "\n",
      "The grid_scores_ attribute was deprecated in version 0.18 in favor of the more elaborate cv_results_ attribute. The grid_scores_ attribute will not be available from 0.20\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(\"Best parameters set found on development set:\")\n",
    "print(grid_result.best_estimator_)\n",
    "print(\"Grid scores on development set:\")\n",
    "for parameters, mean_score, scores in grid_result.grid_scores_:\n",
    "    print(\"%0.3f (+/-%0.03f) for %r\"\n",
    "          % (mean_score, scores.std() / 2, parameters))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.063929028087408635"
      ]
     },
     "execution_count": 275,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grid_result.best_estimator_.fit(X_train_scaled, y_train)\n",
    "grid_result.best_estimator_.score(X_test_scaled, y_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "metadata": {},
   "outputs": [],
   "source": [
    "scores = cross_val_score(grid_result.best_estimator_, X_train_scaled, y_train, scoring='r2', n_jobs=-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 278,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Accuracy: 0.32 (+/- 0.06)\n"
     ]
    }
   ],
   "source": [
    "print(\"Accuracy: %0.2f (+/- %0.2f)\" % (scores.mean(), scores.std() * 2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 269,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.1986647608093981"
      ]
     },
     "execution_count": 269,
     "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": 270,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Accuracy: 0.35 (+/- 0.05)\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": 330,
   "metadata": {},
   "outputs": [],
   "source": [
    "scale = MinMaxScaler()\n",
    "X_predict_scaled = scale.fit_transform(test_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 331,
   "metadata": {},
   "outputs": [],
   "source": [
    "predictions = grid_result.best_estimator_.predict(X_predict_scaled)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 332,
   "metadata": {},
   "outputs": [],
   "source": [
    "show_df = test_df.copy()\n",
    "show_df['predicted'] = predictions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {},
   "outputs": [],
   "source": [
    "# show_df['predict_diff'] = show_df['predicted'].diff()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "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": 283,
   "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": 284,
   "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": 285,
   "metadata": {},
   "outputs": [],
   "source": [
    "# show_df['avg_pred_med'] = (show_df['predicted'] + show_df['med_time']) / 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 286,
   "metadata": {},
   "outputs": [],
   "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": 287,
   "metadata": {},
   "outputs": [],
   "source": [
    "# train_df[train_df['vehicle_no'] == 45]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "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": 289,
   "metadata": {},
   "outputs": [],
   "source": [
    "# show_df['test_pred'] = show_df['predicted'] + show_df.avg_speed_pred"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 333,
   "metadata": {},
   "outputs": [],
   "source": [
    "show_df['round_pred'] = show_df['predicted'].round()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "metadata": {},
   "outputs": [],
   "source": [
    "# show_df['median_input'] =  test_df_copy['time_taken']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "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": 348,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "131"
      ]
     },
     "execution_count": 348,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rev_dict_vehicle_no_test_df['OR04M532M']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 358,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dict_last_ist = {'OR04M532M': datetime.datetime(2017, 11, 4, 12, 10, 12),\n",
    "#  'OR04M53MM': datetime.datetime(2017, 10, 23, 1, 35, 49)}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 359,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['OR04M532M', 'OR04M53MM']"
      ]
     },
     "execution_count": 359,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# for num, truck in dict_vehicle_no_train_df.iteritems():\n",
    "list_truck = []\n",
    "for truck, ist in dict_last_ist.iteritems():\n",
    "    list_truck.append(truck)\n",
    "    \n",
    "for truck in list_truck:\n",
    "    i = rev_dict_vehicle_no_test_df[truck]\n",
    "    dict_last_ist[i] = dict_last_ist.pop(truck)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 362,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{131: datetime.datetime(2017, 11, 4, 12, 10, 12),\n",
       " 132: datetime.datetime(2017, 10, 23, 1, 35, 49)}"
      ]
     },
     "execution_count": 362,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_last_ist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 382,
   "metadata": {},
   "outputs": [],
   "source": [
    "show_df['eta'] = show_df['predicted']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 404,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2017, 11, 4, 21, 4, 12)"
      ]
     },
     "execution_count": 404,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = datetime.datetime(2017, 11, 4, 12, 10, 12)\n",
    "x + datetime.timedelta(hours=8.9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 406,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1970-01-01 00:00:00.000000001\n",
       "1    1970-01-01 00:00:00.000000004\n",
       "2    1970-01-01 00:00:00.000000004\n",
       "3    1970-01-01 00:00:00.000000007\n",
       "4    1970-01-01 00:00:00.000000001\n",
       "5    1970-01-01 00:00:00.000000001\n",
       "6    1970-01-01 00:00:00.000000008\n",
       "7    1970-01-01 00:00:00.000000004\n",
       "8    1970-01-01 00:00:00.000000010\n",
       "9    1970-01-01 00:00:00.000000001\n",
       "10   1970-01-01 00:00:00.000000006\n",
       "11   1970-01-01 00:00:00.000000004\n",
       "12   1970-01-01 00:00:00.000000005\n",
       "13   1970-01-01 00:00:00.000000010\n",
       "14   1970-01-01 00:00:00.000000020\n",
       "Name: eta, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 406,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "show_df['eta'][show_df['vehicle_no'] == 131]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 393,
   "metadata": {
    "scrolled": true
   },
   "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",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for truck, ist in dict_last_ist.iteritems():\n",
    "    show_df['time_taken'][show_df['vehicle_no'] == truck] = show_df['predicted'][show_df['vehicle_no'] == truck].cumsum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 407,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017-10-23 01:35:49\n",
      "2017-11-04 12:10:12\n"
     ]
    }
   ],
   "source": [
    "for truck, ist in dict_last_ist.iteritems():\n",
    "    print ist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 408,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_eta(ist, x):\n",
    "    y = ist + datetime.timedelta(hours=x)\n",
    "    return y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 415,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "toll_both                       int64\n",
       "vehicle_no                      int64\n",
       "loading_out_time               object\n",
       "distance_travelled            float64\n",
       "time_of_travel                 object\n",
       "day_of_travel                  object\n",
       "month_of_travel                object\n",
       "days_from_now                  object\n",
       "predicted             timedelta64[ns]\n",
       "round_pred                    float64\n",
       "eta                    datetime64[ns]\n",
       "time_taken                    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 415,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "show_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 421,
   "metadata": {
    "scrolled": true
   },
   "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",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for truck, ist in dict_last_ist.iteritems():\n",
    "    show_df['eta'][show_df['vehicle_no'] == truck] = ist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 422,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "unsupported type for timedelta hours component: Timedelta",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-422-bead448add33>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mshow_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'time_taken'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mshow_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'time_taken'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtimedelta\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mhours\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mx\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/usr/local/lib/python2.7/dist-packages/pandas/core/series.pyc\u001b[0m in \u001b[0;36mapply\u001b[0;34m(self, func, convert_dtype, args, **kwds)\u001b[0m\n\u001b[1;32m   2353\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2354\u001b[0m                 \u001b[0mvalues\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0masobject\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2355\u001b[0;31m                 \u001b[0mmapped\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mlib\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmap_infer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconvert\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mconvert_dtype\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2356\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2357\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmapped\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmapped\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mSeries\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/src/inference.pyx\u001b[0m in \u001b[0;36mpandas._libs.lib.map_infer (pandas/_libs/lib.c:66645)\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m<ipython-input-422-bead448add33>\u001b[0m in \u001b[0;36m<lambda>\u001b[0;34m(x)\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mshow_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'time_taken'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mshow_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'time_taken'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtimedelta\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mhours\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mx\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m: unsupported type for timedelta hours component: Timedelta"
     ]
    }
   ],
   "source": [
    "show_df['time_taken'] = show_df['time_taken'].apply(lambda x:datetime.timedelta(hours=x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 423,
   "metadata": {},
   "outputs": [],
   "source": [
    "show_df['eta'] = show_df['eta'] + show_df['time_taken']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 424,
   "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>round_pred</th>\n",
       "      <th>eta</th>\n",
       "      <th>time_taken</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>56.155</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>01:59:11.909486</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2017-11-04 14:09:23.909486</td>\n",
       "      <td>0 days 01:59:11.909486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>88.000</td>\n",
       "      <td>11</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>04:42:12.917975</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2017-11-04 18:51:36.827461</td>\n",
       "      <td>0 days 06:41:24.827461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>83.835</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>04:04:26.506094</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2017-11-04 22:56:03.333555</td>\n",
       "      <td>0 days 10:45:51.333555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>115.690</td>\n",
       "      <td>22</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>07:04:15.777586</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2017-11-05 06:00:19.111141</td>\n",
       "      <td>0 days 17:50:07.111141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>27.020</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>01:17:27.354341</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2017-11-05 07:17:46.465482</td>\n",
       "      <td>0 days 19:07:34.465482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>39.330</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>01:35:29.995618</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2017-11-05 08:53:16.461100</td>\n",
       "      <td>0 days 20:43:04.461100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>11</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>127.950</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>08:19:22.144004</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2017-11-05 17:12:38.605104</td>\n",
       "      <td>1 days 05:02:26.605104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>12</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>69.660</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>04:35:52.147158</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2017-11-05 21:48:30.752261</td>\n",
       "      <td>1 days 09:38:18.752261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>14</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>144.380</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>10:20:12.139589</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2017-11-06 08:08:42.891850</td>\n",
       "      <td>1 days 19:58:30.891850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>15</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>45.510</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>01:29:35.523012</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2017-11-06 09:38:18.414862</td>\n",
       "      <td>1 days 21:28:06.414862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>16</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>82.215</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>06:19:01.830613</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2017-11-06 15:57:20.245475</td>\n",
       "      <td>2 days 03:47:08.245475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>17</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>63.975</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>04:50:33.647812</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2017-11-06 20:47:53.893288</td>\n",
       "      <td>2 days 08:37:41.893288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>18</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>62.070</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>05:02:36.375146</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2017-11-07 01:50:30.268434</td>\n",
       "      <td>2 days 13:40:18.268434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>19</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>97.575</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>10:12:57.463096</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2017-11-07 12:03:27.731530</td>\n",
       "      <td>2 days 23:53:15.731530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>21</td>\n",
       "      <td>131</td>\n",
       "      <td>12</td>\n",
       "      <td>217.330</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>20:22:19.033664</td>\n",
       "      <td>20.0</td>\n",
       "      <td>2017-11-08 08:25:46.765195</td>\n",
       "      <td>3 days 20:15:34.765195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>6</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>83.835</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>05:54:21.858145</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2017-10-23 07:30:10.858145</td>\n",
       "      <td>0 days 05:54:21.858145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>8</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>115.690</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>10:15:50.203054</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2017-10-23 17:46:01.061199</td>\n",
       "      <td>0 days 16:10:12.061199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>9</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>27.020</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>02:30:51.834963</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2017-10-23 20:16:52.896162</td>\n",
       "      <td>0 days 18:41:03.896162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>10</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>39.330</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>03:02:44.739036</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2017-10-23 23:19:37.635198</td>\n",
       "      <td>0 days 21:43:48.635198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>11</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>127.950</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>10:54:31.119032</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2017-10-24 10:14:08.754231</td>\n",
       "      <td>1 days 08:38:19.754231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>12</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>69.660</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>03:16:02.652533</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2017-10-24 13:30:11.406764</td>\n",
       "      <td>1 days 11:54:22.406764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>14</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>144.380</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>13:46:11.730506</td>\n",
       "      <td>14.0</td>\n",
       "      <td>2017-10-25 03:16:23.137270</td>\n",
       "      <td>2 days 01:40:34.137270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>15</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>45.510</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>04:21:36.387404</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2017-10-25 07:37:59.524674</td>\n",
       "      <td>2 days 06:02:10.524674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>16</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>82.215</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>07:40:19.962522</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2017-10-25 15:18:19.487196</td>\n",
       "      <td>2 days 13:42:30.487196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>17</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>63.975</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>05:35:56.582141</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2017-10-25 20:54:16.069337</td>\n",
       "      <td>2 days 19:18:27.069337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>18</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>62.070</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>05:07:55.355956</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2017-10-26 02:02:11.425293</td>\n",
       "      <td>3 days 00:26:22.425293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>19</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>97.575</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>07:24:23.778290</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2017-10-26 09:26:35.203583</td>\n",
       "      <td>3 days 07:50:46.203583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>21</td>\n",
       "      <td>132</td>\n",
       "      <td>21</td>\n",
       "      <td>217.330</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>53</td>\n",
       "      <td>21:22:01.937351</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2017-10-27 06:48:37.140934</td>\n",
       "      <td>4 days 05:12:48.140934</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           4         131               12              56.155              8   \n",
       "1           5         131               12              88.000             11   \n",
       "2           6         131               12              83.835             18   \n",
       "3           8         131               12             115.690             22   \n",
       "4           9         131               12              27.020              0   \n",
       "5          10         131               12              39.330              4   \n",
       "6          11         131               12             127.950              8   \n",
       "7          12         131               12              69.660             12   \n",
       "8          14         131               12             144.380             19   \n",
       "9          15         131               12              45.510             20   \n",
       "10         16         131               12              82.215              1   \n",
       "11         17         131               12              63.975              3   \n",
       "12         18         131               12              62.070              5   \n",
       "13         19         131               12              97.575             10   \n",
       "14         21         131               12             217.330              3   \n",
       "15          6         132               21              83.835              3   \n",
       "16          8         132               21             115.690              7   \n",
       "17          9         132               21              27.020              9   \n",
       "18         10         132               21              39.330             13   \n",
       "19         11         132               21             127.950             17   \n",
       "20         12         132               21              69.660             21   \n",
       "21         14         132               21             144.380              4   \n",
       "22         15         132               21              45.510              5   \n",
       "23         16         132               21              82.215             10   \n",
       "24         17         132               21              63.975             12   \n",
       "25         18         132               21              62.070             14   \n",
       "26         19         132               21              97.575             19   \n",
       "27         21         132               21             217.330             12   \n",
       "\n",
       "   day_of_travel month_of_travel days_from_now       predicted  round_pred  \\\n",
       "0              6              11            41 01:59:11.909486         2.0   \n",
       "1              6              11            41 04:42:12.917975         5.0   \n",
       "2              6              11            41 04:04:26.506094         4.0   \n",
       "3              6              11            41 07:04:15.777586         7.0   \n",
       "4              0              11            41 01:17:27.354341         1.0   \n",
       "5              0              11            41 01:35:29.995618         2.0   \n",
       "6              0              11            41 08:19:22.144004         8.0   \n",
       "7              0              11            41 04:35:52.147158         5.0   \n",
       "8              0              11            41 10:20:12.139589        10.0   \n",
       "9              0              11            41 01:29:35.523012         1.0   \n",
       "10             1              11            41 06:19:01.830613         6.0   \n",
       "11             1              11            41 04:50:33.647812         5.0   \n",
       "12             1              11            41 05:02:36.375146         5.0   \n",
       "13             1              11            41 10:12:57.463096        10.0   \n",
       "14             2              11            41 20:22:19.033664        20.0   \n",
       "15             1              10            53 05:54:21.858145         6.0   \n",
       "16             1              10            53 10:15:50.203054        10.0   \n",
       "17             1              10            53 02:30:51.834963         3.0   \n",
       "18             1              10            53 03:02:44.739036         3.0   \n",
       "19             1              10            53 10:54:31.119032        11.0   \n",
       "20             1              10            53 03:16:02.652533         3.0   \n",
       "21             2              10            53 13:46:11.730506        14.0   \n",
       "22             2              10            53 04:21:36.387404         4.0   \n",
       "23             2              10            53 07:40:19.962522         8.0   \n",
       "24             2              10            53 05:35:56.582141         6.0   \n",
       "25             2              10            53 05:07:55.355956         5.0   \n",
       "26             2              10            53 07:24:23.778290         7.0   \n",
       "27             3              10            53 21:22:01.937351        21.0   \n",
       "\n",
       "                          eta             time_taken  \n",
       "0  2017-11-04 14:09:23.909486 0 days 01:59:11.909486  \n",
       "1  2017-11-04 18:51:36.827461 0 days 06:41:24.827461  \n",
       "2  2017-11-04 22:56:03.333555 0 days 10:45:51.333555  \n",
       "3  2017-11-05 06:00:19.111141 0 days 17:50:07.111141  \n",
       "4  2017-11-05 07:17:46.465482 0 days 19:07:34.465482  \n",
       "5  2017-11-05 08:53:16.461100 0 days 20:43:04.461100  \n",
       "6  2017-11-05 17:12:38.605104 1 days 05:02:26.605104  \n",
       "7  2017-11-05 21:48:30.752261 1 days 09:38:18.752261  \n",
       "8  2017-11-06 08:08:42.891850 1 days 19:58:30.891850  \n",
       "9  2017-11-06 09:38:18.414862 1 days 21:28:06.414862  \n",
       "10 2017-11-06 15:57:20.245475 2 days 03:47:08.245475  \n",
       "11 2017-11-06 20:47:53.893288 2 days 08:37:41.893288  \n",
       "12 2017-11-07 01:50:30.268434 2 days 13:40:18.268434  \n",
       "13 2017-11-07 12:03:27.731530 2 days 23:53:15.731530  \n",
       "14 2017-11-08 08:25:46.765195 3 days 20:15:34.765195  \n",
       "15 2017-10-23 07:30:10.858145 0 days 05:54:21.858145  \n",
       "16 2017-10-23 17:46:01.061199 0 days 16:10:12.061199  \n",
       "17 2017-10-23 20:16:52.896162 0 days 18:41:03.896162  \n",
       "18 2017-10-23 23:19:37.635198 0 days 21:43:48.635198  \n",
       "19 2017-10-24 10:14:08.754231 1 days 08:38:19.754231  \n",
       "20 2017-10-24 13:30:11.406764 1 days 11:54:22.406764  \n",
       "21 2017-10-25 03:16:23.137270 2 days 01:40:34.137270  \n",
       "22 2017-10-25 07:37:59.524674 2 days 06:02:10.524674  \n",
       "23 2017-10-25 15:18:19.487196 2 days 13:42:30.487196  \n",
       "24 2017-10-25 20:54:16.069337 2 days 19:18:27.069337  \n",
       "25 2017-10-26 02:02:11.425293 3 days 00:26:22.425293  \n",
       "26 2017-10-26 09:26:35.203583 3 days 07:50:46.203583  \n",
       "27 2017-10-27 06:48:37.140934 4 days 05:12:48.140934  "
      ]
     },
     "execution_count": 424,
     "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
}