Data_Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "from shapely.geometry import Polygon, Point, LineString\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read shapefiles into variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read shape files into geopandas\n",
    "right_half_tri_SF = gpd.read_file(\"shapefiles/right_half_triangle/right_half_triangle.shp\")[['Name', 'geometry']]\n",
    "left_half_tri_SF = gpd.read_file(\"shapefiles/left_half_triangle/left_half_triangle.shp\")[['Name', 'geometry']]\n",
    "island_SF = gpd.read_file(\"shapefiles/island/island.shp\")[['Name', 'geometry']]\n",
    "newport_SF = gpd.read_file(\"shapefiles/inundation_map/inundation_map.shp\")[['Name', 'geometry']]\n",
    "# Read Street Shape Files\n",
    "balboa_blvd = gpd.read_file(\"shapefiles/Streets/Balboa/Balboa_Blvd-polygon.shp\")[['Name', 'geometry']]\n",
    "w_balboa_blvd = gpd.read_file(\"shapefiles/Streets/W_Balboa/W_Balboa-polygon.shp\")[['Name', 'geometry']]\n",
    "newport = gpd.read_file(\"shapefiles/Streets/Newport/Newport_Blvd-polygon.shp\")[['Name', 'geometry']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "section_shapefile_list = [right_half_tri_SF,left_half_tri_SF,island_SF,newport_SF]\n",
    "street_shapefile_list = [balboa_blvd,w_balboa_blvd,newport]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read data for each day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assign data for each day\n",
    "d_1_07012018 = 'Data/Probe_Data/2018_07_01_NewportBeach_basic_probe.csv'\n",
    "d_2_07022018 = 'Data/Probe_Data/2018_07_02_NewportBeach_basic_probe.csv'\n",
    "d_3_07032018 = 'Data/Probe_Data/2018_07_03_NewportBeach_basic_probe.csv'\n",
    "d_4_07042018 = 'Data/Probe_Data/2018_07_04_NewportBeach_basic_probe.csv'\n",
    "d_5_07052018 = 'Data/Probe_Data/2018_07_05_NewportBeach_basic_probe.csv'\n",
    "d_6_07062018 = 'Data/Probe_Data/2018_07_06_NewportBeach_basic_probe.csv'\n",
    "d_7_07072018 = 'Data/Probe_Data/2018_07_07_NewportBeach_basic_probe.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>PROBE_ID</th>\n",
       "      <th>SAMPLE_DATE</th>\n",
       "      <th>LAT</th>\n",
       "      <th>LONG</th>\n",
       "      <th>HEADING</th>\n",
       "      <th>SPEED</th>\n",
       "      <th>PROBE_DATA_PROVIDER</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>LOCAL_TIME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>480</th>\n",
       "      <td>285613c2-f71d-55f6-bb2b-8f16528c0a2c</td>\n",
       "      <td>2018-07-04 00:00:00</td>\n",
       "      <td>33.615262</td>\n",
       "      <td>-117.927329</td>\n",
       "      <td>34.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>CONSUMER14</td>\n",
       "      <td>413977.562793</td>\n",
       "      <td>3.719884e+06</td>\n",
       "      <td>2018-07-03 17:00:00 -0700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>479</th>\n",
       "      <td>285613c2-f71d-55f6-bb2b-8f16528c0a2c</td>\n",
       "      <td>2018-07-04 00:00:01</td>\n",
       "      <td>33.615269</td>\n",
       "      <td>-117.927325</td>\n",
       "      <td>19.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>CONSUMER14</td>\n",
       "      <td>413977.940815</td>\n",
       "      <td>3.719885e+06</td>\n",
       "      <td>2018-07-03 17:00:01 -0700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>285613c2-f71d-55f6-bb2b-8f16528c0a2c</td>\n",
       "      <td>2018-07-04 00:00:02</td>\n",
       "      <td>33.615287</td>\n",
       "      <td>-117.927314</td>\n",
       "      <td>19.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>CONSUMER14</td>\n",
       "      <td>413978.979134</td>\n",
       "      <td>3.719886e+06</td>\n",
       "      <td>2018-07-03 17:00:02 -0700</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 PROBE_ID          SAMPLE_DATE        LAT  \\\n",
       "480  285613c2-f71d-55f6-bb2b-8f16528c0a2c  2018-07-04 00:00:00  33.615262   \n",
       "479  285613c2-f71d-55f6-bb2b-8f16528c0a2c  2018-07-04 00:00:01  33.615269   \n",
       "445  285613c2-f71d-55f6-bb2b-8f16528c0a2c  2018-07-04 00:00:02  33.615287   \n",
       "\n",
       "           LONG  HEADING  SPEED PROBE_DATA_PROVIDER              X  \\\n",
       "480 -117.927329     34.0   14.0          CONSUMER14  413977.562793   \n",
       "479 -117.927325     19.0   12.0          CONSUMER14  413977.940815   \n",
       "445 -117.927314     19.0   17.0          CONSUMER14  413978.979134   \n",
       "\n",
       "                Y                 LOCAL_TIME  \n",
       "480  3.719884e+06  2018-07-03 17:00:00 -0700  \n",
       "479  3.719885e+06  2018-07-03 17:00:01 -0700  \n",
       "445  3.719886e+06  2018-07-03 17:00:02 -0700  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv(d_4_07042018,\n",
    "                   names = [\"PROBE_ID\",\"SAMPLE_DATE\", \"LAT\", \"LONG\", \"HEADING\",\\\n",
    "                           \"SPEED\",\"PROBE_DATA_PROVIDER\",\"X\",\"Y\", \"LOCAL_TIME\"])\n",
    "# This now sorts in date order\n",
    "data.sort_values(by='SAMPLE_DATE', inplace=True, ascending=True) \n",
    "data.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Analyze Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Analyze_Data:\n",
    "    \n",
    "    def __init__(self, data_df,section_shapefile_list,street_shapefile_list):\n",
    "        # Reading data dataframe into class\n",
    "        self.data = data_df \n",
    "        \n",
    "        # Read shapefiles into class as lists\n",
    "        self.section = section_shapefile_list\n",
    "        self.street = street_shapefile_list\n",
    "        \n",
    "    \n",
    "    def plot(self):\n",
    "        # Plot Data\n",
    "        plt.figure(figsize = (12,8))\n",
    "        plt.scatter(self.data.LONG, self.data.LAT, s = 0.5, c = 'k')\n",
    "        plt.show()\n",
    "    \n",
    "    \n",
    "    def create_point(self,row):\n",
    "        # Helper function for turning coordinates into shapely points\n",
    "        return Point(row['LONG'],row['LAT'])\n",
    "    \n",
    "    def append_points(self):\n",
    "\n",
    "        self.data['coord'] = self.data.apply(self.create_point, axis=1)\n",
    "        self.data['section'] = 'N/A'\n",
    "        \n",
    "        return self.data\n",
    "        \n",
    "    def get_section(self):\n",
    "        # For separating data into sections based on defined shapefiles\n",
    "        \n",
    "        self.append_points()\n",
    "        \n",
    "        section_list = []\n",
    "        \n",
    "        for i in self.data.index.values:\n",
    "            if self.section[0]['geometry'][0].contains(self.data['coord'][i]):\n",
    "                section_list.append('right_half_tri')\n",
    "            elif self.section[1]['geometry'][0].contains(self.data['coord'][i]):\n",
    "                section_list.append('left_half_tri')\n",
    "            elif self.section[2]['geometry'][0].contains(self.data['coord'][i]):\n",
    "                section_list.append('island')\n",
    "            elif self.section[3]['geometry'][0].contains(self.data['coord'][i]):\n",
    "                section_list.append('rest_of_new_port')\n",
    "            else:\n",
    "                section_list.append('N/A')\n",
    "        \n",
    "        self.data['section'] = section_list\n",
    "        \n",
    "        return self.data\n",
    "    \n",
    "    def get_street(self):\n",
    "        # For separating data into streets based on defined shapefiles\n",
    "        \n",
    "        self.get_section()\n",
    "        street_list = []\n",
    "\n",
    "        for i in self.data.index.values:\n",
    "            if self.street[0]['geometry'][0].contains(self.data['coord'][i]):\n",
    "                street_list.append('balboa')\n",
    "            elif self.street[1]['geometry'][0].contains(self.data['coord'][i]):\n",
    "                street_list.append('w_balboa')\n",
    "            elif self.street[2]['geometry'][0].contains(self.data['coord'][i]):\n",
    "                street_list.append('newport')\n",
    "            else:\n",
    "                street_list.append('N/A')\n",
    "\n",
    "        self.data['street'] = street_list\n",
    "        \n",
    "        return self.data\n",
    "    \n",
    "    def clean_data(self):\n",
    "        # Remove useless columns and set timezone\n",
    "        self.get_street()\n",
    "        self.data_cleaned = self.data[self.data.section != 'N/A'][['PROBE_ID','LAT','LONG',\\\n",
    "                                                            'HEADING',\\\n",
    "                                                            'SPEED',\\\n",
    "                                                            'PROBE_DATA_PROVIDER',\\\n",
    "                                                            'LOCAL_TIME',\\\n",
    "                                                            'coord',\\\n",
    "                                                            'section','street']]\n",
    "        \n",
    "        self.data_cleaned['LOCAL_TIME'] = pd.to_datetime(self.data_cleaned['LOCAL_TIME'])\\\n",
    "                                        .dt.tz_localize('UTC').dt.tz_convert('America/Los_Angeles')\n",
    "        # Separate to hours\n",
    "        self.data_cleaned['HOUR'] = self.data_cleaned['LOCAL_TIME'].dt.hour  \n",
    "        \n",
    "        return self.data_cleaned\n",
    "    \n",
    "    def clean_data_add_heading(self):\n",
    "        # Add heading information per probe\n",
    "        self.clean_data()\n",
    "        \n",
    "        heading_fixed = []\n",
    "        \n",
    "        for i in self.data_cleaned.index.values:\n",
    "            if self.data_cleaned['HEADING'][i] >180:\n",
    "                heading_fixed.append(-(360 - self.data_cleaned['HEADING'][i]))\n",
    "            else:\n",
    "                heading_fixed.append(self.data_cleaned['HEADING'][i])\n",
    "        \n",
    "        self.data_cleaned['HEADING_FIXED'] = heading_fixed\n",
    "        \n",
    "        return self.data_cleaned\n",
    "\n",
    "    \n",
    "    def probe_count(self, section):\n",
    "        # For counting number of uniuqe probes\n",
    "        self.clean_data_add_heading()\n",
    "        uniq_probe = self.data_cleaned.loc[self.data_cleaned['section'] == section].PROBE_ID.unique()\n",
    "\n",
    "        return len(uniq_probe)\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "class road_specific_analysis():\n",
    "    \n",
    "    def __init__(self, data_cleaned, street_choice):\n",
    "        self.data = data_cleaned\n",
    "        self.street = street_choice\n",
    "    \n",
    "    def road_df(self):\n",
    "        # Filter and create dataframe based on road choice\n",
    "        self.data\n",
    "        self.road = self.data.loc[self.data['street']== self.street].copy()\n",
    "        \n",
    "        return self.road\n",
    "    \n",
    "    def get_heading(self, df, i, lower_head, upper_head):\n",
    "        \n",
    "        # Helper function for differentiating heading\n",
    "        head_df = df.loc[(df['HOUR']==i) & \\\n",
    "                         ((df['HEADING_FIXED'] >= lower_head) | \\\n",
    "                         (df['HEADING_FIXED'] <= upper_head))][['PROBE_ID','SPEED']]\n",
    "        \n",
    "        # This removes all 0 speed instances\n",
    "        head_df = head_df[head_df['SPEED'] != 0]\n",
    "        unique_probe_list = list(set(head_df['PROBE_ID']))\n",
    "        \n",
    "        return head_df, unique_probe_list\n",
    "    \n",
    "    def get_heading_info(self, limits, speed_limit, trans_type):\n",
    "        \n",
    "        '''\n",
    "        Inputs:\n",
    "            df - cleaned up dataframe with hours and streets \n",
    "            limits - list of heading limits, [lower, higher]\n",
    "            trans_type - looking for 'vehicle' or 'pedestrian'\n",
    "            \n",
    "        Outputs:\n",
    "            head_speed - list of 24 north heading speeds averaged over an hour\n",
    "            head_unique_probe - list of 24 counts of unique probe ids in that hour - vehicles\n",
    "        '''\n",
    "        \n",
    "        self.road_df()\n",
    "        \n",
    "        head_speed = []\n",
    "        head_unique_probe = []\n",
    "        \n",
    "        for i in range(24):\n",
    "        # Analyze for 24 hours\n",
    "            head_df, unique_probe_list = self.get_heading(self.road, i, limits[0], limits[1])\n",
    "            \n",
    "            count = 0\n",
    "            speed = []\n",
    "            \n",
    "            for j in unique_probe_list:\n",
    "\n",
    "                probe_df = head_df.loc[head_df['PROBE_ID'] == j]\n",
    "                mean_speed = np.mean(probe_df['SPEED'])\n",
    "\n",
    "                if trans_type == 'Vehicle':\n",
    "\n",
    "                    if mean_speed >= speed_limit:\n",
    "                        count += 1\n",
    "                        speed.append(mean_speed)\n",
    "\n",
    "                elif trans_type == 'Pedestrian':\n",
    "\n",
    "                    if mean_speed <= speed_limit:\n",
    "                        count += 1\n",
    "                        speed.append(mean_speed)\n",
    "                    \n",
    "        \n",
    "            head_unique_probe.append(count)\n",
    "\n",
    "            if not speed:\n",
    "                head_speed.append(0)\n",
    "            else:\n",
    "                head_speed.append(np.mean(speed))\n",
    "            \n",
    "        return head_speed, head_unique_probe\n",
    "            "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# For getting trajectory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>PROBE_ID</th>\n",
       "      <th>LAT</th>\n",
       "      <th>LONG</th>\n",
       "      <th>HEADING</th>\n",
       "      <th>SPEED</th>\n",
       "      <th>PROBE_DATA_PROVIDER</th>\n",
       "      <th>LOCAL_TIME</th>\n",
       "      <th>coord</th>\n",
       "      <th>section</th>\n",
       "      <th>street</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>HEADING_FIXED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>480</th>\n",
       "      <td>285613c2-f71d-55f6-bb2b-8f16528c0a2c</td>\n",
       "      <td>33.615262</td>\n",
       "      <td>-117.927329</td>\n",
       "      <td>34.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>CONSUMER14</td>\n",
       "      <td>2018-07-03 17:00:00-07:00</td>\n",
       "      <td>POINT (-117.927329 33.615262)</td>\n",
       "      <td>right_half_tri</td>\n",
       "      <td>N/A</td>\n",
       "      <td>17</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>479</th>\n",
       "      <td>285613c2-f71d-55f6-bb2b-8f16528c0a2c</td>\n",
       "      <td>33.615269</td>\n",
       "      <td>-117.927325</td>\n",
       "      <td>19.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>CONSUMER14</td>\n",
       "      <td>2018-07-03 17:00:01-07:00</td>\n",
       "      <td>POINT (-117.927325 33.615269)</td>\n",
       "      <td>right_half_tri</td>\n",
       "      <td>N/A</td>\n",
       "      <td>17</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>285613c2-f71d-55f6-bb2b-8f16528c0a2c</td>\n",
       "      <td>33.615287</td>\n",
       "      <td>-117.927314</td>\n",
       "      <td>19.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>CONSUMER14</td>\n",
       "      <td>2018-07-03 17:00:02-07:00</td>\n",
       "      <td>POINT (-117.927314 33.615287)</td>\n",
       "      <td>right_half_tri</td>\n",
       "      <td>N/A</td>\n",
       "      <td>17</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>494</th>\n",
       "      <td>6c247e2bf8f447b2b31b7450261a1e3c</td>\n",
       "      <td>33.618990</td>\n",
       "      <td>-117.930160</td>\n",
       "      <td>189.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>CONSUMER21</td>\n",
       "      <td>2018-07-03 17:00:03-07:00</td>\n",
       "      <td>POINT (-117.93016 33.61899)</td>\n",
       "      <td>right_half_tri</td>\n",
       "      <td>newport</td>\n",
       "      <td>17</td>\n",
       "      <td>-171.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2276</th>\n",
       "      <td>285613c2-f71d-55f6-bb2b-8f16528c0a2c</td>\n",
       "      <td>33.615319</td>\n",
       "      <td>-117.927299</td>\n",
       "      <td>18.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>CONSUMER14</td>\n",
       "      <td>2018-07-03 17:00:03-07:00</td>\n",
       "      <td>POINT (-117.927299 33.615319)</td>\n",
       "      <td>right_half_tri</td>\n",
       "      <td>N/A</td>\n",
       "      <td>17</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  PROBE_ID        LAT        LONG  HEADING  \\\n",
       "480   285613c2-f71d-55f6-bb2b-8f16528c0a2c  33.615262 -117.927329     34.0   \n",
       "479   285613c2-f71d-55f6-bb2b-8f16528c0a2c  33.615269 -117.927325     19.0   \n",
       "445   285613c2-f71d-55f6-bb2b-8f16528c0a2c  33.615287 -117.927314     19.0   \n",
       "494       6c247e2bf8f447b2b31b7450261a1e3c  33.618990 -117.930160    189.0   \n",
       "2276  285613c2-f71d-55f6-bb2b-8f16528c0a2c  33.615319 -117.927299     18.0   \n",
       "\n",
       "      SPEED PROBE_DATA_PROVIDER                LOCAL_TIME  \\\n",
       "480    14.0          CONSUMER14 2018-07-03 17:00:00-07:00   \n",
       "479    12.0          CONSUMER14 2018-07-03 17:00:01-07:00   \n",
       "445    17.0          CONSUMER14 2018-07-03 17:00:02-07:00   \n",
       "494     4.0          CONSUMER21 2018-07-03 17:00:03-07:00   \n",
       "2276   20.0          CONSUMER14 2018-07-03 17:00:03-07:00   \n",
       "\n",
       "                              coord         section   street  HOUR  \\\n",
       "480   POINT (-117.927329 33.615262)  right_half_tri      N/A    17   \n",
       "479   POINT (-117.927325 33.615269)  right_half_tri      N/A    17   \n",
       "445   POINT (-117.927314 33.615287)  right_half_tri      N/A    17   \n",
       "494     POINT (-117.93016 33.61899)  right_half_tri  newport    17   \n",
       "2276  POINT (-117.927299 33.615319)  right_half_tri      N/A    17   \n",
       "\n",
       "      HEADING_FIXED  \n",
       "480            34.0  \n",
       "479            19.0  \n",
       "445            19.0  \n",
       "494          -171.0  \n",
       "2276           18.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traj = Analyze_Data(data, section_shapefile_list, street_shapefile_list)\n",
    "traj_df = traj.clean_data_add_heading()\n",
    "traj_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "uniq_ID = set(traj_df.PROBE_ID)\n",
    "\n",
    "# Create empty dataframe\n",
    "columns = ['PROBE_ID','Route_Num','Start_Section','End_Section','Time_Start','Time_End']\n",
    "traj_route_df = pd.DataFrame(columns=columns)\n",
    "\n",
    "for ID in uniq_ID:\n",
    "    \n",
    "    probe_df = traj_df.loc[traj_df.PROBE_ID == ID]\n",
    "    \n",
    "    route = [probe_df.section.iloc[0]]\n",
    "    route_count = 0\n",
    "    route_time = [probe_df.LOCAL_TIME.iloc[0]]\n",
    "    \n",
    "    for i in range(len(probe_df)-1):\n",
    "\n",
    "        if (probe_df.LOCAL_TIME[i+1] - probe_df.LOCAL_TIME[i])<pd.Timedelta(minutes = 1):\n",
    "\n",
    "            route.append(probe_df.street.iloc[i+1])\n",
    "            route_time.append(probe_df.LOCAL_TIME.iloc[i+1])\n",
    "\n",
    "        else:\n",
    "            route_count += 1  \n",
    "            traj_route_df = traj_route_df.append({'PROBE_ID':ID,'Route_Num':route_count,\\\n",
    "                                                  'Start_Section':route[0],'End_Section':route[-1],\\\n",
    "                                                'Time_Start':route_time[0],'Time_End':route_time[-1]},\\\n",
    "                                                ignore_index=True)\n",
    "            \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "traj_rest = traj_route_df.loc[traj_route_df['Start_Section'] == 'rest_of_new_port']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "balboa = traj_rest.loc[traj_rest['End_Section'] == 'balboa']\n",
    "newport = traj_rest.loc[traj_rest['End_Section'] == 'newport']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "balboa = balboa.drop_duplicates(subset = ['Time_Start','Time_End'],keep=False)\n",
    "newport = newport.drop_duplicates(subset = ['Time_Start','Time_End'],keep=False)"
   ]
  },
  {
   "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>PROBE_ID</th>\n",
       "      <th>Route_Num</th>\n",
       "      <th>Start_Section</th>\n",
       "      <th>End_Section</th>\n",
       "      <th>Time_Start</th>\n",
       "      <th>Time_End</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>373</th>\n",
       "      <td>c9dae7a716114ece8f0395e27ab88668</td>\n",
       "      <td>17</td>\n",
       "      <td>rest_of_new_port</td>\n",
       "      <td>balboa</td>\n",
       "      <td>2018-07-03 17:00:04-07:00</td>\n",
       "      <td>2018-07-03 17:36:34-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>374</th>\n",
       "      <td>c9dae7a716114ece8f0395e27ab88668</td>\n",
       "      <td>18</td>\n",
       "      <td>rest_of_new_port</td>\n",
       "      <td>balboa</td>\n",
       "      <td>2018-07-03 17:00:04-07:00</td>\n",
       "      <td>2018-07-03 17:37:49-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>375</th>\n",
       "      <td>c9dae7a716114ece8f0395e27ab88668</td>\n",
       "      <td>19</td>\n",
       "      <td>rest_of_new_port</td>\n",
       "      <td>balboa</td>\n",
       "      <td>2018-07-03 17:00:04-07:00</td>\n",
       "      <td>2018-07-03 17:38:04-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>924</th>\n",
       "      <td>5636e01b-b74a-5484-bea6-a302424e5551</td>\n",
       "      <td>1</td>\n",
       "      <td>rest_of_new_port</td>\n",
       "      <td>balboa</td>\n",
       "      <td>2018-07-03 17:00:06-07:00</td>\n",
       "      <td>2018-07-03 17:02:02-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>925</th>\n",
       "      <td>5636e01b-b74a-5484-bea6-a302424e5551</td>\n",
       "      <td>2</td>\n",
       "      <td>rest_of_new_port</td>\n",
       "      <td>balboa</td>\n",
       "      <td>2018-07-03 17:00:06-07:00</td>\n",
       "      <td>2018-07-03 17:02:39-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>926</th>\n",
       "      <td>5636e01b-b74a-5484-bea6-a302424e5551</td>\n",
       "      <td>3</td>\n",
       "      <td>rest_of_new_port</td>\n",
       "      <td>balboa</td>\n",
       "      <td>2018-07-03 17:00:06-07:00</td>\n",
       "      <td>2018-07-03 17:04:23-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>927</th>\n",
       "      <td>5636e01b-b74a-5484-bea6-a302424e5551</td>\n",
       "      <td>4</td>\n",
       "      <td>rest_of_new_port</td>\n",
       "      <td>balboa</td>\n",
       "      <td>2018-07-03 17:00:06-07:00</td>\n",
       "      <td>2018-07-03 17:05:10-07:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 PROBE_ID Route_Num     Start_Section  \\\n",
       "373      c9dae7a716114ece8f0395e27ab88668        17  rest_of_new_port   \n",
       "374      c9dae7a716114ece8f0395e27ab88668        18  rest_of_new_port   \n",
       "375      c9dae7a716114ece8f0395e27ab88668        19  rest_of_new_port   \n",
       "924  5636e01b-b74a-5484-bea6-a302424e5551         1  rest_of_new_port   \n",
       "925  5636e01b-b74a-5484-bea6-a302424e5551         2  rest_of_new_port   \n",
       "926  5636e01b-b74a-5484-bea6-a302424e5551         3  rest_of_new_port   \n",
       "927  5636e01b-b74a-5484-bea6-a302424e5551         4  rest_of_new_port   \n",
       "\n",
       "    End_Section                 Time_Start                   Time_End  \n",
       "373      balboa  2018-07-03 17:00:04-07:00  2018-07-03 17:36:34-07:00  \n",
       "374      balboa  2018-07-03 17:00:04-07:00  2018-07-03 17:37:49-07:00  \n",
       "375      balboa  2018-07-03 17:00:04-07:00  2018-07-03 17:38:04-07:00  \n",
       "924      balboa  2018-07-03 17:00:06-07:00  2018-07-03 17:02:02-07:00  \n",
       "925      balboa  2018-07-03 17:00:06-07:00  2018-07-03 17:02:39-07:00  \n",
       "926      balboa  2018-07-03 17:00:06-07:00  2018-07-03 17:04:23-07:00  \n",
       "927      balboa  2018-07-03 17:00:06-07:00  2018-07-03 17:05:10-07:00  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "balboa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PROBE_ID</th>\n",
       "      <th>Route_Num</th>\n",
       "      <th>Start_Section</th>\n",
       "      <th>End_Section</th>\n",
       "      <th>Time_Start</th>\n",
       "      <th>Time_End</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>443</th>\n",
       "      <td>0C023DA300</td>\n",
       "      <td>36</td>\n",
       "      <td>rest_of_new_port</td>\n",
       "      <td>newport</td>\n",
       "      <td>2018-07-04 10:50:04-07:00</td>\n",
       "      <td>2018-07-04 14:25:27-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1006</th>\n",
       "      <td>57b40fd4696c48cfb3d46c786902d36e</td>\n",
       "      <td>4</td>\n",
       "      <td>rest_of_new_port</td>\n",
       "      <td>newport</td>\n",
       "      <td>2018-07-04 16:23:35-07:00</td>\n",
       "      <td>2018-07-04 16:49:26-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1058</th>\n",
       "      <td>8c2c552aa42a40e68b4ca64f4bef9308</td>\n",
       "      <td>13</td>\n",
       "      <td>rest_of_new_port</td>\n",
       "      <td>newport</td>\n",
       "      <td>2018-07-03 17:56:11-07:00</td>\n",
       "      <td>2018-07-03 20:33:57-07:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              PROBE_ID Route_Num     Start_Section  \\\n",
       "443                         0C023DA300        36  rest_of_new_port   \n",
       "1006  57b40fd4696c48cfb3d46c786902d36e         4  rest_of_new_port   \n",
       "1058  8c2c552aa42a40e68b4ca64f4bef9308        13  rest_of_new_port   \n",
       "\n",
       "     End_Section                 Time_Start                   Time_End  \n",
       "443      newport  2018-07-04 10:50:04-07:00  2018-07-04 14:25:27-07:00  \n",
       "1006     newport  2018-07-04 16:23:35-07:00  2018-07-04 16:49:26-07:00  \n",
       "1058     newport  2018-07-03 17:56:11-07:00  2018-07-03 20:33:57-07:00  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newport"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of Cars going to Balboa Blvd: 7\n",
      "Number of Cars going to Newport Blvd: 3\n"
     ]
    }
   ],
   "source": [
    "print('Number of Cars going to Balboa Blvd:', len(balboa))\n",
    "print('Number of Cars going to Newport Blvd:', len(newport))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Notes\n",
    "### For unique probe count\n",
    "### Section Options:\n",
    "- 'right_half_tri'\n",
    "- 'left_half_tri'\n",
    "- 'island'\n",
    "- 'rest_of_new_port'\n",
    "\n",
    "### Street Options\n",
    "- 'balboa'\n",
    "- 'newport'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Blanket sorting for all data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "analysis = Analyze_Data(data, section_shapefile_list, street_shapefile_list)\n",
    "data_cleaned = analysis.clean_data_add_heading()"
   ]
  },
  {
   "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>PROBE_ID</th>\n",
       "      <th>LAT</th>\n",
       "      <th>LONG</th>\n",
       "      <th>HEADING</th>\n",
       "      <th>SPEED</th>\n",
       "      <th>PROBE_DATA_PROVIDER</th>\n",
       "      <th>LOCAL_TIME</th>\n",
       "      <th>coord</th>\n",
       "      <th>section</th>\n",
       "      <th>street</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>HEADING_FIXED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>480</th>\n",
       "      <td>285613c2-f71d-55f6-bb2b-8f16528c0a2c</td>\n",
       "      <td>33.615262</td>\n",
       "      <td>-117.927329</td>\n",
       "      <td>34.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>CONSUMER14</td>\n",
       "      <td>2018-07-03 17:00:00-07:00</td>\n",
       "      <td>POINT (-117.927329 33.615262)</td>\n",
       "      <td>right_half_tri</td>\n",
       "      <td>N/A</td>\n",
       "      <td>17</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>479</th>\n",
       "      <td>285613c2-f71d-55f6-bb2b-8f16528c0a2c</td>\n",
       "      <td>33.615269</td>\n",
       "      <td>-117.927325</td>\n",
       "      <td>19.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>CONSUMER14</td>\n",
       "      <td>2018-07-03 17:00:01-07:00</td>\n",
       "      <td>POINT (-117.927325 33.615269)</td>\n",
       "      <td>right_half_tri</td>\n",
       "      <td>N/A</td>\n",
       "      <td>17</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>285613c2-f71d-55f6-bb2b-8f16528c0a2c</td>\n",
       "      <td>33.615287</td>\n",
       "      <td>-117.927314</td>\n",
       "      <td>19.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>CONSUMER14</td>\n",
       "      <td>2018-07-03 17:00:02-07:00</td>\n",
       "      <td>POINT (-117.927314 33.615287)</td>\n",
       "      <td>right_half_tri</td>\n",
       "      <td>N/A</td>\n",
       "      <td>17</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>494</th>\n",
       "      <td>6c247e2bf8f447b2b31b7450261a1e3c</td>\n",
       "      <td>33.618990</td>\n",
       "      <td>-117.930160</td>\n",
       "      <td>189.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>CONSUMER21</td>\n",
       "      <td>2018-07-03 17:00:03-07:00</td>\n",
       "      <td>POINT (-117.93016 33.61899)</td>\n",
       "      <td>right_half_tri</td>\n",
       "      <td>newport</td>\n",
       "      <td>17</td>\n",
       "      <td>-171.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2276</th>\n",
       "      <td>285613c2-f71d-55f6-bb2b-8f16528c0a2c</td>\n",
       "      <td>33.615319</td>\n",
       "      <td>-117.927299</td>\n",
       "      <td>18.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>CONSUMER14</td>\n",
       "      <td>2018-07-03 17:00:03-07:00</td>\n",
       "      <td>POINT (-117.927299 33.615319)</td>\n",
       "      <td>right_half_tri</td>\n",
       "      <td>N/A</td>\n",
       "      <td>17</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  PROBE_ID        LAT        LONG  HEADING  \\\n",
       "480   285613c2-f71d-55f6-bb2b-8f16528c0a2c  33.615262 -117.927329     34.0   \n",
       "479   285613c2-f71d-55f6-bb2b-8f16528c0a2c  33.615269 -117.927325     19.0   \n",
       "445   285613c2-f71d-55f6-bb2b-8f16528c0a2c  33.615287 -117.927314     19.0   \n",
       "494       6c247e2bf8f447b2b31b7450261a1e3c  33.618990 -117.930160    189.0   \n",
       "2276  285613c2-f71d-55f6-bb2b-8f16528c0a2c  33.615319 -117.927299     18.0   \n",
       "\n",
       "      SPEED PROBE_DATA_PROVIDER                LOCAL_TIME  \\\n",
       "480    14.0          CONSUMER14 2018-07-03 17:00:00-07:00   \n",
       "479    12.0          CONSUMER14 2018-07-03 17:00:01-07:00   \n",
       "445    17.0          CONSUMER14 2018-07-03 17:00:02-07:00   \n",
       "494     4.0          CONSUMER21 2018-07-03 17:00:03-07:00   \n",
       "2276   20.0          CONSUMER14 2018-07-03 17:00:03-07:00   \n",
       "\n",
       "                              coord         section   street  HOUR  \\\n",
       "480   POINT (-117.927329 33.615262)  right_half_tri      N/A    17   \n",
       "479   POINT (-117.927325 33.615269)  right_half_tri      N/A    17   \n",
       "445   POINT (-117.927314 33.615287)  right_half_tri      N/A    17   \n",
       "494     POINT (-117.93016 33.61899)  right_half_tri  newport    17   \n",
       "2276  POINT (-117.927299 33.615319)  right_half_tri      N/A    17   \n",
       "\n",
       "      HEADING_FIXED  \n",
       "480            34.0  \n",
       "479            19.0  \n",
       "445            19.0  \n",
       "494          -171.0  \n",
       "2276           18.0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_cleaned.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Analysis of data based on road and transportation type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Balboa\n",
    "bal_north_limits = [-90,45]\n",
    "bal_south_limits = [145,-150]\n",
    "# Newport\n",
    "newport_north_limits = [-140,0]  \n",
    "newport_south_limits = [90,-160]\n",
    "\n",
    "speed_limit = 15"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For Balboa\n",
    "analysis_balboa = road_specific_analysis(data_cleaned,'balboa')\n",
    "\n",
    "# Northbound\n",
    "veh_balboa_N_speed, veh_balboa_N_probe = analysis_balboa.\\\n",
    "                                         get_heading_info(bal_north_limits,\\\n",
    "                                         speed_limit, \"Vehicle\")\n",
    "\n",
    "\n",
    "ped_balboa_N_speed, ped_balboa_N_probe = analysis_balboa.\\\n",
    "                                         get_heading_info(bal_north_limits,\\\n",
    "                                         speed_limit, \"Pedestrian\")\n",
    "\n",
    "\n",
    "# Southbound\n",
    "\n",
    "veh_balboa_S_speed, veh_balboa_S_probe = analysis_balboa.\\\n",
    "                                         get_heading_info(bal_south_limits,\\\n",
    "                                         speed_limit, \"Vehicle\")\n",
    "\n",
    "\n",
    "ped_balboa_S_speed, ped_balboa_S_probe = analysis_balboa.\\\n",
    "                                         get_heading_info(bal_south_limits,\\\n",
    "                                         speed_limit, \"Pedestrian\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For Newport\n",
    "analysis_newport = road_specific_analysis(data_cleaned,'newport')\n",
    "\n",
    "# Northbound\n",
    "veh_newport_N_speed, veh_newport_N_probe = analysis_newport.\\\n",
    "                                         get_heading_info(newport_north_limits,\\\n",
    "                                         speed_limit, \"Vehicle\")\n",
    "\n",
    "\n",
    "ped_newport_N_speed, ped_newport_N_probe = analysis_newport.\\\n",
    "                                         get_heading_info(newport_north_limits,\\\n",
    "                                         speed_limit, \"Pedestrian\")\n",
    "\n",
    "\n",
    "# Southbound\n",
    "\n",
    "veh_newport_S_speed, veh_newport_S_probe = analysis_newport.\\\n",
    "                                         get_heading_info(newport_south_limits,\\\n",
    "                                         speed_limit, \"Vehicle\")\n",
    "\n",
    "\n",
    "ped_newport_S_speed, ped_newport_S_probe = analysis_newport.\\\n",
    "                                         get_heading_info(newport_south_limits,\\\n",
    "                                         speed_limit, \"Pedestrian\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a dataframe of road information per hour\n",
    "road_info_df = pd.DataFrame({'Hour':range(0,24),\\\n",
    "                'Newport_North_Speed_Veh':veh_newport_N_speed,\\\n",
    "                'Newport_South_Speed_Veh':veh_newport_S_speed,\\\n",
    "                'Newport_North_Probe_Veh':veh_newport_N_probe,\\\n",
    "                'Newport_South_Probe_Veh':veh_newport_S_probe,\\\n",
    "                'Newport_North_Speed_Ped':ped_newport_N_speed,\\\n",
    "                'Newport_South_Speed_Ped':ped_newport_S_speed,\\\n",
    "                'Newport_North_Probe_Ped':ped_newport_N_probe,\\\n",
    "                'Newport_South_Probe_Ped':ped_newport_S_probe,\\\n",
    "                             \n",
    "                'Balboa_North_Speed_Veh':veh_balboa_N_speed,\\\n",
    "                'Balboa_South_Speed_Veh':veh_balboa_S_speed,\\\n",
    "                'Balboa_North_Probe_Veh':veh_balboa_N_probe,\\\n",
    "                'Balboa_South_Probe_Veh':veh_balboa_S_probe,\\\n",
    "                'Balboa_North_Speed_Ped':ped_balboa_N_speed,\\\n",
    "                'Balboa_South_Speed_Ped':ped_balboa_S_speed,\\\n",
    "                'Balboa_North_Probe_Ped':ped_balboa_N_probe,\\\n",
    "                'Balboa_South_Probe_Ped':ped_balboa_S_probe})\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Hour</th>\n",
       "      <th>Newport_North_Speed_Veh</th>\n",
       "      <th>Newport_South_Speed_Veh</th>\n",
       "      <th>Newport_North_Probe_Veh</th>\n",
       "      <th>Newport_South_Probe_Veh</th>\n",
       "      <th>Newport_North_Speed_Ped</th>\n",
       "      <th>Newport_South_Speed_Ped</th>\n",
       "      <th>Newport_North_Probe_Ped</th>\n",
       "      <th>Newport_South_Probe_Ped</th>\n",
       "      <th>Balboa_North_Speed_Veh</th>\n",
       "      <th>Balboa_South_Speed_Veh</th>\n",
       "      <th>Balboa_North_Probe_Veh</th>\n",
       "      <th>Balboa_South_Probe_Veh</th>\n",
       "      <th>Balboa_North_Speed_Ped</th>\n",
       "      <th>Balboa_South_Speed_Ped</th>\n",
       "      <th>Balboa_North_Probe_Ped</th>\n",
       "      <th>Balboa_South_Probe_Ped</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>41.329114</td>\n",
       "      <td>45.329114</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>32.8125</td>\n",
       "      <td>25.777778</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>27.358780</td>\n",
       "      <td>27.358780</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>27.285714</td>\n",
       "      <td>15.571429</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5.734340</td>\n",
       "      <td>7.5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>30.577460</td>\n",
       "      <td>30.577460</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10.720389</td>\n",
       "      <td>8.966323</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10.856683</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>41.038170</td>\n",
       "      <td>15.288730</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>11.905691</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Hour  Newport_North_Speed_Veh  Newport_South_Speed_Veh  \\\n",
       "0     0                41.329114                45.329114   \n",
       "1     1                27.358780                27.358780   \n",
       "2     2                27.285714                15.571429   \n",
       "3     3                30.577460                30.577460   \n",
       "4     4                41.038170                15.288730   \n",
       "\n",
       "   Newport_North_Probe_Veh  Newport_South_Probe_Veh  Newport_North_Speed_Ped  \\\n",
       "0                        2                        2                 0.000000   \n",
       "1                        1                        1                 0.000000   \n",
       "2                        2                        1                 0.000000   \n",
       "3                        1                        1                10.720389   \n",
       "4                        1                        1                11.905691   \n",
       "\n",
       "   Newport_South_Speed_Ped  Newport_North_Probe_Ped  Newport_South_Probe_Ped  \\\n",
       "0                 0.000000                        0                        0   \n",
       "1                 0.000000                        0                        0   \n",
       "2                 0.000000                        0                        0   \n",
       "3                 8.966323                        2                        1   \n",
       "4                 0.000000                        2                        0   \n",
       "\n",
       "   Balboa_North_Speed_Veh  Balboa_South_Speed_Veh  Balboa_North_Probe_Veh  \\\n",
       "0                 32.8125               25.777778                       1   \n",
       "1                  0.0000                0.000000                       0   \n",
       "2                  0.0000                0.000000                       0   \n",
       "3                  0.0000                0.000000                       0   \n",
       "4                  0.0000                0.000000                       0   \n",
       "\n",
       "   Balboa_South_Probe_Veh  Balboa_North_Speed_Ped  Balboa_South_Speed_Ped  \\\n",
       "0                       1                0.000000                     0.0   \n",
       "1                       0                0.000000                     0.0   \n",
       "2                       0                5.734340                     7.5   \n",
       "3                       0               10.856683                     0.0   \n",
       "4                       0                0.000000                     0.0   \n",
       "\n",
       "   Balboa_North_Probe_Ped  Balboa_South_Probe_Ped  \n",
       "0                       0                       0  \n",
       "1                       0                       0  \n",
       "2                       2                       1  \n",
       "3                       2                       0  \n",
       "4                       0                       0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "road_info_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "def find_slowest(df, column,probe):\n",
    "    \n",
    "    slow = []\n",
    "    for i in df[column]:\n",
    "        if i>0:\n",
    "            slow.append(i)\n",
    "    slowest_speed = min(slow)\n",
    "    slowest_df = df.loc[df[column]==slowest_speed][['Hour', column, probe]]\n",
    "\n",
    "    return slowest_df\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "N_N_slow = find_slowest(road_info_df,'Newport_North_Speed_Veh','Newport_North_Probe_Veh')\n",
    "S_N_slow = find_slowest(road_info_df,'Newport_South_Speed_Veh','Newport_South_Probe_Veh')\n",
    "\n",
    "N_B_slow = find_slowest(road_info_df,'Balboa_North_Speed_Veh','Balboa_North_Probe_Veh')\n",
    "S_B_slow = find_slowest(road_info_df,'Balboa_South_Speed_Veh','Balboa_South_Probe_Veh')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Hour</th>\n",
       "      <th>Newport_North_Speed_Veh</th>\n",
       "      <th>Newport_North_Probe_Veh</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>17.756836</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Hour  Newport_North_Speed_Veh  Newport_North_Probe_Veh\n",
       "14    14                17.756836                        3"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "N_N_slow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>Hour</th>\n",
       "      <th>Newport_South_Speed_Veh</th>\n",
       "      <th>Newport_South_Probe_Veh</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>15.020507</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Hour  Newport_South_Speed_Veh  Newport_South_Probe_Veh\n",
       "14    14                15.020507                        1"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "S_N_slow"
   ]
  },
  {
   "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>Hour</th>\n",
       "      <th>Balboa_North_Speed_Veh</th>\n",
       "      <th>Balboa_North_Probe_Veh</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>17.083333</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Hour  Balboa_North_Speed_Veh  Balboa_North_Probe_Veh\n",
       "7     7               17.083333                       2"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "N_B_slow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Hour</th>\n",
       "      <th>Balboa_South_Speed_Veh</th>\n",
       "      <th>Balboa_South_Probe_Veh</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>16.25</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Hour  Balboa_South_Speed_Veh  Balboa_South_Probe_Veh\n",
       "19    19                   16.25                       2"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "S_B_slow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}