README.md

Finding Trips that visit the same stop twice

This notebook provides an example from GTFS data of GTFS Trips which visit a stop twice. At the very bottom of the notebook is a table which has the output for MBTA's GTFS at an earlier time.

GTFS_Multi_Visited_Stops.sqlite3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQLite Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\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": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {},
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    ".open feed.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can now execute SQL queries on the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/csv": [
       "MBTA,http://www.mbta.com,EN,20240127,20240406,\"Winter 2024, 2024-02-03T02:07:36+00:00, version D\",developer@mbta.com,mbta-ma-us\n"
      ],
      "text/html": [
       "<table>\n",
       "<thead><tr><th scope=\"col\">feed_publisher_name</th><th scope=\"col\">feed_publisher_url</th><th scope=\"col\">feed_lang</th><th scope=\"col\">feed_start_date</th><th scope=\"col\">feed_end_date</th><th scope=\"col\">feed_version</th><th scope=\"col\">feed_contact_email</th><th scope=\"col\">feed_id</th></tr></thead>\n",
       "<tbody>\n",
       "<tr><td>MBTA</td><td>http://www.mbta.com</td><td>EN</td><td>20240127</td><td>20240406</td><td>Winter 2024, 2024-02-03T02:07:36+00:00, version D</td><td>developer@mbta.com</td><td>mbta-ma-us</td></tr>\n",
       "</tbody></table>\n"
      ],
      "text/plain": [
       "MBTA|http://www.mbta.com|EN|20240127|20240406|Winter 2024, 2024-02-03T02:07:36+00:00, version D|developer@mbta.com|mbta-ma-us"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "-- Select this cell and press Shift + Return\n",
    "\n",
    "SELECT *\n",
    "FROM\n",
    "\tfeed_info;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Finding stops visited twice"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finding stops visited twice that are visited twice on a trip is defined by finding trips in which a `stop_id` appears twice in the `stop_times`.\n",
    "This can be done by finding the groups of `(stop_id, trip_id)` for each `route_patterns.representative_trip_id`, where the size of the group is larger than `1`, meaning that the `stop_id` was visited `COUNT` times."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/csv": [
       "838,Lagrange St @ Vermont St,2,\"4,13\",37,Avenue Louis Pasteur via Forest Hills,37-3-1,370132-1,60451996_1\n",
       "5881,Bennington St @ Antrim St,2,\"18,35\",120,Orient Heights via Wood Island,120-2-0,1200148,60467724\n",
       "3125,North Quincy,2,\"48,54\",217,Quincy Center via North Quincy,217-1-0,2170118,60487527\n",
       "4023,Granite St @ Davis Rd,2,\"33,36\",238,Holbrook/Randolph,238-9-0,2380227,60488003\n",
       "4023,Granite St @ Davis Rd,2,\"33,36\",238,Crawford Square,238-7-0,2380230,60488004\n",
       "4023,Granite St @ Davis Rd,2,\"33,36\",238,Avon Square,238-3-0,2380231,60488034\n"
      ],
      "text/html": [
       "<table>\n",
       "<thead><tr><th scope=\"col\">stop_id</th><th scope=\"col\">stop_name</th><th scope=\"col\">total_stops</th><th scope=\"col\">stop_sequences</th><th scope=\"col\">route_id</th><th scope=\"col\">trip_headsign</th><th scope=\"col\">route_pattern_id</th><th scope=\"col\">shape_id</th><th scope=\"col\">representative_trip_id</th></tr></thead>\n",
       "<tbody>\n",
       "<tr><td>838</td><td>Lagrange St @ Vermont St</td><td>2</td><td>4,13</td><td>37</td><td>Avenue Louis Pasteur via Forest Hills</td><td>37-3-1</td><td>370132-1</td><td>60451996_1</td></tr>\n",
       "<tr><td>5881</td><td>Bennington St @ Antrim St</td><td>2</td><td>18,35</td><td>120</td><td>Orient Heights via Wood Island</td><td>120-2-0</td><td>1200148</td><td>60467724</td></tr>\n",
       "<tr><td>3125</td><td>North Quincy</td><td>2</td><td>48,54</td><td>217</td><td>Quincy Center via North Quincy</td><td>217-1-0</td><td>2170118</td><td>60487527</td></tr>\n",
       "<tr><td>4023</td><td>Granite St @ Davis Rd</td><td>2</td><td>33,36</td><td>238</td><td>Holbrook/Randolph</td><td>238-9-0</td><td>2380227</td><td>60488003</td></tr>\n",
       "<tr><td>4023</td><td>Granite St @ Davis Rd</td><td>2</td><td>33,36</td><td>238</td><td>Crawford Square</td><td>238-7-0</td><td>2380230</td><td>60488004</td></tr>\n",
       "<tr><td>4023</td><td>Granite St @ Davis Rd</td><td>2</td><td>33,36</td><td>238</td><td>Avon Square</td><td>238-3-0</td><td>2380231</td><td>60488034</td></tr>\n",
       "</tbody></table>\n"
      ],
      "text/plain": [
       "838|Lagrange St @ Vermont St|2|4,13|37|Avenue Louis Pasteur via Forest Hills|37-3-1|370132-1|60451996_1\n",
       "5881|Bennington St @ Antrim St|2|18,35|120|Orient Heights via Wood Island|120-2-0|1200148|60467724\n",
       "3125|North Quincy|2|48,54|217|Quincy Center via North Quincy|217-1-0|2170118|60487527\n",
       "4023|Granite St @ Davis Rd|2|33,36|238|Holbrook/Randolph|238-9-0|2380227|60488003\n",
       "4023|Granite St @ Davis Rd|2|33,36|238|Crawford Square|238-7-0|2380230|60488004\n",
       "4023|Granite St @ Davis Rd|2|33,36|238|Avon Square|238-3-0|2380231|60488034"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT\n",
    "\tstop_time.stop_id,\n",
    "\n",
    "\tstop.stop_name,\n",
    "\n",
    "\tCOUNT(stop_time.stop_id)\n",
    "\t\tAS total_stops,\n",
    "\n",
    "\tgroup_concat(stop_time.stop_sequence)\n",
    "\t\tAS stop_sequences,\n",
    "\n",
    "\ttrip.route_id,\n",
    "\ttrip.trip_headsign,\n",
    "\ttrip.route_pattern_id,\n",
    "\ttrip.shape_id,\n",
    "\ttrip.trip_id\n",
    "\t\tAS representative_trip_id\n",
    "\n",
    "FROM\n",
    "\tstop_times\n",
    "\t\tAS stop_time\n",
    "JOIN\n",
    "\tstops\n",
    "\t\tAS stop\n",
    "\tON\n",
    "\t\tstop_time.stop_id\n",
    "\t\t\t= stop.stop_id,\n",
    "\n",
    "\ttrips\n",
    "\t\tAS trip\n",
    "\tON\n",
    "\t\ttrip.trip_id = stop_time.trip_id\n",
    "\n",
    "\n",
    "WHERE\n",
    "\tstop_time.trip_id IN (\n",
    "\t\tSELECT\n",
    "\t\t\tpattern.representative_trip_id\n",
    "\t\tFROM\n",
    "\t\t\troute_patterns\n",
    "\t\t\t\tAS pattern\n",
    "\t)\n",
    "\n",
    "GROUP BY\n",
    "\tstop_time.trip_id,\n",
    "\tstop_time.stop_id\n",
    "\n",
    "HAVING\n",
    "\ttotal_stops\n",
    "\t\t> 1\n",
    "\n",
    "LIMIT 10"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "SQLite",
   "language": "sql",
   "name": "default"
  },
  "language_info": {
   "file_extension": ".sql",
   "mimetype": "application/sql",
   "name": "sql",
   "version": "3.43.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}