openbus_13_gtfs_stats.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using a mix of `partridge` and `gtfstk` with some of my own additions to create daily statistical DataFrames for trips, routes and stops. This will later become a module which we will run on our historical MoT GTFS archive and schedule for nightly runs. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports and config"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Put these at the top of every notebook, to get automatic reloading and inline plotting\n",
    "%reload_ext autoreload\n",
    "%autoreload 2\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import partridge as ptg\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import altair as alt\n",
    "import datetime\n",
    "\n",
    "from gtfs_utils import *\n",
    "\n",
    "alt.renderers.enable('notebook')\n",
    "alt.data_transformers.enable('json')\n",
    "\n",
    "sns.set_style(\"white\")\n",
    "sns.set_context(\"talk\")\n",
    "sns.set_palette('Set2', 10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "LOCAL_GTFS_ZIP_PATH = 'data/gtfs_feeds/2018-03-05.zip' \n",
    "LOCAL_TARIFF_PATH = 'data/sample/latest_tariff.zip' "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a `partridge` feed\n",
    "We have a util function for getting a `partridge` feed object by date.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "feed = get_partridge_feed_by_date(LOCAL_GTFS_ZIP_PATH, datetime.date(2018,3,5))\n",
    "type(feed)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "zones = get_zones_df(LOCAL_TARIFF_PATH)\n",
    "zones.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Stats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### trip stats\n",
    "1. calculate using partridge feed\n",
    "1. add:\n",
    "    1. stop_code, stop_name\n",
    "    1. zone"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import gtfstk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "trip_id                 object\n",
       "arrival_time           float64\n",
       "departure_time         float64\n",
       "stop_id                 object\n",
       "stop_sequence            int64\n",
       "pickup_type            float64\n",
       "drop_off_type           object\n",
       "shape_dist_traveled      int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feed.stop_times.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "computing trip_stats corresponding to `gtfstk.compute_trip_stats()` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    49026\n",
       "1    41228\n",
       "Name: direction_id, dtype: int64"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feed.trips.direction_id.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "check whether we have bidirectionals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    6673\n",
       "Name: direction_id, dtype: int64"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feed.trips.groupby('route_id').direction_id.nunique().value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Nope"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "f = feed.trips\n",
    "f = (\n",
    "    f[['route_id', 'trip_id', 'direction_id', 'shape_id']]\n",
    "    .merge(feed.routes[['route_id', 'route_short_name', 'route_type']])\n",
    "    .merge(feed.stop_times)\n",
    "    .merge(feed.stops[['stop_id', 'stop_name', 'stop_lat', 'stop_lon', 'stop_code']])\n",
    "    .merge(zones)\n",
    "    .sort_values(['trip_id', 'stop_sequence'])\n",
    "    #.assign(departure_time=lambda x: x['departure_time'].map(\n",
    "    #    hp.timestr_to_seconds)\n",
    "    #       )\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>2235521</th>\n",
       "      <th>2322805</th>\n",
       "      <th>2241223</th>\n",
       "      <th>2241263</th>\n",
       "      <th>2322737</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>route_id</th>\n",
       "      <td>9735</td>\n",
       "      <td>9735</td>\n",
       "      <td>9735</td>\n",
       "      <td>9735</td>\n",
       "      <td>9735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trip_id</th>\n",
       "      <td>10096398_040318</td>\n",
       "      <td>10096398_040318</td>\n",
       "      <td>10096398_040318</td>\n",
       "      <td>10096398_040318</td>\n",
       "      <td>10096398_040318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>direction_id</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shape_id</th>\n",
       "      <td>88862</td>\n",
       "      <td>88862</td>\n",
       "      <td>88862</td>\n",
       "      <td>88862</td>\n",
       "      <td>88862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_short_name</th>\n",
       "      <td>70</td>\n",
       "      <td>70</td>\n",
       "      <td>70</td>\n",
       "      <td>70</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_type</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>arrival_time</th>\n",
       "      <td>75600</td>\n",
       "      <td>75640</td>\n",
       "      <td>75716</td>\n",
       "      <td>75858</td>\n",
       "      <td>76483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>departure_time</th>\n",
       "      <td>75600</td>\n",
       "      <td>75640</td>\n",
       "      <td>75716</td>\n",
       "      <td>75858</td>\n",
       "      <td>76483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stop_id</th>\n",
       "      <td>34657</td>\n",
       "      <td>35317</td>\n",
       "      <td>34436</td>\n",
       "      <td>34444</td>\n",
       "      <td>35506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stop_sequence</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pickup_type</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>drop_off_type</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shape_dist_traveled</th>\n",
       "      <td>0</td>\n",
       "      <td>276</td>\n",
       "      <td>857</td>\n",
       "      <td>1955</td>\n",
       "      <td>14838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stop_name</th>\n",
       "      <td>קניון קרני שומרון</td>\n",
       "      <td>שדרות רחבעם/קרני שומרון</td>\n",
       "      <td>קוממיות/שדרות רחבעם</td>\n",
       "      <td>נווה מנחם</td>\n",
       "      <td>יקיר בסיס צבאי</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stop_lat</th>\n",
       "      <td>32.1744</td>\n",
       "      <td>32.1752</td>\n",
       "      <td>32.1755</td>\n",
       "      <td>32.1796</td>\n",
       "      <td>32.1456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stop_lon</th>\n",
       "      <td>35.0912</td>\n",
       "      <td>35.0939</td>\n",
       "      <td>35.0993</td>\n",
       "      <td>35.1077</td>\n",
       "      <td>35.117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stop_code</th>\n",
       "      <td>63436</td>\n",
       "      <td>65268</td>\n",
       "      <td>63200</td>\n",
       "      <td>63213</td>\n",
       "      <td>65290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zone_name</th>\n",
       "      <td>השומרון</td>\n",
       "      <td>השומרון</td>\n",
       "      <td>השומרון</td>\n",
       "      <td>השומרון</td>\n",
       "      <td>השומרון</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               2235521                  2322805  \\\n",
       "route_id                          9735                     9735   \n",
       "trip_id                10096398_040318          10096398_040318   \n",
       "direction_id                         1                        1   \n",
       "shape_id                         88862                    88862   \n",
       "route_short_name                    70                       70   \n",
       "route_type                           3                        3   \n",
       "arrival_time                     75600                    75640   \n",
       "departure_time                   75600                    75640   \n",
       "stop_id                          34657                    35317   \n",
       "stop_sequence                        1                        2   \n",
       "pickup_type                          0                        0   \n",
       "drop_off_type                        1                        0   \n",
       "shape_dist_traveled                  0                      276   \n",
       "stop_name            קניון קרני שומרון  שדרות רחבעם/קרני שומרון   \n",
       "stop_lat                       32.1744                  32.1752   \n",
       "stop_lon                       35.0912                  35.0939   \n",
       "stop_code                        63436                    65268   \n",
       "zone_name                      השומרון                  השומרון   \n",
       "\n",
       "                                 2241223          2241263          2322737  \n",
       "route_id                            9735             9735             9735  \n",
       "trip_id                  10096398_040318  10096398_040318  10096398_040318  \n",
       "direction_id                           1                1                1  \n",
       "shape_id                           88862            88862            88862  \n",
       "route_short_name                      70               70               70  \n",
       "route_type                             3                3                3  \n",
       "arrival_time                       75716            75858            76483  \n",
       "departure_time                     75716            75858            76483  \n",
       "stop_id                            34436            34444            35506  \n",
       "stop_sequence                          3                4                5  \n",
       "pickup_type                            0                0                0  \n",
       "drop_off_type                          0                0                0  \n",
       "shape_dist_traveled                  857             1955            14838  \n",
       "stop_name            קוממיות/שדרות רחבעם        נווה מנחם   יקיר בסיס צבאי  \n",
       "stop_lat                         32.1755          32.1796          32.1456  \n",
       "stop_lon                         35.0993          35.1077           35.117  \n",
       "stop_code                          63200            63213            65290  \n",
       "zone_name                        השומרון          השומרון          השומרון  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "geometry_by_stop = gtfstk.build_geometry_by_stop(feed, use_utm=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "g = f.groupby('trip_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import OrderedDict\n",
    "def my_agg(group):\n",
    "    d = OrderedDict()\n",
    "    d['route_id'] = group['route_id'].iat[0]\n",
    "    d['route_short_name'] = group['route_short_name'].iat[0]\n",
    "    d['route_type'] = group['route_type'].iat[0]\n",
    "    d['direction_id'] = group['direction_id'].iat[0]\n",
    "    d['shape_id'] = group['shape_id'].iat[0]\n",
    "    d['num_stops'] = group.shape[0]\n",
    "    d['start_time'] = group['departure_time'].iat[0]\n",
    "    d['end_time'] = group['departure_time'].iat[-1]\n",
    "    d['start_stop_id'] = group['stop_id'].iat[0]\n",
    "    d['end_stop_id'] = group['stop_id'].iat[-1]\n",
    "    d['start_stop_code'] = group['stop_code'].iat[0]\n",
    "    d['end_stop_code'] = group['stop_code'].iat[-1]\n",
    "    d['start_stop_name'] = group['stop_name'].iat[0]\n",
    "    d['end_stop_name'] = group['stop_name'].iat[-1]\n",
    "    d['start_zone'] = group['zone_name'].iat[0]\n",
    "    d['end_zone'] = group['zone_name'].iat[-1]\n",
    "    dist = geometry_by_stop[d['start_stop_id']].distance(\n",
    "      geometry_by_stop[d['end_stop_id']])\n",
    "    d['is_loop'] = int(dist < 400)\n",
    "    d['duration'] = (d['end_time'] - d['start_time'])/3600\n",
    "    return pd.Series(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "h = g.apply(my_agg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "h['distance'] = g.shape_dist_traveled.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reset index and compute final stats\n",
    "h = h.reset_index()\n",
    "h['speed'] = h['distance'] / h['duration'] / 1000\n",
    "h[['start_time', 'end_time']] = (\n",
    "  h[['start_time', 'end_time']].applymap(\n",
    "  lambda x: gtfstk.helpers.timestr_to_seconds(x, inverse=True))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>36646</th>\n",
       "      <th>36647</th>\n",
       "      <th>36648</th>\n",
       "      <th>43945</th>\n",
       "      <th>43944</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>trip_id</th>\n",
       "      <td>29575729_040318</td>\n",
       "      <td>29575734_040318</td>\n",
       "      <td>29575739_040318</td>\n",
       "      <td>29899643_040318</td>\n",
       "      <td>29899638_040318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_id</th>\n",
       "      <td>12518</td>\n",
       "      <td>12518</td>\n",
       "      <td>12518</td>\n",
       "      <td>12517</td>\n",
       "      <td>12517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_short_name</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_type</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>direction_id</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shape_id</th>\n",
       "      <td>51387</td>\n",
       "      <td>51387</td>\n",
       "      <td>51387</td>\n",
       "      <td>51386</td>\n",
       "      <td>51386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_stops</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_time</th>\n",
       "      <td>06:10:00</td>\n",
       "      <td>08:10:00</td>\n",
       "      <td>16:25:00</td>\n",
       "      <td>17:52:00</td>\n",
       "      <td>10:37:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_time</th>\n",
       "      <td>06:34:00</td>\n",
       "      <td>08:34:00</td>\n",
       "      <td>16:49:00</td>\n",
       "      <td>18:16:00</td>\n",
       "      <td>11:01:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_stop_id</th>\n",
       "      <td>37310</td>\n",
       "      <td>37310</td>\n",
       "      <td>37310</td>\n",
       "      <td>37314</td>\n",
       "      <td>37314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_stop_id</th>\n",
       "      <td>37314</td>\n",
       "      <td>37314</td>\n",
       "      <td>37314</td>\n",
       "      <td>37310</td>\n",
       "      <td>37310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_stop_code</th>\n",
       "      <td>17086</td>\n",
       "      <td>17086</td>\n",
       "      <td>17086</td>\n",
       "      <td>17082</td>\n",
       "      <td>17082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_stop_code</th>\n",
       "      <td>17082</td>\n",
       "      <td>17082</td>\n",
       "      <td>17082</td>\n",
       "      <td>17086</td>\n",
       "      <td>17086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_stop_name</th>\n",
       "      <td>דימונה</td>\n",
       "      <td>דימונה</td>\n",
       "      <td>דימונה</td>\n",
       "      <td>באר שבע-צפון</td>\n",
       "      <td>באר שבע-צפון</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_stop_name</th>\n",
       "      <td>באר שבע-צפון</td>\n",
       "      <td>באר שבע-צפון</td>\n",
       "      <td>באר שבע-צפון</td>\n",
       "      <td>דימונה</td>\n",
       "      <td>דימונה</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_zone</th>\n",
       "      <td>ערד דימונה</td>\n",
       "      <td>ערד דימונה</td>\n",
       "      <td>ערד דימונה</td>\n",
       "      <td>באר שבע</td>\n",
       "      <td>באר שבע</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_zone</th>\n",
       "      <td>באר שבע</td>\n",
       "      <td>באר שבע</td>\n",
       "      <td>באר שבע</td>\n",
       "      <td>ערד דימונה</td>\n",
       "      <td>ערד דימונה</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_loop</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>duration</th>\n",
       "      <td>0.4</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>distance</th>\n",
       "      <td>37400</td>\n",
       "      <td>37400</td>\n",
       "      <td>37400</td>\n",
       "      <td>37400</td>\n",
       "      <td>37400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>speed</th>\n",
       "      <td>93.5</td>\n",
       "      <td>93.5</td>\n",
       "      <td>93.5</td>\n",
       "      <td>93.5</td>\n",
       "      <td>93.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            36646            36647            36648  \\\n",
       "trip_id           29575729_040318  29575734_040318  29575739_040318   \n",
       "route_id                    12518            12518            12518   \n",
       "route_short_name              NaN              NaN              NaN   \n",
       "route_type                      2                2                2   \n",
       "direction_id                    1                1                1   \n",
       "shape_id                    51387            51387            51387   \n",
       "num_stops                       2                2                2   \n",
       "start_time               06:10:00         08:10:00         16:25:00   \n",
       "end_time                 06:34:00         08:34:00         16:49:00   \n",
       "start_stop_id               37310            37310            37310   \n",
       "end_stop_id                 37314            37314            37314   \n",
       "start_stop_code             17086            17086            17086   \n",
       "end_stop_code               17082            17082            17082   \n",
       "start_stop_name            דימונה           דימונה           דימונה   \n",
       "end_stop_name        באר שבע-צפון     באר שבע-צפון     באר שבע-צפון   \n",
       "start_zone             ערד דימונה       ערד דימונה       ערד דימונה   \n",
       "end_zone                  באר שבע          באר שבע          באר שבע   \n",
       "is_loop                         0                0                0   \n",
       "duration                      0.4              0.4              0.4   \n",
       "distance                    37400            37400            37400   \n",
       "speed                        93.5             93.5             93.5   \n",
       "\n",
       "                            43945            43944  \n",
       "trip_id           29899643_040318  29899638_040318  \n",
       "route_id                    12517            12517  \n",
       "route_short_name              NaN              NaN  \n",
       "route_type                      2                2  \n",
       "direction_id                    0                0  \n",
       "shape_id                    51386            51386  \n",
       "num_stops                       2                2  \n",
       "start_time               17:52:00         10:37:00  \n",
       "end_time                 18:16:00         11:01:00  \n",
       "start_stop_id               37314            37314  \n",
       "end_stop_id                 37310            37310  \n",
       "start_stop_code             17082            17082  \n",
       "end_stop_code               17086            17086  \n",
       "start_stop_name      באר שבע-צפון     באר שבע-צפון  \n",
       "end_stop_name              דימונה           דימונה  \n",
       "start_zone                באר שבע          באר שבע  \n",
       "end_zone               ערד דימונה       ערד דימונה  \n",
       "is_loop                         0                0  \n",
       "duration                      0.4              0.4  \n",
       "distance                    37400            37400  \n",
       "speed                        93.5             93.5  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "h.sort_values(by='speed', ascending=False).head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>29454</th>\n",
       "      <th>29452</th>\n",
       "      <th>29451</th>\n",
       "      <th>29455</th>\n",
       "      <th>29453</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>trip_id</th>\n",
       "      <td>26534192_040318</td>\n",
       "      <td>26534183_040318</td>\n",
       "      <td>26534179_040318</td>\n",
       "      <td>26534197_040318</td>\n",
       "      <td>26534187_040318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_id</th>\n",
       "      <td>8067</td>\n",
       "      <td>8067</td>\n",
       "      <td>8067</td>\n",
       "      <td>8067</td>\n",
       "      <td>8067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_short_name</th>\n",
       "      <td>58</td>\n",
       "      <td>58</td>\n",
       "      <td>58</td>\n",
       "      <td>58</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_type</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>direction_id</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shape_id</th>\n",
       "      <td>91607</td>\n",
       "      <td>91607</td>\n",
       "      <td>91607</td>\n",
       "      <td>91607</td>\n",
       "      <td>91607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_stops</th>\n",
       "      <td>56</td>\n",
       "      <td>56</td>\n",
       "      <td>56</td>\n",
       "      <td>56</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_time</th>\n",
       "      <td>07:30:00</td>\n",
       "      <td>10:15:00</td>\n",
       "      <td>08:45:00</td>\n",
       "      <td>21:00:00</td>\n",
       "      <td>14:50:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_time</th>\n",
       "      <td>09:30:01</td>\n",
       "      <td>12:15:01</td>\n",
       "      <td>10:45:01</td>\n",
       "      <td>23:00:01</td>\n",
       "      <td>16:50:01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_stop_id</th>\n",
       "      <td>37124</td>\n",
       "      <td>37124</td>\n",
       "      <td>37124</td>\n",
       "      <td>37124</td>\n",
       "      <td>37124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_stop_id</th>\n",
       "      <td>27918</td>\n",
       "      <td>27918</td>\n",
       "      <td>27918</td>\n",
       "      <td>27918</td>\n",
       "      <td>27918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_stop_code</th>\n",
       "      <td>13907</td>\n",
       "      <td>13907</td>\n",
       "      <td>13907</td>\n",
       "      <td>13907</td>\n",
       "      <td>13907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_stop_code</th>\n",
       "      <td>15657</td>\n",
       "      <td>15657</td>\n",
       "      <td>15657</td>\n",
       "      <td>15657</td>\n",
       "      <td>15657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_stop_name</th>\n",
       "      <td>תחנה מרכזית באר שבע/רציפים</td>\n",
       "      <td>תחנה מרכזית באר שבע/רציפים</td>\n",
       "      <td>תחנה מרכזית באר שבע/רציפים</td>\n",
       "      <td>תחנה מרכזית באר שבע/רציפים</td>\n",
       "      <td>תחנה מרכזית באר שבע/רציפים</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_stop_name</th>\n",
       "      <td>תחנה מרכזית באר שבע</td>\n",
       "      <td>תחנה מרכזית באר שבע</td>\n",
       "      <td>תחנה מרכזית באר שבע</td>\n",
       "      <td>תחנה מרכזית באר שבע</td>\n",
       "      <td>תחנה מרכזית באר שבע</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_zone</th>\n",
       "      <td>באר שבע</td>\n",
       "      <td>באר שבע</td>\n",
       "      <td>באר שבע</td>\n",
       "      <td>באר שבע</td>\n",
       "      <td>באר שבע</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_zone</th>\n",
       "      <td>באר שבע</td>\n",
       "      <td>באר שבע</td>\n",
       "      <td>באר שבע</td>\n",
       "      <td>באר שבע</td>\n",
       "      <td>באר שבע</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_loop</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>duration</th>\n",
       "      <td>2.00028</td>\n",
       "      <td>2.00028</td>\n",
       "      <td>2.00028</td>\n",
       "      <td>2.00028</td>\n",
       "      <td>2.00028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>distance</th>\n",
       "      <td>85513</td>\n",
       "      <td>85513</td>\n",
       "      <td>85513</td>\n",
       "      <td>85513</td>\n",
       "      <td>85513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>speed</th>\n",
       "      <td>42.7506</td>\n",
       "      <td>42.7506</td>\n",
       "      <td>42.7506</td>\n",
       "      <td>42.7506</td>\n",
       "      <td>42.7506</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       29454                       29452  \\\n",
       "trip_id                      26534192_040318             26534183_040318   \n",
       "route_id                                8067                        8067   \n",
       "route_short_name                          58                          58   \n",
       "route_type                                 3                           3   \n",
       "direction_id                               0                           0   \n",
       "shape_id                               91607                       91607   \n",
       "num_stops                                 56                          56   \n",
       "start_time                          07:30:00                    10:15:00   \n",
       "end_time                            09:30:01                    12:15:01   \n",
       "start_stop_id                          37124                       37124   \n",
       "end_stop_id                            27918                       27918   \n",
       "start_stop_code                        13907                       13907   \n",
       "end_stop_code                          15657                       15657   \n",
       "start_stop_name   תחנה מרכזית באר שבע/רציפים  תחנה מרכזית באר שבע/רציפים   \n",
       "end_stop_name            תחנה מרכזית באר שבע         תחנה מרכזית באר שבע   \n",
       "start_zone                           באר שבע                     באר שבע   \n",
       "end_zone                             באר שבע                     באר שבע   \n",
       "is_loop                                    1                           1   \n",
       "duration                             2.00028                     2.00028   \n",
       "distance                               85513                       85513   \n",
       "speed                                42.7506                     42.7506   \n",
       "\n",
       "                                       29451                       29455  \\\n",
       "trip_id                      26534179_040318             26534197_040318   \n",
       "route_id                                8067                        8067   \n",
       "route_short_name                          58                          58   \n",
       "route_type                                 3                           3   \n",
       "direction_id                               0                           0   \n",
       "shape_id                               91607                       91607   \n",
       "num_stops                                 56                          56   \n",
       "start_time                          08:45:00                    21:00:00   \n",
       "end_time                            10:45:01                    23:00:01   \n",
       "start_stop_id                          37124                       37124   \n",
       "end_stop_id                            27918                       27918   \n",
       "start_stop_code                        13907                       13907   \n",
       "end_stop_code                          15657                       15657   \n",
       "start_stop_name   תחנה מרכזית באר שבע/רציפים  תחנה מרכזית באר שבע/רציפים   \n",
       "end_stop_name            תחנה מרכזית באר שבע         תחנה מרכזית באר שבע   \n",
       "start_zone                           באר שבע                     באר שבע   \n",
       "end_zone                             באר שבע                     באר שבע   \n",
       "is_loop                                    1                           1   \n",
       "duration                             2.00028                     2.00028   \n",
       "distance                               85513                       85513   \n",
       "speed                                42.7506                     42.7506   \n",
       "\n",
       "                                       29453  \n",
       "trip_id                      26534187_040318  \n",
       "route_id                                8067  \n",
       "route_short_name                          58  \n",
       "route_type                                 3  \n",
       "direction_id                               0  \n",
       "shape_id                               91607  \n",
       "num_stops                                 56  \n",
       "start_time                          14:50:00  \n",
       "end_time                            16:50:01  \n",
       "start_stop_id                          37124  \n",
       "end_stop_id                            27918  \n",
       "start_stop_code                        13907  \n",
       "end_stop_code                          15657  \n",
       "start_stop_name   תחנה מרכזית באר שבע/רציפים  \n",
       "end_stop_name            תחנה מרכזית באר שבע  \n",
       "start_zone                           באר שבע  \n",
       "end_zone                             באר שבע  \n",
       "is_loop                                    1  \n",
       "duration                             2.00028  \n",
       "distance                               85513  \n",
       "speed                                42.7506  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "h[h.is_loop==1].sort_values(by='duration', ascending=False).head().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Route stats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is mostly taken from `gtfstk.compute_route_stats_base()`, with some additions:\n",
    "\n",
    "1. first start_stop_id and end_stop_id\n",
    "1. first start_zone and end_zone\n",
    "1. first number of stops"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "    \"\"\"\n",
    "    Compute stats for the given subset of trips stats.\n",
    "\n",
    "    Parameters\n",
    "    ----------\n",
    "    trip_stats_subset : DataFrame\n",
    "        Subset of the output of :func:`.trips.compute_trip_stats`\n",
    "    split_directions : boolean\n",
    "        If ``True``, then separate the stats by trip direction (0 or 1);\n",
    "        otherwise aggregate trips visiting from both directions. \n",
    "        Default: ``False``\n",
    "    headway_start_time : string\n",
    "        HH:MM:SS time string indicating the start time for computing\n",
    "        headway stats\n",
    "        Default: ``'07:00:00'``\n",
    "    headway_end_time : string\n",
    "        HH:MM:SS time string indicating the end time for computing\n",
    "        headway stats.\n",
    "        Default: ``'19:00:00'``\n",
    "\n",
    "    Returns\n",
    "    -------\n",
    "    DataFrame\n",
    "        Columns are\n",
    "\n",
    "        - ``'route_id'``\n",
    "        - ``'route_short_name'``\n",
    "        - ``'agency_id'``\n",
    "        - ``'agency_name'``\n",
    "        - ``'route_long_name'``\n",
    "        - ``'route_type'``\n",
    "        - ``'direction_id'``: 1/0\n",
    "        - ``'num_trips'``: number of trips on the route in the subset\n",
    "        - ``'num_trip_starts'``: number of trips on the route with\n",
    "          nonnull start times\n",
    "        - ``'num_trip_ends'``: number of trips on the route with nonnull\n",
    "          end times that end before 23:59:59\n",
    "        - ``'is_loop'``: 1 if at least one of the trips on the route has\n",
    "          its ``is_loop`` field equal to 1; 0 otherwise\n",
    "        - ``'is_bidirectional'``: 1 if the route has trips in both\n",
    "          directions; 0 otherwise\n",
    "        - ``'start_time'``: start time of the earliest trip on the route\n",
    "        - ``'end_time'``: end time of latest trip on the route\n",
    "        - ``'max_headway'``: maximum of the durations (in minutes)\n",
    "          between trip starts on the route between\n",
    "          ``headway_start_time`` and ``headway_end_time`` on the given\n",
    "          dates\n",
    "        - ``'min_headway'``: minimum of the durations (in minutes)\n",
    "          mentioned above\n",
    "        - ``'mean_headway'``: mean of the durations (in minutes)\n",
    "          mentioned above\n",
    "        - ``'peak_num_trips'``: maximum number of simultaneous trips in\n",
    "          service (for the given direction, or for both directions when\n",
    "          ``split_directions==False``)\n",
    "        - ``'peak_start_time'``: start time of first longest period\n",
    "          during which the peak number of trips occurs\n",
    "        - ``'peak_end_time'``: end time of first longest period during\n",
    "          which the peak number of trips occurs\n",
    "        - ``'service_duration'``: total of the duration of each trip on\n",
    "          the route in the given subset of trips; measured in hours\n",
    "        - ``'service_distance'``: total of the distance traveled by each\n",
    "          trip on the route in the given subset of trips; measured in\n",
    "          whatever distance units are present in ``trip_stats_subset``;\n",
    "          contains all ``np.nan`` entries if ``feed.shapes is None``\n",
    "        - ``'service_speed'``: service_distance/service_duration;\n",
    "          measured in distance units per hour\n",
    "        - ``'mean_trip_distance'``: service_distance/num_trips\n",
    "        - ``'mean_trip_duration'``: service_duration/num_trips\n",
    "        - ``'start_stop_id'``: ``start_stop_id`` of the first trip for the route\n",
    "        - ``'end_stop_id'``: ``end_stop_id`` of the first trip for the route\n",
    "        - ``'num_stops'``: ``num_stops`` of the first trip for the route\n",
    "        - ``'start_zone'``: ``start_zone`` of the first trip for the route\n",
    "        - ``'end_zone'``: ``end_zone`` of the first trip for the route\n",
    "        \n",
    "        If not ``split_directions``, then remove the\n",
    "        direction_id column and compute each route's stats,\n",
    "        except for headways, using\n",
    "        its trips running in both directions.\n",
    "        In this case, (1) compute max headway by taking the max of the\n",
    "        max headways in both directions; (2) compute mean headway by\n",
    "        taking the weighted mean of the mean headways in both\n",
    "        directions.\n",
    "        \n",
    "        If ``trip_stats_subset`` is empty, return an empty DataFrame.\n",
    "\n",
    "    \"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "headway_start_time='07:00:00'\n",
    "headway_end_time='19:00:00'\n",
    "# Convert trip start and end times to seconds to ease calculations below\n",
    "f = h.copy()\n",
    "f[['start_time', 'end_time']] = f[['start_time', 'end_time']\n",
    "  ].applymap(gtfstk.helpers.timestr_to_seconds)\n",
    "\n",
    "headway_start = gtfstk.helpers.timestr_to_seconds(headway_start_time)\n",
    "headway_end = gtfstk.helpers.timestr_to_seconds(headway_end_time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "def compute_route_stats(group):\n",
    "    d = OrderedDict()\n",
    "    d['route_short_name'] = group['route_short_name'].iat[0]\n",
    "    d['route_type'] = group['route_type'].iat[0]\n",
    "    d['num_trips'] = group.shape[0]\n",
    "    d['num_trip_starts'] = group['start_time'].count()\n",
    "    d['num_trip_ends'] = group.loc[\n",
    "      group['end_time'] < 24*3600, 'end_time'].count()\n",
    "    d['is_loop'] = int(group['is_loop'].any())\n",
    "    d['is_bidirectional'] = int(group['direction_id'].unique().size > 1)\n",
    "    d['start_time'] = group['start_time'].min()\n",
    "    d['end_time'] = group['end_time'].max()\n",
    "\n",
    "    # Compute headway stats\n",
    "    headways = np.array([])\n",
    "    for direction in [0, 1]:\n",
    "        stimes = group[group['direction_id'] == direction][\n",
    "          'start_time'].values\n",
    "        stimes = sorted([stime for stime in stimes\n",
    "          if headway_start <= stime <= headway_end])\n",
    "        headways = np.concatenate([headways, np.diff(stimes)])\n",
    "    if headways.size:\n",
    "        d['max_headway'] = np.max(headways)/60  # minutes\n",
    "        d['min_headway'] = np.min(headways)/60  # minutes\n",
    "        d['mean_headway'] = np.mean(headways)/60  # minutes\n",
    "    else:\n",
    "        d['max_headway'] = np.nan\n",
    "        d['min_headway'] = np.nan\n",
    "        d['mean_headway'] = np.nan\n",
    "\n",
    "    # Compute peak num trips\n",
    "    times = np.unique(group[['start_time', 'end_time']].values)\n",
    "    counts = [gtfstk.helpers.count_active_trips(group, t) for t in times]\n",
    "    start, end = gtfstk.helpers.get_peak_indices(times, counts)\n",
    "    d['peak_num_trips'] = counts[start]\n",
    "    d['peak_start_time'] = times[start]\n",
    "    d['peak_end_time'] = times[end]\n",
    "\n",
    "    d['service_distance'] = group['distance'].sum()\n",
    "    d['service_duration'] = group['duration'].sum()\n",
    "    \n",
    "    # Added by cjer\n",
    "    d['start_stop_id'] = group['start_stop_id'].iat[0]\n",
    "    d['end_stop_id'] = group['end_stop_id'].iat[0]\n",
    "    \n",
    "    d['num_stops'] = group['num_stops'].iat[0]\n",
    "    \n",
    "    d['start_zone'] = group['start_zone'].iat[0]\n",
    "    d['end_zone'] = group['end_zone'].iat[0]\n",
    "    \n",
    "    \n",
    "    return pd.Series(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "g = f.groupby('route_id').apply(\n",
    "compute_route_stats).reset_index()\n",
    "\n",
    "# Compute a few more stats\n",
    "g['service_speed'] = g['service_distance']/g['service_duration']\n",
    "g['mean_trip_distance'] = g['service_distance']/g['num_trips']\n",
    "g['mean_trip_duration'] = g['service_duration']/g['num_trips']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert route times to time strings\n",
    "g[['start_time', 'end_time', 'peak_start_time', 'peak_end_time']] =\\\n",
    "    g[['start_time', 'end_time', 'peak_start_time', 'peak_end_time']].\\\n",
    "        applymap(lambda x: gtfstk.helpers.timestr_to_seconds(x, inverse=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "g['service_speed'] = g.service_speed/1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>814</th>\n",
       "      <th>816</th>\n",
       "      <th>1394</th>\n",
       "      <th>1395</th>\n",
       "      <th>4351</th>\n",
       "      <th>4352</th>\n",
       "      <th>338</th>\n",
       "      <th>339</th>\n",
       "      <th>6549</th>\n",
       "      <th>4481</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>route_id</th>\n",
       "      <td>11685</td>\n",
       "      <td>11689</td>\n",
       "      <td>13428</td>\n",
       "      <td>13429</td>\n",
       "      <td>2259</td>\n",
       "      <td>2261</td>\n",
       "      <td>10746</td>\n",
       "      <td>10747</td>\n",
       "      <td>9817</td>\n",
       "      <td>2533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_short_name</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>204</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_type</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_trips</th>\n",
       "      <td>195</td>\n",
       "      <td>186</td>\n",
       "      <td>177</td>\n",
       "      <td>177</td>\n",
       "      <td>156</td>\n",
       "      <td>155</td>\n",
       "      <td>148</td>\n",
       "      <td>148</td>\n",
       "      <td>143</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_trip_starts</th>\n",
       "      <td>195</td>\n",
       "      <td>186</td>\n",
       "      <td>177</td>\n",
       "      <td>177</td>\n",
       "      <td>156</td>\n",
       "      <td>155</td>\n",
       "      <td>148</td>\n",
       "      <td>148</td>\n",
       "      <td>143</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_trip_ends</th>\n",
       "      <td>192</td>\n",
       "      <td>184</td>\n",
       "      <td>173</td>\n",
       "      <td>173</td>\n",
       "      <td>155</td>\n",
       "      <td>154</td>\n",
       "      <td>147</td>\n",
       "      <td>147</td>\n",
       "      <td>142</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_loop</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_bidirectional</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_time</th>\n",
       "      <td>05:00:00</td>\n",
       "      <td>04:40:00</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>00:05:00</td>\n",
       "      <td>05:15:00</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>05:30:00</td>\n",
       "      <td>05:30:00</td>\n",
       "      <td>00:05:00</td>\n",
       "      <td>00:10:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_time</th>\n",
       "      <td>24:45:30</td>\n",
       "      <td>24:22:09</td>\n",
       "      <td>24:49:57</td>\n",
       "      <td>24:55:49</td>\n",
       "      <td>24:19:58</td>\n",
       "      <td>24:12:07</td>\n",
       "      <td>24:09:47</td>\n",
       "      <td>24:04:47</td>\n",
       "      <td>24:08:24</td>\n",
       "      <td>24:43:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max_headway</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min_headway</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean_headway</th>\n",
       "      <td>4.4472</td>\n",
       "      <td>4.49315</td>\n",
       "      <td>5.752</td>\n",
       "      <td>5.744</td>\n",
       "      <td>6.01681</td>\n",
       "      <td>6.06838</td>\n",
       "      <td>6.18261</td>\n",
       "      <td>6.13793</td>\n",
       "      <td>6.78095</td>\n",
       "      <td>7.4375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>peak_num_trips</th>\n",
       "      <td>17</td>\n",
       "      <td>16</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>peak_start_time</th>\n",
       "      <td>07:04:00</td>\n",
       "      <td>07:00:00</td>\n",
       "      <td>07:40:00</td>\n",
       "      <td>14:15:00</td>\n",
       "      <td>12:55:00</td>\n",
       "      <td>14:20:00</td>\n",
       "      <td>07:04:00</td>\n",
       "      <td>07:12:00</td>\n",
       "      <td>07:40:00</td>\n",
       "      <td>14:48:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>peak_end_time</th>\n",
       "      <td>07:05:30</td>\n",
       "      <td>07:02:09</td>\n",
       "      <td>07:44:57</td>\n",
       "      <td>14:15:49</td>\n",
       "      <td>12:59:58</td>\n",
       "      <td>14:22:07</td>\n",
       "      <td>07:09:47</td>\n",
       "      <td>07:13:47</td>\n",
       "      <td>07:40:24</td>\n",
       "      <td>14:53:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>service_distance</th>\n",
       "      <td>4980105</td>\n",
       "      <td>4731840</td>\n",
       "      <td>3944622</td>\n",
       "      <td>3967986</td>\n",
       "      <td>1367184</td>\n",
       "      <td>1364775</td>\n",
       "      <td>1972248</td>\n",
       "      <td>1972692</td>\n",
       "      <td>1411553</td>\n",
       "      <td>2363064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>service_duration</th>\n",
       "      <td>212.875</td>\n",
       "      <td>192.665</td>\n",
       "      <td>191.602</td>\n",
       "      <td>194.159</td>\n",
       "      <td>77.9133</td>\n",
       "      <td>82.9681</td>\n",
       "      <td>43.8656</td>\n",
       "      <td>33.9989</td>\n",
       "      <td>67.6867</td>\n",
       "      <td>118.543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_stop_id</th>\n",
       "      <td>36217</td>\n",
       "      <td>35756</td>\n",
       "      <td>37504</td>\n",
       "      <td>14613</td>\n",
       "      <td>37649</td>\n",
       "      <td>12889</td>\n",
       "      <td>35161</td>\n",
       "      <td>35119</td>\n",
       "      <td>13112</td>\n",
       "      <td>14323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_stop_id</th>\n",
       "      <td>35501</td>\n",
       "      <td>35827</td>\n",
       "      <td>23921</td>\n",
       "      <td>37504</td>\n",
       "      <td>13730</td>\n",
       "      <td>14318</td>\n",
       "      <td>35120</td>\n",
       "      <td>35162</td>\n",
       "      <td>13700</td>\n",
       "      <td>37504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_stops</th>\n",
       "      <td>42</td>\n",
       "      <td>41</td>\n",
       "      <td>51</td>\n",
       "      <td>55</td>\n",
       "      <td>26</td>\n",
       "      <td>26</td>\n",
       "      <td>23</td>\n",
       "      <td>23</td>\n",
       "      <td>33</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_zone</th>\n",
       "      <td>סובב חיפה</td>\n",
       "      <td>סובב חיפה</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>סובב ירושלים</td>\n",
       "      <td>סובב ירושלים</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>גוש דן</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_zone</th>\n",
       "      <td>סובב חיפה</td>\n",
       "      <td>סובב חיפה</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>סובב ירושלים</td>\n",
       "      <td>סובב ירושלים</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>גוש דן</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>service_speed</th>\n",
       "      <td>23.3945</td>\n",
       "      <td>24.5599</td>\n",
       "      <td>20.5875</td>\n",
       "      <td>20.4368</td>\n",
       "      <td>17.5475</td>\n",
       "      <td>16.4494</td>\n",
       "      <td>44.9612</td>\n",
       "      <td>58.0222</td>\n",
       "      <td>20.8542</td>\n",
       "      <td>19.9342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean_trip_distance</th>\n",
       "      <td>25539</td>\n",
       "      <td>25440</td>\n",
       "      <td>22286</td>\n",
       "      <td>22418</td>\n",
       "      <td>8764</td>\n",
       "      <td>8805</td>\n",
       "      <td>13326</td>\n",
       "      <td>13329</td>\n",
       "      <td>9871</td>\n",
       "      <td>17902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean_trip_duration</th>\n",
       "      <td>1.09167</td>\n",
       "      <td>1.03583</td>\n",
       "      <td>1.0825</td>\n",
       "      <td>1.09694</td>\n",
       "      <td>0.499444</td>\n",
       "      <td>0.535278</td>\n",
       "      <td>0.296389</td>\n",
       "      <td>0.229722</td>\n",
       "      <td>0.473333</td>\n",
       "      <td>0.898056</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         814        816       1394      1395      4351  \\\n",
       "route_id                11685      11689     13428     13429      2259   \n",
       "route_short_name            1          1         1         1         5   \n",
       "route_type                  3          3         3         3         3   \n",
       "num_trips                 195        186       177       177       156   \n",
       "num_trip_starts           195        186       177       177       156   \n",
       "num_trip_ends             192        184       173       173       155   \n",
       "is_loop                     0          0         0         0         0   \n",
       "is_bidirectional            0          0         0         0         0   \n",
       "start_time           05:00:00   04:40:00  00:00:00  00:05:00  05:15:00   \n",
       "end_time             24:45:30   24:22:09  24:49:57  24:55:49  24:19:58   \n",
       "max_headway                 5          5         7         6         7   \n",
       "min_headway                 4          4         5         5         5   \n",
       "mean_headway           4.4472    4.49315     5.752     5.744   6.01681   \n",
       "peak_num_trips             17         16        13        14         6   \n",
       "peak_start_time      07:04:00   07:00:00  07:40:00  14:15:00  12:55:00   \n",
       "peak_end_time        07:05:30   07:02:09  07:44:57  14:15:49  12:59:58   \n",
       "service_distance      4980105    4731840   3944622   3967986   1367184   \n",
       "service_duration      212.875    192.665   191.602   194.159   77.9133   \n",
       "start_stop_id           36217      35756     37504     14613     37649   \n",
       "end_stop_id             35501      35827     23921     37504     13730   \n",
       "num_stops                  42         41        51        55        26   \n",
       "start_zone          סובב חיפה  סובב חיפה    גוש דן    גוש דן    גוש דן   \n",
       "end_zone            סובב חיפה  סובב חיפה    גוש דן    גוש דן    גוש דן   \n",
       "service_speed         23.3945    24.5599   20.5875   20.4368   17.5475   \n",
       "mean_trip_distance      25539      25440     22286     22418      8764   \n",
       "mean_trip_duration    1.09167    1.03583    1.0825   1.09694  0.499444   \n",
       "\n",
       "                        4352          338           339       6549      4481  \n",
       "route_id                2261         10746         10747      9817      2533  \n",
       "route_short_name           5             1             1       204        66  \n",
       "route_type                 3             0             0         3         3  \n",
       "num_trips                155           148           148       143       132  \n",
       "num_trip_starts          155           148           148       143       132  \n",
       "num_trip_ends            154           147           147       142       129  \n",
       "is_loop                    0             0             0         0         0  \n",
       "is_bidirectional           0             0             0         0         0  \n",
       "start_time          00:00:00      05:30:00      05:30:00  00:05:00  00:10:00  \n",
       "end_time            24:12:07      24:09:47      24:04:47  24:08:24  24:43:53  \n",
       "max_headway                7            10            10        12        12  \n",
       "min_headway                5             6             6         4         6  \n",
       "mean_headway         6.06838       6.18261       6.13793   6.78095    7.4375  \n",
       "peak_num_trips             7             3             3         7         9  \n",
       "peak_start_time     14:20:00      07:04:00      07:12:00  07:40:00  14:48:00  \n",
       "peak_end_time       14:22:07      07:09:47      07:13:47  07:40:24  14:53:53  \n",
       "service_distance     1364775       1972248       1972692   1411553   2363064  \n",
       "service_duration     82.9681       43.8656       33.9989   67.6867   118.543  \n",
       "start_stop_id          12889         35161         35119     13112     14323  \n",
       "end_stop_id            14318         35120         35162     13700     37504  \n",
       "num_stops                 26            23            23        33        40  \n",
       "start_zone            גוש דן  סובב ירושלים  סובב ירושלים    גוש דן    גוש דן  \n",
       "end_zone              גוש דן  סובב ירושלים  סובב ירושלים    גוש דן    גוש דן  \n",
       "service_speed        16.4494       44.9612       58.0222   20.8542   19.9342  \n",
       "mean_trip_distance      8805         13326         13329      9871     17902  \n",
       "mean_trip_duration  0.535278      0.296389      0.229722  0.473333  0.898056  "
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g.sort_values(by='num_trips', ascending=False).head(10).T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add stuff\n",
    "1. agency_id, agency_name\n",
    "1. route_long_name\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "g = (g\n",
    "     .merge(feed.routes[['route_id', 'route_long_name', 'agency_id']], how='left', on='route_id')\n",
    "     .merge(feed.agency[['agency_id', 'agency_name']], how='left', on='agency_id')\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "g = g[['route_id', 'route_short_name', 'agency_id', 'agency_name', 'route_long_name', 'route_type', \n",
    "           'num_trips', 'num_trip_starts', 'num_trip_ends', 'is_loop', \n",
    "           'is_bidirectional', 'start_time', 'end_time', 'max_headway', 'min_headway', \n",
    "           'mean_headway', 'peak_num_trips', 'peak_start_time', 'peak_end_time',\n",
    "           'service_distance', 'service_duration', 'service_speed',\n",
    "           'mean_trip_distance', 'mean_trip_duration', 'start_stop_id',\n",
    "           'end_stop_id', 'num_stops', 'start_zone', 'end_zone', \n",
    "           ]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>814</th>\n",
       "      <th>1534</th>\n",
       "      <th>816</th>\n",
       "      <th>810</th>\n",
       "      <th>812</th>\n",
       "      <th>1395</th>\n",
       "      <th>4516</th>\n",
       "      <th>1394</th>\n",
       "      <th>6544</th>\n",
       "      <th>5504</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>route_id</th>\n",
       "      <td>11685</td>\n",
       "      <td>14050</td>\n",
       "      <td>11689</td>\n",
       "      <td>11681</td>\n",
       "      <td>11683</td>\n",
       "      <td>13429</td>\n",
       "      <td>2709</td>\n",
       "      <td>13428</td>\n",
       "      <td>9809</td>\n",
       "      <td>6660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_short_name</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>129</td>\n",
       "      <td>1</td>\n",
       "      <td>172</td>\n",
       "      <td>417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>agency_id</th>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>agency_name</th>\n",
       "      <td>דן צפון</td>\n",
       "      <td>דן צפון</td>\n",
       "      <td>דן צפון</td>\n",
       "      <td>דן צפון</td>\n",
       "      <td>דן צפון</td>\n",
       "      <td>דן</td>\n",
       "      <td>דן</td>\n",
       "      <td>דן</td>\n",
       "      <td>דן</td>\n",
       "      <td>אגד</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_long_name</th>\n",
       "      <td>תחנה מרכזית חוף כרמל/רציפים עירוני-חיפה&lt;-&gt;מרכז...</td>\n",
       "      <td>מרכזית הקריות-קרית מוצקין&lt;-&gt;תחנה מרכזית חוף כר...</td>\n",
       "      <td>מרכזית הקריות-קרית מוצקין&lt;-&gt;תחנה מרכזית חוף כר...</td>\n",
       "      <td>הנביאים-חיפה&lt;-&gt;מרכזית הקריות-קרית מוצקין-10</td>\n",
       "      <td>מרכזית הקריות-קרית מוצקין&lt;-&gt;הנביאים-חיפה-20</td>\n",
       "      <td>מרכז הספורט/דרך בגין-בת ים&lt;-&gt;תחנה מרכזית פתח ת...</td>\n",
       "      <td>אבי האסירים-ראשון לציון&lt;-&gt;רדינג-תל אביב יפו-20</td>\n",
       "      <td>תחנה מרכזית פתח תקווה/רציפים עירוני-פתח תקווה&lt;...</td>\n",
       "      <td>האורגים-חולון&lt;-&gt;רדינג-תל אביב יפו-20</td>\n",
       "      <td>שד.נחל צאלים/נחל חבר-בית שמש&lt;-&gt;מסוף אגד/הר חוצ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_type</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_trips</th>\n",
       "      <td>195</td>\n",
       "      <td>27</td>\n",
       "      <td>186</td>\n",
       "      <td>123</td>\n",
       "      <td>123</td>\n",
       "      <td>177</td>\n",
       "      <td>55</td>\n",
       "      <td>177</td>\n",
       "      <td>111</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_trip_starts</th>\n",
       "      <td>195</td>\n",
       "      <td>27</td>\n",
       "      <td>186</td>\n",
       "      <td>123</td>\n",
       "      <td>123</td>\n",
       "      <td>177</td>\n",
       "      <td>55</td>\n",
       "      <td>177</td>\n",
       "      <td>111</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_trip_ends</th>\n",
       "      <td>192</td>\n",
       "      <td>27</td>\n",
       "      <td>184</td>\n",
       "      <td>121</td>\n",
       "      <td>121</td>\n",
       "      <td>173</td>\n",
       "      <td>55</td>\n",
       "      <td>173</td>\n",
       "      <td>110</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_loop</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_bidirectional</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_time</th>\n",
       "      <td>05:00:00</td>\n",
       "      <td>18:00:00</td>\n",
       "      <td>04:40:00</td>\n",
       "      <td>05:00:00</td>\n",
       "      <td>05:00:00</td>\n",
       "      <td>00:05:00</td>\n",
       "      <td>05:30:00</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>05:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_time</th>\n",
       "      <td>24:45:30</td>\n",
       "      <td>21:16:37</td>\n",
       "      <td>24:22:09</td>\n",
       "      <td>24:30:46</td>\n",
       "      <td>24:32:21</td>\n",
       "      <td>24:55:49</td>\n",
       "      <td>20:22:46</td>\n",
       "      <td>24:49:57</td>\n",
       "      <td>24:24:30</td>\n",
       "      <td>24:44:52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max_headway</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>25</td>\n",
       "      <td>7</td>\n",
       "      <td>12</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min_headway</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean_headway</th>\n",
       "      <td>4.4472</td>\n",
       "      <td>4</td>\n",
       "      <td>4.49315</td>\n",
       "      <td>8.18391</td>\n",
       "      <td>8.18391</td>\n",
       "      <td>5.744</td>\n",
       "      <td>17.575</td>\n",
       "      <td>5.752</td>\n",
       "      <td>9</td>\n",
       "      <td>11.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>peak_num_trips</th>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>16</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>peak_start_time</th>\n",
       "      <td>07:04:00</td>\n",
       "      <td>19:06:00</td>\n",
       "      <td>07:00:00</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>14:15:00</td>\n",
       "      <td>07:38:00</td>\n",
       "      <td>07:40:00</td>\n",
       "      <td>07:15:00</td>\n",
       "      <td>08:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>peak_end_time</th>\n",
       "      <td>07:05:30</td>\n",
       "      <td>19:06:37</td>\n",
       "      <td>07:02:09</td>\n",
       "      <td>08:03:46</td>\n",
       "      <td>08:05:21</td>\n",
       "      <td>14:15:49</td>\n",
       "      <td>07:42:46</td>\n",
       "      <td>07:44:57</td>\n",
       "      <td>07:19:30</td>\n",
       "      <td>08:04:52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>service_distance</th>\n",
       "      <td>4980105</td>\n",
       "      <td>741420</td>\n",
       "      <td>4731840</td>\n",
       "      <td>1949919</td>\n",
       "      <td>1948074</td>\n",
       "      <td>3967986</td>\n",
       "      <td>1267200</td>\n",
       "      <td>3944622</td>\n",
       "      <td>1879341</td>\n",
       "      <td>3785793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>service_duration</th>\n",
       "      <td>212.875</td>\n",
       "      <td>29.9775</td>\n",
       "      <td>192.665</td>\n",
       "      <td>145.072</td>\n",
       "      <td>148.318</td>\n",
       "      <td>194.159</td>\n",
       "      <td>66.7028</td>\n",
       "      <td>191.602</td>\n",
       "      <td>100.825</td>\n",
       "      <td>96.2189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>service_speed</th>\n",
       "      <td>23.3945</td>\n",
       "      <td>24.7325</td>\n",
       "      <td>24.5599</td>\n",
       "      <td>13.4411</td>\n",
       "      <td>13.1345</td>\n",
       "      <td>20.4368</td>\n",
       "      <td>18.9977</td>\n",
       "      <td>20.5875</td>\n",
       "      <td>18.6396</td>\n",
       "      <td>39.3456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean_trip_distance</th>\n",
       "      <td>25539</td>\n",
       "      <td>27460</td>\n",
       "      <td>25440</td>\n",
       "      <td>15853</td>\n",
       "      <td>15838</td>\n",
       "      <td>22418</td>\n",
       "      <td>23040</td>\n",
       "      <td>22286</td>\n",
       "      <td>16931</td>\n",
       "      <td>42537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean_trip_duration</th>\n",
       "      <td>1.09167</td>\n",
       "      <td>1.11028</td>\n",
       "      <td>1.03583</td>\n",
       "      <td>1.17944</td>\n",
       "      <td>1.20583</td>\n",
       "      <td>1.09694</td>\n",
       "      <td>1.21278</td>\n",
       "      <td>1.0825</td>\n",
       "      <td>0.908333</td>\n",
       "      <td>1.08111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_stop_id</th>\n",
       "      <td>36217</td>\n",
       "      <td>35756</td>\n",
       "      <td>35756</td>\n",
       "      <td>3734</td>\n",
       "      <td>35756</td>\n",
       "      <td>14613</td>\n",
       "      <td>23881</td>\n",
       "      <td>37504</td>\n",
       "      <td>24174</td>\n",
       "      <td>10840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_stop_id</th>\n",
       "      <td>35501</td>\n",
       "      <td>35827</td>\n",
       "      <td>35827</td>\n",
       "      <td>35501</td>\n",
       "      <td>3734</td>\n",
       "      <td>37504</td>\n",
       "      <td>13700</td>\n",
       "      <td>23921</td>\n",
       "      <td>13700</td>\n",
       "      <td>10684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_stops</th>\n",
       "      <td>42</td>\n",
       "      <td>42</td>\n",
       "      <td>41</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>55</td>\n",
       "      <td>56</td>\n",
       "      <td>51</td>\n",
       "      <td>51</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_zone</th>\n",
       "      <td>סובב חיפה</td>\n",
       "      <td>סובב חיפה</td>\n",
       "      <td>סובב חיפה</td>\n",
       "      <td>סובב חיפה</td>\n",
       "      <td>סובב חיפה</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>אזור בית שמש</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_zone</th>\n",
       "      <td>סובב חיפה</td>\n",
       "      <td>סובב חיפה</td>\n",
       "      <td>סובב חיפה</td>\n",
       "      <td>סובב חיפה</td>\n",
       "      <td>סובב חיפה</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>סובב ירושלים</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                 814   \\\n",
       "route_id                                                        11685   \n",
       "route_short_name                                                    1   \n",
       "agency_id                                                          30   \n",
       "agency_name                                                   דן צפון   \n",
       "route_long_name     תחנה מרכזית חוף כרמל/רציפים עירוני-חיפה<->מרכז...   \n",
       "route_type                                                          3   \n",
       "num_trips                                                         195   \n",
       "num_trip_starts                                                   195   \n",
       "num_trip_ends                                                     192   \n",
       "is_loop                                                             0   \n",
       "is_bidirectional                                                    0   \n",
       "start_time                                                   05:00:00   \n",
       "end_time                                                     24:45:30   \n",
       "max_headway                                                         5   \n",
       "min_headway                                                         4   \n",
       "mean_headway                                                   4.4472   \n",
       "peak_num_trips                                                     17   \n",
       "peak_start_time                                              07:04:00   \n",
       "peak_end_time                                                07:05:30   \n",
       "service_distance                                              4980105   \n",
       "service_duration                                              212.875   \n",
       "service_speed                                                 23.3945   \n",
       "mean_trip_distance                                              25539   \n",
       "mean_trip_duration                                            1.09167   \n",
       "start_stop_id                                                   36217   \n",
       "end_stop_id                                                     35501   \n",
       "num_stops                                                          42   \n",
       "start_zone                                                  סובב חיפה   \n",
       "end_zone                                                    סובב חיפה   \n",
       "\n",
       "                                                                 1534  \\\n",
       "route_id                                                        14050   \n",
       "route_short_name                                                    1   \n",
       "agency_id                                                          30   \n",
       "agency_name                                                   דן צפון   \n",
       "route_long_name     מרכזית הקריות-קרית מוצקין<->תחנה מרכזית חוף כר...   \n",
       "route_type                                                          3   \n",
       "num_trips                                                          27   \n",
       "num_trip_starts                                                    27   \n",
       "num_trip_ends                                                      27   \n",
       "is_loop                                                             0   \n",
       "is_bidirectional                                                    0   \n",
       "start_time                                                   18:00:00   \n",
       "end_time                                                     21:16:37   \n",
       "max_headway                                                         4   \n",
       "min_headway                                                         4   \n",
       "mean_headway                                                        4   \n",
       "peak_num_trips                                                     17   \n",
       "peak_start_time                                              19:06:00   \n",
       "peak_end_time                                                19:06:37   \n",
       "service_distance                                               741420   \n",
       "service_duration                                              29.9775   \n",
       "service_speed                                                 24.7325   \n",
       "mean_trip_distance                                              27460   \n",
       "mean_trip_duration                                            1.11028   \n",
       "start_stop_id                                                   35756   \n",
       "end_stop_id                                                     35827   \n",
       "num_stops                                                          42   \n",
       "start_zone                                                  סובב חיפה   \n",
       "end_zone                                                    סובב חיפה   \n",
       "\n",
       "                                                                 816   \\\n",
       "route_id                                                        11689   \n",
       "route_short_name                                                    1   \n",
       "agency_id                                                          30   \n",
       "agency_name                                                   דן צפון   \n",
       "route_long_name     מרכזית הקריות-קרית מוצקין<->תחנה מרכזית חוף כר...   \n",
       "route_type                                                          3   \n",
       "num_trips                                                         186   \n",
       "num_trip_starts                                                   186   \n",
       "num_trip_ends                                                     184   \n",
       "is_loop                                                             0   \n",
       "is_bidirectional                                                    0   \n",
       "start_time                                                   04:40:00   \n",
       "end_time                                                     24:22:09   \n",
       "max_headway                                                         5   \n",
       "min_headway                                                         4   \n",
       "mean_headway                                                  4.49315   \n",
       "peak_num_trips                                                     16   \n",
       "peak_start_time                                              07:00:00   \n",
       "peak_end_time                                                07:02:09   \n",
       "service_distance                                              4731840   \n",
       "service_duration                                              192.665   \n",
       "service_speed                                                 24.5599   \n",
       "mean_trip_distance                                              25440   \n",
       "mean_trip_duration                                            1.03583   \n",
       "start_stop_id                                                   35756   \n",
       "end_stop_id                                                     35827   \n",
       "num_stops                                                          41   \n",
       "start_zone                                                  סובב חיפה   \n",
       "end_zone                                                    סובב חיפה   \n",
       "\n",
       "                                                           810   \\\n",
       "route_id                                                  11681   \n",
       "route_short_name                                              3   \n",
       "agency_id                                                    30   \n",
       "agency_name                                             דן צפון   \n",
       "route_long_name     הנביאים-חיפה<->מרכזית הקריות-קרית מוצקין-10   \n",
       "route_type                                                    3   \n",
       "num_trips                                                   123   \n",
       "num_trip_starts                                             123   \n",
       "num_trip_ends                                               121   \n",
       "is_loop                                                       0   \n",
       "is_bidirectional                                              0   \n",
       "start_time                                             05:00:00   \n",
       "end_time                                               24:30:46   \n",
       "max_headway                                                  10   \n",
       "min_headway                                                   5   \n",
       "mean_headway                                            8.18391   \n",
       "peak_num_trips                                               14   \n",
       "peak_start_time                                        08:00:00   \n",
       "peak_end_time                                          08:03:46   \n",
       "service_distance                                        1949919   \n",
       "service_duration                                        145.072   \n",
       "service_speed                                           13.4411   \n",
       "mean_trip_distance                                        15853   \n",
       "mean_trip_duration                                      1.17944   \n",
       "start_stop_id                                              3734   \n",
       "end_stop_id                                               35501   \n",
       "num_stops                                                    28   \n",
       "start_zone                                            סובב חיפה   \n",
       "end_zone                                              סובב חיפה   \n",
       "\n",
       "                                                           812   \\\n",
       "route_id                                                  11683   \n",
       "route_short_name                                              3   \n",
       "agency_id                                                    30   \n",
       "agency_name                                             דן צפון   \n",
       "route_long_name     מרכזית הקריות-קרית מוצקין<->הנביאים-חיפה-20   \n",
       "route_type                                                    3   \n",
       "num_trips                                                   123   \n",
       "num_trip_starts                                             123   \n",
       "num_trip_ends                                               121   \n",
       "is_loop                                                       0   \n",
       "is_bidirectional                                              0   \n",
       "start_time                                             05:00:00   \n",
       "end_time                                               24:32:21   \n",
       "max_headway                                                  10   \n",
       "min_headway                                                   5   \n",
       "mean_headway                                            8.18391   \n",
       "peak_num_trips                                               14   \n",
       "peak_start_time                                        08:00:00   \n",
       "peak_end_time                                          08:05:21   \n",
       "service_distance                                        1948074   \n",
       "service_duration                                        148.318   \n",
       "service_speed                                           13.1345   \n",
       "mean_trip_distance                                        15838   \n",
       "mean_trip_duration                                      1.20583   \n",
       "start_stop_id                                             35756   \n",
       "end_stop_id                                                3734   \n",
       "num_stops                                                    28   \n",
       "start_zone                                            סובב חיפה   \n",
       "end_zone                                              סובב חיפה   \n",
       "\n",
       "                                                                 1395  \\\n",
       "route_id                                                        13429   \n",
       "route_short_name                                                    1   \n",
       "agency_id                                                           5   \n",
       "agency_name                                                        דן   \n",
       "route_long_name     מרכז הספורט/דרך בגין-בת ים<->תחנה מרכזית פתח ת...   \n",
       "route_type                                                          3   \n",
       "num_trips                                                         177   \n",
       "num_trip_starts                                                   177   \n",
       "num_trip_ends                                                     173   \n",
       "is_loop                                                             0   \n",
       "is_bidirectional                                                    0   \n",
       "start_time                                                   00:05:00   \n",
       "end_time                                                     24:55:49   \n",
       "max_headway                                                         6   \n",
       "min_headway                                                         5   \n",
       "mean_headway                                                    5.744   \n",
       "peak_num_trips                                                     14   \n",
       "peak_start_time                                              14:15:00   \n",
       "peak_end_time                                                14:15:49   \n",
       "service_distance                                              3967986   \n",
       "service_duration                                              194.159   \n",
       "service_speed                                                 20.4368   \n",
       "mean_trip_distance                                              22418   \n",
       "mean_trip_duration                                            1.09694   \n",
       "start_stop_id                                                   14613   \n",
       "end_stop_id                                                     37504   \n",
       "num_stops                                                          55   \n",
       "start_zone                                                     גוש דן   \n",
       "end_zone                                                       גוש דן   \n",
       "\n",
       "                                                              4516  \\\n",
       "route_id                                                      2709   \n",
       "route_short_name                                               129   \n",
       "agency_id                                                        5   \n",
       "agency_name                                                     דן   \n",
       "route_long_name     אבי האסירים-ראשון לציון<->רדינג-תל אביב יפו-20   \n",
       "route_type                                                       3   \n",
       "num_trips                                                       55   \n",
       "num_trip_starts                                                 55   \n",
       "num_trip_ends                                                   55   \n",
       "is_loop                                                          0   \n",
       "is_bidirectional                                                 0   \n",
       "start_time                                                05:30:00   \n",
       "end_time                                                  20:22:46   \n",
       "max_headway                                                     25   \n",
       "min_headway                                                      5   \n",
       "mean_headway                                                17.575   \n",
       "peak_num_trips                                                  13   \n",
       "peak_start_time                                           07:38:00   \n",
       "peak_end_time                                             07:42:46   \n",
       "service_distance                                           1267200   \n",
       "service_duration                                           66.7028   \n",
       "service_speed                                              18.9977   \n",
       "mean_trip_distance                                           23040   \n",
       "mean_trip_duration                                         1.21278   \n",
       "start_stop_id                                                23881   \n",
       "end_stop_id                                                  13700   \n",
       "num_stops                                                       56   \n",
       "start_zone                                                  גוש דן   \n",
       "end_zone                                                    גוש דן   \n",
       "\n",
       "                                                                 1394  \\\n",
       "route_id                                                        13428   \n",
       "route_short_name                                                    1   \n",
       "agency_id                                                           5   \n",
       "agency_name                                                        דן   \n",
       "route_long_name     תחנה מרכזית פתח תקווה/רציפים עירוני-פתח תקווה<...   \n",
       "route_type                                                          3   \n",
       "num_trips                                                         177   \n",
       "num_trip_starts                                                   177   \n",
       "num_trip_ends                                                     173   \n",
       "is_loop                                                             0   \n",
       "is_bidirectional                                                    0   \n",
       "start_time                                                   00:00:00   \n",
       "end_time                                                     24:49:57   \n",
       "max_headway                                                         7   \n",
       "min_headway                                                         5   \n",
       "mean_headway                                                    5.752   \n",
       "peak_num_trips                                                     13   \n",
       "peak_start_time                                              07:40:00   \n",
       "peak_end_time                                                07:44:57   \n",
       "service_distance                                              3944622   \n",
       "service_duration                                              191.602   \n",
       "service_speed                                                 20.5875   \n",
       "mean_trip_distance                                              22286   \n",
       "mean_trip_duration                                             1.0825   \n",
       "start_stop_id                                                   37504   \n",
       "end_stop_id                                                     23921   \n",
       "num_stops                                                          51   \n",
       "start_zone                                                     גוש דן   \n",
       "end_zone                                                       גוש דן   \n",
       "\n",
       "                                                    6544  \\\n",
       "route_id                                            9809   \n",
       "route_short_name                                     172   \n",
       "agency_id                                              5   \n",
       "agency_name                                           דן   \n",
       "route_long_name     האורגים-חולון<->רדינג-תל אביב יפו-20   \n",
       "route_type                                             3   \n",
       "num_trips                                            111   \n",
       "num_trip_starts                                      111   \n",
       "num_trip_ends                                        110   \n",
       "is_loop                                                0   \n",
       "is_bidirectional                                       0   \n",
       "start_time                                      00:00:00   \n",
       "end_time                                        24:24:30   \n",
       "max_headway                                           12   \n",
       "min_headway                                            5   \n",
       "mean_headway                                           9   \n",
       "peak_num_trips                                        12   \n",
       "peak_start_time                                 07:15:00   \n",
       "peak_end_time                                   07:19:30   \n",
       "service_distance                                 1879341   \n",
       "service_duration                                 100.825   \n",
       "service_speed                                    18.6396   \n",
       "mean_trip_distance                                 16931   \n",
       "mean_trip_duration                              0.908333   \n",
       "start_stop_id                                      24174   \n",
       "end_stop_id                                        13700   \n",
       "num_stops                                             51   \n",
       "start_zone                                        גוש דן   \n",
       "end_zone                                          גוש דן   \n",
       "\n",
       "                                                                 5504  \n",
       "route_id                                                         6660  \n",
       "route_short_name                                                  417  \n",
       "agency_id                                                           3  \n",
       "agency_name                                                       אגד  \n",
       "route_long_name     שד.נחל צאלים/נחל חבר-בית שמש<->מסוף אגד/הר חוצ...  \n",
       "route_type                                                          3  \n",
       "num_trips                                                          89  \n",
       "num_trip_starts                                                    89  \n",
       "num_trip_ends                                                      86  \n",
       "is_loop                                                             0  \n",
       "is_bidirectional                                                    0  \n",
       "start_time                                                   05:30:00  \n",
       "end_time                                                     24:44:52  \n",
       "max_headway                                                        20  \n",
       "min_headway                                                         5  \n",
       "mean_headway                                                    11.25  \n",
       "peak_num_trips                                                     12  \n",
       "peak_start_time                                              08:00:00  \n",
       "peak_end_time                                                08:04:52  \n",
       "service_distance                                              3785793  \n",
       "service_duration                                              96.2189  \n",
       "service_speed                                                 39.3456  \n",
       "mean_trip_distance                                              42537  \n",
       "mean_trip_duration                                            1.08111  \n",
       "start_stop_id                                                   10840  \n",
       "end_stop_id                                                     10684  \n",
       "num_stops                                                          43  \n",
       "start_zone                                               אזור בית שמש  \n",
       "end_zone                                                 סובב ירושלים  "
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g.sort_values(by='peak_num_trips', ascending=False).head(10).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "g.sort_values(by='peak_num_trips', ascending=False).head(10).T.to_csv('180305_route_stats_top10_peak_num_trips.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    6673\n",
       "Name: is_bidirectional, dtype: int64"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g.is_bidirectional.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "start_zone\n",
       "אופקים                        16\n",
       "אזור בית שמש                  51\n",
       "אזור חדרה                     19\n",
       "אזור קריית ארבע                2\n",
       "אשדוד                         74\n",
       "אשקלון                        89\n",
       "באר שבע                       79\n",
       "בית שאן                       14\n",
       "גוש דן                       774\n",
       "גוש עציון                      2\n",
       "גליל עליון ורמת הגולן        251\n",
       "הרי ירושלים                   21\n",
       "השומרון                       44\n",
       "זכרון                         32\n",
       "חבל מודיעין                  118\n",
       "חריש ואדי ערה                134\n",
       "יוקנעם- טבעון                 78\n",
       "ירוחם                          2\n",
       "כרמיאל                       159\n",
       "נהריה                        125\n",
       "נצרת                         223\n",
       "נתיבות שדרות                  60\n",
       "נתניה                        186\n",
       "סובב חיפה                    395\n",
       "סובב ירושלים                 576\n",
       "סובב כנרת ודרום רמת הגולן    103\n",
       "עומר חורה                      2\n",
       "עכו                           64\n",
       "עפולה                         54\n",
       "ערד דימונה                    59\n",
       "קריית גת                      27\n",
       "ראש העין שוהם                 60\n",
       "רהט להבים                     21\n",
       "רחובות                       112\n",
       "שרון                         179\n",
       "Name: route_id, dtype: int64"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g[g.start_zone==g.end_zone].groupby('start_zone').route_id.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6673, 29)"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### add date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "g['date'] = "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What's next\n",
    "\n",
    "TODO\n",
    "\n",
    "1. add date\n",
    "1. add split_directions\n",
    "1. add time between stops - max, min, mean (using delta)\n",
    "1. integrate with custom day cutoff\n",
    "1. add day and night headways and num_trips (maybe noon also)\n",
    "1. create a function for converting this to a timeseries good for pandas\n",
    "1. put this all back into proper documented functions\n",
    "1. write tests\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}