02_transform_e_locations.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "from shapely.geometry import Point\n",
    "from shapely.ops import nearest_points\n",
    "import matplotlib.pyplot as plt\n",
    "from tqdm import tqdm as tqdm_base, tqdm_notebook as tqdm\n",
    "\n",
    "tqdm_base.pandas()\n",
    "\n",
    "# installing geopandas on mac with conda is a pain, i recommend starting with a fresh conda env and...\n",
    "# pip install geopandas numpy pandas shapely fiona six pyproj tables matplotlib tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# because jan 18 is split across 2 gtfs feeds (https://transitfeeds.com/p/king-county-metro/)\n",
    "# let's verify that route_id for D and E are consistent\n",
    "# i downloaded/unzipped gtfs into ./data/source/gtfs_YYYYMMDD dirs\n",
    "routes_1 = pd.read_csv('./data/source/gtfs_20171116/routes.txt')\n",
    "routes_2 = pd.read_csv('./data/source/gtfs_20180118/routes.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>202</th>\n",
       "      <td>102581</td>\n",
       "      <td>KCM</td>\n",
       "      <td>D Line</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Blue Ridge/Crown Hill - Ballard - Downtown Sea...</td>\n",
       "      <td>3</td>\n",
       "      <td>http://metro.kingcounty.gov/schedules/674/n0.html</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>102615</td>\n",
       "      <td>KCM</td>\n",
       "      <td>E Line</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Aurora Village Transit Center - Downtown Seattle</td>\n",
       "      <td>3</td>\n",
       "      <td>http://metro.kingcounty.gov/schedules/675/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",
       "202    102581       KCM           D Line              NaN   \n",
       "205    102615       KCM           E Line              NaN   \n",
       "\n",
       "                                            route_desc  route_type  \\\n",
       "202  Blue Ridge/Crown Hill - Ballard - Downtown Sea...           3   \n",
       "205   Aurora Village Transit Center - Downtown Seattle           3   \n",
       "\n",
       "                                             route_url  route_color  \\\n",
       "202  http://metro.kingcounty.gov/schedules/674/n0.html          NaN   \n",
       "205  http://metro.kingcounty.gov/schedules/675/n0.html          NaN   \n",
       "\n",
       "     route_text_color  \n",
       "202               NaN  \n",
       "205               NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "routes_1[(routes_1.route_short_name == 'D Line') | (routes_1.route_short_name == 'E Line')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>202</th>\n",
       "      <td>102581</td>\n",
       "      <td>KCM</td>\n",
       "      <td>D Line</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Blue Ridge/Crown Hill - Ballard - Downtown Sea...</td>\n",
       "      <td>3</td>\n",
       "      <td>http://metro.kingcounty.gov/schedules/674/n0.html</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>102615</td>\n",
       "      <td>KCM</td>\n",
       "      <td>E Line</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Aurora Village Transit Center - Downtown Seattle</td>\n",
       "      <td>3</td>\n",
       "      <td>http://metro.kingcounty.gov/schedules/675/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",
       "202    102581       KCM           D Line              NaN   \n",
       "205    102615       KCM           E Line              NaN   \n",
       "\n",
       "                                            route_desc  route_type  \\\n",
       "202  Blue Ridge/Crown Hill - Ballard - Downtown Sea...           3   \n",
       "205   Aurora Village Transit Center - Downtown Seattle           3   \n",
       "\n",
       "                                             route_url  route_color  \\\n",
       "202  http://metro.kingcounty.gov/schedules/674/n0.html          NaN   \n",
       "205  http://metro.kingcounty.gov/schedules/675/n0.html          NaN   \n",
       "\n",
       "     route_text_color  \n",
       "202               NaN  \n",
       "205               NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "routes_2[(routes_2.route_short_name == 'D Line') | (routes_2.route_short_name == 'E Line')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# route_id is unchanged\n",
    "# D line = 102581\n",
    "# E line = 102615"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "locations_df = pd.read_hdf('./data/intermed/positions_201801.h5', key='df')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(167619, 8)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "locations_df[locations_df.route_id == 102581].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(197432, 8)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "locations_df[locations_df.route_id == 102615].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# because E line has more locations and keith's suggested analysis highlights 2 segments, we'll look at E first:\n",
    "# For the E, two segments to focus on:\n",
    "# 1). Pine to Denny/Battery\n",
    "# 2). Denny/Battery to Aurora/46th"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "e_df = locations_df[locations_df.route_id == 102615]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>timestamp</th>\n",
       "      <th>trip_id</th>\n",
       "      <th>vehicle_id</th>\n",
       "      <th>vehicle_lat</th>\n",
       "      <th>vehicle_long</th>\n",
       "      <th>time_utc</th>\n",
       "      <th>time_pct</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp_utc</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:58:02</th>\n",
       "      <td>102615</td>\n",
       "      <td>1514764682</td>\n",
       "      <td>34922199</td>\n",
       "      <td>6081</td>\n",
       "      <td>47.774178</td>\n",
       "      <td>-122.342422</td>\n",
       "      <td>2017-12-31 23:58:02</td>\n",
       "      <td>2017-12-31 15:58:02-08:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:58:28</th>\n",
       "      <td>102615</td>\n",
       "      <td>1514764708</td>\n",
       "      <td>34922112</td>\n",
       "      <td>6051</td>\n",
       "      <td>47.739391</td>\n",
       "      <td>-122.345238</td>\n",
       "      <td>2017-12-31 23:58:28</td>\n",
       "      <td>2017-12-31 15:58:28-08:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:58:46</th>\n",
       "      <td>102615</td>\n",
       "      <td>1514764726</td>\n",
       "      <td>34922164</td>\n",
       "      <td>6211</td>\n",
       "      <td>47.694817</td>\n",
       "      <td>-122.344574</td>\n",
       "      <td>2017-12-31 23:58:46</td>\n",
       "      <td>2017-12-31 15:58:46-08:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:59:03</th>\n",
       "      <td>102615</td>\n",
       "      <td>1514764743</td>\n",
       "      <td>34922084</td>\n",
       "      <td>6071</td>\n",
       "      <td>47.686714</td>\n",
       "      <td>-122.344505</td>\n",
       "      <td>2017-12-31 23:59:03</td>\n",
       "      <td>2017-12-31 15:59:03-08:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:59:04</th>\n",
       "      <td>102615</td>\n",
       "      <td>1514764744</td>\n",
       "      <td>34922180</td>\n",
       "      <td>6088</td>\n",
       "      <td>47.618118</td>\n",
       "      <td>-122.343559</td>\n",
       "      <td>2017-12-31 23:59:04</td>\n",
       "      <td>2017-12-31 15:59:04-08:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     route_id   timestamp   trip_id  vehicle_id  vehicle_lat  \\\n",
       "timestamp_utc                                                                  \n",
       "2017-12-31 23:58:02    102615  1514764682  34922199        6081    47.774178   \n",
       "2017-12-31 23:58:28    102615  1514764708  34922112        6051    47.739391   \n",
       "2017-12-31 23:58:46    102615  1514764726  34922164        6211    47.694817   \n",
       "2017-12-31 23:59:03    102615  1514764743  34922084        6071    47.686714   \n",
       "2017-12-31 23:59:04    102615  1514764744  34922180        6088    47.618118   \n",
       "\n",
       "                     vehicle_long             time_utc  \\\n",
       "timestamp_utc                                            \n",
       "2017-12-31 23:58:02   -122.342422  2017-12-31 23:58:02   \n",
       "2017-12-31 23:58:28   -122.345238  2017-12-31 23:58:28   \n",
       "2017-12-31 23:58:46   -122.344574  2017-12-31 23:58:46   \n",
       "2017-12-31 23:59:03   -122.344505  2017-12-31 23:59:03   \n",
       "2017-12-31 23:59:04   -122.343559  2017-12-31 23:59:04   \n",
       "\n",
       "                                      time_pct  \n",
       "timestamp_utc                                   \n",
       "2017-12-31 23:58:02  2017-12-31 15:58:02-08:00  \n",
       "2017-12-31 23:58:28  2017-12-31 15:58:28-08:00  \n",
       "2017-12-31 23:58:46  2017-12-31 15:58:46-08:00  \n",
       "2017-12-31 23:59:03  2017-12-31 15:59:03-08:00  \n",
       "2017-12-31 23:59:04  2017-12-31 15:59:04-08:00  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "trips_df = pd.read_csv('./data/source/gtfs_20180118/trips.txt')\n",
    "shapes_df = pd.read_csv('./data/source/gtfs_20180118/shapes.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>12478</th>\n",
       "      <td>102615</td>\n",
       "      <td>66726</td>\n",
       "      <td>34815718</td>\n",
       "      <td>Aurora Village Transit Center</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>4759418</td>\n",
       "      <td>11675003</td>\n",
       "      <td>1</td>\n",
       "      <td>112.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12479</th>\n",
       "      <td>102615</td>\n",
       "      <td>66726</td>\n",
       "      <td>34815719</td>\n",
       "      <td>Aurora Village Transit Center</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>4759590</td>\n",
       "      <td>11675003</td>\n",
       "      <td>0</td>\n",
       "      <td>102.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12481</th>\n",
       "      <td>102615</td>\n",
       "      <td>66726</td>\n",
       "      <td>34815721</td>\n",
       "      <td>Aurora Village Transit Center</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>4759415</td>\n",
       "      <td>11675003</td>\n",
       "      <td>1</td>\n",
       "      <td>112.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12482</th>\n",
       "      <td>102615</td>\n",
       "      <td>66726</td>\n",
       "      <td>34815722</td>\n",
       "      <td>Aurora Village Transit Center</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>4759519</td>\n",
       "      <td>11675003</td>\n",
       "      <td>1</td>\n",
       "      <td>112.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12483</th>\n",
       "      <td>102615</td>\n",
       "      <td>66726</td>\n",
       "      <td>34815723</td>\n",
       "      <td>Aurora Village Transit Center</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>4759412</td>\n",
       "      <td>11675003</td>\n",
       "      <td>0</td>\n",
       "      <td>102.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       route_id  service_id   trip_id                   trip_headsign  \\\n",
       "12478    102615       66726  34815718  Aurora Village Transit Center    \n",
       "12479    102615       66726  34815719  Aurora Village Transit Center    \n",
       "12481    102615       66726  34815721  Aurora Village Transit Center    \n",
       "12482    102615       66726  34815722  Aurora Village Transit Center    \n",
       "12483    102615       66726  34815723  Aurora Village Transit Center    \n",
       "\n",
       "      trip_short_name  direction_id  block_id  shape_id  peak_flag  fare_id  \n",
       "12478           LOCAL             0   4759418  11675003          1    112.0  \n",
       "12479           LOCAL             0   4759590  11675003          0    102.0  \n",
       "12481           LOCAL             0   4759415  11675003          1    112.0  \n",
       "12482           LOCAL             0   4759519  11675003          1    112.0  \n",
       "12483           LOCAL             0   4759412  11675003          0    102.0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# it appears that direction_id = 0 for the E is northbound\n",
    "trips_df[(trips_df.route_id == 102615) & (trips_df.direction_id == 0)].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "e_shape = shapes_df[shapes_df.shape_id == 11675003]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>shape_id</th>\n",
       "      <th>shape_pt_lat</th>\n",
       "      <th>shape_pt_lon</th>\n",
       "      <th>shape_pt_sequence</th>\n",
       "      <th>shape_dist_traveled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>113213</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601669</td>\n",
       "      <td>-122.330170</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113214</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601719</td>\n",
       "      <td>-122.330269</td>\n",
       "      <td>2</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113215</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601761</td>\n",
       "      <td>-122.330391</td>\n",
       "      <td>3</td>\n",
       "      <td>64.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113216</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601830</td>\n",
       "      <td>-122.330498</td>\n",
       "      <td>4</td>\n",
       "      <td>100.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113217</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601936</td>\n",
       "      <td>-122.330650</td>\n",
       "      <td>5</td>\n",
       "      <td>153.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        shape_id  shape_pt_lat  shape_pt_lon  shape_pt_sequence  \\\n",
       "113213  11675003     47.601669   -122.330170                  1   \n",
       "113214  11675003     47.601719   -122.330269                  2   \n",
       "113215  11675003     47.601761   -122.330391                  3   \n",
       "113216  11675003     47.601830   -122.330498                  4   \n",
       "113217  11675003     47.601936   -122.330650                  5   \n",
       "\n",
       "        shape_dist_traveled  \n",
       "113213                  0.0  \n",
       "113214                 30.0  \n",
       "113215                 64.5  \n",
       "113216                100.1  \n",
       "113217                153.5  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e_shape.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/aps/miniconda3/envs/realtime_buses/lib/python3.6/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n",
      "/Users/aps/miniconda3/envs/realtime_buses/lib/python3.6/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "e_shape['coordinates'] = list(zip(e_shape.shape_pt_lon, e_shape.shape_pt_lat))\n",
    "e_shape['coordinates'] = e_shape['coordinates'].apply(Point)"
   ]
  },
  {
   "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>shape_id</th>\n",
       "      <th>shape_pt_lat</th>\n",
       "      <th>shape_pt_lon</th>\n",
       "      <th>shape_pt_sequence</th>\n",
       "      <th>shape_dist_traveled</th>\n",
       "      <th>coordinates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>113213</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601669</td>\n",
       "      <td>-122.330170</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>POINT (-122.33017 47.6016693)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113214</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601719</td>\n",
       "      <td>-122.330269</td>\n",
       "      <td>2</td>\n",
       "      <td>30.0</td>\n",
       "      <td>POINT (-122.330269 47.6017189)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113215</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601761</td>\n",
       "      <td>-122.330391</td>\n",
       "      <td>3</td>\n",
       "      <td>64.5</td>\n",
       "      <td>POINT (-122.330391 47.6017609)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113216</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601830</td>\n",
       "      <td>-122.330498</td>\n",
       "      <td>4</td>\n",
       "      <td>100.1</td>\n",
       "      <td>POINT (-122.330498 47.6018295)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113217</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601936</td>\n",
       "      <td>-122.330650</td>\n",
       "      <td>5</td>\n",
       "      <td>153.5</td>\n",
       "      <td>POINT (-122.33065 47.6019363)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113218</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.602058</td>\n",
       "      <td>-122.330757</td>\n",
       "      <td>6</td>\n",
       "      <td>205.6</td>\n",
       "      <td>POINT (-122.330757 47.6020584)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113219</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.602085</td>\n",
       "      <td>-122.330788</td>\n",
       "      <td>7</td>\n",
       "      <td>217.6</td>\n",
       "      <td>POINT (-122.330788 47.6020851)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113220</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.602764</td>\n",
       "      <td>-122.331413</td>\n",
       "      <td>8</td>\n",
       "      <td>511.7</td>\n",
       "      <td>POINT (-122.331413 47.6027641)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113221</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.603481</td>\n",
       "      <td>-122.332069</td>\n",
       "      <td>9</td>\n",
       "      <td>818.2</td>\n",
       "      <td>POINT (-122.332069 47.6034813)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113222</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.604153</td>\n",
       "      <td>-122.332672</td>\n",
       "      <td>10</td>\n",
       "      <td>1104.1</td>\n",
       "      <td>POINT (-122.332672 47.6041527)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113223</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.604195</td>\n",
       "      <td>-122.332710</td>\n",
       "      <td>11</td>\n",
       "      <td>1124.1</td>\n",
       "      <td>POINT (-122.33271 47.6041946)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113224</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.604912</td>\n",
       "      <td>-122.333366</td>\n",
       "      <td>12</td>\n",
       "      <td>1430.0</td>\n",
       "      <td>POINT (-122.333366 47.6049118)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113225</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.605625</td>\n",
       "      <td>-122.334023</td>\n",
       "      <td>13</td>\n",
       "      <td>1735.9</td>\n",
       "      <td>POINT (-122.334023 47.60562520000001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113226</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.606338</td>\n",
       "      <td>-122.334679</td>\n",
       "      <td>14</td>\n",
       "      <td>2042.0</td>\n",
       "      <td>POINT (-122.334679 47.6063385)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113227</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.607014</td>\n",
       "      <td>-122.335297</td>\n",
       "      <td>15</td>\n",
       "      <td>2331.9</td>\n",
       "      <td>POINT (-122.335297 47.6070137)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113228</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.607052</td>\n",
       "      <td>-122.335320</td>\n",
       "      <td>16</td>\n",
       "      <td>2347.7</td>\n",
       "      <td>POINT (-122.33532 47.6070518)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113229</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.607761</td>\n",
       "      <td>-122.335976</td>\n",
       "      <td>17</td>\n",
       "      <td>2653.5</td>\n",
       "      <td>POINT (-122.335976 47.6077614)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113230</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.608753</td>\n",
       "      <td>-122.336891</td>\n",
       "      <td>18</td>\n",
       "      <td>3079.5</td>\n",
       "      <td>POINT (-122.336891 47.6087532)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113231</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.609753</td>\n",
       "      <td>-122.337791</td>\n",
       "      <td>19</td>\n",
       "      <td>3505.6</td>\n",
       "      <td>POINT (-122.337791 47.6097527)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113232</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.610451</td>\n",
       "      <td>-122.338432</td>\n",
       "      <td>20</td>\n",
       "      <td>3806.3</td>\n",
       "      <td>POINT (-122.338432 47.6104507)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113233</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.610516</td>\n",
       "      <td>-122.338486</td>\n",
       "      <td>21</td>\n",
       "      <td>3831.3</td>\n",
       "      <td>POINT (-122.338486 47.6105156)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113234</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.610745</td>\n",
       "      <td>-122.338707</td>\n",
       "      <td>22</td>\n",
       "      <td>3931.6</td>\n",
       "      <td>POINT (-122.338707 47.6107445)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113235</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.611443</td>\n",
       "      <td>-122.339363</td>\n",
       "      <td>23</td>\n",
       "      <td>4233.4</td>\n",
       "      <td>POINT (-122.339363 47.6114426)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113236</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.611492</td>\n",
       "      <td>-122.339401</td>\n",
       "      <td>24</td>\n",
       "      <td>4254.4</td>\n",
       "      <td>POINT (-122.339401 47.6114922)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113237</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.611534</td>\n",
       "      <td>-122.339447</td>\n",
       "      <td>25</td>\n",
       "      <td>4272.9</td>\n",
       "      <td>POINT (-122.339447 47.6115341)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113238</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.611572</td>\n",
       "      <td>-122.339500</td>\n",
       "      <td>26</td>\n",
       "      <td>4293.8</td>\n",
       "      <td>POINT (-122.3395 47.6115723)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113239</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.611629</td>\n",
       "      <td>-122.339600</td>\n",
       "      <td>27</td>\n",
       "      <td>4323.4</td>\n",
       "      <td>POINT (-122.3396 47.6116295)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113240</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.612400</td>\n",
       "      <td>-122.340897</td>\n",
       "      <td>28</td>\n",
       "      <td>4749.6</td>\n",
       "      <td>POINT (-122.340897 47.6124001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113241</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.613022</td>\n",
       "      <td>-122.341942</td>\n",
       "      <td>29</td>\n",
       "      <td>5094.6</td>\n",
       "      <td>POINT (-122.341942 47.6130219)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113242</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.613167</td>\n",
       "      <td>-122.342194</td>\n",
       "      <td>30</td>\n",
       "      <td>5175.6</td>\n",
       "      <td>POINT (-122.342194 47.61316679999999)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113505</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.765797</td>\n",
       "      <td>-122.345879</td>\n",
       "      <td>293</td>\n",
       "      <td>62170.7</td>\n",
       "      <td>POINT (-122.345879 47.7657967)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113506</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.767788</td>\n",
       "      <td>-122.345909</td>\n",
       "      <td>294</td>\n",
       "      <td>62898.0</td>\n",
       "      <td>POINT (-122.345909 47.76778789999999)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113507</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.768063</td>\n",
       "      <td>-122.345909</td>\n",
       "      <td>295</td>\n",
       "      <td>62998.0</td>\n",
       "      <td>POINT (-122.345909 47.7680626)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113508</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.770523</td>\n",
       "      <td>-122.345963</td>\n",
       "      <td>296</td>\n",
       "      <td>63897.5</td>\n",
       "      <td>POINT (-122.345963 47.77052310000001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113509</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.770882</td>\n",
       "      <td>-122.345963</td>\n",
       "      <td>297</td>\n",
       "      <td>64028.7</td>\n",
       "      <td>POINT (-122.345963 47.7708817)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113510</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.771015</td>\n",
       "      <td>-122.345909</td>\n",
       "      <td>298</td>\n",
       "      <td>64078.3</td>\n",
       "      <td>POINT (-122.345909 47.7710152)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113511</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.772205</td>\n",
       "      <td>-122.345924</td>\n",
       "      <td>299</td>\n",
       "      <td>64512.3</td>\n",
       "      <td>POINT (-122.345924 47.7722054)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113512</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.772331</td>\n",
       "      <td>-122.346001</td>\n",
       "      <td>300</td>\n",
       "      <td>64561.9</td>\n",
       "      <td>POINT (-122.346001 47.7723312)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113513</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.773232</td>\n",
       "      <td>-122.346016</td>\n",
       "      <td>301</td>\n",
       "      <td>64888.0</td>\n",
       "      <td>POINT (-122.346016 47.7732315)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113514</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.773937</td>\n",
       "      <td>-122.346031</td>\n",
       "      <td>302</td>\n",
       "      <td>65147.5</td>\n",
       "      <td>POINT (-122.346031 47.77393720000001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113515</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774158</td>\n",
       "      <td>-122.346031</td>\n",
       "      <td>303</td>\n",
       "      <td>65227.5</td>\n",
       "      <td>POINT (-122.346031 47.7741585)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113516</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774162</td>\n",
       "      <td>-122.343842</td>\n",
       "      <td>304</td>\n",
       "      <td>65763.1</td>\n",
       "      <td>POINT (-122.343842 47.77416229999999)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113517</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774170</td>\n",
       "      <td>-122.343491</td>\n",
       "      <td>305</td>\n",
       "      <td>65853.0</td>\n",
       "      <td>POINT (-122.343491 47.7741699)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113518</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774174</td>\n",
       "      <td>-122.343216</td>\n",
       "      <td>306</td>\n",
       "      <td>65917.5</td>\n",
       "      <td>POINT (-122.343216 47.77417370000001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113519</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774178</td>\n",
       "      <td>-122.342941</td>\n",
       "      <td>307</td>\n",
       "      <td>65985.7</td>\n",
       "      <td>POINT (-122.342941 47.7741776)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113520</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774178</td>\n",
       "      <td>-122.342751</td>\n",
       "      <td>308</td>\n",
       "      <td>66031.3</td>\n",
       "      <td>POINT (-122.342751 47.7741776)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113521</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774353</td>\n",
       "      <td>-122.342751</td>\n",
       "      <td>309</td>\n",
       "      <td>66095.2</td>\n",
       "      <td>POINT (-122.342751 47.774353)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113522</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774429</td>\n",
       "      <td>-122.342735</td>\n",
       "      <td>310</td>\n",
       "      <td>66124.0</td>\n",
       "      <td>POINT (-122.342735 47.77442929999999)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113523</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774468</td>\n",
       "      <td>-122.342735</td>\n",
       "      <td>311</td>\n",
       "      <td>66136.9</td>\n",
       "      <td>POINT (-122.342735 47.7744675)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113524</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774490</td>\n",
       "      <td>-122.342728</td>\n",
       "      <td>312</td>\n",
       "      <td>66146.3</td>\n",
       "      <td>POINT (-122.342728 47.7744904)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113525</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774509</td>\n",
       "      <td>-122.342682</td>\n",
       "      <td>313</td>\n",
       "      <td>66157.8</td>\n",
       "      <td>POINT (-122.342682 47.7745094)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113526</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774532</td>\n",
       "      <td>-122.342628</td>\n",
       "      <td>314</td>\n",
       "      <td>66171.6</td>\n",
       "      <td>POINT (-122.342628 47.7745323)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113527</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774532</td>\n",
       "      <td>-122.342560</td>\n",
       "      <td>315</td>\n",
       "      <td>66189.9</td>\n",
       "      <td>POINT (-122.34256 47.7745323)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113528</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774551</td>\n",
       "      <td>-122.342232</td>\n",
       "      <td>316</td>\n",
       "      <td>66269.3</td>\n",
       "      <td>POINT (-122.342232 47.7745514)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113529</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774548</td>\n",
       "      <td>-122.341644</td>\n",
       "      <td>317</td>\n",
       "      <td>66416.0</td>\n",
       "      <td>POINT (-122.341644 47.77454760000001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113530</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774551</td>\n",
       "      <td>-122.341415</td>\n",
       "      <td>318</td>\n",
       "      <td>66472.3</td>\n",
       "      <td>POINT (-122.341415 47.7745514)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113531</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774551</td>\n",
       "      <td>-122.341225</td>\n",
       "      <td>319</td>\n",
       "      <td>66518.1</td>\n",
       "      <td>POINT (-122.341225 47.7745514)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113532</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774551</td>\n",
       "      <td>-122.341072</td>\n",
       "      <td>320</td>\n",
       "      <td>66556.9</td>\n",
       "      <td>POINT (-122.341072 47.7745514)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113533</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774551</td>\n",
       "      <td>-122.340866</td>\n",
       "      <td>321</td>\n",
       "      <td>66604.8</td>\n",
       "      <td>POINT (-122.340866 47.7745514)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113534</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.774551</td>\n",
       "      <td>-122.340866</td>\n",
       "      <td>322</td>\n",
       "      <td>66605.6</td>\n",
       "      <td>POINT (-122.340866 47.7745514)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>322 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        shape_id  shape_pt_lat  shape_pt_lon  shape_pt_sequence  \\\n",
       "113213  11675003     47.601669   -122.330170                  1   \n",
       "113214  11675003     47.601719   -122.330269                  2   \n",
       "113215  11675003     47.601761   -122.330391                  3   \n",
       "113216  11675003     47.601830   -122.330498                  4   \n",
       "113217  11675003     47.601936   -122.330650                  5   \n",
       "113218  11675003     47.602058   -122.330757                  6   \n",
       "113219  11675003     47.602085   -122.330788                  7   \n",
       "113220  11675003     47.602764   -122.331413                  8   \n",
       "113221  11675003     47.603481   -122.332069                  9   \n",
       "113222  11675003     47.604153   -122.332672                 10   \n",
       "113223  11675003     47.604195   -122.332710                 11   \n",
       "113224  11675003     47.604912   -122.333366                 12   \n",
       "113225  11675003     47.605625   -122.334023                 13   \n",
       "113226  11675003     47.606338   -122.334679                 14   \n",
       "113227  11675003     47.607014   -122.335297                 15   \n",
       "113228  11675003     47.607052   -122.335320                 16   \n",
       "113229  11675003     47.607761   -122.335976                 17   \n",
       "113230  11675003     47.608753   -122.336891                 18   \n",
       "113231  11675003     47.609753   -122.337791                 19   \n",
       "113232  11675003     47.610451   -122.338432                 20   \n",
       "113233  11675003     47.610516   -122.338486                 21   \n",
       "113234  11675003     47.610745   -122.338707                 22   \n",
       "113235  11675003     47.611443   -122.339363                 23   \n",
       "113236  11675003     47.611492   -122.339401                 24   \n",
       "113237  11675003     47.611534   -122.339447                 25   \n",
       "113238  11675003     47.611572   -122.339500                 26   \n",
       "113239  11675003     47.611629   -122.339600                 27   \n",
       "113240  11675003     47.612400   -122.340897                 28   \n",
       "113241  11675003     47.613022   -122.341942                 29   \n",
       "113242  11675003     47.613167   -122.342194                 30   \n",
       "...          ...           ...           ...                ...   \n",
       "113505  11675003     47.765797   -122.345879                293   \n",
       "113506  11675003     47.767788   -122.345909                294   \n",
       "113507  11675003     47.768063   -122.345909                295   \n",
       "113508  11675003     47.770523   -122.345963                296   \n",
       "113509  11675003     47.770882   -122.345963                297   \n",
       "113510  11675003     47.771015   -122.345909                298   \n",
       "113511  11675003     47.772205   -122.345924                299   \n",
       "113512  11675003     47.772331   -122.346001                300   \n",
       "113513  11675003     47.773232   -122.346016                301   \n",
       "113514  11675003     47.773937   -122.346031                302   \n",
       "113515  11675003     47.774158   -122.346031                303   \n",
       "113516  11675003     47.774162   -122.343842                304   \n",
       "113517  11675003     47.774170   -122.343491                305   \n",
       "113518  11675003     47.774174   -122.343216                306   \n",
       "113519  11675003     47.774178   -122.342941                307   \n",
       "113520  11675003     47.774178   -122.342751                308   \n",
       "113521  11675003     47.774353   -122.342751                309   \n",
       "113522  11675003     47.774429   -122.342735                310   \n",
       "113523  11675003     47.774468   -122.342735                311   \n",
       "113524  11675003     47.774490   -122.342728                312   \n",
       "113525  11675003     47.774509   -122.342682                313   \n",
       "113526  11675003     47.774532   -122.342628                314   \n",
       "113527  11675003     47.774532   -122.342560                315   \n",
       "113528  11675003     47.774551   -122.342232                316   \n",
       "113529  11675003     47.774548   -122.341644                317   \n",
       "113530  11675003     47.774551   -122.341415                318   \n",
       "113531  11675003     47.774551   -122.341225                319   \n",
       "113532  11675003     47.774551   -122.341072                320   \n",
       "113533  11675003     47.774551   -122.340866                321   \n",
       "113534  11675003     47.774551   -122.340866                322   \n",
       "\n",
       "        shape_dist_traveled                            coordinates  \n",
       "113213                  0.0          POINT (-122.33017 47.6016693)  \n",
       "113214                 30.0         POINT (-122.330269 47.6017189)  \n",
       "113215                 64.5         POINT (-122.330391 47.6017609)  \n",
       "113216                100.1         POINT (-122.330498 47.6018295)  \n",
       "113217                153.5          POINT (-122.33065 47.6019363)  \n",
       "113218                205.6         POINT (-122.330757 47.6020584)  \n",
       "113219                217.6         POINT (-122.330788 47.6020851)  \n",
       "113220                511.7         POINT (-122.331413 47.6027641)  \n",
       "113221                818.2         POINT (-122.332069 47.6034813)  \n",
       "113222               1104.1         POINT (-122.332672 47.6041527)  \n",
       "113223               1124.1          POINT (-122.33271 47.6041946)  \n",
       "113224               1430.0         POINT (-122.333366 47.6049118)  \n",
       "113225               1735.9  POINT (-122.334023 47.60562520000001)  \n",
       "113226               2042.0         POINT (-122.334679 47.6063385)  \n",
       "113227               2331.9         POINT (-122.335297 47.6070137)  \n",
       "113228               2347.7          POINT (-122.33532 47.6070518)  \n",
       "113229               2653.5         POINT (-122.335976 47.6077614)  \n",
       "113230               3079.5         POINT (-122.336891 47.6087532)  \n",
       "113231               3505.6         POINT (-122.337791 47.6097527)  \n",
       "113232               3806.3         POINT (-122.338432 47.6104507)  \n",
       "113233               3831.3         POINT (-122.338486 47.6105156)  \n",
       "113234               3931.6         POINT (-122.338707 47.6107445)  \n",
       "113235               4233.4         POINT (-122.339363 47.6114426)  \n",
       "113236               4254.4         POINT (-122.339401 47.6114922)  \n",
       "113237               4272.9         POINT (-122.339447 47.6115341)  \n",
       "113238               4293.8           POINT (-122.3395 47.6115723)  \n",
       "113239               4323.4           POINT (-122.3396 47.6116295)  \n",
       "113240               4749.6         POINT (-122.340897 47.6124001)  \n",
       "113241               5094.6         POINT (-122.341942 47.6130219)  \n",
       "113242               5175.6  POINT (-122.342194 47.61316679999999)  \n",
       "...                     ...                                    ...  \n",
       "113505              62170.7         POINT (-122.345879 47.7657967)  \n",
       "113506              62898.0  POINT (-122.345909 47.76778789999999)  \n",
       "113507              62998.0         POINT (-122.345909 47.7680626)  \n",
       "113508              63897.5  POINT (-122.345963 47.77052310000001)  \n",
       "113509              64028.7         POINT (-122.345963 47.7708817)  \n",
       "113510              64078.3         POINT (-122.345909 47.7710152)  \n",
       "113511              64512.3         POINT (-122.345924 47.7722054)  \n",
       "113512              64561.9         POINT (-122.346001 47.7723312)  \n",
       "113513              64888.0         POINT (-122.346016 47.7732315)  \n",
       "113514              65147.5  POINT (-122.346031 47.77393720000001)  \n",
       "113515              65227.5         POINT (-122.346031 47.7741585)  \n",
       "113516              65763.1  POINT (-122.343842 47.77416229999999)  \n",
       "113517              65853.0         POINT (-122.343491 47.7741699)  \n",
       "113518              65917.5  POINT (-122.343216 47.77417370000001)  \n",
       "113519              65985.7         POINT (-122.342941 47.7741776)  \n",
       "113520              66031.3         POINT (-122.342751 47.7741776)  \n",
       "113521              66095.2          POINT (-122.342751 47.774353)  \n",
       "113522              66124.0  POINT (-122.342735 47.77442929999999)  \n",
       "113523              66136.9         POINT (-122.342735 47.7744675)  \n",
       "113524              66146.3         POINT (-122.342728 47.7744904)  \n",
       "113525              66157.8         POINT (-122.342682 47.7745094)  \n",
       "113526              66171.6         POINT (-122.342628 47.7745323)  \n",
       "113527              66189.9          POINT (-122.34256 47.7745323)  \n",
       "113528              66269.3         POINT (-122.342232 47.7745514)  \n",
       "113529              66416.0  POINT (-122.341644 47.77454760000001)  \n",
       "113530              66472.3         POINT (-122.341415 47.7745514)  \n",
       "113531              66518.1         POINT (-122.341225 47.7745514)  \n",
       "113532              66556.9         POINT (-122.341072 47.7745514)  \n",
       "113533              66604.8         POINT (-122.340866 47.7745514)  \n",
       "113534              66605.6         POINT (-122.340866 47.7745514)  \n",
       "\n",
       "[322 rows x 6 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e_shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf = gpd.GeoDataFrame(e_shape, geometry='coordinates')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAGsAAAEJCAYAAACT9NnpAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvOIA7rQAAEO1JREFUeJztnXuwVdV9xz/fXB4RI+EZQsB4TbTRpBjQW4GxmVGsionRm6kWHTXSCJ2MdiYOUyOMNsYONrHMCLVOtCoxOF6tLSqlEYOo0DAOYC4RLhBquRIfIVQIz4iKAr/+sde5bC77vO495+yzzl6fmT137/XYa22+7MdZ+7t+W2ZGwA8+kXYHAqUTxPKIIJZHBLE8IojlEUEsjwhieUQQyyOCWB7RJ+0OVIJhw4ZZc3Nz2t1IZO3atX8ws+GV2FdDiNXc3Ex7e3va3UhE0luV2le4DHpEEMsjglgeEcTyiCCWRwSxPCKI5RFBLI8IYnlEEMsjglgeEcTyiCCWRwSxPCKI5RFBLI8IYnlEEMsjShZLUpOk1yT93G2vlLTOLb+XtCihzgWxMuskfSiptVB9SedL2hfL+0GlDtZ3yvFgfA/YDAwEMLOv5TIkPQ38Z/cKZrYcGOvKDAE6gRdKqL/SzC4ro28lcceiDTy55h0OF5jmNLB/Ex13Ta500xWhpDNL0mjgG8AjCXkDgUnAcWdWN64Enjez93tYv1fcsWgDj69+u6BQAPsPHuasO39Rza70mFIvg/OA7wNHEvJagZfMbH+RfVwNPFli/YmS1kt6XtJXknYm6W8ktUtq37lzZ9EDeGLN20XL5Nh/8HDJZWtJUbEkXQbsMLO1eYpcQ7II8X2MBMYAS0uo/2vgFDP7KvAv5DnjzOwhM2sxs5bhw4vb8o40wATPUs6s84DLJb0J/BswSdLjAJKGAecCzxXZx18Bz5rZx/HEpPpmtt/M3nPrS4C+rlzmKSqWmc0ys9Fm1kx0KXvZzK5z2VcCPzezD4vsJt/Zd1x9SZ+VJLd+ruvjrqJHUkEG9m+qZXMl09vfWcfdhyS1SHoktt0MnAz8dyn1iQTcKGk9cB9wtdVwlno9Pw2qEWbrt7S0WDH7dPPM/FfqwQP68toPLq50twCQtNbMWiqxr8yMYAw6oW/evIMf1+fTX3cyI9YPL0/8BQDA+x8n/SKpPzIjVuu4UWl3oddkRqxGIDNiXXTvirx5he5n9URmxNqy40DevEL3s3oiM2IVwpf7WRDLI4JYHhHE8ogglkcEsYjeIvtAZsQq9FuqbXXpb5HTJDNiFfot5ct7h8yI5ctvqUKk6Rv8maTfxvJyljVJuk9Sp6QOSWdX6mB9JzXfoONWM1vYrdqlwOluGQ884P5mntR9gwlcATxmEauBQc4dlXnS9g3e7S51cyX1d2mjgHdiZX7n0jJPmr7BWcAZwJ8BQ4DbSulwbJ9lmTwbgdR8g2a23V3qDgKPuv0AbCNyQ+UY7dKOoVyTZyOQmm8wdx9yHsFWYKPLWgx82z0VTgD2mdn2Ug+okUnTN9gmaQOwARgGzHbpS4CtRE+ODwM39bKPDUNZYVfNbAWwIrZ9fkKZdmBabPtNEh4QzGxSnjYMuLmcfmWFzIxgNAJBLI/IlFj5DtaXfwRf+lkR8vlu/fDjZkws3wlieUQQyyOCWB4RxPKIIJZHBLE8IojlEUEsjwhieUQQyyOCWB6RpsmzTdLrkjZK+qmkvi49BIfMQ5omzzYg5+V4gujt8gNuuyrBIX0nNZOnmS1x7iYDXiVyMQUKkLbJE3f5ux6Ih88sGhwyi9RDcMifAL80s5Vuu6TgkMHkmUw1g0PeCQwHZuTSSg0OGUyeCVTR5DkNuAS4xsyOxNJTDw5Zr6Rp8nwQGAGs6vaInmpwyHomTZNnYttmdj9wfzn9ygphBMMjMiVWU3QrLDm93siUWNeMPzkxfcIXBte4Jz0jU2LNbh3DeV8cclz6qq27WfTacVPA6o5MiQXQ/tbe49KOGNz2dEcKvSmPzIl18FCyWTpfej2RObF8JojlEUEsj8icWElPg4XS64nMifXmrg/KSq8nMifWtr3JouRLrycyJ5bPBLE8IojlEUEsj0jT5HmqpDUuYudTkvq59P5uu9PlN1fmUP2nnDMrZ/IEIpOnmY01s7HAKuCZ7hXMbHmszCTgfY6aPO8B5prZacAe4EaXfiOwx6XPdeUCpGTydIaYSUAu5OoCIv8hRJE8F7j1hcCFOQNN1knL5DkU2Gtmh9x2PFpnVyRPl7/PlT+G4BtMoAYmzx4RfIPJVMPkuYsoUHHO4RSP1tkVydPlf5rgGwRSMnk6H+ByVx/gBo7OQlnstnP7fzn4BiPSNHneBsyQ1El0T5rv0ucDQ136DGBmL/vYMKRp8tzK0SDG8fQPgavK6VdWCCMYHhHE8ogglkcEsTwiiOURQSyPCGJ5RBDLI4JYHpEpsS66d0XaXegVZQ03+cxps57jkOfDwZk4sy66d4X3QkFGxNqy40DaXagImRCrFK6b8Pm0u1CUIBYwsH8Ts1vHpN2NolTVN+jKfV7SC5I2S/pNzgeYr36tg0P2EXTcNbmaTVSMqgaHdDwG3G1myyR9CueQKlK/ZsEhO3/0jVo0UxGq6huU9GWgj5ktAzCz97p/AbwM32HmqbZv8E+AvZKecZfQOZKaSqhfs+CQ1z68qpq7ryjV9g32Ab4G/B3Rl76/AEwtUr+mwSFfeWN3j+vWmmr7Bn8HrDOzrc5duwg4O5eZVD8Eh8xPtX2DvyIyc+b+NScBv4nlH1c/BIfMT1V9g2Z2mOgS+JKir32L6KveeetT4+CQfT36pVkL3+Ay4Kw8+0uqX9PgkHOuGlurpnqNR/+vqkPruOP8p3VLJsQaPKBvWen1SibEynfH8226QybE2vvBx2Wl1yuZEKtRCGJ5RBDLI4JYHhHE8ogglkcEsTwiiOURQSyPCGJ5RBDLI4JYHpGmyfNnkn4b20fu49OSdJ8LDtkh6eyk/WaR1EyejlvNbGG38pcCp7tlPNFXwceX0c+GJXWTZwJXAI+5j4OvJjLcjCyln41O2ibPu92lbq6k/i6tKzikIx44MtOkafKcBZzh0ocQRUkrmRDJM5mqmDzNbLu71B0EHuVohLSu4JCOeODILsoxeQ46IdlrkS+9XknN5Jm7DzlDZyuw0ZVZDHzbPRVOAPaZ2fbyD+0ow0/qV1Z6vZKmybPNpW0AhgGzXfoSYCvQ6cre1Ms+5p2m6tv01dRMnmY2KU8bBtxcTr+yQuZHMHz45G2OzIt1+7Mb0u5CyWRerAMfHU67CyWTebHAnzBBmRCrWIwLX54KMyFWKTEuzrrzFzXoSe/IhFhQ/Ozaf/Bw3V8OMyNWKWdXvV8OMyMWwLwp/sxyTCJTYrWOG+XFl77zkSmxANqmT0y7Cz0mc2L5TBDLI4JYHhHE8ogglkekafJsk/S6pI2Sfiqpr0uvaSRPn0jT5NkG5LwcTxC9XX7AbdcskqdPpGbyNLMlzt1kwKtELqZAAdI2eeIuf9cD8WHvopE8g28wgRpE8vwJ8EszW+m2S4rkGYJDJlPNSJ53AsOJvkcMlB7JM4ukafKcBlwCXGNmXZfXEMkzP2maPB8ERgCruj2i1zSSp0+kafJMbLvWkTx9InMjGPX+6r4QmRLr2odX1f2r+0JkRqxFr23zKuB+EpkRa87S19PuQq/JjFi/3/tBSeWaZz7HeT9+uS4nLGRGrM8NOqHkstv2fsAtT63jjkX1NWkhM2LdesmX6Nuksuo8vvrtuhIsM2K1jhvFnCu/yon9un8RqjCPr367bi6JmRELIsE2/cNk5k0ZS/8+pR96vczhypRYOVrHjeL12ZeWbPg88NHhuvgoWibFytE2fSLzpoylzyeK38teeWN36qMfmRYLorOs8x+/zsD+xe9lW3YcSPUMy7xYOTrumlzSZfGVN3anJlgQK0bb9IklfQk8LcGCWN2Y3Tqm5DOs1rMl0/QNnippjQsC+ZSkfi69v9vudPnNvT3IcmmbPpHTP3Ni0XL7Dx5m/N3LatCjiHLOrJxvEIh8g2Y21szGAquAZ/LUewyYY2ZnEvk1drj0e4C5ZnYasAe40aXfCOxx6XNduZqzbMb5JZ1h7/7xoxr0JiIV36DzWEwCclE8FxBZ2iAKDrnArS8ELsx5MmpN2/SJdfUBz7R8g0OBvc7xBMcGgOwKDuny97nyqVBPH/CsB99gj6iVybPY1NYRNQyDl5ZvcBeRRS1nmokHgOwKDunyP02CFa2WJs98j/QjTurHmtsvqmrbx2BmJS/A+UQ+wdz2d4EFBco3AeuB4W77UeBmt/4fRDYziGxpN7n1m4EH3frVwL8X69c555xj9QrQbmX8Gxda0vQN3gbMkNRJdE+a79LnA0Nd+gxgZi/72DDIGsA/2dLSYu3t7Wl3IxFJa82spRL7qqMH00AxglgeEcTyiCCWRwSxPCKI5RFBLI8IYnlEEMsjglgeEcTyiCCWRwSxPCKI5RFBLI8IYnlEEMsjGuJNsaSdwFsFigwD/lDlbuRr4xQzq4ijpyHEKoak9kq9Wk+zjXAZ9IgglkdkRayHGqGNTNyzGoWsnFkNgddiSRoiaZmkLe7v4DzlbnBltki6IZa+wn0IIDcp8DMuPT6hr91NHCy7DUkDJD0n6X8kbZL041j5qZJ2xtqelrTfY6iUDzuNBfgnYKZbnwnck1BmCLDV/R3s1ge7vBVAS0Kdmzjqt18MrO9JG8AA4AJXph+wErjUbU8F7q+l1z1t4hPv4hPy4lwCLDOz3Wa2B1gGTC5jv18CRrsJfWW1YWbvm9lyADP7iCgMeo8/NuC7WCPMbLtb/z+iAMnd6Zqc54hP3AN41F2G/j42wzJeZwSwl2jyRE/bQNIg4JvAS7Hkv5TUIWmhpJPzHGMXdS+WpBcVfVym+3JFvJybXlPuo+21ZjaGaE7z94F3JG0ETgNerFAbuXlmTwL3mdlWl/xfQLOZnUV0Ji7IVz9HWdGn08DM/iJfnqR3JY00s+2SRnJ0cnmcbUTzynKMxkXQNrNt7u8FkqYS3b/+VtJS4IdmtkrSu0Tjfrt60objIWCLmc2LHVd8guAjRPffgtT9mVWExUDu6e4Gkr80tBS4WNJg9yR3MbBUUh83czP3PZTLgI0J+/1fYJs7q8pqw+17NtHszVviFZzwOS4nFgkhL2k/0fXyaXAo0T1gC/AiMMSltwCPxMp9B+h0y1+7tBOBtUAHsAn4Z6DJ5X2SaGZmJ9FDwSs9bGM00WVzM7DOLdNc3o9cu+uB5cAZxY43jGB4hO+XwUwRxPKIIJZHBLE8wluxJF3lBkePSEp8nS7pZEnLXTS2TZK+F8ub4wZYOyQ960YYkHRubHB1vaRvFelH3sHaipP243cvHtvPJBq3W0HCYKwrMxI4262fRPSb6ctu+2KiIGAQRV67x60PiKXnfgT3KdCPvIO1lV68PbPMbLOZFfzAiJltN7Nfu/U/Ev3eGeW2X7Cjgb5W4wZYLRp8zaV/ktjwkqTrJL3qzrp/ldRkFR6sLYS3YpWLoiCT44A1CdnfAZ6PlR0vaROwAfiumR2SdCYwBTjPohiLh4Fru7WRNFhbMep6bFDSi8BnE7JuN7N8H7FO2s+ngKeBW6xbqD1JtwOHiD5yDYCZrQG+4gRaIOl54ELgHOBXbnD+BGLjhHkGaytKXYtlBQZxS8WN+z0NtJnZM93yphKNCV5oCUM5ZrZZ0nvAnxLFnVpgZrPyNHXcYG2laejLoHs/NR/YbGb3dsubTPRa5HJzXyV36ae6swRJpwBnAG8SXdqujL36H+Ly8w7WVpy0n+p68TT4LaKXfAeBd4GlLv1zwBK3/udEDwgdHB1I/brL6yR6YZhLz73Gv55ogHUd0cNCa6zNKS69g2gQeAIFBmsrvYSBXI9o6MtgoxHE8ogglkcEsTwiiOURQSyPCGJ5RBDLI/4fw+PmyZ/tteMAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "gdf.plot()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>shape_id</th>\n",
       "      <th>shape_pt_lat</th>\n",
       "      <th>shape_pt_lon</th>\n",
       "      <th>shape_pt_sequence</th>\n",
       "      <th>shape_dist_traveled</th>\n",
       "      <th>coordinates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>113213</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601669</td>\n",
       "      <td>-122.330170</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>POINT (-122.33017 47.6016693)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113214</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601719</td>\n",
       "      <td>-122.330269</td>\n",
       "      <td>2</td>\n",
       "      <td>30.0</td>\n",
       "      <td>POINT (-122.330269 47.6017189)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113215</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601761</td>\n",
       "      <td>-122.330391</td>\n",
       "      <td>3</td>\n",
       "      <td>64.5</td>\n",
       "      <td>POINT (-122.330391 47.6017609)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113216</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601830</td>\n",
       "      <td>-122.330498</td>\n",
       "      <td>4</td>\n",
       "      <td>100.1</td>\n",
       "      <td>POINT (-122.330498 47.6018295)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113217</th>\n",
       "      <td>11675003</td>\n",
       "      <td>47.601936</td>\n",
       "      <td>-122.330650</td>\n",
       "      <td>5</td>\n",
       "      <td>153.5</td>\n",
       "      <td>POINT (-122.33065 47.6019363)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        shape_id  shape_pt_lat  shape_pt_lon  shape_pt_sequence  \\\n",
       "113213  11675003     47.601669   -122.330170                  1   \n",
       "113214  11675003     47.601719   -122.330269                  2   \n",
       "113215  11675003     47.601761   -122.330391                  3   \n",
       "113216  11675003     47.601830   -122.330498                  4   \n",
       "113217  11675003     47.601936   -122.330650                  5   \n",
       "\n",
       "        shape_dist_traveled                     coordinates  \n",
       "113213                  0.0   POINT (-122.33017 47.6016693)  \n",
       "113214                 30.0  POINT (-122.330269 47.6017189)  \n",
       "113215                 64.5  POINT (-122.330391 47.6017609)  \n",
       "113216                100.1  POINT (-122.330498 47.6018295)  \n",
       "113217                153.5   POINT (-122.33065 47.6019363)  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "stops_df = pd.read_csv('./data/source/gtfs_20180118/stops.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stops_df.head()"
   ]
  },
  {
   "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>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>12481</th>\n",
       "      <td>102615</td>\n",
       "      <td>66726</td>\n",
       "      <td>34815721</td>\n",
       "      <td>Aurora Village Transit Center</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>4759415</td>\n",
       "      <td>11675003</td>\n",
       "      <td>1</td>\n",
       "      <td>112.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       route_id  service_id   trip_id                   trip_headsign  \\\n",
       "12481    102615       66726  34815721  Aurora Village Transit Center    \n",
       "\n",
       "      trip_short_name  direction_id  block_id  shape_id  peak_flag  fare_id  \n",
       "12481           LOCAL             0   4759415  11675003          1    112.0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trips_df[trips_df.trip_id == 34815721]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# NEXT: to get stops for a route: route -> trips -> stop_times -> stops"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "e_trips = trips_df[trips_df.route_id == 102615]"
   ]
  },
  {
   "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>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>12478</th>\n",
       "      <td>102615</td>\n",
       "      <td>66726</td>\n",
       "      <td>34815718</td>\n",
       "      <td>Aurora Village Transit Center</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>4759418</td>\n",
       "      <td>11675003</td>\n",
       "      <td>1</td>\n",
       "      <td>112.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12479</th>\n",
       "      <td>102615</td>\n",
       "      <td>66726</td>\n",
       "      <td>34815719</td>\n",
       "      <td>Aurora Village Transit Center</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>4759590</td>\n",
       "      <td>11675003</td>\n",
       "      <td>0</td>\n",
       "      <td>102.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12481</th>\n",
       "      <td>102615</td>\n",
       "      <td>66726</td>\n",
       "      <td>34815721</td>\n",
       "      <td>Aurora Village Transit Center</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>4759415</td>\n",
       "      <td>11675003</td>\n",
       "      <td>1</td>\n",
       "      <td>112.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12482</th>\n",
       "      <td>102615</td>\n",
       "      <td>66726</td>\n",
       "      <td>34815722</td>\n",
       "      <td>Aurora Village Transit Center</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>4759519</td>\n",
       "      <td>11675003</td>\n",
       "      <td>1</td>\n",
       "      <td>112.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12483</th>\n",
       "      <td>102615</td>\n",
       "      <td>66726</td>\n",
       "      <td>34815723</td>\n",
       "      <td>Aurora Village Transit Center</td>\n",
       "      <td>LOCAL</td>\n",
       "      <td>0</td>\n",
       "      <td>4759412</td>\n",
       "      <td>11675003</td>\n",
       "      <td>0</td>\n",
       "      <td>102.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       route_id  service_id   trip_id                   trip_headsign  \\\n",
       "12478    102615       66726  34815718  Aurora Village Transit Center    \n",
       "12479    102615       66726  34815719  Aurora Village Transit Center    \n",
       "12481    102615       66726  34815721  Aurora Village Transit Center    \n",
       "12482    102615       66726  34815722  Aurora Village Transit Center    \n",
       "12483    102615       66726  34815723  Aurora Village Transit Center    \n",
       "\n",
       "      trip_short_name  direction_id  block_id  shape_id  peak_flag  fare_id  \n",
       "12478           LOCAL             0   4759418  11675003          1    112.0  \n",
       "12479           LOCAL             0   4759590  11675003          0    102.0  \n",
       "12481           LOCAL             0   4759415  11675003          1    112.0  \n",
       "12482           LOCAL             0   4759519  11675003          1    112.0  \n",
       "12483           LOCAL             0   4759412  11675003          0    102.0  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e_trips[e_trips.direction_id == 0].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "stop_times_df = pd.read_csv('./data/source/gtfs_20180118/stop_times.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "e_northbound_trip_ids = e_trips[e_trips.direction_id == 0]['trip_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "e_northbound_stop_times = stop_times_df[stop_times_df['trip_id'].isin(e_northbound_trip_ids)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 1610,   538,   558,   578,   600,   605,  6235,  6300,  6320,\n",
       "       75409,  7630,  7670,  7690,  7710,  7730,  7750,  7770,  7790,\n",
       "        7810,  7850,  7880,  7900,  7912, 75820, 75830, 75840, 75850,\n",
       "       75860, 75870, 75881, 75890, 75901, 75910, 75932, 16103])"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e_north_stop_ids = e_northbound_stop_times['stop_id'].unique()\n",
    "e_north_stop_ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "e_north_stops = stops_df[stops_df.stop_id.isin(e_north_stop_ids)]\n",
    "e_north_stops = e_north_stops.loc[:, ['stop_id', 'stop_lat', 'stop_lon']]\n",
    "e_north_stops.set_index('stop_id', inplace=True)\n",
    "e_north_stops['coordinates'] = list(zip(e_north_stops.stop_lon, e_north_stops.stop_lat))\n",
    "e_north_stops['coordinates'] = e_north_stops['coordinates'].apply(Point)\n",
    "e_north_stops = e_north_stops.loc[:, ['coordinates']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>coordinates</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stop_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1610</th>\n",
       "      <td>POINT (-122.330101 47.60173029999999)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16103</th>\n",
       "      <td>POINT (-122.340881 47.77448270000001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>POINT (-122.332588 47.6041908)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558</th>\n",
       "      <td>POINT (-122.335213 47.6070518)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>578</th>\n",
       "      <td>POINT (-122.338356 47.61048510000001)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   coordinates\n",
       "stop_id                                       \n",
       "1610     POINT (-122.330101 47.60173029999999)\n",
       "16103    POINT (-122.340881 47.77448270000001)\n",
       "538             POINT (-122.332588 47.6041908)\n",
       "558             POINT (-122.335213 47.6070518)\n",
       "578      POINT (-122.338356 47.61048510000001)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e_north_stops.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12478    34815718\n",
       "12479    34815719\n",
       "12481    34815721\n",
       "12482    34815722\n",
       "12483    34815723\n",
       "Name: trip_id, dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e_northbound_trip_ids.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "e_northbound_locations = locations_df[locations_df.trip_id.isin(e_northbound_trip_ids)]\n",
    "e_northbound_locations = e_northbound_locations[['route_id', 'trip_id', 'vehicle_id', 'vehicle_lat', 'vehicle_long']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(101360, 5)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e_northbound_locations.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(197432, 8)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# that's good, our northbound e locations is approx 50% of previously calculated all e locations size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>trip_id</th>\n",
       "      <th>vehicle_id</th>\n",
       "      <th>vehicle_lat</th>\n",
       "      <th>vehicle_long</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp_utc</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:58:46</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922164</td>\n",
       "      <td>6211</td>\n",
       "      <td>47.694817</td>\n",
       "      <td>-122.344574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:59:04</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922180</td>\n",
       "      <td>6088</td>\n",
       "      <td>47.618118</td>\n",
       "      <td>-122.343559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:59:05</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922131</td>\n",
       "      <td>6054</td>\n",
       "      <td>47.774178</td>\n",
       "      <td>-122.342407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:59:22</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922163</td>\n",
       "      <td>6062</td>\n",
       "      <td>47.755703</td>\n",
       "      <td>-122.345726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01 00:00:08</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922131</td>\n",
       "      <td>6054</td>\n",
       "      <td>47.774178</td>\n",
       "      <td>-122.342407</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     route_id   trip_id  vehicle_id  vehicle_lat  vehicle_long\n",
       "timestamp_utc                                                                 \n",
       "2017-12-31 23:58:46    102615  34922164        6211    47.694817   -122.344574\n",
       "2017-12-31 23:59:04    102615  34922180        6088    47.618118   -122.343559\n",
       "2017-12-31 23:59:05    102615  34922131        6054    47.774178   -122.342407\n",
       "2017-12-31 23:59:22    102615  34922163        6062    47.755703   -122.345726\n",
       "2018-01-01 00:00:08    102615  34922131        6054    47.774178   -122.342407"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e_northbound_locations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# now we need to find closest_stop_id from e_north_stops for all e_northbound_locations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>coordinates</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stop_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1610</th>\n",
       "      <td>POINT (-122.330101 47.60173029999999)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16103</th>\n",
       "      <td>POINT (-122.340881 47.77448270000001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>POINT (-122.332588 47.6041908)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558</th>\n",
       "      <td>POINT (-122.335213 47.6070518)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>578</th>\n",
       "      <td>POINT (-122.338356 47.61048510000001)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   coordinates\n",
       "stop_id                                       \n",
       "1610     POINT (-122.330101 47.60173029999999)\n",
       "16103    POINT (-122.340881 47.77448270000001)\n",
       "538             POINT (-122.332588 47.6041908)\n",
       "558             POINT (-122.335213 47.6070518)\n",
       "578      POINT (-122.338356 47.61048510000001)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e_north_stops.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "crs = {'init': 'epsg:4326'}\n",
    "e_north_stops_gdf = gpd.GeoDataFrame(e_north_stops, crs=crs).set_geometry('coordinates')\n",
    "\n",
    "def find_closest_stop_id(pnt):\n",
    "    nearest = nearest_points(e_north_stops_gdf.geometry.unary_union, pnt)\n",
    "    closest_coords = nearest[0]\n",
    "    closest_stop = e_north_stops_gdf[e_north_stops_gdf['coordinates'] == closest_coords]\n",
    "    return closest_stop.index[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7750"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pnt = Point(-122.344574, 47.694817)\n",
    "find_closest_stop_id(pnt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "e_northbound_locations['coordinates'] = e_northbound_locations.apply(lambda x: Point(x['vehicle_long'], x['vehicle_lat']), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 101360/101360 [02:10<00:00, 776.80it/s]\n"
     ]
    }
   ],
   "source": [
    "# output cleared as tqdm generated 1000s of lines\n",
    "e_northbound_locations['closest_stop_id'] = e_northbound_locations.progress_apply(lambda x: find_closest_stop_id(x['coordinates']), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>trip_id</th>\n",
       "      <th>vehicle_id</th>\n",
       "      <th>vehicle_lat</th>\n",
       "      <th>vehicle_long</th>\n",
       "      <th>coordinates</th>\n",
       "      <th>closest_stop_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp_utc</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:58:46</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922164</td>\n",
       "      <td>6211</td>\n",
       "      <td>47.694817</td>\n",
       "      <td>-122.344574</td>\n",
       "      <td>POINT (-122.3445739746094 47.69481658935547)</td>\n",
       "      <td>7750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:59:04</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922180</td>\n",
       "      <td>6088</td>\n",
       "      <td>47.618118</td>\n",
       "      <td>-122.343559</td>\n",
       "      <td>POINT (-122.3435592651367 47.61811828613281)</td>\n",
       "      <td>6235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:59:05</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922131</td>\n",
       "      <td>6054</td>\n",
       "      <td>47.774178</td>\n",
       "      <td>-122.342407</td>\n",
       "      <td>POINT (-122.3424072265625 47.77417755126953)</td>\n",
       "      <td>16103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:59:22</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922163</td>\n",
       "      <td>6062</td>\n",
       "      <td>47.755703</td>\n",
       "      <td>-122.345726</td>\n",
       "      <td>POINT (-122.3457260131836 47.75570297241211)</td>\n",
       "      <td>75881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01 00:00:08</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922131</td>\n",
       "      <td>6054</td>\n",
       "      <td>47.774178</td>\n",
       "      <td>-122.342407</td>\n",
       "      <td>POINT (-122.3424072265625 47.77417755126953)</td>\n",
       "      <td>16103</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     route_id   trip_id  vehicle_id  vehicle_lat  \\\n",
       "timestamp_utc                                                      \n",
       "2017-12-31 23:58:46    102615  34922164        6211    47.694817   \n",
       "2017-12-31 23:59:04    102615  34922180        6088    47.618118   \n",
       "2017-12-31 23:59:05    102615  34922131        6054    47.774178   \n",
       "2017-12-31 23:59:22    102615  34922163        6062    47.755703   \n",
       "2018-01-01 00:00:08    102615  34922131        6054    47.774178   \n",
       "\n",
       "                     vehicle_long  \\\n",
       "timestamp_utc                       \n",
       "2017-12-31 23:58:46   -122.344574   \n",
       "2017-12-31 23:59:04   -122.343559   \n",
       "2017-12-31 23:59:05   -122.342407   \n",
       "2017-12-31 23:59:22   -122.345726   \n",
       "2018-01-01 00:00:08   -122.342407   \n",
       "\n",
       "                                                      coordinates  \\\n",
       "timestamp_utc                                                       \n",
       "2017-12-31 23:58:46  POINT (-122.3445739746094 47.69481658935547)   \n",
       "2017-12-31 23:59:04  POINT (-122.3435592651367 47.61811828613281)   \n",
       "2017-12-31 23:59:05  POINT (-122.3424072265625 47.77417755126953)   \n",
       "2017-12-31 23:59:22  POINT (-122.3457260131836 47.75570297241211)   \n",
       "2018-01-01 00:00:08  POINT (-122.3424072265625 47.77417755126953)   \n",
       "\n",
       "                     closest_stop_id  \n",
       "timestamp_utc                         \n",
       "2017-12-31 23:58:46             7750  \n",
       "2017-12-31 23:59:04             6235  \n",
       "2017-12-31 23:59:05            16103  \n",
       "2017-12-31 23:59:22            75881  \n",
       "2018-01-01 00:00:08            16103  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e_northbound_locations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "e_northbound_locations = e_northbound_locations[['route_id', 'trip_id', 'vehicle_id','coordinates', 'closest_stop_id']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>trip_id</th>\n",
       "      <th>vehicle_id</th>\n",
       "      <th>coordinates</th>\n",
       "      <th>closest_stop_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp_utc</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:58:46</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922164</td>\n",
       "      <td>6211</td>\n",
       "      <td>POINT (-122.3445739746094 47.69481658935547)</td>\n",
       "      <td>7750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:59:04</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922180</td>\n",
       "      <td>6088</td>\n",
       "      <td>POINT (-122.3435592651367 47.61811828613281)</td>\n",
       "      <td>6235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:59:05</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922131</td>\n",
       "      <td>6054</td>\n",
       "      <td>POINT (-122.3424072265625 47.77417755126953)</td>\n",
       "      <td>16103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 23:59:22</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922163</td>\n",
       "      <td>6062</td>\n",
       "      <td>POINT (-122.3457260131836 47.75570297241211)</td>\n",
       "      <td>75881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01 00:00:08</th>\n",
       "      <td>102615</td>\n",
       "      <td>34922131</td>\n",
       "      <td>6054</td>\n",
       "      <td>POINT (-122.3424072265625 47.77417755126953)</td>\n",
       "      <td>16103</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     route_id   trip_id  vehicle_id  \\\n",
       "timestamp_utc                                         \n",
       "2017-12-31 23:58:46    102615  34922164        6211   \n",
       "2017-12-31 23:59:04    102615  34922180        6088   \n",
       "2017-12-31 23:59:05    102615  34922131        6054   \n",
       "2017-12-31 23:59:22    102615  34922163        6062   \n",
       "2018-01-01 00:00:08    102615  34922131        6054   \n",
       "\n",
       "                                                      coordinates  \\\n",
       "timestamp_utc                                                       \n",
       "2017-12-31 23:58:46  POINT (-122.3445739746094 47.69481658935547)   \n",
       "2017-12-31 23:59:04  POINT (-122.3435592651367 47.61811828613281)   \n",
       "2017-12-31 23:59:05  POINT (-122.3424072265625 47.77417755126953)   \n",
       "2017-12-31 23:59:22  POINT (-122.3457260131836 47.75570297241211)   \n",
       "2018-01-01 00:00:08  POINT (-122.3424072265625 47.77417755126953)   \n",
       "\n",
       "                     closest_stop_id  \n",
       "timestamp_utc                         \n",
       "2017-12-31 23:58:46             7750  \n",
       "2017-12-31 23:59:04             6235  \n",
       "2017-12-31 23:59:05            16103  \n",
       "2017-12-31 23:59:22            75881  \n",
       "2018-01-01 00:00:08            16103  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e_northbound_locations.index.names = ['timestamp_utc']\n",
    "e_northbound_locations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/aps/miniconda3/envs/realtime_buses/lib/python3.6/site-packages/pandas/core/generic.py:1996: PerformanceWarning: \n",
      "your performance may suffer as PyTables will pickle object types that it cannot\n",
      "map directly to c-types [inferred_type->mixed,key->block1_values] [items->['coordinates']]\n",
      "\n",
      "  return pytables.to_hdf(path_or_buf, key, self, **kwargs)\n"
     ]
    }
   ],
   "source": [
    "e_northbound_locations.to_hdf('./data/intermed/e_northbound_locations_2018-01.h5', key='e_northbound_locations_gdf', mode='w')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}