Delhivery.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xBi8gq1Ohu1r"
      },
      "source": [
        "# Objective\n",
        "\n",
        "##### Delhivery Team wants to process the data from data engineering pipeline to,\n",
        "*   get useful features out of raw fields\n",
        "*   make sense out of the data to enable data science team to build forecasting models\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9EJj5jr7h5n1"
      },
      "source": [
        "#### Importing requied libraries"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 78,
      "metadata": {
        "id": "d_1JepeWhiI-"
      },
      "outputs": [],
      "source": [
        "# Import libraries\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "import seaborn as sns\n",
        "import matplotlib.pyplot as plt\n",
        "from scipy import stats\n",
        "from scipy.stats import shapiro\n",
        "from datetime import datetime\n",
        "import warnings\n",
        "warnings.filterwarnings('ignore')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ApBTS8UskCIZ"
      },
      "source": [
        "#### Read the Data"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "t-IhaKF-z0fM"
      },
      "source": [
        "1. Transform the data human readable  - cleaning, aggregation, missing value treatment\n",
        "2. EDA\n",
        "3. Make the data understandable by Machine (Pre-Processing) - Normalization, Standardization\n",
        "4. Modelling"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 79,
      "metadata": {
        "id": "OWj-hu1jhtsp"
      },
      "outputs": [],
      "source": [
        "# Read the dataset\n",
        "dvery_df = pd.read_csv(\"https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/001/551/original/delhivery_data.csv?1642751181\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "iV5NF5xjkGoJ"
      },
      "source": [
        "#### Metadata of Delhivery Data"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 80,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 522
        },
        "id": "nsRPWCuFjWxY",
        "outputId": "fa5b9801-ab9d-4199-c3cb-9edb179185f3"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-5f9cc389-9ac7-41e0-aeba-f4afeccc3e0d\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>data</th>\n",
              "      <th>trip_creation_time</th>\n",
              "      <th>route_schedule_uuid</th>\n",
              "      <th>route_type</th>\n",
              "      <th>trip_uuid</th>\n",
              "      <th>source_center</th>\n",
              "      <th>source_name</th>\n",
              "      <th>destination_center</th>\n",
              "      <th>destination_name</th>\n",
              "      <th>od_start_time</th>\n",
              "      <th>...</th>\n",
              "      <th>cutoff_timestamp</th>\n",
              "      <th>actual_distance_to_destination</th>\n",
              "      <th>actual_time</th>\n",
              "      <th>osrm_time</th>\n",
              "      <th>osrm_distance</th>\n",
              "      <th>factor</th>\n",
              "      <th>segment_actual_time</th>\n",
              "      <th>segment_osrm_time</th>\n",
              "      <th>segment_osrm_distance</th>\n",
              "      <th>segment_factor</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>count</th>\n",
              "      <td>144867</td>\n",
              "      <td>144867</td>\n",
              "      <td>144867</td>\n",
              "      <td>144867</td>\n",
              "      <td>144867</td>\n",
              "      <td>144867</td>\n",
              "      <td>144574</td>\n",
              "      <td>144867</td>\n",
              "      <td>144606</td>\n",
              "      <td>144867</td>\n",
              "      <td>...</td>\n",
              "      <td>144867</td>\n",
              "      <td>144867.000000</td>\n",
              "      <td>144867.000000</td>\n",
              "      <td>144867.000000</td>\n",
              "      <td>144867.000000</td>\n",
              "      <td>144867.000000</td>\n",
              "      <td>144867.000000</td>\n",
              "      <td>144867.000000</td>\n",
              "      <td>144867.00000</td>\n",
              "      <td>144867.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>unique</th>\n",
              "      <td>2</td>\n",
              "      <td>14817</td>\n",
              "      <td>1504</td>\n",
              "      <td>2</td>\n",
              "      <td>14817</td>\n",
              "      <td>1508</td>\n",
              "      <td>1498</td>\n",
              "      <td>1481</td>\n",
              "      <td>1468</td>\n",
              "      <td>26369</td>\n",
              "      <td>...</td>\n",
              "      <td>93180</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>top</th>\n",
              "      <td>training</td>\n",
              "      <td>2018-09-28 05:23:15.359220</td>\n",
              "      <td>thanos::sroute:4029a8a2-6c74-4b7e-a6d8-f9e069f...</td>\n",
              "      <td>FTL</td>\n",
              "      <td>trip-153811219535896559</td>\n",
              "      <td>IND000000ACB</td>\n",
              "      <td>Gurgaon_Bilaspur_HB (Haryana)</td>\n",
              "      <td>IND000000ACB</td>\n",
              "      <td>Gurgaon_Bilaspur_HB (Haryana)</td>\n",
              "      <td>2018-09-21 18:37:09.322207</td>\n",
              "      <td>...</td>\n",
              "      <td>2018-09-24 05:19:20</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>freq</th>\n",
              "      <td>104858</td>\n",
              "      <td>101</td>\n",
              "      <td>1812</td>\n",
              "      <td>99660</td>\n",
              "      <td>101</td>\n",
              "      <td>23347</td>\n",
              "      <td>23347</td>\n",
              "      <td>15192</td>\n",
              "      <td>15192</td>\n",
              "      <td>81</td>\n",
              "      <td>...</td>\n",
              "      <td>40</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>mean</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>234.073372</td>\n",
              "      <td>416.927527</td>\n",
              "      <td>213.868272</td>\n",
              "      <td>284.771297</td>\n",
              "      <td>2.120107</td>\n",
              "      <td>36.196111</td>\n",
              "      <td>18.507548</td>\n",
              "      <td>22.82902</td>\n",
              "      <td>2.218368</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>std</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>344.990009</td>\n",
              "      <td>598.103621</td>\n",
              "      <td>308.011085</td>\n",
              "      <td>421.119294</td>\n",
              "      <td>1.715421</td>\n",
              "      <td>53.571158</td>\n",
              "      <td>14.775960</td>\n",
              "      <td>17.86066</td>\n",
              "      <td>4.847530</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>min</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>9.000045</td>\n",
              "      <td>9.000000</td>\n",
              "      <td>6.000000</td>\n",
              "      <td>9.008200</td>\n",
              "      <td>0.144000</td>\n",
              "      <td>-244.000000</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>0.00000</td>\n",
              "      <td>-23.444444</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25%</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>23.355874</td>\n",
              "      <td>51.000000</td>\n",
              "      <td>27.000000</td>\n",
              "      <td>29.914700</td>\n",
              "      <td>1.604264</td>\n",
              "      <td>20.000000</td>\n",
              "      <td>11.000000</td>\n",
              "      <td>12.07010</td>\n",
              "      <td>1.347826</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50%</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>66.126571</td>\n",
              "      <td>132.000000</td>\n",
              "      <td>64.000000</td>\n",
              "      <td>78.525800</td>\n",
              "      <td>1.857143</td>\n",
              "      <td>29.000000</td>\n",
              "      <td>17.000000</td>\n",
              "      <td>23.51300</td>\n",
              "      <td>1.684211</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>75%</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>286.708875</td>\n",
              "      <td>513.000000</td>\n",
              "      <td>257.000000</td>\n",
              "      <td>343.193250</td>\n",
              "      <td>2.213483</td>\n",
              "      <td>40.000000</td>\n",
              "      <td>22.000000</td>\n",
              "      <td>27.81325</td>\n",
              "      <td>2.250000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>max</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1927.447705</td>\n",
              "      <td>4532.000000</td>\n",
              "      <td>1686.000000</td>\n",
              "      <td>2326.199100</td>\n",
              "      <td>77.387097</td>\n",
              "      <td>3051.000000</td>\n",
              "      <td>1611.000000</td>\n",
              "      <td>2191.40370</td>\n",
              "      <td>574.250000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>11 rows × 24 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-5f9cc389-9ac7-41e0-aeba-f4afeccc3e0d')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-5f9cc389-9ac7-41e0-aeba-f4afeccc3e0d button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-5f9cc389-9ac7-41e0-aeba-f4afeccc3e0d');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "            data          trip_creation_time  \\\n",
              "count     144867                      144867   \n",
              "unique         2                       14817   \n",
              "top     training  2018-09-28 05:23:15.359220   \n",
              "freq      104858                         101   \n",
              "mean         NaN                         NaN   \n",
              "std          NaN                         NaN   \n",
              "min          NaN                         NaN   \n",
              "25%          NaN                         NaN   \n",
              "50%          NaN                         NaN   \n",
              "75%          NaN                         NaN   \n",
              "max          NaN                         NaN   \n",
              "\n",
              "                                      route_schedule_uuid route_type  \\\n",
              "count                                              144867     144867   \n",
              "unique                                               1504          2   \n",
              "top     thanos::sroute:4029a8a2-6c74-4b7e-a6d8-f9e069f...        FTL   \n",
              "freq                                                 1812      99660   \n",
              "mean                                                  NaN        NaN   \n",
              "std                                                   NaN        NaN   \n",
              "min                                                   NaN        NaN   \n",
              "25%                                                   NaN        NaN   \n",
              "50%                                                   NaN        NaN   \n",
              "75%                                                   NaN        NaN   \n",
              "max                                                   NaN        NaN   \n",
              "\n",
              "                      trip_uuid source_center                    source_name  \\\n",
              "count                    144867        144867                         144574   \n",
              "unique                    14817          1508                           1498   \n",
              "top     trip-153811219535896559  IND000000ACB  Gurgaon_Bilaspur_HB (Haryana)   \n",
              "freq                        101         23347                          23347   \n",
              "mean                        NaN           NaN                            NaN   \n",
              "std                         NaN           NaN                            NaN   \n",
              "min                         NaN           NaN                            NaN   \n",
              "25%                         NaN           NaN                            NaN   \n",
              "50%                         NaN           NaN                            NaN   \n",
              "75%                         NaN           NaN                            NaN   \n",
              "max                         NaN           NaN                            NaN   \n",
              "\n",
              "       destination_center               destination_name  \\\n",
              "count              144867                         144606   \n",
              "unique               1481                           1468   \n",
              "top          IND000000ACB  Gurgaon_Bilaspur_HB (Haryana)   \n",
              "freq                15192                          15192   \n",
              "mean                  NaN                            NaN   \n",
              "std                   NaN                            NaN   \n",
              "min                   NaN                            NaN   \n",
              "25%                   NaN                            NaN   \n",
              "50%                   NaN                            NaN   \n",
              "75%                   NaN                            NaN   \n",
              "max                   NaN                            NaN   \n",
              "\n",
              "                     od_start_time  ...     cutoff_timestamp  \\\n",
              "count                       144867  ...               144867   \n",
              "unique                       26369  ...                93180   \n",
              "top     2018-09-21 18:37:09.322207  ...  2018-09-24 05:19:20   \n",
              "freq                            81  ...                   40   \n",
              "mean                           NaN  ...                  NaN   \n",
              "std                            NaN  ...                  NaN   \n",
              "min                            NaN  ...                  NaN   \n",
              "25%                            NaN  ...                  NaN   \n",
              "50%                            NaN  ...                  NaN   \n",
              "75%                            NaN  ...                  NaN   \n",
              "max                            NaN  ...                  NaN   \n",
              "\n",
              "        actual_distance_to_destination    actual_time      osrm_time  \\\n",
              "count                    144867.000000  144867.000000  144867.000000   \n",
              "unique                             NaN            NaN            NaN   \n",
              "top                                NaN            NaN            NaN   \n",
              "freq                               NaN            NaN            NaN   \n",
              "mean                        234.073372     416.927527     213.868272   \n",
              "std                         344.990009     598.103621     308.011085   \n",
              "min                           9.000045       9.000000       6.000000   \n",
              "25%                          23.355874      51.000000      27.000000   \n",
              "50%                          66.126571     132.000000      64.000000   \n",
              "75%                         286.708875     513.000000     257.000000   \n",
              "max                        1927.447705    4532.000000    1686.000000   \n",
              "\n",
              "        osrm_distance         factor  segment_actual_time  segment_osrm_time  \\\n",
              "count   144867.000000  144867.000000        144867.000000      144867.000000   \n",
              "unique            NaN            NaN                  NaN                NaN   \n",
              "top               NaN            NaN                  NaN                NaN   \n",
              "freq              NaN            NaN                  NaN                NaN   \n",
              "mean       284.771297       2.120107            36.196111          18.507548   \n",
              "std        421.119294       1.715421            53.571158          14.775960   \n",
              "min          9.008200       0.144000          -244.000000           0.000000   \n",
              "25%         29.914700       1.604264            20.000000          11.000000   \n",
              "50%         78.525800       1.857143            29.000000          17.000000   \n",
              "75%        343.193250       2.213483            40.000000          22.000000   \n",
              "max       2326.199100      77.387097          3051.000000        1611.000000   \n",
              "\n",
              "        segment_osrm_distance  segment_factor  \n",
              "count            144867.00000   144867.000000  \n",
              "unique                    NaN             NaN  \n",
              "top                       NaN             NaN  \n",
              "freq                      NaN             NaN  \n",
              "mean                 22.82902        2.218368  \n",
              "std                  17.86066        4.847530  \n",
              "min                   0.00000      -23.444444  \n",
              "25%                  12.07010        1.347826  \n",
              "50%                  23.51300        1.684211  \n",
              "75%                  27.81325        2.250000  \n",
              "max                2191.40370      574.250000  \n",
              "\n",
              "[11 rows x 24 columns]"
            ]
          },
          "execution_count": 80,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Description of the dataset\n",
        "dvery_df.describe(include='all')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 83,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 455
        },
        "id": "nH3fDxFlv_EB",
        "outputId": "cd320115-89a6-4b0c-f7e5-548441558f0b"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-bf6d04cd-c40d-4886-932c-705d2dccdf83\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th>od_start_time</th>\n",
              "      <th>actual_time</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>trip_uuid</th>\n",
              "      <th>source_center</th>\n",
              "      <th>destination_center</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th rowspan=\"2\" valign=\"top\">trip-153671041653548748</th>\n",
              "      <th>IND209304AAA</th>\n",
              "      <th>IND000000ACB</th>\n",
              "      <td>2018-09-12 16:39:46.8584692018-09-12 16:39:46....</td>\n",
              "      <td>732.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>IND462022AAA</th>\n",
              "      <th>IND209304AAA</th>\n",
              "      <td>2018-09-12 00:00:16.5357412018-09-12 00:00:16....</td>\n",
              "      <td>830.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th rowspan=\"2\" valign=\"top\">trip-153671042288605164</th>\n",
              "      <th>IND561203AAB</th>\n",
              "      <th>IND562101AAA</th>\n",
              "      <td>2018-09-12 02:03:09.6555912018-09-12 02:03:09....</td>\n",
              "      <td>47.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>IND572101AAA</th>\n",
              "      <th>IND561203AAB</th>\n",
              "      <td>2018-09-12 00:00:22.8864302018-09-12 00:00:22....</td>\n",
              "      <td>96.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>trip-153671043369099517</th>\n",
              "      <th>IND000000ACB</th>\n",
              "      <th>IND160002AAC</th>\n",
              "      <td>2018-09-14 03:40:17.1067332018-09-14 03:40:17....</td>\n",
              "      <td>611.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <th>...</th>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th rowspan=\"3\" valign=\"top\">trip-153861115439069069</th>\n",
              "      <th>IND628204AAA</th>\n",
              "      <th>IND627657AAA</th>\n",
              "      <td>2018-10-04 02:29:04.2721942018-10-04 02:29:04....</td>\n",
              "      <td>51.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>IND628613AAA</th>\n",
              "      <th>IND627005AAA</th>\n",
              "      <td>2018-10-04 04:16:39.8948722018-10-04 04:16:39....</td>\n",
              "      <td>90.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>IND628801AAA</th>\n",
              "      <th>IND628204AAA</th>\n",
              "      <td>2018-10-04 01:44:53.8080002018-10-04 01:44:53....</td>\n",
              "      <td>30.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th rowspan=\"2\" valign=\"top\">trip-153861118270144424</th>\n",
              "      <th>IND583119AAA</th>\n",
              "      <th>IND583101AAA</th>\n",
              "      <td>2018-10-04 03:58:40.7265472018-10-04 03:58:40....</td>\n",
              "      <td>233.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>IND583201AAA</th>\n",
              "      <th>IND583119AAA</th>\n",
              "      <td>2018-10-04 02:51:44.7126562018-10-04 02:51:44....</td>\n",
              "      <td>42.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>26368 rows × 2 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-bf6d04cd-c40d-4886-932c-705d2dccdf83')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-bf6d04cd-c40d-4886-932c-705d2dccdf83 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-bf6d04cd-c40d-4886-932c-705d2dccdf83');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "                                                                                              od_start_time  \\\n",
              "trip_uuid               source_center destination_center                                                      \n",
              "trip-153671041653548748 IND209304AAA  IND000000ACB        2018-09-12 16:39:46.8584692018-09-12 16:39:46....   \n",
              "                        IND462022AAA  IND209304AAA        2018-09-12 00:00:16.5357412018-09-12 00:00:16....   \n",
              "trip-153671042288605164 IND561203AAB  IND562101AAA        2018-09-12 02:03:09.6555912018-09-12 02:03:09....   \n",
              "                        IND572101AAA  IND561203AAB        2018-09-12 00:00:22.8864302018-09-12 00:00:22....   \n",
              "trip-153671043369099517 IND000000ACB  IND160002AAC        2018-09-14 03:40:17.1067332018-09-14 03:40:17....   \n",
              "...                                                                                                     ...   \n",
              "trip-153861115439069069 IND628204AAA  IND627657AAA        2018-10-04 02:29:04.2721942018-10-04 02:29:04....   \n",
              "                        IND628613AAA  IND627005AAA        2018-10-04 04:16:39.8948722018-10-04 04:16:39....   \n",
              "                        IND628801AAA  IND628204AAA        2018-10-04 01:44:53.8080002018-10-04 01:44:53....   \n",
              "trip-153861118270144424 IND583119AAA  IND583101AAA        2018-10-04 03:58:40.7265472018-10-04 03:58:40....   \n",
              "                        IND583201AAA  IND583119AAA        2018-10-04 02:51:44.7126562018-10-04 02:51:44....   \n",
              "\n",
              "                                                          actual_time  \n",
              "trip_uuid               source_center destination_center               \n",
              "trip-153671041653548748 IND209304AAA  IND000000ACB              732.0  \n",
              "                        IND462022AAA  IND209304AAA              830.0  \n",
              "trip-153671042288605164 IND561203AAB  IND562101AAA               47.0  \n",
              "                        IND572101AAA  IND561203AAB               96.0  \n",
              "trip-153671043369099517 IND000000ACB  IND160002AAC              611.0  \n",
              "...                                                               ...  \n",
              "trip-153861115439069069 IND628204AAA  IND627657AAA               51.0  \n",
              "                        IND628613AAA  IND627005AAA               90.0  \n",
              "                        IND628801AAA  IND628204AAA               30.0  \n",
              "trip-153861118270144424 IND583119AAA  IND583101AAA              233.0  \n",
              "                        IND583201AAA  IND583119AAA               42.0  \n",
              "\n",
              "[26368 rows x 2 columns]"
            ]
          },
          "execution_count": 83,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "temp_merge = dvery_df.groupby(['trip_uuid', 'source_center', 'destination_center']).agg({'od_start_time' : 'sum', 'actual_time': 'last'})\n",
        "\n",
        "temp_merge"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "einZbSj9xJXw"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "code",
      "execution_count": 42,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "f49SOZPNjlCm",
        "outputId": "e97c933c-ad75-4087-adb8-351cf10ca8e8"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "<bound method DataFrame.info of             data          trip_creation_time  \\\n",
              "0       training  2018-09-20 02:35:36.476840   \n",
              "1       training  2018-09-20 02:35:36.476840   \n",
              "2       training  2018-09-20 02:35:36.476840   \n",
              "3       training  2018-09-20 02:35:36.476840   \n",
              "4       training  2018-09-20 02:35:36.476840   \n",
              "...          ...                         ...   \n",
              "144862  training  2018-09-20 16:24:28.436231   \n",
              "144863  training  2018-09-20 16:24:28.436231   \n",
              "144864  training  2018-09-20 16:24:28.436231   \n",
              "144865  training  2018-09-20 16:24:28.436231   \n",
              "144866  training  2018-09-20 16:24:28.436231   \n",
              "\n",
              "                                      route_schedule_uuid route_type  \\\n",
              "0       thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "1       thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "2       thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "3       thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "4       thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "...                                                   ...        ...   \n",
              "144862  thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...    Carting   \n",
              "144863  thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...    Carting   \n",
              "144864  thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...    Carting   \n",
              "144865  thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...    Carting   \n",
              "144866  thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...    Carting   \n",
              "\n",
              "                      trip_uuid source_center                 source_name  \\\n",
              "0       trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "1       trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "2       trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "3       trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "4       trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "...                         ...           ...                         ...   \n",
              "144862  trip-153746066843555182  IND131028AAB  Sonipat_Kundli_H (Haryana)   \n",
              "144863  trip-153746066843555182  IND131028AAB  Sonipat_Kundli_H (Haryana)   \n",
              "144864  trip-153746066843555182  IND131028AAB  Sonipat_Kundli_H (Haryana)   \n",
              "144865  trip-153746066843555182  IND131028AAB  Sonipat_Kundli_H (Haryana)   \n",
              "144866  trip-153746066843555182  IND131028AAB  Sonipat_Kundli_H (Haryana)   \n",
              "\n",
              "       destination_center               destination_name  \\\n",
              "0            IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "1            IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "2            IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "3            IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "4            IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "...                   ...                            ...   \n",
              "144862       IND000000ACB  Gurgaon_Bilaspur_HB (Haryana)   \n",
              "144863       IND000000ACB  Gurgaon_Bilaspur_HB (Haryana)   \n",
              "144864       IND000000ACB  Gurgaon_Bilaspur_HB (Haryana)   \n",
              "144865       IND000000ACB  Gurgaon_Bilaspur_HB (Haryana)   \n",
              "144866       IND000000ACB  Gurgaon_Bilaspur_HB (Haryana)   \n",
              "\n",
              "                     od_start_time  ...            cutoff_timestamp  \\\n",
              "0       2018-09-20 03:21:32.418600  ...         2018-09-20 04:27:55   \n",
              "1       2018-09-20 03:21:32.418600  ...         2018-09-20 04:17:55   \n",
              "2       2018-09-20 03:21:32.418600  ...  2018-09-20 04:01:19.505586   \n",
              "3       2018-09-20 03:21:32.418600  ...         2018-09-20 03:39:57   \n",
              "4       2018-09-20 03:21:32.418600  ...         2018-09-20 03:33:55   \n",
              "...                            ...  ...                         ...   \n",
              "144862  2018-09-20 16:24:28.436231  ...         2018-09-20 21:57:20   \n",
              "144863  2018-09-20 16:24:28.436231  ...         2018-09-20 21:31:18   \n",
              "144864  2018-09-20 16:24:28.436231  ...         2018-09-20 21:11:18   \n",
              "144865  2018-09-20 16:24:28.436231  ...         2018-09-20 20:53:19   \n",
              "144866  2018-09-20 16:24:28.436231  ...  2018-09-20 16:24:28.436231   \n",
              "\n",
              "        actual_distance_to_destination  actual_time  osrm_time osrm_distance  \\\n",
              "0                            10.435660         14.0       11.0       11.9653   \n",
              "1                            18.936842         24.0       20.0       21.7243   \n",
              "2                            27.637279         40.0       28.0       32.5395   \n",
              "3                            36.118028         62.0       40.0       45.5620   \n",
              "4                            39.386040         68.0       44.0       54.2181   \n",
              "...                                ...          ...        ...           ...   \n",
              "144862                       45.258278         94.0       60.0       67.9280   \n",
              "144863                       54.092531        120.0       76.0       85.6829   \n",
              "144864                       66.163591        140.0       88.0       97.0933   \n",
              "144865                       73.680667        158.0       98.0      111.2709   \n",
              "144866                       70.039010        426.0       95.0       88.7319   \n",
              "\n",
              "          factor  segment_actual_time  segment_osrm_time  \\\n",
              "0       1.272727                 14.0               11.0   \n",
              "1       1.200000                 10.0                9.0   \n",
              "2       1.428571                 16.0                7.0   \n",
              "3       1.550000                 21.0               12.0   \n",
              "4       1.545455                  6.0                5.0   \n",
              "...          ...                  ...                ...   \n",
              "144862  1.566667                 12.0               12.0   \n",
              "144863  1.578947                 26.0               21.0   \n",
              "144864  1.590909                 20.0               34.0   \n",
              "144865  1.612245                 17.0               27.0   \n",
              "144866  4.484211                268.0                9.0   \n",
              "\n",
              "        segment_osrm_distance  segment_factor  \n",
              "0                     11.9653        1.272727  \n",
              "1                      9.7590        1.111111  \n",
              "2                     10.8152        2.285714  \n",
              "3                     13.0224        1.750000  \n",
              "4                      3.9153        1.200000  \n",
              "...                       ...             ...  \n",
              "144862                 8.1858        1.000000  \n",
              "144863                17.3725        1.238095  \n",
              "144864                20.7053        0.588235  \n",
              "144865                18.8885        0.629630  \n",
              "144866                 8.8088       29.777778  \n",
              "\n",
              "[144867 rows x 24 columns]>"
            ]
          },
          "execution_count": 42,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Meta data of the dataset\n",
        "dvery_df.info"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 43,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 473
        },
        "id": "tENdOSd2jPnD",
        "outputId": "6df1cc0d-71f5-43c5-8154-67ff4725c8cc"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-a74f5d2e-3bb8-47a2-bd10-c7c0f3ef3f12\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>data</th>\n",
              "      <th>trip_creation_time</th>\n",
              "      <th>route_schedule_uuid</th>\n",
              "      <th>route_type</th>\n",
              "      <th>trip_uuid</th>\n",
              "      <th>source_center</th>\n",
              "      <th>source_name</th>\n",
              "      <th>destination_center</th>\n",
              "      <th>destination_name</th>\n",
              "      <th>od_start_time</th>\n",
              "      <th>...</th>\n",
              "      <th>cutoff_timestamp</th>\n",
              "      <th>actual_distance_to_destination</th>\n",
              "      <th>actual_time</th>\n",
              "      <th>osrm_time</th>\n",
              "      <th>osrm_distance</th>\n",
              "      <th>factor</th>\n",
              "      <th>segment_actual_time</th>\n",
              "      <th>segment_osrm_time</th>\n",
              "      <th>segment_osrm_distance</th>\n",
              "      <th>segment_factor</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>training</td>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>...</td>\n",
              "      <td>2018-09-20 04:27:55</td>\n",
              "      <td>10.435660</td>\n",
              "      <td>14.0</td>\n",
              "      <td>11.0</td>\n",
              "      <td>11.9653</td>\n",
              "      <td>1.272727</td>\n",
              "      <td>14.0</td>\n",
              "      <td>11.0</td>\n",
              "      <td>11.9653</td>\n",
              "      <td>1.272727</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>training</td>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>...</td>\n",
              "      <td>2018-09-20 04:17:55</td>\n",
              "      <td>18.936842</td>\n",
              "      <td>24.0</td>\n",
              "      <td>20.0</td>\n",
              "      <td>21.7243</td>\n",
              "      <td>1.200000</td>\n",
              "      <td>10.0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>9.7590</td>\n",
              "      <td>1.111111</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>training</td>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>...</td>\n",
              "      <td>2018-09-20 04:01:19.505586</td>\n",
              "      <td>27.637279</td>\n",
              "      <td>40.0</td>\n",
              "      <td>28.0</td>\n",
              "      <td>32.5395</td>\n",
              "      <td>1.428571</td>\n",
              "      <td>16.0</td>\n",
              "      <td>7.0</td>\n",
              "      <td>10.8152</td>\n",
              "      <td>2.285714</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>training</td>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>...</td>\n",
              "      <td>2018-09-20 03:39:57</td>\n",
              "      <td>36.118028</td>\n",
              "      <td>62.0</td>\n",
              "      <td>40.0</td>\n",
              "      <td>45.5620</td>\n",
              "      <td>1.550000</td>\n",
              "      <td>21.0</td>\n",
              "      <td>12.0</td>\n",
              "      <td>13.0224</td>\n",
              "      <td>1.750000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>training</td>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>...</td>\n",
              "      <td>2018-09-20 03:33:55</td>\n",
              "      <td>39.386040</td>\n",
              "      <td>68.0</td>\n",
              "      <td>44.0</td>\n",
              "      <td>54.2181</td>\n",
              "      <td>1.545455</td>\n",
              "      <td>6.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>3.9153</td>\n",
              "      <td>1.200000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 24 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-a74f5d2e-3bb8-47a2-bd10-c7c0f3ef3f12')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-a74f5d2e-3bb8-47a2-bd10-c7c0f3ef3f12 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-a74f5d2e-3bb8-47a2-bd10-c7c0f3ef3f12');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "       data          trip_creation_time  \\\n",
              "0  training  2018-09-20 02:35:36.476840   \n",
              "1  training  2018-09-20 02:35:36.476840   \n",
              "2  training  2018-09-20 02:35:36.476840   \n",
              "3  training  2018-09-20 02:35:36.476840   \n",
              "4  training  2018-09-20 02:35:36.476840   \n",
              "\n",
              "                                 route_schedule_uuid route_type  \\\n",
              "0  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "1  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "2  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "3  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "4  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "\n",
              "                 trip_uuid source_center                 source_name  \\\n",
              "0  trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "1  trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "2  trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "3  trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "4  trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "\n",
              "  destination_center               destination_name  \\\n",
              "0       IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "1       IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "2       IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "3       IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "4       IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "\n",
              "                od_start_time  ...            cutoff_timestamp  \\\n",
              "0  2018-09-20 03:21:32.418600  ...         2018-09-20 04:27:55   \n",
              "1  2018-09-20 03:21:32.418600  ...         2018-09-20 04:17:55   \n",
              "2  2018-09-20 03:21:32.418600  ...  2018-09-20 04:01:19.505586   \n",
              "3  2018-09-20 03:21:32.418600  ...         2018-09-20 03:39:57   \n",
              "4  2018-09-20 03:21:32.418600  ...         2018-09-20 03:33:55   \n",
              "\n",
              "   actual_distance_to_destination  actual_time  osrm_time osrm_distance  \\\n",
              "0                       10.435660         14.0       11.0       11.9653   \n",
              "1                       18.936842         24.0       20.0       21.7243   \n",
              "2                       27.637279         40.0       28.0       32.5395   \n",
              "3                       36.118028         62.0       40.0       45.5620   \n",
              "4                       39.386040         68.0       44.0       54.2181   \n",
              "\n",
              "     factor  segment_actual_time  segment_osrm_time  segment_osrm_distance  \\\n",
              "0  1.272727                 14.0               11.0                11.9653   \n",
              "1  1.200000                 10.0                9.0                 9.7590   \n",
              "2  1.428571                 16.0                7.0                10.8152   \n",
              "3  1.550000                 21.0               12.0                13.0224   \n",
              "4  1.545455                  6.0                5.0                 3.9153   \n",
              "\n",
              "   segment_factor  \n",
              "0        1.272727  \n",
              "1        1.111111  \n",
              "2        2.285714  \n",
              "3        1.750000  \n",
              "4        1.200000  \n",
              "\n",
              "[5 rows x 24 columns]"
            ]
          },
          "execution_count": 43,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Top 5 rows of the dataset\n",
        "dvery_df.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 91,
      "metadata": {
        "id": "0-O7ufEx1uFC"
      },
      "outputs": [],
      "source": [
        "import re"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 90,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "3EOjR3ZK0Vhs",
        "outputId": "b1ca6dd5-ffbe-4be9-cdd3-afce2929c094"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-024dd2ef-40d9-42ae-a78b-7ea85f85351e\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>0</th>\n",
              "      <th>1</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Anand_VUNagar_DC</td>\n",
              "      <td>(Gujarat)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Anand_VUNagar_DC</td>\n",
              "      <td>(Gujarat)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Anand_VUNagar_DC</td>\n",
              "      <td>(Gujarat)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Anand_VUNagar_DC</td>\n",
              "      <td>(Gujarat)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Anand_VUNagar_DC</td>\n",
              "      <td>(Gujarat)</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-024dd2ef-40d9-42ae-a78b-7ea85f85351e')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-024dd2ef-40d9-42ae-a78b-7ea85f85351e button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-024dd2ef-40d9-42ae-a78b-7ea85f85351e');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "                  0          1\n",
              "0  Anand_VUNagar_DC  (Gujarat)\n",
              "1  Anand_VUNagar_DC  (Gujarat)\n",
              "2  Anand_VUNagar_DC  (Gujarat)\n",
              "3  Anand_VUNagar_DC  (Gujarat)\n",
              "4  Anand_VUNagar_DC  (Gujarat)"
            ]
          },
          "execution_count": 90,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "state = dvery_df['source_name'].str.split(' ', n=1, expand=True)\n",
        "state[1]\n",
        "state.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 95,
      "metadata": {
        "id": "7VNY_tK1Cov5"
      },
      "outputs": [],
      "source": [
        "def reg_replace(arr,regex): \n",
        "    temp = [] \n",
        "    for i in arr: \n",
        "      temp.append(regex.findall(i)[0]) \n",
        "      return temp \n",
        "dvery_df['Source_State'] = pd.Series(reg_replace(dvery_df.source_name.str.split(' ',n=1,expand=True)[1],re.compile('(?<=\\()(.*?)(?=\\))')))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 101,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0OU2QMkNDAfr",
        "outputId": "a3e4df74-1fb2-4646-c602-6bf25e35119c"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "data                                   2\n",
              "trip_creation_time                 14817\n",
              "route_schedule_uuid                 1504\n",
              "route_type                             2\n",
              "trip_uuid                          14817\n",
              "source_center                       1508\n",
              "source_name                         1498\n",
              "destination_center                  1481\n",
              "destination_name                    1468\n",
              "od_start_time                      26369\n",
              "od_end_time                        26369\n",
              "start_scan_to_end_scan              1915\n",
              "is_cutoff                              2\n",
              "cutoff_factor                        501\n",
              "cutoff_timestamp                   93180\n",
              "actual_distance_to_destination    144515\n",
              "actual_time                         3182\n",
              "osrm_time                           1531\n",
              "osrm_distance                     138046\n",
              "factor                             45641\n",
              "segment_actual_time                  747\n",
              "segment_osrm_time                    214\n",
              "segment_osrm_distance             113799\n",
              "segment_factor                      5675\n",
              "Source_State                           1\n",
              "dtype: int64"
            ]
          },
          "execution_count": 101,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dvery_df.nunique()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 44,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "y7APOqb2jS0z",
        "outputId": "35e2aeb5-b9bc-4a8f-ebf1-06e830c33ca5"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "Index(['data', 'trip_creation_time', 'route_schedule_uuid', 'route_type',\n",
              "       'trip_uuid', 'source_center', 'source_name', 'destination_center',\n",
              "       'destination_name', 'od_start_time', 'od_end_time',\n",
              "       'start_scan_to_end_scan', 'is_cutoff', 'cutoff_factor',\n",
              "       'cutoff_timestamp', 'actual_distance_to_destination', 'actual_time',\n",
              "       'osrm_time', 'osrm_distance', 'factor', 'segment_actual_time',\n",
              "       'segment_osrm_time', 'segment_osrm_distance', 'segment_factor'],\n",
              "      dtype='object')"
            ]
          },
          "execution_count": 44,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# List of columns in the dataset\n",
        "dvery_df.columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 45,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "npqxxfV9odu_",
        "outputId": "0ce7a0d1-37d8-42f4-fe38-01964a046777"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "data                               object\n",
              "trip_creation_time                 object\n",
              "route_schedule_uuid                object\n",
              "route_type                         object\n",
              "trip_uuid                          object\n",
              "source_center                      object\n",
              "source_name                        object\n",
              "destination_center                 object\n",
              "destination_name                   object\n",
              "od_start_time                      object\n",
              "od_end_time                        object\n",
              "start_scan_to_end_scan            float64\n",
              "is_cutoff                            bool\n",
              "cutoff_factor                       int64\n",
              "cutoff_timestamp                   object\n",
              "actual_distance_to_destination    float64\n",
              "actual_time                       float64\n",
              "osrm_time                         float64\n",
              "osrm_distance                     float64\n",
              "factor                            float64\n",
              "segment_actual_time               float64\n",
              "segment_osrm_time                 float64\n",
              "segment_osrm_distance             float64\n",
              "segment_factor                    float64\n",
              "dtype: object"
            ]
          },
          "execution_count": 45,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Datatypes of the columns\n",
        "dvery_df.dtypes"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 46,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "XHKaoFdLjsqd",
        "outputId": "229bdf73-978f-4149-ce45-07ab5c9b2b86"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Delhivery dataset has 144867 rows and 24 columns\n"
          ]
        }
      ],
      "source": [
        "# Shape of the dataset\n",
        "dvery_df.shape\n",
        "print('Delhivery dataset has {} rows and {} columns'.format(dvery_df.shape[0], dvery_df.shape[1]))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 47,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GtCnXezQov_h",
        "outputId": "50306687-3f4b-421c-b8a2-4e54ac8bbb42"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "3476808"
            ]
          },
          "execution_count": 47,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Size of the dataset\n",
        "dvery_df.size"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fJhiyMTplt3m"
      },
      "source": [
        "##### **Observation**: *Delhivery dataset has 1.4 lakhs plus records and 24 columns*"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UYmKactumNhG"
      },
      "source": [
        "# Data Pre-Processing\n",
        "\n",
        "##### 1. Check for Missing Values\n",
        "##### 2. Exploring Numerical values\n",
        "##### 3. Exploreing Categorical Values"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 48,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rQxugFqxjv7r",
        "outputId": "194b52db-0756-4255-9331-0c0675407353"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "data                                0\n",
              "trip_creation_time                  0\n",
              "route_schedule_uuid                 0\n",
              "route_type                          0\n",
              "trip_uuid                           0\n",
              "source_center                       0\n",
              "source_name                       293\n",
              "destination_center                  0\n",
              "destination_name                  261\n",
              "od_start_time                       0\n",
              "od_end_time                         0\n",
              "start_scan_to_end_scan              0\n",
              "is_cutoff                           0\n",
              "cutoff_factor                       0\n",
              "cutoff_timestamp                    0\n",
              "actual_distance_to_destination      0\n",
              "actual_time                         0\n",
              "osrm_time                           0\n",
              "osrm_distance                       0\n",
              "factor                              0\n",
              "segment_actual_time                 0\n",
              "segment_osrm_time                   0\n",
              "segment_osrm_distance               0\n",
              "segment_factor                      0\n",
              "dtype: int64"
            ]
          },
          "execution_count": 48,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dvery_df.isnull().sum()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 49,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Ab9_mfePmvYE",
        "outputId": "308f4170-aaab-414e-d342-fd1e30a4ccf0"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "['source_name', 'destination_name']"
            ]
          },
          "execution_count": 49,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "[features for features in dvery_df.columns if dvery_df[features].isnull().sum()>0]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "t_SC4l9gSjal"
      },
      "source": [
        "##### **Observation**: *Source name & Destination name have lot of null values*"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 50,
      "metadata": {
        "id": "1PxijNihTcNY"
      },
      "outputs": [],
      "source": [
        "# Take a working copy of the given dataset\n",
        "work_df = dvery_df.copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 51,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "RJwYMiy5pcM7",
        "outputId": "92bf7cd7-0a6a-4bd6-9678-d3710a579b81"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "3476808"
            ]
          },
          "execution_count": 51,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "work_df.size"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 52,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8gXndVdypd2m",
        "outputId": "a0268ae9-c180-4a1e-821e-6841e81f2194"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "(144867, 24)"
            ]
          },
          "execution_count": 52,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "work_df.shape"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 53,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "sLn3F2cKpzzS",
        "outputId": "4e94bce6-549c-4623-e903-221e822d11e2"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "data                              0.00\n",
            "trip_creation_time                0.00\n",
            "route_schedule_uuid               0.00\n",
            "route_type                        0.00\n",
            "trip_uuid                         0.00\n",
            "source_center                     0.00\n",
            "source_name                       0.20\n",
            "destination_center                0.00\n",
            "destination_name                  0.18\n",
            "od_start_time                     0.00\n",
            "od_end_time                       0.00\n",
            "start_scan_to_end_scan            0.00\n",
            "is_cutoff                         0.00\n",
            "cutoff_factor                     0.00\n",
            "cutoff_timestamp                  0.00\n",
            "actual_distance_to_destination    0.00\n",
            "actual_time                       0.00\n",
            "osrm_time                         0.00\n",
            "osrm_distance                     0.00\n",
            "factor                            0.00\n",
            "segment_actual_time               0.00\n",
            "segment_osrm_time                 0.00\n",
            "segment_osrm_distance             0.00\n",
            "segment_factor                    0.00\n",
            "dtype: float64\n"
          ]
        }
      ],
      "source": [
        "# Null values Count & Percentage\n",
        "work_df.isnull().sum()\n",
        "print(round(work_df.isnull().sum()/work_df.shape[0]*100,2))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ctthMmH7rjmU"
      },
      "source": [
        "##### **Observation**: *Source name has 2% null value & Destination name has 0.18% null values*"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 54,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "RCK43JnipgC7",
        "outputId": "7e1d726e-e3fc-4a8e-bc1f-542d9e5e8d4d"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "554"
            ]
          },
          "execution_count": 54,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Total number of missing values from the entire dataset\n",
        "work_df.isnull().sum().sum()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 55,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6Zx_QlPfpni-",
        "outputId": "c67d9eaf-2cb8-4c98-b552-f364461007b2"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Columns with missing values and the missing values Percentage: \n",
            "\n",
            "source_name : 0.2%\n",
            "destination_name : 0.18%\n"
          ]
        }
      ],
      "source": [
        "def missing_values_per_column():\n",
        "  col_count = 0\n",
        "  print(\"Columns with missing values and the missing values Percentage: \\n\")\n",
        "  for col in work_df.columns:\n",
        "    count = work_df[work_df[col].isna()].shape[0]\n",
        "    if count>0:\n",
        "      print(col + ' : ' + str(round((count/work_df.shape[0])*100, 2))+ '%')\n",
        "      col_count = col_count + 1\n",
        "  if col_count == 0:\n",
        "    print(\"There are no missing values in any rows\")\n",
        "  \n",
        "missing_values_per_column()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 56,
      "metadata": {
        "id": "uliHUAJKs88F"
      },
      "outputs": [],
      "source": [
        "# Missing Values Treatment. As the % of null values are relatively low, let's drop them. \n",
        "work_df.dropna(inplace=True)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 57,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8yIBqRnpUgDh",
        "outputId": "17fb17c2-1904-4a2a-a4f3-6393ed379f32"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Columns with missing values and the missing values Percentage: \n",
            "\n",
            "There are no missing values in any rows\n"
          ]
        }
      ],
      "source": [
        "# Re-validating missing values post deletion\n",
        "missing_values_per_column()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 58,
      "metadata": {
        "id": "wkzLKZj7Vw8B"
      },
      "outputs": [],
      "source": [
        "# Removing some of the non-significant columns\n",
        "work_df.drop(columns=['is_cutoff', 'cutoff_factor', 'cutoff_timestamp', 'factor', 'segment_factor'],inplace=True)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 59,
      "metadata": {
        "id": "aSEJwpvTRhJu"
      },
      "outputs": [],
      "source": [
        "# Converting few fields into right datatypes. For eg: trip_creation_time, od_start_time, od_end_time with date and time as values but have datatype as object\n",
        "work_df['trip_creation_time'] = pd.to_datetime(work_df['trip_creation_time'])\n",
        "work_df['od_start_time'] = pd.to_datetime(work_df['od_start_time'])\n",
        "work_df['od_end_time'] = pd.to_datetime(work_df['od_end_time'])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 60,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "uYZ9zxLsUFpZ",
        "outputId": "3702a4eb-177c-44ce-a041-a670afb7d0f7"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 144316 entries, 0 to 144866\n",
            "Data columns (total 19 columns):\n",
            " #   Column                          Non-Null Count   Dtype         \n",
            "---  ------                          --------------   -----         \n",
            " 0   data                            144316 non-null  object        \n",
            " 1   trip_creation_time              144316 non-null  datetime64[ns]\n",
            " 2   route_schedule_uuid             144316 non-null  object        \n",
            " 3   route_type                      144316 non-null  object        \n",
            " 4   trip_uuid                       144316 non-null  object        \n",
            " 5   source_center                   144316 non-null  object        \n",
            " 6   source_name                     144316 non-null  object        \n",
            " 7   destination_center              144316 non-null  object        \n",
            " 8   destination_name                144316 non-null  object        \n",
            " 9   od_start_time                   144316 non-null  datetime64[ns]\n",
            " 10  od_end_time                     144316 non-null  datetime64[ns]\n",
            " 11  start_scan_to_end_scan          144316 non-null  float64       \n",
            " 12  actual_distance_to_destination  144316 non-null  float64       \n",
            " 13  actual_time                     144316 non-null  float64       \n",
            " 14  osrm_time                       144316 non-null  float64       \n",
            " 15  osrm_distance                   144316 non-null  float64       \n",
            " 16  segment_actual_time             144316 non-null  float64       \n",
            " 17  segment_osrm_time               144316 non-null  float64       \n",
            " 18  segment_osrm_distance           144316 non-null  float64       \n",
            "dtypes: datetime64[ns](3), float64(8), object(8)\n",
            "memory usage: 22.0+ MB\n"
          ]
        }
      ],
      "source": [
        "work_df.info()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 61,
      "metadata": {
        "id": "8GlmqvZbqQvd"
      },
      "outputs": [],
      "source": [
        "work_df['od_time_diff'] = (work_df['od_end_time']-work_df['od_start_time']).dt.total_seconds()/60"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 62,
      "metadata": {
        "id": "eKgePqR1BQry"
      },
      "outputs": [],
      "source": [
        "# Segregating Categorical and Numerical Features\n",
        "cat_features = [x for x in work_df.columns if work_df[x].dtype == 'object'] \n",
        "num_features = [y for y in work_df.columns if work_df[y].dtype not in ('object','datetime64[ns]')]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 63,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "id": "Y38BU88_qwy7",
        "outputId": "a8707f8c-2f06-4c3b-e9b8-4365c997fc56"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-2a7eba31-b7a6-4a84-8537-993945b48279\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>data</th>\n",
              "      <th>route_schedule_uuid</th>\n",
              "      <th>route_type</th>\n",
              "      <th>trip_uuid</th>\n",
              "      <th>source_center</th>\n",
              "      <th>source_name</th>\n",
              "      <th>destination_center</th>\n",
              "      <th>destination_name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>training</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>training</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>training</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>training</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>training</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144862</th>\n",
              "      <td>training</td>\n",
              "      <td>thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153746066843555182</td>\n",
              "      <td>IND131028AAB</td>\n",
              "      <td>Sonipat_Kundli_H (Haryana)</td>\n",
              "      <td>IND000000ACB</td>\n",
              "      <td>Gurgaon_Bilaspur_HB (Haryana)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144863</th>\n",
              "      <td>training</td>\n",
              "      <td>thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153746066843555182</td>\n",
              "      <td>IND131028AAB</td>\n",
              "      <td>Sonipat_Kundli_H (Haryana)</td>\n",
              "      <td>IND000000ACB</td>\n",
              "      <td>Gurgaon_Bilaspur_HB (Haryana)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144864</th>\n",
              "      <td>training</td>\n",
              "      <td>thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153746066843555182</td>\n",
              "      <td>IND131028AAB</td>\n",
              "      <td>Sonipat_Kundli_H (Haryana)</td>\n",
              "      <td>IND000000ACB</td>\n",
              "      <td>Gurgaon_Bilaspur_HB (Haryana)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144865</th>\n",
              "      <td>training</td>\n",
              "      <td>thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153746066843555182</td>\n",
              "      <td>IND131028AAB</td>\n",
              "      <td>Sonipat_Kundli_H (Haryana)</td>\n",
              "      <td>IND000000ACB</td>\n",
              "      <td>Gurgaon_Bilaspur_HB (Haryana)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144866</th>\n",
              "      <td>training</td>\n",
              "      <td>thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153746066843555182</td>\n",
              "      <td>IND131028AAB</td>\n",
              "      <td>Sonipat_Kundli_H (Haryana)</td>\n",
              "      <td>IND000000ACB</td>\n",
              "      <td>Gurgaon_Bilaspur_HB (Haryana)</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>144316 rows × 8 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-2a7eba31-b7a6-4a84-8537-993945b48279')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-2a7eba31-b7a6-4a84-8537-993945b48279 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-2a7eba31-b7a6-4a84-8537-993945b48279');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "            data                                route_schedule_uuid  \\\n",
              "0       training  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...   \n",
              "1       training  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...   \n",
              "2       training  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...   \n",
              "3       training  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...   \n",
              "4       training  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...   \n",
              "...          ...                                                ...   \n",
              "144862  training  thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...   \n",
              "144863  training  thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...   \n",
              "144864  training  thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...   \n",
              "144865  training  thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...   \n",
              "144866  training  thanos::sroute:f0569d2f-4e20-4c31-8542-67b86d5...   \n",
              "\n",
              "       route_type                trip_uuid source_center  \\\n",
              "0         Carting  trip-153741093647649320  IND388121AAA   \n",
              "1         Carting  trip-153741093647649320  IND388121AAA   \n",
              "2         Carting  trip-153741093647649320  IND388121AAA   \n",
              "3         Carting  trip-153741093647649320  IND388121AAA   \n",
              "4         Carting  trip-153741093647649320  IND388121AAA   \n",
              "...           ...                      ...           ...   \n",
              "144862    Carting  trip-153746066843555182  IND131028AAB   \n",
              "144863    Carting  trip-153746066843555182  IND131028AAB   \n",
              "144864    Carting  trip-153746066843555182  IND131028AAB   \n",
              "144865    Carting  trip-153746066843555182  IND131028AAB   \n",
              "144866    Carting  trip-153746066843555182  IND131028AAB   \n",
              "\n",
              "                       source_name destination_center  \\\n",
              "0       Anand_VUNagar_DC (Gujarat)       IND388620AAB   \n",
              "1       Anand_VUNagar_DC (Gujarat)       IND388620AAB   \n",
              "2       Anand_VUNagar_DC (Gujarat)       IND388620AAB   \n",
              "3       Anand_VUNagar_DC (Gujarat)       IND388620AAB   \n",
              "4       Anand_VUNagar_DC (Gujarat)       IND388620AAB   \n",
              "...                            ...                ...   \n",
              "144862  Sonipat_Kundli_H (Haryana)       IND000000ACB   \n",
              "144863  Sonipat_Kundli_H (Haryana)       IND000000ACB   \n",
              "144864  Sonipat_Kundli_H (Haryana)       IND000000ACB   \n",
              "144865  Sonipat_Kundli_H (Haryana)       IND000000ACB   \n",
              "144866  Sonipat_Kundli_H (Haryana)       IND000000ACB   \n",
              "\n",
              "                     destination_name  \n",
              "0       Khambhat_MotvdDPP_D (Gujarat)  \n",
              "1       Khambhat_MotvdDPP_D (Gujarat)  \n",
              "2       Khambhat_MotvdDPP_D (Gujarat)  \n",
              "3       Khambhat_MotvdDPP_D (Gujarat)  \n",
              "4       Khambhat_MotvdDPP_D (Gujarat)  \n",
              "...                               ...  \n",
              "144862  Gurgaon_Bilaspur_HB (Haryana)  \n",
              "144863  Gurgaon_Bilaspur_HB (Haryana)  \n",
              "144864  Gurgaon_Bilaspur_HB (Haryana)  \n",
              "144865  Gurgaon_Bilaspur_HB (Haryana)  \n",
              "144866  Gurgaon_Bilaspur_HB (Haryana)  \n",
              "\n",
              "[144316 rows x 8 columns]"
            ]
          },
          "execution_count": 63,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "work_df.select_dtypes(\"object\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 64,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "id": "BI6Z8a9_rBTs",
        "outputId": "ef78051d-6c2c-4be8-918c-68fa95db3847"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-2eb2c523-a761-4b81-9bfd-5cc62cf757bd\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>start_scan_to_end_scan</th>\n",
              "      <th>actual_distance_to_destination</th>\n",
              "      <th>actual_time</th>\n",
              "      <th>osrm_time</th>\n",
              "      <th>osrm_distance</th>\n",
              "      <th>segment_actual_time</th>\n",
              "      <th>segment_osrm_time</th>\n",
              "      <th>segment_osrm_distance</th>\n",
              "      <th>od_time_diff</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>86.0</td>\n",
              "      <td>10.435660</td>\n",
              "      <td>14.0</td>\n",
              "      <td>11.0</td>\n",
              "      <td>11.9653</td>\n",
              "      <td>14.0</td>\n",
              "      <td>11.0</td>\n",
              "      <td>11.9653</td>\n",
              "      <td>86.213637</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>86.0</td>\n",
              "      <td>18.936842</td>\n",
              "      <td>24.0</td>\n",
              "      <td>20.0</td>\n",
              "      <td>21.7243</td>\n",
              "      <td>10.0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>9.7590</td>\n",
              "      <td>86.213637</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>86.0</td>\n",
              "      <td>27.637279</td>\n",
              "      <td>40.0</td>\n",
              "      <td>28.0</td>\n",
              "      <td>32.5395</td>\n",
              "      <td>16.0</td>\n",
              "      <td>7.0</td>\n",
              "      <td>10.8152</td>\n",
              "      <td>86.213637</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>86.0</td>\n",
              "      <td>36.118028</td>\n",
              "      <td>62.0</td>\n",
              "      <td>40.0</td>\n",
              "      <td>45.5620</td>\n",
              "      <td>21.0</td>\n",
              "      <td>12.0</td>\n",
              "      <td>13.0224</td>\n",
              "      <td>86.213637</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>86.0</td>\n",
              "      <td>39.386040</td>\n",
              "      <td>68.0</td>\n",
              "      <td>44.0</td>\n",
              "      <td>54.2181</td>\n",
              "      <td>6.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>3.9153</td>\n",
              "      <td>86.213637</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144862</th>\n",
              "      <td>427.0</td>\n",
              "      <td>45.258278</td>\n",
              "      <td>94.0</td>\n",
              "      <td>60.0</td>\n",
              "      <td>67.9280</td>\n",
              "      <td>12.0</td>\n",
              "      <td>12.0</td>\n",
              "      <td>8.1858</td>\n",
              "      <td>427.686364</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144863</th>\n",
              "      <td>427.0</td>\n",
              "      <td>54.092531</td>\n",
              "      <td>120.0</td>\n",
              "      <td>76.0</td>\n",
              "      <td>85.6829</td>\n",
              "      <td>26.0</td>\n",
              "      <td>21.0</td>\n",
              "      <td>17.3725</td>\n",
              "      <td>427.686364</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144864</th>\n",
              "      <td>427.0</td>\n",
              "      <td>66.163591</td>\n",
              "      <td>140.0</td>\n",
              "      <td>88.0</td>\n",
              "      <td>97.0933</td>\n",
              "      <td>20.0</td>\n",
              "      <td>34.0</td>\n",
              "      <td>20.7053</td>\n",
              "      <td>427.686364</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144865</th>\n",
              "      <td>427.0</td>\n",
              "      <td>73.680667</td>\n",
              "      <td>158.0</td>\n",
              "      <td>98.0</td>\n",
              "      <td>111.2709</td>\n",
              "      <td>17.0</td>\n",
              "      <td>27.0</td>\n",
              "      <td>18.8885</td>\n",
              "      <td>427.686364</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144866</th>\n",
              "      <td>427.0</td>\n",
              "      <td>70.039010</td>\n",
              "      <td>426.0</td>\n",
              "      <td>95.0</td>\n",
              "      <td>88.7319</td>\n",
              "      <td>268.0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>8.8088</td>\n",
              "      <td>427.686364</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>144316 rows × 9 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-2eb2c523-a761-4b81-9bfd-5cc62cf757bd')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-2eb2c523-a761-4b81-9bfd-5cc62cf757bd button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-2eb2c523-a761-4b81-9bfd-5cc62cf757bd');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "        start_scan_to_end_scan  actual_distance_to_destination  actual_time  \\\n",
              "0                         86.0                       10.435660         14.0   \n",
              "1                         86.0                       18.936842         24.0   \n",
              "2                         86.0                       27.637279         40.0   \n",
              "3                         86.0                       36.118028         62.0   \n",
              "4                         86.0                       39.386040         68.0   \n",
              "...                        ...                             ...          ...   \n",
              "144862                   427.0                       45.258278         94.0   \n",
              "144863                   427.0                       54.092531        120.0   \n",
              "144864                   427.0                       66.163591        140.0   \n",
              "144865                   427.0                       73.680667        158.0   \n",
              "144866                   427.0                       70.039010        426.0   \n",
              "\n",
              "        osrm_time  osrm_distance  segment_actual_time  segment_osrm_time  \\\n",
              "0            11.0        11.9653                 14.0               11.0   \n",
              "1            20.0        21.7243                 10.0                9.0   \n",
              "2            28.0        32.5395                 16.0                7.0   \n",
              "3            40.0        45.5620                 21.0               12.0   \n",
              "4            44.0        54.2181                  6.0                5.0   \n",
              "...           ...            ...                  ...                ...   \n",
              "144862       60.0        67.9280                 12.0               12.0   \n",
              "144863       76.0        85.6829                 26.0               21.0   \n",
              "144864       88.0        97.0933                 20.0               34.0   \n",
              "144865       98.0       111.2709                 17.0               27.0   \n",
              "144866       95.0        88.7319                268.0                9.0   \n",
              "\n",
              "        segment_osrm_distance  od_time_diff  \n",
              "0                     11.9653     86.213637  \n",
              "1                      9.7590     86.213637  \n",
              "2                     10.8152     86.213637  \n",
              "3                     13.0224     86.213637  \n",
              "4                      3.9153     86.213637  \n",
              "...                       ...           ...  \n",
              "144862                 8.1858    427.686364  \n",
              "144863                17.3725    427.686364  \n",
              "144864                20.7053    427.686364  \n",
              "144865                18.8885    427.686364  \n",
              "144866                 8.8088    427.686364  \n",
              "\n",
              "[144316 rows x 9 columns]"
            ]
          },
          "execution_count": 64,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "work_df.select_dtypes('number')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 65,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "id": "HakONPpFrLqB",
        "outputId": "48d8d285-09dc-4ee5-dee9-97c87a4dd5bd"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-9a43ad1f-b3b3-4d69-9d6c-810ca51680f6\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>trip_creation_time</th>\n",
              "      <th>od_start_time</th>\n",
              "      <th>od_end_time</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>2018-09-20 04:47:45.236797</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>2018-09-20 04:47:45.236797</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>2018-09-20 04:47:45.236797</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>2018-09-20 04:47:45.236797</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>2018-09-20 04:47:45.236797</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144862</th>\n",
              "      <td>2018-09-20 16:24:28.436231</td>\n",
              "      <td>2018-09-20 16:24:28.436231</td>\n",
              "      <td>2018-09-20 23:32:09.618069</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144863</th>\n",
              "      <td>2018-09-20 16:24:28.436231</td>\n",
              "      <td>2018-09-20 16:24:28.436231</td>\n",
              "      <td>2018-09-20 23:32:09.618069</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144864</th>\n",
              "      <td>2018-09-20 16:24:28.436231</td>\n",
              "      <td>2018-09-20 16:24:28.436231</td>\n",
              "      <td>2018-09-20 23:32:09.618069</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144865</th>\n",
              "      <td>2018-09-20 16:24:28.436231</td>\n",
              "      <td>2018-09-20 16:24:28.436231</td>\n",
              "      <td>2018-09-20 23:32:09.618069</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>144866</th>\n",
              "      <td>2018-09-20 16:24:28.436231</td>\n",
              "      <td>2018-09-20 16:24:28.436231</td>\n",
              "      <td>2018-09-20 23:32:09.618069</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>144316 rows × 3 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-9a43ad1f-b3b3-4d69-9d6c-810ca51680f6')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-9a43ad1f-b3b3-4d69-9d6c-810ca51680f6 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-9a43ad1f-b3b3-4d69-9d6c-810ca51680f6');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "               trip_creation_time              od_start_time  \\\n",
              "0      2018-09-20 02:35:36.476840 2018-09-20 03:21:32.418600   \n",
              "1      2018-09-20 02:35:36.476840 2018-09-20 03:21:32.418600   \n",
              "2      2018-09-20 02:35:36.476840 2018-09-20 03:21:32.418600   \n",
              "3      2018-09-20 02:35:36.476840 2018-09-20 03:21:32.418600   \n",
              "4      2018-09-20 02:35:36.476840 2018-09-20 03:21:32.418600   \n",
              "...                           ...                        ...   \n",
              "144862 2018-09-20 16:24:28.436231 2018-09-20 16:24:28.436231   \n",
              "144863 2018-09-20 16:24:28.436231 2018-09-20 16:24:28.436231   \n",
              "144864 2018-09-20 16:24:28.436231 2018-09-20 16:24:28.436231   \n",
              "144865 2018-09-20 16:24:28.436231 2018-09-20 16:24:28.436231   \n",
              "144866 2018-09-20 16:24:28.436231 2018-09-20 16:24:28.436231   \n",
              "\n",
              "                      od_end_time  \n",
              "0      2018-09-20 04:47:45.236797  \n",
              "1      2018-09-20 04:47:45.236797  \n",
              "2      2018-09-20 04:47:45.236797  \n",
              "3      2018-09-20 04:47:45.236797  \n",
              "4      2018-09-20 04:47:45.236797  \n",
              "...                           ...  \n",
              "144862 2018-09-20 23:32:09.618069  \n",
              "144863 2018-09-20 23:32:09.618069  \n",
              "144864 2018-09-20 23:32:09.618069  \n",
              "144865 2018-09-20 23:32:09.618069  \n",
              "144866 2018-09-20 23:32:09.618069  \n",
              "\n",
              "[144316 rows x 3 columns]"
            ]
          },
          "execution_count": 65,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "work_df.select_dtypes('datetime')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 66,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "lExu6hZRBfKU",
        "outputId": "524da2aa-2f4f-4a5a-c5e5-e58ed0887b5d"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "['data',\n",
              " 'route_schedule_uuid',\n",
              " 'route_type',\n",
              " 'trip_uuid',\n",
              " 'source_center',\n",
              " 'source_name',\n",
              " 'destination_center',\n",
              " 'destination_name']"
            ]
          },
          "execution_count": 66,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "cat_features"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 67,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8YaNmASABjGx",
        "outputId": "edc7bc6f-a252-4525-dd54-bb79875586c5"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "['start_scan_to_end_scan',\n",
              " 'actual_distance_to_destination',\n",
              " 'actual_time',\n",
              " 'osrm_time',\n",
              " 'osrm_distance',\n",
              " 'segment_actual_time',\n",
              " 'segment_osrm_time',\n",
              " 'segment_osrm_distance',\n",
              " 'od_time_diff']"
            ]
          },
          "execution_count": 67,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "num_features"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P8d7CARAachQ"
      },
      "source": [
        "#### Extract & Build new Features\n",
        "###### Destination Name, Source Name and Trip Creation Time"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 68,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "K9OeJbHzrpK8",
        "outputId": "37c535a4-09be-4b42-e027-5e4d05ffd328"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "0    [Khambhat, MotvdDPP, D (Gujarat)]\n",
              "1    [Khambhat, MotvdDPP, D (Gujarat)]\n",
              "2    [Khambhat, MotvdDPP, D (Gujarat)]\n",
              "3    [Khambhat, MotvdDPP, D (Gujarat)]\n",
              "4    [Khambhat, MotvdDPP, D (Gujarat)]\n",
              "Name: destination_name, dtype: object"
            ]
          },
          "execution_count": 68,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "temp_destination_name = work_df.destination_name.str.split('_')\n",
        "temp_destination_name.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 69,
      "metadata": {
        "id": "E24GIVlmayuA"
      },
      "outputs": [],
      "source": [
        "# Extract Destination Name\n",
        "Dest_Name = []\n",
        "for name in work_df['destination_name']:\n",
        "  name = str(name).split('_')\n",
        "  Dest_Name.append(name[0])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 70,
      "metadata": {
        "id": "H6wPySbdfh2v"
      },
      "outputs": [],
      "source": [
        "# Add extracted destination name to the the dataset\n",
        "work_df['Dest_Name'] = Dest_Name"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 71,
      "metadata": {
        "id": "WZwtlIBR5-Di"
      },
      "outputs": [],
      "source": [
        "# Extract Source Name\n",
        "Src_Name = []\n",
        "for name in work_df['source_name']:\n",
        "  name = str(name).split(' (')\n",
        "  Src_Name.append(name[0])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 72,
      "metadata": {
        "id": "WHcBsCqGIIo5"
      },
      "outputs": [],
      "source": [
        "# Add extracted source name to the the dataset\n",
        "work_df['Src_Name'] = Src_Name"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 73,
      "metadata": {
        "id": "S-t3kkPrK1cy"
      },
      "outputs": [],
      "source": [
        "# Extract Date Month and Year from Trip Creation Time\n",
        "temp_date = []\n",
        "for dateVal in work_df['trip_creation_time']:\n",
        "  dt = datetime.strptime(str(dateVal), '%Y-%m-%d %H:%M:%S.%f')\n",
        "  temp_date.append(str(dt.day) + ' ' + str(dt.month) + ' ' + str(dt.year))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 74,
      "metadata": {
        "id": "UNrmF1eZMuye"
      },
      "outputs": [],
      "source": [
        "# Add extracted day, month and year to the dataset\n",
        "work_df['Trip_Creation_Time_New'] = temp_date"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 75,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 473
        },
        "id": "tWEKbs4TM7OZ",
        "outputId": "451e8712-04e9-469b-b9c5-312cecc83451"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-65739163-608a-48d5-89a0-388033056cda\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>data</th>\n",
              "      <th>trip_creation_time</th>\n",
              "      <th>route_schedule_uuid</th>\n",
              "      <th>route_type</th>\n",
              "      <th>trip_uuid</th>\n",
              "      <th>source_center</th>\n",
              "      <th>source_name</th>\n",
              "      <th>destination_center</th>\n",
              "      <th>destination_name</th>\n",
              "      <th>od_start_time</th>\n",
              "      <th>...</th>\n",
              "      <th>actual_time</th>\n",
              "      <th>osrm_time</th>\n",
              "      <th>osrm_distance</th>\n",
              "      <th>segment_actual_time</th>\n",
              "      <th>segment_osrm_time</th>\n",
              "      <th>segment_osrm_distance</th>\n",
              "      <th>od_time_diff</th>\n",
              "      <th>Dest_Name</th>\n",
              "      <th>Src_Name</th>\n",
              "      <th>Trip_Creation_Time_New</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>training</td>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>...</td>\n",
              "      <td>14.0</td>\n",
              "      <td>11.0</td>\n",
              "      <td>11.9653</td>\n",
              "      <td>14.0</td>\n",
              "      <td>11.0</td>\n",
              "      <td>11.9653</td>\n",
              "      <td>86.213637</td>\n",
              "      <td>Khambhat</td>\n",
              "      <td>Anand_VUNagar_DC</td>\n",
              "      <td>20 9 2018</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>training</td>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>...</td>\n",
              "      <td>24.0</td>\n",
              "      <td>20.0</td>\n",
              "      <td>21.7243</td>\n",
              "      <td>10.0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>9.7590</td>\n",
              "      <td>86.213637</td>\n",
              "      <td>Khambhat</td>\n",
              "      <td>Anand_VUNagar_DC</td>\n",
              "      <td>20 9 2018</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>training</td>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>...</td>\n",
              "      <td>40.0</td>\n",
              "      <td>28.0</td>\n",
              "      <td>32.5395</td>\n",
              "      <td>16.0</td>\n",
              "      <td>7.0</td>\n",
              "      <td>10.8152</td>\n",
              "      <td>86.213637</td>\n",
              "      <td>Khambhat</td>\n",
              "      <td>Anand_VUNagar_DC</td>\n",
              "      <td>20 9 2018</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>training</td>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>...</td>\n",
              "      <td>62.0</td>\n",
              "      <td>40.0</td>\n",
              "      <td>45.5620</td>\n",
              "      <td>21.0</td>\n",
              "      <td>12.0</td>\n",
              "      <td>13.0224</td>\n",
              "      <td>86.213637</td>\n",
              "      <td>Khambhat</td>\n",
              "      <td>Anand_VUNagar_DC</td>\n",
              "      <td>20 9 2018</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>training</td>\n",
              "      <td>2018-09-20 02:35:36.476840</td>\n",
              "      <td>thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...</td>\n",
              "      <td>Carting</td>\n",
              "      <td>trip-153741093647649320</td>\n",
              "      <td>IND388121AAA</td>\n",
              "      <td>Anand_VUNagar_DC (Gujarat)</td>\n",
              "      <td>IND388620AAB</td>\n",
              "      <td>Khambhat_MotvdDPP_D (Gujarat)</td>\n",
              "      <td>2018-09-20 03:21:32.418600</td>\n",
              "      <td>...</td>\n",
              "      <td>68.0</td>\n",
              "      <td>44.0</td>\n",
              "      <td>54.2181</td>\n",
              "      <td>6.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>3.9153</td>\n",
              "      <td>86.213637</td>\n",
              "      <td>Khambhat</td>\n",
              "      <td>Anand_VUNagar_DC</td>\n",
              "      <td>20 9 2018</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 23 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-65739163-608a-48d5-89a0-388033056cda')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-65739163-608a-48d5-89a0-388033056cda button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-65739163-608a-48d5-89a0-388033056cda');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "       data         trip_creation_time  \\\n",
              "0  training 2018-09-20 02:35:36.476840   \n",
              "1  training 2018-09-20 02:35:36.476840   \n",
              "2  training 2018-09-20 02:35:36.476840   \n",
              "3  training 2018-09-20 02:35:36.476840   \n",
              "4  training 2018-09-20 02:35:36.476840   \n",
              "\n",
              "                                 route_schedule_uuid route_type  \\\n",
              "0  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "1  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "2  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "3  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "4  thanos::sroute:eb7bfc78-b351-4c0e-a951-fa3d5c3...    Carting   \n",
              "\n",
              "                 trip_uuid source_center                 source_name  \\\n",
              "0  trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "1  trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "2  trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "3  trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "4  trip-153741093647649320  IND388121AAA  Anand_VUNagar_DC (Gujarat)   \n",
              "\n",
              "  destination_center               destination_name  \\\n",
              "0       IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "1       IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "2       IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "3       IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "4       IND388620AAB  Khambhat_MotvdDPP_D (Gujarat)   \n",
              "\n",
              "               od_start_time  ... actual_time  osrm_time  osrm_distance  \\\n",
              "0 2018-09-20 03:21:32.418600  ...        14.0       11.0        11.9653   \n",
              "1 2018-09-20 03:21:32.418600  ...        24.0       20.0        21.7243   \n",
              "2 2018-09-20 03:21:32.418600  ...        40.0       28.0        32.5395   \n",
              "3 2018-09-20 03:21:32.418600  ...        62.0       40.0        45.5620   \n",
              "4 2018-09-20 03:21:32.418600  ...        68.0       44.0        54.2181   \n",
              "\n",
              "   segment_actual_time  segment_osrm_time  segment_osrm_distance  \\\n",
              "0                 14.0               11.0                11.9653   \n",
              "1                 10.0                9.0                 9.7590   \n",
              "2                 16.0                7.0                10.8152   \n",
              "3                 21.0               12.0                13.0224   \n",
              "4                  6.0                5.0                 3.9153   \n",
              "\n",
              "   od_time_diff  Dest_Name          Src_Name  Trip_Creation_Time_New  \n",
              "0     86.213637   Khambhat  Anand_VUNagar_DC               20 9 2018  \n",
              "1     86.213637   Khambhat  Anand_VUNagar_DC               20 9 2018  \n",
              "2     86.213637   Khambhat  Anand_VUNagar_DC               20 9 2018  \n",
              "3     86.213637   Khambhat  Anand_VUNagar_DC               20 9 2018  \n",
              "4     86.213637   Khambhat  Anand_VUNagar_DC               20 9 2018  \n",
              "\n",
              "[5 rows x 23 columns]"
            ]
          },
          "execution_count": 75,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Verifying the newly added features\n",
        "work_df.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 76,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 240
        },
        "id": "9DtQBXT4NLJo",
        "outputId": "b7d83231-392c-443a-f526-24105735241d"
      },
      "outputs": [
        {
          "ename": "TypeError",
          "evalue": "ignored",
          "output_type": "error",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-76-d136cf98257c>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mstart_date\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstrptime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mwork_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'od_start_time'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'%Y-%m-%d %H:%M:%S.%f'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0mend_date\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstrptime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mwork_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'od_end_time'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'%Y-%m-%d %H:%M:%S.%f'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0mduration\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mabs\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mend_date\u001b[0m \u001b[0;34m-\u001b[0m \u001b[0mstart_date\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtotal_seconds\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mduration_in_mins\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mround\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mduration\u001b[0m\u001b[0;34m/\u001b[0m\u001b[0;36m60\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mduration_in_mins\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mTypeError\u001b[0m: strptime() argument 1 must be str, not Timestamp"
          ]
        }
      ],
      "source": [
        "start_date = datetime.strptime(work_df['od_start_time'][0], '%Y-%m-%d %H:%M:%S.%f')\n",
        "end_date = datetime.strptime(work_df['od_end_time'][0], '%Y-%m-%d %H:%M:%S.%f')\n",
        "duration = abs((end_date - start_date).total_seconds())\n",
        "duration_in_mins = round(duration/60)\n",
        "print(duration_in_mins)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "pkLxd6a5O3oc"
      },
      "outputs": [],
      "source": [
        "# Find the Start to End duration and add to the dataset\n",
        "duration_in_mins = []\n",
        "for i in range(len(work_df)):\n",
        "  start_date = datetime.strptime(work_df['od_start_time'][i], '%Y-%m-%d %H:%M:%S.%f')\n",
        "  end_date = datetime.strptime(work_df['od_end_time'][i], '%Y-%m-%d %H:%M:%S.%f')\n",
        "  duration = abs((end_date - start_date).total_seconds())\n",
        "  duration_in_mins.append(round(duration/60))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xkMdYmlTVOcM"
      },
      "outputs": [],
      "source": [
        "work_df['Duration_In_Mins'] = duration_in_mins"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "01uNw8-4VqLR"
      },
      "outputs": [],
      "source": [
        "# Numerical features analysis\n",
        "work_df.describe().T"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5tXIL63Lf1rs"
      },
      "source": [
        "### Insights \n",
        "\n",
        "Averages:\n",
        "*   Avg Time \"start_scan_to_end_scan\" is: 964 mins\n",
        "*   Avg distance to destination is: 234 kms warehouse \n",
        "*   Avg actual time 418 mins\n",
        "*   Avg osrm_time 214 mins\n",
        "*   Avg osrm_distance 285 kms\n",
        "*   Avg segment_actual_time 36 mins\n",
        "*   Avg segment_osrm_time is 18 mins\n",
        "*   Avg segment_osrm_distance is 22 kms\n",
        "*   Avg duration in mins is 964"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "jmWklP7thx4x"
      },
      "outputs": [],
      "source": [
        "work_df.describe(include='object').T"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DQ9ddysniCzS"
      },
      "outputs": [],
      "source": [
        "work_df['route_type'].value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "m3oFDa3hlhqI"
      },
      "outputs": [],
      "source": [
        "plt.figure(figsize=(15,5))\n",
        "plt.pie(work_df['route_type'].value_counts(),labels=['Carting','FTL'], autopct='%.0f%%')\n",
        "plt.title('Route Type Distribution')\n",
        "plt.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_HwELLCuiLOs"
      },
      "source": [
        "##### Of the 2 route types, 'FTL' seems to be more frequent with high numbers\n",
        "##### Gurgaon-Bilaspur-HB tops in terms of Source City and Gurgaon city seems to be the popular destination with high numers"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "PWohpSxu0qz0"
      },
      "outputs": [],
      "source": [
        "for feature in num_features:\n",
        "  print(work_df[feature].value_counts())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4aly_UcsAqrC"
      },
      "outputs": [],
      "source": [
        "# Comparision & Visulization of distance fields\n",
        "plt.figure(figsize = (20,15))\n",
        "sns.displot(work_df['start_scan_to_end_scan'],kind='kde')\n",
        "plt.axvline(x = work_df['start_scan_to_end_scan'].mean(), color = 'b',linestyle='--',label = 'Start Scan to End Scan Mean')\n",
        "plt.title('Start Scan to End Scan Distribution')\n",
        "plt.legend()\n",
        "plt.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "FKlRPe2rO0Zo"
      },
      "outputs": [],
      "source": [
        "plt.figure(figsize = (20,15))\n",
        "sns.displot(work_df['actual_distance_to_destination'],kind='kde')\n",
        "plt.axvline(x = work_df['actual_distance_to_destination'].mean(), color = 'b',linestyle='--',label = 'actual_distance_to_destination KMs Mean')\n",
        "plt.title('Actual Distance To Destination Distribution')\n",
        "plt.legend()\n",
        "plt.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ywvSxB8kkKgp"
      },
      "outputs": [],
      "source": [
        "plt.figure(figsize = (20,15))\n",
        "sns.countplot(data= work_df, x = 'Trip_Creation_Time_New')\n",
        "plt.title('osrm_distance Distribution')\n",
        "plt.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Iiq2mfYpkx8q"
      },
      "source": [
        "#### This indicates, the trip creation time follows a normal distribution with more or less constant time across dates"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ukVKo0rZ3DHL"
      },
      "outputs": [],
      "source": [
        "busy_route_df = work_df.groupby(['trip_uuid','source_center','destination_center']).count().reset_index()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7cR_S3se3HKu"
      },
      "outputs": [],
      "source": [
        "busy_route_df['route_schedule_uuid'].max()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "e-w9zI_4mnwC"
      },
      "outputs": [],
      "source": [
        "# Outliers of Numerical Features\n",
        "for feature in num_features:\n",
        " plt.figure(figsize = (15,8))\n",
        " sns.boxplot(work_df[feature])\n",
        " print('#'*100)\n",
        " print(f'Outlier for {feature}: ')\n",
        " print(np.percentile(work_df[feature],75) + 1.5 * (np.percentile(work_df[feature],75) - np.percentile(work_df[feature],25)))\n",
        " print('\\n')\n",
        "\n",
        " plt.title(feature)\n",
        " plt.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "2CjDWWonmoaT"
      },
      "outputs": [],
      "source": [
        "# Applying the log as there are outliers\n",
        "for i in num_features:\n",
        "  work_df[i] = np.log(work_df[i])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "WcmoJYccoZnm"
      },
      "outputs": [],
      "source": [
        "for i in num_features:\n",
        "  plt.figure(figsize=(15,8))\n",
        "  sns.boxplot(work_df[i])\n",
        "  plt.title(i)\n",
        "  plt.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Q3Nhzlnqo95-"
      },
      "outputs": [],
      "source": [
        "for i in num_features:\n",
        " plt.figure(figsize = (20,15))\n",
        " sns.displot(work_df[i],kind='kde')\n",
        " plt.axvline(x = work_df[i].mean(), color = 'b',linestyle='--',label = f'{i} Mean')\n",
        " plt.title(i)\n",
        " plt.legend()\n",
        " plt.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "aWxOdtC3uKaO"
      },
      "outputs": [],
      "source": [
        "# Bi-variate analysis of Categorical Features\n",
        "for i in cat_features:\n",
        " plt.figure(figsize = (20,15))\n",
        " sns.displot(data=work_df,x = i)\n",
        " plt.title(i)\n",
        "\n",
        " plt.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "VO7IiFsHu1hz"
      },
      "outputs": [],
      "source": [
        "sns.displot(data=work_df, x = od_time_diff, hue='route_type')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "aF_batGZvi1E"
      },
      "outputs": [],
      "source": [
        "plt.figure(figsize=(20, 20))\n",
        "sns.heatmap(work_df.corr(), annot=True)\n",
        "plt.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ICKhprMVrBOr"
      },
      "source": [
        "### Relationship Analysis and Hypothesis Testing"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JSKvyqbTrfnd"
      },
      "source": [
        "###### Compare the difference between Point a. and start_scan_to_end_scan\n",
        "\n",
        "Ho: Mean OD start and end time difference = Start to End Scan mean\n",
        "H1: Mean OD start and end time difference != Start to End Scan mean"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "KKRJv8cGpMxP"
      },
      "outputs": [],
      "source": [
        "print(f\"OD Start and End Time Difference: {shapiro(work_df['od_time_diff'])}\")\n",
        "print(f\"start_scan_to_end_scan: {shapiro(work_df['start_scan_to_end_scan'])}\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "l_q_DMgosHqE"
      },
      "source": [
        "##### Based on the p-value, we can fail to accept the Ho"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "atMgfJI3pNEP"
      },
      "outputs": [],
      "source": [
        "stats.kruskal(work_df['od_time_diff'],work_df['start_scan_to_end_scan'])"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "W8cczQIosp3q"
      },
      "source": [
        "##### Kruskal test too affirms our above observation"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Tj855eeps6nF"
      },
      "source": [
        "##### Do hypothesis testing/ visual analysis between actual_time and OSRM time \n",
        "\n",
        "Ho: Mean Actual time = osrm mean\n",
        "H1: Mean Actual time != osrm mean "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "XtMazYQWpMdy"
      },
      "outputs": [],
      "source": [
        "print(f\"Actual time distribution: {shapiro(work_df['actual_time'])}\")\n",
        "print(f\"osrm time distribution: {shapiro(work_df['osrm_time'])}\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ApxBjEiwtn32"
      },
      "outputs": [],
      "source": [
        "stats.kruskal(work_df['actual_time'],work_df['osrm_time'])"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BvgcMO58O-L5"
      },
      "source": [
        "### Observations\n",
        "\n",
        "*   FTL form of delivery is the highest as compared to Carting\n",
        "*   Gurgaon is the top source and incidently which is also the top destination\n",
        "*   In terms of start_to_end_scan FTL takes less time as against Carting\n",
        "*   On distance covered, Carting has less distance as compared to FTL\n",
        "*   OD start and end time doesn't show huge difference\n",
        "*   But, actual time and osrm time has significant difference\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Z5KoTrhJPGsO"
      },
      "source": [
        "# Summary\n",
        "\n",
        "Some key recommendations based on the analysis and Observations would be,  \n",
        "\n",
        "*   Delhivery can start to increase number of sub hubs between the long distance hubs to shorten the delivery time especially in high traffic zones\n",
        "*   There are few regions where Delhivery can increase their presence to tap and increase their stake in that market"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "authorship_tag": "ABX9TyM71JRMF7VP/R37Of24pjVa",
      "include_colab_link": true,
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}