Zindi transport problem.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#importing the relevant libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn.ensemble import RandomForestClassifier, RandomForestRegressor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#importing the training data\n",
    "data= pd.read_csv('train_revised.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#importing the test data\n",
    "train=pd.read_csv('test_questions.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>ride_id</th>\n",
       "      <th>seat_number</th>\n",
       "      <th>payment_method</th>\n",
       "      <th>payment_receipt</th>\n",
       "      <th>travel_date</th>\n",
       "      <th>travel_time</th>\n",
       "      <th>travel_from</th>\n",
       "      <th>travel_to</th>\n",
       "      <th>car_type</th>\n",
       "      <th>max_capacity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1442</td>\n",
       "      <td>15A</td>\n",
       "      <td>Mpesa</td>\n",
       "      <td>UZUEHCBUSO</td>\n",
       "      <td>17-10-17</td>\n",
       "      <td>7:15</td>\n",
       "      <td>Migori</td>\n",
       "      <td>Nairobi</td>\n",
       "      <td>Bus</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5437</td>\n",
       "      <td>14A</td>\n",
       "      <td>Mpesa</td>\n",
       "      <td>TIHLBUSGTE</td>\n",
       "      <td>19-11-17</td>\n",
       "      <td>7:12</td>\n",
       "      <td>Migori</td>\n",
       "      <td>Nairobi</td>\n",
       "      <td>Bus</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5710</td>\n",
       "      <td>8B</td>\n",
       "      <td>Mpesa</td>\n",
       "      <td>EQX8Q5G19O</td>\n",
       "      <td>26-11-17</td>\n",
       "      <td>7:05</td>\n",
       "      <td>Keroka</td>\n",
       "      <td>Nairobi</td>\n",
       "      <td>Bus</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5777</td>\n",
       "      <td>19A</td>\n",
       "      <td>Mpesa</td>\n",
       "      <td>SGP18CL0ME</td>\n",
       "      <td>27-11-17</td>\n",
       "      <td>7:10</td>\n",
       "      <td>Homa Bay</td>\n",
       "      <td>Nairobi</td>\n",
       "      <td>Bus</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5778</td>\n",
       "      <td>11A</td>\n",
       "      <td>Mpesa</td>\n",
       "      <td>BM97HFRGL9</td>\n",
       "      <td>27-11-17</td>\n",
       "      <td>7:12</td>\n",
       "      <td>Migori</td>\n",
       "      <td>Nairobi</td>\n",
       "      <td>Bus</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ride_id seat_number payment_method payment_receipt travel_date travel_time  \\\n",
       "0     1442         15A          Mpesa      UZUEHCBUSO    17-10-17        7:15   \n",
       "1     5437         14A          Mpesa      TIHLBUSGTE    19-11-17        7:12   \n",
       "2     5710          8B          Mpesa      EQX8Q5G19O    26-11-17        7:05   \n",
       "3     5777         19A          Mpesa      SGP18CL0ME    27-11-17        7:10   \n",
       "4     5778         11A          Mpesa      BM97HFRGL9    27-11-17        7:12   \n",
       "\n",
       "  travel_from travel_to car_type  max_capacity  \n",
       "0      Migori   Nairobi      Bus            49  \n",
       "1      Migori   Nairobi      Bus            49  \n",
       "2      Keroka   Nairobi      Bus            49  \n",
       "3    Homa Bay   Nairobi      Bus            49  \n",
       "4      Migori   Nairobi      Bus            49  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id         0\n",
       "travel_date     0\n",
       "travel_time     0\n",
       "travel_from     0\n",
       "travel_to       0\n",
       "car_type        0\n",
       "max_capacity    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#looking for any null entries\n",
    "train.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id            0\n",
       "seat_number        0\n",
       "payment_method     0\n",
       "payment_receipt    0\n",
       "travel_date        0\n",
       "travel_time        0\n",
       "travel_from        0\n",
       "travel_to          0\n",
       "car_type           0\n",
       "max_capacity       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "rides=data['ride_id'].value_counts()\n",
    "rides1=train['ride_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6249,)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rides.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1111,)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rides1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8454     50\n",
       "8090     49\n",
       "10834    49\n",
       "11382    49\n",
       "13211    49\n",
       "Name: ride_id, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rides.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#combining the day and date columns\n",
    "data['daytime']=data['travel_date']+' '+data['travel_time']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "train['daytime']=train['travel_date']+' '+train['travel_time']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#converting to date time\n",
    "data['daytime']=pd.to_datetime(data['daytime'])\n",
    "train['daytime']=pd.to_datetime(train['daytime'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                     int64\n",
       "seat_number                object\n",
       "payment_method             object\n",
       "payment_receipt            object\n",
       "travel_date                object\n",
       "travel_time                object\n",
       "travel_from                object\n",
       "travel_to                  object\n",
       "car_type                   object\n",
       "max_capacity                int64\n",
       "daytime            datetime64[ns]\n",
       "dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                  int64\n",
       "travel_date             object\n",
       "travel_time             object\n",
       "travel_from             object\n",
       "travel_to               object\n",
       "car_type                object\n",
       "max_capacity             int64\n",
       "daytime         datetime64[ns]\n",
       "dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>ride_id</th>\n",
       "      <th>seat_number</th>\n",
       "      <th>payment_method</th>\n",
       "      <th>payment_receipt</th>\n",
       "      <th>travel_date</th>\n",
       "      <th>travel_time</th>\n",
       "      <th>travel_from</th>\n",
       "      <th>travel_to</th>\n",
       "      <th>car_type</th>\n",
       "      <th>max_capacity</th>\n",
       "      <th>daytime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1442</td>\n",
       "      <td>15A</td>\n",
       "      <td>Mpesa</td>\n",
       "      <td>UZUEHCBUSO</td>\n",
       "      <td>17-10-17</td>\n",
       "      <td>7:15</td>\n",
       "      <td>Migori</td>\n",
       "      <td>Nairobi</td>\n",
       "      <td>Bus</td>\n",
       "      <td>49</td>\n",
       "      <td>2017-10-17 07:15:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5437</td>\n",
       "      <td>14A</td>\n",
       "      <td>Mpesa</td>\n",
       "      <td>TIHLBUSGTE</td>\n",
       "      <td>19-11-17</td>\n",
       "      <td>7:12</td>\n",
       "      <td>Migori</td>\n",
       "      <td>Nairobi</td>\n",
       "      <td>Bus</td>\n",
       "      <td>49</td>\n",
       "      <td>2017-11-19 07:12:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5710</td>\n",
       "      <td>8B</td>\n",
       "      <td>Mpesa</td>\n",
       "      <td>EQX8Q5G19O</td>\n",
       "      <td>26-11-17</td>\n",
       "      <td>7:05</td>\n",
       "      <td>Keroka</td>\n",
       "      <td>Nairobi</td>\n",
       "      <td>Bus</td>\n",
       "      <td>49</td>\n",
       "      <td>2017-11-26 07:05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5777</td>\n",
       "      <td>19A</td>\n",
       "      <td>Mpesa</td>\n",
       "      <td>SGP18CL0ME</td>\n",
       "      <td>27-11-17</td>\n",
       "      <td>7:10</td>\n",
       "      <td>Homa Bay</td>\n",
       "      <td>Nairobi</td>\n",
       "      <td>Bus</td>\n",
       "      <td>49</td>\n",
       "      <td>2017-11-27 07:10:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5778</td>\n",
       "      <td>11A</td>\n",
       "      <td>Mpesa</td>\n",
       "      <td>BM97HFRGL9</td>\n",
       "      <td>27-11-17</td>\n",
       "      <td>7:12</td>\n",
       "      <td>Migori</td>\n",
       "      <td>Nairobi</td>\n",
       "      <td>Bus</td>\n",
       "      <td>49</td>\n",
       "      <td>2017-11-27 07:12:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ride_id seat_number payment_method payment_receipt travel_date travel_time  \\\n",
       "0     1442         15A          Mpesa      UZUEHCBUSO    17-10-17        7:15   \n",
       "1     5437         14A          Mpesa      TIHLBUSGTE    19-11-17        7:12   \n",
       "2     5710          8B          Mpesa      EQX8Q5G19O    26-11-17        7:05   \n",
       "3     5777         19A          Mpesa      SGP18CL0ME    27-11-17        7:10   \n",
       "4     5778         11A          Mpesa      BM97HFRGL9    27-11-17        7:12   \n",
       "\n",
       "  travel_from travel_to car_type  max_capacity             daytime  \n",
       "0      Migori   Nairobi      Bus            49 2017-10-17 07:15:00  \n",
       "1      Migori   Nairobi      Bus            49 2017-11-19 07:12:00  \n",
       "2      Keroka   Nairobi      Bus            49 2017-11-26 07:05:00  \n",
       "3    Homa Bay   Nairobi      Bus            49 2017-11-27 07:10:00  \n",
       "4      Migori   Nairobi      Bus            49 2017-11-27 07:12:00  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extracting the weekday name\n",
    "data['travel_day']=data['daytime'].dt.weekday_name\n",
    "train['travel_day']=train['daytime'].dt.weekday_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#exracting the hour of travel\n",
    "data['travel_hour']=data['daytime'].dt.hour\n",
    "train['travel_hour']=train['daytime'].dt.hour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7     29205\n",
       "8      4709\n",
       "10     4605\n",
       "19     4369\n",
       "9      3692\n",
       "6      2438\n",
       "5      1515\n",
       "11      620\n",
       "23      492\n",
       "Name: travel_hour, dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['travel_hour'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Wednesday    0.159880\n",
       "Tuesday      0.154787\n",
       "Thursday     0.150934\n",
       "Friday       0.144777\n",
       "Monday       0.134224\n",
       "Sunday       0.132578\n",
       "Saturday     0.122819\n",
       "Name: travel_day, dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['travel_day'].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#droping the payment method column\n",
    "data=data.drop('payment_method',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=data.drop('payment_receipt',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=data.drop('daytime',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "train=train.drop('travel_date',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "train=train.drop('daytime',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=data.drop('seat_number',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=data.drop('car_type',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "train=train.drop('car_type',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=data.drop('travel_to',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "train=train.drop('travel_to',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=data.drop('travel_date',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=data.drop('travel_time',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "train=train.drop('travel_time',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "#creating a tickets column with a value of 1 on each row\n",
    "data['tickets']=1"
   ]
  },
  {
   "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>ride_id</th>\n",
       "      <th>travel_from</th>\n",
       "      <th>max_capacity</th>\n",
       "      <th>travel_day</th>\n",
       "      <th>travel_hour</th>\n",
       "      <th>tickets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1442</td>\n",
       "      <td>Migori</td>\n",
       "      <td>49</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5437</td>\n",
       "      <td>Migori</td>\n",
       "      <td>49</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5710</td>\n",
       "      <td>Keroka</td>\n",
       "      <td>49</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5777</td>\n",
       "      <td>Homa Bay</td>\n",
       "      <td>49</td>\n",
       "      <td>Monday</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5778</td>\n",
       "      <td>Migori</td>\n",
       "      <td>49</td>\n",
       "      <td>Monday</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ride_id travel_from  max_capacity travel_day  travel_hour  tickets\n",
       "0     1442      Migori            49    Tuesday            7        1\n",
       "1     5437      Migori            49     Sunday            7        1\n",
       "2     5710      Keroka            49     Sunday            7        1\n",
       "3     5777    Homa Bay            49     Monday            7        1\n",
       "4     5778      Migori            49     Monday            7        1"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>ride_id</th>\n",
       "      <th>travel_from</th>\n",
       "      <th>max_capacity</th>\n",
       "      <th>travel_day</th>\n",
       "      <th>travel_hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>247</td>\n",
       "      <td>Kisii</td>\n",
       "      <td>49</td>\n",
       "      <td>Monday</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>256</td>\n",
       "      <td>Kisii</td>\n",
       "      <td>11</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>275</td>\n",
       "      <td>Kisii</td>\n",
       "      <td>11</td>\n",
       "      <td>Friday</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>285</td>\n",
       "      <td>Kisii</td>\n",
       "      <td>11</td>\n",
       "      <td>Friday</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>286</td>\n",
       "      <td>Kisii</td>\n",
       "      <td>11</td>\n",
       "      <td>Friday</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ride_id travel_from  max_capacity travel_day  travel_hour\n",
       "0      247       Kisii            49     Monday            7\n",
       "1      256       Kisii            11     Sunday           11\n",
       "2      275       Kisii            11     Friday            5\n",
       "3      285       Kisii            11     Friday            9\n",
       "4      286       Kisii            11     Friday            9"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "49    0.619324\n",
       "11    0.380676\n",
       "Name: max_capacity, dtype: float64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#do a mapping on this... use 1 and 2 for the values.\n",
    "data['max_capacity'].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Kisii        22607\n",
       "Migori        7027\n",
       "Homa Bay      6304\n",
       "Sirare        4449\n",
       "Rongo         3928\n",
       "Kehancha      1909\n",
       "Awendo        1788\n",
       "Kijauri       1031\n",
       "Keroka         988\n",
       "Nyachenge      512\n",
       "Rodi           405\n",
       "Mbita          378\n",
       "Ndhiwa         236\n",
       "Sori            55\n",
       "Keumbu          22\n",
       "Oyugis           5\n",
       "Kendu Bay        1\n",
       "Name: travel_from, dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['travel_from'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>ride_id</th>\n",
       "      <th>travel_from</th>\n",
       "      <th>max_capacity</th>\n",
       "      <th>travel_day</th>\n",
       "      <th>travel_hour</th>\n",
       "      <th>tickets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1442</td>\n",
       "      <td>Migori</td>\n",
       "      <td>49</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5437</td>\n",
       "      <td>Migori</td>\n",
       "      <td>49</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5710</td>\n",
       "      <td>Keroka</td>\n",
       "      <td>49</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5777</td>\n",
       "      <td>Homa Bay</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5778</td>\n",
       "      <td>Migori</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ride_id travel_from  max_capacity  travel_day  travel_hour  tickets\n",
       "0     1442      Migori            49           2            7        1\n",
       "1     5437      Migori            49           7            7        1\n",
       "2     5710      Keroka            49           7            7        1\n",
       "3     5777    Homa Bay            49           1            7        1\n",
       "4     5778      Migori            49           1            7        1"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# mapping the days of travel \n",
    "mapping={'Monday':1, 'Tuesday':2, 'Wednesday':3,'Thursday':4,'Friday':5, 'Saturday':6,'Sunday':7}\n",
    "data['travel_day']=data['travel_day'].map(mapping)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>ride_id</th>\n",
       "      <th>travel_from</th>\n",
       "      <th>max_capacity</th>\n",
       "      <th>travel_day</th>\n",
       "      <th>travel_hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>247</td>\n",
       "      <td>Kisii</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>256</td>\n",
       "      <td>Kisii</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>275</td>\n",
       "      <td>Kisii</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>285</td>\n",
       "      <td>Kisii</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>286</td>\n",
       "      <td>Kisii</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ride_id travel_from  max_capacity  travel_day  travel_hour\n",
       "0      247       Kisii            49           1            7\n",
       "1      256       Kisii            11           7           11\n",
       "2      275       Kisii            11           5            5\n",
       "3      285       Kisii            11           5            9\n",
       "4      286       Kisii            11           5            9"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train['travel_day']=train['travel_day'].map(mapping)\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "#mapping the orign\n",
    "mapping1={'Kisii':1,'Migori':2,'Homa Bay':3,'Sirare':4, 'Rongo':5,'Kehancha':6,'Awendo':7,'Kijauri':8,'Keroka':9,'Nyachenge':10,\n",
    "         'Rodi':11,'Mbita':12,'Ndhiwa':13,'Sori':14,'Keumbu':15,'Oyugis':16,'Kendu Bay':17}\n",
    "data['travel_from']=data['travel_from'].map(mapping1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>ride_id</th>\n",
       "      <th>travel_from</th>\n",
       "      <th>max_capacity</th>\n",
       "      <th>travel_day</th>\n",
       "      <th>travel_hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>247</td>\n",
       "      <td>1</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>256</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>275</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>285</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>286</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ride_id  travel_from  max_capacity  travel_day  travel_hour\n",
       "0      247            1            49           1            7\n",
       "1      256            1            11           7           11\n",
       "2      275            1            11           5            5\n",
       "3      285            1            11           5            9\n",
       "4      286            1            11           5            9"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train['travel_from']=train['travel_from'].map(mapping1)\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "#mapping2={11:1,49:2}\n",
    "#data['max_capacity']=data['max_capacity'].map(mapping2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "#train['max_capacity']=train['max_capacity'].map(mapping2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7     482\n",
       "10    155\n",
       "6      96\n",
       "8      85\n",
       "23     81\n",
       "9      69\n",
       "11     52\n",
       "5      49\n",
       "19     42\n",
       "Name: travel_hour, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train['travel_hour'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "#mapping the travel hour\n",
    "mapping3={7:1,8:2,10:3,19:4,9:5,6:6,5:7,11:8,23:9}\n",
    "data['travel_hour']=data['travel_hour'].map(mapping3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "train['travel_hour']=train['travel_hour'].map(mapping3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>ride_id</th>\n",
       "      <th>travel_from</th>\n",
       "      <th>max_capacity</th>\n",
       "      <th>travel_day</th>\n",
       "      <th>travel_hour</th>\n",
       "      <th>tickets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1442</td>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5437</td>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5710</td>\n",
       "      <td>9</td>\n",
       "      <td>49</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5777</td>\n",
       "      <td>3</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5778</td>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ride_id  travel_from  max_capacity  travel_day  travel_hour  tickets\n",
       "0     1442            2            49           2            1        1\n",
       "1     5437            2            49           7            1        1\n",
       "2     5710            9            49           7            1        1\n",
       "3     5777            3            49           1            1        1\n",
       "4     5778            2            49           1            1        1"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>ride_id</th>\n",
       "      <th>travel_from</th>\n",
       "      <th>max_capacity</th>\n",
       "      <th>travel_day</th>\n",
       "      <th>travel_hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>247</td>\n",
       "      <td>1</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>256</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>275</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>285</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>286</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ride_id  travel_from  max_capacity  travel_day  travel_hour\n",
       "0      247            1            49           1            1\n",
       "1      256            1            11           7            8\n",
       "2      275            1            11           5            7\n",
       "3      285            1            11           5            5\n",
       "4      286            1            11           5            5"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "#aggregating the columns by grouping them by the ride id and origin\n",
    "view1=data.groupby(['ride_id','travel_from'],as_index=False).agg({'max_capacity':'mean', 'travel_day':'mean',\n",
    "                                                  'travel_hour':'mean','tickets':'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "view2=train.groupby(['ride_id','travel_from'],as_index=False).agg({'max_capacity':'mean', 'travel_day':'mean',\n",
    "                                                  'travel_hour':'mean'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "view1=pd.DataFrame(view1)\n",
    "#view1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "#finding the portion of the vehicle that was booked \n",
    "view1['cap']=view1['tickets']/view1['max_capacity']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans=view2['ride_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    247\n",
       "1    256\n",
       "2    275\n",
       "3    285\n",
       "4    286\n",
       "Name: ride_id, dtype: int64"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ans.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "#view2=view2.drop('ride_id', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>ride_id</th>\n",
       "      <th>travel_from</th>\n",
       "      <th>max_capacity</th>\n",
       "      <th>travel_day</th>\n",
       "      <th>travel_hour</th>\n",
       "      <th>tickets</th>\n",
       "      <th>cap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1442</td>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.020408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5437</td>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.020408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5710</td>\n",
       "      <td>9</td>\n",
       "      <td>49</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.020408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5777</td>\n",
       "      <td>3</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0.102041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5778</td>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>0.632653</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ride_id  travel_from  max_capacity  travel_day  travel_hour  tickets  \\\n",
       "0     1442            2            49           2            1        1   \n",
       "1     5437            2            49           7            1        1   \n",
       "2     5710            9            49           7            1        1   \n",
       "3     5777            3            49           1            1        5   \n",
       "4     5778            2            49           1            1       31   \n",
       "\n",
       "        cap  \n",
       "0  0.020408  \n",
       "1  0.020408  \n",
       "2  0.020408  \n",
       "3  0.102041  \n",
       "4  0.632653  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "view1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>ride_id</th>\n",
       "      <th>travel_from</th>\n",
       "      <th>max_capacity</th>\n",
       "      <th>travel_day</th>\n",
       "      <th>travel_hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>247</td>\n",
       "      <td>1</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>256</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>275</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>285</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>286</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ride_id  travel_from  max_capacity  travel_day  travel_hour\n",
       "0      247            1            49           1            1\n",
       "1      256            1            11           7            8\n",
       "2      275            1            11           5            7\n",
       "3      285            1            11           5            5\n",
       "4      286            1            11           5            5"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "view2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "view1=view1.drop('tickets',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "view1=view1.drop('ride_id',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "target=view1.cap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "view1=view1.drop('cap', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "view2=view2.drop('ride_id', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extraction of the values to be used in training the model\n",
    "data=view1.values\n",
    "target=target.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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>travel_from</th>\n",
       "      <th>max_capacity</th>\n",
       "      <th>travel_day</th>\n",
       "      <th>travel_hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   travel_from  max_capacity  travel_day  travel_hour\n",
       "0            1            49           1            1\n",
       "1            1            11           7            8\n",
       "2            1            11           5            7\n",
       "3            1            11           5            5\n",
       "4            1            11           5            5"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "view2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "#view3=view2.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "#view2=view2.drop('ride_id',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "test=view2.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "#istanciating the Random forest regressor, fitting of the model and prediction\n",
    "random_forest = RandomForestRegressor(n_estimators=100)\n",
    "random_forest.fit(data, target)\n",
    "pred = random_forest.predict(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "#creating a dataframe for the predition\n",
    "ans1=pd.DataFrame(pred, columns=['cap'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>cap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.146781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.652464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.200845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.792597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.792597</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        cap\n",
       "0  0.146781\n",
       "1  0.652464\n",
       "2  0.200845\n",
       "3  0.792597\n",
       "4  0.792597"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ans1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "#concatinating the test data with the predicted data\n",
    "new=pd.concat([view2,ans1], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "new2=pd.concat([ans,new], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id         0\n",
       "travel_from     0\n",
       "max_capacity    0\n",
       "travel_day      0\n",
       "travel_hour     0\n",
       "cap             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new2.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>ride_id</th>\n",
       "      <th>travel_from</th>\n",
       "      <th>max_capacity</th>\n",
       "      <th>travel_day</th>\n",
       "      <th>travel_hour</th>\n",
       "      <th>cap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>247</td>\n",
       "      <td>1</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.146781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>256</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>0.652464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>275</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>0.200845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>285</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>0.792597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>286</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>0.792597</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ride_id  travel_from  max_capacity  travel_day  travel_hour       cap\n",
       "0      247            1            49           1            1  0.146781\n",
       "1      256            1            11           7            8  0.652464\n",
       "2      275            1            11           5            7  0.200845\n",
       "3      285            1            11           5            5  0.792597\n",
       "4      286            1            11           5            5  0.792597"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "#rounding off the prediction to whole numbers\n",
    "new2['number of tickets']=round(new2['max_capacity']*new2['cap'],0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "new2=new2.drop('cap',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "#creating a csv file to store your output\n",
    "new2.to_csv('submission2.csv', index=False, sep=',')"
   ]
  }
 ],
 "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}