summaries.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "import datetime\n",
    "\n",
    "import matplotlib\n",
    "matplotlib.use('agg')  # allows notebook to be tested in Travis\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import cartopy.crs as ccrs\n",
    "import cartopy\n",
    "import matplotlib.pyplot as plt\n",
    "import pandana as pdna\n",
    "import time\n",
    "\n",
    "import urbanaccess as ua\n",
    "from urbanaccess.config import settings\n",
    "from urbanaccess.gtfsfeeds import feeds\n",
    "from urbanaccess import gtfsfeeds\n",
    "from urbanaccess.gtfs.gtfsfeeds_dataframe import gtfsfeeds_dfs\n",
    "from urbanaccess.network import ua_network, load_network\n",
    "\n",
    "from tqdm import tqdm\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# required bbox including all of Massachusetts and RI as well as parts of NH, CT, NY\n",
    "bbox = (-73.7207, 41.1198, -69.7876, 43.1161)\n",
    "# path to the downloaded and cleaned gtfs - mbta recap file for fall 2018\n",
    "#   this could also be a folder of gtfs folders (pre merge of multiple gtfs)\n",
    "\n",
    "path_to_gtfs = r\"J:\\Shared drives\\TMD_TSA\\Model\\networks\\Transit\\gtfs\\bnrd\\1_gtfs_r\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Checking GTFS text file header whitespace... Reading files using encoding: utf-8 set in configuration.\n",
      "GTFS text file header whitespace check completed. Took 0.46 seconds\n",
      "--------------------------------\n",
      "Processing GTFS feed: 1_gtfs_r\n",
      "The unique agency id: mbta was generated using the name of the agency in the agency.txt file.\n",
      "Unique agency id operation complete. Took 0.02 seconds\n",
      "Unique GTFS feed id operation complete. Took 0.00 seconds\n",
      "No GTFS feed stops were found to be outside the bounding box coordinates\n",
      "1_gtfs_r GTFS feed stops: coordinates are in northwest hemisphere. Latitude = North (90); Longitude = West (-90).\n",
      "Appended route type to stops\n",
      "Appended route type to stop_times\n",
      "--------------------------------\n",
      "Added descriptive definitions to stops, routes, stop_times, and trips tables\n",
      "Successfully converted ['departure_time'] to seconds past midnight and appended new columns to stop_times. Took 1.07 seconds\n",
      "1 GTFS feed file(s) successfully read as dataframes:\n",
      "     1_gtfs_r\n",
      "     Took 2.74 seconds\n"
     ]
    }
   ],
   "source": [
    "loaded_feeds = ua.gtfs.load.gtfsfeed_to_df(gtfsfeed_path= path_to_gtfs,\n",
    "                                           validation=True,\n",
    "                                           verbose=True,\n",
    "                                           bbox=bbox,\n",
    "                                           remove_stops_outsidebbox=False,\n",
    "                                           append_definitions=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_start_stop_times(stop_times):    \n",
    "    '''for every trip, grab the start time and stop time of the trip\n",
    "    \n",
    "    Parameters\n",
    "    -----------\n",
    "    stop_times : df\n",
    "        gtfs stop_times.txt in df format\n",
    "\n",
    "    Returns\n",
    "    --------\n",
    "        flintstone : df\n",
    "            df with start and stop times per trip\n",
    "\n",
    "    '''\n",
    "    chocula =0 \n",
    "    for trip_id in stop_times['trip_id'].unique():\n",
    "        max_row = stop_times.query('trip_id==@trip_id').query('stop_sequence == stop_sequence.max()')[['trip_id','arrival_time']]\n",
    "        min_row = stop_times.query('trip_id==@trip_id').query('stop_sequence == stop_sequence.min()')[['trip_id','arrival_time']]\n",
    "        r2 = min_row.merge(max_row, how='left', on='trip_id', suffixes = ('_start','_end'))\n",
    "        if chocula == 0:\n",
    "            flintstone = pd.DataFrame(r2)\n",
    "        else:\n",
    "            flintstone=pd.concat([flintstone,r2])\n",
    "        chocula +=1\n",
    "    return(flintstone)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "def assign_tod(start_stop):\n",
    "    '''calculate midpoint of trip, use midpoint to assign TOD\n",
    "    \n",
    "    Parameters\n",
    "    -----------\n",
    "    start_stop : df\n",
    "        df with start and stop times per trip\n",
    "\n",
    "    Returns\n",
    "    --------\n",
    "    start_stop :\n",
    "        df with start time, stop time, midpoint time, and TOD per trip\n",
    "\n",
    "    '''\n",
    "    \n",
    "    start_stop['at_end_dec'] = (\n",
    "        (\n",
    "            (start_stop['arrival_time_end'].str.split(\":\").str[0]).astype('int32')\n",
    "            +\n",
    "            ((start_stop['arrival_time_end'].str.split(\":\").str[1]\n",
    "            ).astype('int32')/60)))\n",
    "    start_stop['at_start_dec'] = (\n",
    "        (\n",
    "            (start_stop['arrival_time_start'].str.split(\":\").str[0]).astype('int32')\n",
    "            +\n",
    "            ((start_stop['arrival_time_start'].str.split(\":\").str[1]\n",
    "            ).astype('int32')/60)))\n",
    "    \n",
    "    start_stop['midpoint'] = start_stop['at_start_dec'] + ((start_stop['at_end_dec']-start_stop['at_start_dec'])/2)\n",
    "    start_stop['tod'] = np.where(start_stop['midpoint'].between(6.50,9.50),'AM', np.where(\n",
    "        start_stop['midpoint'].between(9.50,15.00), 'MD', np.where(\n",
    "            start_stop['midpoint'].between(15.00,19.00),'PM', 'NT' \n",
    "        )\n",
    "            ) \n",
    "        )\n",
    "    \n",
    "    return start_stop\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "start_stop = get_start_stop_times(gtfsfeeds_dfs.stop_times)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "start_stop_tod = assign_tod(start_stop) # smurf"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Summaries Step 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "gtfsfeeds_dfs.trips['route_pattern_id'] = np.where(gtfsfeeds_dfs.trips['route_pattern_id'].isna(),gtfsfeeds_dfs.trips['shape_id'],gtfsfeeds_dfs.trips['route_pattern_id'])\n",
    "gtfsfeeds_dfs.trips['trip_headsign'] = np.where(gtfsfeeds_dfs.trips['trip_headsign'].isna(),gtfsfeeds_dfs.trips['direction_id'],gtfsfeeds_dfs.trips['trip_headsign'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "nobuscr_routes = gtfsfeeds_dfs.routes.query('(route_type not in [2,3])')['route_id']\n",
    "sls = gtfsfeeds_dfs.routes.query('route_short_name in [\"SL1\",\"SL2\",\"SL3\",\"SL5\"]')['route_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Blue-6-0', 'Blue-8-0', 'Blue-6-1', 'Mattapan-_-0', 'Mattapan-_-1',\n",
       "       'Orange-3-0', 'Orange-5-0', 'Orange-3-1', 'Red-1-0', 'Red-3-0',\n",
       "       'Red-1-1', 'Red-3-1', 'Green-B-812-0', 'Green-B-812-1',\n",
       "       'Green-C-832-0', 'Green-C-832-1', 'Green-D-851-0', 'Green-D-855-0',\n",
       "       'Green-D-851-1', 'Green-D-855-1', 'Green-E-885-0', 'Green-E-885-1',\n",
       "       'Boat-F1-1-0', 'Boat-F1-2-0', 'Boat-F1-3-0', 'Boat-F1-6-0',\n",
       "       'Boat-F1-9-0', 'Boat-F1-1-1', 'Boat-F1-2-1', 'Boat-F1-3-1',\n",
       "       'Boat-F1-4-1', 'Boat-F1-6-1', 'Boat-F4-0-0', 'Boat-F4-0-1'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gtfsfeeds_dfs.trips.query('route_id in @selected_routes')['route_pattern_id'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "selected_routes = sls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 34/34 [01:01<00:00,  1.80s/it]\n"
     ]
    }
   ],
   "source": [
    "# REDO\n",
    "patterns = []\n",
    "# flag the first stop in every trip\n",
    "gtfsfeeds_dfs.stop_times['first_stop'] =  0\n",
    "gtfsfeeds_dfs.stop_times.loc[gtfsfeeds_dfs.stop_times.groupby('trip_id').stop_sequence.idxmin(),'first_stop'] = 1\n",
    "\n",
    "#chocula = 0\n",
    "rpid_stop_dict = {}\n",
    "# for each route_pattern_id calculate the most common stop pattern\n",
    "for rpid in tqdm(gtfsfeeds_dfs.trips.query('route_id in @selected_routes')['route_pattern_id'].unique()):\n",
    "    #patterns = []\n",
    "    rpid_trips = gtfsfeeds_dfs.trips.query('route_pattern_id == @rpid')['trip_id'].to_list()\n",
    "    rpid_stops = gtfsfeeds_dfs.stop_times.query('@rpid_trips in trip_id').sort_values('stop_sequence')\n",
    "    \n",
    "    # for every trip, get stop pattern and save into a list of lists\n",
    "    for tid in rpid_stops['trip_id']:\n",
    "        patterns.append([tid,', '.join(rpid_stops.query('trip_id == @tid')['stop_id'].to_list())])\n",
    "    \n",
    "# make data frame of every trip and its pattern of stops\n",
    "df = pd.DataFrame(patterns, columns = ['trip_id','stop_pattern'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df.merge(\n",
    "    gtfsfeeds_dfs.trips, \n",
    "    on='trip_id')[['trip_id','route_id', 'route_pattern_id', 'trip_short_name','trip_headsign','service_id', 'stop_pattern']]\n",
    "df3 = df2.merge(start_stop_tod, on='trip_id')\n",
    "df4 = df3.groupby(by=['route_pattern_id','stop_pattern', 'trip_id','tod']).first()\n",
    "\n",
    "#df4.to_csv(r\"C:\\Users\\matkinson.AD\\Downloads\\trip_id_to_stop_pattern_filtered_w_tod.csv\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Summaries Step 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#gtfsfeeds_dfs.stops.query('stop_id in ')\n",
    "df4['stop_names_pattern'] = pd.NA\n",
    "df4 = df4.reset_index()\n",
    "\n",
    "stops = {}\n",
    "for a in df4.stop_pattern:\n",
    "    x = a.strip('').split(', ')\n",
    "    q = []\n",
    "    for y in x:\n",
    "        z = gtfsfeeds_dfs.stops.query('stop_id == @y')['stop_name'].to_list()[0]\n",
    "        q.append(z)\n",
    "        stops[y] = z\n",
    "    filt = (df4['stop_pattern'] == a)\n",
    "    df4.loc[filt,'stop_names_pattern'] = str(q)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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_pattern_id</th>\n",
       "      <th>stop_pattern</th>\n",
       "      <th>stop_names_pattern</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Blue-6-0</td>\n",
       "      <td>70059, 70057, 70055, 70053, 70051, 70049, 7004...</td>\n",
       "      <td>{Orient Heights, Bowdoin, Wood Island, Wonderl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Blue-6-0</td>\n",
       "      <td>70059, 70057, 70055, 70053, 70051, 70049, 7004...</td>\n",
       "      <td>{Orient Heights, Bowdoin, Wood Island, Wonderl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Blue-6-0</td>\n",
       "      <td>70059, 70057, 70055, 70053, 70051, 70049, 7004...</td>\n",
       "      <td>{Orient Heights, Bowdoin, Wood Island, Wonderl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Blue-6-0</td>\n",
       "      <td>70059, 70057, 70055, 70053, 70051, 70049, 7004...</td>\n",
       "      <td>{Orient Heights, Bowdoin, Wood Island, Wonderl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Blue-6-0</td>\n",
       "      <td>70059, 70057, 70055, 70053, 70051, 70049, 7004...</td>\n",
       "      <td>{Orient Heights, Bowdoin, Wood Island, Wonderl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2713</th>\n",
       "      <td>Red-3-1</td>\n",
       "      <td>70105, 70104, 70102, 70100, 70098, 70096, 7008...</td>\n",
       "      <td>{Park Street, JFK/UMass, Andrew, Harvard, Davi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2714</th>\n",
       "      <td>Red-3-1</td>\n",
       "      <td>70105, 70104, 70102, 70100, 70098, 70096, 7008...</td>\n",
       "      <td>{Park Street, JFK/UMass, Andrew, Harvard, Davi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2715</th>\n",
       "      <td>Red-3-1</td>\n",
       "      <td>70105, 70104, 70102, 70100, 70098, 70096, 7008...</td>\n",
       "      <td>{Park Street, JFK/UMass, Andrew, Harvard, Davi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2716</th>\n",
       "      <td>Red-3-1</td>\n",
       "      <td>70105, 70104, 70102, 70100, 70098, 70096, 7008...</td>\n",
       "      <td>{Park Street, JFK/UMass, Andrew, Harvard, Davi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2717</th>\n",
       "      <td>Red-3-1</td>\n",
       "      <td>70105, 70104, 70102, 70100, 70098, 70096, 7008...</td>\n",
       "      <td>{Park Street, JFK/UMass, Andrew, Harvard, Davi...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2718 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     route_pattern_id                                       stop_pattern  \\\n",
       "0            Blue-6-0  70059, 70057, 70055, 70053, 70051, 70049, 7004...   \n",
       "1            Blue-6-0  70059, 70057, 70055, 70053, 70051, 70049, 7004...   \n",
       "2            Blue-6-0  70059, 70057, 70055, 70053, 70051, 70049, 7004...   \n",
       "3            Blue-6-0  70059, 70057, 70055, 70053, 70051, 70049, 7004...   \n",
       "4            Blue-6-0  70059, 70057, 70055, 70053, 70051, 70049, 7004...   \n",
       "...               ...                                                ...   \n",
       "2713          Red-3-1  70105, 70104, 70102, 70100, 70098, 70096, 7008...   \n",
       "2714          Red-3-1  70105, 70104, 70102, 70100, 70098, 70096, 7008...   \n",
       "2715          Red-3-1  70105, 70104, 70102, 70100, 70098, 70096, 7008...   \n",
       "2716          Red-3-1  70105, 70104, 70102, 70100, 70098, 70096, 7008...   \n",
       "2717          Red-3-1  70105, 70104, 70102, 70100, 70098, 70096, 7008...   \n",
       "\n",
       "                                     stop_names_pattern  \n",
       "0     {Orient Heights, Bowdoin, Wood Island, Wonderl...  \n",
       "1     {Orient Heights, Bowdoin, Wood Island, Wonderl...  \n",
       "2     {Orient Heights, Bowdoin, Wood Island, Wonderl...  \n",
       "3     {Orient Heights, Bowdoin, Wood Island, Wonderl...  \n",
       "4     {Orient Heights, Bowdoin, Wood Island, Wonderl...  \n",
       "...                                                 ...  \n",
       "2713  {Park Street, JFK/UMass, Andrew, Harvard, Davi...  \n",
       "2714  {Park Street, JFK/UMass, Andrew, Harvard, Davi...  \n",
       "2715  {Park Street, JFK/UMass, Andrew, Harvard, Davi...  \n",
       "2716  {Park Street, JFK/UMass, Andrew, Harvard, Davi...  \n",
       "2717  {Park Street, JFK/UMass, Andrew, Harvard, Davi...  \n",
       "\n",
       "[2718 rows x 3 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4['stop_names_pattern'] = df4['stop_names_pattern'].str.strip(\"[']\").str.split(\"', '\").apply(set)\n",
    "df4[['route_pattern_id','stop_pattern','stop_names_pattern']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "fred = df4 #pd.read_csv(r\"C:\\Users\\matkinson.AD\\Downloads\\trip_id_to_stop_pattern_filtered_w_tod.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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_pattern_id</th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Blue-6-0</td>\n",
       "      <td>{ 70043,  70039,  70838,  70051,  70053,  7004...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Blue-6-1</td>\n",
       "      <td>{ 70044,  70056,  70040,  70046,  70050,  7005...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Blue-8-0</td>\n",
       "      <td>{ 70049, 70051,  70043,  70047,  70039,  70838...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Boat-F1-1-0</td>\n",
       "      <td>{ Boat-Hingham, Boat-Long,  Boat-Hull}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Boat-F1-1-1</td>\n",
       "      <td>{Boat-Hingham,  Boat-Long,  Boat-Hull}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Boat-F1-2-0</td>\n",
       "      <td>{ Boat-Hingham, Boat-Rowes}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Boat-F1-2-1</td>\n",
       "      <td>{ Boat-Rowes, Boat-Hingham}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Boat-F1-3-0</td>\n",
       "      <td>{Boat-Long,  Boat-Hull}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Boat-F1-3-1</td>\n",
       "      <td>{Boat-Hull,  Boat-Long}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Boat-F1-4-1</td>\n",
       "      <td>{Boat-Hingham,  Boat-Long,  Boat-Logan}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Boat-F1-6-0</td>\n",
       "      <td>{ Boat-Hingham, Boat-Long}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Boat-F1-6-1</td>\n",
       "      <td>{ Boat-Long, Boat-Hingham}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Boat-F1-9-0</td>\n",
       "      <td>{Boat-Long,  Boat-Hull,  Boat-Logan}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Boat-F4-0-0</td>\n",
       "      <td>{ Boat-Charlestown, Boat-Long-South}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Boat-F4-0-1</td>\n",
       "      <td>{ Boat-Long-South, Boat-Charlestown}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Green-B-812-0</td>\n",
       "      <td>{ 70147,  70196,  70125,  70135,  70117,  1701...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Green-B-812-1</td>\n",
       "      <td>{ 70126,  70112, 70106,  70128,  70154,  17013...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Green-C-832-0</td>\n",
       "      <td>{ 70227,  70225,  70219,  70157,  70217,  7023...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Green-C-832-1</td>\n",
       "      <td>{70238,  70154,  70232,  70150,  70234,  70216...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Green-D-851-0</td>\n",
       "      <td>{ 70187,  70179, 70206,  70198,  70157,  70161...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Green-D-851-1</td>\n",
       "      <td>{ 70178,  70154,  70150,  70182,  70164,  7020...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Green-D-855-0</td>\n",
       "      <td>{ 70175,  70165,  70187,  70151,  70179,  7017...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Green-D-855-1</td>\n",
       "      <td>{ 70180,  70178,  70186,  70158,  70154,  7015...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Green-E-885-0</td>\n",
       "      <td>{ 70199,  70249,  70251,  70206,  70253,  7024...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Green-E-885-1</td>\n",
       "      <td>{ 70252,  70244,  70154,  70250,  70205,  7020...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Mattapan-_-0</td>\n",
       "      <td>{ 70263,  70273,  70267,  70269,  70265,  7027...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Mattapan-_-1</td>\n",
       "      <td>{ 70268, 70276,  70266,  70270,  70264,  70274...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Orange-3-0</td>\n",
       "      <td>{ 70020,  70014,  70022,  70278,  70016,  7000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Orange-3-1</td>\n",
       "      <td>{ 70011,  70027,  70017,  70003,  70025, 70001...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Orange-5-0</td>\n",
       "      <td>{ 70026,  70020,  70024,  70014,  70022,  7002...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Red-1-0</td>\n",
       "      <td>{ 70081,  70083,  70089,  70091,  70067,  7007...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Red-1-1</td>\n",
       "      <td>{ 70078,  70070, 70094,  70082,  70064,  70066...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Red-3-0</td>\n",
       "      <td>{ 70081,  70083,  70103,  70095,  70101,  7010...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Red-3-1</td>\n",
       "      <td>{ 70078,  70100,  70070, 70105,  70082,  70104...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   route_pattern_id                                                  0\n",
       "0          Blue-6-0  { 70043,  70039,  70838,  70051,  70053,  7004...\n",
       "1          Blue-6-1  { 70044,  70056,  70040,  70046,  70050,  7005...\n",
       "2          Blue-8-0  { 70049, 70051,  70043,  70047,  70039,  70838...\n",
       "3       Boat-F1-1-0             { Boat-Hingham, Boat-Long,  Boat-Hull}\n",
       "4       Boat-F1-1-1             {Boat-Hingham,  Boat-Long,  Boat-Hull}\n",
       "5       Boat-F1-2-0                        { Boat-Hingham, Boat-Rowes}\n",
       "6       Boat-F1-2-1                        { Boat-Rowes, Boat-Hingham}\n",
       "7       Boat-F1-3-0                            {Boat-Long,  Boat-Hull}\n",
       "8       Boat-F1-3-1                            {Boat-Hull,  Boat-Long}\n",
       "9       Boat-F1-4-1            {Boat-Hingham,  Boat-Long,  Boat-Logan}\n",
       "10      Boat-F1-6-0                         { Boat-Hingham, Boat-Long}\n",
       "11      Boat-F1-6-1                         { Boat-Long, Boat-Hingham}\n",
       "12      Boat-F1-9-0               {Boat-Long,  Boat-Hull,  Boat-Logan}\n",
       "13      Boat-F4-0-0               { Boat-Charlestown, Boat-Long-South}\n",
       "14      Boat-F4-0-1               { Boat-Long-South, Boat-Charlestown}\n",
       "15    Green-B-812-0  { 70147,  70196,  70125,  70135,  70117,  1701...\n",
       "16    Green-B-812-1  { 70126,  70112, 70106,  70128,  70154,  17013...\n",
       "17    Green-C-832-0  { 70227,  70225,  70219,  70157,  70217,  7023...\n",
       "18    Green-C-832-1  {70238,  70154,  70232,  70150,  70234,  70216...\n",
       "19    Green-D-851-0  { 70187,  70179, 70206,  70198,  70157,  70161...\n",
       "20    Green-D-851-1  { 70178,  70154,  70150,  70182,  70164,  7020...\n",
       "21    Green-D-855-0  { 70175,  70165,  70187,  70151,  70179,  7017...\n",
       "22    Green-D-855-1  { 70180,  70178,  70186,  70158,  70154,  7015...\n",
       "23    Green-E-885-0  { 70199,  70249,  70251,  70206,  70253,  7024...\n",
       "24    Green-E-885-1  { 70252,  70244,  70154,  70250,  70205,  7020...\n",
       "25     Mattapan-_-0  { 70263,  70273,  70267,  70269,  70265,  7027...\n",
       "26     Mattapan-_-1  { 70268, 70276,  70266,  70270,  70264,  70274...\n",
       "27       Orange-3-0  { 70020,  70014,  70022,  70278,  70016,  7000...\n",
       "28       Orange-3-1  { 70011,  70027,  70017,  70003,  70025, 70001...\n",
       "29       Orange-5-0  { 70026,  70020,  70024,  70014,  70022,  7002...\n",
       "30          Red-1-0  { 70081,  70083,  70089,  70091,  70067,  7007...\n",
       "31          Red-1-1  { 70078,  70070, 70094,  70082,  70064,  70066...\n",
       "32          Red-3-0  { 70081,  70083,  70103,  70095,  70101,  7010...\n",
       "33          Red-3-1  { 70078,  70100,  70070, 70105,  70082,  70104..."
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "george = fred.set_index(['trip_id','route_pattern_id'])['stop_pattern'].str.split(',').apply(set)\n",
    "harry = george.reset_index().groupby('route_pattern_id').apply(lambda x: set.union(*x.stop_pattern)).reset_index()\n",
    "\n",
    "gg = fred.set_index(['trip_id','route_pattern_id'])['stop_names_pattern']\n",
    "hh = gg.reset_index().groupby('route_pattern_id').apply(lambda x: set.union(*x.stop_names_pattern)).reset_index()\n",
    "hh= hh.merge(harry, on='route_pattern_id', suffixes=['_names','_ids'])\n",
    "harry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "stops_dict = {}\n",
    "for rpid in harry['route_pattern_id']:\n",
    "    ron = list(harry.query('route_pattern_id == @rpid')[0].to_list()[0])\n",
    "    ron = [item.strip() for item in ron]\n",
    "\n",
    "    trips_tod = fred.query('route_pattern_id == @rpid')[['trip_id','tod']]\n",
    "\n",
    "    hermy = pd.DataFrame(data = trips_tod, columns = ['trip_id','tod']+ron)\n",
    "    hermy = hermy.set_index('trip_id')\n",
    "\n",
    "    for trip in hermy.index:\n",
    "        for sp in fred.query('trip_id == @trip').set_index('trip_id')['stop_pattern'].str.split(',').reset_index()['stop_pattern']:\n",
    "            #print(sp)\n",
    "            for stop in sp:\n",
    "                stop = stop.strip()\n",
    "                #print(stop)\n",
    "                if stop in hermy.columns:\n",
    "                    hermy.at[trip,stop] = 1\n",
    "\n",
    "    hermy = hermy.reset_index().merge(fred[['trip_id','route_pattern_id','stop_pattern']].query('route_pattern_id == @rpid'), on='trip_id')\n",
    "    stops_dict[rpid] = hermy\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "for key in stops_dict:\n",
    "    a =stops_dict[key].groupby(by=['route_pattern_id','tod','stop_pattern']).count().reset_index()\n",
    "    \n",
    "    b = a.groupby(['stop_pattern','route_pattern_id']).first().drop(['tod','trip_id'], axis=1)\n",
    "    c = b/b\n",
    "    a = a.pivot(index=['route_pattern_id','stop_pattern'], columns=['tod'], values = 'trip_id').reset_index()\n",
    "\n",
    "    tod_list = [x for x in [\"AM\",\"MD\",\"PM\",\"NT\"] if x not in a.columns.to_list()]\n",
    "    if len(tod_list) > 0:\n",
    "        a[tod_list] = 0\n",
    "\n",
    "    subaru = gtfsfeeds_dfs.stop_times.merge(stops_dict[key][['route_pattern_id','trip_id',]], on='trip_id')[['route_pattern_id','stop_id','stop_sequence']]\n",
    "    sub_stops = subaru.groupby(by=['stop_id','stop_sequence']).count().reset_index().groupby(by=['stop_id']).agg({'stop_sequence':'max'}).sort_values(by = 'stop_sequence')\n",
    "\n",
    "    stops_dict[key] = a.merge(c[sub_stops.index.to_list()].reset_index(), on=['route_pattern_id','stop_pattern'])\n",
    "\n",
    "key_dict = {}\n",
    "for key in stops_dict:\n",
    "    key_parts = key.split('-')\n",
    "    if len(key_parts) > 2:\n",
    "        key_parts.pop(2)\n",
    "        partial = '-'.join(key_parts)\n",
    "    else:\n",
    "        partial=key_parts[0]\n",
    "\n",
    "    if partial in key_dict.keys():\n",
    "        key_dict[partial].append(key)\n",
    "    else:\n",
    "        key_dict[partial] = [key]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  route_pattern_id                                       stop_pattern  AM  MD  \\\n",
      "0         Blue-6-0  70059, 70057, 70055, 70053, 70051, 70049, 7004...  36  44   \n",
      "0         Blue-6-1  70038, 70040, 70042, 70044, 70046, 70048, 7005...  36  44   \n",
      "\n",
      "   NT  PM  70059  70057  70055  70053  ...  70042  70044  70046  70048  70050  \\\n",
      "0  53  47    1.0    1.0    1.0    1.0  ...    NaN    NaN    NaN    NaN    NaN   \n",
      "0  52  48    NaN    NaN    NaN    NaN  ...    1.0    1.0    1.0    1.0    1.0   \n",
      "\n",
      "   70052  70054  70056  70058  70060  \n",
      "0    NaN    NaN    NaN    NaN    NaN  \n",
      "0    1.0    1.0    1.0    1.0    1.0  \n",
      "\n",
      "[2 rows x 30 columns]\n",
      "  route_pattern_id                        stop_pattern  MD  NT  PM  AM  \\\n",
      "0      Boat-F1-1-0  Boat-Long, Boat-Hull, Boat-Hingham   2   1   4   0   \n",
      "0      Boat-F1-2-0            Boat-Rowes, Boat-Hingham   1   2   6   4   \n",
      "0      Boat-F1-3-0                Boat-Long, Boat-Hull   0   0   1   2   \n",
      "0      Boat-F1-6-0             Boat-Long, Boat-Hingham   3   1   0   0   \n",
      "0      Boat-F1-9-0    Boat-Long, Boat-Logan, Boat-Hull   0   0   0   2   \n",
      "\n",
      "   Boat-Long  Boat-Hull  Boat-Hingham  Boat-Rowes  Boat-Logan  \n",
      "0        1.0        1.0           1.0         NaN         NaN  \n",
      "0        NaN        NaN           1.0         1.0         NaN  \n",
      "0        1.0        1.0           NaN         NaN         NaN  \n",
      "0        1.0        NaN           1.0         NaN         NaN  \n",
      "0        1.0        1.0           NaN         NaN         1.0  \n",
      "  route_pattern_id                         stop_pattern  AM  MD  NT  PM  \\\n",
      "0      Boat-F1-1-1   Boat-Hingham, Boat-Hull, Boat-Long   1   1   1   0   \n",
      "0      Boat-F1-2-1             Boat-Hingham, Boat-Rowes   5   2   1   5   \n",
      "0      Boat-F1-3-1                 Boat-Hull, Boat-Long   4   0   0   1   \n",
      "0      Boat-F1-4-1  Boat-Hingham, Boat-Logan, Boat-Long   0   1   0   1   \n",
      "0      Boat-F1-6-1              Boat-Hingham, Boat-Long   0   2   2   2   \n",
      "\n",
      "   Boat-Hingham  Boat-Hull  Boat-Long  Boat-Rowes  Boat-Logan  \n",
      "0           1.0        1.0        1.0         NaN         NaN  \n",
      "0           1.0        NaN        NaN         1.0         NaN  \n",
      "0           NaN        1.0        1.0         NaN         NaN  \n",
      "0           1.0        NaN        1.0         NaN         1.0  \n",
      "0           1.0        NaN        1.0         NaN         NaN  \n",
      "  route_pattern_id                                       stop_pattern  AM  MD  \\\n",
      "0    Green-D-851-0  70206, 70204, 70202, 70198, 70159, 70157, 7015...  21  41   \n",
      "0    Green-D-855-0  70504, 70502, 70208, 70206, 70204, 70202, 7019...   0   0   \n",
      "\n",
      "   NT  PM  70206  70204  70202  70198  ...  70173  70171  70169  70167  70165  \\\n",
      "0  47  29    1.0    1.0    1.0    1.0  ...    1.0    1.0    1.0    1.0    1.0   \n",
      "0   1   0    1.0    1.0    1.0    1.0  ...    1.0    1.0    1.0    1.0    1.0   \n",
      "\n",
      "   70163  70161  70504  70502  70208  \n",
      "0    1.0    1.0    NaN    NaN    NaN  \n",
      "0    1.0    1.0    1.0    1.0    1.0  \n",
      "\n",
      "[2 rows x 31 columns]\n",
      "  route_pattern_id                                       stop_pattern  AM  MD  \\\n",
      "0    Green-D-851-1  70160, 70162, 70164, 70166, 70168, 70170, 7017...  23  40   \n",
      "0    Green-D-855-1  70160, 70162, 70164, 70166, 70168, 70170, 7017...   1   0   \n",
      "\n",
      "   NT  PM  70160  70162  70164  70166  ...  70154  70156  70158  70200  70201  \\\n",
      "0  45  30    1.0    1.0    1.0    1.0  ...    1.0    1.0    1.0    1.0    1.0   \n",
      "0   0   0    1.0    1.0    1.0    1.0  ...    1.0    1.0    1.0    1.0    1.0   \n",
      "\n",
      "   70203  70205  70207  70501  70503  \n",
      "0    1.0    1.0    NaN    NaN    NaN  \n",
      "0    1.0    1.0    1.0    1.0    1.0  \n",
      "\n",
      "[2 rows x 31 columns]\n",
      "  route_pattern_id                                       stop_pattern  AM  MD  \\\n",
      "0     Mattapan-_-0  70261, 70263, 70265, 70267, 70269, 70271, 7027...  33  47   \n",
      "0     Mattapan-_-1  70276, 70274, 70272, 70270, 70268, 70266, 7026...  33  48   \n",
      "\n",
      "   NT  PM  70261  70263  70265  70267  ...  70271  70273  70275  70276  70274  \\\n",
      "0  36  47    1.0    1.0    1.0    1.0  ...    1.0    1.0    1.0    NaN    NaN   \n",
      "0  36  46    1.0    NaN    NaN    NaN  ...    NaN    NaN    NaN    1.0    1.0   \n",
      "\n",
      "   70272  70270  70268  70266  70264  \n",
      "0    NaN    NaN    NaN    NaN    NaN  \n",
      "0    1.0    1.0    1.0    1.0    1.0  \n",
      "\n",
      "[2 rows x 21 columns]\n",
      "  route_pattern_id                                       stop_pattern  AM  MD  \\\n",
      "0       Orange-3-0  70036, 70034, 70032, 70278, 70030, 70028, 7002...  27  45   \n",
      "0       Orange-3-1  70001, 70003, 70005, 70007, 70009, 70011, 7001...  27  45   \n",
      "\n",
      "   NT  PM  70036  70034  70032  70278  ...  70019  70021  70023  70025  70027  \\\n",
      "0  46  37    1.0    1.0    1.0    1.0  ...    NaN    NaN    NaN    NaN    NaN   \n",
      "0  48  38    1.0    NaN    NaN    NaN  ...    1.0    1.0    1.0    1.0    1.0   \n",
      "\n",
      "   70029  70031  70279  70033  70035  \n",
      "0    NaN    NaN    NaN    NaN    NaN  \n",
      "0    1.0    1.0    1.0    1.0    1.0  \n",
      "\n",
      "[2 rows x 44 columns]\n",
      "  route_pattern_id                                       stop_pattern  AM  MD  \\\n",
      "0          Red-1-0  70061, 70063, 70065, 70067, 70069, 70071, 7007...  20  27   \n",
      "0          Red-1-1  70094, 70092, 70090, 70088, 70086, 70084, 7008...  21  26   \n",
      "\n",
      "   NT  PM  70061  70063  70065  70067  ...  70082  70080  70078  70076  70074  \\\n",
      "0  35  26    1.0    1.0    1.0    1.0  ...    NaN    NaN    NaN    NaN    NaN   \n",
      "0  34  27    1.0    NaN    NaN    NaN  ...    1.0    1.0    1.0    1.0    1.0   \n",
      "\n",
      "   70072  70070  70068  70066  70064  \n",
      "0    NaN    NaN    NaN    NaN    NaN  \n",
      "0    1.0    1.0    1.0    1.0    1.0  \n",
      "\n",
      "[2 rows x 39 columns]\n",
      "  route_pattern_id                                       stop_pattern  AM  MD  \\\n",
      "0          Red-3-0  70061, 70063, 70065, 70067, 70069, 70071, 7007...  20  30   \n",
      "0          Red-3-1  70105, 70104, 70102, 70100, 70098, 70096, 7008...  22  28   \n",
      "\n",
      "   NT  PM  70061  70063  70065  70067  ...  70082  70080  70078  70076  70074  \\\n",
      "0  36  29    1.0    1.0    1.0    1.0  ...    NaN    NaN    NaN    NaN    NaN   \n",
      "0  35  30    1.0    NaN    NaN    NaN  ...    1.0    1.0    1.0    1.0    1.0   \n",
      "\n",
      "   70072  70070  70068  70066  70064  \n",
      "0    NaN    NaN    NaN    NaN    NaN  \n",
      "0    1.0    1.0    1.0    1.0    1.0  \n",
      "\n",
      "[2 rows x 40 columns]\n"
     ]
    }
   ],
   "source": [
    "final_tables_dict = {}\n",
    "#summarized key in dictionary of keys\n",
    "for key in key_dict:\n",
    "    # if more than one real key associated with the summarized key\n",
    "    if len(key_dict[key]) > 1:\n",
    "        fred1 = []\n",
    "        # for each real key, grab table, and append to list\n",
    "        for x in range(len(key_dict[key])):\n",
    "            heebee = stops_dict[key_dict[key][x]].loc[:,~stops_dict[key_dict[key][x]].columns.duplicated()]\n",
    "            fred1.append(heebee)\n",
    "        \n",
    "        # sort tables by number of columns\n",
    "        \n",
    "        # concat all real key tables per summarized key\n",
    "        gee = pd.concat(fred1, axis = 0)\n",
    "        print(gee)\n",
    "    else:\n",
    "        gee = stops_dict[key_dict[key][0]]\n",
    "    \n",
    "    final_tables_dict[key] = gee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'70059': 'Wonderland',\n",
       " '70057': 'Revere Beach',\n",
       " '70055': 'Beachmont',\n",
       " '70053': 'Suffolk Downs',\n",
       " '70051': 'Orient Heights',\n",
       " '70049': 'Wood Island',\n",
       " '70047': 'Airport',\n",
       " '70045': 'Maverick',\n",
       " '70043': 'Aquarium',\n",
       " '70041': 'State',\n",
       " '70039': 'Government Center',\n",
       " '70838': 'Bowdoin',\n",
       " '70038': 'Bowdoin',\n",
       " '70040': 'Government Center',\n",
       " '70042': 'State',\n",
       " '70044': 'Aquarium',\n",
       " '70046': 'Maverick',\n",
       " '70048': 'Airport',\n",
       " '70050': 'Wood Island',\n",
       " '70052': 'Orient Heights',\n",
       " '70054': 'Suffolk Downs',\n",
       " '70056': 'Beachmont',\n",
       " '70058': 'Revere Beach',\n",
       " '70060': 'Wonderland',\n",
       " 'Boat-Long': 'Long Wharf (North)',\n",
       " 'Boat-Hull': 'Hull',\n",
       " 'Boat-Hingham': 'Hingham',\n",
       " 'Boat-Rowes': 'Rowes Wharf',\n",
       " 'Boat-Logan': 'Logan Airport Ferry Terminal',\n",
       " 'Boat-Long-South': 'Long Wharf (South)',\n",
       " 'Boat-Charlestown': 'Charlestown',\n",
       " '70202': 'Government Center',\n",
       " '70196': 'Park Street',\n",
       " '70159': 'Boylston',\n",
       " '70157': 'Arlington',\n",
       " '70155': 'Copley',\n",
       " '70153': 'Hynes Convention Center',\n",
       " '71151': 'Kenmore',\n",
       " '70149': 'Blandford Street',\n",
       " '70147': 'Boston University East',\n",
       " '70145': 'Boston University Central',\n",
       " '170141': 'Amory Street',\n",
       " '170137': 'Babcock Street',\n",
       " '70135': \"Packard's Corner\",\n",
       " '70131': 'Harvard Avenue',\n",
       " '70129': 'Griggs Street',\n",
       " '70127': 'Allston Street',\n",
       " '70125': 'Warren Street',\n",
       " '70121': 'Washington Street',\n",
       " '70117': 'Sutherland Road',\n",
       " '70115': 'Chiswick Road',\n",
       " '70113': 'Chestnut Hill Avenue',\n",
       " '70111': 'South Street',\n",
       " '70107': 'Boston College',\n",
       " '70106': 'Boston College',\n",
       " '70110': 'South Street',\n",
       " '70112': 'Chestnut Hill Avenue',\n",
       " '70114': 'Chiswick Road',\n",
       " '70116': 'Sutherland Road',\n",
       " '70120': 'Washington Street',\n",
       " '70124': 'Warren Street',\n",
       " '70126': 'Allston Street',\n",
       " '70128': 'Griggs Street',\n",
       " '70130': 'Harvard Avenue',\n",
       " '70134': \"Packard's Corner\",\n",
       " '170136': 'Babcock Street',\n",
       " '170140': 'Amory Street',\n",
       " '70144': 'Boston University Central',\n",
       " '70146': 'Boston University East',\n",
       " '70148': 'Blandford Street',\n",
       " '71150': 'Kenmore',\n",
       " '70152': 'Hynes Convention Center',\n",
       " '70154': 'Copley',\n",
       " '70156': 'Arlington',\n",
       " '70158': 'Boylston',\n",
       " '70200': 'Park Street',\n",
       " '70201': 'Government Center',\n",
       " '70197': 'Park Street',\n",
       " '70151': 'Kenmore',\n",
       " '70211': \"Saint Mary's Street\",\n",
       " '70213': 'Hawes Street',\n",
       " '70215': 'Kent Street',\n",
       " '70217': 'Saint Paul Street',\n",
       " '70219': 'Coolidge Corner',\n",
       " '70223': 'Summit Avenue',\n",
       " '70225': 'Brandon Hall',\n",
       " '70227': 'Fairbanks Street',\n",
       " '70229': 'Washington Square',\n",
       " '70231': 'Tappan Street',\n",
       " '70233': 'Dean Road',\n",
       " '70235': 'Englewood Avenue',\n",
       " '70237': 'Cleveland Circle',\n",
       " '70238': 'Cleveland Circle',\n",
       " '70236': 'Englewood Avenue',\n",
       " '70234': 'Dean Road',\n",
       " '70232': 'Tappan Street',\n",
       " '70230': 'Washington Square',\n",
       " '70228': 'Fairbanks Street',\n",
       " '70226': 'Brandon Hall',\n",
       " '70224': 'Summit Avenue',\n",
       " '70220': 'Coolidge Corner',\n",
       " '70218': 'Saint Paul Street',\n",
       " '70216': 'Kent Street',\n",
       " '70214': 'Hawes Street',\n",
       " '70212': \"Saint Mary's Street\",\n",
       " '70150': 'Kenmore',\n",
       " '70206': 'North Station',\n",
       " '70204': 'Haymarket',\n",
       " '70198': 'Park Street',\n",
       " '70187': 'Fenway',\n",
       " '70183': 'Longwood',\n",
       " '70181': 'Brookline Village',\n",
       " '70179': 'Brookline Hills',\n",
       " '70177': 'Beaconsfield',\n",
       " '70175': 'Reservoir',\n",
       " '70173': 'Chestnut Hill',\n",
       " '70171': 'Newton Centre',\n",
       " '70169': 'Newton Highlands',\n",
       " '70167': 'Eliot',\n",
       " '70165': 'Waban',\n",
       " '70163': 'Woodland',\n",
       " '70161': 'Riverside',\n",
       " '70160': 'Riverside',\n",
       " '70162': 'Woodland',\n",
       " '70164': 'Waban',\n",
       " '70166': 'Eliot',\n",
       " '70168': 'Newton Highlands',\n",
       " '70170': 'Newton Centre',\n",
       " '70172': 'Chestnut Hill',\n",
       " '70174': 'Reservoir',\n",
       " '70176': 'Beaconsfield',\n",
       " '70178': 'Brookline Hills',\n",
       " '70180': 'Brookline Village',\n",
       " '70182': 'Longwood',\n",
       " '70186': 'Fenway',\n",
       " '70203': 'Haymarket',\n",
       " '70205': 'North Station',\n",
       " '70504': 'Union Square',\n",
       " '70502': 'Lechmere',\n",
       " '70208': 'Science Park/West End',\n",
       " '70207': 'Science Park/West End',\n",
       " '70501': 'Lechmere',\n",
       " '70503': 'Union Square',\n",
       " '70199': 'Park Street',\n",
       " '70239': 'Prudential',\n",
       " '70241': 'Symphony',\n",
       " '70243': 'Northeastern University',\n",
       " '70245': 'Museum of Fine Arts',\n",
       " '70247': 'Longwood Medical Area',\n",
       " '70249': 'Brigham Circle',\n",
       " '70251': 'Fenwood Road',\n",
       " '70253': 'Mission Park',\n",
       " '70255': 'Riverway',\n",
       " '70257': 'Back of the Hill',\n",
       " '70260': 'Heath Street',\n",
       " '70258': 'Back of the Hill',\n",
       " '70256': 'Riverway',\n",
       " '70254': 'Mission Park',\n",
       " '70252': 'Fenwood Road',\n",
       " '70250': 'Brigham Circle',\n",
       " '70248': 'Longwood Medical Area',\n",
       " '70246': 'Museum of Fine Arts',\n",
       " '70244': 'Northeastern University',\n",
       " '70242': 'Symphony',\n",
       " '70240': 'Prudential',\n",
       " '70261': 'Ashmont',\n",
       " '70263': 'Cedar Grove',\n",
       " '70265': 'Butler',\n",
       " '70267': 'Milton',\n",
       " '70269': 'Central Avenue',\n",
       " '70271': 'Valley Road',\n",
       " '70273': 'Capen Street',\n",
       " '70275': 'Mattapan',\n",
       " '70276': 'Mattapan',\n",
       " '70274': 'Capen Street',\n",
       " '70272': 'Valley Road',\n",
       " '70270': 'Central Avenue',\n",
       " '70268': 'Milton',\n",
       " '70266': 'Butler',\n",
       " '70264': 'Cedar Grove',\n",
       " '70036': 'Oak Grove',\n",
       " '70034': 'Malden Center',\n",
       " '70032': 'Wellington',\n",
       " '70278': 'Assembly',\n",
       " '70030': 'Sullivan Square',\n",
       " '70028': 'Community College',\n",
       " '70026': 'North Station',\n",
       " '70024': 'Haymarket',\n",
       " '70022': 'State',\n",
       " '70020': 'Downtown Crossing',\n",
       " '70018': 'Chinatown',\n",
       " '70016': 'Tufts Medical Center',\n",
       " '70014': 'Back Bay',\n",
       " '70012': 'Massachusetts Avenue',\n",
       " '70010': 'Ruggles',\n",
       " '70008': 'Roxbury Crossing',\n",
       " '70006': 'Jackson Square',\n",
       " '70004': 'Stony Brook',\n",
       " '70002': 'Green Street',\n",
       " '70001': 'Forest Hills',\n",
       " '70003': 'Green Street',\n",
       " '70005': 'Stony Brook',\n",
       " '70007': 'Jackson Square',\n",
       " '70009': 'Roxbury Crossing',\n",
       " '70011': 'Ruggles',\n",
       " '70013': 'Massachusetts Avenue',\n",
       " '70015': 'Back Bay',\n",
       " '70017': 'Tufts Medical Center',\n",
       " '70019': 'Chinatown',\n",
       " '70021': 'Downtown Crossing',\n",
       " '70023': 'State',\n",
       " '70025': 'Haymarket',\n",
       " '70027': 'North Station',\n",
       " '70029': 'Community College',\n",
       " '70031': 'Sullivan Square',\n",
       " '70279': 'Assembly',\n",
       " '70033': 'Wellington',\n",
       " '70035': 'Malden Center',\n",
       " '70061': 'Alewife',\n",
       " '70063': 'Davis',\n",
       " '70065': 'Porter',\n",
       " '70067': 'Harvard',\n",
       " '70069': 'Central',\n",
       " '70071': 'Kendall/MIT',\n",
       " '70073': 'Charles/MGH',\n",
       " '70075': 'Park Street',\n",
       " '70077': 'Downtown Crossing',\n",
       " '70079': 'South Station',\n",
       " '70081': 'Broadway',\n",
       " '70083': 'Andrew',\n",
       " '70085': 'JFK/UMass',\n",
       " '70087': 'Savin Hill',\n",
       " '70089': 'Fields Corner',\n",
       " '70091': 'Shawmut',\n",
       " '70093': 'Ashmont',\n",
       " '70094': 'Ashmont',\n",
       " '70092': 'Shawmut',\n",
       " '70090': 'Fields Corner',\n",
       " '70088': 'Savin Hill',\n",
       " '70086': 'JFK/UMass',\n",
       " '70084': 'Andrew',\n",
       " '70082': 'Broadway',\n",
       " '70080': 'South Station',\n",
       " '70078': 'Downtown Crossing',\n",
       " '70076': 'Park Street',\n",
       " '70074': 'Charles/MGH',\n",
       " '70072': 'Kendall/MIT',\n",
       " '70070': 'Central',\n",
       " '70068': 'Harvard',\n",
       " '70066': 'Porter',\n",
       " '70064': 'Davis',\n",
       " '70095': 'JFK/UMass',\n",
       " '70097': 'North Quincy',\n",
       " '70099': 'Wollaston',\n",
       " '70101': 'Quincy Center',\n",
       " '70103': 'Quincy Adams',\n",
       " '70105': 'Braintree',\n",
       " '70104': 'Quincy Adams',\n",
       " '70102': 'Quincy Center',\n",
       " '70100': 'Wollaston',\n",
       " '70098': 'North Quincy',\n",
       " '70096': 'JFK/UMass'}"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stops"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "for key in final_tables_dict:\n",
    "    for col in final_tables_dict[key]:\n",
    "        if col in stops:\n",
    "            final_tables_dict[key] = final_tables_dict[key].rename(columns = {col:stops[col]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "for key in final_tables_dict:\n",
    "    final_tables_dict[key].to_csv(r\"C:\\Users\\matkinson.AD\\Downloads\\\\\"+key+\".csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "urbanAccess",
   "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.8.13 | packaged by conda-forge | (default, Mar 25 2022, 05:59:45) [MSC v.1929 64 bit (AMD64)]"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "2a2b370b38977169157650f5355ad729af7f449719cdba662b087dc855e43e33"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}