openbus_15_gtfs_stats_prod.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": 2,
   "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": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import partridge as ptg\n",
    "import datetime\n",
    "\n",
    "from gtfs_utils import *\n",
    "import gtfstk\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import OrderedDict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def compute_trip_stats_partridge(feed, zones):\n",
    "    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",
    "        )\n",
    "    geometry_by_stop = gtfstk.build_geometry_by_stop(feed, use_utm=True)\n",
    "    \n",
    "    g = f.groupby('trip_id')\n",
    "    \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)\n",
    "    \n",
    "    h = g.apply(my_agg)\n",
    "    h['distance'] = g.shape_dist_traveled.max()\n",
    "    \n",
    "    # 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",
    "        )\n",
    "    return h\n",
    "\n",
    "def compute_route_stats_base_partridge(trip_stats_subset,\n",
    "  headway_start_time='07:00:00', headway_end_time='19:00:00', *,\n",
    "  split_directions=False):\n",
    "    \"\"\"\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",
    "    f = trip_stats_subset.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)\n",
    "    \n",
    "    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)\n",
    "    \n",
    "    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']\n",
    "    \n",
    "    # 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))\n",
    "    \n",
    "    g['service_speed'] = g.service_speed/1000\n",
    "    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",
    "        )\n",
    "    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",
    "           ]]\n",
    "    \n",
    "    return g"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import logging\n",
    "\n",
    "# create logger with 'gtfs_stats'\n",
    "logger = logging.getLogger('gtfs_stats')\n",
    "logger.setLevel(logging.DEBUG)\n",
    "# create file handler which logs even debug messages\n",
    "fh = logging.FileHandler('gtfs_stats.log')\n",
    "fh.setLevel(logging.DEBUG)\n",
    "# create console handler with a higher log level\n",
    "ch = logging.StreamHandler()\n",
    "ch.setLevel(logging.ERROR)\n",
    "# create formatter and add it to the handlers\n",
    "formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')\n",
    "fh.setFormatter(formatter)\n",
    "ch.setFormatter(formatter)\n",
    "# add the handlers to the logger\n",
    "logger.addHandler(fh)\n",
    "logger.addHandler(ch)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import os\n",
    "import re\n",
    "import boto3\n",
    "\n",
    "GTFS_FEEDS_PATH = 'data/gtfs_feeds/'\n",
    "\n",
    "OUTPUT_DIR = 'data/gtfs_stats/'\n",
    "OUTPUT_FILE_NAME_RE = re.compile('^(?P<date_str>[^_]+?)_(?P<type>\\w+)\\.pkl\\.gz')\n",
    "\n",
    "BUCKET_NAME = 's3.obus.hasadna.org.il'\n",
    "BUCKET_VALID_FILES_RE = re.compile('2018-0[234567]-\\d\\d\\.zip')\n",
    "\n",
    "STATS_TYPES = ['trip_stats', 'route_stats']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def batch_stats(folder=GTFS_FEEDS_PATH, output_folder=OUTPUT_DIR):\n",
    "    for file in os.listdir(folder):\n",
    "        date_str = file.split('.')[0]\n",
    "        date = datetime.datetime.strptime(date_str, '%Y-%m-%d').date()\n",
    "        feed = get_partridge_feed_by_date(output_folder+file, date)\n",
    "        zones = get_zones_df(LOCAL_TARIFF_PATH)\n",
    "        ts = compute_trip_stats_partridge(feed, zones)\n",
    "        ts.to_pickle(output_folder+date_str+'_trip_stats.pkl.gz', compression='gzip')\n",
    "        rs = compute_route_stats_base_partridge(ts)\n",
    "        rs.to_pickle(output_folder+date_str+'_route_stats.pkl.gz', compression='gzip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def batch_stats_s3(bucket_name = BUCKET_NAME, output_folder = OUTPUT_DIR, delete_gtfs_zips=True):\n",
    "    try:\n",
    "        existing_output_files = [(g[0], g[1]) for g in \n",
    "                                  (re.match(OUTPUT_FILE_NAME_RE, file).groups() \n",
    "                                   for file in os.listdir(OUTPUT_DIR))]\n",
    "\n",
    "        logger.info(f'found {len(existing_output_files)} output files in {output_folder}')\n",
    "\n",
    "        s3 = boto3.resource('s3')\n",
    "        bucket = s3.Bucket(bucket_name)\n",
    "\n",
    "        logger.info(f'connected to S3 bucket {bucket_name}')\n",
    "\n",
    "        valid_files = [obj.key for obj in bucket.objects.all() \n",
    "                       if re.match(BUCKET_VALID_FILES_RE, obj.key) and \n",
    "                                   obj.key not in [g[0]+'.zip' \n",
    "                                                   for g in existing_output_files \n",
    "                                                   if g[1]=='route_stats']]\n",
    "        \n",
    "        logger.info(f'found {len(valid_files)} valid files (GTFS) in bucket {bucket_name}')\n",
    "        logger.debug(f'Files: {valid_files}')\n",
    "\n",
    "        logger.info(f'starting synchronous gtfs file download and stats computation from  s3 bucket {bucket_name}')\n",
    "        for file in valid_files:\n",
    "            logger.info(f'starting file download (key=\"{file}\", local path=\"{output_folder+file}\")')\n",
    "            bucket.download_file(file, output_folder+file)\n",
    "            logger.debug(f'finished file download (key=\"{file}\", local path=\"{output_folder+file}\")')\n",
    "            \n",
    "            logger.info(f'extracting date from file name \"{file}\"')\n",
    "            date_str = file.split('.')[0]\n",
    "            date = datetime.datetime.strptime(date_str, '%Y-%m-%d').date()\n",
    "            \n",
    "            logger.info(f'starting create daily partridge feed for file \"{output_folder+file}\" with date \"{date}\"')\n",
    "            feed = get_partridge_feed_by_date(output_folder+file, date)\n",
    "            logger.debug(f'finished create daily partridge feed for file \"{output_folder+file}\" with date \"{date}\"')\n",
    "            # TODO: add changing zones from archive\n",
    "            \n",
    "            logger.info(f'creating zones DF from \"{LOCAL_TARIFF_PATH}\"')\n",
    "            zones = get_zones_df(LOCAL_TARIFF_PATH)\n",
    "            \n",
    "            logger.info(f'starting compute_trip_stats_partridge for file \"{output_folder+file}\" with date \"{date}\" and zones \"{LOCAL_TARIFF_PATH}\"')\n",
    "            ts = compute_trip_stats_partridge(feed, zones)\n",
    "            logger.debug(f'finished compute_trip_stats_partridge for file \"{output_folder+file}\" with date \"{date}\" and zones \"{LOCAL_TARIFF_PATH}\"')\n",
    "            \n",
    "            trip_stats_output_path = output_folder+date_str+'_trip_stats.pkl.gz'\n",
    "            logger.info(f'saving trip stats result DF to gzipped pickle \"{trip_stats_output_path}\"')\n",
    "            ts.to_pickle(trip_stats_output_path, compression='gzip')\n",
    "            \n",
    "            logger.info(f'starting compute_route_stats_base_partridge from trip stats result')\n",
    "            rs = compute_route_stats_base_partridge(ts)\n",
    "            logger.debug(f'finished compute_route_stats_base_partridge from trip stats result')\n",
    "            \n",
    "            route_stats_output_path = output_folder+date_str+'_route_stats.pkl.gz'\n",
    "            logger.info(f'saving route stats result DF to gzipped pickle \"{route_stats_output_path}\"')\n",
    "            rs.to_pickle(route_stats_output_path, compression='gzip')\n",
    "            if delete_gtfs_zips:\n",
    "                logging.info(f'deleting gtfs zip file \"{output_folder+file}\"')\n",
    "                os.remove(output_folder+file)\n",
    "            else:\n",
    "                logging.debug(f'keeping gtfs zip file \"{output_folder+file}\"')\n",
    "    except:\n",
    "        logger.error('Failed', exc_info=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "logger.info(f'starting batch_stats_s3 with default config')\n",
    "batch_stats_s3()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What's next\n",
    "\n",
    "TODO\n",
    "\n",
    "1. put this all back into proper documented functions\n",
    "1. add date\n",
    "1. integrate with custom day cutoff\n",
    "1. add logging\n",
    "1. write tests\n",
    "1. add split_directions\n",
    "1. add time between stops - max, min, mean (using delta)\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",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "import glob\n",
    "rs = pd.concat([pd.read_pickle(f, compression='gzip') for f in glob.glob('data\\\\gtfs_stats_MOD\\\\*_route_stats.pkl.gz')]) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1447084, 30)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rs.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>route_id</th>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>10000</td>\n",
       "      <td>10001</td>\n",
       "      <td>10002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_short_name</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>91</td>\n",
       "      <td>91</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>agency_id</th>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>route_long_name</th>\n",
       "      <td>תחנת רכבת יבנה מערב-יבנה&lt;-&gt;תחנת רכבת מזרח-יבנה-1#</td>\n",
       "      <td>תחנת רכבת מזרח-יבנה&lt;-&gt;תחנת רכבת יבנה מערב-יבנה-2#</td>\n",
       "      <td>קופת חולים/הנשיא-צפת&lt;-&gt;דרך חטיבת יפתח-צפת-11</td>\n",
       "      <td>האר''י/ישראל ב''ק-צפת&lt;-&gt;שפרינצק/קרן היסוד-צפת-2#</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_trips</th>\n",
       "      <td>26</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_trip_starts</th>\n",
       "      <td>26</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_trip_ends</th>\n",
       "      <td>26</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</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>is_bidirectional</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>start_time</th>\n",
       "      <td>05:10:00</td>\n",
       "      <td>06:05:00</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>13:00:00</td>\n",
       "      <td>07:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_time</th>\n",
       "      <td>21:54:07</td>\n",
       "      <td>22:51:36</td>\n",
       "      <td>08:17:31</td>\n",
       "      <td>16:51:42</td>\n",
       "      <td>07:58:22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max_headway</th>\n",
       "      <td>65</td>\n",
       "      <td>50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min_headway</th>\n",
       "      <td>20</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean_headway</th>\n",
       "      <td>37.8125</td>\n",
       "      <td>28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>peak_num_trips</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>peak_start_time</th>\n",
       "      <td>05:10:00</td>\n",
       "      <td>15:55:00</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>13:00:00</td>\n",
       "      <td>07:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>peak_end_time</th>\n",
       "      <td>05:29:07</td>\n",
       "      <td>16:06:36</td>\n",
       "      <td>08:17:31</td>\n",
       "      <td>13:21:42</td>\n",
       "      <td>07:58:22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>service_distance</th>\n",
       "      <td>183170</td>\n",
       "      <td>283920</td>\n",
       "      <td>6326</td>\n",
       "      <td>41880</td>\n",
       "      <td>7156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>service_duration</th>\n",
       "      <td>8.28389</td>\n",
       "      <td>12.6</td>\n",
       "      <td>0.291944</td>\n",
       "      <td>1.80833</td>\n",
       "      <td>0.472778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>service_speed</th>\n",
       "      <td>22.1116</td>\n",
       "      <td>22.5333</td>\n",
       "      <td>21.6685</td>\n",
       "      <td>23.1594</td>\n",
       "      <td>15.1361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean_trip_distance</th>\n",
       "      <td>7045</td>\n",
       "      <td>8112</td>\n",
       "      <td>6326</td>\n",
       "      <td>8376</td>\n",
       "      <td>7156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean_trip_duration</th>\n",
       "      <td>0.318611</td>\n",
       "      <td>0.36</td>\n",
       "      <td>0.291944</td>\n",
       "      <td>0.361667</td>\n",
       "      <td>0.472778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>start_stop_id</th>\n",
       "      <td>38725</td>\n",
       "      <td>15745</td>\n",
       "      <td>18423</td>\n",
       "      <td>37179</td>\n",
       "      <td>7270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_stop_id</th>\n",
       "      <td>15745</td>\n",
       "      <td>38725</td>\n",
       "      <td>38800</td>\n",
       "      <td>18420</td>\n",
       "      <td>17976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_stops</th>\n",
       "      <td>18</td>\n",
       "      <td>25</td>\n",
       "      <td>33</td>\n",
       "      <td>26</td>\n",
       "      <td>17</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>date</th>\n",
       "      <td>2017-11-01</td>\n",
       "      <td>2017-11-01</td>\n",
       "      <td>2017-11-01</td>\n",
       "      <td>2017-11-01</td>\n",
       "      <td>2017-11-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                    0  \\\n",
       "route_id                                                            1   \n",
       "route_short_name                                                    1   \n",
       "agency_id                                                          25   \n",
       "agency_name                                                    אפיקים   \n",
       "route_long_name     תחנת רכבת יבנה מערב-יבנה<->תחנת רכבת מזרח-יבנה-1#   \n",
       "route_type                                                          3   \n",
       "num_trips                                                          26   \n",
       "num_trip_starts                                                    26   \n",
       "num_trip_ends                                                      26   \n",
       "is_loop                                                             0   \n",
       "is_bidirectional                                                    0   \n",
       "start_time                                                   05:10:00   \n",
       "end_time                                                     21:54:07   \n",
       "max_headway                                                        65   \n",
       "min_headway                                                        20   \n",
       "mean_headway                                                  37.8125   \n",
       "peak_num_trips                                                      1   \n",
       "peak_start_time                                              05:10:00   \n",
       "peak_end_time                                                05:29:07   \n",
       "service_distance                                               183170   \n",
       "service_duration                                              8.28389   \n",
       "service_speed                                                 22.1116   \n",
       "mean_trip_distance                                               7045   \n",
       "mean_trip_duration                                           0.318611   \n",
       "start_stop_id                                                   38725   \n",
       "end_stop_id                                                     15745   \n",
       "num_stops                                                          18   \n",
       "start_zone                                                     רחובות   \n",
       "end_zone                                                       רחובות   \n",
       "date                                                       2017-11-01   \n",
       "\n",
       "                                                                    1  \\\n",
       "route_id                                                           10   \n",
       "route_short_name                                                    4   \n",
       "agency_id                                                          25   \n",
       "agency_name                                                    אפיקים   \n",
       "route_long_name     תחנת רכבת מזרח-יבנה<->תחנת רכבת יבנה מערב-יבנה-2#   \n",
       "route_type                                                          3   \n",
       "num_trips                                                          35   \n",
       "num_trip_starts                                                    35   \n",
       "num_trip_ends                                                      35   \n",
       "is_loop                                                             0   \n",
       "is_bidirectional                                                    0   \n",
       "start_time                                                   06:05:00   \n",
       "end_time                                                     22:51:36   \n",
       "max_headway                                                        50   \n",
       "min_headway                                                        10   \n",
       "mean_headway                                                       28   \n",
       "peak_num_trips                                                      2   \n",
       "peak_start_time                                              15:55:00   \n",
       "peak_end_time                                                16:06:36   \n",
       "service_distance                                               283920   \n",
       "service_duration                                                 12.6   \n",
       "service_speed                                                 22.5333   \n",
       "mean_trip_distance                                               8112   \n",
       "mean_trip_duration                                               0.36   \n",
       "start_stop_id                                                   15745   \n",
       "end_stop_id                                                     38725   \n",
       "num_stops                                                          25   \n",
       "start_zone                                                     רחובות   \n",
       "end_zone                                                       רחובות   \n",
       "date                                                       2017-11-01   \n",
       "\n",
       "                                                               2  \\\n",
       "route_id                                                   10000   \n",
       "route_short_name                                              91   \n",
       "agency_id                                                     14   \n",
       "agency_name                                          נתיב אקספרס   \n",
       "route_long_name     קופת חולים/הנשיא-צפת<->דרך חטיבת יפתח-צפת-11   \n",
       "route_type                                                     3   \n",
       "num_trips                                                      1   \n",
       "num_trip_starts                                                1   \n",
       "num_trip_ends                                                  1   \n",
       "is_loop                                                        0   \n",
       "is_bidirectional                                               0   \n",
       "start_time                                              08:00:00   \n",
       "end_time                                                08:17:31   \n",
       "max_headway                                                  NaN   \n",
       "min_headway                                                  NaN   \n",
       "mean_headway                                                 NaN   \n",
       "peak_num_trips                                                 1   \n",
       "peak_start_time                                         08:00:00   \n",
       "peak_end_time                                           08:17:31   \n",
       "service_distance                                            6326   \n",
       "service_duration                                        0.291944   \n",
       "service_speed                                            21.6685   \n",
       "mean_trip_distance                                          6326   \n",
       "mean_trip_duration                                      0.291944   \n",
       "start_stop_id                                              18423   \n",
       "end_stop_id                                                38800   \n",
       "num_stops                                                     33   \n",
       "start_zone                                 גליל עליון ורמת הגולן   \n",
       "end_zone                                   גליל עליון ורמת הגולן   \n",
       "date                                                  2017-11-01   \n",
       "\n",
       "                                                                   3  \\\n",
       "route_id                                                       10001   \n",
       "route_short_name                                                  91   \n",
       "agency_id                                                         14   \n",
       "agency_name                                              נתיב אקספרס   \n",
       "route_long_name     האר''י/ישראל ב''ק-צפת<->שפרינצק/קרן היסוד-צפת-2#   \n",
       "route_type                                                         3   \n",
       "num_trips                                                          5   \n",
       "num_trip_starts                                                    5   \n",
       "num_trip_ends                                                      5   \n",
       "is_loop                                                            0   \n",
       "is_bidirectional                                                   0   \n",
       "start_time                                                  13:00:00   \n",
       "end_time                                                    16:51:42   \n",
       "max_headway                                                       90   \n",
       "min_headway                                                        0   \n",
       "mean_headway                                                    52.5   \n",
       "peak_num_trips                                                     2   \n",
       "peak_start_time                                             13:00:00   \n",
       "peak_end_time                                               13:21:42   \n",
       "service_distance                                               41880   \n",
       "service_duration                                             1.80833   \n",
       "service_speed                                                23.1594   \n",
       "mean_trip_distance                                              8376   \n",
       "mean_trip_duration                                          0.361667   \n",
       "start_stop_id                                                  37179   \n",
       "end_stop_id                                                    18420   \n",
       "num_stops                                                         26   \n",
       "start_zone                                     גליל עליון ורמת הגולן   \n",
       "end_zone                                       גליל עליון ורמת הגולן   \n",
       "date                                                      2017-11-01   \n",
       "\n",
       "                                                                    4  \n",
       "route_id                                                        10002  \n",
       "route_short_name                                                   92  \n",
       "agency_id                                                          14  \n",
       "agency_name                                               נתיב אקספרס  \n",
       "route_long_name     געתון/מירון-כפר ורדים<->בית ספר כפר ורדים-כפר ...  \n",
       "route_type                                                          3  \n",
       "num_trips                                                           1  \n",
       "num_trip_starts                                                     1  \n",
       "num_trip_ends                                                       1  \n",
       "is_loop                                                             0  \n",
       "is_bidirectional                                                    0  \n",
       "start_time                                                   07:30:00  \n",
       "end_time                                                     07:58:22  \n",
       "max_headway                                                       NaN  \n",
       "min_headway                                                       NaN  \n",
       "mean_headway                                                      NaN  \n",
       "peak_num_trips                                                      1  \n",
       "peak_start_time                                              07:30:00  \n",
       "peak_end_time                                                07:58:22  \n",
       "service_distance                                                 7156  \n",
       "service_duration                                             0.472778  \n",
       "service_speed                                                 15.1361  \n",
       "mean_trip_distance                                               7156  \n",
       "mean_trip_duration                                           0.472778  \n",
       "start_stop_id                                                    7270  \n",
       "end_stop_id                                                     17976  \n",
       "num_stops                                                          17  \n",
       "start_zone                                                      נהריה  \n",
       "end_zone                                                        נהריה  \n",
       "date                                                       2017-11-01  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rs.head().T"
   ]
  }
 ],
 "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
}