01-build-db.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Building a SQL database\n",
    "This notebook builds the necessary db files for the project using SQLite3.\n",
    "\n",
    "Most data is taken from Chicago Data Portal https://data.cityofchicago.org/ using Socrata library.\n",
    "The API endpoints for the data are:\n",
    "- Red Light Violations: https://data.cityofchicago.org/resource/spqx-js37.json\n",
    "- Congestion by Region 2018-Present: https://data.cityofchicago.org/resource/kf7e-cur8.json\n",
    "- Congestion by Region 2013-2018: https://data.cityofchicago.org/resource/emtn-qqdi.json\n",
    "- Traffic Crashes: https://data.cityofchicago.org/resource/85ca-t3if.json\n",
    "\n",
    "Weather data is taken from https://openweathermap.org/weather-data and is saved as csv in data folder\n",
    "\n",
    "Tables to build:\n",
    "- daily_violations (one entry for each camera each day with total violations)\n",
    "- intersection_locations (one entry for each intersection with lat/long)\n",
    "- intersection_cams (one entry for each intersection with camera_ids)\n",
    "- signal_crashes (one entry for each intersection crash with many columns)\n",
    "- cam_locations (one entry for each cam, with lat/long)\n",
    "- cam_startend (one entry for each cam with start end dates for min/max dates active)\n",
    "- hourly_congestion (one entry per hour with bus speed averages for each region)\n",
    "- hourly_weather (one entry per hour with many weather cols)\n",
    "- region_data (one entry per region with locations and descriptions to place intersections)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connecting and Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Required Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sodapy import Socrata\n",
    "#import matplotlib.pyplot as plt\n",
    "from datetime import datetime\n",
    "from modules.myfuncs import *\n",
    "import warnings\n",
    "import numpy as np\n",
    "from geopy.geocoders import Nominatim\n",
    "from scipy.stats import mode, percentileofscore\n",
    "\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create/connect to db and build the TABLEs\n",
    "We create the connection and cursor objects we will use to communicate with our SQLite database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sqlite3 version: 2.6.0\n",
      "connected to database/rlc.db\n"
     ]
    }
   ],
   "source": [
    "# Create a db file or open connection\n",
    "conn = create_connection('database/rlc.db')  # function I created in myfuncs file\n",
    "c = conn.cursor()\n",
    "#conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Set up the Socrata client\n",
    "The Chicago Data Portal, which contains most of my data used in this project, uses the Socrata software which can be accessed through Python's sodapy library.\n",
    "Here we create a client, which we will use to query the data at the portal.\n",
    "\n",
    "The data API is at [data.cityofchicago.org](data.cityofchicago.org)\n",
    "The individual db endpoints are found by browsing the site."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:root:Requests made without an app_token will be subject to strict throttling limits.\n"
     ]
    }
   ],
   "source": [
    "# Unauthenticated client only works with public data sets. Note 'None'\n",
    "# in place of application token, and no username or password:\n",
    "\n",
    "url = \"data.cityofchicago.org\"\n",
    "client = Socrata(url, None)\n",
    "\n",
    "# Example authenticated client (needed for non-public datasets):\n",
    "# client = Socrata(data.cityofchicago.org,\n",
    "#                  MyAppToken,\n",
    "#                  userame=\"user@example.com\",\n",
    "#                  password=\"AFakePassword\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Build the TABLEs\n",
    "\n",
    "For every TABLE\n",
    "- Use a Socrata client query to get all relevant data\n",
    "- Preprocess data as needed\n",
    "- Create Table\n",
    "\n",
    "Our data\n",
    "- rlc_cam is up to 1M redlight cams from 2015 to 2020\n",
    "- crash_data is up to 1M crashes from 2015 to 2020\n",
    "- traffic_data is up to 10M from 2015 to 2020\n",
    "- wx_data is a csv file from [openweathermap.org](openweathermap.org)\n",
    "\n",
    "Weather data is taken from csv in data folder"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": true
   },
   "source": [
    "## 1) Build intersection_chars TABLE from int_df\n",
    "\n",
    "This data is created by me.  It is a dictionary contained in this repository under the file 'int_chars.py'.\n",
    "\n",
    "To create this file, I went through all 180+ intersections with red light cameras.  I cross referenced it with a map at https://data.cityofchicago.org/Transportation/Average-Daily-Traffic-Counts-Map/pf56-35rv and google maps to compile the following data.\n",
    "\n",
    "- roads (list): of road segments as identified in average-daily-traffic-counts db in link above.  Used to determine volume of traffic.\n",
    "- protected_turn (int): How any of the left turns are protected (left turn arrow).\n",
    "- total_lanes (int): Count of total lanes.  If a road has one lane for all directions N/E/S/W bound traffic, that counts as 4.  Rangees from 3 to 14 lanes.\n",
    "- medians (int): Count of physical median barriers that extend up to intersection.\n",
    "- exit (int): 0 if no exit.  1 if exit on/off ramp within 100m of center of intersection.  Traffic flow is affected by proximity to exit.\n",
    "- split (int): 1 if it is a divided boulevard (common in Chicago) where divided lanes are split by traffic signals in median. Look at examples on google map.\n",
    "- way (int): directions of traffic flow. A 4 way intersection might be NESW.\n",
    "- underpass (int): number of ways that have an underpass extending up to the intersection.  These are notoriously bad intersections in Chicago.\n",
    "- no_left (int): number of no left turn signs.  Usually with smaller streets onto larger roads or high volumne intersections.\n",
    "- angled (int): 1 if angle between two 2way roads is greater than 30 degrees (used 1/2/sqrt(3) rule to measure.\n",
    "- triangle (int): 1 if three 2way roads meet intersect or form a triangle where all 3 roads <50m\n",
    "- one_way (int): number of 1 way directions.\n",
    "- turn_lanes (int): how many directions have physical and identified turn lanes for left hand turns.\n",
    "- lat (float): latitude of center of inersection.  \n",
    "- long (float): longitude \n",
    "- rlc (int): 1 for red light camera is present\n",
    "- intersection (str): name of intersection as defined in signal_crashes table in db\n",
    "- daily_traffic (int): volume of daily traffic through intersection.  Sum of incoming roads from roads list."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preprocess int_df\n",
    "Import the dictionary and convert to DataFrame which will be written as Table in my db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "roads             0\n",
       "protected_turn    0\n",
       "total_lanes       0\n",
       "medians           0\n",
       "exit              0\n",
       "split             0\n",
       "way               0\n",
       "underpass         0\n",
       "no_left           0\n",
       "angled            0\n",
       "triangle          0\n",
       "one_way           0\n",
       "turn_lanes        0\n",
       "lat               0\n",
       "long              0\n",
       "rlc               0\n",
       "intersection      0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from modules.int_chars import *\n",
    "import pandas as pd\n",
    "\n",
    "int_chars.keys()\n",
    "int_df = pd.DataFrame.from_dict(int_chars, orient='index')\n",
    "int_df['intersection'] = int_chars.keys()\n",
    "int_df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For now, we will only use intersections with rlc of 1.  I may later add intersections without rlc to identify crash characterisics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['roads', 'protected_turn', 'total_lanes', 'medians', 'exit', 'split',\n",
       "       'way', 'underpass', 'no_left', 'angled', 'triangle', 'one_way',\n",
       "       'turn_lanes', 'lat', 'long', 'rlc', 'intersection'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int_df = int_df[int_df['rlc']==1]  # I entertained adding additional non-camera intersections\n",
    "int_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "They were read in as non_null objects.  Would like to cast them before creating a table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 182 entries, 111TH AND HALSTED to WESTERN AND TOUHY\n",
      "Data columns (total 17 columns):\n",
      "roads             182 non-null object\n",
      "protected_turn    182 non-null int64\n",
      "total_lanes       182 non-null int64\n",
      "medians           182 non-null int64\n",
      "exit              182 non-null int64\n",
      "split             182 non-null int64\n",
      "way               182 non-null int64\n",
      "underpass         182 non-null int64\n",
      "no_left           182 non-null int64\n",
      "angled            182 non-null int64\n",
      "triangle          182 non-null int64\n",
      "one_way           182 non-null int64\n",
      "turn_lanes        182 non-null int64\n",
      "lat               182 non-null float64\n",
      "long              182 non-null float64\n",
      "rlc               182 non-null int64\n",
      "intersection      182 non-null object\n",
      "dtypes: float64(2), int64(13), object(2)\n",
      "memory usage: 25.6+ KB\n"
     ]
    }
   ],
   "source": [
    "cols_toint = ['protected_turn', 'total_lanes', 'medians', 'exit', 'split',\n",
    "       'way', 'underpass', 'no_left', 'angled', 'triangle', 'one_way',\n",
    "       'turn_lanes', 'rlc']\n",
    "cols_tofloat = ['lat', 'long',]\n",
    "\n",
    "int_df[cols_toint] = int_df[cols_toint].astype(int)\n",
    "int_df[cols_tofloat] = int_df[cols_tofloat].astype(float)\n",
    "int_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>roads</th>\n",
       "      <th>protected_turn</th>\n",
       "      <th>total_lanes</th>\n",
       "      <th>medians</th>\n",
       "      <th>exit</th>\n",
       "      <th>split</th>\n",
       "      <th>way</th>\n",
       "      <th>underpass</th>\n",
       "      <th>no_left</th>\n",
       "      <th>angled</th>\n",
       "      <th>triangle</th>\n",
       "      <th>one_way</th>\n",
       "      <th>turn_lanes</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>rlc</th>\n",
       "      <th>intersection</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>111TH AND HALSTED</td>\n",
       "      <td>[28 West, 11600 South]</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>41.692362</td>\n",
       "      <td>-87.642423</td>\n",
       "      <td>1</td>\n",
       "      <td>111TH AND HALSTED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>115TH AND HALSTED</td>\n",
       "      <td>[714 West, 11600 South]</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>41.685089</td>\n",
       "      <td>-87.642094</td>\n",
       "      <td>1</td>\n",
       "      <td>115TH AND HALSTED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>119TH AND HALSTED</td>\n",
       "      <td>[446 West, 11600 South]</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>41.677774</td>\n",
       "      <td>-87.641930</td>\n",
       "      <td>1</td>\n",
       "      <td>119TH AND HALSTED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31ST AND CALIFORNIA</td>\n",
       "      <td>[2825 West, 3026 South]</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>41.837424</td>\n",
       "      <td>-87.695022</td>\n",
       "      <td>1</td>\n",
       "      <td>31ST AND CALIFORNIA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31ST ST AND MARTIN LUTHER KING DRIVE</td>\n",
       "      <td>[440 East, 3030 South]</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>41.838441</td>\n",
       "      <td>-87.617338</td>\n",
       "      <td>1</td>\n",
       "      <td>31ST ST AND MARTIN LUTHER KING DRIVE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                        roads  protected_turn  \\\n",
       "111TH AND HALSTED                      [28 West, 11600 South]               2   \n",
       "115TH AND HALSTED                     [714 West, 11600 South]               4   \n",
       "119TH AND HALSTED                     [446 West, 11600 South]               4   \n",
       "31ST AND CALIFORNIA                   [2825 West, 3026 South]               2   \n",
       "31ST ST AND MARTIN LUTHER KING DRIVE   [440 East, 3030 South]               2   \n",
       "\n",
       "                                      total_lanes  medians  exit  split  way  \\\n",
       "111TH AND HALSTED                               6        2     0      0    4   \n",
       "115TH AND HALSTED                               6        2     0      0    4   \n",
       "119TH AND HALSTED                               6        2     0      0    4   \n",
       "31ST AND CALIFORNIA                             6        0     0      0    4   \n",
       "31ST ST AND MARTIN LUTHER KING DRIVE           10        2     0      1    4   \n",
       "\n",
       "                                      underpass  no_left  angled  triangle  \\\n",
       "111TH AND HALSTED                             0        0       1         0   \n",
       "115TH AND HALSTED                             0        0       0         0   \n",
       "119TH AND HALSTED                             0        0       0         0   \n",
       "31ST AND CALIFORNIA                           0        0       0         0   \n",
       "31ST ST AND MARTIN LUTHER KING DRIVE          0        2       0         0   \n",
       "\n",
       "                                      one_way  turn_lanes        lat  \\\n",
       "111TH AND HALSTED                           0           2  41.692362   \n",
       "115TH AND HALSTED                           0           4  41.685089   \n",
       "119TH AND HALSTED                           0           4  41.677774   \n",
       "31ST AND CALIFORNIA                         0           4  41.837424   \n",
       "31ST ST AND MARTIN LUTHER KING DRIVE        0           0  41.838441   \n",
       "\n",
       "                                           long  rlc  \\\n",
       "111TH AND HALSTED                    -87.642423    1   \n",
       "115TH AND HALSTED                    -87.642094    1   \n",
       "119TH AND HALSTED                    -87.641930    1   \n",
       "31ST AND CALIFORNIA                  -87.695022    1   \n",
       "31ST ST AND MARTIN LUTHER KING DRIVE -87.617338    1   \n",
       "\n",
       "                                                              intersection  \n",
       "111TH AND HALSTED                                        111TH AND HALSTED  \n",
       "115TH AND HALSTED                                        115TH AND HALSTED  \n",
       "119TH AND HALSTED                                        119TH AND HALSTED  \n",
       "31ST AND CALIFORNIA                                    31ST AND CALIFORNIA  \n",
       "31ST ST AND MARTIN LUTHER KING DRIVE  31ST ST AND MARTIN LUTHER KING DRIVE  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int_df.head()  # verify my table data before commit"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add daily traffic volume\n",
    "Now bring in my count information using my roads list associated with each intersection\n",
    "This data is also from the data portal.  A survey was done in 2013 for two weeks.  Traffic flow was recoreding at many points around the city.  We make the assumption that the traffic patterns at that time are at least somewhat consistent with the more current patterns and can be used as an estimation of how busy the intersections is on a typical day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "daily_traffic = client.get(\"pfsx-4n4m\", \n",
    "                     limit=2000,\n",
    "                    )\n",
    "\n",
    "daily_traffic = pd.DataFrame.from_records(daily_traffic) # Convert to pandas DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1279 entries, 0 to 1278\n",
      "Data columns (total 15 columns):\n",
      "id                                             1279 non-null object\n",
      "traffic_volume_count_location_address          1279 non-null object\n",
      "street                                         1279 non-null object\n",
      "date_of_count                                  1279 non-null object\n",
      "total_passing_vehicle_volume                   1279 non-null object\n",
      "vehicle_volume_by_each_direction_of_traffic    1279 non-null object\n",
      "latitude                                       1279 non-null object\n",
      "longitude                                      1279 non-null object\n",
      "location                                       1279 non-null object\n",
      ":@computed_region_rpca_8um6                    1266 non-null object\n",
      ":@computed_region_vrxf_vc4k                    1266 non-null object\n",
      ":@computed_region_6mkv_f3dw                    1279 non-null object\n",
      ":@computed_region_bdys_3d7i                    1265 non-null object\n",
      ":@computed_region_43wa_7qmu                    1266 non-null object\n",
      ":@computed_region_awaf_s7ux                    1266 non-null object\n",
      "dtypes: object(15)\n",
      "memory usage: 150.0+ KB\n"
     ]
    },
    {
     "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>traffic_volume_count_location_address</th>\n",
       "      <th>total_passing_vehicle_volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5838 West</td>\n",
       "      <td>7100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>320 East</td>\n",
       "      <td>8600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1730 East</td>\n",
       "      <td>53500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>125 East</td>\n",
       "      <td>700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2924 East</td>\n",
       "      <td>4200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  traffic_volume_count_location_address total_passing_vehicle_volume\n",
       "0                             5838 West                         7100\n",
       "1                              320 East                         8600\n",
       "2                             1730 East                        53500\n",
       "3                              125 East                          700\n",
       "4                             2924 East                         4200"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_traffic.info()\n",
    "cols_tokeep = ['traffic_volume_count_location_address', 'total_passing_vehicle_volume',]\n",
    "daily_traffic = daily_traffic[cols_tokeep]\n",
    "\n",
    "daily_traffic.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "daily_traffic.total_passing_vehicle_volume = daily_traffic.total_passing_vehicle_volume.astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Combine my characteristics with my daily_traffic by looking up traffic volume from daily_traffic df."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def look_up_roads(road_list):\n",
    "    '''\n",
    "    Look up function to get the values and return the total\n",
    "            Parameters:\n",
    "                roads (list): road segment list for intersection\n",
    "            Returns:\n",
    "                total (int): combined traffic volume of every road in roads list.\n",
    "    '''\n",
    "    total = 0  \n",
    "    for road in road_list:\n",
    "        count = daily_traffic[daily_traffic['traffic_volume_count_location_address']==road]['total_passing_vehicle_volume'].values[0]\n",
    "        total += count\n",
    "    return total\n",
    "\n",
    "int_df['daily_traffic'] = int_df['roads'].apply(look_up_roads)\n",
    "int_df.drop(columns=['roads'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>protected_turn</th>\n",
       "      <th>total_lanes</th>\n",
       "      <th>medians</th>\n",
       "      <th>exit</th>\n",
       "      <th>split</th>\n",
       "      <th>way</th>\n",
       "      <th>underpass</th>\n",
       "      <th>no_left</th>\n",
       "      <th>angled</th>\n",
       "      <th>triangle</th>\n",
       "      <th>one_way</th>\n",
       "      <th>turn_lanes</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>rlc</th>\n",
       "      <th>intersection</th>\n",
       "      <th>daily_traffic</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>111TH AND HALSTED</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>41.692362</td>\n",
       "      <td>-87.642423</td>\n",
       "      <td>1</td>\n",
       "      <td>111TH AND HALSTED</td>\n",
       "      <td>43100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>115TH AND HALSTED</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>41.685089</td>\n",
       "      <td>-87.642094</td>\n",
       "      <td>1</td>\n",
       "      <td>115TH AND HALSTED</td>\n",
       "      <td>42500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>119TH AND HALSTED</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>41.677774</td>\n",
       "      <td>-87.641930</td>\n",
       "      <td>1</td>\n",
       "      <td>119TH AND HALSTED</td>\n",
       "      <td>41800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31ST AND CALIFORNIA</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>41.837424</td>\n",
       "      <td>-87.695022</td>\n",
       "      <td>1</td>\n",
       "      <td>31ST AND CALIFORNIA</td>\n",
       "      <td>41100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31ST ST AND MARTIN LUTHER KING DRIVE</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>41.838441</td>\n",
       "      <td>-87.617338</td>\n",
       "      <td>1</td>\n",
       "      <td>31ST ST AND MARTIN LUTHER KING DRIVE</td>\n",
       "      <td>36500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      protected_turn  total_lanes  medians  \\\n",
       "111TH AND HALSTED                                  2            6        2   \n",
       "115TH AND HALSTED                                  4            6        2   \n",
       "119TH AND HALSTED                                  4            6        2   \n",
       "31ST AND CALIFORNIA                                2            6        0   \n",
       "31ST ST AND MARTIN LUTHER KING DRIVE               2           10        2   \n",
       "\n",
       "                                      exit  split  way  underpass  no_left  \\\n",
       "111TH AND HALSTED                        0      0    4          0        0   \n",
       "115TH AND HALSTED                        0      0    4          0        0   \n",
       "119TH AND HALSTED                        0      0    4          0        0   \n",
       "31ST AND CALIFORNIA                      0      0    4          0        0   \n",
       "31ST ST AND MARTIN LUTHER KING DRIVE     0      1    4          0        2   \n",
       "\n",
       "                                      angled  triangle  one_way  turn_lanes  \\\n",
       "111TH AND HALSTED                          1         0        0           2   \n",
       "115TH AND HALSTED                          0         0        0           4   \n",
       "119TH AND HALSTED                          0         0        0           4   \n",
       "31ST AND CALIFORNIA                        0         0        0           4   \n",
       "31ST ST AND MARTIN LUTHER KING DRIVE       0         0        0           0   \n",
       "\n",
       "                                            lat       long  rlc  \\\n",
       "111TH AND HALSTED                     41.692362 -87.642423    1   \n",
       "115TH AND HALSTED                     41.685089 -87.642094    1   \n",
       "119TH AND HALSTED                     41.677774 -87.641930    1   \n",
       "31ST AND CALIFORNIA                   41.837424 -87.695022    1   \n",
       "31ST ST AND MARTIN LUTHER KING DRIVE  41.838441 -87.617338    1   \n",
       "\n",
       "                                                              intersection  \\\n",
       "111TH AND HALSTED                                        111TH AND HALSTED   \n",
       "115TH AND HALSTED                                        115TH AND HALSTED   \n",
       "119TH AND HALSTED                                        119TH AND HALSTED   \n",
       "31ST AND CALIFORNIA                                    31ST AND CALIFORNIA   \n",
       "31ST ST AND MARTIN LUTHER KING DRIVE  31ST ST AND MARTIN LUTHER KING DRIVE   \n",
       "\n",
       "                                      daily_traffic  \n",
       "111TH AND HALSTED                             43100  \n",
       "115TH AND HALSTED                             42500  \n",
       "119TH AND HALSTED                             41800  \n",
       "31ST AND CALIFORNIA                           41100  \n",
       "31ST ST AND MARTIN LUTHER KING DRIVE          36500  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create intersection_chars TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('cam_locations',), ('cam_startend',), ('daily_violations',), ('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('intersection_cams',), ('all_hours',), ('int_startend',), ('signal_crashes',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(int_df, 'intersection_chars', c, conn)  # function from import"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2) Build daily_violations TABLE  from rlc_df\n",
    "\n",
    "This table will hold the red light camera violations data.  It will include daily violations of every red light camera.\n",
    "This is a large dataset.  (320 cameras with daily violations over the past 5 years)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query red light violations\n",
    "Use the API endpoint to get data with Socrata query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Red light violations\n",
    "# Takes several minutes to run and holds about 500mb in memory to build\n",
    "\n",
    "# First 1000000 results, returned as JSON from API / converted to Python list of dictionaries by sodapy\n",
    "rlc_df = client.get(\"spqx-js37\", #speed cams are at 'hhkd-xvj4' if you want to investigate?\n",
    "                     #where='violation_date > 01-01-2020',\n",
    "                     where='violation_date > \\'2017-01-01T00:00:00.000\\'',\n",
    "                     limit=10000000,\n",
    "                    )\n",
    "\n",
    "rlc_df = pd.DataFrame.from_records(rlc_df) # Convert to pandas DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preprocess Red Light Camera Data\n",
    "\n",
    "Data Columns of interest (from API docs):\n",
    "\n",
    "INTERSECTION -\n",
    "Intersection of the location of the red light enforcement camera(s). There may be more than one camera at each intersection. Plain Text\n",
    "\n",
    "CAMERA ID -\n",
    "A unique ID for each physical camera at an intersection, which may contain more than one camera. Plain Text\n",
    "\n",
    "ADDRESS\t-\n",
    "The address of the physical camera (CAMERA ID). The address may be the same for all cameras or different, based on the physical installation of each camera. Plain Text\n",
    "\n",
    "VIOLATION DATE -\n",
    "The date of when the violations occurred. NOTE: The citation may be issued on a different date. Date & Time\n",
    "\n",
    "VIOLATIONS - \n",
    "Number of violations for each camera on a particular day. Number\n",
    "\n",
    "LATITUDE -\n",
    "The latitude of the physical location of the camera(s) based on the ADDRESS column. Geocoded using the WGS84. Number\n",
    "\n",
    "LONGITUDE -\n",
    "The longitude of the physical location of the camera(s) based on the ADDRESS column. Geocoded using the WGS84.\n",
    "Number"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Investigate violation data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 389217 entries, 0 to 389216\n",
      "Data columns (total 10 columns):\n",
      "intersection      389217 non-null object\n",
      "camera_id         389216 non-null object\n",
      "address           389217 non-null object\n",
      "violation_date    389217 non-null object\n",
      "violations        389217 non-null object\n",
      "x_coordinate      368859 non-null object\n",
      "y_coordinate      368859 non-null object\n",
      "latitude          368859 non-null object\n",
      "longitude         368859 non-null object\n",
      "location          368859 non-null object\n",
      "dtypes: object(10)\n",
      "memory usage: 29.7+ MB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "intersection          0\n",
       "camera_id             1\n",
       "address               0\n",
       "violation_date        0\n",
       "violations            0\n",
       "x_coordinate      20358\n",
       "y_coordinate      20358\n",
       "latitude          20358\n",
       "longitude         20358\n",
       "location          20358\n",
       "dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rlc_df.info()\n",
    "rlc_df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Drop nan values and unnecessary columns\n",
    "We see that we have all text/non-null objects.  Need to convert first before manipulating for preprocess.\n",
    "\n",
    "There are a fair number of missing locations/lat/long.  Hope to be able to replace those missing values.\n",
    "This represents a large enough portion of dataset that we should look them up.\n",
    "\n",
    "The na values for camera_id will have to be dropped, since we don't know what they are.\n",
    "\n",
    "We will not be using x andy y_coordinate, so we drop those.  We will also drop location.  We already have lat long in other columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "intersection          0\n",
       "camera_id             0\n",
       "address               0\n",
       "violation_date        0\n",
       "violations            0\n",
       "latitude          20358\n",
       "longitude         20358\n",
       "dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#client_df.dropna(subset=['camera_id']).isna().sum()\n",
    "try:\n",
    "    # put this is a try in case we run it twice, it will skip it.\n",
    "    rlc_df.dropna(subset=['camera_id'], inplace=True)\n",
    "    \n",
    "    # drop xy coord and location columns\n",
    "    rlc_df = rlc_df.drop(columns=['x_coordinate', 'y_coordinate', 'location'], index=1)\n",
    "except:\n",
    "    pass\n",
    "\n",
    "\n",
    "\n",
    "rlc_df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['111TH AND HALSTED', '115TH AND HALSTED', '119TH AND HALSTED',\n",
       "       '31ST ST AND MARTIN LUTHER KING DRIVE', '35TH AND WESTERN',\n",
       "       '4700 WESTERN', '55TH AND KEDZIE', '55TH AND WESTERN',\n",
       "       '55TH and PULASKI', '63RD AND STATE', '71ST AND ASHLAND',\n",
       "       '75TH AND STATE', '79TH AND HALSTED', '79TH AND KEDZIE',\n",
       "       '87TH AND VINCENNES', '95TH AND STONEY ISLAND', '99TH AND HALSTED',\n",
       "       'ADDISON AND HARLEM', 'ARCHER AND CICERO', 'ASHLAND AND 87TH',\n",
       "       'ASHLAND AND 95TH', 'ASHLAND AND DIVISION',\n",
       "       'ASHLAND AND FULLERTON', 'ASHLAND AND IRVING PARK',\n",
       "       'ASHLAND AND LAWRENCE', 'ASHLAND AND MADISON',\n",
       "       'AUSTIN AND ADDISON', 'AUSTIN AND IRVING PARK',\n",
       "       'BELMONT AND KEDZIE', 'BROADWAY/SHERIDAN AND DEVON',\n",
       "       'CALIFORNIA AND DEVON', 'CALIFORNIA AND DIVERSEY',\n",
       "       'CALIFORNIA AND PETERSON', 'CANAL AND ROOSEVELT',\n",
       "       'CENTRAL AND ADDISON', 'CENTRAL AND BELMONT',\n",
       "       'CENTRAL AND CHICAGO', 'CENTRAL AND DIVERSEY',\n",
       "       'CENTRAL AND FULLERTON', 'CENTRAL AND IRVING PARK',\n",
       "       'CENTRAL AND LAKE', 'CENTRAL AND MILWAUKEE', 'CERMAK AND PULASKI',\n",
       "       'CHICAGO AND CLARK', 'CICERO AND 47TH', 'CICERO AND ADDISON',\n",
       "       'CICERO AND ARMITAGE', 'CICERO AND CHICAGO', 'CICERO AND DIVERSEY',\n",
       "       'CICERO AND FULLERTON', 'CICERO AND HARRISON', 'CICERO AND I55',\n",
       "       'CICERO AND NORTH', 'CICERO AND PETERSON', 'CICERO AND WASHINGTON',\n",
       "       'CLARK AND FULLERTON', 'CLARK AND IRVING PARK',\n",
       "       'COLUMBUS AND ILLINOIS', 'CORTLAND AND ASHLAND',\n",
       "       'COTTAGE GROVE AND 71ST', 'DAMEN AND 63RD', 'DAMEN AND DIVERSEY',\n",
       "       'DAMEN AND ELSTON', 'DAMEN AND FULLERTON', 'DIVERSEY AND AUSTIN',\n",
       "       'DIVERSEY AND WESTERN', 'DIVISION AND DAMEN', 'ELSTON AND ADDISON',\n",
       "       'ELSTON AND IRVING PARK', 'ELSTON AND LAWRENCE',\n",
       "       'FOSTER AND BROADWAY', 'FOSTER AND NAGLE',\n",
       "       'FOSTER AND NORTHWEST HIGHWAY', 'FULLERTON AND NARRAGANSETT',\n",
       "       'GRAND AND OAK PARK', 'HALSTED AND 103RD', 'HALSTED AND 95TH',\n",
       "       'HALSTED AND DIVISION', 'HALSTED AND FULLERTON',\n",
       "       'HALSTED AND MADISON', 'HALSTED AND NORTH', 'HAMLIN AND LAKE',\n",
       "       'HAMLIN AND MADISON', 'HARLEM AND BELMONT',\n",
       "       'HOLLYWOOD AND SHERIDAN', 'HOMAN/KIMBALL AND NORTH',\n",
       "       'IRVING PARK AND CALIFORNIA', 'IRVING PARK AND KEDZIE',\n",
       "       'IRVING PARK AND KILPATRICK', 'IRVING PARK AND LARAMIE',\n",
       "       'IRVING PARK AND NARRAGANSETT', 'JEFFERY AND 95TH',\n",
       "       'KEDZIE AND 26TH', 'KEDZIE AND 31ST', 'KEDZIE AND 47TH',\n",
       "       'KEDZIE AND 63RD', 'KEDZIE AND 71ST', 'KEDZIE AND ARMITAGE',\n",
       "       'KIMBALL AND DIVERSEY', 'KOSTNER AND NORTH', 'LAFAYETTE AND 87TH',\n",
       "       'LAKE AND UPPER WACKER', 'LAKE SHORE DR AND BELMONT',\n",
       "       'LARAMIE AND FULLERTON', 'LARAMIE AND MADISON',\n",
       "       'LASALLE AND KINZIE', 'LAWRENCE AND CICERO',\n",
       "       'LAWRENCE AND WESTERN', 'MADISON AND WESTERN',\n",
       "       'MICHIGAN AND JACKSON', 'MICHIGAN AND ONTARIO',\n",
       "       'MILWAUKEE AND CENTRAL', 'MILWAUKEE AND DEVON',\n",
       "       'MILWAUKEE AND MONTROSE', 'MONTROSE AND WESTERN',\n",
       "       'NORTHWEST HIGHWAY AND FOSTER', 'OGDEN AND KOSTNER',\n",
       "       'PERSHING AND WESTERN', 'PETERSON AND WESTERN', 'PULASKI AND 63RD',\n",
       "       'PULASKI AND 79TH', 'PULASKI AND ARCHER', 'PULASKI AND ARMITAGE',\n",
       "       'PULASKI AND BELMONT', 'PULASKI AND CHICAGO',\n",
       "       'PULASKI AND DIVERSEY', 'PULASKI AND DIVISION',\n",
       "       'PULASKI AND FOSTER', 'PULASKI AND FULLERTON',\n",
       "       'PULASKI AND IRVING PARK', 'PULASKI AND LAWRENCE',\n",
       "       'PULASKI AND NORTH', 'PULASKI AND PETERSON', 'RIDGE AND CLARK',\n",
       "       'ROOSEVELT AND HALSTED', 'ROOSEVELT AND KOSTNER',\n",
       "       'ROOSEVELT AND PULASKI', 'SACRAMENTO AND CHICAGO',\n",
       "       'SACRAMENTO AND LAKE', 'SHERIDAN AND FOSTER', 'STATE AND 79TH',\n",
       "       'STONEY ISLAND AND 76TH', 'STONEY ISLAND AND 79TH',\n",
       "       'STONY ISLAND/CORNELL AND 67TH', 'TOUHY AND OSCEOLA',\n",
       "       'VAN BUREN AND WESTERN', 'WENTWORTH AND GARFIELD',\n",
       "       'WESTERN AND 63RD', 'WESTERN AND 71ST', 'WESTERN AND 79TH',\n",
       "       'WESTERN AND ADDISON', 'WESTERN AND CERMAK', 'WESTERN AND CHICAGO',\n",
       "       'WESTERN AND DEVON', 'WESTERN AND FOSTER', 'WESTERN AND FULLERTON',\n",
       "       'WESTERN AND MARQUETTE', 'WESTERN AND NORTH', 'WESTERN AND TOUHY'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rlc_df.intersection.sort_values().unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Fix intersection errors identified in EDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fix a specific intersection naming problem\n",
    "# both 'NORTHWEST HIGHWAY AND FOSTER' and 'FOSTER AND NORTHWEST HIGHWAY' exist.  Only one should be used.  They are same.\n",
    "# Also happens with \"MILWAUKEE AND CENTRAL\" / \"CENTRAL AND MILWAUKEE\"\n",
    "rlc_df['intersection'] = rlc_df['intersection'].apply(lambda x: 'MILWAUKEE AND CENTRAL' if x=='CENTRAL AND MILWAUKEE' else x)\n",
    "rlc_df['intersection'] = rlc_df['intersection'].apply(lambda x: 'FOSTER AND NORTHWEST HIGHWAY' if x=='NORTHWEST HIGHWAY AND FOSTER' else x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Manipulate datatypes for preprocessing. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "rlc_df['violations'] = rlc_df['violations'].astype(int)\n",
    "rlc_df['latitude'] = rlc_df['latitude'].astype(float)\n",
    "rlc_df['longitude'] = rlc_df['longitude'].astype(float)\n",
    "rlc_df['violation_date'] = pd.to_datetime(rlc_df['violation_date'])\n",
    "rlc_df['month'] = rlc_df['violation_date'].apply(lambda x: int(x.month))\n",
    "rlc_df['day'] = rlc_df['violation_date'].apply(lambda x: int(x.day))  # fixed from dat to day!\n",
    "\n",
    "rlc_df['weekday'] = rlc_df['violation_date'].apply(lambda x: int(datetime.weekday(x)))\n",
    "rlc_df['year'] = rlc_df['violation_date'].apply(lambda x: int(x.year))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create daily_violations TABLE "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('cam_locations',), ('cam_startend',), ('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('intersection_cams',), ('all_hours',), ('int_startend',), ('signal_crashes',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('daily_violations',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(rlc_df, 'daily_violations', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3) Build cam_locations and cam_startend TABLEs\n",
    "Build tables from cam_locs AND TABLE from cam_startend.\n",
    "We wll bring in camera locations from data portal and identify the start and end date for each camera.  We will use the start end dates to develop a natural experiment where we identify which cameras were turned on or off during our study timeframe."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Camera location preprocessing\n",
    "Make a df with info for each camera\n",
    "Will contain the following:\n",
    "- camera_id\n",
    "- location\n",
    "- start date (when was the camera turned on)\n",
    "- end date (when was the camera turned off)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "cam_df = rlc_df.copy()\n",
    "cam_df['start'] = cam_df['camera_id'].apply(lambda x: None)\n",
    "cam_df['end'] = cam_df['camera_id'].apply(lambda x: None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NA values in cam_startend:\n",
      "camera_id    0\n",
      "start        0\n",
      "end          0\n",
      "dtype: int64\n",
      "\n",
      "Describe cam_startend:\n",
      "       camera_id                start                  end\n",
      "count        316                  316                  316\n",
      "unique       316                   16                   18\n",
      "top         1011  2017-01-02 00:00:00  2021-02-17 00:00:00\n",
      "freq           1                  253                  253\n",
      "first        NaN  2017-01-02 00:00:00  2017-05-29 00:00:00\n",
      "last         NaN  2018-03-05 00:00:00  2021-02-17 00:00:00\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cam_start = cam_df.groupby(['camera_id'])['violation_date'].min().reset_index()\n",
    "cam_end = cam_df.groupby(['camera_id'])['violation_date'].max().reset_index()\n",
    "\n",
    "cam_startend = cam_start.copy()\n",
    "\n",
    "#print(cam_end[cam_end['camera_id']=='1503'].values[0][1])  # for testing output\n",
    "cam_startend['end'] = cam_start['camera_id'].apply(lambda x: cam_end[cam_end['camera_id']==x].values[0][1])\n",
    "\n",
    "cam_startend.rename(columns={\"violation_date\": \"start\"}, inplace=True)\n",
    "                                                   \n",
    "print('NA values in cam_startend:', cam_startend.isna().sum(), end='\\n\\n', sep='\\n')\n",
    "\n",
    "print('Describe cam_startend:', cam_startend.describe(), end='\\n\\n', sep='\\n')\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Make a df that has camera locations and intersections\n",
    "Intersections are present (and addresses), but we do not have lat/long info for all cams"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Two of them\n",
      "    camera_id           intersection                  address violation_date  \\\n",
      "75      1421     DAMEN AND DIVERSEY  2000 W DIVERSEY PARKWAY     2017-11-30   \n",
      "76      1421  LARAMIE AND FULLERTON    2400 N LARAMIE AVENUE     2021-02-17   \n",
      "\n",
      "    violations   latitude  longitude  month  day  weekday  year  \n",
      "75           1  41.932394 -87.678173     11   30        3  2017  \n",
      "76           6  41.924152 -87.756295     12   31        6  2021  \n",
      "\n",
      "Damen/Diversey 1\n",
      "Laramie/Fullerton: 854\n",
      "Total cams 316\n"
     ]
    }
   ],
   "source": [
    "# we had some incorrect data in the code below, but have a creative fix.\n",
    "cam_locs = rlc_df.groupby(['camera_id', 'intersection']).max().reset_index()\n",
    "cam_locs.head()\n",
    "\n",
    "# we find there is a mismatch between lens, one of them is duplicated\n",
    "len(cam_locs)  # 364 total\n",
    "len(cam_locs['camera_id'].unique()) # 363\n",
    "\n",
    "cam_locs[cam_locs['camera_id'].duplicated()]  # 1421 is dupe\n",
    "print('Two of them\\n', cam_locs[cam_locs['camera_id'] == '1421'])  # we see two of them\n",
    "print()\n",
    "\n",
    "# Which one is it?\n",
    "print('Damen/Diversey', rlc_df[(rlc_df['camera_id']=='1421') & (rlc_df['intersection']=='DAMEN AND DIVERSEY')]['camera_id'].count())\n",
    "print('Laramie/Fullerton:', rlc_df[(rlc_df['camera_id']=='1421') & (rlc_df['intersection']=='LARAMIE AND FULLERTON')]['camera_id'].count())\n",
    "\n",
    "# Turns out that a camera has two locations. One was only used one time.  We drop it.\n",
    "cam_locs = cam_locs[(cam_locs['camera_id']!='1421') | (cam_locs['intersection']!='DAMEN AND DIVERSEY')]\n",
    "print(\"Total cams\", len(cam_locs))  # 363 total (got rid of the bad one)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "camera_id          0\n",
       "intersection       0\n",
       "address            0\n",
       "violation_date     0\n",
       "violations         0\n",
       "latitude          17\n",
       "longitude         17\n",
       "month              0\n",
       "day                0\n",
       "weekday            0\n",
       "year               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cam_locs.isna().sum()  # missing location for 19 cameras.  Let's fix it"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like we also are missing 19 of the 363 cam locations.  Let's look it up!\n",
    "\n",
    "We actually changed this code.  We have gone to the maps to get intersection locations, but will leave old code in case we ever need exact location of cameras."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 316 entries, 0 to 316\n",
      "Data columns (total 11 columns):\n",
      "camera_id         316 non-null object\n",
      "intersection      316 non-null object\n",
      "address           316 non-null object\n",
      "violation_date    316 non-null datetime64[ns]\n",
      "violations        316 non-null int64\n",
      "latitude          299 non-null float64\n",
      "longitude         299 non-null float64\n",
      "month             316 non-null int64\n",
      "day               316 non-null int64\n",
      "weekday           316 non-null int64\n",
      "year              316 non-null int64\n",
      "dtypes: datetime64[ns](1), float64(2), int64(5), object(3)\n",
      "memory usage: 29.6+ KB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "camera_id          0\n",
       "intersection       0\n",
       "address            0\n",
       "violation_date     0\n",
       "violations         0\n",
       "latitude          17\n",
       "longitude         17\n",
       "month              0\n",
       "day                0\n",
       "weekday            0\n",
       "year               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cam_locs.info()\n",
    "cam_locs.isna().sum() # No longer missing location for 19 cameras."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create cam_locations TABLE\n",
    "from cam_locs AND cam_startend from cam_startend"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('cam_startend',), ('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('intersection_cams',), ('all_hours',), ('int_startend',), ('signal_crashes',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('daily_violations',), ('cam_locations',)]\n",
      "[('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('intersection_cams',), ('all_hours',), ('int_startend',), ('signal_crashes',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('daily_violations',), ('cam_locations',), ('cam_startend',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(cam_locs, 'cam_locations', c, conn)\n",
    "make_table(cam_startend, 'cam_startend', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Fix lat/long data\n",
    "We still have missing lat/long info for our rlc_df.  Let's fix it\n",
    "Before moving on.  Now that we have cam_locs, we can fix our rlc_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "intersection          0\n",
       "camera_id             0\n",
       "address               0\n",
       "violation_date        0\n",
       "violations            0\n",
       "latitude          20358\n",
       "longitude         20358\n",
       "month                 0\n",
       "day                   0\n",
       "weekday               0\n",
       "year                  0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rlc_df.isna().sum()  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Change cam position to intersection lat/lon\n",
    "During EDA, we found out that five cameras were in completely wrong lat/long location.  \n",
    "Several others were located a little too far from the intersection to work properly.  When we rebuild the db, we will use bigger number than 30 m.\n",
    "\n",
    "I intend to use intersection locations in lieu of camera locations.  I hope this makes all of my position data consistent for gathering crash info, and eliminates chicago data portal errors.\n",
    "\n",
    "When using cam location, it is sometimes up to 35 m up road where cam position is.  This would cause us to misidentify crashes from other intersections or miss some in the intersection of interest. \n",
    "\n",
    "Remedy: Use center point of intersection for all cams.  I have done this in the intersection_chars (looked it up on google maps for all 180+ intersections)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['protected_turn', 'total_lanes', 'medians', 'exit', 'split', 'way',\n",
       "       'underpass', 'no_left', 'angled', 'triangle', 'one_way', 'turn_lanes',\n",
       "       'lat', 'long', 'rlc', 'intersection', 'daily_traffic'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int_df.columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "def location_correction(int_df, intersect, latlong):\n",
    "    # lookup function from intersection df to get the lat long\n",
    "    # int_df is the intersection characteristic frame from 1) above\n",
    "    # intersect is the intersection name used to link tables/df\n",
    "    # latlong is either 'lat' or 'long'\n",
    "    if latlong == 'lat':\n",
    "        lat = int_df[int_df['intersection']==intersect]['lat'].values[0]\n",
    "        if lat==None: print(lat, intersect)\n",
    "        return lat\n",
    "    else:\n",
    "        long = int_df[int_df['intersection']==intersect]['long'].values[0]\n",
    "        return long\n",
    "\n",
    "cam_locs['latitude'] = cam_locs['intersection'].apply(lambda x: location_correction(int_df, x, 'lat'))\n",
    "cam_locs['longitude'] = cam_locs['intersection'].apply(lambda x: location_correction(int_df, x, 'long'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                 4700 WESTERN\n",
       "2           CICERO AND ADDISON\n",
       "3    LAKE SHORE DR AND BELMONT\n",
       "4       SACRAMENTO AND CHICAGO\n",
       "5         PETERSON AND WESTERN\n",
       "Name: intersection, dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rlc_df.intersection.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>protected_turn</th>\n",
       "      <th>total_lanes</th>\n",
       "      <th>medians</th>\n",
       "      <th>exit</th>\n",
       "      <th>split</th>\n",
       "      <th>way</th>\n",
       "      <th>underpass</th>\n",
       "      <th>no_left</th>\n",
       "      <th>angled</th>\n",
       "      <th>triangle</th>\n",
       "      <th>one_way</th>\n",
       "      <th>turn_lanes</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>rlc</th>\n",
       "      <th>intersection</th>\n",
       "      <th>daily_traffic</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>IRVING PARK AND KILPATRICK</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>41.953395</td>\n",
       "      <td>-87.744635</td>\n",
       "      <td>1</td>\n",
       "      <td>IRVING PARK AND KILPATRICK</td>\n",
       "      <td>37100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            protected_turn  total_lanes  medians  exit  split  \\\n",
       "IRVING PARK AND KILPATRICK               1            6        0     0      0   \n",
       "\n",
       "                            way  underpass  no_left  angled  triangle  \\\n",
       "IRVING PARK AND KILPATRICK    4          0        0       0         0   \n",
       "\n",
       "                            one_way  turn_lanes        lat       long  rlc  \\\n",
       "IRVING PARK AND KILPATRICK        1           3  41.953395 -87.744635    1   \n",
       "\n",
       "                                          intersection  daily_traffic  \n",
       "IRVING PARK AND KILPATRICK  IRVING PARK AND KILPATRICK          37100  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int_df[int_df['intersection']=='IRVING PARK AND KILPATRICK']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make my cameras have a location that is center of intersection instead of exact cam location."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>intersection</th>\n",
       "      <th>camera_id</th>\n",
       "      <th>address</th>\n",
       "      <th>violation_date</th>\n",
       "      <th>violations</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>weekday</th>\n",
       "      <th>year</th>\n",
       "      <th>location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>4700 WESTERN</td>\n",
       "      <td>2141</td>\n",
       "      <td>4700 S WESTERN AVENUE</td>\n",
       "      <td>2019-06-05</td>\n",
       "      <td>3</td>\n",
       "      <td>41.808378</td>\n",
       "      <td>-87.684571</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2019</td>\n",
       "      <td>(41.808442084381, -87.68418270817706)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CICERO AND ADDISON</td>\n",
       "      <td>1612</td>\n",
       "      <td>3600 N CICERO AVENUE</td>\n",
       "      <td>2019-06-05</td>\n",
       "      <td>7</td>\n",
       "      <td>41.946164</td>\n",
       "      <td>-87.747215</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2019</td>\n",
       "      <td>(41.946123417859745, -87.74705265633155)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>LAKE SHORE DR AND BELMONT</td>\n",
       "      <td>1413</td>\n",
       "      <td>400 W BELMONT AVE</td>\n",
       "      <td>2019-06-05</td>\n",
       "      <td>75</td>\n",
       "      <td>41.940241</td>\n",
       "      <td>-87.639639</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2019</td>\n",
       "      <td>(41.94046398185605, -87.6383448872575)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>SACRAMENTO AND CHICAGO</td>\n",
       "      <td>1814</td>\n",
       "      <td>3000 W CHICAGO AVENUE</td>\n",
       "      <td>2019-06-05</td>\n",
       "      <td>8</td>\n",
       "      <td>41.895705</td>\n",
       "      <td>-87.702219</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2019</td>\n",
       "      <td>(41.89559271274954, -87.70223070169483)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>PETERSON AND WESTERN</td>\n",
       "      <td>1014</td>\n",
       "      <td>2400 W PETERSON</td>\n",
       "      <td>2019-06-05</td>\n",
       "      <td>6</td>\n",
       "      <td>41.990609</td>\n",
       "      <td>-87.689735</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2019</td>\n",
       "      <td>(41.99053050329496, -87.68961714584131)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                intersection camera_id                address violation_date  \\\n",
       "0               4700 WESTERN      2141  4700 S WESTERN AVENUE     2019-06-05   \n",
       "2         CICERO AND ADDISON      1612   3600 N CICERO AVENUE     2019-06-05   \n",
       "3  LAKE SHORE DR AND BELMONT      1413      400 W BELMONT AVE     2019-06-05   \n",
       "4     SACRAMENTO AND CHICAGO      1814  3000 W CHICAGO AVENUE     2019-06-05   \n",
       "5       PETERSON AND WESTERN      1014        2400 W PETERSON     2019-06-05   \n",
       "\n",
       "   violations   latitude  longitude  month  day  weekday  year  \\\n",
       "0           3  41.808378 -87.684571      6    5        2  2019   \n",
       "2           7  41.946164 -87.747215      6    5        2  2019   \n",
       "3          75  41.940241 -87.639639      6    5        2  2019   \n",
       "4           8  41.895705 -87.702219      6    5        2  2019   \n",
       "5           6  41.990609 -87.689735      6    5        2  2019   \n",
       "\n",
       "                                   location  \n",
       "0     (41.808442084381, -87.68418270817706)  \n",
       "2  (41.946123417859745, -87.74705265633155)  \n",
       "3    (41.94046398185605, -87.6383448872575)  \n",
       "4   (41.89559271274954, -87.70223070169483)  \n",
       "5   (41.99053050329496, -87.68961714584131)  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#⏳⏳⏳⏳⏳  (8min on my macbook pro)\n",
    "def read_loc(int_df, intersection):\n",
    "    # This function looks up the new camera/intersction location using the intersection name as key\n",
    "    cam = int_df[int_df['intersection']==intersection]\n",
    "    #print(cam)\n",
    "    return (float(cam['lat']), float(cam['long']))\n",
    "        \n",
    "\n",
    "\n",
    "# create a location column so we only have to do it once\n",
    "rlc_df['location'] = rlc_df['intersection'].apply(lambda x: read_loc(int_df, x))\n",
    "rlc_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# then add in the new lat longs to the df\n",
    "rlc_df['latitude'] = rlc_df['location'].apply(lambda x: x[0])\n",
    "rlc_df['longitude'] = rlc_df['location'].apply(lambda x: x[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([], dtype=object)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rlc_df[rlc_df.latitude.isna()]['intersection'].unique()  # which intersections am I still missing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>intersection</th>\n",
       "      <th>camera_id</th>\n",
       "      <th>address</th>\n",
       "      <th>violation_date</th>\n",
       "      <th>violations</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>weekday</th>\n",
       "      <th>year</th>\n",
       "      <th>location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>4700 WESTERN</td>\n",
       "      <td>2141</td>\n",
       "      <td>4700 S WESTERN AVENUE</td>\n",
       "      <td>2019-06-05</td>\n",
       "      <td>3</td>\n",
       "      <td>41.808442</td>\n",
       "      <td>-87.684183</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2019</td>\n",
       "      <td>(41.808442084381, -87.68418270817706)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CICERO AND ADDISON</td>\n",
       "      <td>1612</td>\n",
       "      <td>3600 N CICERO AVENUE</td>\n",
       "      <td>2019-06-05</td>\n",
       "      <td>7</td>\n",
       "      <td>41.946123</td>\n",
       "      <td>-87.747053</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2019</td>\n",
       "      <td>(41.946123417859745, -87.74705265633155)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>LAKE SHORE DR AND BELMONT</td>\n",
       "      <td>1413</td>\n",
       "      <td>400 W BELMONT AVE</td>\n",
       "      <td>2019-06-05</td>\n",
       "      <td>75</td>\n",
       "      <td>41.940464</td>\n",
       "      <td>-87.638345</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2019</td>\n",
       "      <td>(41.94046398185605, -87.6383448872575)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>SACRAMENTO AND CHICAGO</td>\n",
       "      <td>1814</td>\n",
       "      <td>3000 W CHICAGO AVENUE</td>\n",
       "      <td>2019-06-05</td>\n",
       "      <td>8</td>\n",
       "      <td>41.895593</td>\n",
       "      <td>-87.702231</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2019</td>\n",
       "      <td>(41.89559271274954, -87.70223070169483)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>PETERSON AND WESTERN</td>\n",
       "      <td>1014</td>\n",
       "      <td>2400 W PETERSON</td>\n",
       "      <td>2019-06-05</td>\n",
       "      <td>6</td>\n",
       "      <td>41.990531</td>\n",
       "      <td>-87.689617</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2019</td>\n",
       "      <td>(41.99053050329496, -87.68961714584131)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                intersection camera_id                address violation_date  \\\n",
       "0               4700 WESTERN      2141  4700 S WESTERN AVENUE     2019-06-05   \n",
       "2         CICERO AND ADDISON      1612   3600 N CICERO AVENUE     2019-06-05   \n",
       "3  LAKE SHORE DR AND BELMONT      1413      400 W BELMONT AVE     2019-06-05   \n",
       "4     SACRAMENTO AND CHICAGO      1814  3000 W CHICAGO AVENUE     2019-06-05   \n",
       "5       PETERSON AND WESTERN      1014        2400 W PETERSON     2019-06-05   \n",
       "\n",
       "   violations   latitude  longitude  month  day  weekday  year  \\\n",
       "0           3  41.808442 -87.684183      6    5        2  2019   \n",
       "2           7  41.946123 -87.747053      6    5        2  2019   \n",
       "3          75  41.940464 -87.638345      6    5        2  2019   \n",
       "4           8  41.895593 -87.702231      6    5        2  2019   \n",
       "5           6  41.990531 -87.689617      6    5        2  2019   \n",
       "\n",
       "                                   location  \n",
       "0     (41.808442084381, -87.68418270817706)  \n",
       "2  (41.946123417859745, -87.74705265633155)  \n",
       "3    (41.94046398185605, -87.6383448872575)  \n",
       "4   (41.89559271274954, -87.70223070169483)  \n",
       "5   (41.99053050329496, -87.68961714584131)  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rlc_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get rid of location column.  We have latitude and longitude in separate columns and don't want BLOBs for now\n",
    "if 'location' in rlc_df.columns:\n",
    "    rlc_df.drop(columns=['location'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create daily_violations TABLE\n",
    "Now we have gone back to rlc_df and fixd the location data to line up with intersections.\n",
    "We commit it to our database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('intersection_cams',), ('all_hours',), ('int_startend',), ('signal_crashes',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('cam_locations',), ('cam_startend',), ('daily_violations',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(rlc_df, 'daily_violations', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4) Build intersection_cams TABLE\n",
    "Create a table to store intersection and camera data using int_cams df."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preprocess intersection_cams\n",
    "We will now focus on trying to bring rlc intersections to our crashes\n",
    "We find that we have 363 cameras at 183 intersections.\n",
    "\n",
    "Group my data by intersection and pull out the individual camera_id.  We place them into separate columns.  This allows us to store the individual camera ids associated with each intersection should we need them for a query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>intersection</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>cam1</th>\n",
       "      <th>cam2</th>\n",
       "      <th>cam3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>111TH AND HALSTED</td>\n",
       "      <td>41.692362</td>\n",
       "      <td>-87.642423</td>\n",
       "      <td>2422</td>\n",
       "      <td>2424</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>115TH AND HALSTED</td>\n",
       "      <td>41.685089</td>\n",
       "      <td>-87.642094</td>\n",
       "      <td>2552</td>\n",
       "      <td>2553</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>119TH AND HALSTED</td>\n",
       "      <td>41.677774</td>\n",
       "      <td>-87.641930</td>\n",
       "      <td>2402</td>\n",
       "      <td>2404</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>31ST ST AND MARTIN LUTHER KING DRIVE</td>\n",
       "      <td>41.838441</td>\n",
       "      <td>-87.617338</td>\n",
       "      <td>2121</td>\n",
       "      <td>2123</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>35TH AND WESTERN</td>\n",
       "      <td>41.830281</td>\n",
       "      <td>-87.684775</td>\n",
       "      <td>2091</td>\n",
       "      <td>2092</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           intersection   latitude  longitude  cam1  cam2  \\\n",
       "0                     111TH AND HALSTED  41.692362 -87.642423  2422  2424   \n",
       "1                     115TH AND HALSTED  41.685089 -87.642094  2552  2553   \n",
       "2                     119TH AND HALSTED  41.677774 -87.641930  2402  2404   \n",
       "3  31ST ST AND MARTIN LUTHER KING DRIVE  41.838441 -87.617338  2121  2123   \n",
       "4                      35TH AND WESTERN  41.830281 -87.684775  2091  2092   \n",
       "\n",
       "   cam3  \n",
       "0  None  \n",
       "1  None  \n",
       "2  None  \n",
       "3  None  \n",
       "4  None  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int_cams = cam_locs.groupby(['intersection']) \\\n",
    "                    .agg({'latitude':pd.Series.max, 'longitude':pd.Series.max,}) \\\n",
    "                    .reset_index()\n",
    "\n",
    "int_cams['cam1'] = int_cams['intersection'] \\\n",
    "                            .apply(lambda x: cam_locs[cam_locs['intersection']==x]['camera_id'].iloc[0])\n",
    "\n",
    "int_cams['cam2'] = int_cams['intersection'].apply( \\\n",
    "                            lambda x: None if len(cam_locs[cam_locs['intersection']==x])==1 \\\n",
    "                            else cam_locs[cam_locs['intersection']==x]['camera_id'].iloc[1])\n",
    "\n",
    "int_cams['cam3'] = int_cams['intersection'].apply( \\\n",
    "                            lambda x: None if len(cam_locs[cam_locs['intersection']==x])<3 \\\n",
    "                            else cam_locs[cam_locs['intersection']==x]['camera_id'].iloc[2])                             \n",
    "\n",
    "int_cams.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Cameras 316\n",
      "Total Intersections 157\n"
     ]
    }
   ],
   "source": [
    "print('Total Cameras', len(cam_locs))\n",
    "print('Total Intersections', len(int_cams))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create intersection_cams TABLE \n",
    "Create it from int_cams"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('all_hours',), ('int_startend',), ('signal_crashes',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('intersection_cams',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(int_cams, 'intersection_cams', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5) Create signal_crashes and all_crashes TABLE\n",
    "Create all_crashes from crash_df.  All crashes will include all signal crashes in the city.\n",
    "\n",
    "Create signal_crashes from crash_df. This will only be the signal crashes at red light cam intersections.\n",
    "\n",
    "The crash data has a column for 'intersection related crashes', and another for 'traffic signal'.  We use this to filter our data.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Crash data preprocessing\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Crash Data\n",
    "crash_data = client.get(\"85ca-t3if\", \n",
    "                     where=\"crash_date > \\'2016-01-01T00:00:00.000\\'\",\n",
    "                     limit=1000000,\n",
    "                    )\n",
    "\n",
    "crash_df = pd.DataFrame.from_records(crash_data) # Convert to pandas DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop a few columns we don't need, including location (we have lat/long)\n",
    "dropme = ['statements_taken_i', 'private_property_i', 'photos_taken_i', 'dooring_i', 'date_police_notified','location']\n",
    "\n",
    "crash_df.drop(columns=dropme, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "crash_record_id                       0\n",
       "rd_no                              4004\n",
       "crash_date                            0\n",
       "posted_speed_limit                    0\n",
       "traffic_control_device                0\n",
       "device_condition                      0\n",
       "weather_condition                     0\n",
       "lighting_condition                    0\n",
       "first_crash_type                      0\n",
       "trafficway_type                       0\n",
       "alignment                             0\n",
       "roadway_surface_cond                  0\n",
       "road_defect                           0\n",
       "report_type                       11718\n",
       "crash_type                            0\n",
       "damage                                0\n",
       "prim_contributory_cause               0\n",
       "sec_contributory_cause                0\n",
       "street_no                             0\n",
       "street_direction                      3\n",
       "street_name                           1\n",
       "beat_of_occurrence                    5\n",
       "num_units                             0\n",
       "most_severe_injury                  962\n",
       "injuries_total                      951\n",
       "injuries_fatal                      951\n",
       "injuries_incapacitating             951\n",
       "injuries_non_incapacitating         951\n",
       "injuries_reported_not_evident       951\n",
       "injuries_no_indication              951\n",
       "injuries_unknown                    951\n",
       "crash_hour                            0\n",
       "crash_day_of_week                     0\n",
       "crash_month                           0\n",
       "latitude                           2614\n",
       "longitude                          2614\n",
       "lane_cnt                         282192\n",
       "intersection_related_i           365601\n",
       "hit_and_run_i                    333250\n",
       "crash_date_est_i                 437462\n",
       "work_zone_i                      469479\n",
       "work_zone_type                   470103\n",
       "workers_present_i                471761\n",
       "dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crash_df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have 2.5k entries that have no location.  Let's drop them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "crash_df.dropna(subset=['latitude',], inplace=True)  # get rid of na locations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Contents of crash data\n",
    "\n",
    "Let's look at what is in the data.  We have over 30 columns.  Much of it is categorical.  We want to look and see what is in here before we continue.  \n",
    "\n",
    "We start with over half a million crashes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "traffic_control_device ['NO CONTROLS' 'STOP SIGN/FLASHER' 'TRAFFIC SIGNAL' 'UNKNOWN'\n",
      " 'OTHER WARNING SIGN' 'PEDESTRIAN CROSSING SIGN' 'OTHER' 'YIELD'\n",
      " 'OTHER REG. SIGN' 'LANE USE MARKING' 'DELINEATORS'\n",
      " 'FLASHING CONTROL SIGNAL' 'POLICE/FLAGMAN' 'RAILROAD CROSSING GATE'\n",
      " 'SCHOOL ZONE' 'OTHER RAILROAD CROSSING' 'RR CROSSING SIGN' 'NO PASSING'\n",
      " 'BICYCLE CROSSING SIGN']\n",
      "device_condition ['NO CONTROLS' 'FUNCTIONING PROPERLY' 'NOT FUNCTIONING' 'UNKNOWN' 'OTHER'\n",
      " 'FUNCTIONING IMPROPERLY' 'WORN REFLECTIVE MATERIAL' 'MISSING']\n",
      "weather_condition ['CLEAR' 'RAIN' 'UNKNOWN' 'SNOW' 'CLOUDY/OVERCAST' 'SLEET/HAIL'\n",
      " 'FREEZING RAIN/DRIZZLE' 'FOG/SMOKE/HAZE' 'OTHER' 'BLOWING SNOW'\n",
      " 'SEVERE CROSS WIND GATE' 'BLOWING SAND, SOIL, DIRT']\n",
      "lighting_condition ['DAYLIGHT' 'DARKNESS' 'DARKNESS, LIGHTED ROAD' 'UNKNOWN' 'DAWN' 'DUSK']\n",
      "first_crash_type ['TURNING' 'REAR END' 'PARKED MOTOR VEHICLE'\n",
      " 'SIDESWIPE OPPOSITE DIRECTION' 'ANGLE' 'SIDESWIPE SAME DIRECTION'\n",
      " 'OTHER OBJECT' 'HEAD ON' 'PEDESTRIAN' 'FIXED OBJECT' 'PEDALCYCLIST'\n",
      " 'REAR TO FRONT' 'REAR TO SIDE' 'REAR TO REAR' 'ANIMAL'\n",
      " 'OTHER NONCOLLISION' 'OVERTURNED' 'TRAIN']\n",
      "trafficway_type ['ONE-WAY' 'NOT DIVIDED' 'FOUR WAY' 'DIVIDED - W/MEDIAN (NOT RAISED)'\n",
      " 'DIVIDED - W/MEDIAN BARRIER' 'UNKNOWN' 'PARKING LOT' 'ALLEY'\n",
      " 'T-INTERSECTION' 'OTHER' 'CENTER TURN LANE' 'FIVE POINT, OR MORE'\n",
      " 'TRAFFIC ROUTE' 'NOT REPORTED' 'Y-INTERSECTION' 'RAMP' 'DRIVEWAY'\n",
      " 'UNKNOWN INTERSECTION TYPE' 'ROUNDABOUT' 'L-INTERSECTION']\n",
      "alignment ['STRAIGHT AND LEVEL' 'CURVE ON HILLCREST' 'STRAIGHT ON GRADE'\n",
      " 'CURVE, LEVEL' 'STRAIGHT ON HILLCREST' 'CURVE ON GRADE']\n",
      "roadway_surface_cond ['DRY' 'WET' 'UNKNOWN' 'SNOW OR SLUSH' 'ICE' 'OTHER' 'SAND, MUD, DIRT']\n",
      "road_defect ['NO DEFECTS' 'UNKNOWN' 'RUT, HOLES' 'SHOULDER DEFECT' 'OTHER'\n",
      " 'WORN SURFACE' 'DEBRIS ON ROADWAY']\n",
      "report_type ['ON SCENE' 'NOT ON SCENE (DESK REPORT)' nan 'AMENDED']\n",
      "crash_type ['NO INJURY / DRIVE AWAY' 'INJURY AND / OR TOW DUE TO CRASH']\n",
      "hit_and_run_i [nan 'Y' 'N']\n",
      "damage ['OVER $1,500' '$500 OR LESS' '$501 - $1,500']\n",
      "prim_contributory_cause ['IMPROPER BACKING' 'FAILING TO YIELD RIGHT-OF-WAY' 'UNABLE TO DETERMINE'\n",
      " 'OPERATING VEHICLE IN ERRATIC, RECKLESS, CARELESS, NEGLIGENT OR AGGRESSIVE MANNER'\n",
      " 'IMPROPER OVERTAKING/PASSING' 'IMPROPER LANE USAGE'\n",
      " 'IMPROPER TURNING/NO SIGNAL' 'EXCEEDING SAFE SPEED FOR CONDITIONS'\n",
      " 'FOLLOWING TOO CLOSELY' 'DISTRACTION - FROM INSIDE VEHICLE'\n",
      " 'DRIVING SKILLS/KNOWLEDGE/EXPERIENCE'\n",
      " 'FAILING TO REDUCE SPEED TO AVOID CRASH' 'DISREGARDING TRAFFIC SIGNALS'\n",
      " 'NOT APPLICABLE' 'EXCEEDING AUTHORIZED SPEED LIMIT'\n",
      " 'ROAD CONSTRUCTION/MAINTENANCE' 'WEATHER'\n",
      " 'DISTRACTION - FROM OUTSIDE VEHICLE' 'EQUIPMENT - VEHICLE CONDITION'\n",
      " 'DISREGARDING STOP SIGN'\n",
      " 'UNDER THE INFLUENCE OF ALCOHOL/DRUGS (USE WHEN ARREST IS EFFECTED)'\n",
      " 'VISION OBSCURED (SIGNS, TREE LIMBS, BUILDINGS, ETC.)'\n",
      " 'DRIVING ON WRONG SIDE/WRONG WAY' 'DISREGARDING OTHER TRAFFIC SIGNS'\n",
      " 'ROAD ENGINEERING/SURFACE/MARKING DEFECTS' 'PHYSICAL CONDITION OF DRIVER'\n",
      " 'HAD BEEN DRINKING (USE WHEN ARREST IS NOT MADE)' 'TEXTING'\n",
      " 'EVASIVE ACTION DUE TO ANIMAL, OBJECT, NONMOTORIST'\n",
      " 'DISREGARDING YIELD SIGN' 'CELL PHONE USE OTHER THAN TEXTING'\n",
      " 'DISREGARDING ROAD MARKINGS' 'BICYCLE ADVANCING LEGALLY ON RED LIGHT'\n",
      " 'ANIMAL'\n",
      " 'DISTRACTION - OTHER ELECTRONIC DEVICE (NAVIGATION DEVICE, DVD PLAYER, ETC.)'\n",
      " 'RELATED TO BUS STOP' 'TURNING RIGHT ON RED' 'OBSTRUCTED CROSSWALKS'\n",
      " 'PASSING STOPPED SCHOOL BUS' 'MOTORCYCLE ADVANCING LEGALLY ON RED LIGHT']\n",
      "sec_contributory_cause ['UNABLE TO DETERMINE' 'NOT APPLICABLE' 'FAILING TO YIELD RIGHT-OF-WAY'\n",
      " 'IMPROPER LANE USAGE' 'FOLLOWING TOO CLOSELY'\n",
      " 'DISTRACTION - FROM INSIDE VEHICLE'\n",
      " 'FAILING TO REDUCE SPEED TO AVOID CRASH' 'DISREGARDING STOP SIGN'\n",
      " 'EXCEEDING AUTHORIZED SPEED LIMIT' 'DRIVING SKILLS/KNOWLEDGE/EXPERIENCE'\n",
      " 'WEATHER' 'EQUIPMENT - VEHICLE CONDITION' 'PHYSICAL CONDITION OF DRIVER'\n",
      " 'DISREGARDING OTHER TRAFFIC SIGNS' 'IMPROPER BACKING'\n",
      " 'DISREGARDING TRAFFIC SIGNALS'\n",
      " 'HAD BEEN DRINKING (USE WHEN ARREST IS NOT MADE)'\n",
      " 'IMPROPER TURNING/NO SIGNAL' 'IMPROPER OVERTAKING/PASSING'\n",
      " 'ROAD CONSTRUCTION/MAINTENANCE'\n",
      " 'UNDER THE INFLUENCE OF ALCOHOL/DRUGS (USE WHEN ARREST IS EFFECTED)'\n",
      " 'OPERATING VEHICLE IN ERRATIC, RECKLESS, CARELESS, NEGLIGENT OR AGGRESSIVE MANNER'\n",
      " 'DISTRACTION - FROM OUTSIDE VEHICLE'\n",
      " 'VISION OBSCURED (SIGNS, TREE LIMBS, BUILDINGS, ETC.)'\n",
      " 'DRIVING ON WRONG SIDE/WRONG WAY' 'DISREGARDING ROAD MARKINGS'\n",
      " 'DISREGARDING YIELD SIGN' 'ROAD ENGINEERING/SURFACE/MARKING DEFECTS'\n",
      " 'EXCEEDING SAFE SPEED FOR CONDITIONS'\n",
      " 'EVASIVE ACTION DUE TO ANIMAL, OBJECT, NONMOTORIST'\n",
      " 'DISTRACTION - OTHER ELECTRONIC DEVICE (NAVIGATION DEVICE, DVD PLAYER, ETC.)'\n",
      " 'PASSING STOPPED SCHOOL BUS' 'MOTORCYCLE ADVANCING LEGALLY ON RED LIGHT'\n",
      " 'CELL PHONE USE OTHER THAN TEXTING' 'TURNING RIGHT ON RED' 'ANIMAL'\n",
      " 'RELATED TO BUS STOP' 'OBSTRUCTED CROSSWALKS'\n",
      " 'BICYCLE ADVANCING LEGALLY ON RED LIGHT' 'TEXTING']\n",
      "street_no ['2158' '8301' '1632' ... '12437' '11181' '11167']\n",
      "street_direction ['N' 'S' 'E' 'W']\n",
      "street_name ['MARMORA AVE' 'CICERO AVE' '67TH ST' ... 'CRESTLINE ST' 'PARK PL'\n",
      " 'BALDWIN AVE']\n",
      "beat_of_occurrence ['2515' '834' '331' '224' '1732' '1115' '1822' '225' '1034' '1113' '131'\n",
      " '511' '2511' '234' '623' '1011' '222' '612' '1412' '2411' '1821' '1654'\n",
      " '814' '524' '1922' '1414' '815' '1235' '1925' '1221' '411' '2223' '2412'\n",
      " '1111' '334' '523' '1911' '1832' '1611' '1513' '733' '1224' '1032' '413'\n",
      " '1214' '1431' '423' '1634' '2521' '1621' '114' '233' '2211' '1523' '324'\n",
      " '2212' '1921' '1724' '824' '2534' '1733' '1215' '1834' '911' '822' '611'\n",
      " '1713' '1622' '211' '2423' '823' '833' '1813' '1532' '724' '1934' '1935'\n",
      " '1135' '1933' '1031' '933' '614' '2531' '1233' '1234' '711' '1023' '2031'\n",
      " '1923' '923' '1012' '1824' '633' '1133' '1432' '2432' '111' '1711' '722'\n",
      " '1632' '1912' '1422' '1013' '2533' '1712' '533' '2523' '122' '212' '2032'\n",
      " '121' '1433' '1932' '2024' '1212' '725' '831' '1022' '2222' '1924' '1913'\n",
      " '714' '1222' '811' '432' '2433' '1424' '313' '1624' '422' '2022' '2512'\n",
      " '2233' '632' '1731' '1812' '2524' '2532' '1915' '622' '1522' '2535' '821'\n",
      " '912' '914' '932' '2213' '1121' '1124' '1434' '913' '412' '434' '1232'\n",
      " '123' '812' '431' '2424' '1411' '513' '532' '1814' '1211' '825' '1531'\n",
      " '1631' '2525' '1722' '835' '915' '832' '1833' '634' '2033' '1123' '924'\n",
      " '433' '221' '1223' '2012' '2011' '132' '631' '323' '1524' '713' '1122'\n",
      " '1225' '1125' '1014' '214' '223' '1112' '935' '813' '732' '2013' '2522'\n",
      " '925' '1723' '2413' '1623' '934' '1612' '322' '1511' '621' '2232' '1633'\n",
      " '1512' '726' '1131' '421' '1613' '1213' '1132' '1021' '922' '321' '624'\n",
      " '1811' '1421' '1033' '231' '2514' '731' '734' '1914' '512' '931' '1931'\n",
      " '312' '2221' '522' '735' '1413' '715' '2431' '124' '332' '1423' '1134'\n",
      " '235' '333' '2234' '414' '424' '1231' '921' '1533' '1024' '1823' '213'\n",
      " '112' '133' '712' '314' '1114' '1831' '2422' '311' '2513' '113' '215'\n",
      " '2023' '613' '232' '723' '1614' '531' '1651' nan]\n",
      "num_units ['2' '3' '1' '4' '5' '8' '7' '6' '9' '11' '10' '12' '14' '15' '18' '16']\n",
      "most_severe_injury ['NO INDICATION OF INJURY' 'NONINCAPACITATING INJURY'\n",
      " 'REPORTED, NOT EVIDENT' 'INCAPACITATING INJURY' 'FATAL' nan]\n",
      "injuries_fatal ['0' '1' nan '2' '3' '4']\n",
      "injuries_incapacitating ['0' '1' '2' nan '4' '3' '5' '6' '7']\n",
      "injuries_non_incapacitating ['0' '1' '2' '3' '4' '5' nan '6' '10' '7' '8' '21' '14' '18' '9' '12' '11'\n",
      " '16']\n",
      "injuries_reported_not_evident ['0' '1' '2' nan '3' '5' '4' '6' '8' '11' '7' '9' '10' '15']\n",
      "injuries_no_indication ['3' '2' '1' '5' '4' '0' '6' '8' '9' nan '7' '11' '10' '24' '12' '27' '19'\n",
      " '16' '13' '30' '50' '18' '21' '17' '14' '42' '46' '26' '15' '22' '61'\n",
      " '38' '20' '34' '29' '40' '37' '28' '31' '36' '33' '32' '25' '39']\n",
      "injuries_unknown ['0' nan]\n",
      "crash_hour ['17' '16' '10' '1' '22' '14' '8' '4' '12' '11' '18' '15' '7' '19' '6' '5'\n",
      " '20' '3' '21' '13' '9' '23' '2' '0']\n",
      "crash_day_of_week ['4' '6' '7' '5' '2' '3' '1']\n",
      "crash_month ['7' '6' '3' '8' '5' '1' '12' '2' '10' '11' '9' '4']\n",
      "latitude ['41.919663833' '41.741803599' '41.773455972' ... '41.9874699'\n",
      " '41.765745906' '41.85856185']\n",
      "longitude ['-87.773287883' '-87.740953582' '-87.585022352' ... '-87.689423075'\n",
      " '-87.651852167' '-87.627360226']\n",
      "lane_cnt [nan '4' '2' '6' '3' '0' '5' '1' '99' '8' '10' '7' '40' '9' '16' '12' '28'\n",
      " '20' '11' '14' '22' '15' '41' '433634' '25' '30' '35' '1191625' '60' '19'\n",
      " '400' '21' '902' '100' '299679' '17' '45' '218474' '80' '24' '44']\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "intersection_related_i [nan 'Y' 'N']\n",
      "crash_date_est_i [nan 'Y' 'N']\n",
      "work_zone_i [nan 'Y' 'N']\n",
      "work_zone_type [nan 'MAINTENANCE' 'CONSTRUCTION' 'UTILITY' 'UNKNOWN']\n",
      "workers_present_i [nan 'Y' 'N']\n"
     ]
    }
   ],
   "source": [
    "# What's in this data?\n",
    "col_interest = ['traffic_control_device', 'device_condition', 'weather_condition',\n",
    "       'lighting_condition', 'first_crash_type', 'trafficway_type',\n",
    "       'alignment', 'roadway_surface_cond', 'road_defect', 'report_type',\n",
    "       'crash_type', 'hit_and_run_i', 'damage', 'prim_contributory_cause',\n",
    "       'sec_contributory_cause', 'street_no', 'street_direction',\n",
    "       'street_name', 'beat_of_occurrence', 'num_units', 'most_severe_injury', \n",
    "        'injuries_fatal', 'injuries_incapacitating',\n",
    "       'injuries_non_incapacitating', 'injuries_reported_not_evident',\n",
    "       'injuries_no_indication', 'injuries_unknown', 'crash_hour',\n",
    "       'crash_day_of_week', 'crash_month', 'latitude', 'longitude', 'lane_cnt',\n",
    "       'intersection_related_i', 'crash_date_est_i',\n",
    "       'work_zone_i', 'work_zone_type',\n",
    "       'workers_present_i']\n",
    "\n",
    "for col in col_interest:\n",
    "    print(col, crash_df[col].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create all_crashes TABLE\n",
    "We create this and will continue to process it to deliver a table with only crashes at signaled intersections."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('hourly_congestion',), ('hourly_weather',), ('region_data',), ('all_hours',), ('int_startend',), ('signal_crashes',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('intersection_cams',), ('all_crashes',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(crash_df, 'all_crashes', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filter for desired crashes (intersections with signal)\n",
    "The contents of crash data section has helped us.  \n",
    "We can filter 'traffic_control_device' == 'TRAFFIC SIGNAL'.  \n",
    "We can filter 'intersection_related_i' == 'Y'\n",
    "\n",
    "This will leave us with only crashes that occurred at/because of intersections, and with a signal at the intersection.\n",
    "\n",
    "intersection_related_i: A field observation by the police officer whether an intersection played a role in the crash. Does not represent whether or not the crash occurred within the intersection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "crash_df = crash_df[(crash_df['traffic_control_device']=='TRAFFIC SIGNAL') & \\\n",
    "                    (crash_df['intersection_related_i']=='Y')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add intersection to my signal crashes\n",
    "\n",
    "Look up each lat long for crash and get the corresponding intersection if we have it.\n",
    "\n",
    "Tried to look up using geo equations took forever.  Also slow using pythag thrm, took long time.  Finally settled on using a simple box.  I basically check to see if the crash location is inside a box defining the intersection (within 50m of the intersection centerpoint)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "#⏳⏳⏳⏳⏳\n",
    "# This takes too long to process.  Let's simplify it and make it a box instead.\n",
    "box_side = 100  # effectively makes it check for crash being within 50m of intersection\n",
    "box_lat = box_side / 111070 / 2 # 111070 is meters in deg lat in Chicago\n",
    "box_long = box_side / 83000 / 2 # 83000 is meters in deg long in Chicago\n",
    "\n",
    "def box_check(lat, long, int_df):\n",
    "    answer = (int_df[  (int_df['lat'] > (lat - box_lat)) & \n",
    "                      (int_df['lat'] < (lat + box_lat)) &\n",
    "                      (int_df['long'] > (long - box_long)) &\n",
    "                      (int_df['long'] < (long + box_long))\n",
    "                     ])\n",
    "    if answer.empty: return None\n",
    "    return answer['intersection'].values[0]\n",
    "    \n",
    "# THIS SEEMS TO WORK WITH SPEED AND ELIMINATES MEMORY PROBLEM\n",
    "crash_df['intersection'] = crash_df.apply(lambda x: box_check(float(x.latitude), \n",
    "                                                              float(x.longitude), \n",
    "                                                              int_df), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Extract the integer date data from the crash_date column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# #Ex: df[['two', 'three']] = df[['two', 'three']].astype(float)\n",
    "crash_df['crash_date'] = pd.to_datetime(crash_df['crash_date'])\n",
    "crash_df['year'] = crash_df['crash_date'].apply(lambda x: int(x.year))\n",
    "crash_df['month'] = crash_df['crash_date'].apply(lambda x: int(x.month))\n",
    "crash_df['day'] = crash_df['crash_date'].apply(lambda x: int(x.day))\n",
    "crash_df['hour'] = crash_df['crash_date'].apply(lambda x: int(x.hour))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 61162 entries, 5 to 472495\n",
      "Data columns (total 48 columns):\n",
      "crash_record_id                  61162 non-null object\n",
      "rd_no                            60716 non-null object\n",
      "crash_date                       61162 non-null datetime64[ns]\n",
      "posted_speed_limit               61162 non-null object\n",
      "traffic_control_device           61162 non-null object\n",
      "device_condition                 61162 non-null object\n",
      "weather_condition                61162 non-null object\n",
      "lighting_condition               61162 non-null object\n",
      "first_crash_type                 61162 non-null object\n",
      "trafficway_type                  61162 non-null object\n",
      "alignment                        61162 non-null object\n",
      "roadway_surface_cond             61162 non-null object\n",
      "road_defect                      61162 non-null object\n",
      "report_type                      59300 non-null object\n",
      "crash_type                       61162 non-null object\n",
      "damage                           61162 non-null object\n",
      "prim_contributory_cause          61162 non-null object\n",
      "sec_contributory_cause           61162 non-null object\n",
      "street_no                        61162 non-null object\n",
      "street_direction                 61162 non-null object\n",
      "street_name                      61162 non-null object\n",
      "beat_of_occurrence               61162 non-null object\n",
      "num_units                        61162 non-null object\n",
      "most_severe_injury               61144 non-null object\n",
      "injuries_total                   61144 non-null object\n",
      "injuries_fatal                   61144 non-null object\n",
      "injuries_incapacitating          61144 non-null object\n",
      "injuries_non_incapacitating      61144 non-null object\n",
      "injuries_reported_not_evident    61144 non-null object\n",
      "injuries_no_indication           61144 non-null object\n",
      "injuries_unknown                 61144 non-null object\n",
      "crash_hour                       61162 non-null object\n",
      "crash_day_of_week                61162 non-null object\n",
      "crash_month                      61162 non-null object\n",
      "latitude                         61162 non-null object\n",
      "longitude                        61162 non-null object\n",
      "lane_cnt                         25966 non-null object\n",
      "intersection_related_i           61162 non-null object\n",
      "hit_and_run_i                    11705 non-null object\n",
      "crash_date_est_i                 2674 non-null object\n",
      "work_zone_i                      461 non-null object\n",
      "work_zone_type                   359 non-null object\n",
      "workers_present_i                98 non-null object\n",
      "intersection                     8795 non-null object\n",
      "year                             61162 non-null int64\n",
      "month                            61162 non-null int64\n",
      "day                              61162 non-null int64\n",
      "hour                             61162 non-null int64\n",
      "dtypes: datetime64[ns](1), int64(4), object(43)\n",
      "memory usage: 22.9+ MB\n"
     ]
    }
   ],
   "source": [
    "crash_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create signal_crashes TABLE\n",
    "Create from crash_df.  These 60k crashes are labeled with region_id and intersection as foreign keys for SQL queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('hourly_congestion',), ('hourly_weather',), ('region_data',), ('all_hours',), ('int_startend',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('intersection_cams',), ('all_crashes',), ('signal_crashes',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(crash_df, 'signal_crashes', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6) Build hourly_congestion TABLE \n",
    "\n",
    "Chicago tracks periodic (irregular) bus speed data.  The data is aggregated so that we get an average bus_speed for every region at least once an hour while buses are running.  \n",
    "\n",
    "Build from all_traffic DataFrame.\n",
    "\n",
    "For this one, we have to combine two different datasets.  Chicago changed the way data was recorded in 2018.  Columns are similar, but more data collected."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### First dataset\n",
    "Won't be able to table it until we get both datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Congestion Data\n",
    "traffic_df = client.get(\"emtn-qqdi\", \n",
    "                     #where=\"TIME > \\'2015-01-01T00:00:00.000\\'\",\n",
    "                     where='TIME > \\'2016-01-01T00:00:00.000\\'',\n",
    "                     limit=10000000,\n",
    "                    )\n",
    "\n",
    "traffic_df = pd.DataFrame.from_records(traffic_df) # Convert to pandas DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "traffic_df.rename(columns={'number_of_reads':'num_reads'}, inplace=True)\n",
    "traffic_df['time'] = pd.to_datetime(traffic_df['time'])\n",
    "traffic_df['bus_count'] = traffic_df['bus_count'].astype(int)\n",
    "traffic_df['num_reads'] = traffic_df['num_reads'].astype(int)\n",
    "traffic_df['speed'] = traffic_df['speed'].astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Second dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Congestion data from later\n",
    "traffic_df2 = client.get(\"kf7e-cur8\", #2018 to present\n",
    "                     select='time, region_id, speed, bus_count, num_reads',  # this set is huge, so we won't get all       \n",
    "                     where=\"TIME < \\'2021-01-31T00:00:00.000\\'\",\n",
    "                     limit=10000000,\n",
    "                    )\n",
    "\n",
    "# Convert to pandas DataFrame\n",
    "traffic_df2 = pd.DataFrame.from_records(traffic_df2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "#traffic2_df.rename(columns={'number_of_reads':'num_reads'}, inplace=True)\n",
    "traffic_df2['time'] = pd.to_datetime(traffic_df2['time'])\n",
    "traffic_df2['bus_count'] = traffic_df2['bus_count'].astype(int)\n",
    "traffic_df2['num_reads'] = traffic_df2['num_reads'].astype(int)\n",
    "traffic_df2['speed'] = traffic_df2['speed'].astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preprocess hourly_congestion\n",
    "We have two separate traffic_dfs.  There is data prior to 2018 and after in two different api endpoints.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4067819 entries, 0 to 4067818\n",
      "Data columns (total 5 columns):\n",
      "time         datetime64[ns]\n",
      "region_id    object\n",
      "speed        float64\n",
      "bus_count    int64\n",
      "num_reads    int64\n",
      "dtypes: datetime64[ns](1), float64(1), int64(2), object(1)\n",
      "memory usage: 155.2+ MB\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3507463 entries, 0 to 3507462\n",
      "Data columns (total 5 columns):\n",
      "time         datetime64[ns]\n",
      "region_id    object\n",
      "bus_count    int64\n",
      "num_reads    int64\n",
      "speed        float64\n",
      "dtypes: datetime64[ns](1), float64(1), int64(2), object(1)\n",
      "memory usage: 133.8+ MB\n"
     ]
    }
   ],
   "source": [
    "traffic_df.head()\n",
    "traffic_df2.head()\n",
    "traffic_df2.info()\n",
    "print()\n",
    "traffic_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "traffic dfs merged\n"
     ]
    }
   ],
   "source": [
    "# Merge my two data sets for congestion by region\n",
    "all_traffic = pd.merge(traffic_df, traffic_df2, how='outer')\n",
    "print('traffic dfs merged')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "added hour column\n",
      "added day column\n",
      "added month column\n",
      "added year column\n",
      "added weekday column\n"
     ]
    }
   ],
   "source": [
    "all_traffic['hour'] = all_traffic['time'].dt.hour\n",
    "print('added hour column')\n",
    "\n",
    "all_traffic['day'] = all_traffic.time.dt.day\n",
    "print('added day column')\n",
    "\n",
    "all_traffic['month'] = all_traffic.time.dt.month\n",
    "print('added month column')\n",
    "\n",
    "all_traffic['year'] = all_traffic.time.dt.year\n",
    "print('added year column')\n",
    "\n",
    "all_traffic['weekday'] = all_traffic.time.dt.weekday\n",
    "print('added weekday column')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7365206\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1261906 entries, 0 to 1261905\n",
      "Data columns (total 9 columns):\n",
      "year         1261906 non-null int64\n",
      "month        1261906 non-null int64\n",
      "day          1261906 non-null int64\n",
      "hour         1261906 non-null int64\n",
      "region_id    1261906 non-null object\n",
      "bus_count    1261906 non-null float64\n",
      "num_reads    1261906 non-null float64\n",
      "speed        1261906 non-null float64\n",
      "weekday      1261906 non-null float64\n",
      "dtypes: float64(4), int64(4), object(1)\n",
      "memory usage: 86.6+ MB\n"
     ]
    }
   ],
   "source": [
    "print(len(all_traffic))  # lots of dupes \n",
    "all_traffic = all_traffic.groupby(['year', 'month', 'day', 'hour', 'region_id']).mean().reset_index()\n",
    "all_traffic.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "      <th>region_id</th>\n",
       "      <th>bus_count</th>\n",
       "      <th>num_reads</th>\n",
       "      <th>speed</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6.8</td>\n",
       "      <td>120.2</td>\n",
       "      <td>27.062</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>34.0</td>\n",
       "      <td>390.8</td>\n",
       "      <td>24.834</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>16.2</td>\n",
       "      <td>265.8</td>\n",
       "      <td>26.004</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>25.0</td>\n",
       "      <td>350.8</td>\n",
       "      <td>16.526</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>33.6</td>\n",
       "      <td>511.4</td>\n",
       "      <td>18.136</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  day  hour region_id  bus_count  num_reads   speed  weekday\n",
       "0  2016      1    1     0         1        6.8      120.2  27.062      4.0\n",
       "1  2016      1    1     0        10       34.0      390.8  24.834      4.0\n",
       "2  2016      1    1     0        11       16.2      265.8  26.004      4.0\n",
       "3  2016      1    1     0        12       25.0      350.8  16.526      4.0\n",
       "4  2016      1    1     0        13       33.6      511.4  18.136      4.0"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_traffic.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('hourly_weather',), ('region_data',), ('all_hours',), ('int_startend',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('intersection_cams',), ('all_crashes',), ('signal_crashes',), ('hourly_congestion',)]\n"
     ]
    }
   ],
   "source": [
    "# couple minutes\n",
    "make_table(all_traffic, 'hourly_congestion', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Speed fix for congestion\n",
    "Congestion is measured by average bus speed.\n",
    "\n",
    "The problem:\n",
    "- Overnight (between 11 and 5am) we have few bus routes.\n",
    "- Some regions have no buses overnight\n",
    "- Some regions have only a few buses \n",
    "- Some buses are ending routes and have only a few reads\n",
    "- Some buses are stationary (next morning staging)\n",
    "\n",
    "The fix:\n",
    "- replace speed for few buses/reads if speed is low\n",
    "- we assume low buses/reads to be overnight when congestion is minimal\n",
    "- replacement speed is a low congestion quantile speed (90% or so)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Let's get the 0.90 quantile for every region, and then use that to fill in missing data\n",
    "\n",
    "regions_90 = all_traffic.groupby(['region_id'])['speed'].quantile(0.9).reset_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>region_id</th>\n",
       "      <th>speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>25.093400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>26.433333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>26.796467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>23.156667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>24.148333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  region_id      speed\n",
       "0         1  25.093400\n",
       "1        10  26.433333\n",
       "2        11  26.796467\n",
       "3        12  23.156667\n",
       "4        13  24.148333"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "regions_90.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "#### 5 MINUTES OR SO\n",
    "\n",
    "#my read on this is that few buses run 24/7, so the data is unreliable.  \n",
    "# buses stage for next morning.  You can see them all along Clark, LSD etc.  \n",
    "# They have speed=0 and may be recording.  Could talk to owner of dataset.\n",
    "\n",
    "# I will draw the cutoff at 100 reads, 5 buses, speed < 10\n",
    "# in that case I will put in a quantile speed for the region\n",
    "\n",
    "\n",
    "def speed_check(bus, speed, reads, region_id, regions_90):\n",
    "    if (bus <= 5 or reads < 100) and speed < 25 or speed > 40:\n",
    "        return regions_90[regions_90['region_id']==region_id]['speed'].values[0]\n",
    "    else:\n",
    "        return speed\n",
    "    \n",
    "\n",
    "# apply is SLOOOOOOWWW, but not sure how else to accomplish this without iterating\n",
    "all_traffic['speed'] = all_traffic.apply(lambda x: speed_check(x.bus_count, x.speed, x.num_reads, x.region_id, regions_90), axis=1)\n",
    "      \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create hourly_congestion TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('hourly_weather',), ('region_data',), ('all_hours',), ('int_startend',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('intersection_cams',), ('all_crashes',), ('signal_crashes',), ('hourly_congestion',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(all_traffic, 'hourly_congestion', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Add quantile speed\n",
    "In models, congestion was not as useful as the EDA suggested.  I would like to create a quantile speed for each area.  That way downtown traffic midday is not worse than peak traffic in other regions.\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "      <th>region_id</th>\n",
       "      <th>bus_count</th>\n",
       "      <th>num_reads</th>\n",
       "      <th>speed</th>\n",
       "      <th>weekday</th>\n",
       "      <th>quantile_speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6.800000</td>\n",
       "      <td>120.200000</td>\n",
       "      <td>27.0620</td>\n",
       "      <td>4.0</td>\n",
       "      <td>97.835179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>69.500000</td>\n",
       "      <td>25.0934</td>\n",
       "      <td>4.0</td>\n",
       "      <td>82.096567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>58</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>25.0934</td>\n",
       "      <td>4.0</td>\n",
       "      <td>82.096567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>87</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3.500000</td>\n",
       "      <td>58.166667</td>\n",
       "      <td>25.0934</td>\n",
       "      <td>4.0</td>\n",
       "      <td>82.096567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>116</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3.666667</td>\n",
       "      <td>43.000000</td>\n",
       "      <td>25.0934</td>\n",
       "      <td>4.0</td>\n",
       "      <td>82.096567</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     year month day hour region_id  bus_count   num_reads    speed  weekday  \\\n",
       "0    2016     1   1    0         1   6.800000  120.200000  27.0620      4.0   \n",
       "29   2016     1   1    1         1   5.000000   69.500000  25.0934      4.0   \n",
       "58   2016     1   1    2         1   3.000000   34.000000  25.0934      4.0   \n",
       "87   2016     1   1    3         1   3.500000   58.166667  25.0934      4.0   \n",
       "116  2016     1   1    4         1   3.666667   43.000000  25.0934      4.0   \n",
       "\n",
       "     quantile_speed  \n",
       "0         97.835179  \n",
       "29        82.096567  \n",
       "58        82.096567  \n",
       "87        82.096567  \n",
       "116       82.096567  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import timeit\n",
    "\n",
    "# This is slow!  Original speed took about 0.01s per iteration!  (200 minutes!!!!)\n",
    "# used dictionary of smaller dfs and looked up inside lambda function (0.003 per iteration) (60 minutes!!!)\n",
    "\n",
    "# split into 29 dfs, cycle perform lambda on each (no need for df.apply x, could do series.apply) (2 minutes...)\n",
    "\n",
    "def quant_speed(speed, df):\n",
    "    # returns a percentile speed for the region\n",
    "    quant = percentileofscore(df['speed'], speed)\n",
    "    return quant\n",
    "    \n",
    "    \n",
    "new_df = pd.DataFrame(columns=all_traffic.columns)\n",
    "new_df['quantile_speed'] = None\n",
    "\n",
    "for i in all_traffic.region_id.unique():\n",
    "    df = all_traffic[all_traffic['region_id'] == str(i)]\n",
    "    df['quantile_speed'] = df['speed'].apply(lambda x: quant_speed(x, df))\n",
    "    new_df = new_df.append(df)\n",
    "    \n",
    "new_df.head()\n",
    "# filtering first and pull from dict increased speed by 15x by using dictionary lookup\n",
    "# starttime = timeit.default_timer()\n",
    "# all_traffic['quantile_speed'] = all_traffic.apply(lambda x: quant_speed(x.speed, x.region_id, df_dict[x.region_id]), axis=1)\n",
    "# print(\"The time difference is :\", timeit.default_timer() - starttime)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['1', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19',\n",
       "       '2', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29',\n",
       "       '3', '4', '5', '6', '7', '8', '9'], dtype=object)"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.region_id.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('hourly_weather',), ('region_data',), ('all_hours',), ('int_startend',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('intersection_cams',), ('all_crashes',), ('signal_crashes',), ('hourly_congestion',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(new_df, 'hourly_congestion', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7) Create hourly_weather \n",
    "Created from wx_df dataframe.  \n",
    "\n",
    "This data is hourly historical data from a bulk query from openweathermap.org for the city of Chicago.\n",
    "Columns of interest:\n",
    "- temp (deg K)\n",
    "- rain_1h (mm rain in last hour)\n",
    "- snow_1h (mm snow in last hour)\n",
    "- weather_main (description)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preprocess hourly_weather"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import weather data from csv\n",
    "wx_df = pd.read_csv('data/chi_wx.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Clear', 'Clouds', 'Snow', 'Mist', 'Rain', 'Drizzle', 'Haze',\n",
       "       'Fog', 'Thunderstorm', 'Smoke', 'Tornado', 'Dust', 'Squall'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wx_df.weather_main.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 57306 entries, 0 to 57305\n",
      "Data columns (total 26 columns):\n",
      "dt                     57306 non-null int64\n",
      "dt_iso                 57306 non-null object\n",
      "timezone               57306 non-null int64\n",
      "city_name              57306 non-null object\n",
      "lat                    57306 non-null float64\n",
      "lon                    57306 non-null float64\n",
      "temp                   57306 non-null float64\n",
      "feels_like             57306 non-null float64\n",
      "temp_min               57306 non-null float64\n",
      "temp_max               57306 non-null float64\n",
      "pressure               57306 non-null int64\n",
      "sea_level              0 non-null float64\n",
      "grnd_level             0 non-null float64\n",
      "humidity               57306 non-null int64\n",
      "wind_speed             57306 non-null float64\n",
      "wind_deg               57306 non-null int64\n",
      "rain_1h                6750 non-null float64\n",
      "rain_3h                820 non-null float64\n",
      "snow_1h                1991 non-null float64\n",
      "snow_3h                113 non-null float64\n",
      "clouds_all             57306 non-null int64\n",
      "weather_id             57306 non-null int64\n",
      "weather_main           57306 non-null object\n",
      "weather_description    57306 non-null object\n",
      "weather_icon           57306 non-null object\n",
      "time                   57306 non-null datetime64[ns, UTC]\n",
      "dtypes: datetime64[ns, UTC](1), float64(13), int64(7), object(5)\n",
      "memory usage: 11.4+ MB\n"
     ]
    }
   ],
   "source": [
    "wx_df['time'] = pd.to_datetime(wx_df['dt_iso'].apply(lambda x: x[:-4]))\n",
    "wx_df.head()\n",
    "wx_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "wx_df['rain_3h'] = wx_df['rain_3h'].fillna(0)\n",
    "wx_df['rain_1h'] = wx_df['rain_1h'].fillna(0)\n",
    "wx_df['snow_3h'] = wx_df['snow_3h'].fillna(0)\n",
    "wx_df['snow_1h'] = wx_df['snow_1h'].fillna(0)\n",
    "wx_df['temp'] = wx_df['temp_max']\n",
    "wx_df['year'] = wx_df.time.dt.year\n",
    "wx_df['month'] = wx_df.time.dt.month\n",
    "wx_df['day'] = wx_df.time.dt.day\n",
    "wx_df['hour'] = wx_df.time.dt.hour\n",
    "wx_df['weekday'] = wx_df.time.dt.weekday"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>dt</th>\n",
       "      <th>timezone</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>temp</th>\n",
       "      <th>feels_like</th>\n",
       "      <th>temp_min</th>\n",
       "      <th>temp_max</th>\n",
       "      <th>pressure</th>\n",
       "      <th>sea_level</th>\n",
       "      <th>...</th>\n",
       "      <th>rain_3h</th>\n",
       "      <th>snow_1h</th>\n",
       "      <th>snow_3h</th>\n",
       "      <th>clouds_all</th>\n",
       "      <th>weather_id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>5.730600e+04</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>5.730600e+04</td>\n",
       "      <td>5.730600e+04</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>57306.000000</td>\n",
       "      <td>57306.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>1.517459e+09</td>\n",
       "      <td>-19338.016962</td>\n",
       "      <td>4.187811e+01</td>\n",
       "      <td>-8.762980e+01</td>\n",
       "      <td>285.037523</td>\n",
       "      <td>279.694640</td>\n",
       "      <td>281.447694</td>\n",
       "      <td>285.037523</td>\n",
       "      <td>1016.055526</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.046291</td>\n",
       "      <td>0.015908</td>\n",
       "      <td>0.001911</td>\n",
       "      <td>61.439361</td>\n",
       "      <td>750.328482</td>\n",
       "      <td>2017.599745</td>\n",
       "      <td>6.354082</td>\n",
       "      <td>15.741196</td>\n",
       "      <td>11.457648</td>\n",
       "      <td>3.002181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>5.588673e+07</td>\n",
       "      <td>1739.719658</td>\n",
       "      <td>1.421098e-14</td>\n",
       "      <td>2.842196e-14</td>\n",
       "      <td>11.189403</td>\n",
       "      <td>13.319188</td>\n",
       "      <td>10.489184</td>\n",
       "      <td>11.189403</td>\n",
       "      <td>7.644737</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.695364</td>\n",
       "      <td>0.127158</td>\n",
       "      <td>0.066529</td>\n",
       "      <td>32.006648</td>\n",
       "      <td>111.551016</td>\n",
       "      <td>1.768262</td>\n",
       "      <td>3.527509</td>\n",
       "      <td>8.822858</td>\n",
       "      <td>6.909577</td>\n",
       "      <td>2.001015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>1.420070e+09</td>\n",
       "      <td>-21600.000000</td>\n",
       "      <td>4.187811e+01</td>\n",
       "      <td>-8.762980e+01</td>\n",
       "      <td>245.370000</td>\n",
       "      <td>233.180000</td>\n",
       "      <td>242.150000</td>\n",
       "      <td>245.370000</td>\n",
       "      <td>965.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>200.000000</td>\n",
       "      <td>2015.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>1.469056e+09</td>\n",
       "      <td>-21600.000000</td>\n",
       "      <td>4.187811e+01</td>\n",
       "      <td>-8.762980e+01</td>\n",
       "      <td>276.103000</td>\n",
       "      <td>269.340000</td>\n",
       "      <td>273.710000</td>\n",
       "      <td>276.103000</td>\n",
       "      <td>1011.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>800.000000</td>\n",
       "      <td>2016.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>1.517942e+09</td>\n",
       "      <td>-18000.000000</td>\n",
       "      <td>4.187811e+01</td>\n",
       "      <td>-8.762980e+01</td>\n",
       "      <td>284.260000</td>\n",
       "      <td>278.355000</td>\n",
       "      <td>280.645000</td>\n",
       "      <td>284.260000</td>\n",
       "      <td>1016.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>802.000000</td>\n",
       "      <td>2018.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>1.565992e+09</td>\n",
       "      <td>-18000.000000</td>\n",
       "      <td>4.187811e+01</td>\n",
       "      <td>-8.762980e+01</td>\n",
       "      <td>294.850000</td>\n",
       "      <td>291.510000</td>\n",
       "      <td>290.714500</td>\n",
       "      <td>294.850000</td>\n",
       "      <td>1021.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>90.000000</td>\n",
       "      <td>803.000000</td>\n",
       "      <td>2019.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>1.613430e+09</td>\n",
       "      <td>-18000.000000</td>\n",
       "      <td>4.187811e+01</td>\n",
       "      <td>-8.762980e+01</td>\n",
       "      <td>311.480000</td>\n",
       "      <td>309.890000</td>\n",
       "      <td>306.132000</td>\n",
       "      <td>311.480000</td>\n",
       "      <td>1044.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>35.000000</td>\n",
       "      <td>8.400000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>804.000000</td>\n",
       "      <td>2021.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>31.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 dt      timezone           lat           lon          temp  \\\n",
       "count  5.730600e+04  57306.000000  5.730600e+04  5.730600e+04  57306.000000   \n",
       "mean   1.517459e+09 -19338.016962  4.187811e+01 -8.762980e+01    285.037523   \n",
       "std    5.588673e+07   1739.719658  1.421098e-14  2.842196e-14     11.189403   \n",
       "min    1.420070e+09 -21600.000000  4.187811e+01 -8.762980e+01    245.370000   \n",
       "25%    1.469056e+09 -21600.000000  4.187811e+01 -8.762980e+01    276.103000   \n",
       "50%    1.517942e+09 -18000.000000  4.187811e+01 -8.762980e+01    284.260000   \n",
       "75%    1.565992e+09 -18000.000000  4.187811e+01 -8.762980e+01    294.850000   \n",
       "max    1.613430e+09 -18000.000000  4.187811e+01 -8.762980e+01    311.480000   \n",
       "\n",
       "         feels_like      temp_min      temp_max      pressure  sea_level  ...  \\\n",
       "count  57306.000000  57306.000000  57306.000000  57306.000000        0.0  ...   \n",
       "mean     279.694640    281.447694    285.037523   1016.055526        NaN  ...   \n",
       "std       13.319188     10.489184     11.189403      7.644737        NaN  ...   \n",
       "min      233.180000    242.150000    245.370000    965.000000        NaN  ...   \n",
       "25%      269.340000    273.710000    276.103000   1011.000000        NaN  ...   \n",
       "50%      278.355000    280.645000    284.260000   1016.000000        NaN  ...   \n",
       "75%      291.510000    290.714500    294.850000   1021.000000        NaN  ...   \n",
       "max      309.890000    306.132000    311.480000   1044.000000        NaN  ...   \n",
       "\n",
       "            rain_3h       snow_1h       snow_3h    clouds_all    weather_id  \\\n",
       "count  57306.000000  57306.000000  57306.000000  57306.000000  57306.000000   \n",
       "mean       0.046291      0.015908      0.001911     61.439361    750.328482   \n",
       "std        0.695364      0.127158      0.066529     32.006648    111.551016   \n",
       "min        0.000000      0.000000      0.000000      0.000000    200.000000   \n",
       "25%        0.000000      0.000000      0.000000     40.000000    800.000000   \n",
       "50%        0.000000      0.000000      0.000000     75.000000    802.000000   \n",
       "75%        0.000000      0.000000      0.000000     90.000000    803.000000   \n",
       "max       35.000000      8.400000      6.000000    100.000000    804.000000   \n",
       "\n",
       "               year         month           day          hour       weekday  \n",
       "count  57306.000000  57306.000000  57306.000000  57306.000000  57306.000000  \n",
       "mean    2017.599745      6.354082     15.741196     11.457648      3.002181  \n",
       "std        1.768262      3.527509      8.822858      6.909577      2.001015  \n",
       "min     2015.000000      1.000000      1.000000      0.000000      0.000000  \n",
       "25%     2016.000000      3.000000      8.000000      5.000000      1.000000  \n",
       "50%     2018.000000      6.000000     16.000000     11.000000      3.000000  \n",
       "75%     2019.000000      9.000000     23.000000     17.000000      5.000000  \n",
       "max     2021.000000     12.000000     31.000000     23.000000      6.000000  \n",
       "\n",
       "[8 rows x 25 columns]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wx_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    wx_df = wx_df.drop(columns=['dt', \n",
    "                        'dt_iso', \n",
    "                        'timezone', \n",
    "                        'city_name', \n",
    "                        'lat', \n",
    "                        'lon', \n",
    "                        'feels_like', \n",
    "                        'temp_min', \n",
    "                        'temp_max',\n",
    "                        'pressure',\n",
    "                        'sea_level',\n",
    "                        'grnd_level',\n",
    "                        'humidity',\n",
    "                        'wind_speed',\n",
    "                        'wind_deg',\n",
    "                        'clouds_all',\n",
    "                        'weather_description',\n",
    "                        'weather_icon',\n",
    "                        'weather_id',\n",
    "                       ], axis=1)\n",
    "except:\n",
    "    print('Failed')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "57306\n",
      "0\n",
      "Total hours in 6 years: 52596.0\n",
      "Unique entries: 57306\n",
      "\n",
      "2015-01-01 00:00:00+00:00 2021-02-15 23:00:00+00:00\n",
      "Total hours in 6 years (-1 mos): 51852.0\n"
     ]
    }
   ],
   "source": [
    "print(len(wx_df))\n",
    "print(wx_df.duplicated().sum())\n",
    "\n",
    "\n",
    "print('Total hours in 6 years:', 365.25 * 24 * 6)\n",
    "print('Unique entries:', len(wx_df.drop_duplicates()))  \n",
    "# missing a few entries (700+ out of 52k)  Am I missing a month??\n",
    "\n",
    "print()\n",
    "print(wx_df.time.min(), wx_df.time.max())  # OH!!!!  I am missin last month\n",
    "print('Total hours in 6 years (-1 mos):', 365.25 * 24 * 6 - 31 * 24)  # okay, we are only missing a few\n",
    "\n",
    "\n",
    "wx_df.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create hourly_weather TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('region_data',), ('all_hours',), ('int_startend',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('intersection_cams',), ('all_crashes',), ('signal_crashes',), ('hourly_congestion',), ('hourly_weather',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(wx_df, 'hourly_weather', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8) Create region_data TABLE \n",
    "Created from region_df dataframe.\n",
    "\n",
    "Wanted to add a table which contained the region information.  Congestion TABLE uses region_id to break city into 29 traffic regions.  We use the regions for congestion (bus speed) data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preprocess region_data TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "# THis time we only grab what we need\n",
    "\n",
    "region_df = client.get(\"kf7e-cur8\", # regional congestion current data\n",
    "                         select='region_id, region, description, north, south, east, west',\n",
    "                         limit=1000\n",
    "                    )\n",
    "\n",
    "# Convert to pandas DataFrame\n",
    "region_df = pd.DataFrame.from_records(region_df)  # should only return most recent for each region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "region_df = region_df.groupby('region_id').max().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 29 entries, 0 to 28\n",
      "Data columns (total 7 columns):\n",
      "region_id      29 non-null object\n",
      "region         29 non-null object\n",
      "description    29 non-null object\n",
      "north          29 non-null float64\n",
      "south          29 non-null float64\n",
      "east           29 non-null float64\n",
      "west           29 non-null float64\n",
      "dtypes: float64(4), object(3)\n",
      "memory usage: 1.7+ KB\n"
     ]
    }
   ],
   "source": [
    "# need these as floats so we can compare them\n",
    "region_df[['north', 'south', 'east', 'west']] = region_df[['north', 'south', 'east', 'west']].astype(float)\n",
    "region_df.info()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add region to my crash df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 61162 entries, 5 to 472495\n",
      "Data columns (total 48 columns):\n",
      "crash_record_id                  61162 non-null object\n",
      "rd_no                            60716 non-null object\n",
      "crash_date                       61162 non-null datetime64[ns]\n",
      "posted_speed_limit               61162 non-null object\n",
      "traffic_control_device           61162 non-null object\n",
      "device_condition                 61162 non-null object\n",
      "weather_condition                61162 non-null object\n",
      "lighting_condition               61162 non-null object\n",
      "first_crash_type                 61162 non-null object\n",
      "trafficway_type                  61162 non-null object\n",
      "alignment                        61162 non-null object\n",
      "roadway_surface_cond             61162 non-null object\n",
      "road_defect                      61162 non-null object\n",
      "report_type                      59300 non-null object\n",
      "crash_type                       61162 non-null object\n",
      "damage                           61162 non-null object\n",
      "prim_contributory_cause          61162 non-null object\n",
      "sec_contributory_cause           61162 non-null object\n",
      "street_no                        61162 non-null object\n",
      "street_direction                 61162 non-null object\n",
      "street_name                      61162 non-null object\n",
      "beat_of_occurrence               61162 non-null object\n",
      "num_units                        61162 non-null object\n",
      "most_severe_injury               61144 non-null object\n",
      "injuries_total                   61144 non-null object\n",
      "injuries_fatal                   61144 non-null object\n",
      "injuries_incapacitating          61144 non-null object\n",
      "injuries_non_incapacitating      61144 non-null object\n",
      "injuries_reported_not_evident    61144 non-null object\n",
      "injuries_no_indication           61144 non-null object\n",
      "injuries_unknown                 61144 non-null object\n",
      "crash_hour                       61162 non-null object\n",
      "crash_day_of_week                61162 non-null object\n",
      "crash_month                      61162 non-null object\n",
      "latitude                         61162 non-null float64\n",
      "longitude                        61162 non-null float64\n",
      "lane_cnt                         25966 non-null object\n",
      "intersection_related_i           61162 non-null object\n",
      "hit_and_run_i                    11705 non-null object\n",
      "crash_date_est_i                 2674 non-null object\n",
      "work_zone_i                      461 non-null object\n",
      "work_zone_type                   359 non-null object\n",
      "workers_present_i                98 non-null object\n",
      "intersection                     8795 non-null object\n",
      "year                             61162 non-null int64\n",
      "month                            61162 non-null int64\n",
      "day                              61162 non-null int64\n",
      "hour                             61162 non-null int64\n",
      "dtypes: datetime64[ns](1), float64(2), int64(4), object(41)\n",
      "memory usage: 22.9+ MB\n"
     ]
    }
   ],
   "source": [
    "crash_df[['latitude', 'longitude']] = crash_df[['latitude', 'longitude']].astype(float)\n",
    "crash_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add in the region for my crashes\n",
    "# Resource hog\n",
    "crash_df.columns\n",
    "\n",
    "\n",
    "def which_region(lat, long, region_df):\n",
    "    #print(lat, long)\n",
    "    row = region_df[(region_df['east'] >= long) &\n",
    "                    (region_df['west'] < long) &\n",
    "                    (region_df['north'] >= lat) &\n",
    "                    (region_df['south'] < lat)]['region_id'].max()\n",
    "    return row\n",
    "\n",
    "#df.iloc[:5]\n",
    "# takes some 5min\n",
    "crash_df['region_id'] = crash_df.apply(lambda x: which_region(x.latitude, x.longitude, region_df), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(crash_df)\n",
    "crash_df.columns\n",
    "\n",
    "crash_df['time'] = pd.to_datetime(crash_df.crash_date)\n",
    "crash_df['year'] = crash_df.time.dt.year\n",
    "crash_df['month'] = crash_df.time.dt.month\n",
    "crash_df['day'] = crash_df.time.dt.day\n",
    "crash_df['hour'] = crash_df.time.dt.hour\n",
    "crash_df['weekday'] = crash_df.time.dt.weekday"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create region_df TABLE and update signal_crashes TABLE\n",
    "\n",
    "With new info added to crash table, we overwrite previous table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('all_hours',), ('int_startend',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('intersection_cams',), ('all_crashes',), ('signal_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',)]\n",
      "\n",
      "[('all_hours',), ('int_startend',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('intersection_cams',), ('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('signal_crashes',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(region_df, 'region_data', c, conn)\n",
    "print()\n",
    "make_table(crash_df, 'signal_crashes', c, conn)  # also update my crash data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add region_id to intersection_cams\n",
    "While I'm here and I have the function ready.\n",
    "I would like to add region_id number to my red light camera (daily_violations TABLE)\n",
    "The region there will help me link the daily_violations and hourly_congestion TABLEs\n",
    "\n",
    "*** NOTE: Makes more sense to come back and put the region into the intersection_cameras table to speed this up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1 minutes\n",
    "#rlc['region_id'] = \n",
    "int_cams['region_id'] = int_cams.apply(lambda x: which_region(x.latitude, x.longitude, region_df), axis=1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('all_hours',), ('int_startend',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('signal_crashes',), ('intersection_cams',)]\n"
     ]
    }
   ],
   "source": [
    "## commit my change\n",
    "make_table(int_cams, 'intersection_cams', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b> Use this code to test any of your tables for proper data storage </b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('2141', 3), ('1612', 7), ('1413', 75), ('1814', 8), ('1014', 6)]\n",
      "389215\n"
     ]
    }
   ],
   "source": [
    "query = c.execute(\"SELECT camera_id, violations FROM daily_violations;\").fetchall()\n",
    "print(query[:5])\n",
    "print(len(query))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add intersections to signal_crashes \n",
    "Want to add this as a foreign key to help with queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('all_hours',),\n",
       " ('int_startend',),\n",
       " ('rlc_all_hours',),\n",
       " ('daily_covid',),\n",
       " ('holidays',),\n",
       " ('intersection_chars',),\n",
       " ('cam_locations',),\n",
       " ('cam_startend',),\n",
       " ('daily_violations',),\n",
       " ('all_crashes',),\n",
       " ('hourly_congestion',),\n",
       " ('hourly_weather',),\n",
       " ('region_data',),\n",
       " ('signal_crashes',),\n",
       " ('intersection_cams',)]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_fetch_tables(c, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read data back in to prevent having to rerun code.\n",
    "df = pd.read_sql_query(\"SELECT * FROM signal_crashes\", conn)\n",
    "camloc_df = pd.read_sql_query('SELECT * FROM cam_locations', conn)\n",
    "ints_df = pd.read_sql_query('SELECT * FROM intersection_cams', conn)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "#ints_df.astype({'longitude':float})\n",
    "pd.options.display.max_rows = 200\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add intersection data to crashes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DAMEN AND FULLERTON\n",
      "CENTRAL AND LAKE\n",
      "VAN BUREN AND WESTERN\n",
      "PULASKI AND DIVISION\n",
      "31ST ST AND MARTIN LUTHER KING DRIVE\n",
      "HALSTED AND 103RD\n",
      "HALSTED AND 95TH\n",
      "CALIFORNIA AND DEVON\n",
      "PULASKI AND 63RD\n",
      "AUSTIN AND ADDISON\n"
     ]
    }
   ],
   "source": [
    "# Let's simplify it and make it a box instead.\n",
    "box_side = 100  # effectively makes it check for crash being within 25m of interscection\n",
    "box_lat = box_side / 111070 / 2 # 111070 is meters in deg lat in Chicago\n",
    "box_long = box_side / 83000 / 2 # 83000 is meters in deg long in Chicago\n",
    "\n",
    "def box_check(lat, long, int_df):\n",
    "    n = lat + box_lat\n",
    "    s = lat - box_lat\n",
    "    e = long + box_long\n",
    "    w = long - box_long\n",
    "    # print('n', n, 's', s, 'e', e, 'w', w, 'lat:', lat, 'long:', long)\n",
    "    answer = int_df[  (int_df['lat'] > s) &\n",
    "                      (int_df['lat'] < n) &\n",
    "                      (int_df['long'] > w) &\n",
    "                      (int_df['long'] < e)\n",
    "                      \n",
    "                     ]\n",
    "    if answer.empty: return None\n",
    "    return answer['intersection'].values[0]\n",
    "\n",
    "    \n",
    "# THIS SEEMS TO WORK AT SPEED AND ELIMINATES MEMORY PROBLEM\n",
    "# this code is just to test out a chunk of data\n",
    "for i in range(5000, 5100): \n",
    "    lat = float(df.iloc[i]['latitude'])\n",
    "    long = float(df.iloc[i]['longitude'])\n",
    "    n = lat + box_lat\n",
    "    s = lat - box_lat\n",
    "    e = long + box_long\n",
    "    w = long - box_long\n",
    "    answer = int_df[  (int_df['lat'] > s) &\n",
    "                      (int_df['lat'] < n) &\n",
    "                      (int_df['long'] > w) &\n",
    "                      (int_df['long'] < e)]['intersection'].values\n",
    "    if len(answer): print(answer[0])\n",
    "    \n",
    "# 99th Halsted: 41.714230\t-87.643043\n",
    "# MOMENT OF TRUTH\n",
    "df['intersection'] = df.apply(lambda x: box_check(float(x.latitude), float(x.longitude), int_df), axis=1)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overwrite signal_crashes TABLE\n",
    "Now it is updated with intersections"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('all_hours',), ('int_startend',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('intersection_cams',), ('signal_crashes',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(df, 'signal_crashes', c, conn)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9) Create all_hours TABLE\n",
    "Make a table that just has every date and hour for every intersection.  Will help out my queries so I can use LEFT JOINS only.  Will use the weather data (hourly) to build my table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "#int_chars = pd.read_sql_query(\"SELECT * FROM intersection_chars\", conn)\n",
    "v_df = pd.read_sql_query(\"SELECT * FROM daily_violations\", conn)\n",
    "wx_df = pd.read_sql_query(\"SELECT * FROM hourly_weather\", conn)\n",
    "rlc_df = pd.read_sql_query(\"SELECT * FROM daily_violations\", conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_dates = wx_df.groupby(['year', 'month', 'day', 'hour']).max().reset_index()[['year', 'month', 'day', 'hour']]\n",
    "grouped_dates = grouped_dates[grouped_dates['year']>2015]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 44952 entries, 8760 to 53711\n",
      "Data columns (total 4 columns):\n",
      "year     44952 non-null int64\n",
      "month    44952 non-null int64\n",
      "day      44952 non-null int64\n",
      "hour     44952 non-null int64\n",
      "dtypes: int64(4)\n",
      "memory usage: 1.7 MB\n"
     ]
    }
   ],
   "source": [
    "# didn't work \n",
    "grouped_dates.head()\n",
    "grouped_dates.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "big_df = grouped_dates.copy()\n",
    "\n",
    "big_df['datetime'] = big_df.apply(lambda x: datetime(x.year, x.month, x.day, x.hour), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_ints = rlc_df.intersection.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Should only have to do this once\n",
    "#for i in range(len(wx_df)):\n",
    "big_df['intersection'] = my_ints[0]\n",
    "\n",
    "\n",
    "for i in range(1, len(my_ints)):\n",
    "    df = grouped_dates.copy()\n",
    "    df['intersection'] = my_ints[i]\n",
    "    big_df = pd.concat([big_df, df])\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "big_df.intersection.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "big_df.intersection.unique()\n",
    "big_df.dropna(subset=['intersection'], inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 7057464 entries, 8760 to 53711\n",
      "Data columns (total 6 columns):\n",
      "datetime        datetime64[ns]\n",
      "day             int64\n",
      "hour            int64\n",
      "intersection    object\n",
      "month           int64\n",
      "year            int64\n",
      "dtypes: datetime64[ns](1), int64(4), object(1)\n",
      "memory usage: 376.9+ MB\n"
     ]
    }
   ],
   "source": [
    "big_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "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>datetime</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "      <th>intersection</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>8760</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4700 WESTERN</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8761</td>\n",
       "      <td>2016-01-01 01:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4700 WESTERN</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8762</td>\n",
       "      <td>2016-01-01 02:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4700 WESTERN</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8763</td>\n",
       "      <td>2016-01-01 03:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4700 WESTERN</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8764</td>\n",
       "      <td>2016-01-01 04:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4700 WESTERN</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                datetime  day  hour  intersection  month  year\n",
       "8760 2016-01-01 00:00:00    1     0  4700 WESTERN      1  2016\n",
       "8761 2016-01-01 01:00:00    1     1  4700 WESTERN      1  2016\n",
       "8762 2016-01-01 02:00:00    1     2  4700 WESTERN      1  2016\n",
       "8763 2016-01-01 03:00:00    1     3  4700 WESTERN      1  2016\n",
       "8764 2016-01-01 04:00:00    1     4  4700 WESTERN      1  2016"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "big_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create all_hours TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('int_startend',), ('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('intersection_cams',), ('signal_crashes',), ('all_hours',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(big_df, 'all_hours', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10) Build int_startend TABLE \n",
    "\n",
    "Create a table which serves to provide start and end dates for camera functioning at every intersection.\n",
    "\n",
    "We will just look at the earliest and latest entries from the violations.\n",
    "When cameras are off, they just disappear from the database.\n",
    "\n",
    "All cameras have gaps (maintenance and malfunctions). I do not differentiate these in this project."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preprocess int_startend\n",
    "Add intersection start-end dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "v_df = pd.read_sql_query(\"SELECT * FROM daily_violations\", conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "v_df['start'] = v_df['violation_date']\n",
    "v_df['end'] = v_df['violation_date']\n",
    "\n",
    "start_end = v_df.groupby('intersection').agg({'start':'min', 'end':'max'}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "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>intersection</th>\n",
       "      <th>start</th>\n",
       "      <th>end</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>111TH AND HALSTED</td>\n",
       "      <td>2017-01-02 00:00:00</td>\n",
       "      <td>2021-02-17 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>115TH AND HALSTED</td>\n",
       "      <td>2017-01-02 00:00:00</td>\n",
       "      <td>2017-10-26 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>119TH AND HALSTED</td>\n",
       "      <td>2017-01-02 00:00:00</td>\n",
       "      <td>2021-02-17 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>31ST ST AND MARTIN LUTHER KING DRIVE</td>\n",
       "      <td>2017-01-02 00:00:00</td>\n",
       "      <td>2021-02-17 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>35TH AND WESTERN</td>\n",
       "      <td>2017-01-02 00:00:00</td>\n",
       "      <td>2021-02-17 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           intersection                start  \\\n",
       "0                     111TH AND HALSTED  2017-01-02 00:00:00   \n",
       "1                     115TH AND HALSTED  2017-01-02 00:00:00   \n",
       "2                     119TH AND HALSTED  2017-01-02 00:00:00   \n",
       "3  31ST ST AND MARTIN LUTHER KING DRIVE  2017-01-02 00:00:00   \n",
       "4                      35TH AND WESTERN  2017-01-02 00:00:00   \n",
       "\n",
       "                   end  \n",
       "0  2021-02-17 00:00:00  \n",
       "1  2017-10-26 00:00:00  \n",
       "2  2021-02-17 00:00:00  \n",
       "3  2021-02-17 00:00:00  \n",
       "4  2021-02-17 00:00:00  "
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_end.start.sort_values().tail(50)\n",
    "start_end.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create start_end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('rlc_all_hours',), ('daily_covid',), ('holidays',), ('intersection_chars',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('intersection_cams',), ('signal_crashes',), ('all_hours',), ('int_startend',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(start_end, 'int_startend', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add speed to int_char TABLE\n",
    "Use the average (or mode speed) from crashes at intersection to add to my intersection charateristics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "speed_df = pd.read_sql_query(\"SELECT * FROM signal_crashes\", conn)\n",
    "int_char_df = pd.read_sql_query(\"SELECT * FROM intersection_chars\", conn)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "speed_df.posted_speed_limit = speed_df.posted_speed_limit.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "speed_df = speed_df.groupby('intersection').agg({'posted_speed_limit':max}).reset_index()  # mode is from scipy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "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>intersection</th>\n",
       "      <th>posted_speed_limit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>111TH AND HALSTED</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>115TH AND HALSTED</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>119TH AND HALSTED</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>31ST AND CALIFORNIA</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>31ST ST AND MARTIN LUTHER KING DRIVE</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           intersection  posted_speed_limit\n",
       "0                     111TH AND HALSTED                  35\n",
       "1                     115TH AND HALSTED                  35\n",
       "2                     119TH AND HALSTED                  35\n",
       "3                   31ST AND CALIFORNIA                  35\n",
       "4  31ST ST AND MARTIN LUTHER KING DRIVE                  35"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "speed_df.posted_speed_limit.unique()\n",
    "speed_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# df is defined globally\n",
    "def speed_lookup(intersection):\n",
    "    # needed to put a try in there because one intersection had no crashes over time period\n",
    "    try:\n",
    "        speed = speed_df[speed_df['intersection']==intersection]['posted_speed_limit'].values[0]\n",
    "    except:\n",
    "        speed=30\n",
    "    return speed\n",
    "    \n",
    "int_char_df['speed'] = int_char_df['intersection'].apply(speed_lookup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "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>protected_turn</th>\n",
       "      <th>total_lanes</th>\n",
       "      <th>medians</th>\n",
       "      <th>exit</th>\n",
       "      <th>split</th>\n",
       "      <th>way</th>\n",
       "      <th>underpass</th>\n",
       "      <th>no_left</th>\n",
       "      <th>angled</th>\n",
       "      <th>triangle</th>\n",
       "      <th>one_way</th>\n",
       "      <th>turn_lanes</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>rlc</th>\n",
       "      <th>intersection</th>\n",
       "      <th>daily_traffic</th>\n",
       "      <th>speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>41.692362</td>\n",
       "      <td>-87.642423</td>\n",
       "      <td>1</td>\n",
       "      <td>111TH AND HALSTED</td>\n",
       "      <td>43100</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>41.685089</td>\n",
       "      <td>-87.642094</td>\n",
       "      <td>1</td>\n",
       "      <td>115TH AND HALSTED</td>\n",
       "      <td>42500</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>41.677774</td>\n",
       "      <td>-87.641930</td>\n",
       "      <td>1</td>\n",
       "      <td>119TH AND HALSTED</td>\n",
       "      <td>41800</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>41.837424</td>\n",
       "      <td>-87.695022</td>\n",
       "      <td>1</td>\n",
       "      <td>31ST AND CALIFORNIA</td>\n",
       "      <td>41100</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>41.838441</td>\n",
       "      <td>-87.617338</td>\n",
       "      <td>1</td>\n",
       "      <td>31ST ST AND MARTIN LUTHER KING DRIVE</td>\n",
       "      <td>36500</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   protected_turn  total_lanes  medians  exit  split  way  underpass  no_left  \\\n",
       "0               2            6        2     0      0    4          0        0   \n",
       "1               4            6        2     0      0    4          0        0   \n",
       "2               4            6        2     0      0    4          0        0   \n",
       "3               2            6        0     0      0    4          0        0   \n",
       "4               2           10        2     0      1    4          0        2   \n",
       "\n",
       "   angled  triangle  one_way  turn_lanes        lat       long  rlc  \\\n",
       "0       1         0        0           2  41.692362 -87.642423    1   \n",
       "1       0         0        0           4  41.685089 -87.642094    1   \n",
       "2       0         0        0           4  41.677774 -87.641930    1   \n",
       "3       0         0        0           4  41.837424 -87.695022    1   \n",
       "4       0         0        0           0  41.838441 -87.617338    1   \n",
       "\n",
       "                           intersection  daily_traffic  speed  \n",
       "0                     111TH AND HALSTED          43100     35  \n",
       "1                     115TH AND HALSTED          42500     35  \n",
       "2                     119TH AND HALSTED          41800     35  \n",
       "3                   31ST AND CALIFORNIA          41100     35  \n",
       "4  31ST ST AND MARTIN LUTHER KING DRIVE          36500     35  "
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int_char_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rewrite int_char"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('rlc_all_hours',), ('daily_covid',), ('holidays',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('intersection_cams',), ('signal_crashes',), ('all_hours',), ('int_startend',), ('intersection_chars',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(int_char_df, 'intersection_chars', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add Red Light On/Off to crash data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "crash_df2 = pd.read_sql_query(\"SELECT * FROM signal_crashes\", conn)\n",
    "se_df = pd.read_sql_query(\"SELECT * FROM int_startend\", conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "crash_df2 = crash_df.merge(se_df, on='intersection', how='left')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['crash_record_id', 'rd_no', 'crash_date', 'posted_speed_limit',\n",
       "       'traffic_control_device', 'device_condition', 'weather_condition',\n",
       "       'lighting_condition', 'first_crash_type', 'trafficway_type',\n",
       "       'alignment', 'roadway_surface_cond', 'road_defect', 'report_type',\n",
       "       'crash_type', 'damage', 'prim_contributory_cause',\n",
       "       'sec_contributory_cause', 'street_no', 'street_direction',\n",
       "       'street_name', 'beat_of_occurrence', 'num_units', 'most_severe_injury',\n",
       "       'injuries_total', 'injuries_fatal', 'injuries_incapacitating',\n",
       "       'injuries_non_incapacitating', 'injuries_reported_not_evident',\n",
       "       'injuries_no_indication', 'injuries_unknown', 'crash_hour',\n",
       "       'crash_day_of_week', 'crash_month', 'latitude', 'longitude', 'lane_cnt',\n",
       "       'intersection_related_i', 'hit_and_run_i', 'crash_date_est_i',\n",
       "       'work_zone_i', 'work_zone_type', 'workers_present_i', 'intersection',\n",
       "       'year', 'month', 'day', 'hour', 'region_id', 'time', 'weekday', 'start',\n",
       "       'end'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crash_df2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "crash_df2['start'] = pd.to_datetime(crash_df2['start'])\n",
    "crash_df2['end'] = pd.to_datetime(crash_df2['end'])\n",
    "crash_df2['crash_date'] = pd.to_datetime(crash_df2['crash_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "# need to determine if crash occurred in or outside of cam on dates\n",
    "\n",
    "def rlc_state(start, end, my_date):\n",
    "\n",
    "    if (end - my_date).days >= 0 and (my_date - start).days >= 0:\n",
    "        return 1\n",
    "    elif (my_date - end).days > 0:\n",
    "        return 0\n",
    "    elif (start - my_date).days > 0:\n",
    "        return 0\n",
    "    else:\n",
    "        return None\n",
    "\n",
    "crash_df2['rlc_state'] = crash_df2.apply(lambda x: rlc_state(x.start, x.end, x.crash_date), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total crashes rlc on: 6683.0\n",
      "Total crashes rlc off: 1137\n"
     ]
    }
   ],
   "source": [
    "print('Total crashes rlc on: {}'.format(crash_df2['rlc_state'].sum()))\n",
    "print('Total crashes rlc off: {}'.format(crash_df2[crash_df2['rlc_state']==0]['crash_date'].count()))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('rlc_all_hours',), ('daily_covid',), ('holidays',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('intersection_cams',), ('all_hours',), ('int_startend',), ('intersection_chars',), ('signal_crashes',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(crash_df2, 'signal_crashes', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add camera state to all_hours\n",
    "Needed to do this to make my queries easier since I don't have an outer \n",
    "join in SQLite3.  This takes a significant amount of time.  Hours "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "all_hours = pd.read_sql_query(\"SELECT * FROM all_hours\", conn)\n"
   ]
  },
  {
   "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>datetime</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "      <th>intersection</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4700 WESTERN</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2016-01-01 01:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4700 WESTERN</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2016-01-01 02:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4700 WESTERN</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2016-01-01 03:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4700 WESTERN</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2016-01-01 04:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4700 WESTERN</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              datetime  day  hour  intersection  month  year\n",
       "0  2016-01-01 00:00:00    1     0  4700 WESTERN      1  2016\n",
       "1  2016-01-01 01:00:00    1     1  4700 WESTERN      1  2016\n",
       "2  2016-01-01 02:00:00    1     2  4700 WESTERN      1  2016\n",
       "3  2016-01-01 03:00:00    1     3  4700 WESTERN      1  2016\n",
       "4  2016-01-01 04:00:00    1     4  4700 WESTERN      1  2016"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#all_hours['datetime'] = pd.to_datetime(all_hours)\n",
    "all_hours.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_hours.datetime = all_hours.apply(lambda x: datetime(int(x.year), x.month, x.day, x.hour), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>datetime</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "      <th>intersection</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>7057459</td>\n",
       "      <td>2021-02-15 19:00:00</td>\n",
       "      <td>15</td>\n",
       "      <td>19</td>\n",
       "      <td>PULASKI AND PETERSON</td>\n",
       "      <td>2</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7057460</td>\n",
       "      <td>2021-02-15 20:00:00</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>PULASKI AND PETERSON</td>\n",
       "      <td>2</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7057461</td>\n",
       "      <td>2021-02-15 21:00:00</td>\n",
       "      <td>15</td>\n",
       "      <td>21</td>\n",
       "      <td>PULASKI AND PETERSON</td>\n",
       "      <td>2</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7057462</td>\n",
       "      <td>2021-02-15 22:00:00</td>\n",
       "      <td>15</td>\n",
       "      <td>22</td>\n",
       "      <td>PULASKI AND PETERSON</td>\n",
       "      <td>2</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7057463</td>\n",
       "      <td>2021-02-15 23:00:00</td>\n",
       "      <td>15</td>\n",
       "      <td>23</td>\n",
       "      <td>PULASKI AND PETERSON</td>\n",
       "      <td>2</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   datetime  day  hour          intersection  month  year\n",
       "7057459 2021-02-15 19:00:00   15    19  PULASKI AND PETERSON      2  2021\n",
       "7057460 2021-02-15 20:00:00   15    20  PULASKI AND PETERSON      2  2021\n",
       "7057461 2021-02-15 21:00:00   15    21  PULASKI AND PETERSON      2  2021\n",
       "7057462 2021-02-15 22:00:00   15    22  PULASKI AND PETERSON      2  2021\n",
       "7057463 2021-02-15 23:00:00   15    23  PULASKI AND PETERSON      2  2021"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_hours.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "se_df = pd.read_sql_query(\"SELECT * FROM int_startend\", conn)\n",
    "se_df['start'] = pd.to_datetime(se_df['start'])\n",
    "se_df['end'] = pd.to_datetime(se_df['end'])\n",
    "\n",
    "se_df.intersection\n",
    "\n",
    "se_dict = {x:[se_df[se_df['intersection']==x]['start'].values[0],  \n",
    "              se_df[se_df['intersection']==x]['end'].values[0]] for x in se_df.intersection}\n"
   ]
  },
  {
   "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>intersection</th>\n",
       "      <th>start</th>\n",
       "      <th>end</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>115TH AND HALSTED</td>\n",
       "      <td>2017-01-02</td>\n",
       "      <td>2017-10-26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        intersection      start        end\n",
       "1  115TH AND HALSTED 2017-01-02 2017-10-26"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "se_df[se_df.intersection =='115TH AND HALSTED']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   datetime  day  hour       intersection  month  year  \\\n",
      "5663952 2016-01-01 00:00:00    1     0  111TH AND HALSTED      1  2016   \n",
      "5663953 2016-01-01 01:00:00    1     1  111TH AND HALSTED      1  2016   \n",
      "5663954 2016-01-01 02:00:00    1     2  111TH AND HALSTED      1  2016   \n",
      "5663955 2016-01-01 03:00:00    1     3  111TH AND HALSTED      1  2016   \n",
      "5663956 2016-01-01 04:00:00    1     4  111TH AND HALSTED      1  2016   \n",
      "\n",
      "         rlc_state  \n",
      "5663952          0  \n",
      "5663953          0  \n",
      "5663954          0  \n",
      "5663955          0  \n",
      "5663956          0  \n",
      "111TH AND HALSTED complete\n",
      "115TH AND HALSTED,119TH AND HALSTED,31ST ST AND MARTIN LUTHER KING DRIVE,35TH AND WESTERN,4700 WESTERN,55TH AND KEDZIE,55TH AND WESTERN,55TH and PULASKI,63RD AND STATE,71ST AND ASHLAND,75TH AND STATE,79TH AND HALSTED,79TH AND KEDZIE,87TH AND VINCENNES,95TH AND STONEY ISLAND,99TH AND HALSTED,ADDISON AND HARLEM,ARCHER AND CICERO,ASHLAND AND 87TH,ASHLAND AND 95TH,ASHLAND AND DIVISION,ASHLAND AND FULLERTON,ASHLAND AND IRVING PARK,ASHLAND AND LAWRENCE,ASHLAND AND MADISON,AUSTIN AND ADDISON,AUSTIN AND IRVING PARK,BELMONT AND KEDZIE,BROADWAY/SHERIDAN AND DEVON,CALIFORNIA AND DEVON,CALIFORNIA AND DIVERSEY,CALIFORNIA AND PETERSON,CANAL AND ROOSEVELT,CENTRAL AND ADDISON,CENTRAL AND BELMONT,CENTRAL AND CHICAGO,CENTRAL AND DIVERSEY,CENTRAL AND FULLERTON,CENTRAL AND IRVING PARK,CENTRAL AND LAKE,CERMAK AND PULASKI,CHICAGO AND CLARK,CICERO AND 47TH,CICERO AND ADDISON,CICERO AND ARMITAGE,CICERO AND CHICAGO,CICERO AND DIVERSEY,CICERO AND FULLERTON,CICERO AND HARRISON,CICERO AND I55,CICERO AND NORTH,CICERO AND PETERSON,CICERO AND WASHINGTON,CLARK AND FULLERTON,CLARK AND IRVING PARK,COLUMBUS AND ILLINOIS,CORTLAND AND ASHLAND,COTTAGE GROVE AND 71ST,DAMEN AND 63RD,DAMEN AND DIVERSEY,DAMEN AND ELSTON,DAMEN AND FULLERTON,DIVERSEY AND AUSTIN,DIVERSEY AND WESTERN,DIVISION AND DAMEN,ELSTON AND ADDISON,ELSTON AND IRVING PARK,ELSTON AND LAWRENCE,FOSTER AND BROADWAY,FOSTER AND NAGLE,FOSTER AND NORTHWEST HIGHWAY,FULLERTON AND NARRAGANSETT,GRAND AND OAK PARK,HALSTED AND 103RD,HALSTED AND 95TH,HALSTED AND DIVISION,HALSTED AND FULLERTON,HALSTED AND MADISON,HALSTED AND NORTH,HAMLIN AND LAKE,HAMLIN AND MADISON,HARLEM AND BELMONT,HOLLYWOOD AND SHERIDAN,HOMAN/KIMBALL AND NORTH,IRVING PARK AND CALIFORNIA,IRVING PARK AND KEDZIE,IRVING PARK AND KILPATRICK,IRVING PARK AND LARAMIE,IRVING PARK AND NARRAGANSETT,JEFFERY AND 95TH,KEDZIE AND 26TH,KEDZIE AND 31ST,KEDZIE AND 47TH,KEDZIE AND 63RD,KEDZIE AND 71ST,KEDZIE AND ARMITAGE,KIMBALL AND DIVERSEY,KOSTNER AND NORTH,LAFAYETTE AND 87TH,LAKE AND UPPER WACKER,LAKE SHORE DR AND BELMONT,LARAMIE AND FULLERTON,LARAMIE AND MADISON,LASALLE AND KINZIE,LAWRENCE AND CICERO,LAWRENCE AND WESTERN,MADISON AND WESTERN,MICHIGAN AND JACKSON,MICHIGAN AND ONTARIO,MILWAUKEE AND CENTRAL,MILWAUKEE AND DEVON,MILWAUKEE AND MONTROSE,MONTROSE AND WESTERN,OGDEN AND KOSTNER,PERSHING AND WESTERN,PETERSON AND WESTERN,PULASKI AND 63RD,PULASKI AND 79TH,PULASKI AND ARCHER,PULASKI AND ARMITAGE,PULASKI AND BELMONT,PULASKI AND CHICAGO,PULASKI AND DIVERSEY,PULASKI AND DIVISION,PULASKI AND FOSTER,PULASKI AND FULLERTON,PULASKI AND IRVING PARK,PULASKI AND LAWRENCE,PULASKI AND NORTH,PULASKI AND PETERSON,RIDGE AND CLARK,ROOSEVELT AND HALSTED,ROOSEVELT AND KOSTNER,ROOSEVELT AND PULASKI,SACRAMENTO AND CHICAGO,SACRAMENTO AND LAKE,SHERIDAN AND FOSTER,STATE AND 79TH,STONEY ISLAND AND 76TH,STONEY ISLAND AND 79TH,STONY ISLAND/CORNELL AND 67TH,TOUHY AND OSCEOLA,VAN BUREN AND WESTERN,WENTWORTH AND GARFIELD,WESTERN AND 63RD,WESTERN AND 71ST,WESTERN AND 79TH,WESTERN AND ADDISON,WESTERN AND CERMAK,WESTERN AND CHICAGO,WESTERN AND DEVON,WESTERN AND FOSTER,WESTERN AND FULLERTON,WESTERN AND MARQUETTE,WESTERN AND NORTH,WESTERN AND TOUHY,END\n"
     ]
    }
   ],
   "source": [
    "# Let's make it faster\n",
    "def get_camstate2(date, start, end):\n",
    "    if date >= start and date <= end:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "\n",
    "    \n",
    "intersections = list(se_dict.keys())\n",
    "intersection = intersections[0]\n",
    "new_df = all_hours[all_hours['intersection']==intersection]\n",
    "new_df['rlc_state'] = new_df['datetime'].apply(lambda x: get_camstate2(x, \n",
    "                                                  se_dict[intersection][0],\n",
    "                                                  se_dict[intersection][1]\n",
    "                                                    ))\n",
    "print(new_df.head())\n",
    "print(intersection, 'complete')\n",
    "\n",
    "for intersection in intersections[1:]:\n",
    "    concat_me = all_hours[all_hours['intersection']==intersection]\n",
    "    concat_me['rlc_state'] = concat_me['datetime'].apply(lambda x: get_camstate2(x, \n",
    "                                                  se_dict[intersection][0],\n",
    "                                                  se_dict[intersection][1]\n",
    "                                                    ))\n",
    "    print(intersection, end=',')\n",
    "    new_df = pd.concat([new_df, concat_me])\n",
    "\n",
    "print(\"END\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above code was extremely slow until I broke up the df into parts in a loop. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>datetime</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "      <th>intersection</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>rlc_state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>5663952</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>111TH AND HALSTED</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5663953</td>\n",
       "      <td>2016-01-01 01:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>111TH AND HALSTED</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5663954</td>\n",
       "      <td>2016-01-01 02:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>111TH AND HALSTED</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5663955</td>\n",
       "      <td>2016-01-01 03:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>111TH AND HALSTED</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5663956</td>\n",
       "      <td>2016-01-01 04:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>111TH AND HALSTED</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   datetime  day  hour       intersection  month  year  \\\n",
       "5663952 2016-01-01 00:00:00    1     0  111TH AND HALSTED      1  2016   \n",
       "5663953 2016-01-01 01:00:00    1     1  111TH AND HALSTED      1  2016   \n",
       "5663954 2016-01-01 02:00:00    1     2  111TH AND HALSTED      1  2016   \n",
       "5663955 2016-01-01 03:00:00    1     3  111TH AND HALSTED      1  2016   \n",
       "5663956 2016-01-01 04:00:00    1     4  111TH AND HALSTED      1  2016   \n",
       "\n",
       "         rlc_state  \n",
       "5663952          0  \n",
       "5663953          0  \n",
       "5663954          0  \n",
       "5663955          0  \n",
       "5663956          0  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime        0\n",
       "day             0\n",
       "hour            0\n",
       "intersection    0\n",
       "month           0\n",
       "year            0\n",
       "rlc_state       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5362979"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.rlc_state.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "make_table(new_df, 'rlc_all_hours', c, conn)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding COVID TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "daily_covid= client.get(\"naz8-j4nc\", \n",
    "                     limit=20000,\n",
    "                    )\n",
    "\n",
    "daily_covid = pd.DataFrame.from_records(daily_covid) # Convert to pandas DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('holidays',), ('cam_locations',), ('cam_startend',), ('daily_violations',), ('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('intersection_cams',), ('all_hours',), ('int_startend',), ('intersection_chars',), ('signal_crashes',), ('rlc_all_hours',), ('daily_covid',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(daily_covid, 'daily_covid', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add Holiday TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "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>index</th>\n",
       "      <th>date</th>\n",
       "      <th>holiday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2012-01-02</td>\n",
       "      <td>New Year Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2012-01-16</td>\n",
       "      <td>Martin Luther King Jr. Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2012-02-20</td>\n",
       "      <td>Presidents Day (Washingtons Birthday)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2012-05-28</td>\n",
       "      <td>Memorial Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2012-07-04</td>\n",
       "      <td>Independence Day</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index        date                                holiday\n",
       "0      1  2012-01-02                           New Year Day\n",
       "1      2  2012-01-16             Martin Luther King Jr. Day\n",
       "2      3  2012-02-20  Presidents Day (Washingtons Birthday)\n",
       "3      4  2012-05-28                           Memorial Day\n",
       "4      5  2012-07-04                       Independence Day"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import weather data from csv\n",
    "holiday_df = pd.read_csv('data/holidays.csv')\n",
    "holiday_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "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>index</th>\n",
       "      <th>date</th>\n",
       "      <th>holiday</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2012-01-02</td>\n",
       "      <td>New Year Day</td>\n",
       "      <td>2012</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2012-01-16</td>\n",
       "      <td>Martin Luther King Jr. Day</td>\n",
       "      <td>2012</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2012-02-20</td>\n",
       "      <td>Presidents Day (Washingtons Birthday)</td>\n",
       "      <td>2012</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2012-05-28</td>\n",
       "      <td>Memorial Day</td>\n",
       "      <td>2012</td>\n",
       "      <td>5</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2012-07-04</td>\n",
       "      <td>Independence Day</td>\n",
       "      <td>2012</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index       date                                holiday  year  month  day\n",
       "0      1 2012-01-02                           New Year Day  2012      1    2\n",
       "1      2 2012-01-16             Martin Luther King Jr. Day  2012      1   16\n",
       "2      3 2012-02-20  Presidents Day (Washingtons Birthday)  2012      2   20\n",
       "3      4 2012-05-28                           Memorial Day  2012      5   28\n",
       "4      5 2012-07-04                       Independence Day  2012      7    4"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "holiday_df.date = pd.to_datetime(holiday_df.date)\n",
    "holiday_df['year'] = holiday_df.date.apply(lambda x: x.year)\n",
    "holiday_df['month'] = holiday_df.date.apply(lambda x: x.month)\n",
    "holiday_df['day'] = holiday_df.date.apply(lambda x: x.day)\n",
    "holiday_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('cam_locations',), ('cam_startend',), ('daily_violations',), ('all_crashes',), ('hourly_congestion',), ('hourly_weather',), ('region_data',), ('intersection_cams',), ('all_hours',), ('int_startend',), ('intersection_chars',), ('signal_crashes',), ('rlc_all_hours',), ('daily_covid',), ('holidays',)]\n"
     ]
    }
   ],
   "source": [
    "make_table(holiday_df, 'holidays', c, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Close my connection to the db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "learn-env",
   "language": "python",
   "name": "learn-env"
  },
  "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}