trip_data_processing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Process to import Boris Bike Trip Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data has been downloaded from the [Transport for London TFL Open Data Website](https://tfl.gov.uk/info-for/open-data-users/) into a folder called `bike_data` from where I will import, process and consolidate the data into a single Pandas dataframe."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Import Libraries**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import MySQLdb\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Get file names of downloaded Trip Data**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from os import walk\n",
    "\n",
    "f = []\n",
    "for (dirpath, dirnames, filenames) in walk('bike_data'):\n",
    "    f.extend(filenames)\n",
    "    break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Deal with a few rogue files**\n",
    "\n",
    "The 4 files referenced below have 3 extra columns at the end and a couple of them have a load of blank records at the bottom that I'm excluding by manually setting the number of rows to import."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "x = pd.read_csv('bike_data/10b. Journey Data Extract 28Sep14-11Oct14.csv', nrows=430382, usecols=[0, 1, 2, 3, 4, 5, 6, 7, 8])\n",
    "x.to_csv('bike_data/10b. Journey Data Extract 28Sep14-11Oct14.csv', index = False)\n",
    "\n",
    "x = pd.read_csv('bike_data/4. Journey Data Extract 01Apr14-26Apr14.csv', usecols=[0, 1, 2, 3, 4, 5, 6, 7, 8])\n",
    "x.to_csv('bike_data/4. Journey Data Extract 01Apr14-26Apr14.csv', index = False)\n",
    "\n",
    "x = pd.read_csv('bike_data/6. Journey Data Extract 25May14-21Jun14.csv', usecols=[0, 1, 2, 3, 4, 5, 6, 7, 8])\n",
    "x.to_csv('bike_data/6. Journey Data Extract 25May14-21Jun14.csv', index = False)\n",
    "\n",
    "x = pd.read_csv('bike_data/13b. Journey Data Extract 22Dec14-03Jan15.csv', nrows=174385, usecols=[0, 1, 2, 3, 4, 5, 6, 7, 8])\n",
    "x.to_csv('bike_data/13b. Journey Data Extract 22Dec14-03Jan15.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Loop through files, import the csv and add to master df**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12aJourneyDataExtract15Nov15-27Nov15.csv 2 of 79\n",
      "2. Journey Data Extract 03Feb14-01Mar14.csv 3 of 79\n",
      "13a. Journey Data Extract 07Dec14-21Dec14.csv 4 of 79\n",
      "8. Journey Data Extract 23Jun-20Jul13.csv 5 of 79\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/jb/anaconda/lib/python2.7/site-packages/IPython/core/interactiveshell.py:2723: DtypeWarning: Columns (7) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6. Journey Data Extract_27May-23Jun12.csv 6 of 79\n",
      "7. Journey Data Extract_24Jun-21Jul12.csv 7 of 79\n",
      "02bJourneyDataExtract21Feb16-05Mar2016.csv 8 of 79\n",
      "11b. Journey Data Extract 12Oct14-08Nov14.csv 9 of 79\n",
      "8b-Journey-Data-Extract-08Aug15-22Aug15.csv 10 of 79\n",
      "11a-Journey-Data-Extract-18Oct15-31Oct15.csv 11 of 79\n",
      "13bJourneyDataExtract25Dec15-09Jan16.csv 12 of 79\n",
      "5b. Journey Data Extract 17May15-30May15.csv 13 of 79\n",
      "6. Journey Data Extract 25May14-21Jun14.csv 14 of 79\n",
      "5. Journey Data Extract 27Apr14-24May14.csv 15 of 79\n",
      "2. Journey Data Extract_01Feb-29Feb 12.csv 16 of 79\n",
      "4a. Journey Data Extract 01Apr15-16Apr15.csv 17 of 79\n",
      "6. Journey Data Extract 28Apr-25May13.csv 18 of 79\n",
      "9b-Journey-Data-Extract-06Sep15-19Sep15.csv 19 of 79\n",
      "3a. Journey Data Extract 01Mar15-15Mar15.csv 20 of 79\n",
      "1. Journey Data Extract 04Jan-31Jan 12.csv 21 of 79\n",
      "8b Journey Data Extract 01Aug14-16Aug14.csv 22 of 79\n",
      "11. Journey Data Extract 14Sep13-12Oct13.csv 23 of 79\n",
      "5. Journey Data Extract_29Apr-26May12.csv 24 of 79\n",
      "2. Journey Data Extract 06Jan-02Feb13.csv 25 of 79\n",
      "12. Journey Data Extract 26Aug-27 Aug12.csv 26 of 79\n",
      "2a. Journey Data Extract 01Feb15-14Feb15.csv 27 of 79\n",
      "9b Journey Data Extract 01Sep14-13Sep14.csv 28 of 79\n",
      "11a. Journey Data Extract 12Oct14-08Nov14.csv 29 of 79\n",
      "8. Journey Data Extract 22Jul-18Aug12.csv 30 of 79\n",
      "8a Journey Data Extract 20Jul14-31Jul14.csv 31 of 79\n",
      "9a-Journey-Data-Extract-23Aug15-05Sep15.csv 32 of 79\n",
      "1b. Journey Data Extract 18Jan15-31Jan15.csv 33 of 79\n",
      "1a. Journey Data Extract 04Jan15-17Jan15.csv 34 of 79\n",
      "1. Journey Data Extract 01Jan-05Jan13.csv 35 of 79\n",
      "3b. Journey Data Extract 16Mar15-31Mar15.csv 36 of 79\n",
      "5. Journey Data Extract 01Apr-27Apr13.csv 37 of 79\n",
      "3. Journey Data Extract 03Feb-02Mar13.csv 38 of 79\n",
      "11. Journey Data Extract 23Aug-25 Aug12.csv 39 of 79\n",
      "12bJourneyDataExtract28Nov15-12Dec15.csv 40 of 79\n",
      "10b. Journey Data Extract 28Sep14-11Oct14.csv 41 of 79\n",
      "13. Journey Data Extract 28Aug-29 Aug12.csv 42 of 79\n",
      "11b-Journey-Data-Extract-01Nov15-14Nov15.csv 43 of 79\n",
      "4. Journey Data Extract 01Apr14-26Apr14.csv 44 of 79\n",
      "10b-Journey-Data-Extract-04Oct15-17Oct15.csv 45 of 79\n",
      "6b Journey Data Extract 13Jun15-27Jun15.csv 46 of 79\n",
      "12a. Journey Data Extract 09Nov14-06Dec14.csv 47 of 79\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/jb/anaconda/lib/python2.7/site-packages/IPython/core/interactiveshell.py:2723: DtypeWarning: Columns (3,5,6,8) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9a Journey Data Extract 17Aug14-31Aug14.csv 48 of 79\n",
      "02aJourneyDataExtract07Fe16-20Feb2016.csv 49 of 79\n",
      "17. Journey Data Extract 01Nov-30Nov12.csv 50 of 79\n",
      "7b. Journey Data Extract 12Jul15-25Jul15.csv 51 of 79\n",
      "7. Journey Data Extract 22Jun14-19Jul14.csv 52 of 79\n",
      "16. Journey Data Extract 01Oct-31Oct12.csv 53 of 79\n",
      "13aJourneyDataExtract13Dec15-24Dec15.csv 54 of 79\n",
      "3. Journey Data Extract_01Mar-31Mar12.csv 55 of 79\n",
      "12b. Journey Data Extract 09Nov14-06Dec14.csv 56 of 79\n",
      "18. Journey Data Extract 01Dec-31Dec12.csv 57 of 79\n",
      "15. Journey Data Extract 01Sep-30Sep12.csv 58 of 79\n",
      "14. Journey Data Extract 08Dec13-04Jan14.csv 59 of 79\n",
      "7a. Journey Data Extract 28Jun15-11Jul15.csv 60 of 79\n",
      "10. Journey Data Extract 21Aug-22 Aug12.csv 61 of 79\n",
      "10a-Journey-Data-Extract-20Sep15-03Oct15.csv 62 of 79\n",
      "8a-Journey-Data-Extract-26Jul15-07Aug15.csv 63 of 79\n",
      "6a Journey Data Extract 31May15-12Jun15.csv 64 of 79\n",
      "10a. Journey Data Extract 14Sep14-27Sep14.csv 65 of 79\n",
      "5a. Journey Data Extract 03May15-16May15.csv 66 of 79\n",
      "1. Journey Data Extract 05Jan14-02Feb14.csv 67 of 79\n",
      "10. Journey Data Extract 18Aug-13Sep13.csv 68 of 79\n",
      "14. Journey Data Extract 30Aug-31 Aug12.csv 69 of 79\n",
      "7. Journey Data Extract 26May-22Jun13.csv 70 of 79\n",
      "9. Journey Data Extract 21Jul-17Aug13.csv 71 of 79\n",
      "4. Journey Data Extract 03Mar-31Mar13.csv 72 of 79\n",
      "12. Journey Data Extract 13Oct13-09Nov13.csv 73 of 79\n",
      "3. Journey Data Extract 02Mar14-31Mar14.csv 74 of 79\n",
      "13b. Journey Data Extract 22Dec14-03Jan15.csv 75 of 79\n",
      "4. Journey Data Extract_1Apr-28Apr12.csv 76 of 79\n",
      "13. Journey Data Extract 10Nov13-07Dec13.csv 77 of 79\n",
      "9. Journey Data Extract 19Aug-20 Aug12.csv 78 of 79\n",
      "4b. Journey Data Extract 17Apr15-02May15.csv 79 of 79\n"
     ]
    }
   ],
   "source": [
    "counter = 0\n",
    "for file in f:\n",
    "    counter += 1\n",
    "    try:\n",
    "        x = pd.read_csv('bike_data/' + file)\n",
    "        x = x.dropna()\n",
    "        master = pd.concat([master, x], axis = 0)\n",
    "        print file + ' ' + str(counter) + ' of ' + str(len(f))\n",
    "    except:\n",
    "        master = pd.read_csv('bike_data/' + file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Quick Inspection of the Data**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data Frame of 37267718 rows and 9 columns: \n",
      "\n",
      "Rental Id            float64\n",
      "Duration             float64\n",
      "Bike Id              float64\n",
      "End Date              object\n",
      "EndStation Id        float64\n",
      "EndStation Name       object\n",
      "Start Date            object\n",
      "StartStation Id       object\n",
      "StartStation Name     object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print 'Data Frame of ' + str(master.shape[0]) + ' rows and ' + str(master.shape[1]) + ' columns: \\n'\n",
    "print(master.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rental Id</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Bike Id</th>\n",
       "      <th>End Date</th>\n",
       "      <th>EndStation Id</th>\n",
       "      <th>EndStation Name</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>StartStation Id</th>\n",
       "      <th>StartStation Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>41215296</td>\n",
       "      <td>120</td>\n",
       "      <td>12077</td>\n",
       "      <td>15/02/2015 00:02</td>\n",
       "      <td>647</td>\n",
       "      <td>Richmond Way, Shepherd's Bush</td>\n",
       "      <td>15/02/2015 00:00</td>\n",
       "      <td>613</td>\n",
       "      <td>Woodstock Grove, Shepherd's Bush</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>41215294</td>\n",
       "      <td>120</td>\n",
       "      <td>7892</td>\n",
       "      <td>15/02/2015 00:02</td>\n",
       "      <td>647</td>\n",
       "      <td>Richmond Way, Shepherd's Bush</td>\n",
       "      <td>15/02/2015 00:00</td>\n",
       "      <td>613</td>\n",
       "      <td>Woodstock Grove, Shepherd's Bush</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>41215295</td>\n",
       "      <td>1140</td>\n",
       "      <td>3903</td>\n",
       "      <td>15/02/2015 00:19</td>\n",
       "      <td>469</td>\n",
       "      <td>Lindfield Street, Poplar</td>\n",
       "      <td>15/02/2015 00:00</td>\n",
       "      <td>448</td>\n",
       "      <td>Fishermans Walk West, Canary Wharf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>41215303</td>\n",
       "      <td>720</td>\n",
       "      <td>5719</td>\n",
       "      <td>15/02/2015 00:13</td>\n",
       "      <td>487</td>\n",
       "      <td>Canton Street, Poplar</td>\n",
       "      <td>15/02/2015 00:01</td>\n",
       "      <td>200</td>\n",
       "      <td>LMU Commercial Road, Whitechapel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>41215298</td>\n",
       "      <td>180</td>\n",
       "      <td>3695</td>\n",
       "      <td>15/02/2015 00:04</td>\n",
       "      <td>321</td>\n",
       "      <td>Bermondsey Street, Bermondsey</td>\n",
       "      <td>15/02/2015 00:01</td>\n",
       "      <td>269</td>\n",
       "      <td>Empire Square, The Borough</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Rental Id  Duration  Bike Id          End Date  EndStation Id  \\\n",
       "0   41215296       120    12077  15/02/2015 00:02            647   \n",
       "1   41215294       120     7892  15/02/2015 00:02            647   \n",
       "2   41215295      1140     3903  15/02/2015 00:19            469   \n",
       "3   41215303       720     5719  15/02/2015 00:13            487   \n",
       "4   41215298       180     3695  15/02/2015 00:04            321   \n",
       "\n",
       "                 EndStation Name        Start Date StartStation Id  \\\n",
       "0  Richmond Way, Shepherd's Bush  15/02/2015 00:00             613   \n",
       "1  Richmond Way, Shepherd's Bush  15/02/2015 00:00             613   \n",
       "2       Lindfield Street, Poplar  15/02/2015 00:00             448   \n",
       "3          Canton Street, Poplar  15/02/2015 00:01             200   \n",
       "4  Bermondsey Street, Bermondsey  15/02/2015 00:01             269   \n",
       "\n",
       "                    StartStation Name  \n",
       "0    Woodstock Grove, Shepherd's Bush  \n",
       "1    Woodstock Grove, Shepherd's Bush  \n",
       "2  Fishermans Walk West, Canary Wharf  \n",
       "3    LMU Commercial Road, Whitechapel  \n",
       "4          Empire Square, The Borough  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "**Inital conclusions on the Data **\n",
    "\n",
    "_Problems_\n",
    "* Start and End date need to be converted to datetime (tried this on import, it was crazy slow).\n",
    "* That `StartStation Id` is an object is suspcious as they appear to be numerical.\n",
    "* A quick group by on the Start/End Station Names tells us that there is a fair but of inconsistency amongst them over time.\n",
    "* There's also a few mangled records with strange durations and start times."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rental Id</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Bike Id</th>\n",
       "      <th>End Date</th>\n",
       "      <th>EndStation Id</th>\n",
       "      <th>EndStation Name</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>StartStation Id</th>\n",
       "      <th>StartStation Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20783</th>\n",
       "      <td>12542513</td>\n",
       "      <td>749724</td>\n",
       "      <td>8999</td>\n",
       "      <td>05/06/2012 12:06:16</td>\n",
       "      <td>434</td>\n",
       "      <td>6,0</td>\n",
       "      <td>22/03/1901 00:00:00</td>\n",
       "      <td>200054</td>\n",
       "      <td>Cubitt Town,0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46980</th>\n",
       "      <td>12907812</td>\n",
       "      <td>731501</td>\n",
       "      <td>8731</td>\n",
       "      <td>18/06/2012 11:05:46</td>\n",
       "      <td>434</td>\n",
       "      <td>6,0</td>\n",
       "      <td>20/07/1900 00:00:00</td>\n",
       "      <td>1102</td>\n",
       "      <td>Aldgate,0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120353</th>\n",
       "      <td>13207448</td>\n",
       "      <td>246794</td>\n",
       "      <td>7981</td>\n",
       "      <td>23/06/2012 09:28:10</td>\n",
       "      <td>434</td>\n",
       "      <td>6,0</td>\n",
       "      <td>03/03/1901 00:00:00</td>\n",
       "      <td>22179</td>\n",
       "      <td>South Kensington,0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180334</th>\n",
       "      <td>12593058</td>\n",
       "      <td>573308</td>\n",
       "      <td>7352</td>\n",
       "      <td>05/06/2012 11:15:16</td>\n",
       "      <td>434</td>\n",
       "      <td>6,0</td>\n",
       "      <td>15/02/1900 00:00:00</td>\n",
       "      <td>1031</td>\n",
       "      <td>Wapping,0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222113</th>\n",
       "      <td>12767871</td>\n",
       "      <td>335562</td>\n",
       "      <td>6938</td>\n",
       "      <td>07/06/2012 11:50:00</td>\n",
       "      <td>434</td>\n",
       "      <td>6,0</td>\n",
       "      <td>01/12/1900 00:00:00</td>\n",
       "      <td>1212</td>\n",
       "      <td>South Bank,1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Rental Id  Duration  Bike Id             End Date  EndStation Id  \\\n",
       "20783    12542513    749724     8999  05/06/2012 12:06:16            434   \n",
       "46980    12907812    731501     8731  18/06/2012 11:05:46            434   \n",
       "120353   13207448    246794     7981  23/06/2012 09:28:10            434   \n",
       "180334   12593058    573308     7352  05/06/2012 11:15:16            434   \n",
       "222113   12767871    335562     6938  07/06/2012 11:50:00            434   \n",
       "\n",
       "       EndStation Name           Start Date StartStation Id  \\\n",
       "20783              6,0  22/03/1901 00:00:00          200054   \n",
       "46980              6,0  20/07/1900 00:00:00            1102   \n",
       "120353             6,0  03/03/1901 00:00:00           22179   \n",
       "180334             6,0  15/02/1900 00:00:00            1031   \n",
       "222113             6,0  01/12/1900 00:00:00            1212   \n",
       "\n",
       "          StartStation Name  \n",
       "20783         Cubitt Town,0  \n",
       "46980             Aldgate,0  \n",
       "120353   South Kensington,0  \n",
       "180334            Wapping,0  \n",
       "222113         South Bank,1  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master.loc[master['EndStation Name'] == '6,0'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Conclusion**\n",
    "\n",
    "Records with an EndStation Name of `6,0` seem to be mangled - the good news is that there aren't many of them so I will simply exclude. \n",
    "\n",
    "The non numeric start id is caused by a handful of records with the value 'Tabletop1' which I will also exclude.\n",
    "\n",
    "Duration also seems to have been rounded in the later files rendering the field completely useless, I will calculate it manually."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#remove the '6,0' records\n",
    "master = master[master['EndStation Name'] != '6,0']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#select relevant columns and improve names\n",
    "data = master[['Rental Id', 'Bike Id', 'StartStation Id', 'Start Date', 'EndStation Id', 'End Date']]\n",
    "data.columns = ['rental_id', 'bike_id', 'start_id', 'start_time', 'end_id', 'end_time']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rental_id</th>\n",
       "      <th>bike_id</th>\n",
       "      <th>start_id</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_id</th>\n",
       "      <th>end_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>41215296</td>\n",
       "      <td>12077</td>\n",
       "      <td>613</td>\n",
       "      <td>15/02/2015 00:00</td>\n",
       "      <td>647</td>\n",
       "      <td>15/02/2015 00:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>41215294</td>\n",
       "      <td>7892</td>\n",
       "      <td>613</td>\n",
       "      <td>15/02/2015 00:00</td>\n",
       "      <td>647</td>\n",
       "      <td>15/02/2015 00:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>41215295</td>\n",
       "      <td>3903</td>\n",
       "      <td>448</td>\n",
       "      <td>15/02/2015 00:00</td>\n",
       "      <td>469</td>\n",
       "      <td>15/02/2015 00:19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>41215303</td>\n",
       "      <td>5719</td>\n",
       "      <td>200</td>\n",
       "      <td>15/02/2015 00:01</td>\n",
       "      <td>487</td>\n",
       "      <td>15/02/2015 00:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>41215298</td>\n",
       "      <td>3695</td>\n",
       "      <td>269</td>\n",
       "      <td>15/02/2015 00:01</td>\n",
       "      <td>321</td>\n",
       "      <td>15/02/2015 00:04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rental_id  bike_id start_id        start_time  end_id          end_time\n",
       "0   41215296    12077      613  15/02/2015 00:00     647  15/02/2015 00:02\n",
       "1   41215294     7892      613  15/02/2015 00:00     647  15/02/2015 00:02\n",
       "2   41215295     3903      448  15/02/2015 00:00     469  15/02/2015 00:19\n",
       "3   41215303     5719      200  15/02/2015 00:01     487  15/02/2015 00:13\n",
       "4   41215298     3695      269  15/02/2015 00:01     321  15/02/2015 00:04"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Get rid of the weird tabletop records**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data = data[data['start_id'] != 'Tabletop1']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Convert columns to appropriate data types**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data['start_id'] = pd.to_numeric(data['start_id'], errors='ignore')\n",
    "data['start_time'] = pd.to_datetime(data['start_time'], dayfirst = True)\n",
    "data['end_time'] = pd.to_datetime(data['end_time'], dayfirst = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "start_time\n",
       "1900-01-02        1\n",
       "1900-01-04        2\n",
       "1900-01-05        1\n",
       "1900-01-08        1\n",
       "1900-01-09        3\n",
       "1900-01-12        1\n",
       "1900-01-14        6\n",
       "1900-01-15        1\n",
       "1900-01-16        1\n",
       "1900-01-17        2\n",
       "1900-01-18        5\n",
       "1900-01-22        1\n",
       "1900-01-23        1\n",
       "1900-01-24        1\n",
       "1900-01-25        1\n",
       "1900-01-27        1\n",
       "1900-01-28        2\n",
       "1900-01-29        4\n",
       "1900-01-31        2\n",
       "1900-02-01        5\n",
       "1900-02-02        6\n",
       "1900-02-03        1\n",
       "1900-02-05        2\n",
       "1900-02-06        3\n",
       "1900-02-07        1\n",
       "1900-02-08        9\n",
       "1900-02-09        3\n",
       "1900-02-10        3\n",
       "1900-02-12        3\n",
       "1900-02-13        4\n",
       "              ...  \n",
       "2016-01-08    20761\n",
       "2016-01-09    11583\n",
       "2016-02-07    14101\n",
       "2016-02-08    19850\n",
       "2016-02-09    23306\n",
       "2016-02-10    23626\n",
       "2016-02-11    25184\n",
       "2016-02-12    22819\n",
       "2016-02-13     9868\n",
       "2016-02-14    13697\n",
       "2016-02-15    21787\n",
       "2016-02-16    24386\n",
       "2016-02-17    19033\n",
       "2016-02-18    23516\n",
       "2016-02-19    22172\n",
       "2016-02-20    11135\n",
       "2016-02-21    14669\n",
       "2016-02-22    20463\n",
       "2016-02-23    25655\n",
       "2016-02-24    25480\n",
       "2016-02-25    24173\n",
       "2016-02-26    23578\n",
       "2016-02-27    14745\n",
       "2016-02-28    13451\n",
       "2016-02-29    23579\n",
       "2016-03-01    20702\n",
       "2016-03-02    20634\n",
       "2016-03-03    24415\n",
       "2016-03-04    23966\n",
       "2016-03-05    12214\n",
       "Name: rental_id, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.groupby(data.start_time.dt.date).count()['rental_id']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**'Pickle' dataframe for future use**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def lookup(s):\n",
    "    \"\"\"\n",
    "    This is an extremely fast approach to datetime parsing.\n",
    "    For large data, the same dates are often repeated. Rather than\n",
    "    re-parse these, we store all unique dates, parse them, and\n",
    "    use a lookup to convert all dates.\n",
    "    Source: http://stackoverflow.com/questions/29882573/pandas-slow-date-conversion\n",
    "    \"\"\"\n",
    "    dates = {date:pd.to_datetime(date, dayfirst = True) for date in s.unique()}\n",
    "    return s.apply(lambda v: dates[v])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data['start_time'] = lookup(data['start_time'])\n",
    "data['end_time'] = lookup(data['end_time'])\n",
    "data = data.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "rental_id            float64\n",
       "bike_id              float64\n",
       "start_id              object\n",
       "start_time    datetime64[ns]\n",
       "end_id               float64\n",
       "end_time      datetime64[ns]\n",
       "dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data.to_pickle('consol_data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}