openbus_4_cleanup.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once more, I'm trying to show a play-by-play analysis of the Israeli GTFS, with [hasadna/open-bus](https://github.com/hasadna/open-bus) interests in mind.\n",
    "\n",
    "This post is more of a data cleanup diary log. While working on some stuff I found issues with the dataset I was working with, I thought it would be good to document this, and show the process of finding these issues, and how some simple analysis (AKA debugging) helped finding the source of these symptoms and a cure.\n",
    "\n",
    "The agenda:\n",
    "1. [Get up-to-date GTFS files from the MOT FTP server](#Get-the-data)\n",
    "2. [Merge and tidy](#Tidy-it-up)\n",
    "3. [Cleanup Diary](#Cleanup-Diary)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import datetime\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get the data\n",
    "We get the data straight from MOT's ftp. Just so I don't repeat the last post, this went out to a separate module."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'221 Goodbye.'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from gtfs_utils import *\n",
    "LOCAL_ZIP_PATH = 'data/sample/gtfs_171218.zip' \n",
    "\n",
    "conn = ftp_connect()\n",
    "ftp_dir = get_ftp_dir(conn)\n",
    "UPTODATE = 90 #days\n",
    "our_uptodateness = get_uptodateness(ftp_dir, local_zip_path = LOCAL_ZIP_PATH)\n",
    "\n",
    "if our_uptodateness > UPTODATE:\n",
    "    get_ftp_file(conn)\n",
    "    get_ftp_file(conn, file_name = 'Tariff.zip', local_zip_path = 'data/sample/tariff.zip' )\n",
    "\n",
    "conn.quit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We load MOT's tarrif.txt file, which will give us zone names and other local info.\n",
    "Found out it's the file is even more fd-up than I realized, so I add the southern zones manualy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "tariff_df = extract_tariff_df(local_zip_path = 'data/sample/tariff.zip')\n",
    "south = [\n",
    "    {\n",
    "        'zone_name': 'מצפה רמון',\n",
    "        'zone_id': '903'\n",
    "    },\n",
    "    {\n",
    "        'zone_name': 'ערבה',\n",
    "        'zone_id': '902'\n",
    "    },\n",
    "    {\n",
    "        'zone_name': 'אילת',\n",
    "        'zone_id': '901'\n",
    "    },]\n",
    "south = pd.DataFrame(south)\n",
    "tariff_df = tariff_df.append(south)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tidy it up\n",
    "Again I'm using [partridge](https://github.com/remix/partridge/tree/master/partridge) for filtering on dates, and then some tidying up and transformations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import partridge as ptg\n",
    "\n",
    "service_ids_by_date = ptg.read_service_ids_by_date(LOCAL_ZIP_PATH)\n",
    "service_ids = service_ids_by_date[datetime.date(2017, 12, 21)]\n",
    "\n",
    "feed = ptg.feed(LOCAL_ZIP_PATH, view={\n",
    "    'trips.txt': {\n",
    "        'service_id': service_ids,\n",
    "    },\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def to_timedelta(df):\n",
    "    '''\n",
    "    Turn time columns into timedelta dtype\n",
    "    '''\n",
    "    cols = ['arrival_time', 'departure_time']\n",
    "    numeric = df[cols].apply(pd.to_timedelta, unit='s')\n",
    "    df = df.copy()\n",
    "    df[cols] = numeric\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = feed.stops\n",
    "r = feed.routes\n",
    "t = (feed.trips\n",
    "     .assign(route_id=lambda x: pd.Categorical(x['route_id'])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "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>trip_id</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>date</th>\n",
       "      <th>stop_name</th>\n",
       "      <th>stop_lat</th>\n",
       "      <th>stop_lon</th>\n",
       "      <th>zone_id</th>\n",
       "      <th>zone_name</th>\n",
       "      <th>route_id</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>route_short_name</th>\n",
       "      <th>route_long_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>28917380_191217</td>\n",
       "      <td>08:57:00</td>\n",
       "      <td>08:57:00</td>\n",
       "      <td>37312</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>באר שבע מרכז</td>\n",
       "      <td>31.242886</td>\n",
       "      <td>34.798546</td>\n",
       "      <td>410</td>\n",
       "      <td>באר שבע</td>\n",
       "      <td>20950</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>באר שבע מרכז-באר שבע&lt;-&gt;תל אביב מרכז-תל אביב יפו</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>28917380_191217</td>\n",
       "      <td>09:04:00</td>\n",
       "      <td>09:04:00</td>\n",
       "      <td>37314</td>\n",
       "      <td>2</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>באר שבע-צפון</td>\n",
       "      <td>31.262089</td>\n",
       "      <td>34.809287</td>\n",
       "      <td>410</td>\n",
       "      <td>באר שבע</td>\n",
       "      <td>20950</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>באר שבע מרכז-באר שבע&lt;-&gt;תל אביב מרכז-תל אביב יפו</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28917380_191217</td>\n",
       "      <td>09:13:00</td>\n",
       "      <td>09:13:00</td>\n",
       "      <td>37308</td>\n",
       "      <td>3</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>להבים רהט</td>\n",
       "      <td>31.369907</td>\n",
       "      <td>34.798040</td>\n",
       "      <td>421</td>\n",
       "      <td>רהט להבים</td>\n",
       "      <td>20950</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>באר שבע מרכז-באר שבע&lt;-&gt;תל אביב מרכז-תל אביב יפו</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28917380_191217</td>\n",
       "      <td>09:29:00</td>\n",
       "      <td>09:29:00</td>\n",
       "      <td>37316</td>\n",
       "      <td>4</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>קרית גת</td>\n",
       "      <td>31.603526</td>\n",
       "      <td>34.777955</td>\n",
       "      <td>802</td>\n",
       "      <td>קריית גת</td>\n",
       "      <td>20950</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>באר שבע מרכז-באר שבע&lt;-&gt;תל אביב מרכז-תל אביב יפו</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28917380_191217</td>\n",
       "      <td>09:54:00</td>\n",
       "      <td>09:54:00</td>\n",
       "      <td>37336</td>\n",
       "      <td>5</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>רמלה</td>\n",
       "      <td>31.928809</td>\n",
       "      <td>34.877304</td>\n",
       "      <td>210</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>20950</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>באר שבע מרכז-באר שבע&lt;-&gt;תל אביב מרכז-תל אביב יפו</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           trip_id departure_time arrival_time stop_id  stop_sequence  \\\n",
       "0  28917380_191217       08:57:00     08:57:00   37312              1   \n",
       "1  28917380_191217       09:04:00     09:04:00   37314              2   \n",
       "2  28917380_191217       09:13:00     09:13:00   37308              3   \n",
       "3  28917380_191217       09:29:00     09:29:00   37316              4   \n",
       "4  28917380_191217       09:54:00     09:54:00   37336              5   \n",
       "\n",
       "         date     stop_name   stop_lat   stop_lon zone_id  zone_name route_id  \\\n",
       "0  2017-12-21  באר שבע מרכז  31.242886  34.798546     410    באר שבע    20950   \n",
       "1  2017-12-21  באר שבע-צפון  31.262089  34.809287     410    באר שבע    20950   \n",
       "2  2017-12-21     להבים רהט  31.369907  34.798040     421  רהט להבים    20950   \n",
       "3  2017-12-21       קרית גת  31.603526  34.777955     802   קריית גת    20950   \n",
       "4  2017-12-21          רמלה  31.928809  34.877304     210     גוש דן    20950   \n",
       "\n",
       "   direction_id route_short_name  \\\n",
       "0             1              NaN   \n",
       "1             1              NaN   \n",
       "2             1              NaN   \n",
       "3             1              NaN   \n",
       "4             1              NaN   \n",
       "\n",
       "                                   route_long_name  \n",
       "0  באר שבע מרכז-באר שבע<->תל אביב מרכז-תל אביב יפו  \n",
       "1  באר שבע מרכז-באר שבע<->תל אביב מרכז-תל אביב יפו  \n",
       "2  באר שבע מרכז-באר שבע<->תל אביב מרכז-תל אביב יפו  \n",
       "3  באר שבע מרכז-באר שבע<->תל אביב מרכז-תל אביב יפו  \n",
       "4  באר שבע מרכז-באר שבע<->תל אביב מרכז-תל אביב יפו  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f = (feed.stop_times[['trip_id', 'departure_time', 'arrival_time', 'stop_id', 'stop_sequence']]\n",
    "     .assign(date = datetime.date(2017, 12, 21))\n",
    "     .merge(s[['stop_id', 'stop_name', 'stop_lat', 'stop_lon', 'zone_id']], on='stop_id')\n",
    "     # Much faster joins and slices with Categorical dtypes\n",
    "     .merge(tariff_df[['zone_id', 'zone_name']], on='zone_id')\n",
    "     .assign(zone_id=lambda x: pd.Categorical(x['zone_id']))\n",
    "     .assign(zone_name=lambda x: pd.Categorical(x['zone_name']))\n",
    "     .merge(t[['trip_id', 'route_id', 'direction_id']], on='trip_id')\n",
    "     .merge(r[['route_id', 'route_short_name', 'route_long_name']], on='route_id')\n",
    "     .assign(route_id=lambda x: pd.Categorical(x['route_id']))\n",
    "     .pipe(to_timedelta)\n",
    "    )\n",
    "f.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleanup Diary\n",
    "The question that ignited this post was about final destinations. This is an important parameter we have to be familiar with - last stop of every trip in `trips.txt`, based on `stop_times.txt` - since our current best polling strategy sends requests for these stops only."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### One (wrong) way\n",
    "I first tried to use a dataframe I constructed when I was trying to find simple loop lines (A --> A). I (naively) thought to myself, lets get the first and last stop for all trips.\n",
    "\n",
    "I did it like so - "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# using a custom groupby aggregation function, this is VERY inefficient,\n",
    "# but far more readable than doing some other magic, so I'm sticking to it for now\n",
    "\n",
    "def apply_and_pickle(f, pkl_path, apply_func):\n",
    "    if not os.path.exists(pkl_path):\n",
    "        ret_df = f.groupby('trip_id').apply(apply_func)\n",
    "        # since it's so inefficient I pickle it\n",
    "        with open(pkl_path, 'wb') as pkl_file:\n",
    "            pickle.dump(orig_dest_only, pkl_file)\n",
    "    else:\n",
    "        with open(pkl_path, 'rb') as pkl_file:\n",
    "            ret_df = pickle.load(pkl_file)\n",
    "        return ret_df\n",
    "\n",
    "def first_last(df):\n",
    "    return df.sort_values(by='stop_sequence').iloc[[0, -1]]\n",
    "\n",
    "orig_dest_only = apply_and_pickle(f, 'softloop.pkl', first_last)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x251ec1e0a90>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAmAAAAGfCAYAAAD4R26BAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMS4xLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvAOZPmwAAIABJREFUeJzs3X+QXfV93//nqzjgH8QIG7OVJbUiU9nfYtRQa4eQZuJhTWp+xI1wJrRiqKGYVHaK3aSJS6Cdb0jtUjuNKTXEcUYJxFArrKkJlQbk2EQJ8dczBtuKFSSMCQIUay0ixRaWo6CSyn5//7hH9V2dlVbeu/fC3ft8zNzZcz7nc87n3NfIw9vnnHs+qSokSZI0OH/nhT4BSZKkUWMBJkmSNGAWYJIkSQNmASZJkjRgFmCSJEkDZgEmSZI0YBZgkiRJA2YBJkmSNGCzFmBJbk+yN8n2rrb3J3kkydYkn0ny2qb9vCT7m/atSX6la58LkzyeZEeS6/rzdSRJkl78Mtub8JO8CTgA3FlVZzVtr6yqbzfL/xY4s6releQ84L1V9dYjjnEC8OfAPwWmgC8Cl1XVV2Y7wdNOO62WL1/+/X6v78vf/M3f8IpXvKKvYwwT82gzkzYzaTOTNjOZzjzaFlomW7Zs+UZVvWa2fi+ZrUNVfTbJ8iPavt21+gpgtvmMzgF2VNVTAEkmgdXArAXY8uXL+dKXvjRbt548+OCDnHfeeX0dY5iYR5uZtJlJm5m0mcl05tG20DJJ8hfH02/Oz4AluTHJLuBy4Fe6Nv1okj9L8qkkb2jalgC7uvpMNW2SJEkjZ9ZbkADNFbD7Dt+CPGLb9cBLq+qGJK8EvltVB5JcDHy4qlYkuRS4oKp+ttnn7cA5VfWeo4y3FlgLMDY2tmpycnJu3+44HThwgJNPPrmvYwwT82gzkzYzaTOTNjOZzjzaFlomExMTW6pqfLZ+s96CPA6/B9wP3NB9a7KqNiX5zSSn0bnitaxrn6XA7qMdsKrWAesAxsfHq9+XJhfa5c9emUebmbSZSZuZtJnJdObRNqqZzOkWZJIVXas/BXy1af+7SdIsn9Mc/5t0HrpfkeSMJCcCa4CNvZy4JEnSsJr1CliSu4DzgNOSTAE3ABcneT3wXeAvgHc13X8G+Lkkh4CDwJrq3OM8lOTdwKeBE4Dbq+rR+f4ykiRJw+B4fgV52QzNtx2l728Av3GUbZuATd/X2UmSJC1AvglfkiRpwCzAJEmSBswCTJIkacAswCRJkgbMAkySJGnAjqsAS3J7kr1Jtne1vT/JI0m2JvlMktc27UlyS5IdzfY3du1zZZInms+V8/91JEmSXvyO9wrYx4ALj2j79ar6R1V1NnAf35sP8iJgRfNZC3wUIMmr6LxD7EfoTM59Q5JTezp7SZKkIXRcBVhVfRbYd0Tbt7tWXwEcnlRyNXBndTwELEqyGLgAeKCq9lXVs8ADtIs6SZKkBa+nuSCT3AhcAewHJprmJcCurm5TTdvR2iVJkkZKOjMFHUfHZDlwX1WdNcO264GXVtUNSe4HPlBVn2u2bQauBd4MnFRV/7lp/3+B56rqphmOt5bO7UvGxsZWTU5OzuGrHb+FNhN7r8yjzUzazKTNTNrMZDrzaFtomUxMTGypqvHZ+vV0BazL7wH303nGawpY1rVtKbC7aT/viPYHZzpYVa0D1gGMj49Xv2dJv3X9Bm763N8AsPODP9nXsYbBqM5Mfyxm0mYmbWbSZibTmUfbqGYy59dQJFnRtfpTwFeb5Y3AFc2vIc8F9lfVM3Qm4n5LklObh+/f0rRJkiSNlOO6ApbkLjpXr05LMkXnStfFSV4PfBf4C+BdTfdNwMXADuA54CqAqtqX5P3AF5t+76uqaQ/2S5IkjYLjKsCq6rIZmm87St8CrjnKttuB24/77CRJkhYg34QvSZI0YBZgkiRJA2YBJkmSNGAWYJIkSQNmASZJkjRgsxZgSW5PsjfJ9q62X0/y1SSPJLk3yaKmfXmSg0m2Np/f6tpnVZJtSXYkuSVJ+vOVJEmSXtyO5wrYx2hPmv0AcFZV/SPgz4Hru7Y9WVVnN593dbV/lM70QiuajxNxS5KkkTRrAVZVnwX2HdH2mao61Kw+RGdaoaNKshh4ZVV9vnlP2J3AJXM7ZUmSpOE2H8+AvQP4VNf6GUm+nORPkvx407aEzlyQh001bZIkSSMnnQtSs3RKlgP3VdVZR7T/R2Ac+OmqqiQnASdX1TeTrAL+F/AG4PXAB6rqJ5r9fhy4tqr+2VHGW0vndiVjY2OrJicn5/j1js/effvZc7CzvHLJKX0daxgstJnp54OZtJlJm5m0mcl05tG20DKZmJjYUlXjs/U7rqmIZpLkSuCtwPnNbUWq6nng+WZ5S5IngdfRueLVfZtyKbD7aMeuqnXAOoDx8fHq9yzpt67fwE3bOlHsvLy/Yw2DUZ2Z/ljMpM1M2sykzUymM4+2Uc1kTrcgk1wI/DLwU1X1XFf7a5Kc0Cz/EJ2H7Z+qqmeAv05ybvPrxyuADT2fvSRJ0hCa9QpYkruA84DTkkwBN9D51eNJwAPN2yQean7x+CbgfUkOAd8B3lVVhx/g/zk6v6h8GZ1nxrqfG5MkSRoZsxZgVXXZDM23HaXvPcA9R9n2JeCsmbZJkiSNEt+EL0mSNGAWYJIkSQNmASZJkjRgFmCSJEkDZgEmSZI0YLMWYEluT7I3yfautl9P8tUkjyS5N8mirm3XJ9mR5PEkF3S1X9i07Uhy3fx/FUmSpOFwPFfAPgZceETbA8BZVfWPgD+n814wkpwJrKEz/dCFwG8mOaF5OetHgIuAM4HLmr6SJEkjZ9YCrKo+C+w7ou0zVXWoWX2I700ztBqYrKrnq+ppYAdwTvPZUVVPVdXfApNNX0mSpJEzH8+AvYPvvdV+CbCra9tU03a0dkmSpJEz58m4AZL8R+AQsP5w0wzdipkLvTrGcdcCawHGxsZ48MEHeznNWY29DH5pZeeCXr/HGgYHDhwwhyOYSZuZtJlJm5lMZx5to5rJnAuwJFcCbwXOr6rDxdQUsKyr21Jgd7N8tPaWqloHrAMYHx+vfs+Sfuv6Ddy0rRPFzsv7O9YwGNWZ6Y/FTNrMpM1M2sxkOvNoG9VM5nQLMsmFwC8DP1VVz3Vt2gisSXJSkjOAFcAXgC8CK5KckeREOg/qb+zt1CVJkobTrFfAktwFnAeclmQKuIHOrx5PAh5IAvBQVb2rqh5NcjfwFTq3Jq+pqu80x3k38GngBOD2qnq0D99HkiTpRW/WAqyqLpuh+bZj9L8RuHGG9k3Apu/r7CRJkhYg34QvSZI0YBZgkiRJA2YBJkmSNGAWYJIkSQNmASZJkjRgsxZgSW5PsjfJ9q62S5M8muS7Sca72pcnOZhka/P5ra5tq5JsS7IjyS1p3l8hSZI0ao7nCtjHgAuPaNsO/DTw2Rn6P1lVZzefd3W1f5TO9EIrms+Rx5QkSRoJsxZgVfVZYN8RbY9V1ePHO0iSxcArq+rzzbRFdwKXfL8nK0mStBD04xmwM5J8OcmfJPnxpm0JnXkiD5tq2iRJkkZOvjeP9jE6JcuB+6rqrCPaHwTeW1VfatZPAk6uqm8mWQX8L+ANwOuBD1TVTzT9fhy4tqr+2VHGW0vndiVjY2OrJicn5/TljtfeffvZc7CzvHLJKX0daxgcOHCAk08++YU+jRcVM2kzkzYzaTOT6cyjbaFlMjExsaWqxmfrN+tURN+PqnoeeL5Z3pLkSeB1dK54Le3quhTYfYzjrAPWAYyPj1e/Z0m/df0GbtrWiWLn5f0daxiM6sz0x2ImbWbSZiZtZjKdebSNaibzegsyyWuSnNAs/xCdh+2fqqpngL9Ocm7z68crgA3zObYkSdKwOJ7XUNwFfB54fZKpJFcneVuSKeBHgfuTfLrp/ibgkSR/BnwSeFdVHX6A/+eA3wF2AE8Cn5rn7yJJkjQUZr0FWVWXHWXTvTP0vQe45yjH+RJw1kzbJEmSRolvwpckSRowCzBJkqQBswCTJEkaMAswSZKkAbMAkyRJGrDjeQ3F7Un2Jtne1XZpkkeTfDfJ+BH9r0+yI8njSS7oar+waduR5Lr5/RqSJEnD43iugH0MuPCItu3ATwOf7W5Mciawhs70QxcCv5nkhOblrB8BLgLOBC5r+kqSJI2c43kP2GebuSC72x4D6LzUfprVwGQzJdHTSXYA5zTbdlTVU81+k03fr/Ry8pIkScNovp8BWwLs6lqfatqO1i5JkjRy5nUybqB1SQwoZi706qgHSdYCawHGxsZ48MEH5+XkjmbsZfBLKw8B9H2sYXDgwAFzOIKZtJlJm5m0mcl05tE2qpnMdwE2BSzrWl8K7G6Wj9beUlXrgHUA4+Pj1e9Z0m9dv4GbtnWi2Hl5f8caBqM6M/2xmEmbmbSZSZuZTGcebaOayXzfgtwIrElyUpIzgBXAF4AvAiuSnJHkRDoP6m+c57ElSZKGwqxXwJLcBZwHnJZkCrgB2AfcCrwGuD/J1qq6oKoeTXI3nYfrDwHXVNV3muO8G/g0cAJwe1U92o8vJEmS9GJ3PL+CvOwom+49Sv8bgRtnaN8EbPq+zk6SJGkB8k34kiRJA2YBJkmSNGAWYJIkSQNmASZJkjRgFmCSJEkDNmsBluT2JHuTbO9qe1WSB5I80fw9tWk/L8n+JFubz6907XNhkseT7EhyXX++jiRJ0ovf8VwB+xhw4RFt1wGbq2oFsLlZP+z/q6qzm8/7AJKcAHwEuAg4E7gsyZm9nrwkSdIwmrUAq6rP0nnxarfVwB3N8h3AJbMc5hxgR1U9VVV/C0w2x5AkSRo5c30GbKyqngFo/p7ete1Hk/xZkk8leUPTtgTY1dVnqmmTJEkaOamq2Tsly4H7quqsZv1bVbWoa/uzVXVqklcC362qA0kuBj5cVSuSXApcUFU/2/R/O3BOVb3nKOOtBdYCjI2NrZqcnOzpS85m77797DnYWV655JS+jjUMDhw4wMknn/xCn8aLipm0mUmbmbSZyXTm0bbQMpmYmNhSVeOz9Zt1KqKj2JNkcVU9k2QxsBegqr59uENVbUrym0lOo3PFa1nX/kuB3Uc7eFWtA9YBjI+PV79nSb91/QZu2taJYufl/R1rGIzqzPTHYiZtZtJmJm1mMp15tI1qJnO9BbkRuLJZvhLYAJDk7yZJs3xOc/xvAl8EViQ5I8mJwJrmGJIkSSNn1itgSe4CzgNOSzIF3AB8ELg7ydXA14BLm+4/A/xckkPAQWBNde5xHkrybuDTwAnA7VX16Hx/GUmSpGEwawFWVZcdZdP5M/T9DeA3jnKcTcCm7+vsJEmSFiDfhC9JkjRgFmCSJEkDZgEmSZI0YBZgkiRJA2YBJkmSNGDHVYAluT3J3iTbu9peleSBJE80f09t2pPkliQ7kjyS5I1d+1zZ9H8iyZUzjSVJkrTQHe8VsI8BFx7Rdh2wuapWAJubdYCLgBXNZy3wUegUbHTeIfYjdCbnvuFw0SZJkjRKjqsAq6rPAvuOaF4N3NEs3wFc0tV+Z3U8BCxqpiu6AHigqvZV1bPAA7SLOkmSpAWvl2fAxqrqGYDm7+lN+xJgV1e/qabtaO2SJEkjZa6TcR9LZmirY7S3D5CspXP7krGxMR588MF5O7mZjL0MfmnlIYC+jzUMDhw4YA5HMJM2M2kzkzYzmc482kY1k14KsD1JFlfVM80txr1N+xSwrKvfUmB3037eEe0PznTgqloHrAMYHx+vfs+Sfuv6Ddy0rRPFzsv7O9YwGNWZ6Y/FTNrMpM1M2sxkOvNoG9VMerkFuRE4/EvGK4ENXe1XNL+GPBfY39yi/DTwliSnNg/fv6VpkyRJGinHdQUsyV10rl6dlmSKzq8ZPwjcneRq4GvApU33TcDFwA7gOeAqgKral+T9wBebfu+rqiMf7JckSVrwjqsAq6rLjrLp/Bn6FnDNUY5zO3D7cZ+dJEnSAuSb8CVJkgbMAkySJGnALMAkSZIGzAJMkiRpwCzAJEmSBqynAizJzyfZnuTRJL/QtP1qkq8n2dp8Lu7qf32SHUkeT3JBrycvSZI0jOb8JvwkZwH/GjgH+FvgD5Lc32y+uao+dET/M4E1wBuA1wJ/mOR1VfWduZ6DJEnSMOrlCtg/BB6qqueq6hDwJ8DbjtF/NTBZVc9X1dN0XtR6Tg/jS5IkDaVeCrDtwJuSvDrJy+m8/f7wHJDvTvJIktubaYcAlgC7uvafatokSZJGSjovrp/jzp1piK4BDgBfAQ7SmaLoG0AB7wcWV9U7knwE+HxVfbzZ9zZgU1XdM8Nx1wJrAcbGxlZNTk7O+RyPx959+9lzsLO8cskpfR1rGBw4cICTTz75hT6NFxUzaTOTNjNpM5PpzKNtoWUyMTGxparGZ+s352fAAKrqNuA2gCT/BZiqqj2Htyf5beC+ZnWK710hA1gK7D7KcdcB6wDGx8er37Ok37p+Azdt60Sx8/L+jjUMRnVm+mMxkzYzaTOTNjOZzjzaRjWTXn8FeXrz9+8BPw3clWRxV5e30blVCbARWJPkpCRnACuAL/QyviRJ0jDq6QoYcE+SVwP/B7imqp5N8j+SnE3nFuRO4J0AVfVokrvp3Ko81PT3F5CSJGnk9HoL8sdnaHv7MfrfCNzYy5iSJEnDzjfhS5IkDZgFmCRJ0oBZgEmSJA2YBZgkSdKAWYBJkiQNWK/vAfv5JNuTPJrkF5q2VyV5IMkTzd9Tm/YkuSXJjmaaojfOxxeQJEkaNnMuwJKcBfxrOhNq/zDw1iQrgOuAzVW1AtjcrANcROflqyvoTDP00R7OW5IkaWj1cgXsHwIPVdVzVXUI+BM6b75fDdzR9LkDuKRZXg3cWR0PAYuOeGu+JEnSSOilANsOvCnJq5O8HLiYzlyPY1X1DEDz9/Sm/xJgV9f+U02bJEnSSElVzX3n5GrgGuAAnSmGDgJXVdWirj7PVtWpSe4HPlBVn2vaNwPXVtWWGY67ls5tSsbGxlZNTk7O+RyPx959+9lzsLO8cskpfR1rGCy0menng5m0mUmbmbSZyXTm0bbQMpmYmNhSVeOz9et1KqLbgNsAkvwXOle19iRZXFXPNLcY9zbdp+hcITtsKbD7KMddB6wDGB8fr37Pkn7r+g3ctK0Txc7L+zvWMBjVmemPxUzazKTNTNrMZDrzaBvVTHr9FeTpzd+/B/w0cBewEbiy6XIlsKFZ3ghc0fwa8lxg/+FblZIkSaOkpytgwD1JXg38H+Caqno2yQeBu5vbk18DLm36bqLznNgO4Dngqh7HliRJGkq93oL88RnavgmcP0N70XleTJIkaaT5JnxJkqQBswCTJEkaMAswSZKkAbMAkyRJGjALMEmSpAHr9T1g/y7Jo0m2J7kryUuTfCzJ00m2Np+zm75JckuSHUkeSfLG+fkKkiRJw2XOr6FIsgT4t8CZVXUwyd3Ammbzv6+qTx6xy0XAiubzI8BHm7+SJEkjpddbkC8BXpbkJcDLOcrUQo3VwJ3V8RCwqJmqSJIkaaTMuQCrqq8DH6Lztvtn6Ewt9Jlm843Nbcabk5zUtC0BdnUdYqppkyRJGinpvKB+DjsmpwL3AP8C+BbwP4FPApuBvwROpDOh9pNV9b4k9wMfqKrPNftvBq6tqi0zHHstsBZgbGxs1eTk5JzO8Xjt3befPQc7yyuXnNLXsYbBQpuZfj6YSZuZtJlJm5lMZx5tCy2TiYmJLVU1Plu/XqYi+gng6ar6K4Akvw/8k6r6eLP9+SS/C7y3WZ8ClnXtv5Sj3LKsqnV0ijfGx8er37Ok37p+Azdt60Sx8/L+jjUMRnVm+mMxkzYzaTOTNjOZzjzaRjWTXp4B+xpwbpKXJwmd+R8fO/xcV9N2CbC96b8RuKL5NeS5dG5ZPtPD+JIkSUNpzlfAqurhJJ8E/hQ4BHyZzlWrTyV5DRBgK/CuZpdNwMXADuA54KoezluSJGlo9XILkqq6AbjhiOY3H6VvAdf0Mp4kSdJC4JvwJUmSBswCTJIkacAswCRJkgbMAkySJGnALMAkSZIGrKcCLMm/S/Joku1J7kry0iRnJHk4yRNJPpHkxKbvSc36jmb78vn4ApIkScNmzgVYkiXAvwXGq+os4ARgDfBrwM1VtQJ4Fri62eVq4Nmq+gfAzU0/SZKkkdPrLciXAC9L8hLg5XQm5X4znTkhAe6g8zZ8gNXNOs3285u35UuSJI2UORdgVfV14EN0piR6BtgPbAG+VVWHmm5TwJJmeQmwq9n3UNP/1XMdX5IkaVil84L6OeyYnArcA/wL4FvA/2zWb2huM5JkGbCpqlYmeRS4oKqmmm1PAudU1TdnOPZaYC3A2NjYqsnJyTmd4/Hau28/ew52llcuOaWvYw2DhTYz/XwwkzYzaTOTNjOZzjzaFlomExMTW6pqfLZ+vUxF9BPA01X1VwBJfh/4J8CiJC9prnItBXY3/aeAZcBUc8vyFGDfTAeuqnV05pVkfHy8+j1L+q3rN3DTtk4UOy/v71jDYFRnpj8WM2kzkzYzaTOT6cyjbVQz6eUZsK8B5yZ5efMs1/nAV4A/Bn6m6XMlsKFZ3tis02z/o5rr5TdJkqQh1sszYA/TeZj+T4FtzbHWAb8M/GKSHXSe8bqt2eU24NVN+y8C1/Vw3pIkSUOrl1uQVNUNwA1HND8FnDND3/8NXNrLeJIkSQuBb8KXJEkaMAswSZKkAbMAkyRJGjALMEmSpAGzAJMkSRqwXibjfn2SrV2fbyf5hSS/muTrXe0Xd+1zfZIdSR5PcsH8fAVJkqThMufXUFTV48DZAElOAL4O3AtcBdxcVR/q7p/kTGAN8AbgtcAfJnldVX1nrucgSZI0jObrFuT5wJNV9RfH6LMamKyq56vqaWAHM7wvTJIkaaGbrwJsDXBX1/q7kzyS5PZm0m6AJcCurj5TTZskSdJISa/TMSY5kc6E22+oqj1JxoBvAAW8H1hcVe9I8hHg81X18Wa/24BNVXXPDMdcC6wFGBsbWzU5OdnTOc5m77797DnYWV655JS+jjUMFtrM9PPBTNrMpM1M2sxkOvNoW2iZTExMbKmq8dn69TQVUeMi4E+rag/A4b8ASX4buK9ZnQKWde23lE7h1lJV6+jMK8n4+Hj1e5b0W9dv4KZtnSh2Xt7fsYbBqM5Mfyxm0mYmbWbSZibTmUfbqGYyH7cgL6Pr9mOSxV3b3gZsb5Y3AmuSnJTkDGAF8IV5GF+SJGmo9HQFLMnLgX8KvLOr+b8mOZvOLcidh7dV1aNJ7ga+AhwCrvEXkJIkaRT1VIBV1XPAq49oe/sx+t8I3NjLmJIkScPON+FLkiQNmAWYJEnSgFmASZIkDZgFmCRJ0oBZgEmSJA1YTwVYktcn2dr1+XaSX0jyqiQPJHmi+Xtq0z9Jbkmyo5mq6I3z8zUkSZKGR08FWFU9XlVnV9XZwCrgOeBe4Dpgc1WtADY369B5a/6K5rMW+Ggv40uSJA2j+bwFeT7wZFX9BbAauKNpvwO4pFleDdxZHQ8Bi454c74kSdKCN58F2Bq+NyXRWFU9A9D8Pb1pXwLs6tpnqmmTJEkaGamq3g+SnEhnYu03VNWeJN+qqkVd25+tqlOT3A98oKo+17RvBq6tqi1HHG8tnVuUjI2NrZqcnOz5HI9l77797DnYWV655JS+jjUMFtrM9PPBTNrMpM1M2sxkOvNoW2iZTExMbKmq8dn69TQVUZeLgD+tqj3N+p4ki6vqmeYW496mfQpY1rXfUjqF2zRVtQ5YBzA+Pl79niX91vUbuGlbJ4qdl/d3rGEwqjPTH4uZtJlJm5m0mcl05tE2qpnM1y3Iy/je7UeAjcCVzfKVwIau9iuaX0OeC+w/fKtSkiRpVPR8BSzJy4F/Cryzq/mDwN1Jrga+BlzatG8CLgZ20PnF5FW9ji9JkjRsei7Aquo54NVHtH2Tzq8ij+xbwDW9jilJkjTMfBO+JEnSgFmASZIkDZgFmCRJ0oBZgEmSJA2YBZgkSdKA9VSAJVmU5JNJvprksSQ/muRXk3w9ydbmc3FX/+uT7EjyeJILej99SZKk4dPrayg+DPxBVf1MMx3Ry4ELgJur6kPdHZOcSWe+yDcArwX+MMnrquo7PZ6DJEnSUJnzFbAkrwTeBNwGUFV/W1XfOsYuq4HJqnq+qp6m8zLWc+Y6viRJ0rDq5RbkDwF/Bfxuki8n+Z0kr2i2vTvJI0luT3Jq07YE2NW1/1TTJkmSNFLSeTn9HHZMxoGHgB+rqoeTfBj4NvAbwDeAAt4PLK6qdyT5CPD5qvp4s/9twKaqumeGY68F1gKMjY2tmpycnNM5Hq+9+/az52BneeWSU/o61jBYaDPTzwczaTOTNjNpM5PpzKNtoWUyMTGxparGZ+vXyzNgU8BUVT3crH8SuK6q9hzukOS3gfu6+i/r2n8psHumA1fVOmAdwPj4ePV7lvRb12/gpm2dKHZe3t+xhsGozkx/LGbSZiZtZtJmJtOZR9uoZjLnW5BV9ZfAriSvb5rOB76SZHFXt7cB25vljcCaJCclOQNYAXxhruNLkiQNq15/BfkeYH3zC8ingKuAW5KcTecW5E7gnQBV9WiSu4GvAIeAa/wFpCRJGkU9FWBVtRU48j7n24/R/0bgxl7GlCRJGna+CV+SJGnALMAkSZIGzAJMkiRpwCzAJEmSBswCTJIkacB6KsCSLEryySRfTfJYkh9N8qokDyR5ovl7atM3SW5JsqOZpuiN8/MVJEmShkuvV8A+DPxBVf0/wA8DjwHXAZuragWwuVkHuIjOy1dX0Jlm6KM9ji1JkjSU5lyAJXkl8CbgNoCq+tuq+hawGrij6XYHcEmzvBq4szoeAhYd8dZ8SZKkkdDLFbAfAv4K+N0kX07yO0leAYxV1TMAzd/Tm/5LgF1d+081bZIkSSMlVTW3HZNx4CHgx6rq4SQfBr4NvKeqFnX1e7aqTk1yP/CBqvpc074ZuLaqtsxw7LV0blMyNja2anJyck7neLz27tvPnoOd5ZVLTunrWMNgoc1MPx/MpM1M2sykzUymM4+2hZbJxMTElqo6cpagll6mIpoCpqrq4Wb9k3Se99qTZHFVPdPcYtzb1X9Z1/5Lgd0zHbiq1gH/CY4KAAAcQElEQVTrAMbHx6vfs6Tfun4DN23rRLHz8v6ONQxGdWb6YzGTNjNpM5M2M5nOPNpGNZM534Ksqr8EdiV5fdN0Pp2JtjcCVzZtVwIbmuWNwBXNryHPBfYfvlUpSZI0SnqajBt4D7A+yYnAU8BVdIq6u5NcDXwNuLTpuwm4GNgBPNf0lSRJGjk9FWBVtRWY6T7n+TP0LeCaXsaTJElaCHwTviRJ0oBZgEmSJA2YBZgkSdKAWYBJkiQNmAWYJEnSgPVUgCXZmWRbkq1JvtS0/WqSrzdtW5Nc3NX/+iQ7kjye5IJeT16SJGkY9foeMICJqvrGEW03V9WHuhuSnAmsAd4AvBb4wySvq6rvzMM5SJIkDY1B3oJcDUxW1fNV9TSdF7KeM8DxJUmSXhR6LcAK+EySLc0E2oe9O8kjSW5PcmrTtgTY1dVnqmmTJEkaKem8oH6OOyevrardSU4HHqAzNdHjwDfoFGfvBxZX1TuSfAT4fFV9vNn3NmBTVd0zw3HXAmsBxsbGVk1OTs75HI/H3n372XOws7xyySl9HWsYLLSZ6eeDmbSZSZuZtJnJdObRttAymZiY2FJVM80SNE2vUxHtbv7uTXIvcE5Vffbw9iS/DdzXrE4By7p2XwrsPspx1wHrAMbHx6vfs6Tfun4DN23rRLHz8v6ONQxGdWb6YzGTNjNpM5M2M5nOPNpGNZM534JM8ookP3h4GXgLsD3J4q5ubwO2N8sbgTVJTkpyBrAC+MJcx5ckSRpWvVwBGwPuTXL4OL9XVX+Q5H8kOZvOLcidwDsBqurRJHcDXwEOAdf4C0hJkjSK5lyAVdVTwA/P0P72Y+xzI3DjXMeUJElaCHwTviRJ0oBZgEmSJA2YBZgkSdKAWYBJkiQNmAWYJEnSgPVcgCXZmWRbkq1JvtS0vSrJA0meaP6e2rQnyS1JdjRTFb2x1/ElSZKGzXxdAZuoqrO7Xr1/HbC5qlYAm5t1gIvovIB1BZ2phj46T+NLkiQNjX7dglwN3NEs3wFc0tV+Z3U8BCw64s35kiRJC958FGAFfCbJlmYSbYCxqnoGoPl7etO+BNjVte9U0yZJkjQyUlW9HSB5bVXtTnI68ADwHmBjVS3q6vNsVZ2a5H7gA1X1uaZ9M3BtVW054phr6dyiZGxsbNXk5GRP5zibvfv2s+dgZ3nlklP6OtYwWGgz088HM2kzkzYzaTOT6cyjbaFlMjExsaXrkayj6mUuSACqanfzd2+Se4FzgD1JFlfVM80txr1N9ylgWdfuS4HdMxxzHbAOYHx8vPo9S/qt6zdw07ZOFDsv7+9Yw2BUZ6Y/FjNpM5M2M2kzk+nMo21UM+npFmSSVyT5wcPLwFuA7cBG4Mqm25XAhmZ5I3BF82vIc4H9h29VSpIkjYper4CNAfcmOXys36uqP0jyReDuJFcDXwMubfpvAi4GdgDPAVf1OL4kSdLQ6akAq6qngB+eof2bwPkztBdwTS9jSpIkDTvfhC9JkjRgFmCSJEkDZgEmSZI0YBZgkiRJA2YBJkmSNGA9F2BJTkjy5ST3NesfS/J0kq3N5+ymPUluSbIjySNJ3tjr2JIkScOo5zfhAz8PPAa8sqvt31fVJ4/odxGwovn8CPDR5q8kSdJI6fVN+EuBnwR+5zi6rwburI6HgEXNNEWSJEkjpddbkP8duBb47hHtNza3GW9OclLTtgTY1dVnqmmTJEkaKem8nH4OOyZvBS6uqn+T5DzgvVX11uaq1l8CJ9KZUPvJqnpfkvuBD1TV55r9NwPXVtWWGY69FlgLMDY2tmpycnJO53i89u7bz56DneWVS07p61jDYKHNTD8fzKTNTNrMpM1MpjOPtoWWycTExJaqGp+tXy/PgP0Y8FNJLgZeCrwyycer6l82259P8rvAe5v1KWBZ1/5Lgd0zHbiq1tEp3hgfH69+z5J+6/oN3LStE8XOy/s71jAY1Znpj8VM2sykzUzazGQ682gb1UzmfAuyqq6vqqVVtRxYA/xRVf3Lw891pTND9yXA9maXjcAVza8hzwX2V9UzvZ2+JEnS8JmPX0EeaX2S1wABtgLvato3ARcDO4DngKv6MLYkSdKL3rwUYFX1IPBgs/zmo/Qp4Jr5GE+SJGmY+SZ8SZKkAbMAkyRJGjALMEmSpAGzAJMkSRowCzBJkqQB67kAS3JCki8nua9ZPyPJw0meSPKJJCc27Sc16zua7ct7HVuSJGkYzccVsJ8HHuta/zXg5qpaATwLXN20Xw08W1X/ALi56SdJkjRyeirAkiwFfhL4nWY9wJuBTzZd7qDzNnyA1c06zfbzm/6SJEkjpdcrYP8duBb4brP+auBbVXWoWZ8CljTLS4BdAM32/U1/SZKkkZLOC+rnsGPyVuDiqvo3Sc6jM+n2VcDnm9uMJFkGbKqqlUkeBS6oqqlm25PAOVX1zRmOvRZYCzA2NrZqcnJyTud4vPbu28+eg53llUtO6etYw2ChzUw/H8ykzUzazKTNTKYzj7aFlsnExMSWqhqfrV8vUxH9GPBTSS4GXgq8ks4VsUVJXtJc5VoK7G76TwHLgKkkLwFOAfbNdOCqWgesAxgfH69+z5J+6/oN3LStE8XOy/s71jAY1Znpj8VM2sykzUzazGQ682gb1UzmfAuyqq6vqqVVtRxYA/xRVV0O/DHwM023K4ENzfLGZp1m+x/VXC+/SZIkDbF+vAfsl4FfTLKDzjNetzXttwGvbtp/EbiuD2NLkiS96PVyC/L/qqoHgQeb5aeAc2bo87+BS+djPEmSpGHmm/AlSZIGzAJMkiRpwCzAJEmSBswCTJIkacAswCRJkgZszgVYkpcm+UKSP0vyaJL/1LR/LMnTSbY2n7Ob9iS5JcmOJI8keeN8fQlJkqRh0strKJ4H3lxVB5L8APC5JJ9qtv37qvrkEf0vAlY0nx8BPtr8lSRJGim9vAm/qupAs/oDzedYb7ZfDdzZ7PcQnSmLFs91fEmSpGHV0zNgSU5IshXYCzxQVQ83m25sbjPenOSkpm0JsKtr96mmTZIkaaRkPqZjTLIIuBd4D/BN4C+BE+lMqP1kVb0vyf3AB6rqc80+m4Frq2rLDMdbC6wFGBsbWzU5OdnzOR7L3n372XOws7xyySl9HWsYLLSZ6eeDmbSZSZuZtJnJdObRttAymZiY2FJV47P1m6+piL6V5EHgwqr6UNP8fJLfBd7brE8By7p2WwrsPsrx1tEp3hgfH69+z5J+6/oN3LStE8XOy/s71jAY1Znpj8VM2sykzUzazGQ682gb1Ux6+RXka5orXyR5GfATwFcPP9eVJMAlwPZml43AFc2vIc8F9lfVMz2dvSRJ0hDq5QrYYuCOJCfQKeTurqr7kvxRktcAAbYC72r6bwIuBnYAzwFX9TC2JEnS0JpzAVZVjwD/eIb2Nx+lfwHXzHU8SZKkhcI34UuSJA2YBZgkSdKAWYBJkiQNmAWYJEnSgFmASZIkDVivUxG9NMkXkvxZkkeT/Kem/YwkDyd5IsknkpzYtJ/UrO9oti/v/StIkiQNl16vgD0PvLmqfhg4G7iwecnqrwE3V9UK4Fng6qb/1cCzVfUPgJubfpIkSSOlpwKsOg40qz/QfAp4M/DJpv0OOm/EB1jdrNNsP795Y74kSdLI6PkZsCQnJNkK7AUeAJ4EvlVVh5ouU8CSZnkJsAug2b4feHWv5yBJkjRM0nlB/TwcqDMv5L3ArwC/29xmJMkyYFNVrUzyKHBBVU01254Ezqmqbx5xrLXAWoCxsbFVk5OT83KOR7N33372HOwsr1xySl/HGgYLbWb6+WAmbWbSZiZtZjKdebQttEwmJia2VNX4bP16mQtymqr6VpIHgXOBRUle0lzlWgrsbrpNAcuAqSQvAU4B9s1wrHXAOoDx8fHq9yzpt67fwE3bOlHsvLy/Yw2DUZ2Z/ljMpM1M2sykzUymM4+2Uc2k119Bvqa58kWSlwE/ATwG/DHwM023K4ENzfLGZp1m+x/VfF2CkyRJGhK9XgFbDNyR5AQ6xdzdVXVfkq8Ak0n+M/Bl4Lam/23A/0iyg86VrzU9ji9JkjR0eirAquoR4B/P0P4UcM4M7f8buLSXMSVJkoadb8KXJEkaMAswSZKkAbMAkyRJGjALMEmSpAGzAJMkSRqwORdgSZYl+eMkjyV5NMnPN+2/muTrSbY2n4u79rk+yY4kjye5YD6+gCRJ0rDp5TUUh4Bfqqo/TfKDwJYkDzTbbq6qD3V3TnImnfd+vQF4LfCHSV5XVd/p4RwkSZKGzpyvgFXVM1X1p83yX9N5A/6SY+yyGpisquer6mlgBzO8K0ySJGmhm5dnwJIsp/NC1oebpncneSTJ7UlObdqWALu6dpvi2AWbJEnSgpRep2JMcjLwJ8CNVfX7ScaAbwAFvB9YXFXvSPIR4PNV9fFmv9uATVV1zwzHXAusBRgbG1s1OTnZ0znOZu++/ew52FleueSUvo41DBbazPTzwUzazKTNTNrMZDrzaFtomUxMTGypqvHZ+vU0FVGSHwDuAdZX1e8DVNWeru2/DdzXrE4By7p2Xwrsnum4VbUOWAcwPj5e/Z4l/db1G7hpWyeKnZf3d6xhMKoz0x+LmbSZSZuZtJnJdObRNqqZ9PIryNCZXPuxqvpvXe2Lu7q9DdjeLG8E1iQ5KckZwArgC3MdX5IkaVj1cgXsx4C3A9uSbG3a/gNwWZKz6dyC3Am8E6CqHk1yN/AVOr+gvMZfQEqSpFE05wKsqj4HZIZNm46xz43AjXMdU5IkaSHwTfiSJEkDZgEmSZI0YBZgkiRJA2YBJkmSNGAWYJIkSQM2519BJlkG3An8XeC7wLqq+nCSVwGfAJbTeQ3FP6+qZ5v3hn0YuBh4DvhXh+eSfDFZft3909Z3fvAnX6AzkSRJC1UvV8AOAb9UVf8QOBe4JsmZwHXA5qpaAWxu1gEuovPy1RV0phn6aA9jS5IkDa05F2BV9czhK1hV9dfAY3Qm114N3NF0uwO4pFleDdxZHQ8Bi454a74kSdJImJdnwJIsB/4x8DAwVlXPQKdIA05vui0BdnXtNtW0SZIkjZRUVW8HSE4G/gS4sap+P8m3qmpR1/Znq+rUJPcDH2jeoE+SzcC1VbVlhmOupXObkrGxsVWTk5M9neNs9u7bz56DM29bueSUvo79YrTQZqafD2bSZiZtZtJmJtOZR9tCy2RiYmJLVY3P1q+XuSBJ8gPAPcD6qvr9pnlPksVV9Uxzi3Fv0z4FLOvafSmwe6bjVtU6YB3A+Ph49XuW9FvXb+CmbTNHsfPy/o79YjSqM9Mfi5m0mUmbmbSZyXTm0Taqmcz5FmTzq8bbgMeq6r91bdoIXNksXwls6Gq/Ih3nAvsP36qUJEkaJb1cAfsx4O3AtiRbm7b/AHwQuDvJ1cDXgEubbZvovIJiB53XUFzVw9iSJElDa84FWPMsV46y+fwZ+hdwzVzHkyRJWih8E74kSdKAWYBJkiQNmAWYJEnSgFmASZIkDZgFmCRJ0oD1VIAluT3J3iTbu9p+NcnXk2xtPhd3bbs+yY4kjye5oJexJUmShlWvV8A+Blw4Q/vNVXV289kEkORMYA3whmaf30xyQo/jS5IkDZ2eCrCq+iyw7zi7rwYmq+r5qnqazgtZz+llfEmSpGHUr2fA3p3kkeYW5alN2xJgV1efqaZNkiRppKTzgvoeDpAsB+6rqrOa9THgG0AB7wcWV9U7knwE+HxVfbzpdxuwqarumeGYa4G1AGNjY6smJyd7OsfZ7N23nz0HZ962cskpfR37xWihzUw/H8ykzUzazKTNTKYzj7aFlsnExMSWqhqfrV8vc0HOqKr2HF5O8tvAfc3qFLCsq+tSYPdRjrEOWAcwPj5e/Z4l/db1G7hp28xR7Ly8v2O/GI3qzPTHYiZtZtJmJm1mMp15tI1qJvN+CzLJ4q7VtwGHfyG5EViT5KQkZwArgC/M9/iSJEkvdj1dAUtyF3AecFqSKeAG4LwkZ9O5BbkTeCdAVT2a5G7gK8Ah4Jqq+k4v4w/C8uvu/7/LOz/4ky/gmUiSpIWipwKsqi6bofm2Y/S/EbixlzElSZKGnW/ClyRJGjALMEmSpAGzAJMkSRowCzBJkqQBswCTJEkasJ4KsGaqob1Jtne1vSrJA0meaP6e2rQnyS1JdjTTFL2x15OXJEkaRr1eAfsYcOERbdcBm6tqBbC5WQe4iM7LV1fQmWbooz2OLUmSNJR6KsCq6rPAviOaVwN3NMt3AJd0td9ZHQ8Bi454a74kSdJI6MczYGNV9QxA8/f0pn0JsKur31TTJkmSNFJSVb0dIFkO3FdVZzXr36qqRV3bn62qU5PcD3ygqj7XtG8Grq2qLTMccy2d25SMjY2tmpyc7OkcZ7N33372HJy938olp/T1PF4sFtrM9PPBTNrMpM1M2sxkOvNoW2iZTExMbKmq8dn69TQV0VHsSbK4qp5pbjHubdqngGVd/ZYCu2c6QFWtA9YBjI+PV79nSb91/QZu2nYcUWz7m2mrC3VuyFGdmf5YzKTNTNrMpM1MpjOPtlHNpB+3IDcCVzbLVwIbutqvaH4NeS6w//CtSkmSpFHS0xWwJHcB5wGnJZkCbgA+CNyd5Grga8ClTfdNwMXADuA54KpexpYkSRpWPRVgVXXZUTadP0PfAq7pZbwXm+XX3f9/lxfq7UhJkjT/fBO+JEnSgFmASZIkDZgFmCRJ0oBZgEmSJA2YBZgkSdKA9eNFrAAk2Qn8NfAd4FBVjSd5FfAJYDmwE/jnVfVsv85BkiTpxahvBVhjoqq+0bV+HbC5qj6Y5Lpm/Zf7fA4D0f1KCvC1FJIk6egGfQtyNXBHs3wHcMmAx5ckSXrB9bMAK+AzSbY0k2sDjB2efqj5e3ofx5ckSXpRSucF9X04cPLaqtqd5HTgAeA9wMaqWtTV59mqOnWGfdcCawHGxsZWTU5O9uUcD9u7bz97Dvbv+CuXnNK/g/fBQpuZfj6YSZuZtJlJm5lMZx5tCy2TiYmJLVU1Plu/vj0DVlW7m797k9wLnAPsSbK4qp5JshjYe5R91wHrAMbHx6vfs6Tfun4DN23r3+NwOy8/r2/H7odRnZn+WMykzUzazKTNTKYzj7ZRzaQvVUeSVwB/p6r+ull+C/A+YCNwJZ0Ju68ENvRj/BcbH9CXJEnd+nXZZwy4N8nhMX6vqv4gyReBu5NcDXwNuLRP40uSJL1o9aUAq6qngB+eof2bwPn9GHOYdF8R82qYJEmjxzfhS5IkDZgFmCRJ0oD1+034moW3IyVJGj0WYC8i/lpSkqTRYAH2InZkQdbN4kySpOE18AIsyYXAh4ETgN+pqg8O+hwWgmMVZ8fSXbh5xU2SpBfGQB/CT3IC8BHgIuBM4LIkZw7yHCRJkl5og74Cdg6wo3lPGEkmgdXAVwZ8HiNrLlfOvp9boXP9UcHR9jvWVTqv4EmShtWgC7AlwK6u9SngRwZ8DjqKwwXNL608xHnf5z7z2fdY/Y5327EKwyPNR+E234XnIMbWi5f/50Ja+FJVgxssuRS4oKp+tll/O3BOVb3niH5rgbXN6uuBx/t8aqcB3+jzGMPEPNrMpM1M2sykzUymM4+2hZbJ36+q18zWadBXwKaAZV3rS4HdR3aqqnXAukGdVJIvVdX4oMZ7sTOPNjNpM5M2M2kzk+nMo21UMxn0m/C/CKxIckaSE4E1wMYBn4MkSdILaqBXwKrqUJJ3A5+m8xqK26vq0UGegyRJ0gtt4O8Bq6pNwKZBjzuLgd3uHBLm0WYmbWbSZiZtZjKdebSNZCYDfQhfkiRJg38GTJIkaeSNdAGW5MIkjyfZkeS6F/p8BiXJsiR/nOSxJI8m+fmm/VVJHkjyRPP31KY9SW5pcnokyRtf2G/QH0lOSPLlJPc162ckebjJ4xPND0dIclKzvqPZvvyFPO9+SbIoySeTfLX5t/Kj/hvJv2v+N7M9yV1JXjpq/06S3J5kb5LtXW3f97+LJFc2/Z9IcuUL8V3my1Ey+fXmfzuPJLk3yaKubdc3mTye5IKu9gXz36SZMuna9t4kleS0Zn0k/p20VNVIfuj8COBJ4IeAE4E/A858oc9rQN99MfDGZvkHgT+nMzXUfwWua9qvA36tWb4Y+BQQ4Fzg4Rf6O/Qpl18Efg+4r1m/G1jTLP8W8HPN8r8BfqtZXgN84oU+9z7lcQfws83yicCiUf43QudF0k8DL+v69/GvRu3fCfAm4I3A9q627+vfBfAq4Knm76nN8qkv9Heb50zeArykWf61rkzObP57cxJwRvPfoRMW2n+TZsqkaV9G54d4fwH/f3tn8yJHEYbx3wsr6kY0fhC/9hBXxKsBD0E9SCIx0bDrIQchoKj/gCdFFgTvol5EDwmCEhRcg+amQQVPiZhgjBg/FhLiho0JxKyiYBJ8PNTbm3Ho1awwM9j1/KCZ6ao6dD88U/V2Vb3T3FCTT/qPmmfAll6LJOkc0LwWqfNIWpB0ML//ChyhDC7TlEGX/Hwkv08Db6qwD1gdETcP+bIHSkRMAA8DO/I8gA3AbDbp16PRaRbYmO07Q0RcTelAdwJIOifpLBV7JBkDroyIMWAcWKAyn0j6DDjTV7xSXzwI7JV0RtLPwF5g8+CvfjC0aSLpI0kX8nQf5X8voWjyjqQ/JB0F5ijjUafGpGV8AvAy8AzQuwG9Cp/0U3MA1vZapFtHdC0jI5dF1gH7gRslLUAJ0oA12awGrV6hdAp/5vn1wNmeDrT3npf0yPrFbN8lJoHTwBu5LLsjIlZRsUcknQBeBI5TAq9F4AB1+6Rhpb7ovF/6eJIywwMVaxIRU8AJSYf6qqrUpOYArO1JtKqU0Ii4CngPeFrSL//UtKWsM1pFxFbglKQDvcUtTXUJdV1hjLJ88JqkdcBvlKWl5ei8JrmvaZqybHQLsArY0tK0Jp/8G8tpUI02ETEDXAB2NUUtzTqvSUSMAzPA823VLWWd16TmAOySXovUVSLiMkrwtUvS7iz+qVk2ys9TWd51re4FpiLiGGXafwNlRmx1LjXB3+95SY+sv4b2qfb/M/PAvKT9eT5LCchq9QjAA8BRSaclnQd2A/dQt08aVuqLGvxCbhrfCmxXbmqiXk1upzy8HMq+dgI4GBE3UakmNQdg1b4WKfeh7ASOSHqpp2oP0GSZPA580FP+WGaqrAcWm+WGLiDpOUkTktZSfPCJpO3Ap8C2bNavR6PTtmzfmacyAEkngR8j4s4s2gh8Q6UeSY4D6yNiPH9DjSbV+qSHlfriQ2BTRFybM4ubsqwzRMRm4FlgStLvPVV7gEczS/Y24A7gczo+Jkk6LGmNpLXZ185TksFOUqtPRp0FMMqDknnxPSXzZGbU1zPE+76PMo37FfBlHg9R9qd8DPyQn9dl+wBeTZ0OA3eP+h4GqM39XMyCnKR0jHPAu8DlWX5Fns9l/eSor3tAWtwFfJE+eZ+ShVS1R4AXgG+Br4G3KJlsVfkEeJuyB+48ZRB96r/4grIvai6PJ0Z9XwPQZI6yf6npY1/vaT+TmnwHbOkp78yY1KZJX/0xLmZBVuGT/sP/hG+MMcYYM2RqXoI0xhhjjBkJDsCMMcYYY4aMAzBjjDHGmCHjAMwYY4wxZsg4ADPGGGOMGTIOwIwxxhhjhowDMGOMMcaYIeMAzBhjjDFmyPwFNBJd0e0VHncAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x251ef7b00f0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig, ax = plt.subplots(figsize=(10,7))\n",
    "ax.set_yticks(np.arange(32)*50)\n",
    "orig_dest_only[orig_dest_only.stop_sequence!=1].groupby('stop_id').size().sort_values(ascending=False).hist(bins=147, ax=ax)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I was all jolly and happy, until I did this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "92384"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orig_dest_only[orig_dest_only.stop_sequence!=1].groupby('stop_id').size().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(89777, 6)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No good. I've actually found more last stops than trips...\n",
    "\n",
    "This could be due to a gazillion reasons, so lets go ahead and go over them until we nail it.\n",
    "\n",
    "#### stop_sequence Mystery or \"The Bus with No Origin\"\n",
    "I used stop_sequence to find first and last stops, maybe there's an issue in the data\n",
    "\n",
    "Let's just do a simple count - "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(89105, 15)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f[f.stop_sequence==1].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(91314, 15)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f[f.stop_sequence==2].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There's less first stop than trips, but more 2nd stops than trips. Ok this is crazy, and certainly needs to be looked at in the future, but lets just leave it and use some other field for this task.\n",
    "\n",
    "TODO: dig into `stop_sequence` and talk to MOT about this\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Better yet, or \"Timing is Everything\"\n",
    "Lets use the `arrival_time` field instead, which should be cleaner. I'll skip the inefficient method I used before with the custom groupby aggregationm, and go for some neat trick using the mighty `transform` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>trip_id</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>date</th>\n",
       "      <th>stop_name</th>\n",
       "      <th>stop_lat</th>\n",
       "      <th>stop_lon</th>\n",
       "      <th>zone_id</th>\n",
       "      <th>zone_name</th>\n",
       "      <th>route_id</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>route_short_name</th>\n",
       "      <th>route_long_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3226900</th>\n",
       "      <td>10096398_171217</td>\n",
       "      <td>0 days 22:05:22</td>\n",
       "      <td>0 days 22:05:22</td>\n",
       "      <td>34503</td>\n",
       "      <td>37</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>אוניברסיטת אריאל/כביש 31</td>\n",
       "      <td>32.105926</td>\n",
       "      <td>35.210692</td>\n",
       "      <td>234</td>\n",
       "      <td>השומרון</td>\n",
       "      <td>9735</td>\n",
       "      <td>1</td>\n",
       "      <td>70</td>\n",
       "      <td>קניון קרני שומרון-קרני שומרון&lt;-&gt;אוניברסיטת ארי...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3226937</th>\n",
       "      <td>10096399_171217</td>\n",
       "      <td>1 days 00:05:22</td>\n",
       "      <td>1 days 00:05:22</td>\n",
       "      <td>34503</td>\n",
       "      <td>37</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>אוניברסיטת אריאל/כביש 31</td>\n",
       "      <td>32.105926</td>\n",
       "      <td>35.210692</td>\n",
       "      <td>234</td>\n",
       "      <td>השומרון</td>\n",
       "      <td>9735</td>\n",
       "      <td>1</td>\n",
       "      <td>70</td>\n",
       "      <td>קניון קרני שומרון-קרני שומרון&lt;-&gt;אוניברסיטת ארי...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3227555</th>\n",
       "      <td>10221383_171217</td>\n",
       "      <td>0 days 15:21:28</td>\n",
       "      <td>0 days 15:21:28</td>\n",
       "      <td>34654</td>\n",
       "      <td>16</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>קופת חולים לאומית/דרך הנחשונים</td>\n",
       "      <td>32.105873</td>\n",
       "      <td>35.171849</td>\n",
       "      <td>234</td>\n",
       "      <td>השומרון</td>\n",
       "      <td>9194</td>\n",
       "      <td>1</td>\n",
       "      <td>102</td>\n",
       "      <td>קופת חולים כללית-ברכה&lt;-&gt;קופת חולים לאומית/דרך ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3227571</th>\n",
       "      <td>10221384_171217</td>\n",
       "      <td>0 days 16:26:28</td>\n",
       "      <td>0 days 16:26:28</td>\n",
       "      <td>34654</td>\n",
       "      <td>16</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>קופת חולים לאומית/דרך הנחשונים</td>\n",
       "      <td>32.105873</td>\n",
       "      <td>35.171849</td>\n",
       "      <td>234</td>\n",
       "      <td>השומרון</td>\n",
       "      <td>9194</td>\n",
       "      <td>1</td>\n",
       "      <td>102</td>\n",
       "      <td>קופת חולים כללית-ברכה&lt;-&gt;קופת חולים לאומית/דרך ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>973521</th>\n",
       "      <td>10230038_171217</td>\n",
       "      <td>0 days 16:26:40</td>\n",
       "      <td>0 days 16:26:40</td>\n",
       "      <td>22173</td>\n",
       "      <td>31</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>בריכה/עמק דותן</td>\n",
       "      <td>31.899377</td>\n",
       "      <td>35.017810</td>\n",
       "      <td>232</td>\n",
       "      <td>חבל מודיעין</td>\n",
       "      <td>11169</td>\n",
       "      <td>1</td>\n",
       "      <td>101</td>\n",
       "      <td>מסוף 2000-תל אביב יפו&lt;-&gt;בריכה/עמק דותן-מודיעין...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 trip_id  departure_time    arrival_time stop_id  \\\n",
       "3226900  10096398_171217 0 days 22:05:22 0 days 22:05:22   34503   \n",
       "3226937  10096399_171217 1 days 00:05:22 1 days 00:05:22   34503   \n",
       "3227555  10221383_171217 0 days 15:21:28 0 days 15:21:28   34654   \n",
       "3227571  10221384_171217 0 days 16:26:28 0 days 16:26:28   34654   \n",
       "973521   10230038_171217 0 days 16:26:40 0 days 16:26:40   22173   \n",
       "\n",
       "         stop_sequence        date                       stop_name   stop_lat  \\\n",
       "3226900             37  2017-12-21        אוניברסיטת אריאל/כביש 31  32.105926   \n",
       "3226937             37  2017-12-21        אוניברסיטת אריאל/כביש 31  32.105926   \n",
       "3227555             16  2017-12-21  קופת חולים לאומית/דרך הנחשונים  32.105873   \n",
       "3227571             16  2017-12-21  קופת חולים לאומית/דרך הנחשונים  32.105873   \n",
       "973521              31  2017-12-21                  בריכה/עמק דותן  31.899377   \n",
       "\n",
       "          stop_lon zone_id    zone_name route_id  direction_id  \\\n",
       "3226900  35.210692     234      השומרון     9735             1   \n",
       "3226937  35.210692     234      השומרון     9735             1   \n",
       "3227555  35.171849     234      השומרון     9194             1   \n",
       "3227571  35.171849     234      השומרון     9194             1   \n",
       "973521   35.017810     232  חבל מודיעין    11169             1   \n",
       "\n",
       "        route_short_name                                    route_long_name  \n",
       "3226900               70  קניון קרני שומרון-קרני שומרון<->אוניברסיטת ארי...  \n",
       "3226937               70  קניון קרני שומרון-קרני שומרון<->אוניברסיטת ארי...  \n",
       "3227555              102  קופת חולים כללית-ברכה<->קופת חולים לאומית/דרך ...  \n",
       "3227571              102  קופת חולים כללית-ברכה<->קופת חולים לאומית/דרך ...  \n",
       "973521               101  מסוף 2000-תל אביב יפו<->בריכה/עמק דותן-מודיעין...  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idx = f.groupby(['trip_id'], sort=False)['arrival_time'].transform(max) == f['arrival_time']\n",
    "f[idx].sort_values(by='trip_id').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That took less than a second to run, now lets see if we also got better results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(92212, 15)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f[idx].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok we're on the good side of the error this time (I think), with some false positives instead of negatives.\n",
    "\n",
    "But now it means we might have duplicate trips in our dataset :(\n",
    "\n",
    "Let's zoom-in on one of the duplicate trips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29881457_211217    2\n",
       "24473826_211217    2\n",
       "29871173_211217    2\n",
       "10475384_211217    2\n",
       "11944462_171217    2\n",
       "Name: trip_id, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f[idx].trip_id.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>trip_id</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>pickup_type</th>\n",
       "      <th>drop_off_type</th>\n",
       "      <th>shape_dist_traveled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14876171</th>\n",
       "      <td>24167955_171217</td>\n",
       "      <td>56920.0</td>\n",
       "      <td>56920.0</td>\n",
       "      <td>36923</td>\n",
       "      <td>31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>6364</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  trip_id  arrival_time  departure_time stop_id  \\\n",
       "14876171  24167955_171217       56920.0         56920.0   36923   \n",
       "\n",
       "          stop_sequence  pickup_type drop_off_type  shape_dist_traveled  \n",
       "14876171             31          0.0             0                 6364  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "st = feed.stop_times\n",
    "st[((st.trip_id=='24167955_171217') & (st.stop_sequence==31)) ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>trip_id</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>date</th>\n",
       "      <th>stop_name</th>\n",
       "      <th>stop_lat</th>\n",
       "      <th>stop_lon</th>\n",
       "      <th>zone_id</th>\n",
       "      <th>zone_name</th>\n",
       "      <th>route_id</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>route_short_name</th>\n",
       "      <th>route_long_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2757683</th>\n",
       "      <td>24167955_171217</td>\n",
       "      <td>15:48:40</td>\n",
       "      <td>15:48:40</td>\n",
       "      <td>36923</td>\n",
       "      <td>31</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>הריטב''א ג</td>\n",
       "      <td>31.929852</td>\n",
       "      <td>35.049796</td>\n",
       "      <td>232</td>\n",
       "      <td>חבל מודיעין</td>\n",
       "      <td>11514</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>מסילת ישרים/דרך קרית ספר-מודיעין עילית&lt;-&gt;אבי ע...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2757684</th>\n",
       "      <td>24167955_171217</td>\n",
       "      <td>15:48:40</td>\n",
       "      <td>15:48:40</td>\n",
       "      <td>36923</td>\n",
       "      <td>31</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>הריטב''א ג</td>\n",
       "      <td>31.929852</td>\n",
       "      <td>35.049796</td>\n",
       "      <td>232</td>\n",
       "      <td>חבל מודיעין</td>\n",
       "      <td>11514</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>מסילת ישרים/דרך קרית ספר-מודיעין עילית&lt;-&gt;אבי ע...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 trip_id departure_time arrival_time stop_id  stop_sequence  \\\n",
       "2757683  24167955_171217       15:48:40     15:48:40   36923             31   \n",
       "2757684  24167955_171217       15:48:40     15:48:40   36923             31   \n",
       "\n",
       "               date   stop_name   stop_lat   stop_lon zone_id    zone_name  \\\n",
       "2757683  2017-12-21  הריטב''א ג  31.929852  35.049796     232  חבל מודיעין   \n",
       "2757684  2017-12-21  הריטב''א ג  31.929852  35.049796     232  חבל מודיעין   \n",
       "\n",
       "        route_id  direction_id route_short_name  \\\n",
       "2757683    11514             1                5   \n",
       "2757684    11514             1                5   \n",
       "\n",
       "                                           route_long_name  \n",
       "2757683  מסילת ישרים/דרך קרית ספר-מודיעין עילית<->אבי ע...  \n",
       "2757684  מסילת ישרים/דרך קרית ספר-מודיעין עילית<->אבי ע...  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f[((f.trip_id=='24167955_171217') & (f.stop_sequence==31)) ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, that looks better, this problem is only in my `f` feed merged data frame, not in the original `stop_times.txt`.\n",
    "\n",
    "I debug `f`'s creation to see (by building it one step at a time), I spare you these steps, and give you the problematic step: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>trip_id</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>date</th>\n",
       "      <th>stop_name</th>\n",
       "      <th>stop_lat</th>\n",
       "      <th>stop_lon</th>\n",
       "      <th>zone_id</th>\n",
       "      <th>zone_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2557765</th>\n",
       "      <td>10230038_171217</td>\n",
       "      <td>59200.0</td>\n",
       "      <td>59200.0</td>\n",
       "      <td>22173</td>\n",
       "      <td>31</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>בריכה/עמק דותן</td>\n",
       "      <td>31.899377</td>\n",
       "      <td>35.01781</td>\n",
       "      <td>232</td>\n",
       "      <td>חבל מודיעין</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2557766</th>\n",
       "      <td>10230038_171217</td>\n",
       "      <td>59200.0</td>\n",
       "      <td>59200.0</td>\n",
       "      <td>22173</td>\n",
       "      <td>31</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>בריכה/עמק דותן</td>\n",
       "      <td>31.899377</td>\n",
       "      <td>35.01781</td>\n",
       "      <td>232</td>\n",
       "      <td>חבל מודיעין</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 trip_id  departure_time  arrival_time stop_id  stop_sequence  \\\n",
       "2557765  10230038_171217         59200.0       59200.0   22173             31   \n",
       "2557766  10230038_171217         59200.0       59200.0   22173             31   \n",
       "\n",
       "               date       stop_name   stop_lat  stop_lon zone_id    zone_name  \n",
       "2557765  2017-12-21  בריכה/עמק דותן  31.899377  35.01781     232  חבל מודיעין  \n",
       "2557766  2017-12-21  בריכה/עמק דותן  31.899377  35.01781     232  חבל מודיעין  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ftest = (feed.stop_times[['trip_id', 'departure_time', 'arrival_time', 'stop_id', 'stop_sequence']]\n",
    "     .assign(date = datetime.date(2017, 12, 21))\n",
    "     .merge(s[['stop_id', 'stop_name', 'stop_lat', 'stop_lon', 'zone_id']], on='stop_id')\n",
    "      # Much faster joins and slices with Categorical dtypes\n",
    "     .merge(tariff_df[['zone_id', 'zone_name']], on='zone_id')\n",
    "\n",
    "    )\n",
    "ftest[ ((ftest.trip_id=='10230038_171217') & (ftest.stop_sequence==31)) ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Daily</th>\n",
       "      <th>FromDate</th>\n",
       "      <th>Monthly</th>\n",
       "      <th>ShareCode</th>\n",
       "      <th>ToDate</th>\n",
       "      <th>Weekly</th>\n",
       "      <th>zone_id</th>\n",
       "      <th>zone_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>13.5</td>\n",
       "      <td>01/04/2016 00:00:00</td>\n",
       "      <td>149.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>01/01/2200 00:00:00</td>\n",
       "      <td>57.5</td>\n",
       "      <td>232</td>\n",
       "      <td>חבל מודיעין</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>13.5</td>\n",
       "      <td>01/04/2016 00:00:00</td>\n",
       "      <td>149.0</td>\n",
       "      <td>697.0</td>\n",
       "      <td>01/01/2200 00:00:00</td>\n",
       "      <td>57.5</td>\n",
       "      <td>232</td>\n",
       "      <td>חבל מודיעין</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Daily             FromDate  Monthly  ShareCode               ToDate  \\\n",
       "14   13.5  01/04/2016 00:00:00    149.0      156.0  01/01/2200 00:00:00   \n",
       "57   13.5  01/04/2016 00:00:00    149.0      697.0  01/01/2200 00:00:00   \n",
       "\n",
       "    Weekly zone_id    zone_name  \n",
       "14    57.5     232  חבל מודיעין  \n",
       "57    57.5     232  חבל מודיעין  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tariff_df[tariff_df.zone_id=='232']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Got it! Source of all evil - the super-messy tariff file where we get the zone names from."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "zone_id  zone_name                \n",
       "110      סובב ירושלים                 1\n",
       "121      הרי ירושלים                  1\n",
       "122      בנימין                       1\n",
       "123      גוש עציון                    1\n",
       "131      אזור בית שמש                 1\n",
       "133      בקעת ים המלח                 1\n",
       "134      אזור קריית ארבע              1\n",
       "135      גוש שילה ובקעה               1\n",
       "210      גוש דן                       1\n",
       "221      שרון                         1\n",
       "222      ראש העין שוהם                1\n",
       "223      רחובות                       1\n",
       "231      נתניה                        1\n",
       "232      חבל מודיעין                  2\n",
       "233      אשדוד                        1\n",
       "234      השומרון                      1\n",
       "310      סובב חיפה                    1\n",
       "321      עכו                          1\n",
       "322      יוקנעם- טבעון                1\n",
       "331      נהריה                        1\n",
       "332      כרמיאל                       1\n",
       "333      נצרת                         1\n",
       "334      זכרון                        1\n",
       "410      באר שבע                      1\n",
       "421      רהט להבים                    1\n",
       "422      אופקים                       1\n",
       "423      עומר חורה                    1\n",
       "431      נתיבות שדרות                 1\n",
       "432      ירוחם                        1\n",
       "433      ערד דימונה                   1\n",
       "434      דרום הר חברון                1\n",
       "501      גליל עליון ורמת הגולן        1\n",
       "502      סובב כנרת ודרום רמת הגולן    1\n",
       "601      עפולה                        1\n",
       "602      בית שאן                      1\n",
       "701      אזור חדרה                    1\n",
       "702      חריש ואדי ערה                1\n",
       "801      אשקלון                       1\n",
       "802      קריית גת                     1\n",
       "901      אילת                         1\n",
       "902      ערבה                         1\n",
       "903      מצפה רמון                    1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tariff_df.groupby(['zone_id', 'zone_name']).size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See if you can find the needle...\n",
    "\n",
    "Now lets rebuild our `f` dataset with deduped zones:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>trip_id</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>date</th>\n",
       "      <th>stop_name</th>\n",
       "      <th>stop_lat</th>\n",
       "      <th>stop_lon</th>\n",
       "      <th>zone_id</th>\n",
       "      <th>zone_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2493834</th>\n",
       "      <td>10230038_171217</td>\n",
       "      <td>59200.0</td>\n",
       "      <td>59200.0</td>\n",
       "      <td>22173</td>\n",
       "      <td>31</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>בריכה/עמק דותן</td>\n",
       "      <td>31.899377</td>\n",
       "      <td>35.01781</td>\n",
       "      <td>232</td>\n",
       "      <td>חבל מודיעין</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 trip_id  departure_time  arrival_time stop_id  stop_sequence  \\\n",
       "2493834  10230038_171217         59200.0       59200.0   22173             31   \n",
       "\n",
       "               date       stop_name   stop_lat  stop_lon zone_id    zone_name  \n",
       "2493834  2017-12-21  בריכה/עמק דותן  31.899377  35.01781     232  חבל מודיעין  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ftry = (feed.stop_times[['trip_id', 'departure_time', 'arrival_time', 'stop_id', 'stop_sequence']]\n",
    "     .assign(date = datetime.date(2017, 12, 21))\n",
    "     .merge(s[['stop_id', 'stop_name', 'stop_lat', 'stop_lon', 'zone_id']], on='stop_id')\n",
    "      # Much faster joins and slices with Categorical dtypes\n",
    "     .merge(tariff_df.groupby(['zone_id', 'zone_name']).size().reset_index()[['zone_id', 'zone_name']], on='zone_id')\n",
    "    )\n",
    "ftry [ ((ftry.trip_id=='10230038_171217') & (ftry.stop_sequence==31)) ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>trip_id</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>date</th>\n",
       "      <th>stop_name</th>\n",
       "      <th>stop_lat</th>\n",
       "      <th>stop_lon</th>\n",
       "      <th>zone_id</th>\n",
       "      <th>zone_name</th>\n",
       "      <th>route_id</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>route_short_name</th>\n",
       "      <th>route_long_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>28917380_191217</td>\n",
       "      <td>08:57:00</td>\n",
       "      <td>08:57:00</td>\n",
       "      <td>37312</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>באר שבע מרכז</td>\n",
       "      <td>31.242886</td>\n",
       "      <td>34.798546</td>\n",
       "      <td>410</td>\n",
       "      <td>באר שבע</td>\n",
       "      <td>20950</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>באר שבע מרכז-באר שבע&lt;-&gt;תל אביב מרכז-תל אביב יפו</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>28917380_191217</td>\n",
       "      <td>09:04:00</td>\n",
       "      <td>09:04:00</td>\n",
       "      <td>37314</td>\n",
       "      <td>2</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>באר שבע-צפון</td>\n",
       "      <td>31.262089</td>\n",
       "      <td>34.809287</td>\n",
       "      <td>410</td>\n",
       "      <td>באר שבע</td>\n",
       "      <td>20950</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>באר שבע מרכז-באר שבע&lt;-&gt;תל אביב מרכז-תל אביב יפו</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28917380_191217</td>\n",
       "      <td>09:13:00</td>\n",
       "      <td>09:13:00</td>\n",
       "      <td>37308</td>\n",
       "      <td>3</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>להבים רהט</td>\n",
       "      <td>31.369907</td>\n",
       "      <td>34.798040</td>\n",
       "      <td>421</td>\n",
       "      <td>רהט להבים</td>\n",
       "      <td>20950</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>באר שבע מרכז-באר שבע&lt;-&gt;תל אביב מרכז-תל אביב יפו</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28917380_191217</td>\n",
       "      <td>09:29:00</td>\n",
       "      <td>09:29:00</td>\n",
       "      <td>37316</td>\n",
       "      <td>4</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>קרית גת</td>\n",
       "      <td>31.603526</td>\n",
       "      <td>34.777955</td>\n",
       "      <td>802</td>\n",
       "      <td>קריית גת</td>\n",
       "      <td>20950</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>באר שבע מרכז-באר שבע&lt;-&gt;תל אביב מרכז-תל אביב יפו</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28917380_191217</td>\n",
       "      <td>09:54:00</td>\n",
       "      <td>09:54:00</td>\n",
       "      <td>37336</td>\n",
       "      <td>5</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>רמלה</td>\n",
       "      <td>31.928809</td>\n",
       "      <td>34.877304</td>\n",
       "      <td>210</td>\n",
       "      <td>גוש דן</td>\n",
       "      <td>20950</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>באר שבע מרכז-באר שבע&lt;-&gt;תל אביב מרכז-תל אביב יפו</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           trip_id departure_time arrival_time stop_id  stop_sequence  \\\n",
       "0  28917380_191217       08:57:00     08:57:00   37312              1   \n",
       "1  28917380_191217       09:04:00     09:04:00   37314              2   \n",
       "2  28917380_191217       09:13:00     09:13:00   37308              3   \n",
       "3  28917380_191217       09:29:00     09:29:00   37316              4   \n",
       "4  28917380_191217       09:54:00     09:54:00   37336              5   \n",
       "\n",
       "         date     stop_name   stop_lat   stop_lon zone_id  zone_name route_id  \\\n",
       "0  2017-12-21  באר שבע מרכז  31.242886  34.798546     410    באר שבע    20950   \n",
       "1  2017-12-21  באר שבע-צפון  31.262089  34.809287     410    באר שבע    20950   \n",
       "2  2017-12-21     להבים רהט  31.369907  34.798040     421  רהט להבים    20950   \n",
       "3  2017-12-21       קרית גת  31.603526  34.777955     802   קריית גת    20950   \n",
       "4  2017-12-21          רמלה  31.928809  34.877304     210     גוש דן    20950   \n",
       "\n",
       "   direction_id route_short_name  \\\n",
       "0             1              NaN   \n",
       "1             1              NaN   \n",
       "2             1              NaN   \n",
       "3             1              NaN   \n",
       "4             1              NaN   \n",
       "\n",
       "                                   route_long_name  \n",
       "0  באר שבע מרכז-באר שבע<->תל אביב מרכז-תל אביב יפו  \n",
       "1  באר שבע מרכז-באר שבע<->תל אביב מרכז-תל אביב יפו  \n",
       "2  באר שבע מרכז-באר שבע<->תל אביב מרכז-תל אביב יפו  \n",
       "3  באר שבע מרכז-באר שבע<->תל אביב מרכז-תל אביב יפו  \n",
       "4  באר שבע מרכז-באר שבע<->תל אביב מרכז-תל אביב יפו  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_f = (feed.stop_times[['trip_id', 'departure_time', 'arrival_time', 'stop_id', 'stop_sequence']]\n",
    "     .assign(date = datetime.date(2017, 12, 21))\n",
    "     .merge(s[['stop_id', 'stop_name', 'stop_lat', 'stop_lon', 'zone_id']], on='stop_id')\n",
    "     # Much faster joins and slices with Categorical dtypes\n",
    "     .merge(tariff_df.groupby(['zone_id', 'zone_name']).size().reset_index()[['zone_id', 'zone_name']], on='zone_id')\n",
    "     .assign(zone_id=lambda x: pd.Categorical(x['zone_id']))\n",
    "     .assign(zone_name=lambda x: pd.Categorical(x['zone_name']))\n",
    "     .merge(t[['trip_id', 'route_id', 'direction_id']], on='trip_id')\n",
    "     .merge(r[['route_id', 'route_short_name', 'route_long_name']], on='route_id')\n",
    "     .assign(route_id=lambda x: pd.Categorical(x['route_id']))\n",
    "     .pipe(to_timedelta)\n",
    "    )\n",
    "new_f.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TODO: apply this fix to previous and in-progress notebooks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24120086_171217    1\n",
       "29732399_201217    1\n",
       "18611907_171217    1\n",
       "29838665_211217    1\n",
       "23938776_211217    1\n",
       "Name: trip_id, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idx = new_f.groupby(['trip_id'], sort=False)['arrival_time'].transform(max) == new_f['arrival_time']\n",
    "new_f[idx].trip_id.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(89768, 15)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_f[idx].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Trip duplicates - <span style=\"color:green\">**check.**</span>\n",
    "\n",
    "And now we're actually missing 9. hmmmm... \n",
    "\n",
    "Lets see which ones we're missing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>route_id</th>\n",
       "      <th>service_id</th>\n",
       "      <th>trip_headsign</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>shape_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trip_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>30277345_211217</th>\n",
       "      <td>8630</td>\n",
       "      <td>54178129</td>\n",
       "      <td>מודיעין עילית _ מרכז</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30277344_211217</th>\n",
       "      <td>8630</td>\n",
       "      <td>54178129</td>\n",
       "      <td>מודיעין עילית _ מרכז</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30277772_211217</th>\n",
       "      <td>8647</td>\n",
       "      <td>54178161</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30277770_211217</th>\n",
       "      <td>8647</td>\n",
       "      <td>54178161</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30278247_211217</th>\n",
       "      <td>8664</td>\n",
       "      <td>54178172</td>\n",
       "      <td>מודיעין עילית _ אחוזת ברכפלד</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30278261_211217</th>\n",
       "      <td>8664</td>\n",
       "      <td>54178172</td>\n",
       "      <td>מודיעין עילית _ אחוזת ברכפלד</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30278540_211217</th>\n",
       "      <td>8678</td>\n",
       "      <td>54178210</td>\n",
       "      <td>בני ברק _ מרכז</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30278718_211217</th>\n",
       "      <td>8681</td>\n",
       "      <td>54178238</td>\n",
       "      <td>מודיעין עילית _ נאות הפסגה</td>\n",
       "      <td>1</td>\n",
       "      <td>63594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30278717_211217</th>\n",
       "      <td>8681</td>\n",
       "      <td>54178238</td>\n",
       "      <td>מודיעין עילית _ נאות הפסגה</td>\n",
       "      <td>1</td>\n",
       "      <td>63594</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                route_id service_id                 trip_headsign  \\\n",
       "trip_id                                                             \n",
       "30277345_211217     8630   54178129          מודיעין עילית _ מרכז   \n",
       "30277344_211217     8630   54178129          מודיעין עילית _ מרכז   \n",
       "30277772_211217     8647   54178161                           NaN   \n",
       "30277770_211217     8647   54178161                           NaN   \n",
       "30278247_211217     8664   54178172  מודיעין עילית _ אחוזת ברכפלד   \n",
       "30278261_211217     8664   54178172  מודיעין עילית _ אחוזת ברכפלד   \n",
       "30278540_211217     8678   54178210                בני ברק _ מרכז   \n",
       "30278718_211217     8681   54178238    מודיעין עילית _ נאות הפסגה   \n",
       "30278717_211217     8681   54178238    מודיעין עילית _ נאות הפסגה   \n",
       "\n",
       "                 direction_id shape_id  \n",
       "trip_id                                 \n",
       "30277345_211217             1      NaN  \n",
       "30277344_211217             1      NaN  \n",
       "30277772_211217             1      NaN  \n",
       "30277770_211217             1      NaN  \n",
       "30278247_211217             1      NaN  \n",
       "30278261_211217             1      NaN  \n",
       "30278540_211217             0      NaN  \n",
       "30278718_211217             1    63594  \n",
       "30278717_211217             1    63594  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = t.set_index('trip_id')\n",
    "missing_trips = t[~t.index.isin(new_f.trip_id)]\n",
    "missing_trips"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Maybe they just don't have stop times?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 8)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "st[st.trip_id.isin(missing_trips.index)].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Nope. Good!\n",
    "\n",
    "To be safe lets test we are getting `stop_times` rows from the same line of code, on another series of trips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>route_id</th>\n",
       "      <th>service_id</th>\n",
       "      <th>trip_headsign</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>shape_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trip_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>30179315_211217</th>\n",
       "      <td>20444</td>\n",
       "      <td>54163484</td>\n",
       "      <td>יהוד מונוסון _ דרך העצמאות</td>\n",
       "      <td>0</td>\n",
       "      <td>82704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30179316_211217</th>\n",
       "      <td>20444</td>\n",
       "      <td>54163484</td>\n",
       "      <td>יהוד מונוסון _ דרך העצמאות</td>\n",
       "      <td>0</td>\n",
       "      <td>82704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30180186_211217</th>\n",
       "      <td>20354</td>\n",
       "      <td>54163559</td>\n",
       "      <td>יהוד מונוסון _ דרך העצמאות</td>\n",
       "      <td>1</td>\n",
       "      <td>82455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30180187_211217</th>\n",
       "      <td>20354</td>\n",
       "      <td>54163559</td>\n",
       "      <td>יהוד מונוסון _ דרך העצמאות</td>\n",
       "      <td>1</td>\n",
       "      <td>82455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30180188_211217</th>\n",
       "      <td>20354</td>\n",
       "      <td>54163559</td>\n",
       "      <td>יהוד מונוסון _ דרך העצמאות</td>\n",
       "      <td>1</td>\n",
       "      <td>82455</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                route_id service_id               trip_headsign  direction_id  \\\n",
       "trip_id                                                                         \n",
       "30179315_211217    20444   54163484  יהוד מונוסון _ דרך העצמאות             0   \n",
       "30179316_211217    20444   54163484  יהוד מונוסון _ דרך העצמאות             0   \n",
       "30180186_211217    20354   54163559  יהוד מונוסון _ דרך העצמאות             1   \n",
       "30180187_211217    20354   54163559  יהוד מונוסון _ דרך העצמאות             1   \n",
       "30180188_211217    20354   54163559  יהוד מונוסון _ דרך העצמאות             1   \n",
       "\n",
       "                shape_id  \n",
       "trip_id                   \n",
       "30179315_211217    82704  \n",
       "30179316_211217    82704  \n",
       "30180186_211217    82455  \n",
       "30180187_211217    82455  \n",
       "30180188_211217    82455  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_trips = t.head()\n",
    "test_trips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(256, 8)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "st[st.trip_id.isin(test_trips.index)].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Missing trip - <span style=\"color:green\">**check.**</span>\n",
    "\n",
    "Woohoo! looks like we're good to go (For now :O )\n",
    "\n",
    "## Goodbye\n",
    "\n",
    "To shortly sum up - cleanup is an ongoing process. I have found an issue in the original data (stop_sequence) and an issue with how I merged tables (double zone in the very very messy tariffs table). \n",
    "\n",
    "See you soon when I test [GTFS best practices](http://gtfs.org/best-practices) on our GTFS files (with regards to loops routes, lasso routes and some more)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24120086_171217    1\n",
       "29732399_201217    1\n",
       "18611907_171217    1\n",
       "29838665_211217    1\n",
       "23938776_211217    1\n",
       "Name: trip_id, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idx = new_f.groupby(['trip_id'], sort=False)['arrival_time'].transform(min) == new_f['arrival_time']\n",
    "new_f[idx].trip_id.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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
}