parse_data_sf_schedule_joining.ipynb

{
 "metadata": {
  "name": "parse_data_sf_schedule_joining"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "import json"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def filter_by(df, key, value): return df[df[key] == value]\n",
      "pd.DataFrame.filter_by = filter_by"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "store = pd.HDFStore('data/san-francisco/data.hd5')\n",
      "pcounts = store['passenger_counts']\n",
      "schedule = store['schedule']\n",
      "stops = store['stops']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "schedule"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "pyout",
       "prompt_number": 46,
       "text": [
        "<class 'pandas.core.frame.DataFrame'>\n",
        "Int64Index: 641063 entries, 0 to 641062\n",
        "Data columns:\n",
        "route_name                641063  non-null values\n",
        "id_trip                   641063  non-null values\n",
        "longitude                 641063  non-null values\n",
        "latitude                  641063  non-null values\n",
        "time_scheduled_arrival    641063  non-null values\n",
        "id_route                  508082  non-null values\n",
        "dtypes: float64(3), int64(1), object(2)"
       ]
      }
     ],
     "prompt_number": 46
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Get id_stop"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "It turns out that the real-time arrivals data has id_stop"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "arrivals = pd.read_csv('data/san-francisco/realtime-arrivals.csv',\n",
      "    usecols=[\n",
      "        'PUBLIC_ROUTE_NAME',\n",
      "        'TRIP_ID',\n",
      "        'NEXTBUS_ARRIVAL_TIME',\n",
      "        'LATITUDE',\n",
      "        'LONGITUDE',\n",
      "        'STOP_ID',\n",
      "        ],\n",
      "    parse_dates=['NEXTBUS_ARRIVAL_TIME']\n",
      "    ).rename(columns=dict(\n",
      "        PUBLIC_ROUTE_NAME='route_name',\n",
      "        TRIP_ID='id_trip',\n",
      "        NEXTBUS_ARRIVAL_TIME='time_arrival',\n",
      "        LATITUDE='latitude',\n",
      "        LONGITUDE='longitude',\n",
      "        STOP_ID='id_stop',\n",
      "    ))\n",
      "\n",
      "store['arrivals'] = arrivals\n",
      "arrivals"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "pyout",
       "prompt_number": 42,
       "text": [
        "<class 'pandas.core.frame.DataFrame'>\n",
        "Int64Index: 520974 entries, 0 to 520973\n",
        "Data columns:\n",
        "route_name      520974  non-null values\n",
        "id_trip         520974  non-null values\n",
        "id_stop         520974  non-null values\n",
        "longitude       520974  non-null values\n",
        "latitude        520974  non-null values\n",
        "time_arrival    520974  non-null values\n",
        "dtypes: float64(2), int64(2), object(2)"
       ]
      }
     ],
     "prompt_number": 42
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "schedule['date'] = schedule.time_scheduled_arrival.apply(lambda t: t.date())\n",
      "arrivals['date'] = arrivals.time_arrival.apply(lambda t: t.date())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 51
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sched_arrivals = pd.merge(arrivals, schedule, \n",
      "    on=['route_name', 'id_trip', 'latitude', 'longitude', 'date'],\n",
      "    how='inner')\n",
      "sched_arrivals"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "pyout",
       "prompt_number": 213,
       "text": [
        "<class 'pandas.core.frame.DataFrame'>\n",
        "Int64Index: 568776 entries, 0 to 568775\n",
        "Data columns:\n",
        "route_name                568776  non-null values\n",
        "id_trip                   568776  non-null values\n",
        "id_stop                   568776  non-null values\n",
        "longitude                 568776  non-null values\n",
        "latitude                  568776  non-null values\n",
        "time_arrival              568776  non-null values\n",
        "date                      568776  non-null values\n",
        "time_scheduled_arrival    568776  non-null values\n",
        "id_route                  425968  non-null values\n",
        "dtypes: float64(3), int64(2), object(4)"
       ]
      }
     ],
     "prompt_number": 213
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# train lines still don't have route ids\n",
      "sched_arrivals.route_name[pd.isnull(sched_arrivals.id_route)].unique()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "pyout",
       "prompt_number": 65,
       "text": [
        "array([L OWL, M, KT, J, N, N OWL, F, L, E, P, NX, T OWL, K OWL, M OWL], dtype=object)"
       ]
      }
     ],
     "prompt_number": 65
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# assume anything more than 4hrs late is bad data\n",
      "sched_arrivals = sched_arrivals[\n",
      "    (sched_arrivals.time_arrival - sched_arrivals.time_scheduled_arrival).apply(\n",
      "    lambda t: np.abs(t.total_seconds())\n",
      "    ) < 4*3600]\n",
      "\n",
      "# drop the rows without a route id and convert to integer\n",
      "sched_arrivals = sched_arrivals[sched_arrivals.id_route.notnull()]\n",
      "sched_arrivals['id_route'] = sched_arrivals.id_route.astype(int)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 214
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sched_arrivals"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "pyout",
       "prompt_number": 215,
       "text": [
        "<class 'pandas.core.frame.DataFrame'>\n",
        "Int64Index: 425481 entries, 0 to 568775\n",
        "Data columns:\n",
        "route_name                425481  non-null values\n",
        "id_trip                   425481  non-null values\n",
        "id_stop                   425481  non-null values\n",
        "longitude                 425481  non-null values\n",
        "latitude                  425481  non-null values\n",
        "time_arrival              425481  non-null values\n",
        "date                      425481  non-null values\n",
        "time_scheduled_arrival    425481  non-null values\n",
        "id_route                  425481  non-null values\n",
        "dtypes: float64(2), int64(3), object(4)"
       ]
      }
     ],
     "prompt_number": 215
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "store['scheduled_arrivals'] = sched_arrivals"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 216
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Get id_trip"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# label the trip by the first scheduled time of the trip\n",
      "sched_arrivals['id_trip_start_time_stop'] = ''\n",
      "\n",
      "for idx, grp in sched_arrivals.sort(['date', 'id_route', 'id_trip', 'time_scheduled_arrival']).groupby(\n",
      "    ['date', 'id_route', 'id_trip']):\n",
      "    t = grp.ix[grp.index[0], 'time_scheduled_arrival']\n",
      "    sched_arrivals.ix[grp.index, 'id_trip_start_time_stop'] = \"{h}{m}_{sid}\".format(\n",
      "        h=t.hour, m=str(t.minute).zfill(2), \n",
      "        sid=grp.ix[grp.index[0], 'id_stop'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 227
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pcounts['date'] = pcounts.date.apply(lambda t: t.date())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# pcounts['id_trip_start_time_stop'] = pcounts.id_trip.astype('str') + '_' + pcounts.id_stop.astype('str')\n",
      "\n",
      "# label the passenger counts the same way (except the id_trip is already setup in HMM form)\n",
      "pcounts['id_trip_start_time_stop'] = ''\n",
      "\n",
      "for idx, grp in pcounts.sort(['date', 'id_route', 'id_trip', 'time_stop']).groupby(\n",
      "    ['date', 'id_route', 'id_trip']):\n",
      "    pcounts.ix[grp.index, 'id_trip_start_time_stop'] = \"{tid}_{sid}\".format(\n",
      "        tid=grp.ix[grp.index[0], 'id_trip'], \n",
      "        sid=grp.ix[grp.index[0], 'id_stop'])\n",
      "    \n",
      "# sched_arrivals['id_trip_pcount'] = sched_arrivals.id_trip_pcount.astype(int)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 233
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sched_pcounts = pd.merge(\n",
      "    sched_arrivals.drop(['id_trip', 'latitude', 'longitude', ], axis=1), #.rename(columns={'id_trip_pcount': 'id_trip'}),\n",
      "    pcounts, \n",
      "    left_on=['date', 'id_route', 'id_trip_start_time_stop', 'id_stop'],\n",
      "    right_on=['date', 'id_route',  'id_trip_start_time_stop', 'id_stop'],\n",
      "    how='inner'\n",
      ")\n",
      "sched_pcounts"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "pyout",
       "prompt_number": 234,
       "text": [
        "<class 'pandas.core.frame.DataFrame'>\n",
        "Int64Index: 64245 entries, 0 to 64244\n",
        "Data columns:\n",
        "route_name                 64245  non-null values\n",
        "id_stop                    64245  non-null values\n",
        "time_arrival               64245  non-null values\n",
        "date                       64245  non-null values\n",
        "time_scheduled_arrival     64245  non-null values\n",
        "id_route                   64245  non-null values\n",
        "id_trip_pcount             64245  non-null values\n",
        "id_trip_start_time_stop    64245  non-null values\n",
        "time_stop                  64245  non-null values\n",
        "time_door_close            64245  non-null values\n",
        "time_pullout               64245  non-null values\n",
        "count_boarding             64245  non-null values\n",
        "count_exiting              64245  non-null values\n",
        "count                      64245  non-null values\n",
        "id_trip                    64245  non-null values\n",
        "dtypes: int64(7), object(8)"
       ]
      }
     ],
     "prompt_number": 234
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "store['counts_w_schedule'] = sched_pcounts.drop(\n",
      "        ['id_trip_start_time_stop', 'id_trip_pcount', 'route_name'], axis=1\n",
      "    ).sort(\n",
      "        ['date', 'id_route', 'id_trip', 'time_scheduled_arrival']\n",
      "    ).set_index(\n",
      "        ['date', 'id_route', 'id_trip', 'id_stop'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 242
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# DEV"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sched_pcounts.sort(['date', 'id_route', 'id_trip', 'time_scheduled_arrival']).set_index(\n",
      "    ['date', 'id_route', 'id_trip', 'id_stop']).head(20)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th>route_name</th>\n",
        "      <th>time_arrival</th>\n",
        "      <th>time_scheduled_arrival</th>\n",
        "      <th>id_trip_pcount</th>\n",
        "      <th>id_trip_start_time_stop</th>\n",
        "      <th>time_stop</th>\n",
        "      <th>time_door_close</th>\n",
        "      <th>time_pullout</th>\n",
        "      <th>count_boarding</th>\n",
        "      <th>count_exiting</th>\n",
        "      <th>count</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>date</th>\n",
        "      <th>id_route</th>\n",
        "      <th>id_trip</th>\n",
        "      <th>id_stop</th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <td rowspan=\"20\" valign=\"top\"><strong>2012-10-01</strong></td>\n",
        "      <td rowspan=\"20\" valign=\"top\"><strong>1</strong></td>\n",
        "      <td rowspan=\"6\" valign=\"top\"><strong>744</strong></td>\n",
        "      <td><strong>3893</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 07:44:19</td>\n",
        "      <td> 2012-10-01 07:44:00.925000</td>\n",
        "      <td> 744</td>\n",
        "      <td> 744_3893</td>\n",
        "      <td> 2012-10-01 07:55:36</td>\n",
        "      <td> 2012-10-01 07:56:53</td>\n",
        "      <td> 2012-10-01 07:57:25</td>\n",
        "      <td> 0</td>\n",
        "      <td>  0</td>\n",
        "      <td>  0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6296</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 07:50:45</td>\n",
        "      <td> 2012-10-01 07:50:00.940000</td>\n",
        "      <td> 744</td>\n",
        "      <td> 744_3893</td>\n",
        "      <td> 2012-10-01 08:02:38</td>\n",
        "      <td> 2012-10-01 08:02:53</td>\n",
        "      <td> 2012-10-01 08:02:53</td>\n",
        "      <td> 3</td>\n",
        "      <td>  0</td>\n",
        "      <td>  6</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4026</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 07:58:52</td>\n",
        "      <td> 2012-10-01 07:58:00.933000</td>\n",
        "      <td> 744</td>\n",
        "      <td> 744_3893</td>\n",
        "      <td> 2012-10-01 08:10:03</td>\n",
        "      <td> 2012-10-01 08:10:24</td>\n",
        "      <td> 2012-10-01 08:10:24</td>\n",
        "      <td> 5</td>\n",
        "      <td>  0</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4027</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:05:11</td>\n",
        "      <td> 2012-10-01 08:03:00.948000</td>\n",
        "      <td> 744</td>\n",
        "      <td> 744_3893</td>\n",
        "      <td> 2012-10-01 08:15:25</td>\n",
        "      <td> 2012-10-01 08:15:59</td>\n",
        "      <td> 2012-10-01 08:15:59</td>\n",
        "      <td> 0</td>\n",
        "      <td>  1</td>\n",
        "      <td> 27</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4028</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:09:39</td>\n",
        "      <td> 2012-10-01 08:08:00.964000</td>\n",
        "      <td> 744</td>\n",
        "      <td> 744_3893</td>\n",
        "      <td> 2012-10-01 08:20:45</td>\n",
        "      <td> 2012-10-01 08:21:08</td>\n",
        "      <td> 2012-10-01 08:21:41</td>\n",
        "      <td> 0</td>\n",
        "      <td>  4</td>\n",
        "      <td> 14</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4015</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:11:48</td>\n",
        "      <td> 2012-10-01 08:10:00.955000</td>\n",
        "      <td> 744</td>\n",
        "      <td> 744_3893</td>\n",
        "      <td> 2012-10-01 08:23:36</td>\n",
        "      <td> 2012-10-01 08:28:36</td>\n",
        "      <td> 2012-10-01 08:28:36</td>\n",
        "      <td> 0</td>\n",
        "      <td>  8</td>\n",
        "      <td>  0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td rowspan=\"6\" valign=\"top\"><strong>814</strong></td>\n",
        "      <td><strong>4015</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:16:04</td>\n",
        "      <td> 2012-10-01 08:14:00.885000</td>\n",
        "      <td> 814</td>\n",
        "      <td> 814_4015</td>\n",
        "      <td> 2012-10-01 08:23:36</td>\n",
        "      <td> 2012-10-01 08:28:36</td>\n",
        "      <td> 2012-10-01 08:28:36</td>\n",
        "      <td> 3</td>\n",
        "      <td>  0</td>\n",
        "      <td>  3</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6314</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:20:06</td>\n",
        "      <td> 2012-10-01 08:17:00.893000</td>\n",
        "      <td> 814</td>\n",
        "      <td> 814_4015</td>\n",
        "      <td> 2012-10-01 08:32:26</td>\n",
        "      <td> 2012-10-01 08:32:26</td>\n",
        "      <td> 2012-10-01 08:32:26</td>\n",
        "      <td> 0</td>\n",
        "      <td>  0</td>\n",
        "      <td> 18</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6312</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:23:59</td>\n",
        "      <td> 2012-10-01 08:21:00.900000</td>\n",
        "      <td> 814</td>\n",
        "      <td> 814_4015</td>\n",
        "      <td> 2012-10-01 08:37:54</td>\n",
        "      <td> 2012-10-01 08:38:27</td>\n",
        "      <td> 2012-10-01 08:38:48</td>\n",
        "      <td> 0</td>\n",
        "      <td>  2</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6311</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:31:08</td>\n",
        "      <td> 2012-10-01 08:27:00.861000</td>\n",
        "      <td> 814</td>\n",
        "      <td> 814_4015</td>\n",
        "      <td> 2012-10-01 08:43:03</td>\n",
        "      <td> 2012-10-01 08:43:31</td>\n",
        "      <td> 2012-10-01 08:43:31</td>\n",
        "      <td> 7</td>\n",
        "      <td>  1</td>\n",
        "      <td> 19</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6295</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:37:28</td>\n",
        "      <td> 2012-10-01 08:34:00.876000</td>\n",
        "      <td> 814</td>\n",
        "      <td> 814_4015</td>\n",
        "      <td> 2012-10-01 08:49:22</td>\n",
        "      <td> 2012-10-01 08:49:40</td>\n",
        "      <td> 2012-10-01 08:50:03</td>\n",
        "      <td> 2</td>\n",
        "      <td>  6</td>\n",
        "      <td> 15</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>3892</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:43:14</td>\n",
        "      <td> 2012-10-01 08:39:00.869000</td>\n",
        "      <td> 814</td>\n",
        "      <td> 814_4015</td>\n",
        "      <td> 2012-10-01 08:56:19</td>\n",
        "      <td> 2012-10-01 09:01:53</td>\n",
        "      <td> 2012-10-01 09:02:25</td>\n",
        "      <td> 0</td>\n",
        "      <td> 10</td>\n",
        "      <td>  0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td rowspan=\"6\" valign=\"top\"><strong>828</strong></td>\n",
        "      <td><strong>4015</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:28:54</td>\n",
        "      <td> 2012-10-01 08:28:00.339000</td>\n",
        "      <td> 828</td>\n",
        "      <td> 828_4015</td>\n",
        "      <td> 2012-10-01 08:17:29</td>\n",
        "      <td> 2012-10-01 08:20:49</td>\n",
        "      <td> 2012-10-01 08:20:49</td>\n",
        "      <td> 1</td>\n",
        "      <td>  0</td>\n",
        "      <td>  1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6314</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:32:41</td>\n",
        "      <td> 2012-10-01 08:31:00.357000</td>\n",
        "      <td> 828</td>\n",
        "      <td> 828_4015</td>\n",
        "      <td> 2012-10-01 08:23:54</td>\n",
        "      <td> 2012-10-01 08:23:54</td>\n",
        "      <td> 2012-10-01 08:23:54</td>\n",
        "      <td> 0</td>\n",
        "      <td>  0</td>\n",
        "      <td> 11</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6312</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:38:04</td>\n",
        "      <td> 2012-10-01 08:36:00.372000</td>\n",
        "      <td> 828</td>\n",
        "      <td> 828_4015</td>\n",
        "      <td> 2012-10-01 08:28:22</td>\n",
        "      <td> 2012-10-01 08:28:43</td>\n",
        "      <td> 2012-10-01 08:29:17</td>\n",
        "      <td> 0</td>\n",
        "      <td>  0</td>\n",
        "      <td> 15</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6311</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:43:33</td>\n",
        "      <td> 2012-10-01 08:41:00.348000</td>\n",
        "      <td> 828</td>\n",
        "      <td> 828_4015</td>\n",
        "      <td> 2012-10-01 08:36:06</td>\n",
        "      <td> 2012-10-01 08:36:13</td>\n",
        "      <td> 2012-10-01 08:36:13</td>\n",
        "      <td> 1</td>\n",
        "      <td>  0</td>\n",
        "      <td> 17</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6295</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:49:52</td>\n",
        "      <td> 2012-10-01 08:48:00.364000</td>\n",
        "      <td> 828</td>\n",
        "      <td> 828_4015</td>\n",
        "      <td> 2012-10-01 08:43:19</td>\n",
        "      <td> 2012-10-01 08:43:32</td>\n",
        "      <td> 2012-10-01 08:43:32</td>\n",
        "      <td> 2</td>\n",
        "      <td>  4</td>\n",
        "      <td> 16</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>3892</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:56:07</td>\n",
        "      <td> 2012-10-01 08:53:00.380000</td>\n",
        "      <td> 828</td>\n",
        "      <td> 828_4015</td>\n",
        "      <td> 2012-10-01 08:48:38</td>\n",
        "      <td> 2012-10-01 08:53:17</td>\n",
        "      <td> 2012-10-01 08:53:53</td>\n",
        "      <td> 0</td>\n",
        "      <td> 12</td>\n",
        "      <td>  0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td rowspan=\"2\" valign=\"top\"><strong>847</strong></td>\n",
        "      <td><strong>3893</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:47:32</td>\n",
        "      <td> 2012-10-01 08:47:00.096000</td>\n",
        "      <td> 847</td>\n",
        "      <td> 847_3893</td>\n",
        "      <td> 2012-10-01 08:56:19</td>\n",
        "      <td> 2012-10-01 09:01:53</td>\n",
        "      <td> 2012-10-01 09:02:25</td>\n",
        "      <td> 0</td>\n",
        "      <td>  0</td>\n",
        "      <td>  0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6296</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td> 2012-10-01 08:53:50</td>\n",
        "      <td> 2012-10-01 08:53:00.105000</td>\n",
        "      <td> 847</td>\n",
        "      <td> 847_3893</td>\n",
        "      <td> 2012-10-01 09:08:10</td>\n",
        "      <td> 2012-10-01 09:08:48</td>\n",
        "      <td> 2012-10-01 09:08:48</td>\n",
        "      <td> 5</td>\n",
        "      <td>  0</td>\n",
        "      <td>  6</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "output_type": "pyout",
       "prompt_number": 237,
       "text": [
        "                                    route_name         time_arrival  \\\n",
        "date       id_route id_trip id_stop                                   \n",
        "2012-10-01 1        744     3893            01  2012-10-01 07:44:19   \n",
        "                            6296            01  2012-10-01 07:50:45   \n",
        "                            4026            01  2012-10-01 07:58:52   \n",
        "                            4027            01  2012-10-01 08:05:11   \n",
        "                            4028            01  2012-10-01 08:09:39   \n",
        "                            4015            01  2012-10-01 08:11:48   \n",
        "                    814     4015            01  2012-10-01 08:16:04   \n",
        "                            6314            01  2012-10-01 08:20:06   \n",
        "                            6312            01  2012-10-01 08:23:59   \n",
        "                            6311            01  2012-10-01 08:31:08   \n",
        "                            6295            01  2012-10-01 08:37:28   \n",
        "                            3892            01  2012-10-01 08:43:14   \n",
        "                    828     4015            01  2012-10-01 08:28:54   \n",
        "                            6314            01  2012-10-01 08:32:41   \n",
        "                            6312            01  2012-10-01 08:38:04   \n",
        "                            6311            01  2012-10-01 08:43:33   \n",
        "                            6295            01  2012-10-01 08:49:52   \n",
        "                            3892            01  2012-10-01 08:56:07   \n",
        "                    847     3893            01  2012-10-01 08:47:32   \n",
        "                            6296            01  2012-10-01 08:53:50   \n",
        "\n",
        "                                         time_scheduled_arrival  id_trip_pcount  \\\n",
        "date       id_route id_trip id_stop                                               \n",
        "2012-10-01 1        744     3893     2012-10-01 07:44:00.925000             744   \n",
        "                            6296     2012-10-01 07:50:00.940000             744   \n",
        "                            4026     2012-10-01 07:58:00.933000             744   \n",
        "                            4027     2012-10-01 08:03:00.948000             744   \n",
        "                            4028     2012-10-01 08:08:00.964000             744   \n",
        "                            4015     2012-10-01 08:10:00.955000             744   \n",
        "                    814     4015     2012-10-01 08:14:00.885000             814   \n",
        "                            6314     2012-10-01 08:17:00.893000             814   \n",
        "                            6312     2012-10-01 08:21:00.900000             814   \n",
        "                            6311     2012-10-01 08:27:00.861000             814   \n",
        "                            6295     2012-10-01 08:34:00.876000             814   \n",
        "                            3892     2012-10-01 08:39:00.869000             814   \n",
        "                    828     4015     2012-10-01 08:28:00.339000             828   \n",
        "                            6314     2012-10-01 08:31:00.357000             828   \n",
        "                            6312     2012-10-01 08:36:00.372000             828   \n",
        "                            6311     2012-10-01 08:41:00.348000             828   \n",
        "                            6295     2012-10-01 08:48:00.364000             828   \n",
        "                            3892     2012-10-01 08:53:00.380000             828   \n",
        "                    847     3893     2012-10-01 08:47:00.096000             847   \n",
        "                            6296     2012-10-01 08:53:00.105000             847   \n",
        "\n",
        "                                    id_trip_start_time_stop            time_stop  \\\n",
        "date       id_route id_trip id_stop                                                \n",
        "2012-10-01 1        744     3893                   744_3893  2012-10-01 07:55:36   \n",
        "                            6296                   744_3893  2012-10-01 08:02:38   \n",
        "                            4026                   744_3893  2012-10-01 08:10:03   \n",
        "                            4027                   744_3893  2012-10-01 08:15:25   \n",
        "                            4028                   744_3893  2012-10-01 08:20:45   \n",
        "                            4015                   744_3893  2012-10-01 08:23:36   \n",
        "                    814     4015                   814_4015  2012-10-01 08:23:36   \n",
        "                            6314                   814_4015  2012-10-01 08:32:26   \n",
        "                            6312                   814_4015  2012-10-01 08:37:54   \n",
        "                            6311                   814_4015  2012-10-01 08:43:03   \n",
        "                            6295                   814_4015  2012-10-01 08:49:22   \n",
        "                            3892                   814_4015  2012-10-01 08:56:19   \n",
        "                    828     4015                   828_4015  2012-10-01 08:17:29   \n",
        "                            6314                   828_4015  2012-10-01 08:23:54   \n",
        "                            6312                   828_4015  2012-10-01 08:28:22   \n",
        "                            6311                   828_4015  2012-10-01 08:36:06   \n",
        "                            6295                   828_4015  2012-10-01 08:43:19   \n",
        "                            3892                   828_4015  2012-10-01 08:48:38   \n",
        "                    847     3893                   847_3893  2012-10-01 08:56:19   \n",
        "                            6296                   847_3893  2012-10-01 09:08:10   \n",
        "\n",
        "                                         time_door_close         time_pullout  \\\n",
        "date       id_route id_trip id_stop                                             \n",
        "2012-10-01 1        744     3893     2012-10-01 07:56:53  2012-10-01 07:57:25   \n",
        "                            6296     2012-10-01 08:02:53  2012-10-01 08:02:53   \n",
        "                            4026     2012-10-01 08:10:24  2012-10-01 08:10:24   \n",
        "                            4027     2012-10-01 08:15:59  2012-10-01 08:15:59   \n",
        "                            4028     2012-10-01 08:21:08  2012-10-01 08:21:41   \n",
        "                            4015     2012-10-01 08:28:36  2012-10-01 08:28:36   \n",
        "                    814     4015     2012-10-01 08:28:36  2012-10-01 08:28:36   \n",
        "                            6314     2012-10-01 08:32:26  2012-10-01 08:32:26   \n",
        "                            6312     2012-10-01 08:38:27  2012-10-01 08:38:48   \n",
        "                            6311     2012-10-01 08:43:31  2012-10-01 08:43:31   \n",
        "                            6295     2012-10-01 08:49:40  2012-10-01 08:50:03   \n",
        "                            3892     2012-10-01 09:01:53  2012-10-01 09:02:25   \n",
        "                    828     4015     2012-10-01 08:20:49  2012-10-01 08:20:49   \n",
        "                            6314     2012-10-01 08:23:54  2012-10-01 08:23:54   \n",
        "                            6312     2012-10-01 08:28:43  2012-10-01 08:29:17   \n",
        "                            6311     2012-10-01 08:36:13  2012-10-01 08:36:13   \n",
        "                            6295     2012-10-01 08:43:32  2012-10-01 08:43:32   \n",
        "                            3892     2012-10-01 08:53:17  2012-10-01 08:53:53   \n",
        "                    847     3893     2012-10-01 09:01:53  2012-10-01 09:02:25   \n",
        "                            6296     2012-10-01 09:08:48  2012-10-01 09:08:48   \n",
        "\n",
        "                                     count_boarding  count_exiting  count  \n",
        "date       id_route id_trip id_stop                                        \n",
        "2012-10-01 1        744     3893                  0              0      0  \n",
        "                            6296                  3              0      6  \n",
        "                            4026                  5              0     20  \n",
        "                            4027                  0              1     27  \n",
        "                            4028                  0              4     14  \n",
        "                            4015                  0              8      0  \n",
        "                    814     4015                  3              0      3  \n",
        "                            6314                  0              0     18  \n",
        "                            6312                  0              2     20  \n",
        "                            6311                  7              1     19  \n",
        "                            6295                  2              6     15  \n",
        "                            3892                  0             10      0  \n",
        "                    828     4015                  1              0      1  \n",
        "                            6314                  0              0     11  \n",
        "                            6312                  0              0     15  \n",
        "                            6311                  1              0     17  \n",
        "                            6295                  2              4     16  \n",
        "                            3892                  0             12      0  \n",
        "                    847     3893                  0              0      0  \n",
        "                            6296                  5              0      6  "
       ]
      }
     ],
     "prompt_number": 237
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sched_arrivals[sched_arrivals.id_route == 1].sort(['date', 'id_route', 'id_trip_pcount', 'id_trip', 'time_scheduled_arrival']).set_index(\n",
      "    ['date', 'id_route', 'id_trip_pcount', 'id_trip', 'id_stop']).ix[(datetime.date(2012,10,1), 1, 744)]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th>route_name</th>\n",
        "      <th>longitude</th>\n",
        "      <th>latitude</th>\n",
        "      <th>time_arrival</th>\n",
        "      <th>time_scheduled_arrival</th>\n",
        "      <th>id_trip_start_time_stop</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>id_trip</th>\n",
        "      <th>id_stop</th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <td rowspan=\"6\" valign=\"top\"><strong>5213107</strong></td>\n",
        "      <td><strong>3893</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.446885</td>\n",
        "      <td> 37.787149</td>\n",
        "      <td> 2012-10-01 07:44:19</td>\n",
        "      <td> 2012-10-01 07:44:00.925000</td>\n",
        "      <td> 744_3893</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6296</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.433669</td>\n",
        "      <td> 37.789839</td>\n",
        "      <td> 2012-10-01 07:50:45</td>\n",
        "      <td> 2012-10-01 07:50:00.940000</td>\n",
        "      <td> 744_3893</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4026</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.420714</td>\n",
        "      <td> 37.792385</td>\n",
        "      <td> 2012-10-01 07:58:52</td>\n",
        "      <td> 2012-10-01 07:58:00.933000</td>\n",
        "      <td> 744_3893</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4027</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.409774</td>\n",
        "      <td> 37.793790</td>\n",
        "      <td> 2012-10-01 08:05:11</td>\n",
        "      <td> 2012-10-01 08:03:00.948000</td>\n",
        "      <td> 744_3893</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4028</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.401118</td>\n",
        "      <td> 37.794820</td>\n",
        "      <td> 2012-10-01 08:09:39</td>\n",
        "      <td> 2012-10-01 08:08:00.964000</td>\n",
        "      <td> 744_3893</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4015</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.396996</td>\n",
        "      <td> 37.795415</td>\n",
        "      <td> 2012-10-01 08:11:48</td>\n",
        "      <td> 2012-10-01 08:10:00.955000</td>\n",
        "      <td> 744_3893</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td rowspan=\"8\" valign=\"top\"><strong>5213128</strong></td>\n",
        "      <td><strong>4277</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.493227</td>\n",
        "      <td> 37.779744</td>\n",
        "      <td> 2012-10-01 07:45:05</td>\n",
        "      <td> 2012-10-01 07:44:00.727000</td>\n",
        "      <td> 744_4277</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>3825</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.464709</td>\n",
        "      <td> 37.784902</td>\n",
        "      <td> 2012-10-01 07:55:49</td>\n",
        "      <td> 2012-10-01 07:55:00.686000</td>\n",
        "      <td> 744_4277</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>3893</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.446885</td>\n",
        "      <td> 37.787149</td>\n",
        "      <td> 2012-10-01 08:03:32</td>\n",
        "      <td> 2012-10-01 08:01:00.699000</td>\n",
        "      <td> 744_4277</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6296</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.433669</td>\n",
        "      <td> 37.789839</td>\n",
        "      <td> 2012-10-01 08:09:44</td>\n",
        "      <td> 2012-10-01 08:07:00.722000</td>\n",
        "      <td> 744_4277</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4026</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.420714</td>\n",
        "      <td> 37.792385</td>\n",
        "      <td> 2012-10-01 08:18:34</td>\n",
        "      <td> 2012-10-01 08:15:00.717000</td>\n",
        "      <td> 744_4277</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4027</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.409774</td>\n",
        "      <td> 37.793790</td>\n",
        "      <td> 2012-10-01 08:24:06</td>\n",
        "      <td> 2012-10-01 08:20:00.694000</td>\n",
        "      <td> 744_4277</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4028</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.401118</td>\n",
        "      <td> 37.794820</td>\n",
        "      <td> 2012-10-01 08:28:44</td>\n",
        "      <td> 2012-10-01 08:25:00.712000</td>\n",
        "      <td> 744_4277</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4015</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.396996</td>\n",
        "      <td> 37.795415</td>\n",
        "      <td> 2012-10-01 08:30:58</td>\n",
        "      <td> 2012-10-01 08:27:00.706000</td>\n",
        "      <td> 744_4277</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "output_type": "pyout",
       "prompt_number": 238,
       "text": [
        "                route_name   longitude   latitude         time_arrival  \\\n",
        "id_trip id_stop                                                          \n",
        "5213107 3893            01 -122.446885  37.787149  2012-10-01 07:44:19   \n",
        "        6296            01 -122.433669  37.789839  2012-10-01 07:50:45   \n",
        "        4026            01 -122.420714  37.792385  2012-10-01 07:58:52   \n",
        "        4027            01 -122.409774  37.793790  2012-10-01 08:05:11   \n",
        "        4028            01 -122.401118  37.794820  2012-10-01 08:09:39   \n",
        "        4015            01 -122.396996  37.795415  2012-10-01 08:11:48   \n",
        "5213128 4277            01 -122.493227  37.779744  2012-10-01 07:45:05   \n",
        "        3825            01 -122.464709  37.784902  2012-10-01 07:55:49   \n",
        "        3893            01 -122.446885  37.787149  2012-10-01 08:03:32   \n",
        "        6296            01 -122.433669  37.789839  2012-10-01 08:09:44   \n",
        "        4026            01 -122.420714  37.792385  2012-10-01 08:18:34   \n",
        "        4027            01 -122.409774  37.793790  2012-10-01 08:24:06   \n",
        "        4028            01 -122.401118  37.794820  2012-10-01 08:28:44   \n",
        "        4015            01 -122.396996  37.795415  2012-10-01 08:30:58   \n",
        "\n",
        "                     time_scheduled_arrival id_trip_start_time_stop  \n",
        "id_trip id_stop                                                      \n",
        "5213107 3893     2012-10-01 07:44:00.925000                744_3893  \n",
        "        6296     2012-10-01 07:50:00.940000                744_3893  \n",
        "        4026     2012-10-01 07:58:00.933000                744_3893  \n",
        "        4027     2012-10-01 08:03:00.948000                744_3893  \n",
        "        4028     2012-10-01 08:08:00.964000                744_3893  \n",
        "        4015     2012-10-01 08:10:00.955000                744_3893  \n",
        "5213128 4277     2012-10-01 07:44:00.727000                744_4277  \n",
        "        3825     2012-10-01 07:55:00.686000                744_4277  \n",
        "        3893     2012-10-01 08:01:00.699000                744_4277  \n",
        "        6296     2012-10-01 08:07:00.722000                744_4277  \n",
        "        4026     2012-10-01 08:15:00.717000                744_4277  \n",
        "        4027     2012-10-01 08:20:00.694000                744_4277  \n",
        "        4028     2012-10-01 08:25:00.712000                744_4277  \n",
        "        4015     2012-10-01 08:27:00.706000                744_4277  "
       ]
      }
     ],
     "prompt_number": 238
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pcounts.sort(['date', 'id_route', 'id_trip', 'time_stop']).set_index(\n",
      "    ['date', 'id_route', 'id_trip', 'id_stop']).head(20)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th>time_stop</th>\n",
        "      <th>time_door_close</th>\n",
        "      <th>time_pullout</th>\n",
        "      <th>count_boarding</th>\n",
        "      <th>count_exiting</th>\n",
        "      <th>count</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>date</th>\n",
        "      <th>id_route</th>\n",
        "      <th>id_trip</th>\n",
        "      <th>id_stop</th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <td rowspan=\"20\" valign=\"top\"><strong>2012-10-01</strong></td>\n",
        "      <td rowspan=\"20\" valign=\"top\"><strong>1</strong></td>\n",
        "      <td rowspan=\"20\" valign=\"top\"><strong>744</strong></td>\n",
        "      <td><strong>3893</strong></td>\n",
        "      <td> 2012-10-01 07:55:36</td>\n",
        "      <td> 2012-10-01 07:56:53</td>\n",
        "      <td> 2012-10-01 07:57:25</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td>  0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>3848</strong></td>\n",
        "      <td> 2012-10-01 07:58:16</td>\n",
        "      <td> 2012-10-01 07:59:05</td>\n",
        "      <td> 2012-10-01 07:59:05</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td>  0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>3859</strong></td>\n",
        "      <td> 2012-10-01 07:59:38</td>\n",
        "      <td> 2012-10-01 08:00:12</td>\n",
        "      <td> 2012-10-01 08:00:27</td>\n",
        "      <td> 2</td>\n",
        "      <td> 0</td>\n",
        "      <td>  2</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>3885</strong></td>\n",
        "      <td> 2012-10-01 08:01:07</td>\n",
        "      <td> 2012-10-01 08:01:07</td>\n",
        "      <td> 2012-10-01 08:01:07</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td>  2</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6489</strong></td>\n",
        "      <td> 2012-10-01 08:01:40</td>\n",
        "      <td> 2012-10-01 08:01:48</td>\n",
        "      <td> 2012-10-01 08:02:16</td>\n",
        "      <td> 1</td>\n",
        "      <td> 0</td>\n",
        "      <td>  3</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6296</strong></td>\n",
        "      <td> 2012-10-01 08:02:38</td>\n",
        "      <td> 2012-10-01 08:02:53</td>\n",
        "      <td> 2012-10-01 08:02:53</td>\n",
        "      <td> 3</td>\n",
        "      <td> 0</td>\n",
        "      <td>  6</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6320</strong></td>\n",
        "      <td> 2012-10-01 08:03:16</td>\n",
        "      <td> 2012-10-01 08:03:31</td>\n",
        "      <td> 2012-10-01 08:03:31</td>\n",
        "      <td> 2</td>\n",
        "      <td> 0</td>\n",
        "      <td>  8</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6292</strong></td>\n",
        "      <td> 2012-10-01 08:04:00</td>\n",
        "      <td> 2012-10-01 08:04:09</td>\n",
        "      <td> 2012-10-01 08:04:09</td>\n",
        "      <td> 1</td>\n",
        "      <td> 0</td>\n",
        "      <td>  9</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6306</strong></td>\n",
        "      <td> 2012-10-01 08:04:37</td>\n",
        "      <td> 2012-10-01 08:04:48</td>\n",
        "      <td> 2012-10-01 08:05:08</td>\n",
        "      <td> 2</td>\n",
        "      <td> 0</td>\n",
        "      <td> 11</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6310</strong></td>\n",
        "      <td> 2012-10-01 08:05:28</td>\n",
        "      <td> 2012-10-01 08:05:37</td>\n",
        "      <td> 2012-10-01 08:05:37</td>\n",
        "      <td> 1</td>\n",
        "      <td> 0</td>\n",
        "      <td> 12</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4905</strong></td>\n",
        "      <td> 2012-10-01 08:06:14</td>\n",
        "      <td> 2012-10-01 08:06:25</td>\n",
        "      <td> 2012-10-01 08:06:58</td>\n",
        "      <td> 2</td>\n",
        "      <td> 0</td>\n",
        "      <td> 14</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4016</strong></td>\n",
        "      <td> 2012-10-01 08:07:17</td>\n",
        "      <td> 2012-10-01 08:08:50</td>\n",
        "      <td> 2012-10-01 08:08:50</td>\n",
        "      <td> 2</td>\n",
        "      <td> 1</td>\n",
        "      <td> 15</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4031</strong></td>\n",
        "      <td> 2012-10-01 08:09:31</td>\n",
        "      <td> 2012-10-01 08:09:31</td>\n",
        "      <td> 2012-10-01 08:09:31</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 15</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4026</strong></td>\n",
        "      <td> 2012-10-01 08:10:03</td>\n",
        "      <td> 2012-10-01 08:10:24</td>\n",
        "      <td> 2012-10-01 08:10:24</td>\n",
        "      <td> 5</td>\n",
        "      <td> 0</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4022</strong></td>\n",
        "      <td> 2012-10-01 08:10:47</td>\n",
        "      <td> 2012-10-01 08:10:47</td>\n",
        "      <td> 2012-10-01 08:10:47</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4019</strong></td>\n",
        "      <td> 2012-10-01 08:11:28</td>\n",
        "      <td> 2012-10-01 08:11:28</td>\n",
        "      <td> 2012-10-01 08:11:28</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4023</strong></td>\n",
        "      <td> 2012-10-01 08:11:53</td>\n",
        "      <td> 2012-10-01 08:12:32</td>\n",
        "      <td> 2012-10-01 08:12:32</td>\n",
        "      <td> 6</td>\n",
        "      <td> 1</td>\n",
        "      <td> 25</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4020</strong></td>\n",
        "      <td> 2012-10-01 08:13:10</td>\n",
        "      <td> 2012-10-01 08:13:17</td>\n",
        "      <td> 2012-10-01 08:13:17</td>\n",
        "      <td> 1</td>\n",
        "      <td> 0</td>\n",
        "      <td> 26</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4030</strong></td>\n",
        "      <td> 2012-10-01 08:14:03</td>\n",
        "      <td> 2012-10-01 08:14:16</td>\n",
        "      <td> 2012-10-01 08:14:16</td>\n",
        "      <td> 2</td>\n",
        "      <td> 0</td>\n",
        "      <td> 28</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4024</strong></td>\n",
        "      <td> 2012-10-01 08:14:46</td>\n",
        "      <td> 2012-10-01 08:14:54</td>\n",
        "      <td> 2012-10-01 08:14:54</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 28</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "output_type": "pyout",
       "prompt_number": 220,
       "text": [
        "                                               time_stop      time_door_close  \\\n",
        "date       id_route id_trip id_stop                                             \n",
        "2012-10-01 1        744     3893     2012-10-01 07:55:36  2012-10-01 07:56:53   \n",
        "                            3848     2012-10-01 07:58:16  2012-10-01 07:59:05   \n",
        "                            3859     2012-10-01 07:59:38  2012-10-01 08:00:12   \n",
        "                            3885     2012-10-01 08:01:07  2012-10-01 08:01:07   \n",
        "                            6489     2012-10-01 08:01:40  2012-10-01 08:01:48   \n",
        "                            6296     2012-10-01 08:02:38  2012-10-01 08:02:53   \n",
        "                            6320     2012-10-01 08:03:16  2012-10-01 08:03:31   \n",
        "                            6292     2012-10-01 08:04:00  2012-10-01 08:04:09   \n",
        "                            6306     2012-10-01 08:04:37  2012-10-01 08:04:48   \n",
        "                            6310     2012-10-01 08:05:28  2012-10-01 08:05:37   \n",
        "                            4905     2012-10-01 08:06:14  2012-10-01 08:06:25   \n",
        "                            4016     2012-10-01 08:07:17  2012-10-01 08:08:50   \n",
        "                            4031     2012-10-01 08:09:31  2012-10-01 08:09:31   \n",
        "                            4026     2012-10-01 08:10:03  2012-10-01 08:10:24   \n",
        "                            4022     2012-10-01 08:10:47  2012-10-01 08:10:47   \n",
        "                            4019     2012-10-01 08:11:28  2012-10-01 08:11:28   \n",
        "                            4023     2012-10-01 08:11:53  2012-10-01 08:12:32   \n",
        "                            4020     2012-10-01 08:13:10  2012-10-01 08:13:17   \n",
        "                            4030     2012-10-01 08:14:03  2012-10-01 08:14:16   \n",
        "                            4024     2012-10-01 08:14:46  2012-10-01 08:14:54   \n",
        "\n",
        "                                            time_pullout  count_boarding  \\\n",
        "date       id_route id_trip id_stop                                        \n",
        "2012-10-01 1        744     3893     2012-10-01 07:57:25               0   \n",
        "                            3848     2012-10-01 07:59:05               0   \n",
        "                            3859     2012-10-01 08:00:27               2   \n",
        "                            3885     2012-10-01 08:01:07               0   \n",
        "                            6489     2012-10-01 08:02:16               1   \n",
        "                            6296     2012-10-01 08:02:53               3   \n",
        "                            6320     2012-10-01 08:03:31               2   \n",
        "                            6292     2012-10-01 08:04:09               1   \n",
        "                            6306     2012-10-01 08:05:08               2   \n",
        "                            6310     2012-10-01 08:05:37               1   \n",
        "                            4905     2012-10-01 08:06:58               2   \n",
        "                            4016     2012-10-01 08:08:50               2   \n",
        "                            4031     2012-10-01 08:09:31               0   \n",
        "                            4026     2012-10-01 08:10:24               5   \n",
        "                            4022     2012-10-01 08:10:47               0   \n",
        "                            4019     2012-10-01 08:11:28               0   \n",
        "                            4023     2012-10-01 08:12:32               6   \n",
        "                            4020     2012-10-01 08:13:17               1   \n",
        "                            4030     2012-10-01 08:14:16               2   \n",
        "                            4024     2012-10-01 08:14:54               0   \n",
        "\n",
        "                                     count_exiting  count  \n",
        "date       id_route id_trip id_stop                        \n",
        "2012-10-01 1        744     3893                 0      0  \n",
        "                            3848                 0      0  \n",
        "                            3859                 0      2  \n",
        "                            3885                 0      2  \n",
        "                            6489                 0      3  \n",
        "                            6296                 0      6  \n",
        "                            6320                 0      8  \n",
        "                            6292                 0      9  \n",
        "                            6306                 0     11  \n",
        "                            6310                 0     12  \n",
        "                            4905                 0     14  \n",
        "                            4016                 1     15  \n",
        "                            4031                 0     15  \n",
        "                            4026                 0     20  \n",
        "                            4022                 0     20  \n",
        "                            4019                 0     20  \n",
        "                            4023                 1     25  \n",
        "                            4020                 0     26  \n",
        "                            4030                 0     28  \n",
        "                            4024                 0     28  "
       ]
      }
     ],
     "prompt_number": 220
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sorted(pcounts[(pcounts.id_route == 1)].sort(\n",
      "       ['date', 'id_route', 'id_trip', 'time_stop']\n",
      "    ).set_index(['date', 'id_route', 'id_trip', 'id_stop']).ix[\n",
      "    (datetime.date(2012, 10, 1), 1)].reset_index().id_trip.unique())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "pyout",
       "prompt_number": 190,
       "text": [
        "[744,\n",
        " 758,\n",
        " 814,\n",
        " 828,\n",
        " 847,\n",
        " 902,\n",
        " 917,\n",
        " 924,\n",
        " 932,\n",
        " 1020,\n",
        " 1110,\n",
        " 1205,\n",
        " 1255,\n",
        " 1350,\n",
        " 1440,\n",
        " 1517,\n",
        " 1548]"
       ]
      }
     ],
     "prompt_number": 190
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "idx = sched_arrivals[sched_arrivals.id_route == 1].sort(['date', 'id_route', 'id_trip', 'time_arrival']).set_index(\n",
      "    ['date', 'id_route', 'id_trip', 'id_stop'])[\n",
      "    ['time_arrival', 'time_scheduled_arrival']].index[50]\n",
      "print idx\n",
      "sched_arrivals[sched_arrivals.id_route == 1].sort(['date', 'id_route', 'id_trip', 'time_arrival']).set_index(\n",
      "    ['date', 'id_route', 'id_trip', 'id_stop']).ix[idx[:-1]]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(datetime.date(2012, 10, 1), 1, 5212781, 4015)\n"
       ]
      },
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>route_name</th>\n",
        "      <th>longitude</th>\n",
        "      <th>latitude</th>\n",
        "      <th>time_arrival</th>\n",
        "      <th>time_scheduled_arrival</th>\n",
        "      <th>seconds_late</th>\n",
        "      <th>id_trip_pcount</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>id_stop</th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <td><strong>4015</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.396996</td>\n",
        "      <td> 37.795415</td>\n",
        "      <td> 2012-10-01 09:43:05</td>\n",
        "      <td> 2012-10-01 09:41:00.373000</td>\n",
        "      <td> 124.627</td>\n",
        "      <td> 941</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6314</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.401551</td>\n",
        "      <td> 37.794030</td>\n",
        "      <td> 2012-10-01 09:43:44</td>\n",
        "      <td> 2012-10-01 09:44:00.421000</td>\n",
        "      <td> -16.421</td>\n",
        "      <td> 941</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6312</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.409116</td>\n",
        "      <td> 37.793043</td>\n",
        "      <td> 2012-10-01 09:49:27</td>\n",
        "      <td> 2012-10-01 09:49:00.397000</td>\n",
        "      <td>  26.603</td>\n",
        "      <td> 941</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6311</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.421146</td>\n",
        "      <td> 37.791516</td>\n",
        "      <td> 2012-10-01 09:55:44</td>\n",
        "      <td> 2012-10-01 09:54:00.428000</td>\n",
        "      <td> 103.572</td>\n",
        "      <td> 941</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>6295</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.434336</td>\n",
        "      <td> 37.789840</td>\n",
        "      <td> 2012-10-01 10:04:28</td>\n",
        "      <td> 2012-10-01 10:01:00.389000</td>\n",
        "      <td> 207.611</td>\n",
        "      <td> 941</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>3892</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.446908</td>\n",
        "      <td> 37.787328</td>\n",
        "      <td> 2012-10-01 10:09:45</td>\n",
        "      <td> 2012-10-01 10:06:00.405000</td>\n",
        "      <td> 224.595</td>\n",
        "      <td> 941</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>3824</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.464939</td>\n",
        "      <td> 37.785017</td>\n",
        "      <td> 2012-10-01 10:16:10</td>\n",
        "      <td> 2012-10-01 10:13:00.380000</td>\n",
        "      <td> 189.620</td>\n",
        "      <td> 941</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <td><strong>4277</strong></td>\n",
        "      <td> 01</td>\n",
        "      <td>-122.493227</td>\n",
        "      <td> 37.779744</td>\n",
        "      <td> 2012-10-01 10:25:43</td>\n",
        "      <td> 2012-10-01 10:22:00.413000</td>\n",
        "      <td> 222.587</td>\n",
        "      <td> 941</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "output_type": "pyout",
       "prompt_number": 186,
       "text": [
        "        route_name   longitude   latitude         time_arrival  \\\n",
        "id_stop                                                          \n",
        "4015            01 -122.396996  37.795415  2012-10-01 09:43:05   \n",
        "6314            01 -122.401551  37.794030  2012-10-01 09:43:44   \n",
        "6312            01 -122.409116  37.793043  2012-10-01 09:49:27   \n",
        "6311            01 -122.421146  37.791516  2012-10-01 09:55:44   \n",
        "6295            01 -122.434336  37.789840  2012-10-01 10:04:28   \n",
        "3892            01 -122.446908  37.787328  2012-10-01 10:09:45   \n",
        "3824            01 -122.464939  37.785017  2012-10-01 10:16:10   \n",
        "4277            01 -122.493227  37.779744  2012-10-01 10:25:43   \n",
        "\n",
        "             time_scheduled_arrival  seconds_late  id_trip_pcount  \n",
        "id_stop                                                            \n",
        "4015     2012-10-01 09:41:00.373000       124.627             941  \n",
        "6314     2012-10-01 09:44:00.421000       -16.421             941  \n",
        "6312     2012-10-01 09:49:00.397000        26.603             941  \n",
        "6311     2012-10-01 09:54:00.428000       103.572             941  \n",
        "6295     2012-10-01 10:01:00.389000       207.611             941  \n",
        "3892     2012-10-01 10:06:00.405000       224.595             941  \n",
        "3824     2012-10-01 10:13:00.380000       189.620             941  \n",
        "4277     2012-10-01 10:22:00.413000       222.587             941  "
       ]
      }
     ],
     "prompt_number": 186
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print len(sched_arrivals), len(pcounts)\n",
      "print sched_arrivals.dtypes\n",
      "print pcounts.dtypes\n",
      "print sched_arrivals.date.ix[0]\n",
      "print pcounts.date.ix[0]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "425481 484907\n",
        "route_name                 object\n",
        "id_trip                     int64\n",
        "id_stop                     int64\n",
        "longitude                 float64\n",
        "latitude                  float64\n",
        "time_arrival               object\n",
        "date                       object\n",
        "time_scheduled_arrival     object\n",
        "id_route                    int64\n",
        "seconds_late              float64\n",
        "id_trip_pcount              int64\n",
        "date               object\n",
        "time_stop          object\n",
        "time_door_close    object\n",
        "time_pullout       object\n",
        "id_stop             int64\n",
        "count_boarding      int64\n",
        "count_exiting       int64\n",
        "count               int64\n",
        "id_route            int64\n",
        "id_trip             int64\n",
        "2012-10-01\n",
        "2012-10-04\n"
       ]
      }
     ],
     "prompt_number": 203
    }
   ],
   "metadata": {}
  }
 ]
}