ZINDI -SENDY2019.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Predicting the Estimated Time of Arrival for Bikes in Nairobi\n",
    "\n",
    "BLOSSOM ML GROUP C - MARCH 2020</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "#importing libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import data to be used\n",
    "train=pd.read_csv('Train.csv')\n",
    "riders=pd.read_csv('Riders.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Order No</th>\n",
       "      <th>User Id</th>\n",
       "      <th>Vehicle Type</th>\n",
       "      <th>Platform Type</th>\n",
       "      <th>Personal or Business</th>\n",
       "      <th>Placement - Day of Month</th>\n",
       "      <th>Placement - Weekday (Mo = 1)</th>\n",
       "      <th>Placement - Time</th>\n",
       "      <th>Confirmation - Day of Month</th>\n",
       "      <th>Confirmation - Weekday (Mo = 1)</th>\n",
       "      <th>...</th>\n",
       "      <th>Arrival at Destination - Time</th>\n",
       "      <th>Distance (KM)</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Precipitation in millimeters</th>\n",
       "      <th>Pickup Lat</th>\n",
       "      <th>Pickup Long</th>\n",
       "      <th>Destination Lat</th>\n",
       "      <th>Destination Long</th>\n",
       "      <th>Rider Id</th>\n",
       "      <th>Time from Pickup to Arrival</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Order_No_4211</td>\n",
       "      <td>User_Id_633</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Business</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>9:35:46 AM</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>10:39:55 AM</td>\n",
       "      <td>4</td>\n",
       "      <td>20.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.317755</td>\n",
       "      <td>36.830370</td>\n",
       "      <td>-1.300406</td>\n",
       "      <td>36.829741</td>\n",
       "      <td>Rider_Id_432</td>\n",
       "      <td>745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Order_No_25375</td>\n",
       "      <td>User_Id_2285</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Personal</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>11:16:16 AM</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>12:17:22 PM</td>\n",
       "      <td>16</td>\n",
       "      <td>26.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.351453</td>\n",
       "      <td>36.899315</td>\n",
       "      <td>-1.295004</td>\n",
       "      <td>36.814358</td>\n",
       "      <td>Rider_Id_856</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Order_No_1899</td>\n",
       "      <td>User_Id_265</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Business</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "      <td>12:39:25 PM</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>1:00:38 PM</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.308284</td>\n",
       "      <td>36.843419</td>\n",
       "      <td>-1.300921</td>\n",
       "      <td>36.828195</td>\n",
       "      <td>Rider_Id_155</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Order_No_9336</td>\n",
       "      <td>User_Id_1402</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Business</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>9:25:34 AM</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>10:05:27 AM</td>\n",
       "      <td>9</td>\n",
       "      <td>19.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.281301</td>\n",
       "      <td>36.832396</td>\n",
       "      <td>-1.257147</td>\n",
       "      <td>36.795063</td>\n",
       "      <td>Rider_Id_855</td>\n",
       "      <td>1341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Order_No_27883</td>\n",
       "      <td>User_Id_1737</td>\n",
       "      <td>Bike</td>\n",
       "      <td>1</td>\n",
       "      <td>Personal</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>9:55:18 AM</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>10:25:37 AM</td>\n",
       "      <td>9</td>\n",
       "      <td>15.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.266597</td>\n",
       "      <td>36.792118</td>\n",
       "      <td>-1.295041</td>\n",
       "      <td>36.809817</td>\n",
       "      <td>Rider_Id_770</td>\n",
       "      <td>1214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21196</th>\n",
       "      <td>Order_No_8834</td>\n",
       "      <td>User_Id_2001</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Personal</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>3:54:38 PM</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>4:20:17 PM</td>\n",
       "      <td>3</td>\n",
       "      <td>28.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.258414</td>\n",
       "      <td>36.804800</td>\n",
       "      <td>-1.275285</td>\n",
       "      <td>36.802702</td>\n",
       "      <td>Rider_Id_953</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21197</th>\n",
       "      <td>Order_No_22892</td>\n",
       "      <td>User_Id_1796</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Business</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>10:13:34 AM</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>10:46:17 AM</td>\n",
       "      <td>7</td>\n",
       "      <td>26.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.307143</td>\n",
       "      <td>36.825009</td>\n",
       "      <td>-1.331619</td>\n",
       "      <td>36.847976</td>\n",
       "      <td>Rider_Id_155</td>\n",
       "      <td>770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21198</th>\n",
       "      <td>Order_No_2831</td>\n",
       "      <td>User_Id_2956</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Business</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>5:06:16 PM</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>6:40:05 PM</td>\n",
       "      <td>20</td>\n",
       "      <td>29.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.286018</td>\n",
       "      <td>36.897534</td>\n",
       "      <td>-1.258414</td>\n",
       "      <td>36.804800</td>\n",
       "      <td>Rider_Id_697</td>\n",
       "      <td>2953</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21199</th>\n",
       "      <td>Order_No_6174</td>\n",
       "      <td>User_Id_2524</td>\n",
       "      <td>Bike</td>\n",
       "      <td>1</td>\n",
       "      <td>Personal</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>9:31:39 AM</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>10:08:15 AM</td>\n",
       "      <td>13</td>\n",
       "      <td>15.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.250030</td>\n",
       "      <td>36.874167</td>\n",
       "      <td>-1.279209</td>\n",
       "      <td>36.794872</td>\n",
       "      <td>Rider_Id_347</td>\n",
       "      <td>1380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21200</th>\n",
       "      <td>Order_No_9836</td>\n",
       "      <td>User_Id_718</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Business</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>2:19:47 PM</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>3:17:23 PM</td>\n",
       "      <td>12</td>\n",
       "      <td>30.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.255189</td>\n",
       "      <td>36.782203</td>\n",
       "      <td>-1.320157</td>\n",
       "      <td>36.830887</td>\n",
       "      <td>Rider_Id_177</td>\n",
       "      <td>2128</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21201 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Order No       User Id Vehicle Type  Platform Type  \\\n",
       "0       Order_No_4211   User_Id_633         Bike              3   \n",
       "1      Order_No_25375  User_Id_2285         Bike              3   \n",
       "2       Order_No_1899   User_Id_265         Bike              3   \n",
       "3       Order_No_9336  User_Id_1402         Bike              3   \n",
       "4      Order_No_27883  User_Id_1737         Bike              1   \n",
       "...               ...           ...          ...            ...   \n",
       "21196   Order_No_8834  User_Id_2001         Bike              3   \n",
       "21197  Order_No_22892  User_Id_1796         Bike              3   \n",
       "21198   Order_No_2831  User_Id_2956         Bike              3   \n",
       "21199   Order_No_6174  User_Id_2524         Bike              1   \n",
       "21200   Order_No_9836   User_Id_718         Bike              3   \n",
       "\n",
       "      Personal or Business  Placement - Day of Month  \\\n",
       "0                 Business                         9   \n",
       "1                 Personal                        12   \n",
       "2                 Business                        30   \n",
       "3                 Business                        15   \n",
       "4                 Personal                        13   \n",
       "...                    ...                       ...   \n",
       "21196             Personal                        20   \n",
       "21197             Business                        13   \n",
       "21198             Business                         7   \n",
       "21199             Personal                         4   \n",
       "21200             Business                        26   \n",
       "\n",
       "       Placement - Weekday (Mo = 1) Placement - Time  \\\n",
       "0                                 5       9:35:46 AM   \n",
       "1                                 5      11:16:16 AM   \n",
       "2                                 2      12:39:25 PM   \n",
       "3                                 5       9:25:34 AM   \n",
       "4                                 1       9:55:18 AM   \n",
       "...                             ...              ...   \n",
       "21196                             3       3:54:38 PM   \n",
       "21197                             6      10:13:34 AM   \n",
       "21198                             4       5:06:16 PM   \n",
       "21199                             3       9:31:39 AM   \n",
       "21200                             2       2:19:47 PM   \n",
       "\n",
       "       Confirmation - Day of Month  Confirmation - Weekday (Mo = 1)  ...  \\\n",
       "0                                9                                5  ...   \n",
       "1                               12                                5  ...   \n",
       "2                               30                                2  ...   \n",
       "3                               15                                5  ...   \n",
       "4                               13                                1  ...   \n",
       "...                            ...                              ...  ...   \n",
       "21196                           20                                3  ...   \n",
       "21197                           13                                6  ...   \n",
       "21198                            7                                4  ...   \n",
       "21199                            4                                3  ...   \n",
       "21200                           26                                2  ...   \n",
       "\n",
       "      Arrival at Destination - Time  Distance (KM)  Temperature  \\\n",
       "0                       10:39:55 AM              4         20.4   \n",
       "1                       12:17:22 PM             16         26.4   \n",
       "2                        1:00:38 PM              3          NaN   \n",
       "3                       10:05:27 AM              9         19.2   \n",
       "4                       10:25:37 AM              9         15.4   \n",
       "...                             ...            ...          ...   \n",
       "21196                    4:20:17 PM              3         28.6   \n",
       "21197                   10:46:17 AM              7         26.0   \n",
       "21198                    6:40:05 PM             20         29.2   \n",
       "21199                   10:08:15 AM             13         15.0   \n",
       "21200                    3:17:23 PM             12         30.9   \n",
       "\n",
       "      Precipitation in millimeters  Pickup Lat  Pickup Long Destination Lat  \\\n",
       "0                              NaN   -1.317755    36.830370       -1.300406   \n",
       "1                              NaN   -1.351453    36.899315       -1.295004   \n",
       "2                              NaN   -1.308284    36.843419       -1.300921   \n",
       "3                              NaN   -1.281301    36.832396       -1.257147   \n",
       "4                              NaN   -1.266597    36.792118       -1.295041   \n",
       "...                            ...         ...          ...             ...   \n",
       "21196                          NaN   -1.258414    36.804800       -1.275285   \n",
       "21197                          NaN   -1.307143    36.825009       -1.331619   \n",
       "21198                          NaN   -1.286018    36.897534       -1.258414   \n",
       "21199                          NaN   -1.250030    36.874167       -1.279209   \n",
       "21200                          NaN   -1.255189    36.782203       -1.320157   \n",
       "\n",
       "       Destination Long      Rider Id Time from Pickup to Arrival  \n",
       "0             36.829741  Rider_Id_432                         745  \n",
       "1             36.814358  Rider_Id_856                        1993  \n",
       "2             36.828195  Rider_Id_155                         455  \n",
       "3             36.795063  Rider_Id_855                        1341  \n",
       "4             36.809817  Rider_Id_770                        1214  \n",
       "...                 ...           ...                         ...  \n",
       "21196         36.802702  Rider_Id_953                           9  \n",
       "21197         36.847976  Rider_Id_155                         770  \n",
       "21198         36.804800  Rider_Id_697                        2953  \n",
       "21199         36.794872  Rider_Id_347                        1380  \n",
       "21200         36.830887  Rider_Id_177                        2128  \n",
       "\n",
       "[21201 rows x 29 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#visualize dataframes\n",
    "train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rider Id</th>\n",
       "      <th>No_Of_Orders</th>\n",
       "      <th>Age</th>\n",
       "      <th>Average_Rating</th>\n",
       "      <th>No_of_Ratings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rider_Id_396</td>\n",
       "      <td>2946</td>\n",
       "      <td>2298</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rider_Id_479</td>\n",
       "      <td>360</td>\n",
       "      <td>951</td>\n",
       "      <td>13.5</td>\n",
       "      <td>176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rider_Id_648</td>\n",
       "      <td>1746</td>\n",
       "      <td>821</td>\n",
       "      <td>14.3</td>\n",
       "      <td>466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rider_Id_753</td>\n",
       "      <td>314</td>\n",
       "      <td>980</td>\n",
       "      <td>12.5</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rider_Id_335</td>\n",
       "      <td>536</td>\n",
       "      <td>1113</td>\n",
       "      <td>13.7</td>\n",
       "      <td>156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>955</th>\n",
       "      <td>Rider_Id_896</td>\n",
       "      <td>152</td>\n",
       "      <td>99</td>\n",
       "      <td>12.4</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>956</th>\n",
       "      <td>Rider_Id_149</td>\n",
       "      <td>69</td>\n",
       "      <td>101</td>\n",
       "      <td>10.2</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>957</th>\n",
       "      <td>Rider_Id_270</td>\n",
       "      <td>338</td>\n",
       "      <td>96</td>\n",
       "      <td>14.4</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>958</th>\n",
       "      <td>Rider_Id_201</td>\n",
       "      <td>159</td>\n",
       "      <td>96</td>\n",
       "      <td>15.0</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>959</th>\n",
       "      <td>Rider_Id_904</td>\n",
       "      <td>172</td>\n",
       "      <td>108</td>\n",
       "      <td>13.7</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>960 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Rider Id  No_Of_Orders   Age  Average_Rating  No_of_Ratings\n",
       "0    Rider_Id_396          2946  2298            14.0           1159\n",
       "1    Rider_Id_479           360   951            13.5            176\n",
       "2    Rider_Id_648          1746   821            14.3            466\n",
       "3    Rider_Id_753           314   980            12.5             75\n",
       "4    Rider_Id_335           536  1113            13.7            156\n",
       "..            ...           ...   ...             ...            ...\n",
       "955  Rider_Id_896           152    99            12.4             18\n",
       "956  Rider_Id_149            69   101            10.2             10\n",
       "957  Rider_Id_270           338    96            14.4             41\n",
       "958  Rider_Id_201           159    96            15.0              9\n",
       "959  Rider_Id_904           172   108            13.7             18\n",
       "\n",
       "[960 rows x 5 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#visualize dataframes\n",
    "riders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Platform Type</th>\n",
       "      <th>Placement - Day of Month</th>\n",
       "      <th>Placement - Weekday (Mo = 1)</th>\n",
       "      <th>Confirmation - Day of Month</th>\n",
       "      <th>Confirmation - Weekday (Mo = 1)</th>\n",
       "      <th>Arrival at Pickup - Day of Month</th>\n",
       "      <th>Arrival at Pickup - Weekday (Mo = 1)</th>\n",
       "      <th>Pickup - Day of Month</th>\n",
       "      <th>Pickup - Weekday (Mo = 1)</th>\n",
       "      <th>Arrival at Destination - Day of Month</th>\n",
       "      <th>Arrival at Destination - Weekday (Mo = 1)</th>\n",
       "      <th>Distance (KM)</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Precipitation in millimeters</th>\n",
       "      <th>Pickup Lat</th>\n",
       "      <th>Pickup Long</th>\n",
       "      <th>Destination Lat</th>\n",
       "      <th>Destination Long</th>\n",
       "      <th>Time from Pickup to Arrival</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>16835.000000</td>\n",
       "      <td>552.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "      <td>21201.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2.752182</td>\n",
       "      <td>15.653696</td>\n",
       "      <td>3.240083</td>\n",
       "      <td>15.653837</td>\n",
       "      <td>3.240225</td>\n",
       "      <td>15.653837</td>\n",
       "      <td>3.240225</td>\n",
       "      <td>15.653837</td>\n",
       "      <td>3.240225</td>\n",
       "      <td>15.653837</td>\n",
       "      <td>3.240225</td>\n",
       "      <td>9.506533</td>\n",
       "      <td>23.258889</td>\n",
       "      <td>7.905797</td>\n",
       "      <td>-1.281470</td>\n",
       "      <td>36.811264</td>\n",
       "      <td>-1.282581</td>\n",
       "      <td>36.811220</td>\n",
       "      <td>1556.920947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.625178</td>\n",
       "      <td>8.798916</td>\n",
       "      <td>1.567295</td>\n",
       "      <td>8.798886</td>\n",
       "      <td>1.567228</td>\n",
       "      <td>8.798886</td>\n",
       "      <td>1.567228</td>\n",
       "      <td>8.798886</td>\n",
       "      <td>1.567228</td>\n",
       "      <td>8.798886</td>\n",
       "      <td>1.567228</td>\n",
       "      <td>5.668963</td>\n",
       "      <td>3.615768</td>\n",
       "      <td>17.089971</td>\n",
       "      <td>0.030507</td>\n",
       "      <td>0.037473</td>\n",
       "      <td>0.034824</td>\n",
       "      <td>0.044721</td>\n",
       "      <td>987.270788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>11.200000</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>-1.438302</td>\n",
       "      <td>36.653621</td>\n",
       "      <td>-1.430298</td>\n",
       "      <td>36.606594</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>20.600000</td>\n",
       "      <td>1.075000</td>\n",
       "      <td>-1.300921</td>\n",
       "      <td>36.784605</td>\n",
       "      <td>-1.301201</td>\n",
       "      <td>36.785661</td>\n",
       "      <td>882.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>23.500000</td>\n",
       "      <td>2.900000</td>\n",
       "      <td>-1.279395</td>\n",
       "      <td>36.807040</td>\n",
       "      <td>-1.284382</td>\n",
       "      <td>36.808002</td>\n",
       "      <td>1369.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>26.000000</td>\n",
       "      <td>4.900000</td>\n",
       "      <td>-1.257147</td>\n",
       "      <td>36.829741</td>\n",
       "      <td>-1.261177</td>\n",
       "      <td>36.829477</td>\n",
       "      <td>2040.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4.000000</td>\n",
       "      <td>31.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>31.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>31.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>31.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>31.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>49.000000</td>\n",
       "      <td>32.100000</td>\n",
       "      <td>99.100000</td>\n",
       "      <td>-1.147170</td>\n",
       "      <td>36.991046</td>\n",
       "      <td>-1.030225</td>\n",
       "      <td>37.016779</td>\n",
       "      <td>7883.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Platform Type  Placement - Day of Month  Placement - Weekday (Mo = 1)  \\\n",
       "count   21201.000000              21201.000000                  21201.000000   \n",
       "mean        2.752182                 15.653696                      3.240083   \n",
       "std         0.625178                  8.798916                      1.567295   \n",
       "min         1.000000                  1.000000                      1.000000   \n",
       "25%         3.000000                  8.000000                      2.000000   \n",
       "50%         3.000000                 15.000000                      3.000000   \n",
       "75%         3.000000                 23.000000                      5.000000   \n",
       "max         4.000000                 31.000000                      7.000000   \n",
       "\n",
       "       Confirmation - Day of Month  Confirmation - Weekday (Mo = 1)  \\\n",
       "count                 21201.000000                     21201.000000   \n",
       "mean                     15.653837                         3.240225   \n",
       "std                       8.798886                         1.567228   \n",
       "min                       1.000000                         1.000000   \n",
       "25%                       8.000000                         2.000000   \n",
       "50%                      15.000000                         3.000000   \n",
       "75%                      23.000000                         5.000000   \n",
       "max                      31.000000                         7.000000   \n",
       "\n",
       "       Arrival at Pickup - Day of Month  Arrival at Pickup - Weekday (Mo = 1)  \\\n",
       "count                      21201.000000                          21201.000000   \n",
       "mean                          15.653837                              3.240225   \n",
       "std                            8.798886                              1.567228   \n",
       "min                            1.000000                              1.000000   \n",
       "25%                            8.000000                              2.000000   \n",
       "50%                           15.000000                              3.000000   \n",
       "75%                           23.000000                              5.000000   \n",
       "max                           31.000000                              7.000000   \n",
       "\n",
       "       Pickup - Day of Month  Pickup - Weekday (Mo = 1)  \\\n",
       "count           21201.000000               21201.000000   \n",
       "mean               15.653837                   3.240225   \n",
       "std                 8.798886                   1.567228   \n",
       "min                 1.000000                   1.000000   \n",
       "25%                 8.000000                   2.000000   \n",
       "50%                15.000000                   3.000000   \n",
       "75%                23.000000                   5.000000   \n",
       "max                31.000000                   7.000000   \n",
       "\n",
       "       Arrival at Destination - Day of Month  \\\n",
       "count                           21201.000000   \n",
       "mean                               15.653837   \n",
       "std                                 8.798886   \n",
       "min                                 1.000000   \n",
       "25%                                 8.000000   \n",
       "50%                                15.000000   \n",
       "75%                                23.000000   \n",
       "max                                31.000000   \n",
       "\n",
       "       Arrival at Destination - Weekday (Mo = 1)  Distance (KM)   Temperature  \\\n",
       "count                               21201.000000   21201.000000  16835.000000   \n",
       "mean                                    3.240225       9.506533     23.258889   \n",
       "std                                     1.567228       5.668963      3.615768   \n",
       "min                                     1.000000       1.000000     11.200000   \n",
       "25%                                     2.000000       5.000000     20.600000   \n",
       "50%                                     3.000000       8.000000     23.500000   \n",
       "75%                                     5.000000      13.000000     26.000000   \n",
       "max                                     7.000000      49.000000     32.100000   \n",
       "\n",
       "       Precipitation in millimeters    Pickup Lat   Pickup Long  \\\n",
       "count                    552.000000  21201.000000  21201.000000   \n",
       "mean                       7.905797     -1.281470     36.811264   \n",
       "std                       17.089971      0.030507      0.037473   \n",
       "min                        0.100000     -1.438302     36.653621   \n",
       "25%                        1.075000     -1.300921     36.784605   \n",
       "50%                        2.900000     -1.279395     36.807040   \n",
       "75%                        4.900000     -1.257147     36.829741   \n",
       "max                       99.100000     -1.147170     36.991046   \n",
       "\n",
       "       Destination Lat  Destination Long  Time from Pickup to Arrival  \n",
       "count     21201.000000      21201.000000                 21201.000000  \n",
       "mean         -1.282581         36.811220                  1556.920947  \n",
       "std           0.034824          0.044721                   987.270788  \n",
       "min          -1.430298         36.606594                     1.000000  \n",
       "25%          -1.301201         36.785661                   882.000000  \n",
       "50%          -1.284382         36.808002                  1369.000000  \n",
       "75%          -1.261177         36.829477                  2040.000000  \n",
       "max          -1.030225         37.016779                  7883.000000  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get summary of the train data\n",
    "train.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 21201 entries, 0 to 21200\n",
      "Data columns (total 29 columns):\n",
      "Order No                                     21201 non-null object\n",
      "User Id                                      21201 non-null object\n",
      "Vehicle Type                                 21201 non-null object\n",
      "Platform Type                                21201 non-null int64\n",
      "Personal or Business                         21201 non-null object\n",
      "Placement - Day of Month                     21201 non-null int64\n",
      "Placement - Weekday (Mo = 1)                 21201 non-null int64\n",
      "Placement - Time                             21201 non-null object\n",
      "Confirmation - Day of Month                  21201 non-null int64\n",
      "Confirmation - Weekday (Mo = 1)              21201 non-null int64\n",
      "Confirmation - Time                          21201 non-null object\n",
      "Arrival at Pickup - Day of Month             21201 non-null int64\n",
      "Arrival at Pickup - Weekday (Mo = 1)         21201 non-null int64\n",
      "Arrival at Pickup - Time                     21201 non-null object\n",
      "Pickup - Day of Month                        21201 non-null int64\n",
      "Pickup - Weekday (Mo = 1)                    21201 non-null int64\n",
      "Pickup - Time                                21201 non-null object\n",
      "Arrival at Destination - Day of Month        21201 non-null int64\n",
      "Arrival at Destination - Weekday (Mo = 1)    21201 non-null int64\n",
      "Arrival at Destination - Time                21201 non-null object\n",
      "Distance (KM)                                21201 non-null int64\n",
      "Temperature                                  16835 non-null float64\n",
      "Precipitation in millimeters                 552 non-null float64\n",
      "Pickup Lat                                   21201 non-null float64\n",
      "Pickup Long                                  21201 non-null float64\n",
      "Destination Lat                              21201 non-null float64\n",
      "Destination Long                             21201 non-null float64\n",
      "Rider Id                                     21201 non-null object\n",
      "Time from Pickup to Arrival                  21201 non-null int64\n",
      "dtypes: float64(6), int64(13), object(10)\n",
      "memory usage: 4.7+ MB\n"
     ]
    }
   ],
   "source": [
    "train.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4>For the temperature and precipitation in millimeters we realised that the data frame is missing some points</h4>\n",
    "\n",
    "Temperature has a fill of 16835 / 21201 - including the heading<br>\n",
    "Precipitation in millimeters has 552/21201 - including the heading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Order No                                     21201\n",
       "User Id                                       3186\n",
       "Vehicle Type                                     1\n",
       "Platform Type                                    4\n",
       "Personal or Business                             2\n",
       "Placement - Day of Month                        31\n",
       "Placement - Weekday (Mo = 1)                     7\n",
       "Placement - Time                             15686\n",
       "Confirmation - Day of Month                     31\n",
       "Confirmation - Weekday (Mo = 1)                  7\n",
       "Confirmation - Time                          15742\n",
       "Arrival at Pickup - Day of Month                31\n",
       "Arrival at Pickup - Weekday (Mo = 1)             7\n",
       "Arrival at Pickup - Time                     15767\n",
       "Pickup - Day of Month                           31\n",
       "Pickup - Weekday (Mo = 1)                        7\n",
       "Pickup - Time                                15690\n",
       "Arrival at Destination - Day of Month           31\n",
       "Arrival at Destination - Weekday (Mo = 1)        7\n",
       "Arrival at Destination - Time                15725\n",
       "Distance (KM)                                   45\n",
       "Temperature                                    188\n",
       "Precipitation in millimeters                    54\n",
       "Pickup Lat                                    3666\n",
       "Pickup Long                                   3656\n",
       "Destination Lat                               5302\n",
       "Destination Long                              5267\n",
       "Rider Id                                       924\n",
       "Time from Pickup to Arrival                   4067\n",
       "dtype: int64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Checking if the objects under Temperature and precipitation are still valuable to the analysis lets do a count of the uniqueness of the values in them using .nunique()\n",
    "train.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([20.4, 26.4,  nan, 19.2, 15.4, 27.2, 20.3, 28.7, 24.6, 16.4, 26.1,\n",
       "       20.9, 24.4, 19.4, 20.8, 23.3, 18.1, 22.5, 26.5, 27.3, 25.6, 20.5,\n",
       "       18.8, 16.1, 25.3, 22.3, 26.3, 29.2, 20. , 18. , 21.6, 24.8, 23. ,\n",
       "       28.1, 14.7, 25. , 26.7, 17.6, 22.7, 23.9, 24.7, 28.2, 19.1, 20.1,\n",
       "       17.7, 17.3, 21.9, 24.5, 26.9, 18.4, 22. , 25.7, 23.7, 25.8, 16.2,\n",
       "       31. , 26.2, 28.6, 19.3, 25.2, 24. , 25.5, 23.6, 30.1, 22.8, 27.7,\n",
       "       21.3, 19.7, 14.5, 30.8, 23.8, 21.7, 24.2, 25.9, 21.2, 18.9, 17.9,\n",
       "       18.7, 25.4, 22.9, 27.8, 22.6, 18.6, 26. , 21.1, 24.9, 18.5, 15.3,\n",
       "       27.5, 22.4, 29.6, 19.9, 27.6, 19.8, 20.6, 16.8, 27.4, 24.1, 22.1,\n",
       "       23.1, 27. , 29.8, 19.6, 21.8, 25.1, 16.9, 30. , 23.5, 23.2, 26.6,\n",
       "       28.5, 17.8, 30.5, 15.1, 21. , 16. , 26.8, 24.3, 18.3, 27.1, 20.7,\n",
       "       18.2, 23.4, 14.8, 15.8, 28.4, 21.4, 17.5, 29. , 19.5, 27.9, 22.2,\n",
       "       28. , 20.2, 29.1, 17.1, 29.9, 15.6, 31.8, 28.3, 30.7, 17.2, 29.7,\n",
       "       16.7, 19. , 29.3, 30.6, 29.4, 21.5, 16.6, 28.9, 15. , 13.9, 17.4,\n",
       "       15.2, 30.3, 29.5, 14.4, 13.8, 30.9, 13.4, 28.8, 16.5, 13.3, 15.5,\n",
       "       31.2, 17. , 16.3, 31.1, 30.4, 30.2, 14. , 14.2, 15.7, 13.5, 14.3,\n",
       "       31.9, 31.3, 15.9, 13.6, 14.9, 32.1, 14.6, 13.7, 13.2, 31.7, 14.1,\n",
       "       32. , 11.2])"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train['Temperature'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ nan,  3.9,  0.8,  0.4,  7. ,  1.1,  9.1,  1.9,  2.9,  4.9, 10.9,\n",
       "        3. ,  0.2, 51.1, 15.9,  2. ,  0.9,  1. ,  3.1, 16.1,  5. ,  4. ,\n",
       "        4.1, 11. ,  0.7, 51.9,  2.1, 83.1, 16. ,  0.1,  0.5,  0.6, 42.9,\n",
       "       51. ,  8.9,  0.3, 11.1,  5.1, 52.1, 16.9, 83. , 50.9, 17. , 43. ,\n",
       "       14. , 99.1, 17.1, 98.9, 82.9, 13.9, 43.1,  7.1, 14.9, 99. ,  9. ])"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train['Precipitation in millimeters'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With this we see that the two columns have cells filled with nan\n",
    "since the column contain other value it would still be valuable to the analysis  else we would have droped it using that train=train.drop(['Precipitation in millimeters','Temperature'],axis=1). Save new dataframes as train.to_csv(\"train_new.csv\", index=false)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>No_Of_Orders</th>\n",
       "      <th>Age</th>\n",
       "      <th>Average_Rating</th>\n",
       "      <th>No_of_Ratings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>960.000000</td>\n",
       "      <td>960.000000</td>\n",
       "      <td>960.000000</td>\n",
       "      <td>960.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1864.851042</td>\n",
       "      <td>1200.234375</td>\n",
       "      <td>13.412604</td>\n",
       "      <td>427.983333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1880.337785</td>\n",
       "      <td>810.930171</td>\n",
       "      <td>2.675794</td>\n",
       "      <td>486.957931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>96.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>261.750000</td>\n",
       "      <td>478.250000</td>\n",
       "      <td>13.500000</td>\n",
       "      <td>30.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1475.500000</td>\n",
       "      <td>1021.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>223.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2847.250000</td>\n",
       "      <td>1891.500000</td>\n",
       "      <td>14.300000</td>\n",
       "      <td>678.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>9756.000000</td>\n",
       "      <td>3764.000000</td>\n",
       "      <td>15.200000</td>\n",
       "      <td>2298.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       No_Of_Orders          Age  Average_Rating  No_of_Ratings\n",
       "count    960.000000   960.000000      960.000000     960.000000\n",
       "mean    1864.851042  1200.234375       13.412604     427.983333\n",
       "std     1880.337785   810.930171        2.675794     486.957931\n",
       "min        2.000000    96.000000        0.000000       0.000000\n",
       "25%      261.750000   478.250000       13.500000      30.000000\n",
       "50%     1475.500000  1021.000000       14.000000     223.000000\n",
       "75%     2847.250000  1891.500000       14.300000     678.750000\n",
       "max     9756.000000  3764.000000       15.200000    2298.000000"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get summary of the riders data\n",
    "riders.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 960 entries, 0 to 959\n",
      "Data columns (total 5 columns):\n",
      "Rider Id          960 non-null object\n",
      "No_Of_Orders      960 non-null int64\n",
      "Age               960 non-null int64\n",
      "Average_Rating    960 non-null float64\n",
      "No_of_Ratings     960 non-null int64\n",
      "dtypes: float64(1), int64(3), object(1)\n",
      "memory usage: 37.6+ KB\n"
     ]
    }
   ],
   "source": [
    "riders.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Rider Id          960\n",
       "No_Of_Orders      811\n",
       "Age               725\n",
       "Average_Rating     45\n",
       "No_of_Ratings     553\n",
       "dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#checking for how dynamic the values in the rider dataset is we used .nunique()\n",
    "riders.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Order No</th>\n",
       "      <th>User Id</th>\n",
       "      <th>Vehicle Type</th>\n",
       "      <th>Platform Type</th>\n",
       "      <th>Personal or Business</th>\n",
       "      <th>Placement - Day of Month</th>\n",
       "      <th>Placement - Weekday (Mo = 1)</th>\n",
       "      <th>Placement - Time</th>\n",
       "      <th>Confirmation - Day of Month</th>\n",
       "      <th>Confirmation - Weekday (Mo = 1)</th>\n",
       "      <th>...</th>\n",
       "      <th>Pickup Lat</th>\n",
       "      <th>Pickup Long</th>\n",
       "      <th>Destination Lat</th>\n",
       "      <th>Destination Long</th>\n",
       "      <th>Rider Id</th>\n",
       "      <th>Time from Pickup to Arrival</th>\n",
       "      <th>No_Of_Orders</th>\n",
       "      <th>Age</th>\n",
       "      <th>Average_Rating</th>\n",
       "      <th>No_of_Ratings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Order_No_4211</td>\n",
       "      <td>User_Id_633</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Business</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>9:35:46 AM</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.317755</td>\n",
       "      <td>36.830370</td>\n",
       "      <td>-1.300406</td>\n",
       "      <td>36.829741</td>\n",
       "      <td>Rider_Id_432</td>\n",
       "      <td>745</td>\n",
       "      <td>1637</td>\n",
       "      <td>1309</td>\n",
       "      <td>13.8</td>\n",
       "      <td>549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Order_No_25375</td>\n",
       "      <td>User_Id_2285</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Personal</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>11:16:16 AM</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.351453</td>\n",
       "      <td>36.899315</td>\n",
       "      <td>-1.295004</td>\n",
       "      <td>36.814358</td>\n",
       "      <td>Rider_Id_856</td>\n",
       "      <td>1993</td>\n",
       "      <td>396</td>\n",
       "      <td>339</td>\n",
       "      <td>13.6</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Order_No_1899</td>\n",
       "      <td>User_Id_265</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Business</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "      <td>12:39:25 PM</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.308284</td>\n",
       "      <td>36.843419</td>\n",
       "      <td>-1.300921</td>\n",
       "      <td>36.828195</td>\n",
       "      <td>Rider_Id_155</td>\n",
       "      <td>455</td>\n",
       "      <td>1023</td>\n",
       "      <td>242</td>\n",
       "      <td>12.5</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Order_No_9336</td>\n",
       "      <td>User_Id_1402</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Business</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>9:25:34 AM</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.281301</td>\n",
       "      <td>36.832396</td>\n",
       "      <td>-1.257147</td>\n",
       "      <td>36.795063</td>\n",
       "      <td>Rider_Id_855</td>\n",
       "      <td>1341</td>\n",
       "      <td>886</td>\n",
       "      <td>283</td>\n",
       "      <td>14.5</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Order_No_27883</td>\n",
       "      <td>User_Id_1737</td>\n",
       "      <td>Bike</td>\n",
       "      <td>1</td>\n",
       "      <td>Personal</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>9:55:18 AM</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.266597</td>\n",
       "      <td>36.792118</td>\n",
       "      <td>-1.295041</td>\n",
       "      <td>36.809817</td>\n",
       "      <td>Rider_Id_770</td>\n",
       "      <td>1214</td>\n",
       "      <td>2311</td>\n",
       "      <td>872</td>\n",
       "      <td>14.1</td>\n",
       "      <td>533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21196</th>\n",
       "      <td>Order_No_8834</td>\n",
       "      <td>User_Id_2001</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Personal</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>3:54:38 PM</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.258414</td>\n",
       "      <td>36.804800</td>\n",
       "      <td>-1.275285</td>\n",
       "      <td>36.802702</td>\n",
       "      <td>Rider_Id_953</td>\n",
       "      <td>9</td>\n",
       "      <td>1270</td>\n",
       "      <td>367</td>\n",
       "      <td>14.4</td>\n",
       "      <td>131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21197</th>\n",
       "      <td>Order_No_22892</td>\n",
       "      <td>User_Id_1796</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Business</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>10:13:34 AM</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.307143</td>\n",
       "      <td>36.825009</td>\n",
       "      <td>-1.331619</td>\n",
       "      <td>36.847976</td>\n",
       "      <td>Rider_Id_155</td>\n",
       "      <td>770</td>\n",
       "      <td>1023</td>\n",
       "      <td>242</td>\n",
       "      <td>12.5</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21198</th>\n",
       "      <td>Order_No_2831</td>\n",
       "      <td>User_Id_2956</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Business</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>5:06:16 PM</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.286018</td>\n",
       "      <td>36.897534</td>\n",
       "      <td>-1.258414</td>\n",
       "      <td>36.804800</td>\n",
       "      <td>Rider_Id_697</td>\n",
       "      <td>2953</td>\n",
       "      <td>314</td>\n",
       "      <td>473</td>\n",
       "      <td>13.8</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21199</th>\n",
       "      <td>Order_No_6174</td>\n",
       "      <td>User_Id_2524</td>\n",
       "      <td>Bike</td>\n",
       "      <td>1</td>\n",
       "      <td>Personal</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>9:31:39 AM</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.250030</td>\n",
       "      <td>36.874167</td>\n",
       "      <td>-1.279209</td>\n",
       "      <td>36.794872</td>\n",
       "      <td>Rider_Id_347</td>\n",
       "      <td>1380</td>\n",
       "      <td>2451</td>\n",
       "      <td>1821</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21200</th>\n",
       "      <td>Order_No_9836</td>\n",
       "      <td>User_Id_718</td>\n",
       "      <td>Bike</td>\n",
       "      <td>3</td>\n",
       "      <td>Business</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>2:19:47 PM</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.255189</td>\n",
       "      <td>36.782203</td>\n",
       "      <td>-1.320157</td>\n",
       "      <td>36.830887</td>\n",
       "      <td>Rider_Id_177</td>\n",
       "      <td>2128</td>\n",
       "      <td>526</td>\n",
       "      <td>286</td>\n",
       "      <td>13.6</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21201 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Order No       User Id Vehicle Type  Platform Type  \\\n",
       "0       Order_No_4211   User_Id_633         Bike              3   \n",
       "1      Order_No_25375  User_Id_2285         Bike              3   \n",
       "2       Order_No_1899   User_Id_265         Bike              3   \n",
       "3       Order_No_9336  User_Id_1402         Bike              3   \n",
       "4      Order_No_27883  User_Id_1737         Bike              1   \n",
       "...               ...           ...          ...            ...   \n",
       "21196   Order_No_8834  User_Id_2001         Bike              3   \n",
       "21197  Order_No_22892  User_Id_1796         Bike              3   \n",
       "21198   Order_No_2831  User_Id_2956         Bike              3   \n",
       "21199   Order_No_6174  User_Id_2524         Bike              1   \n",
       "21200   Order_No_9836   User_Id_718         Bike              3   \n",
       "\n",
       "      Personal or Business  Placement - Day of Month  \\\n",
       "0                 Business                         9   \n",
       "1                 Personal                        12   \n",
       "2                 Business                        30   \n",
       "3                 Business                        15   \n",
       "4                 Personal                        13   \n",
       "...                    ...                       ...   \n",
       "21196             Personal                        20   \n",
       "21197             Business                        13   \n",
       "21198             Business                         7   \n",
       "21199             Personal                         4   \n",
       "21200             Business                        26   \n",
       "\n",
       "       Placement - Weekday (Mo = 1) Placement - Time  \\\n",
       "0                                 5       9:35:46 AM   \n",
       "1                                 5      11:16:16 AM   \n",
       "2                                 2      12:39:25 PM   \n",
       "3                                 5       9:25:34 AM   \n",
       "4                                 1       9:55:18 AM   \n",
       "...                             ...              ...   \n",
       "21196                             3       3:54:38 PM   \n",
       "21197                             6      10:13:34 AM   \n",
       "21198                             4       5:06:16 PM   \n",
       "21199                             3       9:31:39 AM   \n",
       "21200                             2       2:19:47 PM   \n",
       "\n",
       "       Confirmation - Day of Month  Confirmation - Weekday (Mo = 1)  ...  \\\n",
       "0                                9                                5  ...   \n",
       "1                               12                                5  ...   \n",
       "2                               30                                2  ...   \n",
       "3                               15                                5  ...   \n",
       "4                               13                                1  ...   \n",
       "...                            ...                              ...  ...   \n",
       "21196                           20                                3  ...   \n",
       "21197                           13                                6  ...   \n",
       "21198                            7                                4  ...   \n",
       "21199                            4                                3  ...   \n",
       "21200                           26                                2  ...   \n",
       "\n",
       "      Pickup Lat  Pickup Long  Destination Lat Destination Long      Rider Id  \\\n",
       "0      -1.317755    36.830370        -1.300406        36.829741  Rider_Id_432   \n",
       "1      -1.351453    36.899315        -1.295004        36.814358  Rider_Id_856   \n",
       "2      -1.308284    36.843419        -1.300921        36.828195  Rider_Id_155   \n",
       "3      -1.281301    36.832396        -1.257147        36.795063  Rider_Id_855   \n",
       "4      -1.266597    36.792118        -1.295041        36.809817  Rider_Id_770   \n",
       "...          ...          ...              ...              ...           ...   \n",
       "21196  -1.258414    36.804800        -1.275285        36.802702  Rider_Id_953   \n",
       "21197  -1.307143    36.825009        -1.331619        36.847976  Rider_Id_155   \n",
       "21198  -1.286018    36.897534        -1.258414        36.804800  Rider_Id_697   \n",
       "21199  -1.250030    36.874167        -1.279209        36.794872  Rider_Id_347   \n",
       "21200  -1.255189    36.782203        -1.320157        36.830887  Rider_Id_177   \n",
       "\n",
       "       Time from Pickup to Arrival No_Of_Orders   Age  Average_Rating  \\\n",
       "0                              745         1637  1309            13.8   \n",
       "1                             1993          396   339            13.6   \n",
       "2                              455         1023   242            12.5   \n",
       "3                             1341          886   283            14.5   \n",
       "4                             1214         2311   872            14.1   \n",
       "...                            ...          ...   ...             ...   \n",
       "21196                            9         1270   367            14.4   \n",
       "21197                          770         1023   242            12.5   \n",
       "21198                         2953          314   473            13.8   \n",
       "21199                         1380         2451  1821            14.0   \n",
       "21200                         2128          526   286            13.6   \n",
       "\n",
       "      No_of_Ratings  \n",
       "0               549  \n",
       "1                69  \n",
       "2               114  \n",
       "3               113  \n",
       "4               533  \n",
       "...             ...  \n",
       "21196           131  \n",
       "21197           114  \n",
       "21198            29  \n",
       "21199          1101  \n",
       "21200            70  \n",
       "\n",
       "[21201 rows x 33 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#in merging the two dataframes into one using a comon field\n",
    "\n",
    "sendy=train.merge(riders,how='left',on='Rider Id')\n",
    "sendy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "from the table we can see the number of columns have increased to 33 due to the merge of the train and riders dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}