GTFS_Onboarding.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# GTFS Onboarding Exercise"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n",
    "The [MBTA’s GTFS Feed](https://github.com/mbta/gtfs-documentation/blob/master/reference/gtfs.md) consists of a series of [text files in a standard format](https://developers.google.com/transit/gtfs/reference) that represent the system’s static schedule (along with other geographic information). This data ultimately underlies the schedule data available through the [MBTA's V3 API](https://www.mbta.com/developers/v3-api), and is referenced by data included in our [GTFS-realtime](https://www.mbta.com/developers/gtfs-realtime) feeds (not covered in this guide).\n",
    "\n",
    "Throughout this guide, you will gain familiarity with the data tables included in the feed, organized by their corresponding API resources. For the purposes of this guide, we will be using the GTFS Feed active on [October 14, 2020](https://cdn.mbta.com/archive/archived_feeds.txt).\n",
    "\n",
    "In the following sections, you will cover the these concepts:\n",
    "* [Services](#Services): A set of dates on which trips run for one or more routes\n",
    "* [Routes](#Routes): Data about individual routes\n",
    "* [Route Patterns](#Route-Patterns): Subsets of a route, representing different possible patterns of where trips may serve\n",
    "* [Trips](#Trips): The journey of a particular vehicle through a set of stops\n",
    "* [Schedules](#Schedules): When a vehicle should arrive and depart a given stop for a given trip\n",
    "* [Stops](#Stops): Details for a specific stop\n",
    "\n",
    "At the end of each section, there will be a question to test your understanding. If you'd like to see an example solution, click the ellipsis under \"Reveal Solution\"\n",
    "\n",
    "![](img/reveal.png)\n",
    "\n",
    "To open a table of contents, click this button in the sidebar.\n",
    "\n",
    "![](img/toc.png)\n",
    "\n",
    "### Resources\n",
    "* [GTFS Documentation](https://github.com/google/transit/blob/master/gtfs/spec/en/reference.md)\n",
    "* [MBTA GTFS Documentation](https://github.com/mbta/gtfs-documentation/blob/master/reference/gtfs.md)\n",
    "* [MBTA V3 API Documentation](https://api-v3.mbta.com/docs/swagger/index.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Getting Oriented"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Jupyter Notebook\n",
    "You can simply follow along with this guide and run the sample code by selecting code cells and pressing the \"Run\" button in the toolbar or pressing `Shift + Return`. To learn more about the Jupyter Notebook and its ecoystem, see its [documentation](https://jupyter.org/documentation)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL\n",
    "A SQLite database with tables corresponding to each table in the [MBTA GTFS Documenation](https://github.com/mbta/gtfs-documentation/blob/master/reference/gtfs.md) has been loaded for your convenience. Data exploration will be performed in SQL throughout this guide. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Select this cell and press Shift + Return\n",
    "\n",
    "%load_ext sql\n",
    "%sql sqlite:///feed.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can now execute SQL queries by adding `%%sql` to the top of code cells."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Select this cell and press Shift + Return\n",
    "\n",
    "%sql SELECT * FROM feed_info;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note**: While SQL is convenient to use for exploring tabular data like the GTFS Feed, it's important to note that it is not required. In fact, SQL is not _actually_ used anywhere for creating or querying our GTFS feed in our various systems. You should feel free to learn the underlying concepts and explore the data using any tool with which you feel comfortable."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Python\n",
    "Additionally, you can execute arbitrary Python code if you would rather explore the files that way. The `pandas` library has been imported for your convenience. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Select this cell and press Shift + Return\n",
    "import pandas as pd\n",
    "print(2 + 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Raw Data\n",
    "The raw `.txt` file are available to download or inspect in the `feed` directory. For example, see [/feed/feed_info.txt](feed/feed_info.txt). Feel free to explore these files in Excel, Google Sheets, or your favorite spreadsheet software."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Services\n",
    "*A set of dates on which trips run for one or more routes*\n",
    "\n",
    "**GTFS Tables**: [`calendar`](feed/calendar.txt), [`calendar_dates`](feed/calendar_dates.txt), [`calendar_attributes`](feed/calendar_attributes.txt)\n",
    "\n",
    "**MBTA V3 API Resource**: [`Service`](https://api-v3.mbta.com/docs/swagger/index.html#/Service/ApiWeb_ServiceController_index)\n",
    "\n",
    "When a rider is trying to figure out if and when a particular route is running trips, they probably have to ask themselves a few questions:\n",
    "* What is the day of week?\n",
    "* What's the date?\n",
    "* Is it a holiday?\n",
    "\n",
    "As an example, let's look at the 89 bus using MBTA.com's [Schedule Finder](https://www.mbta.com/schedules/89/line?schedule_direction%5Bdirection_id%5D=0&schedule_direction%5Borigin%5D=place-sull), or the [PDF schedule](https://cdn.mbta.com/sites/default/files/route_pdfs/2020-fall/R089.pdf). On MBTA.com, note the four different schedules to choose from (pictured below):\n",
    "* Weekday Schedule (Fall)\n",
    "* Saturday Schedule (Fall)\n",
    "* Sunday Schedule (Fall)\n",
    "* Thanksgiving Day, Nov 26\n",
    "\n",
    "![](img/services.png)\n",
    "\n",
    "As you can see in the Schedule Finder and PDF schedules, the major difference between these services is in the number and frequency of scheduled trips, though in some cases the pattern, or combination of stops, can also vary. If you've ever heard someone refer to the \"Fall weekday schedule\" or a \"Holiday schedule,\" they are referring to this concept of a service.\n",
    "\n",
    "So how do we know on which dates these services are active and running trips, and how is this represented in our GTFS data? Through a combination of the data in the `calendar`, `calendar_attributes`, and `calendar_dates` tables."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### calendar\n",
    "First, let's take a look at the first few rows of the `calendar` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM calendar\n",
    "LIMIT 3;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `service_id` is just a unique identifier for a service.\n",
    "\n",
    "For each column representing a day of week (`monday`, `tuesday`, etc.), a value of `1` indicates that the service **does** run trips on that day of week, and a value of `0` indicates that the service **does not** run trips on that day of week.\n",
    "\n",
    "The `start_date` and `end_date` fields indicate the date range for which the service is active. \n",
    "\n",
    "So looking at service `BUS420-10-Wdy-02`, we can say it runs trips on weekdays only from October 2, 2020 to December 18, 2020.\n",
    "\n",
    "So if we wanted to find services running trips only on weekdays, we could run this query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM calendar\n",
    "WHERE monday = 1\n",
    "AND tuesday = 1\n",
    "AND wednesday = 1\n",
    "AND thursday = 1\n",
    "AND friday = 1\n",
    "AND saturday = 0\n",
    "AND sunday = 0\n",
    "ORDER BY service_id\n",
    "LIMIT 3;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And if we want to find services ending before December 1, 2020, we could run this query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM calendar\n",
    "WHERE end_date < \"20201201\"\n",
    "ORDER BY service_id\n",
    "LIMIT 3;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### calendar_attributes\n",
    "We include additional data about services in the `calendar_attributes` table. Let's take a look at the first few rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM calendar_attributes\n",
    "LIMIT 3;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, this table includes a `service_id` column, which corresponds to a row in the `calendar` table, so we can combine these two tables to view human readable names and descriptions for each service."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT calendar.service_id, calendar_attributes.service_schedule_name, calendar_attributes.service_description\n",
    "FROM calendar\n",
    "JOIN calendar_attributes on calendar.service_id = calendar_attributes.service_id\n",
    "LIMIT 3;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### calendar_dates\n",
    "Rows in the `calendar_dates` table define exceptions to the default service patterns defined in the `calendar` table. Let's take a look at the first few rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM calendar_dates\n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Like `calendar_attributes`, the `service_id` in `calendar_dates` corresponds to a row in the `calendar` table. \n",
    "\n",
    "An `exception_type` of `1` indicates that we have added the service and its trips on the specified `date`.\n",
    "\n",
    "An `exception_type` of `2` indicates that we have removed the service and its trips on the specified `date`.\n",
    "\n",
    "As seen above, on October 16, 2020, we added service `RTL420-5-Wdy-01-OrgSkpNrhStnNrd` and removed service `RTL420-5-Wdy-01`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test Your Knowledge\n",
    "What is the `service_id`, `service_description`, and `end_date` for the Saturday service which has the earliest end date?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "/* Your code here */"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reveal solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT c.service_id, ca.service_description, c.end_date\n",
    "FROM calendar as c\n",
    "JOIN calendar_attributes as ca\n",
    "ON c.service_id = ca.service_id\n",
    "WHERE monday = 0\n",
    "AND tuesday = 0\n",
    "AND wednesday = 0\n",
    "AND thursday = 0\n",
    "AND friday = 0\n",
    "AND saturday = 1\n",
    "AND sunday = 0\n",
    "ORDER BY end_date\n",
    "LIMIT 1;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Which service has the most days added to the feed as exceptions?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "/* Your code here */"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reveal solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT service_id, COUNT(*) as added_days\n",
    "FROM calendar_dates\n",
    "WHERE exception_type = 1\n",
    "GROUP BY service_id\n",
    "ORDER BY added_days DESC\n",
    "LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So now that we can identify which services are running trips on a given date, how can we identify *which* trips for *which* routes are running?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Routes\n",
    "*Data about individual routes*\n",
    "\n",
    "**GTFS Table:** [routes](feed/routes.txt)\n",
    "\n",
    "**MBTA V3 API Resource**: [Route](https://api-v3.mbta.com/docs/swagger/index.html#/Route/ApiWeb_RouteController_show)\n",
    "\n",
    "First, let's take a look at the first few rows of the `routes` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM routes\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`route_id` is a unique identifier for individual routes, which is referenced in other tables, much like `service_id` is referenced in `calendar_attributes` and `calendar_dates`.\n",
    "\n",
    "`route_desc` categorizes a route's level of service. Possible values for the MBTA implementation include:\n",
    "* `Commuter Rail`\n",
    "* `Rapid Transit`\n",
    "* `Local Bus`\n",
    "* `Key Bus`\n",
    "* `Supplemental Bus`\n",
    "* `Community Bus`\n",
    "* `Commuter Bus`\n",
    "* `Ferry`\n",
    "* `Rail Replacement Bus`\n",
    "\n",
    "Let's look at the entry for the 89 bus."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM routes\n",
    "WHERE route_id = \"89\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test Your Knowledge\n",
    "How many routes are categorized as `Local Bus`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql \n",
    "\n",
    "/* Your code here */"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reveal solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*)\n",
    "FROM routes\n",
    "WHERE route_desc = \"Local Bus\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each individual route has one entry in the `routes` table. But you may be wondering, how do we represent the different patterns a route may take? We know a trip on a route might vary by a direction (e.g. Inbound vs Outbound) or in the pattern of stops the trip might take, like the 89 bus.\n",
    "\n",
    "![](img/rp.png)\n",
    "\n",
    "How do we know which patterns exist for a given route, and which pattern a given trip follows? The simplest way is to identify a given trip's `route_pattern`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Route Patterns\n",
    "*Subsets of a route, representing different possible patterns of where trips may serve*\n",
    "\n",
    "**GTFS Tables**: [route_patterns](feed/route_patterns.txt), [directions](feed/directions.txt)\n",
    "\n",
    "**MBTA V3 API Resource**: [RoutePattern](https://api-v3.mbta.com/docs/swagger/index.html#/RoutePattern/ApiWeb_RoutePatternController_show)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### route_patterns\n",
    "Let's take a look at the `route_patterns` for the 89 bus."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * \n",
    "FROM route_patterns\n",
    "WHERE route_id = \"89\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, you can see four distinct patterns for the Orange Line:\n",
    "* `89-2-0` for the Sullivan Sq to Davis Station pattern\n",
    "* `89-_-0` for the Sullivan Sq to Clarendon Hill pattern\n",
    "* `89-2-1` for the Davis Square Station to Sullivan Sq pattern\n",
    "* `89-_-1` for the Clarendon Hill to Sullivan Sq pattern\n",
    "\n",
    "You can also see that each pattern has a `direction_id`, either 0 or 1, representing the direction of traveled on a trip. While you could infer trip's the final destination given the `route_pattern_name`, (or a given trip's `trip_headsign`), a pattern's `direction_id` refers to an additional passenger-facing name documented in the `directions` table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### directions\n",
    "\n",
    "Let's take a look at the directions for the 89 bus."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT *\n",
    "FROM directions\n",
    "WHERE route_id = \"89\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we can see that the `89-2-0` and `89-_-0` patterns could also be refered to as \"Outbound\" and the `89-2-1` and `89-_-1` patterns could also be refered to as \"Inbound.\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "With the combination of `service_id`, `route_id`, `route_pattern_id`, and `direction_id`, we can tell which routes are running which trips on a given date."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test Your Knowledge"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Which routes have exactly two patterns?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "/* Your code here */"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reveal solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT route_id, COUNT(*) as n_patterns\n",
    "FROM route_patterns\n",
    "GROUP BY route_id\n",
    "HAVING COUNT(*) = 2;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Trips\n",
    "*The journey of a particular vehicle through a set of stops*\n",
    "\n",
    "**GTFS Table**: [trips](feed/trips.txt)\n",
    "\n",
    "**MBTA V3 API Resource**: [`Trip`](https://api-v3.mbta.com/docs/swagger/index.html#/Trip/ApiWeb_TripController_show)\n",
    "\n",
    "The `trips` table contains data for each individual trip in a GTFS feed. Let's look at the first few rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM trips\n",
    "LIMIT 3;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once again, we see a `service_id` column corresponding to a row in the `calendar` table.\n",
    "\n",
    "Additionally, we see:\n",
    "* `trip_id`: a unique identifier for the trip\n",
    "* `route_id`: a unique identifier for a route in the `routes` table, which contains data about each route\n",
    "* `route_pattern_id`: a unique identifier for a pattern in the `route_patterns` table, which contains data about each combination of stops a route may take\n",
    "\n",
    "You may also notice there is no date field. That is because in GTFS, a `trip` is not an individual trip as a rider would perceive it (i.e. “the Inbound 89 trip that occurs at 8:39am on October 27th, 2020”), but a trip that occurs once on every date defined for a given service. \n",
    "\n",
    "Let's take a look at all of the services which include trips by the 89 bus."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT service_id\n",
    "FROM trips\n",
    "WHERE route_id = \"89\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As a reminder, these services can include many trips from many different routes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*)\n",
    "FROM (\n",
    "    SELECT DISTINCT route_id\n",
    "    FROM trips\n",
    "    WHERE service_id == \"FallWeekday\"\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's take a look at all of the trips for the 89 bus on the `FallWeekday` service."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM trips\n",
    "WHERE service_id == \"FallWeekday\" AND route_id = \"89\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, let's look at only trips on the Sullivan Sq to Davis Square pattern (recall this is `89-2-0` as seen above)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM trips\n",
    "WHERE service_id == \"FallWeekday\" AND route_pattern_id = \"89-2-0\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test Your Knowledge\n",
    "For each service running 89 trips, how many trips are on the Sullivan Sq to Clarendon Hill pattern?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "/* Your code here */"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reveal solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT service_id, COUNT(*) as n_trips\n",
    "FROM TRIPS\n",
    "WHERE route_pattern_id = \"89-_-0\"\n",
    "GROUP BY service_id;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Schedules\n",
    "*When a vehicle should arrive and depart a given stop for a given trip*\n",
    "\n",
    "**GTFS Table**: [`stop_times`](feed/stop_times.txt)\n",
    "\n",
    "**MBTA V3 API Resource**: [`Schedule`](https://api-v3.mbta.com/docs/swagger/index.html#/Schedule/ApiWeb_ScheduleController_index)\n",
    "\n",
    "The `stop_times` table includes a row for every scheduled stop on every scheduled trip in a GTFS Feed. If that sounds like a lot of rows, it is! This is by far the largest table in the feed. Here, we can see there are 2,019,434 `stop_times` in this version of the feed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*)\n",
    "FROM stop_times;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at the first few rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM stop_times\n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each `trip_id` corresponds to a row in the `trips` table, and there should be one row for every stop that trip makes.\n",
    "\n",
    "The `arrival_time` and `departure_time` indicate when a vehicle should arrive and depart the stop identified by `stop_id` (more on stops later).\n",
    "\n",
    "The `stop_sequence` field indicates the order of stops for a particular trip. The values must increase along the trip but do not need to be consecutive.\n",
    "\n",
    "So if we have a given `trip_id`, we can see all of the stops in order for that trip."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM stop_times\n",
    "WHERE trip_id = \"45295219\"\n",
    "ORDER BY stop_sequence;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data about the physical stops referenced by `stop_id` can be found in the `stops` table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test Your Knowledge\n",
    "At what time should the trip `45692465` arrive at its final stop?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "/* Your code here */"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reveal solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT arrival_time\n",
    "FROM stop_times\n",
    "WHERE trip_id = \"45692465\"\n",
    "AND stop_sequence = (\n",
    "    SELECT MAX(stop_sequence)\n",
    "    FROM stop_times\n",
    "    WHERE trip_id = \"45692465\"\n",
    "    GROUP BY trip_id\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Stops\n",
    "*Details for a specific stop*\n",
    "\n",
    "**GTFS Table**: [`stops`](feed/stops.txt)\n",
    "\n",
    "**MBTA V3 API Resource**: [`Stop`](https://api-v3.mbta.com/docs/swagger/index.html#/Stop/ApiWeb_StopController_show)\n",
    "\n",
    "Let's take a look at the first few rows of the `stops` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM stops\n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This information can be joined with the `stop_times` table to construct a readable schedule for a given trip."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT st.trip_id as trip_id, \n",
    "    st.arrival_time as arrival_time, \n",
    "    st.departure_time as departure_time, \n",
    "    st.stop_id as stop_id,\n",
    "    st.stop_sequence as stop_sequence,\n",
    "    s.stop_name as stop_name\n",
    "FROM stop_times as st\n",
    "JOIN stops as s \n",
    "ON st.stop_id = s.stop_id\n",
    "WHERE trip_id = \"45771950\"\n",
    "ORDER BY stop_sequence;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test Your Knowledge\n",
    "Display the following fields for the last trip of the day for the 89 bus on the Sullivan Sq to Clarendon Hill pattern on Fall weekdays:\n",
    "* `stop_id`\n",
    "* `stop_name`\n",
    "* `arrival_time`\n",
    "* `departure_time`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "/* Your code here */"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reveal solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT st.stop_id as stop_id, s.stop_name as stop_name, MAX(st.arrival_time) as arrival_time, MAX(st.departure_time) as departure_time\n",
    "FROM stop_times as st\n",
    "JOIN stops as s\n",
    "ON st.stop_id == s.stop_id\n",
    "JOIN trips as t\n",
    "ON st.trip_id == t.trip_id\n",
    "WHERE t.route_id == \"89\"\n",
    "AND t.service_id == \"FallWeekday\"\n",
    "AND t.route_pattern_id == \"89-_-1\"\n",
    "GROUP BY st.stop_sequence, s.stop_name\n",
    "ORDER BY stop_sequence"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What's next?\n",
    "Open up a [new notebook](GTFS_Exploration.ipynb) and explore the current feed!"
   ]
  }
 ],
 "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}