GTFS_Schedule_For_aTrip.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Goal of this example is to extract route details, stops, and schedule information for one trip of a particular route. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I live in Seattle, and want to find the information about bus line '7' that goes next to where I live. Seattle bus service is provided by King County Metro (KCM). I've downloaded the GTFS files for KCM from https://transitfeeds.com/. Side note: https://transitfeeds.com/ has a lot of agencies' data and you may find the one you're looking for there. I've saved KCM data under ./data/kcm/ directory. Change the directory to where you stored your gtfs feeds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "bus_num = \"7\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "dir_path=\"./data/kcm\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's load routes.txt (Remember that even though the file extension is .txt, it is actually a csv file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "routes_file=dir_path+\"/routes.txt\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(routes_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's examine the first few rows of the csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>agency_id</th>\n",
       "      <th>route_short_name</th>\n",
       "      <th>route_long_name</th>\n",
       "      <th>route_desc</th>\n",
       "      <th>route_type</th>\n",
       "      <th>route_url</th>\n",
       "      <th>route_color</th>\n",
       "      <th>route_text_color</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>KCM</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kinnear - Downtown Seattle</td>\n",
       "      <td>3</td>\n",
       "      <td>http://metro.kingcounty.gov/schedules/001/n0.html</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100002</td>\n",
       "      <td>KCM</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Capitol Hill - Downtown Seattle</td>\n",
       "      <td>3</td>\n",
       "      <td>http://metro.kingcounty.gov/schedules/010/n0.html</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>KCM</td>\n",
       "      <td>101</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Renton Transit Center - Downtown Seattle</td>\n",
       "      <td>3</td>\n",
       "      <td>http://metro.kingcounty.gov/schedules/101/n0.html</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100004</td>\n",
       "      <td>KCM</td>\n",
       "      <td>105</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Renton Highlands - Renton Transit Center</td>\n",
       "      <td>3</td>\n",
       "      <td>http://metro.kingcounty.gov/schedules/105/n0.html</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100005</td>\n",
       "      <td>KCM</td>\n",
       "      <td>106</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Renton Transit Center - Skyway - Downtown Seattle</td>\n",
       "      <td>3</td>\n",
       "      <td>http://metro.kingcounty.gov/schedules/106/n0.html</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   route_id agency_id route_short_name  route_long_name  \\\n",
       "0    100001       KCM                1              NaN   \n",
       "1    100002       KCM               10              NaN   \n",
       "2    100003       KCM              101              NaN   \n",
       "3    100004       KCM              105              NaN   \n",
       "4    100005       KCM              106              NaN   \n",
       "\n",
       "                                          route_desc  route_type  \\\n",
       "0                         Kinnear - Downtown Seattle           3   \n",
       "1                    Capitol Hill - Downtown Seattle           3   \n",
       "2           Renton Transit Center - Downtown Seattle           3   \n",
       "3           Renton Highlands - Renton Transit Center           3   \n",
       "4  Renton Transit Center - Skyway - Downtown Seattle           3   \n",
       "\n",
       "                                           route_url  route_color  \\\n",
       "0  http://metro.kingcounty.gov/schedules/001/n0.html          NaN   \n",
       "1  http://metro.kingcounty.gov/schedules/010/n0.html          NaN   \n",
       "2  http://metro.kingcounty.gov/schedules/101/n0.html          NaN   \n",
       "3  http://metro.kingcounty.gov/schedules/105/n0.html          NaN   \n",
       "4  http://metro.kingcounty.gov/schedules/106/n0.html          NaN   \n",
       "\n",
       "   route_text_color  \n",
       "0               NaN  \n",
       "1               NaN  \n",
       "2               NaN  \n",
       "3               NaN  \n",
       "4               NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As expected, routes.txt contains all the different routes agencies operate, their short name, and the verbal description of the path they display on buses. Let's explore this file a bit more:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many routes are there?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "215"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['route_id'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Are all these routes operated by the same agency?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['agency_id'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Routes.txt has 4 agencies listed. What are they, and how many routes are operated by each agencies?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "KCM    203\n",
       "ST       8\n",
       "KMD      2\n",
       "EOS      2\n",
       "Name: agency_id, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['agency_id'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I didn't realize when I first started that it is possible to have information from various agencies in one GTFS feed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Does the route for bus line 7 even exist in this routes.txt file?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Yes, 7 route is in the routes list\n"
     ]
    }
   ],
   "source": [
    "if df.isin([bus_num]).any().any():\n",
    "    print (\"Yes, {} route is in the routes list\".format(bus_num))\n",
    "else:\n",
    "    print(\"No. Route { } is not part of this routes list\".format(bus_num))\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great! Let's see what information we have about bus line 7 in this file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                                                132\n",
      "route_id                                                     100263\n",
      "agency_id                                                       KCM\n",
      "route_short_name                                                  7\n",
      "route_long_name                                                 NaN\n",
      "route_desc           Prentice St - Rainier Beach - Downtown Seattle\n",
      "route_type                                                        3\n",
      "route_url         http://metro.kingcounty.gov/schedules/007/n0.html\n",
      "route_color                                                     NaN\n",
      "route_text_color                                                NaN\n"
     ]
    }
   ],
   "source": [
    "print(df[df.values == bus_num].T)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's examine this information. Check https://developers.google.com/transit/gtfs/reference/#routestxt for more information\n",
    "route_id is the internal id used by King County Metro for this particular route\n",
    "route_short_name is the bus line number\n",
    "route_desc is what one would see on the bus board display for this route, along with the short name '7'\n",
    "route_type shows '3' indicating this is a bus (as opposed to a train or a subway). \n",
    "route_url will take you to KCM's website where they display the schedule"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Recall that a particular instance of a route is called a trip. Trip information is contained in trips.txt, and the link between these two files is the route_id. See the relations picture."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "bus_route_id = df[df.values == bus_num][\"route_id\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "132    100263\n",
      "Name: route_id, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(bus_route_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's load the trips.txt file. Again, remember this is a csv file with txt extension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "trips_file = dir_path+\"/trips.txt\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "trips_df = pd.read_csv(trips_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "bus_trips = trips_df[trips_df['route_id'].isin([bus_route_id])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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_id</th>\n",
       "      <th>trip_headsign</th>\n",
       "      <th>trip_short_name</th>\n",
       "      <th>direction_id</th>\n",
       "      <th>block_id</th>\n",
       "      <th>shape_id</th>\n",
       "      <th>peak_flag</th>\n",
       "      <th>fare_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17964</th>\n",
       "      <td>100263</td>\n",
       "      <td>75804</td>\n",
       "      <td>41980848</td>\n",
       "      <td>Rainier Beach Via Rainier Ave S</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>5580995</td>\n",
       "      <td>21007003</td>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17965</th>\n",
       "      <td>100263</td>\n",
       "      <td>75804</td>\n",
       "      <td>41980851</td>\n",
       "      <td>Rainier Beach Via Rainier Ave S</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>5580999</td>\n",
       "      <td>21007003</td>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17966</th>\n",
       "      <td>100263</td>\n",
       "      <td>75804</td>\n",
       "      <td>41980853</td>\n",
       "      <td>Rainier Beach Via Rainier Ave S</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>5580998</td>\n",
       "      <td>21007008</td>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17967</th>\n",
       "      <td>100263</td>\n",
       "      <td>75804</td>\n",
       "      <td>41980857</td>\n",
       "      <td>Rainier Beach Via Rainier Ave S</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>5581000</td>\n",
       "      <td>21007008</td>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17968</th>\n",
       "      <td>100263</td>\n",
       "      <td>75804</td>\n",
       "      <td>41980862</td>\n",
       "      <td>Rainier Beach Via Rainier Ave S</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>5580995</td>\n",
       "      <td>21007003</td>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       route_id  service_id   trip_id                    trip_headsign  \\\n",
       "17964    100263       75804  41980848  Rainier Beach Via Rainier Ave S   \n",
       "17965    100263       75804  41980851  Rainier Beach Via Rainier Ave S   \n",
       "17966    100263       75804  41980853  Rainier Beach Via Rainier Ave S   \n",
       "17967    100263       75804  41980857  Rainier Beach Via Rainier Ave S   \n",
       "17968    100263       75804  41980862  Rainier Beach Via Rainier Ave S   \n",
       "\n",
       "      trip_short_name  direction_id  block_id  shape_id  peak_flag  fare_id  \n",
       "17964           LOCAL             0   5580995  21007003          0      101  \n",
       "17965           LOCAL             0   5580999  21007003          0      101  \n",
       "17966           LOCAL             0   5580998  21007008          0      101  \n",
       "17967           LOCAL             0   5581000  21007008          0      101  \n",
       "17968           LOCAL             0   5580995  21007003          0      101  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bus_trips.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As expected each line in the output has the same route_id, as we're examining bus 7 route. trip_id is a unique for each of the trips. service_id points to which kind of service this trip follows. We'll leave the rest aside for now."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Information about the schedule is given in stop_times.txt. The columns in stop_times.txt are : trip_id,arrival_time,departure_time,stop_id,stop_sequence,stop_headsign,pickup_type,drop_off_type,shape_dist_traveled. The one's we're currently interested in are the trip_id (to look up the trip_id we're interested in), stop_id (to figure out which stops are on this trip), arrival_time & departure_time for that stop. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "stop_times_file = dir_path+\"/stop_times.txt\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3051: DtypeWarning: Columns (5) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "stop_times_df = pd.read_csv(stop_times_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As always, let's examine the first few rows of this file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>stop_headsign</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>0</th>\n",
       "      <td>34745814</td>\n",
       "      <td>07:04:00</td>\n",
       "      <td>07:04:00</td>\n",
       "      <td>74232</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>34745814</td>\n",
       "      <td>07:04:18</td>\n",
       "      <td>07:04:18</td>\n",
       "      <td>74731</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>435.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>34745814</td>\n",
       "      <td>07:04:52</td>\n",
       "      <td>07:04:52</td>\n",
       "      <td>74734</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1284.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>34745814</td>\n",
       "      <td>07:05:44</td>\n",
       "      <td>07:05:44</td>\n",
       "      <td>85450</td>\n",
       "      <td>16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2554.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34745814</td>\n",
       "      <td>07:09:00</td>\n",
       "      <td>07:09:00</td>\n",
       "      <td>70516</td>\n",
       "      <td>47</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7366.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    trip_id arrival_time departure_time  stop_id  stop_sequence stop_headsign  \\\n",
       "0  34745814     07:04:00       07:04:00    74232              1           NaN   \n",
       "1  34745814     07:04:18       07:04:18    74731             10           NaN   \n",
       "2  34745814     07:04:52       07:04:52    74734             13           NaN   \n",
       "3  34745814     07:05:44       07:05:44    85450             16           NaN   \n",
       "4  34745814     07:09:00       07:09:00    70516             47           NaN   \n",
       "\n",
       "   pickup_type  drop_off_type  shape_dist_traveled  \n",
       "0            0              0                  0.0  \n",
       "1            0              0                435.7  \n",
       "2            0              0               1284.7  \n",
       "3            0              0               2554.8  \n",
       "4            0              0               7366.2  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stop_times_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's pick one trip of route 7, and map its times. For simplicity we'll pick the trip mentioned in the first row of 7 routes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "a_bus_trip = bus_trips.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "route_id                                    100263\n",
      "service_id                                   75804\n",
      "trip_id                                   41980848\n",
      "trip_headsign      Rainier Beach Via Rainier Ave S\n",
      "trip_short_name                              LOCAL\n",
      "direction_id                                     0\n",
      "block_id                                   5580995\n",
      "shape_id                                  21007003\n",
      "peak_flag                                        0\n",
      "fare_id                                        101\n",
      "Name: 17964, dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(a_bus_trip)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's extract the rows from stop_times dataframe that correspond to this particular trip."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "a_bus_trip_stop_times = stop_times_df.loc[stop_times_df.trip_id == a_bus_trip.trip_id]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>stop_headsign</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>652708</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:21:00</td>\n",
       "      <td>15:21:00</td>\n",
       "      <td>880</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>652709</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:26:00</td>\n",
       "      <td>15:26:00</td>\n",
       "      <td>430</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2405.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>652710</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:28:00</td>\n",
       "      <td>15:28:00</td>\n",
       "      <td>450</td>\n",
       "      <td>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3493.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>652711</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:31:19</td>\n",
       "      <td>15:31:19</td>\n",
       "      <td>480</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4890.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>652712</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:33:32</td>\n",
       "      <td>15:33:32</td>\n",
       "      <td>500</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5826.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         trip_id arrival_time departure_time  stop_id  stop_sequence  \\\n",
       "652708  41980848     15:21:00       15:21:00      880              1   \n",
       "652709  41980848     15:26:00       15:26:00      430             20   \n",
       "652710  41980848     15:28:00       15:28:00      450             25   \n",
       "652711  41980848     15:31:19       15:31:19      480             30   \n",
       "652712  41980848     15:33:32       15:33:32      500             35   \n",
       "\n",
       "       stop_headsign  pickup_type  drop_off_type  shape_dist_traveled  \n",
       "652708           NaN            0              0                  0.0  \n",
       "652709           NaN            0              0               2405.8  \n",
       "652710           NaN            0              0               3493.3  \n",
       "652711           NaN            0              0               4890.6  \n",
       "652712           NaN            0              0               5826.1  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a_bus_trip_stop_times.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we've arrival, departure times of this trip, we need to figure out the names of the stops along the way. Stops for this particular trip are in the stop_id of this data. The stop names are stored in stops.txt. Let's load that file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "stops_txt = dir_path+\"/stops.txt\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "stops_df = pd.read_csv(stops_txt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's examine the first few rows of this file."
   ]
  },
  {
   "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>stop_id</th>\n",
       "      <th>stop_code</th>\n",
       "      <th>stop_name</th>\n",
       "      <th>stop_desc</th>\n",
       "      <th>stop_lat</th>\n",
       "      <th>stop_lon</th>\n",
       "      <th>zone_id</th>\n",
       "      <th>stop_url</th>\n",
       "      <th>location_type</th>\n",
       "      <th>parent_station</th>\n",
       "      <th>stop_timezone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pine St &amp; 9th Ave</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.613415</td>\n",
       "      <td>-122.332138</td>\n",
       "      <td>21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NE 55th St &amp; 43rd Ave NE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.668575</td>\n",
       "      <td>-122.283653</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40th Ave NE &amp; NE 51st St</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.665886</td>\n",
       "      <td>-122.284897</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NE 55th St &amp; 39th Ave NE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.668579</td>\n",
       "      <td>-122.285667</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NE 55th St &amp; 37th Ave NE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.668579</td>\n",
       "      <td>-122.288300</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   stop_id  stop_code                 stop_name  stop_desc   stop_lat  \\\n",
       "0     1000        NaN         Pine St & 9th Ave        NaN  47.613415   \n",
       "1    10000        NaN  NE 55th St & 43rd Ave NE        NaN  47.668575   \n",
       "2    10005        NaN  40th Ave NE & NE 51st St        NaN  47.665886   \n",
       "3    10010        NaN  NE 55th St & 39th Ave NE        NaN  47.668579   \n",
       "4    10020        NaN  NE 55th St & 37th Ave NE        NaN  47.668579   \n",
       "\n",
       "     stop_lon  zone_id  stop_url  location_type  parent_station  \\\n",
       "0 -122.332138       21       NaN              0             NaN   \n",
       "1 -122.283653        1       NaN              0             NaN   \n",
       "2 -122.284897        1       NaN              0             NaN   \n",
       "3 -122.285667        1       NaN              0             NaN   \n",
       "4 -122.288300        1       NaN              0             NaN   \n",
       "\n",
       "         stop_timezone  \n",
       "0  America/Los_Angeles  \n",
       "1  America/Los_Angeles  \n",
       "2  America/Los_Angeles  \n",
       "3  America/Los_Angeles  \n",
       "4  America/Los_Angeles  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stops_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Recall this file contains all the stops in the transit area. stop_id is the unique identifier for a stop. stop_name is what you would see at the bus stop signs. stop_lon, and stop_lat give the GPS coordinates for the stop. stop_timezone is the time zone in which the stop is. For now, we will leave the others aside."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We're only interested in stops that bus line 7 makes. Let's extract only those stops out."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "stops = stops_df[stops_df.stop_id.isin(a_bus_trip_stop_times.stop_id)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "stop_names = stops_df[['stop_id','stop_name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So now we have the stop_ids, and arrival/departure time in one data frame, and the stop_ids, and stop_names in another. Let's combine them to get comprehensive information about the schedule for this particular trip."
   ]
  },
  {
   "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>arrival_time</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_sequence</th>\n",
       "      <th>stop_headsign</th>\n",
       "      <th>pickup_type</th>\n",
       "      <th>drop_off_type</th>\n",
       "      <th>shape_dist_traveled</th>\n",
       "      <th>stop_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:21:00</td>\n",
       "      <td>15:21:00</td>\n",
       "      <td>880</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Virginia St &amp; 6th Ave</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:26:00</td>\n",
       "      <td>15:26:00</td>\n",
       "      <td>430</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2405.8</td>\n",
       "      <td>3rd Ave &amp; Pine St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:28:00</td>\n",
       "      <td>15:28:00</td>\n",
       "      <td>450</td>\n",
       "      <td>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3493.3</td>\n",
       "      <td>3rd Ave &amp; Union St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:31:19</td>\n",
       "      <td>15:31:19</td>\n",
       "      <td>480</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4890.6</td>\n",
       "      <td>3rd Ave &amp; Marion St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:33:32</td>\n",
       "      <td>15:33:32</td>\n",
       "      <td>500</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5826.1</td>\n",
       "      <td>3rd Ave &amp; James St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:36:02</td>\n",
       "      <td>15:36:02</td>\n",
       "      <td>515</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6881.3</td>\n",
       "      <td>3rd Ave S &amp; S Main St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:38:00</td>\n",
       "      <td>15:38:00</td>\n",
       "      <td>1471</td>\n",
       "      <td>50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7709.5</td>\n",
       "      <td>S Jackson St &amp; 5th Ave S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:39:32</td>\n",
       "      <td>15:39:32</td>\n",
       "      <td>1480</td>\n",
       "      <td>55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8564.5</td>\n",
       "      <td>S Jackson St &amp; Maynard Ave S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:40:40</td>\n",
       "      <td>15:40:40</td>\n",
       "      <td>1490</td>\n",
       "      <td>59</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9188.2</td>\n",
       "      <td>S Jackson St &amp; 8th Ave S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:43:00</td>\n",
       "      <td>15:43:00</td>\n",
       "      <td>8540</td>\n",
       "      <td>63</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10489.8</td>\n",
       "      <td>S Jackson St &amp; 12th Ave S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:44:09</td>\n",
       "      <td>15:44:09</td>\n",
       "      <td>8550</td>\n",
       "      <td>68</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11436.8</td>\n",
       "      <td>Rainier Ave S &amp; S King St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:46:32</td>\n",
       "      <td>15:46:32</td>\n",
       "      <td>8590</td>\n",
       "      <td>77</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13385.9</td>\n",
       "      <td>Rainier Ave S &amp; S Charles St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:48:53</td>\n",
       "      <td>15:48:53</td>\n",
       "      <td>8608</td>\n",
       "      <td>89</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15314.3</td>\n",
       "      <td>Rainier Ave S &amp; I-90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:49:39</td>\n",
       "      <td>15:49:39</td>\n",
       "      <td>8620</td>\n",
       "      <td>93</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15931.5</td>\n",
       "      <td>Rainier Ave S &amp; S State St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:51:32</td>\n",
       "      <td>15:51:32</td>\n",
       "      <td>8641</td>\n",
       "      <td>99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17486.8</td>\n",
       "      <td>Rainier Ave S &amp; S Walker St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:53:01</td>\n",
       "      <td>15:53:01</td>\n",
       "      <td>8660</td>\n",
       "      <td>105</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>18697.2</td>\n",
       "      <td>Rainier Ave S &amp; S Bayview St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:55:00</td>\n",
       "      <td>15:55:00</td>\n",
       "      <td>8681</td>\n",
       "      <td>110</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20322.1</td>\n",
       "      <td>Rainier Ave S &amp; S Forest St - Bay 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:55:36</td>\n",
       "      <td>15:55:36</td>\n",
       "      <td>8690</td>\n",
       "      <td>114</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20774.8</td>\n",
       "      <td>Rainier Ave S &amp; Martin Luther King Junior Way S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:57:15</td>\n",
       "      <td>15:57:15</td>\n",
       "      <td>8710</td>\n",
       "      <td>118</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>22020.6</td>\n",
       "      <td>Rainier Ave S &amp; S Walden St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:58:51</td>\n",
       "      <td>15:58:51</td>\n",
       "      <td>8730</td>\n",
       "      <td>124</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23234.6</td>\n",
       "      <td>Rainier Ave S &amp; Letitia Ave S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>41980848</td>\n",
       "      <td>15:59:59</td>\n",
       "      <td>15:59:59</td>\n",
       "      <td>8740</td>\n",
       "      <td>129</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>24089.1</td>\n",
       "      <td>Rainier Ave S &amp; S Andover St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:02:00</td>\n",
       "      <td>16:02:00</td>\n",
       "      <td>8760</td>\n",
       "      <td>138</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25621.2</td>\n",
       "      <td>Rainier Ave S &amp; S Genesee St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:03:31</td>\n",
       "      <td>16:03:31</td>\n",
       "      <td>8780</td>\n",
       "      <td>145</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>26898.8</td>\n",
       "      <td>Rainier Ave S &amp; S Alaska St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:04:25</td>\n",
       "      <td>16:04:25</td>\n",
       "      <td>8790</td>\n",
       "      <td>149</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>27662.5</td>\n",
       "      <td>Rainier Ave S &amp; S Edmunds St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:06:00</td>\n",
       "      <td>16:06:00</td>\n",
       "      <td>8810</td>\n",
       "      <td>158</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>28998.1</td>\n",
       "      <td>Rainier Ave S &amp; S Dawson St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:07:18</td>\n",
       "      <td>16:07:18</td>\n",
       "      <td>8820</td>\n",
       "      <td>163</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30091.2</td>\n",
       "      <td>Rainier Ave S &amp; S Brandon St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:08:37</td>\n",
       "      <td>16:08:37</td>\n",
       "      <td>8840</td>\n",
       "      <td>171</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>31211.9</td>\n",
       "      <td>Rainier Ave S &amp; S Orcas St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:09:37</td>\n",
       "      <td>16:09:37</td>\n",
       "      <td>8850</td>\n",
       "      <td>178</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>32047.0</td>\n",
       "      <td>Rainier Ave S &amp; S Kenny St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:11:00</td>\n",
       "      <td>16:11:00</td>\n",
       "      <td>8870</td>\n",
       "      <td>187</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33219.2</td>\n",
       "      <td>Rainier Ave S &amp; S Graham St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:12:39</td>\n",
       "      <td>16:12:39</td>\n",
       "      <td>8890</td>\n",
       "      <td>195</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>34688.4</td>\n",
       "      <td>Rainier Ave S &amp; S Holly St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:13:57</td>\n",
       "      <td>16:13:57</td>\n",
       "      <td>8905</td>\n",
       "      <td>199</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>35851.5</td>\n",
       "      <td>Rainier Ave S &amp; S Frontenac St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:15:04</td>\n",
       "      <td>16:15:04</td>\n",
       "      <td>8920</td>\n",
       "      <td>205</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>36847.6</td>\n",
       "      <td>Rainier Ave S &amp; S Othello St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:16:25</td>\n",
       "      <td>16:16:25</td>\n",
       "      <td>8940</td>\n",
       "      <td>215</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>38051.1</td>\n",
       "      <td>Rainier Ave S &amp; S Holden St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:18:16</td>\n",
       "      <td>16:18:16</td>\n",
       "      <td>8970</td>\n",
       "      <td>223</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>39708.3</td>\n",
       "      <td>Rainier Ave S &amp; S Rose St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:19:27</td>\n",
       "      <td>16:19:27</td>\n",
       "      <td>8990</td>\n",
       "      <td>226</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40757.2</td>\n",
       "      <td>Rainier Ave S &amp; S Cloverdale St</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>41980848</td>\n",
       "      <td>16:21:00</td>\n",
       "      <td>16:21:00</td>\n",
       "      <td>31137</td>\n",
       "      <td>231</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>42149.0</td>\n",
       "      <td>S Henderson St &amp; Rainier Ave S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     trip_id arrival_time departure_time  stop_id  stop_sequence  \\\n",
       "0   41980848     15:21:00       15:21:00      880              1   \n",
       "1   41980848     15:26:00       15:26:00      430             20   \n",
       "2   41980848     15:28:00       15:28:00      450             25   \n",
       "3   41980848     15:31:19       15:31:19      480             30   \n",
       "4   41980848     15:33:32       15:33:32      500             35   \n",
       "5   41980848     15:36:02       15:36:02      515             44   \n",
       "6   41980848     15:38:00       15:38:00     1471             50   \n",
       "7   41980848     15:39:32       15:39:32     1480             55   \n",
       "8   41980848     15:40:40       15:40:40     1490             59   \n",
       "9   41980848     15:43:00       15:43:00     8540             63   \n",
       "10  41980848     15:44:09       15:44:09     8550             68   \n",
       "11  41980848     15:46:32       15:46:32     8590             77   \n",
       "12  41980848     15:48:53       15:48:53     8608             89   \n",
       "13  41980848     15:49:39       15:49:39     8620             93   \n",
       "14  41980848     15:51:32       15:51:32     8641             99   \n",
       "15  41980848     15:53:01       15:53:01     8660            105   \n",
       "16  41980848     15:55:00       15:55:00     8681            110   \n",
       "17  41980848     15:55:36       15:55:36     8690            114   \n",
       "18  41980848     15:57:15       15:57:15     8710            118   \n",
       "19  41980848     15:58:51       15:58:51     8730            124   \n",
       "20  41980848     15:59:59       15:59:59     8740            129   \n",
       "21  41980848     16:02:00       16:02:00     8760            138   \n",
       "22  41980848     16:03:31       16:03:31     8780            145   \n",
       "23  41980848     16:04:25       16:04:25     8790            149   \n",
       "24  41980848     16:06:00       16:06:00     8810            158   \n",
       "25  41980848     16:07:18       16:07:18     8820            163   \n",
       "26  41980848     16:08:37       16:08:37     8840            171   \n",
       "27  41980848     16:09:37       16:09:37     8850            178   \n",
       "28  41980848     16:11:00       16:11:00     8870            187   \n",
       "29  41980848     16:12:39       16:12:39     8890            195   \n",
       "30  41980848     16:13:57       16:13:57     8905            199   \n",
       "31  41980848     16:15:04       16:15:04     8920            205   \n",
       "32  41980848     16:16:25       16:16:25     8940            215   \n",
       "33  41980848     16:18:16       16:18:16     8970            223   \n",
       "34  41980848     16:19:27       16:19:27     8990            226   \n",
       "35  41980848     16:21:00       16:21:00    31137            231   \n",
       "\n",
       "   stop_headsign  pickup_type  drop_off_type  shape_dist_traveled  \\\n",
       "0            NaN            0              0                  0.0   \n",
       "1            NaN            0              0               2405.8   \n",
       "2            NaN            0              0               3493.3   \n",
       "3            NaN            0              0               4890.6   \n",
       "4            NaN            0              0               5826.1   \n",
       "5            NaN            0              0               6881.3   \n",
       "6            NaN            0              0               7709.5   \n",
       "7            NaN            0              0               8564.5   \n",
       "8            NaN            0              0               9188.2   \n",
       "9            NaN            0              0              10489.8   \n",
       "10           NaN            0              0              11436.8   \n",
       "11           NaN            0              0              13385.9   \n",
       "12           NaN            0              0              15314.3   \n",
       "13           NaN            0              0              15931.5   \n",
       "14           NaN            0              0              17486.8   \n",
       "15           NaN            0              0              18697.2   \n",
       "16           NaN            0              0              20322.1   \n",
       "17           NaN            0              0              20774.8   \n",
       "18           NaN            0              0              22020.6   \n",
       "19           NaN            0              0              23234.6   \n",
       "20           NaN            0              0              24089.1   \n",
       "21           NaN            0              0              25621.2   \n",
       "22           NaN            0              0              26898.8   \n",
       "23           NaN            0              0              27662.5   \n",
       "24           NaN            0              0              28998.1   \n",
       "25           NaN            0              0              30091.2   \n",
       "26           NaN            0              0              31211.9   \n",
       "27           NaN            0              0              32047.0   \n",
       "28           NaN            0              0              33219.2   \n",
       "29           NaN            0              0              34688.4   \n",
       "30           NaN            0              0              35851.5   \n",
       "31           NaN            0              0              36847.6   \n",
       "32           NaN            0              0              38051.1   \n",
       "33           NaN            0              0              39708.3   \n",
       "34           NaN            0              0              40757.2   \n",
       "35           NaN            0              0              42149.0   \n",
       "\n",
       "                                          stop_name  \n",
       "0                             Virginia St & 6th Ave  \n",
       "1                                 3rd Ave & Pine St  \n",
       "2                                3rd Ave & Union St  \n",
       "3                               3rd Ave & Marion St  \n",
       "4                                3rd Ave & James St  \n",
       "5                             3rd Ave S & S Main St  \n",
       "6                          S Jackson St & 5th Ave S  \n",
       "7                      S Jackson St & Maynard Ave S  \n",
       "8                          S Jackson St & 8th Ave S  \n",
       "9                         S Jackson St & 12th Ave S  \n",
       "10                        Rainier Ave S & S King St  \n",
       "11                     Rainier Ave S & S Charles St  \n",
       "12                             Rainier Ave S & I-90  \n",
       "13                       Rainier Ave S & S State St  \n",
       "14                      Rainier Ave S & S Walker St  \n",
       "15                     Rainier Ave S & S Bayview St  \n",
       "16              Rainier Ave S & S Forest St - Bay 4  \n",
       "17  Rainier Ave S & Martin Luther King Junior Way S  \n",
       "18                      Rainier Ave S & S Walden St  \n",
       "19                    Rainier Ave S & Letitia Ave S  \n",
       "20                     Rainier Ave S & S Andover St  \n",
       "21                     Rainier Ave S & S Genesee St  \n",
       "22                      Rainier Ave S & S Alaska St  \n",
       "23                     Rainier Ave S & S Edmunds St  \n",
       "24                      Rainier Ave S & S Dawson St  \n",
       "25                     Rainier Ave S & S Brandon St  \n",
       "26                       Rainier Ave S & S Orcas St  \n",
       "27                       Rainier Ave S & S Kenny St  \n",
       "28                      Rainier Ave S & S Graham St  \n",
       "29                       Rainier Ave S & S Holly St  \n",
       "30                   Rainier Ave S & S Frontenac St  \n",
       "31                     Rainier Ave S & S Othello St  \n",
       "32                      Rainier Ave S & S Holden St  \n",
       "33                        Rainier Ave S & S Rose St  \n",
       "34                  Rainier Ave S & S Cloverdale St  \n",
       "35                   S Henderson St & Rainier Ave S  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a_bus_trip_stop_times.merge(stop_names, on='stop_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Voila! There we have it! The schedule for trip 41980848 of bus line 7! "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we have the stop_ids, which means we have their GPS locations, it would be fun to take the above information, and to show case it in a map form, including the times! That would be for another time!"
   ]
  }
 ],
 "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.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}