CapstoneTwo/DataWranglingFinal.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3063: DtypeWarning: Columns (7) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "filepath = '/Users/jacksonpeoples/Desktop/Springboard/CapstoneTwo/Data/Vision_Zero_Crash_Report_Data.csv'\n",
    "crash_data = pd.read_csv(filepath)\n",
    "\n",
    "weather_path = '/Users/jacksonpeoples/Desktop/Springboard/CapstoneTwo/Data/WeatherAustin.csv'\n",
    "weather_data = pd.read_csv(weather_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 161824 entries, 0 to 161823\n",
      "Data columns (total 51 columns):\n",
      " #   Column                              Non-Null Count   Dtype  \n",
      "---  ------                              --------------   -----  \n",
      " 0   crash_id                            161824 non-null  int64  \n",
      " 1   crash_fatal_fl                      161824 non-null  object \n",
      " 2   crash_date                          161824 non-null  object \n",
      " 3   crash_time                          161824 non-null  object \n",
      " 4   case_id                             159592 non-null  object \n",
      " 5   rpt_latitude                        5349 non-null    float64\n",
      " 6   rpt_longitude                       5349 non-null    float64\n",
      " 7   rpt_block_num                       140411 non-null  object \n",
      " 8   rpt_street_pfx                      90518 non-null   object \n",
      " 9   rpt_street_name                     161818 non-null  object \n",
      " 10  rpt_street_sfx                      118702 non-null  object \n",
      " 11  crash_speed_limit                   161819 non-null  float64\n",
      " 12  road_constr_zone_fl                 161819 non-null  object \n",
      " 13  latitude                            155992 non-null  float64\n",
      " 14  longitude                           155992 non-null  float64\n",
      " 15  street_name                         161819 non-null  object \n",
      " 16  street_nbr                          67555 non-null   float64\n",
      " 17  street_name_2                       70496 non-null   object \n",
      " 18  street_nbr_2                        0 non-null       float64\n",
      " 19  crash_sev_id                        161819 non-null  float64\n",
      " 20  sus_serious_injry_cnt               161824 non-null  int64  \n",
      " 21  nonincap_injry_cnt                  161821 non-null  float64\n",
      " 22  poss_injry_cnt                      161821 non-null  float64\n",
      " 23  non_injry_cnt                       161821 non-null  float64\n",
      " 24  unkn_injry_cnt                      161819 non-null  float64\n",
      " 25  tot_injry_cnt                       161820 non-null  float64\n",
      " 26  death_cnt                           161824 non-null  int64  \n",
      " 27  contrib_factr_p1_id                 30884 non-null   float64\n",
      " 28  contrib_factr_p2_id                 6298 non-null    float64\n",
      " 29  units_involved                      161817 non-null  object \n",
      " 30  atd_mode_category_metadata          161817 non-null  object \n",
      " 31  pedestrian_fl                       4389 non-null    object \n",
      " 32  motor_vehicle_fl                    160319 non-null  object \n",
      " 33  motorcycle_fl                       4509 non-null    object \n",
      " 34  bicycle_fl                          3125 non-null    object \n",
      " 35  other_fl                            5130 non-null    object \n",
      " 36  point                               155992 non-null  object \n",
      " 37  apd_confirmed_fatality              161824 non-null  object \n",
      " 38  apd_confirmed_death_count           161822 non-null  float64\n",
      " 39  motor_vehicle_death_count           161824 non-null  int64  \n",
      " 40  motor_vehicle_serious_injury_count  161824 non-null  int64  \n",
      " 41  bicycle_death_count                 161824 non-null  int64  \n",
      " 42  bicycle_serious_injury_count        161824 non-null  int64  \n",
      " 43  pedestrian_death_count              161824 non-null  int64  \n",
      " 44  pedestrian_serious_injury_count     161824 non-null  int64  \n",
      " 45  motorcycle_death_count              161824 non-null  int64  \n",
      " 46  motorcycle_serious_injury_count     161824 non-null  int64  \n",
      " 47  other_death_count                   161824 non-null  int64  \n",
      " 48  other_serious_injury_count          161824 non-null  int64  \n",
      " 49  onsys_fl                            161819 non-null  object \n",
      " 50  private_dr_fl                       161819 non-null  object \n",
      "dtypes: float64(16), int64(13), object(22)\n",
      "memory usage: 63.0+ MB\n"
     ]
    }
   ],
   "source": [
    "#First get some basic info on each dataset.\n",
    "crash_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>crash_id</th>\n",
       "      <th>crash_fatal_fl</th>\n",
       "      <th>crash_date</th>\n",
       "      <th>crash_time</th>\n",
       "      <th>case_id</th>\n",
       "      <th>rpt_latitude</th>\n",
       "      <th>rpt_longitude</th>\n",
       "      <th>rpt_block_num</th>\n",
       "      <th>rpt_street_pfx</th>\n",
       "      <th>rpt_street_name</th>\n",
       "      <th>...</th>\n",
       "      <th>bicycle_death_count</th>\n",
       "      <th>bicycle_serious_injury_count</th>\n",
       "      <th>pedestrian_death_count</th>\n",
       "      <th>pedestrian_serious_injury_count</th>\n",
       "      <th>motorcycle_death_count</th>\n",
       "      <th>motorcycle_serious_injury_count</th>\n",
       "      <th>other_death_count</th>\n",
       "      <th>other_serious_injury_count</th>\n",
       "      <th>onsys_fl</th>\n",
       "      <th>private_dr_fl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16776868</td>\n",
       "      <td>N</td>\n",
       "      <td>12/04/2018 03:36:00 PM</td>\n",
       "      <td>15:36:00</td>\n",
       "      <td>183381097</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1900</td>\n",
       "      <td>N</td>\n",
       "      <td>IH 35 SB</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16786471</td>\n",
       "      <td>N</td>\n",
       "      <td>12/07/2018 01:38:00 PM</td>\n",
       "      <td>13:38:00</td>\n",
       "      <td>183410837</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12400</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N IH 35 SVRD SB</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16757244</td>\n",
       "      <td>N</td>\n",
       "      <td>11/18/2018 04:10:00 AM</td>\n",
       "      <td>04:10:00</td>\n",
       "      <td>183220392</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4500</td>\n",
       "      <td>N</td>\n",
       "      <td>N IH 35 NB</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16776852</td>\n",
       "      <td>N</td>\n",
       "      <td>12/03/2018 11:19:00 AM</td>\n",
       "      <td>11:19:00</td>\n",
       "      <td>183370654</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NUECES ST</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16765750</td>\n",
       "      <td>N</td>\n",
       "      <td>11/29/2018 09:07:00 AM</td>\n",
       "      <td>09:07:00</td>\n",
       "      <td>183330494</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4100</td>\n",
       "      <td>N</td>\n",
       "      <td>LAMAR</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>16758169</td>\n",
       "      <td>N</td>\n",
       "      <td>11/23/2018 07:34:00 AM</td>\n",
       "      <td>07:34:00</td>\n",
       "      <td>183270358</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000</td>\n",
       "      <td>W</td>\n",
       "      <td>OLTORF</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16780753</td>\n",
       "      <td>N</td>\n",
       "      <td>12/06/2018 06:25:00 PM</td>\n",
       "      <td>18:25:00</td>\n",
       "      <td>183401402</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3000</td>\n",
       "      <td>S</td>\n",
       "      <td>S IH 35 NB</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>16757603</td>\n",
       "      <td>N</td>\n",
       "      <td>11/21/2018 05:39:00 PM</td>\n",
       "      <td>17:39:00</td>\n",
       "      <td>183251200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11700</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JOLLYVILLE RD</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   crash_id crash_fatal_fl              crash_date crash_time    case_id  \\\n",
       "0  16776868              N  12/04/2018 03:36:00 PM   15:36:00  183381097   \n",
       "1  16786471              N  12/07/2018 01:38:00 PM   13:38:00  183410837   \n",
       "2  16757244              N  11/18/2018 04:10:00 AM   04:10:00  183220392   \n",
       "3  16776852              N  12/03/2018 11:19:00 AM   11:19:00  183370654   \n",
       "4  16765750              N  11/29/2018 09:07:00 AM   09:07:00  183330494   \n",
       "5  16758169              N  11/23/2018 07:34:00 AM   07:34:00  183270358   \n",
       "6  16780753              N  12/06/2018 06:25:00 PM   18:25:00  183401402   \n",
       "7  16757603              N  11/21/2018 05:39:00 PM   17:39:00  183251200   \n",
       "\n",
       "   rpt_latitude  rpt_longitude rpt_block_num rpt_street_pfx  rpt_street_name  \\\n",
       "0           NaN            NaN          1900              N         IH 35 SB   \n",
       "1           NaN            NaN         12400            NaN  N IH 35 SVRD SB   \n",
       "2           NaN            NaN          4500              N       N IH 35 NB   \n",
       "3           NaN            NaN           200            NaN        NUECES ST   \n",
       "4           NaN            NaN          4100              N            LAMAR   \n",
       "5           NaN            NaN          1000              W           OLTORF   \n",
       "6           NaN            NaN          3000              S       S IH 35 NB   \n",
       "7           NaN            NaN         11700            NaN    JOLLYVILLE RD   \n",
       "\n",
       "   ... bicycle_death_count  bicycle_serious_injury_count  \\\n",
       "0  ...                   0                             0   \n",
       "1  ...                   0                             0   \n",
       "2  ...                   0                             0   \n",
       "3  ...                   0                             0   \n",
       "4  ...                   0                             0   \n",
       "5  ...                   0                             0   \n",
       "6  ...                   0                             0   \n",
       "7  ...                   0                             0   \n",
       "\n",
       "  pedestrian_death_count  pedestrian_serious_injury_count  \\\n",
       "0                      0                                0   \n",
       "1                      0                                0   \n",
       "2                      0                                0   \n",
       "3                      0                                0   \n",
       "4                      0                                0   \n",
       "5                      0                                0   \n",
       "6                      0                                0   \n",
       "7                      0                                0   \n",
       "\n",
       "   motorcycle_death_count motorcycle_serious_injury_count  other_death_count  \\\n",
       "0                       0                               0                  0   \n",
       "1                       0                               0                  0   \n",
       "2                       0                               0                  0   \n",
       "3                       0                               0                  0   \n",
       "4                       0                               0                  0   \n",
       "5                       0                               0                  0   \n",
       "6                       0                               0                  0   \n",
       "7                       0                               0                  0   \n",
       "\n",
       "  other_serious_injury_count  onsys_fl  private_dr_fl  \n",
       "0                          0         Y              N  \n",
       "1                          0         Y              N  \n",
       "2                          0         Y              N  \n",
       "3                          0         N              N  \n",
       "4                          0         Y              N  \n",
       "5                          0         N              N  \n",
       "6                          0         Y              N  \n",
       "7                          0         N              N  \n",
       "\n",
       "[8 rows x 51 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crash_data.head(8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 115449 entries, 0 to 115448\n",
      "Data columns (total 25 columns):\n",
      " #   Column               Non-Null Count   Dtype  \n",
      "---  ------               --------------   -----  \n",
      " 0   dt                   115449 non-null  int64  \n",
      " 1   dt_iso               115449 non-null  object \n",
      " 2   timezone             115449 non-null  int64  \n",
      " 3   city_name            115449 non-null  object \n",
      " 4   lat                  115449 non-null  float64\n",
      " 5   lon                  115449 non-null  float64\n",
      " 6   temp                 115449 non-null  float64\n",
      " 7   feels_like           115449 non-null  float64\n",
      " 8   temp_min             115449 non-null  float64\n",
      " 9   temp_max             115449 non-null  float64\n",
      " 10  pressure             115449 non-null  int64  \n",
      " 11  sea_level            0 non-null       float64\n",
      " 12  grnd_level           0 non-null       float64\n",
      " 13  humidity             115449 non-null  int64  \n",
      " 14  wind_speed           115449 non-null  float64\n",
      " 15  wind_deg             115449 non-null  int64  \n",
      " 16  rain_1h              10096 non-null   float64\n",
      " 17  rain_3h              1634 non-null    float64\n",
      " 18  snow_1h              125 non-null     float64\n",
      " 19  snow_3h              14 non-null      float64\n",
      " 20  clouds_all           115449 non-null  int64  \n",
      " 21  weather_id           115449 non-null  int64  \n",
      " 22  weather_main         115449 non-null  object \n",
      " 23  weather_description  115449 non-null  object \n",
      " 24  weather_icon         115449 non-null  object \n",
      "dtypes: float64(13), int64(7), object(5)\n",
      "memory usage: 22.0+ MB\n"
     ]
    }
   ],
   "source": [
    "weather_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>dt_iso</th>\n",
       "      <th>timezone</th>\n",
       "      <th>city_name</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>...</th>\n",
       "      <th>wind_deg</th>\n",
       "      <th>rain_1h</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>weather_main</th>\n",
       "      <th>weather_description</th>\n",
       "      <th>weather_icon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1199145600</td>\n",
       "      <td>2008-01-01 00:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>57.49</td>\n",
       "      <td>46.69</td>\n",
       "      <td>55.74</td>\n",
       "      <td>57.92</td>\n",
       "      <td>...</td>\n",
       "      <td>360</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1199149200</td>\n",
       "      <td>2008-01-01 01:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>55.13</td>\n",
       "      <td>47.39</td>\n",
       "      <td>53.96</td>\n",
       "      <td>57.22</td>\n",
       "      <td>...</td>\n",
       "      <td>360</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1199152800</td>\n",
       "      <td>2008-01-01 02:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>52.79</td>\n",
       "      <td>42.84</td>\n",
       "      <td>51.08</td>\n",
       "      <td>54.89</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1199156400</td>\n",
       "      <td>2008-01-01 03:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>50.65</td>\n",
       "      <td>38.66</td>\n",
       "      <td>48.98</td>\n",
       "      <td>51.98</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1199160000</td>\n",
       "      <td>2008-01-01 04:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>49.14</td>\n",
       "      <td>39.16</td>\n",
       "      <td>47.69</td>\n",
       "      <td>50.00</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           dt                         dt_iso  timezone city_name        lat  \\\n",
       "0  1199145600  2008-01-01 00:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "1  1199149200  2008-01-01 01:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "2  1199152800  2008-01-01 02:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "3  1199156400  2008-01-01 03:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "4  1199160000  2008-01-01 04:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "\n",
       "         lon   temp  feels_like  temp_min  temp_max  ...  wind_deg  rain_1h  \\\n",
       "0 -97.743061  57.49       46.69     55.74     57.92  ...       360      NaN   \n",
       "1 -97.743061  55.13       47.39     53.96     57.22  ...       360      NaN   \n",
       "2 -97.743061  52.79       42.84     51.08     54.89  ...        10      NaN   \n",
       "3 -97.743061  50.65       38.66     48.98     51.98  ...        10      NaN   \n",
       "4 -97.743061  49.14       39.16     47.69     50.00  ...        10      NaN   \n",
       "\n",
       "   rain_3h  snow_1h  snow_3h  clouds_all  weather_id  weather_main  \\\n",
       "0      NaN      NaN      NaN           1         800         Clear   \n",
       "1      NaN      NaN      NaN           1         800         Clear   \n",
       "2      NaN      NaN      NaN           1         800         Clear   \n",
       "3      NaN      NaN      NaN           1         800         Clear   \n",
       "4      NaN      NaN      NaN           1         800         Clear   \n",
       "\n",
       "   weather_description  weather_icon  \n",
       "0         sky is clear           01n  \n",
       "1         sky is clear           01n  \n",
       "2         sky is clear           01n  \n",
       "3         sky is clear           01n  \n",
       "4         sky is clear           01n  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# One important difference is the time format. Will either need to create a column for local time on the \n",
    "# weather data or a utc column on the crash data.\n",
    "\n",
    "#  Definitely some columns in crash data that won't be helpful. 'case_id' is unuseful because we already have \n",
    "# 'crash_id' to differentiate between evetns. The rest because of either redundancy or lack of data.\n",
    "\n",
    "del_col = ['case_id', 'rpt_latitude', 'rpt_longitude', 'units_involved','atd_mode_category_metadata', 'street_nbr_2']\n",
    "crash_data = crash_data.drop(del_col, 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to match weather data will need to create a column in crash data that is UTC unix. \n",
    "# First need to convert 'crash_time' to datetime.\n",
    "\n",
    "crash_data['crash_date']= pd.to_datetime(crash_data['crash_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018-12-03 11:19:00\n"
     ]
    }
   ],
   "source": [
    "print(crash_data['crash_date'][3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#need to also round to hour to match granularity of weather data.\n",
    "\n",
    "def rounding(time):\n",
    "    return time.floor('H')\n",
    "\n",
    "crash_data['utc_unix'] = crash_data['crash_date'].apply(rounding)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Probably not the most elegant way, but will write out what times are daylight savings, then convert to unix.\n",
    "\n",
    "def utc(time):\n",
    "    if time > pd.Timestamp('2010-03-14T02') and time < pd.Timestamp('2010-11-07T02'):\n",
    "        new_time = time + pd.Timedelta('5 hours')\n",
    "    elif time > pd.Timestamp('2011-03-13T02') and time < pd.Timestamp('2011-11-06T02'):\n",
    "        new_time = time + pd.Timedelta('5 hours')\n",
    "    elif time > pd.Timestamp('2012-03-11T02') and time < pd.Timestamp('2012-11-04T02'):\n",
    "        new_time = time + pd.Timedelta('5 hours')\n",
    "    elif time > pd.Timestamp('2013-03-10T02') and time < pd.Timestamp('2013-11-03T02'):\n",
    "        new_time = time + pd.Timedelta('5 hours')\n",
    "    elif time > pd.Timestamp('2014-03-09T02') and time < pd.Timestamp('2014-11-02T02'):\n",
    "        new_time = time + pd.Timedelta('5 hours')\n",
    "    elif time > pd.Timestamp('2015-03-08T02') and time < pd.Timestamp('2015-11-01T02'):\n",
    "        new_time = time + pd.Timedelta('5 hours')\n",
    "    elif time > pd.Timestamp('2016-03-13T02') and time < pd.Timestamp('2016-11-06T02'):\n",
    "        new_time = time + pd.Timedelta('5 hours')\n",
    "    elif time > pd.Timestamp('2017-03-12T02') and time < pd.Timestamp('2017-11-05T02'):\n",
    "        new_time = time + pd.Timedelta('5 hours')\n",
    "    elif time > pd.Timestamp('2018-03-11T02') and time < pd.Timestamp('2018-11-04T02'):\n",
    "        new_time = time + pd.Timedelta('5 hours')\n",
    "    elif time > pd.Timestamp('2019-03-10T02') and time < pd.Timestamp('2019-11-03T02'):\n",
    "        new_time = time + pd.Timedelta('5 hours')\n",
    "    elif time > pd.Timestamp('2020-03-08T02') and time < pd.Timestamp('2020-11-01T02'):\n",
    "        new_time = time + pd.Timedelta('5 hours')\n",
    "    else:\n",
    "        new_time = time + pd.Timedelta('6 hours')\n",
    "    return new_time\n",
    "        \n",
    "def unix(time):\n",
    "    return (time - pd.Timestamp(\"1970-01-01\")) // pd.Timedelta('1s')\n",
    "    \n",
    "def unix_utc(time):\n",
    "    return unix(utc(time))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "crash_data['utc_unix'] = crash_data['utc_unix'].apply(unix_utc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1543957200\n",
       "1    1544209200\n",
       "2    1542535200\n",
       "3    1543856400\n",
       "4    1543503600\n",
       "Name: utc_unix, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crash_data['utc_unix'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "115449\n",
      "112200\n"
     ]
    }
   ],
   "source": [
    "# now I need to checkout the weather data, the biggest issue in merging would be if there were more than one\n",
    "# weather observation per hour.\n",
    "\n",
    "print(len(weather_data['dt_iso']))\n",
    "print(weather_data['dt_iso'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "weather_id\n",
      "115449\n",
      "weather_main\n",
      "115449\n",
      "weather_description\n",
      "115449\n",
      "weather_icon\n",
      "115443\n"
     ]
    }
   ],
   "source": [
    "# there does appear to be more than one observation per hour. I'll group by each column and see which columns result\n",
    "# in more groupings than we have dates. That will tell me what the repeat times are differentiating on.\n",
    "\n",
    "for col in weather_data.columns:\n",
    "    if weather_data['dt_iso'].nunique() < weather_data.groupby(['dt_iso',col]).ngroups:\n",
    "        print(col)\n",
    "        print(weather_data.groupby(['dt_iso',col]).ngroups)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# based on documentation, these aren't very important columns. They're created as a function of the numbers\n",
    "# recorded in other columns to offer a display or summary. If I drop these and then delete repeat rows, I should\n",
    "# have only unique observations.\n",
    "\n",
    "weather_cols_drop = ['weather_id', 'weather_main', 'weather_description', 'weather_icon']\n",
    "weather_data = weather_data.drop(weather_cols_drop, 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "115449\n",
      "112200\n"
     ]
    }
   ],
   "source": [
    "print(len(weather_data['dt_iso']))\n",
    "print(weather_data['dt_iso'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_data = weather_data.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "112200\n",
      "112200\n"
     ]
    }
   ],
   "source": [
    "print(len(weather_data['dt_iso']))\n",
    "print(weather_data['dt_iso'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Success! Now should be theoretically ready to merge these two dataframes.\n",
    "weather_data = weather_data.rename(columns= {'dt':'utc_unix'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.merge(crash_data, weather_data, how= 'left', on = 'utc_unix')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "161824\n",
      "161824\n"
     ]
    }
   ],
   "source": [
    "# new dataframe should have as many rows as crash_data\n",
    "\n",
    "print(len(crash_data))\n",
    "print(len(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a function to get the weekday for each row based on the date.\n",
    "def get_weekday(date):\n",
    "    return date.weekday()\n",
    "weekdays = {0: 'Monday', 1:'Tuesday', 2:'Wednesday', 3: \"Thursday\", 4:'Friday', 5:'Saturday', 6: \"Sunday\"}\n",
    "#use that function to create weekday column.\n",
    "df['Weekday'] = df['crash_date'].apply(get_weekday)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Friday       26897\n",
       "Thursday     24202\n",
       "Tuesday      23605\n",
       "Wednesday    23297\n",
       "Saturday     22844\n",
       "Monday       21798\n",
       "Sunday       19181\n",
       "Name: Weekday, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Weekday'] = df['Weekday'].replace(weekdays)\n",
    "df['Weekday'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 161824 entries, 0 to 161823\n",
      "Data columns (total 67 columns):\n",
      " #   Column                              Non-Null Count   Dtype         \n",
      "---  ------                              --------------   -----         \n",
      " 0   crash_id                            161824 non-null  int64         \n",
      " 1   crash_fatal_fl                      161824 non-null  object        \n",
      " 2   crash_date                          161824 non-null  datetime64[ns]\n",
      " 3   crash_time                          161824 non-null  object        \n",
      " 4   rpt_block_num                       140411 non-null  object        \n",
      " 5   rpt_street_pfx                      90518 non-null   object        \n",
      " 6   rpt_street_name                     161818 non-null  object        \n",
      " 7   rpt_street_sfx                      118702 non-null  object        \n",
      " 8   crash_speed_limit                   161819 non-null  float64       \n",
      " 9   road_constr_zone_fl                 161819 non-null  object        \n",
      " 10  latitude                            155992 non-null  float64       \n",
      " 11  longitude                           155992 non-null  float64       \n",
      " 12  street_name                         161819 non-null  object        \n",
      " 13  street_nbr                          67555 non-null   float64       \n",
      " 14  street_name_2                       70496 non-null   object        \n",
      " 15  crash_sev_id                        161819 non-null  float64       \n",
      " 16  sus_serious_injry_cnt               161824 non-null  int64         \n",
      " 17  nonincap_injry_cnt                  161821 non-null  float64       \n",
      " 18  poss_injry_cnt                      161821 non-null  float64       \n",
      " 19  non_injry_cnt                       161821 non-null  float64       \n",
      " 20  unkn_injry_cnt                      161819 non-null  float64       \n",
      " 21  tot_injry_cnt                       161820 non-null  float64       \n",
      " 22  death_cnt                           161824 non-null  int64         \n",
      " 23  contrib_factr_p1_id                 30884 non-null   float64       \n",
      " 24  contrib_factr_p2_id                 6298 non-null    float64       \n",
      " 25  pedestrian_fl                       4389 non-null    object        \n",
      " 26  motor_vehicle_fl                    160319 non-null  object        \n",
      " 27  motorcycle_fl                       4509 non-null    object        \n",
      " 28  bicycle_fl                          3125 non-null    object        \n",
      " 29  other_fl                            5130 non-null    object        \n",
      " 30  point                               155992 non-null  object        \n",
      " 31  apd_confirmed_fatality              161824 non-null  object        \n",
      " 32  apd_confirmed_death_count           161822 non-null  float64       \n",
      " 33  motor_vehicle_death_count           161824 non-null  int64         \n",
      " 34  motor_vehicle_serious_injury_count  161824 non-null  int64         \n",
      " 35  bicycle_death_count                 161824 non-null  int64         \n",
      " 36  bicycle_serious_injury_count        161824 non-null  int64         \n",
      " 37  pedestrian_death_count              161824 non-null  int64         \n",
      " 38  pedestrian_serious_injury_count     161824 non-null  int64         \n",
      " 39  motorcycle_death_count              161824 non-null  int64         \n",
      " 40  motorcycle_serious_injury_count     161824 non-null  int64         \n",
      " 41  other_death_count                   161824 non-null  int64         \n",
      " 42  other_serious_injury_count          161824 non-null  int64         \n",
      " 43  onsys_fl                            161819 non-null  object        \n",
      " 44  private_dr_fl                       161819 non-null  object        \n",
      " 45  utc_unix                            161824 non-null  int64         \n",
      " 46  dt_iso                              161824 non-null  object        \n",
      " 47  timezone                            161824 non-null  int64         \n",
      " 48  city_name                           161824 non-null  object        \n",
      " 49  lat                                 161824 non-null  float64       \n",
      " 50  lon                                 161824 non-null  float64       \n",
      " 51  temp                                161824 non-null  float64       \n",
      " 52  feels_like                          161824 non-null  float64       \n",
      " 53  temp_min                            161824 non-null  float64       \n",
      " 54  temp_max                            161824 non-null  float64       \n",
      " 55  pressure                            161824 non-null  int64         \n",
      " 56  sea_level                           0 non-null       float64       \n",
      " 57  grnd_level                          0 non-null       float64       \n",
      " 58  humidity                            161824 non-null  int64         \n",
      " 59  wind_speed                          161824 non-null  float64       \n",
      " 60  wind_deg                            161824 non-null  int64         \n",
      " 61  rain_1h                             14899 non-null   float64       \n",
      " 62  rain_3h                             2186 non-null    float64       \n",
      " 63  snow_1h                             159 non-null     float64       \n",
      " 64  snow_3h                             14 non-null      float64       \n",
      " 65  clouds_all                          161824 non-null  int64         \n",
      " 66  Weekday                             161824 non-null  object        \n",
      "dtypes: datetime64[ns](1), float64(26), int64(19), object(21)\n",
      "memory usage: 84.0+ MB\n"
     ]
    }
   ],
   "source": [
    "# looking through info about df to see if merging created any redundant columns:\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# there's a few unnecessary columns now. Since we have a column with utc time in unix, there's no need \n",
    "# for timezone. The 'lat' and 'lon' from weather data are all the same, 'city_name' is also all the same. 'sea_level'\n",
    "# and 'ground_level' are also completely empty.\n",
    "\n",
    "df = df.drop(['timezone', 'lat', 'lon', 'city_name', 'sea_level', 'grnd_level'], 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rpt_street_pfx</th>\n",
       "      <th>rpt_street_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N</td>\n",
       "      <td>IH 35 SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>N IH 35 SVRD SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>N</td>\n",
       "      <td>N IH 35 NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NUECES ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>N</td>\n",
       "      <td>LAMAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>W</td>\n",
       "      <td>OLTORF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>S</td>\n",
       "      <td>S IH 35 NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>JOLLYVILLE RD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>W</td>\n",
       "      <td>W RIVERSIDE DR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>BIG BEND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NaN</td>\n",
       "      <td>N IH 35 SVRD NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>BLUFF SPRINGS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>N MOPAC EXPY NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>E 12TH ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>S</td>\n",
       "      <td>S IH 35 SVRD SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>N</td>\n",
       "      <td>IH 35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>NaN</td>\n",
       "      <td>VALLEY HIGH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>NaN</td>\n",
       "      <td>BARTON SPRINGS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>E</td>\n",
       "      <td>E RIVERSIDE DR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>E</td>\n",
       "      <td>KOENIG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rpt_street_pfx  rpt_street_name\n",
       "0               N         IH 35 SB\n",
       "1             NaN  N IH 35 SVRD SB\n",
       "2               N       N IH 35 NB\n",
       "3             NaN        NUECES ST\n",
       "4               N            LAMAR\n",
       "5               W           OLTORF\n",
       "6               S       S IH 35 NB\n",
       "7             NaN    JOLLYVILLE RD\n",
       "8               W   W RIVERSIDE DR\n",
       "9             NaN         BIG BEND\n",
       "10            NaN  N IH 35 SVRD NB\n",
       "11            NaN    BLUFF SPRINGS\n",
       "12            NaN  N MOPAC EXPY NB\n",
       "13            NaN        E 12TH ST\n",
       "14              S  S IH 35 SVRD SB\n",
       "15              N            IH 35\n",
       "16            NaN      VALLEY HIGH\n",
       "17            NaN   BARTON SPRINGS\n",
       "18              E   E RIVERSIDE DR\n",
       "19              E           KOENIG"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['rpt_street_pfx', 'rpt_street_name']].head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# would like to add the prefix to street names that dont already have it, and then drop the prefix column because\n",
    "# it is often redundant information.\n",
    "\n",
    "# make sure there is no trailing/leading whitespace on either column:\n",
    "df['rpt_street_pfx'] = df['rpt_street_pfx'].str.strip()\n",
    "df['rpt_street_name'] = df['rpt_street_name'].str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "N     30102\n",
       "E     21863\n",
       "S     21111\n",
       "W     17281\n",
       "SW       52\n",
       "NE       46\n",
       "SE       34\n",
       "NW       29\n",
       "Name: rpt_street_pfx, dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['rpt_street_pfx'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "prefix_true = df['rpt_street_pfx'].notna()"
   ]
  },
  {
   "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>crash_id</th>\n",
       "      <th>crash_fatal_fl</th>\n",
       "      <th>crash_date</th>\n",
       "      <th>crash_time</th>\n",
       "      <th>rpt_block_num</th>\n",
       "      <th>rpt_street_pfx</th>\n",
       "      <th>rpt_street_name</th>\n",
       "      <th>rpt_street_sfx</th>\n",
       "      <th>crash_speed_limit</th>\n",
       "      <th>road_constr_zone_fl</th>\n",
       "      <th>...</th>\n",
       "      <th>pressure</th>\n",
       "      <th>humidity</th>\n",
       "      <th>wind_speed</th>\n",
       "      <th>wind_deg</th>\n",
       "      <th>rain_1h</th>\n",
       "      <th>rain_3h</th>\n",
       "      <th>snow_1h</th>\n",
       "      <th>snow_3h</th>\n",
       "      <th>clouds_all</th>\n",
       "      <th>Weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16776868</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-04 15:36:00</td>\n",
       "      <td>15:36:00</td>\n",
       "      <td>1900</td>\n",
       "      <td>N</td>\n",
       "      <td>IH 35 SB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1025</td>\n",
       "      <td>38</td>\n",
       "      <td>6.93</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16757244</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-11-18 04:10:00</td>\n",
       "      <td>04:10:00</td>\n",
       "      <td>4500</td>\n",
       "      <td>N</td>\n",
       "      <td>N IH 35 NB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1020</td>\n",
       "      <td>88</td>\n",
       "      <td>3.22</td>\n",
       "      <td>120</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16765750</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-11-29 09:07:00</td>\n",
       "      <td>09:07:00</td>\n",
       "      <td>4100</td>\n",
       "      <td>N</td>\n",
       "      <td>LAMAR</td>\n",
       "      <td>BLVD</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1013</td>\n",
       "      <td>88</td>\n",
       "      <td>5.75</td>\n",
       "      <td>200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>16758169</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-11-23 07:34:00</td>\n",
       "      <td>07:34:00</td>\n",
       "      <td>1000</td>\n",
       "      <td>W</td>\n",
       "      <td>OLTORF</td>\n",
       "      <td>ST</td>\n",
       "      <td>35.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1014</td>\n",
       "      <td>88</td>\n",
       "      <td>4.61</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16780753</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-06 18:25:00</td>\n",
       "      <td>18:25:00</td>\n",
       "      <td>3000</td>\n",
       "      <td>S</td>\n",
       "      <td>S IH 35 NB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1022</td>\n",
       "      <td>93</td>\n",
       "      <td>4.52</td>\n",
       "      <td>27</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>16772597</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-01 02:30:00</td>\n",
       "      <td>02:30:00</td>\n",
       "      <td>500</td>\n",
       "      <td>W</td>\n",
       "      <td>W RIVERSIDE DR</td>\n",
       "      <td>DR</td>\n",
       "      <td>35.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1005</td>\n",
       "      <td>88</td>\n",
       "      <td>9.01</td>\n",
       "      <td>288</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>16759539</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-11-27 02:00:00</td>\n",
       "      <td>02:00:00</td>\n",
       "      <td>6400</td>\n",
       "      <td>S</td>\n",
       "      <td>S IH 35 SVRD SB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>...</td>\n",
       "      <td>1026</td>\n",
       "      <td>70</td>\n",
       "      <td>0.98</td>\n",
       "      <td>289</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16786472</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-07 15:34:00</td>\n",
       "      <td>15:34:00</td>\n",
       "      <td>9600</td>\n",
       "      <td>N</td>\n",
       "      <td>IH 35</td>\n",
       "      <td>HWY</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1020</td>\n",
       "      <td>89</td>\n",
       "      <td>6.93</td>\n",
       "      <td>20</td>\n",
       "      <td>3.45</td>\n",
       "      <td>12.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>16772584</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-11-30 17:33:00</td>\n",
       "      <td>17:33:00</td>\n",
       "      <td>2100</td>\n",
       "      <td>E</td>\n",
       "      <td>E RIVERSIDE DR</td>\n",
       "      <td>DR</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1003</td>\n",
       "      <td>65</td>\n",
       "      <td>10.36</td>\n",
       "      <td>160</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>16759407</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-11-26 18:32:00</td>\n",
       "      <td>18:32:00</td>\n",
       "      <td>800</td>\n",
       "      <td>E</td>\n",
       "      <td>KOENIG</td>\n",
       "      <td>LN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1025</td>\n",
       "      <td>34</td>\n",
       "      <td>3.36</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>16750408</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-11-19 12:00:00</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>9500</td>\n",
       "      <td>N</td>\n",
       "      <td>US HWY 183</td>\n",
       "      <td>BLVD</td>\n",
       "      <td>65.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1024</td>\n",
       "      <td>57</td>\n",
       "      <td>4.61</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>16780799</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-07 01:49:00</td>\n",
       "      <td>01:49:00</td>\n",
       "      <td>9600</td>\n",
       "      <td>S</td>\n",
       "      <td>S IH 35 SB</td>\n",
       "      <td>HWY</td>\n",
       "      <td>70.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1023</td>\n",
       "      <td>88</td>\n",
       "      <td>5.75</td>\n",
       "      <td>30</td>\n",
       "      <td>2.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>16757587</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-11-18 14:34:00</td>\n",
       "      <td>14:34:00</td>\n",
       "      <td>1400</td>\n",
       "      <td>S</td>\n",
       "      <td>S MOPAC EXPY NB</td>\n",
       "      <td>EXPY</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1023</td>\n",
       "      <td>65</td>\n",
       "      <td>5.82</td>\n",
       "      <td>360</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>16772599</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-01 09:03:00</td>\n",
       "      <td>09:03:00</td>\n",
       "      <td>800</td>\n",
       "      <td>W</td>\n",
       "      <td>15TH ST</td>\n",
       "      <td>ST</td>\n",
       "      <td>35.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1007</td>\n",
       "      <td>37</td>\n",
       "      <td>6.91</td>\n",
       "      <td>310</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>16765720</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-11-28 16:28:00</td>\n",
       "      <td>16:28:00</td>\n",
       "      <td>60</td>\n",
       "      <td>N</td>\n",
       "      <td>N PLEASANT VALLEY RD</td>\n",
       "      <td>RD</td>\n",
       "      <td>35.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1012</td>\n",
       "      <td>49</td>\n",
       "      <td>6.91</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>16758236</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-11-24 08:22:00</td>\n",
       "      <td>08:22:00</td>\n",
       "      <td>5800</td>\n",
       "      <td>E</td>\n",
       "      <td>MARTIN LUTHER KING JR</td>\n",
       "      <td>BLVD</td>\n",
       "      <td>45.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1012</td>\n",
       "      <td>76</td>\n",
       "      <td>5.50</td>\n",
       "      <td>244</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>16772684</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-02 12:40:00</td>\n",
       "      <td>12:40:00</td>\n",
       "      <td>2600</td>\n",
       "      <td>W</td>\n",
       "      <td>W SLAUGHTER</td>\n",
       "      <td>LN</td>\n",
       "      <td>40.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1012</td>\n",
       "      <td>34</td>\n",
       "      <td>4.70</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>16780755</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-06 21:12:00</td>\n",
       "      <td>21:12:00</td>\n",
       "      <td>3100</td>\n",
       "      <td>S</td>\n",
       "      <td>MOPAC SB SVRD</td>\n",
       "      <td>LOOP</td>\n",
       "      <td>65.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1022</td>\n",
       "      <td>94</td>\n",
       "      <td>1.83</td>\n",
       "      <td>58</td>\n",
       "      <td>0.50</td>\n",
       "      <td>1.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>16778964</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-05 16:44:00</td>\n",
       "      <td>16:44:00</td>\n",
       "      <td>4500</td>\n",
       "      <td>W</td>\n",
       "      <td>E BEN WHITE BLVD WB</td>\n",
       "      <td>BLVD</td>\n",
       "      <td>65.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1027</td>\n",
       "      <td>38</td>\n",
       "      <td>5.75</td>\n",
       "      <td>140</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>16759390</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-09-09 11:13:00</td>\n",
       "      <td>11:13:00</td>\n",
       "      <td>3000</td>\n",
       "      <td>S</td>\n",
       "      <td>MOPAC EXPY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.0</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>1014</td>\n",
       "      <td>93</td>\n",
       "      <td>5.82</td>\n",
       "      <td>150</td>\n",
       "      <td>4.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 61 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    crash_id crash_fatal_fl          crash_date crash_time rpt_block_num  \\\n",
       "0   16776868              N 2018-12-04 15:36:00   15:36:00          1900   \n",
       "2   16757244              N 2018-11-18 04:10:00   04:10:00          4500   \n",
       "4   16765750              N 2018-11-29 09:07:00   09:07:00          4100   \n",
       "5   16758169              N 2018-11-23 07:34:00   07:34:00          1000   \n",
       "6   16780753              N 2018-12-06 18:25:00   18:25:00          3000   \n",
       "8   16772597              N 2018-12-01 02:30:00   02:30:00           500   \n",
       "14  16759539              N 2018-11-27 02:00:00   02:00:00          6400   \n",
       "15  16786472              N 2018-12-07 15:34:00   15:34:00          9600   \n",
       "18  16772584              N 2018-11-30 17:33:00   17:33:00          2100   \n",
       "19  16759407              N 2018-11-26 18:32:00   18:32:00           800   \n",
       "21  16750408              N 2018-11-19 12:00:00   12:00:00          9500   \n",
       "22  16780799              N 2018-12-07 01:49:00   01:49:00          9600   \n",
       "25  16757587              N 2018-11-18 14:34:00   14:34:00          1400   \n",
       "26  16772599              N 2018-12-01 09:03:00   09:03:00           800   \n",
       "27  16765720              N 2018-11-28 16:28:00   16:28:00            60   \n",
       "29  16758236              N 2018-11-24 08:22:00   08:22:00          5800   \n",
       "30  16772684              N 2018-12-02 12:40:00   12:40:00          2600   \n",
       "32  16780755              N 2018-12-06 21:12:00   21:12:00          3100   \n",
       "34  16778964              N 2018-12-05 16:44:00   16:44:00          4500   \n",
       "36  16759390              N 2018-09-09 11:13:00   11:13:00          3000   \n",
       "\n",
       "   rpt_street_pfx        rpt_street_name rpt_street_sfx  crash_speed_limit  \\\n",
       "0               N               IH 35 SB            NaN               -1.0   \n",
       "2               N             N IH 35 NB            NaN               70.0   \n",
       "4               N                  LAMAR           BLVD               -1.0   \n",
       "5               W                 OLTORF             ST               35.0   \n",
       "6               S             S IH 35 NB            NaN               65.0   \n",
       "8               W         W RIVERSIDE DR             DR               35.0   \n",
       "14              S        S IH 35 SVRD SB            NaN               55.0   \n",
       "15              N                  IH 35            HWY               -1.0   \n",
       "18              E         E RIVERSIDE DR             DR               -1.0   \n",
       "19              E                 KOENIG             LN               -1.0   \n",
       "21              N             US HWY 183           BLVD               65.0   \n",
       "22              S             S IH 35 SB            HWY               70.0   \n",
       "25              S        S MOPAC EXPY NB           EXPY               -1.0   \n",
       "26              W                15TH ST             ST               35.0   \n",
       "27              N   N PLEASANT VALLEY RD             RD               35.0   \n",
       "29              E  MARTIN LUTHER KING JR           BLVD               45.0   \n",
       "30              W            W SLAUGHTER             LN               40.0   \n",
       "32              S          MOPAC SB SVRD           LOOP               65.0   \n",
       "34              W    E BEN WHITE BLVD WB           BLVD               65.0   \n",
       "36              S             MOPAC EXPY            NaN               40.0   \n",
       "\n",
       "   road_constr_zone_fl  ...  pressure  humidity wind_speed  wind_deg rain_1h  \\\n",
       "0                    N  ...      1025        38       6.93        20     NaN   \n",
       "2                    N  ...      1020        88       3.22       120     NaN   \n",
       "4                    N  ...      1013        88       5.75       200     NaN   \n",
       "5                    N  ...      1014        88       4.61         0     NaN   \n",
       "6                    N  ...      1022        93       4.52        27    1.00   \n",
       "8                    N  ...      1005        88       9.01       288     NaN   \n",
       "14                   Y  ...      1026        70       0.98       289     NaN   \n",
       "15                   N  ...      1020        89       6.93        20    3.45   \n",
       "18                   N  ...      1003        65      10.36       160     NaN   \n",
       "19                   N  ...      1025        34       3.36        10     NaN   \n",
       "21                   N  ...      1024        57       4.61         0     NaN   \n",
       "22                   N  ...      1023        88       5.75        30    2.25   \n",
       "25                   N  ...      1023        65       5.82       360     NaN   \n",
       "26                   N  ...      1007        37       6.91       310     NaN   \n",
       "27                   N  ...      1012        49       6.91         0     NaN   \n",
       "29                   N  ...      1012        76       5.50       244     NaN   \n",
       "30                   N  ...      1012        34       4.70         0     NaN   \n",
       "32                   N  ...      1022        94       1.83        58    0.50   \n",
       "34                   N  ...      1027        38       5.75       140     NaN   \n",
       "36                   N  ...      1014        93       5.82       150    4.30   \n",
       "\n",
       "    rain_3h  snow_1h  snow_3h  clouds_all    Weekday  \n",
       "0       NaN      NaN      NaN           1    Tuesday  \n",
       "2       NaN      NaN      NaN          75     Sunday  \n",
       "4       NaN      NaN      NaN          90   Thursday  \n",
       "5       NaN      NaN      NaN          90     Friday  \n",
       "6       NaN      NaN      NaN          90   Thursday  \n",
       "8       NaN      NaN      NaN          20   Saturday  \n",
       "14      NaN      NaN      NaN           1    Tuesday  \n",
       "15     12.2      NaN      NaN          90     Friday  \n",
       "18      NaN      NaN      NaN           1     Friday  \n",
       "19      NaN      NaN      NaN          99     Monday  \n",
       "21      NaN      NaN      NaN          90     Monday  \n",
       "22      NaN      NaN      NaN          90     Friday  \n",
       "25      NaN      NaN      NaN          90     Sunday  \n",
       "26      NaN      NaN      NaN           1   Saturday  \n",
       "27      NaN      NaN      NaN           1  Wednesday  \n",
       "29      NaN      NaN      NaN           1   Saturday  \n",
       "30      NaN      NaN      NaN           0     Sunday  \n",
       "32      1.9      NaN      NaN          90   Thursday  \n",
       "34      NaN      NaN      NaN          90  Wednesday  \n",
       "36      NaN      NaN      NaN          90     Sunday  \n",
       "\n",
       "[20 rows x 61 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[prefix_true].head(20)"
   ]
  },
  {
   "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>street_name</th>\n",
       "      <th>rpt_street_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IH0035</td>\n",
       "      <td>IH 35 SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>IH0035</td>\n",
       "      <td>N IH 35 SVRD SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>IH0035</td>\n",
       "      <td>N IH 35 NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NUECES ST</td>\n",
       "      <td>NUECES ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SL0343</td>\n",
       "      <td>LAMAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>OLTORF ST W</td>\n",
       "      <td>OLTORF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>IH0035</td>\n",
       "      <td>S IH 35 NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>JOLLYVILLE RD</td>\n",
       "      <td>JOLLYVILLE RD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>W RIVERSIDE DR</td>\n",
       "      <td>W RIVERSIDE DR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>BIG BEND DR</td>\n",
       "      <td>BIG BEND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>S I 35</td>\n",
       "      <td>N IH 35 SVRD NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>BLUFF SPRINGS RD</td>\n",
       "      <td>BLUFF SPRINGS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>SL0001</td>\n",
       "      <td>N MOPAC EXPY NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>E 12TH ST</td>\n",
       "      <td>E 12TH ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>S I 35</td>\n",
       "      <td>S IH 35 SVRD SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>IH0035</td>\n",
       "      <td>IH 35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>VALLEY HIGH CIR</td>\n",
       "      <td>VALLEY HIGH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>BARTON SPRINGS RD</td>\n",
       "      <td>BARTON SPRINGS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>E RIVERSIDE DR</td>\n",
       "      <td>E RIVERSIDE DR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>RM2222</td>\n",
       "      <td>KOENIG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>GUADALUPE ST</td>\n",
       "      <td>GUADALUPE ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>US0183</td>\n",
       "      <td>US HWY 183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>IH0035</td>\n",
       "      <td>S IH 35 SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>SHOAL CREST AVE</td>\n",
       "      <td>SHOAL CREST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>SL0071</td>\n",
       "      <td>NOT REPORTED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>SL0001</td>\n",
       "      <td>S MOPAC EXPY NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>W 15TH ST</td>\n",
       "      <td>15TH ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>N N PLEASANT VALLEY RD RD</td>\n",
       "      <td>N PLEASANT VALLEY RD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>RED RIVER ST</td>\n",
       "      <td>RED RIVER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>FM0969</td>\n",
       "      <td>MARTIN LUTHER KING JR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>W SLAUGHTER LN</td>\n",
       "      <td>W SLAUGHTER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>BURNET RD</td>\n",
       "      <td>BURNET RD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>SL0001</td>\n",
       "      <td>MOPAC SB SVRD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>LAKELINE MALL DR</td>\n",
       "      <td>LAKELINE MALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>US0290</td>\n",
       "      <td>E BEN WHITE BLVD WB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>LOST CREEK BLVD</td>\n",
       "      <td>LOST CREEK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>S MOPAC EXPY</td>\n",
       "      <td>MOPAC EXPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>IH0035</td>\n",
       "      <td>IH 35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>IH0035</td>\n",
       "      <td>N IH 35 SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>SL0343</td>\n",
       "      <td>CESAR CHAVEZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>MOONGLOW DR</td>\n",
       "      <td>VIRGO LN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>US0183</td>\n",
       "      <td>NOT REPORTED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>IH0035</td>\n",
       "      <td>S IH 35 SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>SL0343</td>\n",
       "      <td>S LAMAR BLVD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>SL0001</td>\n",
       "      <td>NEWFIELD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>SL0343</td>\n",
       "      <td>S LAMAR BLVD NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>SL0001</td>\n",
       "      <td>MOPAC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>S 1ST ST</td>\n",
       "      <td>1ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>RESEARCH BLVD</td>\n",
       "      <td>RESEARCH SVRD NB TRN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>IH0035</td>\n",
       "      <td>IH 35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  street_name        rpt_street_name\n",
       "0                      IH0035               IH 35 SB\n",
       "1                      IH0035        N IH 35 SVRD SB\n",
       "2                      IH0035             N IH 35 NB\n",
       "3                   NUECES ST              NUECES ST\n",
       "4                      SL0343                  LAMAR\n",
       "5                 OLTORF ST W                 OLTORF\n",
       "6                      IH0035             S IH 35 NB\n",
       "7               JOLLYVILLE RD          JOLLYVILLE RD\n",
       "8              W RIVERSIDE DR         W RIVERSIDE DR\n",
       "9                 BIG BEND DR               BIG BEND\n",
       "10                     S I 35        N IH 35 SVRD NB\n",
       "11           BLUFF SPRINGS RD          BLUFF SPRINGS\n",
       "12                     SL0001        N MOPAC EXPY NB\n",
       "13                  E 12TH ST              E 12TH ST\n",
       "14                     S I 35        S IH 35 SVRD SB\n",
       "15                     IH0035                  IH 35\n",
       "16            VALLEY HIGH CIR            VALLEY HIGH\n",
       "17          BARTON SPRINGS RD         BARTON SPRINGS\n",
       "18             E RIVERSIDE DR         E RIVERSIDE DR\n",
       "19                     RM2222                 KOENIG\n",
       "20               GUADALUPE ST           GUADALUPE ST\n",
       "21                     US0183             US HWY 183\n",
       "22                     IH0035             S IH 35 SB\n",
       "23            SHOAL CREST AVE            SHOAL CREST\n",
       "24                     SL0071           NOT REPORTED\n",
       "25                     SL0001        S MOPAC EXPY NB\n",
       "26                  W 15TH ST                15TH ST\n",
       "27  N N PLEASANT VALLEY RD RD   N PLEASANT VALLEY RD\n",
       "28               RED RIVER ST              RED RIVER\n",
       "29                     FM0969  MARTIN LUTHER KING JR\n",
       "30             W SLAUGHTER LN            W SLAUGHTER\n",
       "31                  BURNET RD              BURNET RD\n",
       "32                     SL0001          MOPAC SB SVRD\n",
       "33           LAKELINE MALL DR          LAKELINE MALL\n",
       "34                     US0290    E BEN WHITE BLVD WB\n",
       "35            LOST CREEK BLVD             LOST CREEK\n",
       "36               S MOPAC EXPY             MOPAC EXPY\n",
       "37                     IH0035                  IH 35\n",
       "38                     IH0035             N IH 35 SB\n",
       "39                     SL0343           CESAR CHAVEZ\n",
       "40                MOONGLOW DR               VIRGO LN\n",
       "41                     US0183           NOT REPORTED\n",
       "42                     IH0035             S IH 35 SB\n",
       "43                     SL0343           S LAMAR BLVD\n",
       "44                     SL0001               NEWFIELD\n",
       "45                     SL0343        S LAMAR BLVD NB\n",
       "46                     SL0001                  MOPAC\n",
       "47                   S 1ST ST                    1ST\n",
       "48              RESEARCH BLVD   RESEARCH SVRD NB TRN\n",
       "49                     IH0035                  IH 35"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# seeing an example where the prefix column is 'W' then reported street is 'E BEN WHITE BLVD'\n",
    "# maybe actual 'street_name' columns is less messy and I can just focus on that one.\n",
    "df[['street_name','rpt_street_name']]. head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>apd_confirmed_fatality</th>\n",
       "      <th>apd_confirmed_death_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>968</th>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1348</th>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     apd_confirmed_fatality  apd_confirmed_death_count\n",
       "968                       N                        NaN\n",
       "1348                      N                        NaN"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pretty messy either way. Will come back to this later.\n",
    "\n",
    "df[['apd_confirmed_fatality','apd_confirmed_death_count']][df['apd_confirmed_death_count'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# no confirmed fatality, death count should be zero.\n",
    "df = df.fillna({'apd_confirmed_death_count':0})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['apd_confirmed_death_count'].isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "rpt_block_num           21413\n",
       "rpt_street_pfx          71306\n",
       "rpt_street_name             6\n",
       "rpt_street_sfx          43122\n",
       "crash_speed_limit           5\n",
       "road_constr_zone_fl         5\n",
       "latitude                 5832\n",
       "longitude                5832\n",
       "street_name                 5\n",
       "street_nbr              94269\n",
       "street_name_2           91328\n",
       "crash_sev_id                5\n",
       "nonincap_injry_cnt          3\n",
       "poss_injry_cnt              3\n",
       "non_injry_cnt               3\n",
       "unkn_injry_cnt              5\n",
       "tot_injry_cnt               4\n",
       "contrib_factr_p1_id    130940\n",
       "contrib_factr_p2_id    155526\n",
       "pedestrian_fl          157435\n",
       "motor_vehicle_fl         1505\n",
       "motorcycle_fl          157315\n",
       "bicycle_fl             158699\n",
       "other_fl               156694\n",
       "point                    5832\n",
       "onsys_fl                    5\n",
       "private_dr_fl               5\n",
       "rain_1h                146925\n",
       "rain_3h                159638\n",
       "snow_1h                161665\n",
       "snow_3h                161810\n",
       "dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()[df.isna().sum()>0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\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>rain_1h</th>\n",
       "      <th>rain_3h</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3.40</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2.25</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>4.30</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>0.30</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161782</th>\n",
       "      <td>8.90</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161799</th>\n",
       "      <td>0.50</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161801</th>\n",
       "      <td>3.15</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161803</th>\n",
       "      <td>2.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161819</th>\n",
       "      <td>0.50</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13543 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        rain_1h  rain_3h\n",
       "6          1.00      NaN\n",
       "11         3.40      NaN\n",
       "22         2.25      NaN\n",
       "36         4.30      NaN\n",
       "39         0.30      NaN\n",
       "...         ...      ...\n",
       "161782     8.90      NaN\n",
       "161799     0.50      NaN\n",
       "161801     3.15      NaN\n",
       "161803     2.00      NaN\n",
       "161819     0.50      NaN\n",
       "\n",
       "[13543 rows x 2 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['rain_1h','rain_3h']][df['rain_3h'].isna()][df['rain_1h'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "IH0035                          25687\n",
       "US0183                          10808\n",
       "SL0001                           8179\n",
       "US0290                           5060\n",
       "SH0071                           3447\n",
       "FM0734                           2785\n",
       "SL0343                           2585\n",
       "SL0360                           2390\n",
       "SL0275                           2382\n",
       "S 1ST ST                         2283\n",
       "N LAMAR BLVD                     2222\n",
       "E RIVERSIDE DR                   2199\n",
       "W SLAUGHTER LN                   1943\n",
       "RM2222                           1666\n",
       "S CONGRESS AVE                   1580\n",
       "GUADALUPE ST                     1448\n",
       "RM0620                           1357\n",
       "W WILLIAM CANNON DR              1330\n",
       "AIRPORT BLVD                     1294\n",
       "BURNET RD                        1221\n",
       "CAMERON RD                       1077\n",
       "W BRAKER LN                      1072\n",
       "MANCHACA RD                      1071\n",
       "SH0045                            984\n",
       "S PLEASANT VALLEY RD              967\n",
       "DESSAU RD                         966\n",
       "RESEARCH BLVD                     941\n",
       "E 7TH ST                          923\n",
       "FM0969                            900\n",
       "E WILLIAM CANNON DR               881\n",
       "ANDERSON MILL RD                  831\n",
       "E OLTORF ST                       799\n",
       "E STASSNEY LN                     784\n",
       "SL0111                            756\n",
       "FM1325                            717\n",
       "METRIC BLVD                       703\n",
       "FM2304                            699\n",
       "CONGRESS AVE S                    676\n",
       "BURLESON RD                       663\n",
       "E BEN WHITE BLVD                  635\n",
       "MANOR RD                          607\n",
       "W 6TH ST                          592\n",
       "SPRINGDALE RD                     575\n",
       "MONTOPOLIS DR                     565\n",
       "BRODIE LN                         561\n",
       "E 51ST ST                         551\n",
       "BARTON SPRINGS RD                 503\n",
       "E RUNDBERG LN                     492\n",
       "E MARTIN LUTHER KING JR BLVD      488\n",
       "MCNEIL DR                         484\n",
       "Name: street_name, dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['street_name'].value_counts().head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>crash_id</th>\n",
       "      <th>crash_fatal_fl</th>\n",
       "      <th>crash_date</th>\n",
       "      <th>crash_time</th>\n",
       "      <th>rpt_block_num</th>\n",
       "      <th>rpt_street_pfx</th>\n",
       "      <th>rpt_street_name</th>\n",
       "      <th>rpt_street_sfx</th>\n",
       "      <th>crash_speed_limit</th>\n",
       "      <th>road_constr_zone_fl</th>\n",
       "      <th>...</th>\n",
       "      <th>pressure</th>\n",
       "      <th>humidity</th>\n",
       "      <th>wind_speed</th>\n",
       "      <th>wind_deg</th>\n",
       "      <th>rain_1h</th>\n",
       "      <th>rain_3h</th>\n",
       "      <th>snow_1h</th>\n",
       "      <th>snow_3h</th>\n",
       "      <th>clouds_all</th>\n",
       "      <th>Weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>955</th>\n",
       "      <td>1002</td>\n",
       "      <td>Y</td>\n",
       "      <td>2018-07-15 00:00:00</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1018</td>\n",
       "      <td>70</td>\n",
       "      <td>5.75</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>968</th>\n",
       "      <td>1005</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-08-03 05:50:00</td>\n",
       "      <td>05:50:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1014</td>\n",
       "      <td>78</td>\n",
       "      <td>5.95</td>\n",
       "      <td>196</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1348</th>\n",
       "      <td>1006</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-08-15 22:57:00</td>\n",
       "      <td>22:57:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1012</td>\n",
       "      <td>32</td>\n",
       "      <td>4.70</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67203</th>\n",
       "      <td>14720068</td>\n",
       "      <td>Y</td>\n",
       "      <td>2014-03-13 00:30:00</td>\n",
       "      <td>00:30:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1026</td>\n",
       "      <td>28</td>\n",
       "      <td>4.61</td>\n",
       "      <td>350</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114329</th>\n",
       "      <td>180290542</td>\n",
       "      <td>Y</td>\n",
       "      <td>2018-01-29 12:18:00</td>\n",
       "      <td>12:18:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1034</td>\n",
       "      <td>24</td>\n",
       "      <td>6.93</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 61 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         crash_id crash_fatal_fl          crash_date crash_time rpt_block_num  \\\n",
       "955          1002              Y 2018-07-15 00:00:00   00:00:00           NaN   \n",
       "968          1005              Y 2020-08-03 05:50:00   05:50:00           NaN   \n",
       "1348         1006              Y 2020-08-15 22:57:00   22:57:00           NaN   \n",
       "67203    14720068              Y 2014-03-13 00:30:00   00:30:00           NaN   \n",
       "114329  180290542              Y 2018-01-29 12:18:00   12:18:00           NaN   \n",
       "\n",
       "       rpt_street_pfx rpt_street_name rpt_street_sfx  crash_speed_limit  \\\n",
       "955               NaN             NaN            NaN                NaN   \n",
       "968               NaN             NaN            NaN                NaN   \n",
       "1348              NaN             NaN            NaN                NaN   \n",
       "67203             NaN             NaN            NaN                NaN   \n",
       "114329            NaN             NaN            NaN                NaN   \n",
       "\n",
       "       road_constr_zone_fl  ...  pressure  humidity wind_speed  wind_deg  \\\n",
       "955                    NaN  ...      1018        70       5.75         0   \n",
       "968                    NaN  ...      1014        78       5.95       196   \n",
       "1348                   NaN  ...      1012        32       4.70         0   \n",
       "67203                  NaN  ...      1026        28       4.61       350   \n",
       "114329                 NaN  ...      1034        24       6.93        20   \n",
       "\n",
       "       rain_1h  rain_3h  snow_1h  snow_3h  clouds_all   Weekday  \n",
       "955        NaN      NaN      NaN      NaN           1    Sunday  \n",
       "968        NaN      NaN      NaN      NaN           1    Monday  \n",
       "1348       NaN      NaN      NaN      NaN          33  Saturday  \n",
       "67203      NaN      NaN      NaN      NaN           1  Thursday  \n",
       "114329     NaN      NaN      NaN      NaN           0    Monday  \n",
       "\n",
       "[5 rows x 61 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'street_name' column appears to be much more consistent in its formatting.\n",
    "df[df['street_name'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "rpt_block_num           21408\n",
       "rpt_street_pfx          71301\n",
       "rpt_street_name             1\n",
       "rpt_street_sfx          43117\n",
       "crash_speed_limit           0\n",
       "road_constr_zone_fl         0\n",
       "latitude                 5832\n",
       "longitude                5832\n",
       "street_name                 0\n",
       "street_nbr              94264\n",
       "street_name_2           91323\n",
       "crash_sev_id                0\n",
       "nonincap_injry_cnt          0\n",
       "poss_injry_cnt              0\n",
       "non_injry_cnt               0\n",
       "unkn_injry_cnt              0\n",
       "tot_injry_cnt               0\n",
       "contrib_factr_p1_id    130935\n",
       "contrib_factr_p2_id    155521\n",
       "pedestrian_fl          157430\n",
       "motor_vehicle_fl         1503\n",
       "motorcycle_fl          157310\n",
       "bicycle_fl             158696\n",
       "other_fl               156690\n",
       "point                    5832\n",
       "onsys_fl                    0\n",
       "private_dr_fl               0\n",
       "rain_1h                146920\n",
       "rain_3h                159633\n",
       "snow_1h                161660\n",
       "snow_3h                161805\n",
       "dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# very stange that almost all crash information is missing in these records but they're all listed as fatal.\n",
    "\n",
    "df[df['street_name'].notna()].isna().sum()[df.isna().sum()>0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "rpt_block_num           21413\n",
       "rpt_street_pfx          71306\n",
       "rpt_street_name             6\n",
       "rpt_street_sfx          43122\n",
       "crash_speed_limit           5\n",
       "road_constr_zone_fl         5\n",
       "latitude                 5832\n",
       "longitude                5832\n",
       "street_name                 5\n",
       "street_nbr              94269\n",
       "street_name_2           91328\n",
       "crash_sev_id                5\n",
       "nonincap_injry_cnt          3\n",
       "poss_injry_cnt              3\n",
       "non_injry_cnt               3\n",
       "unkn_injry_cnt              5\n",
       "tot_injry_cnt               4\n",
       "contrib_factr_p1_id    130940\n",
       "contrib_factr_p2_id    155526\n",
       "pedestrian_fl          157435\n",
       "motor_vehicle_fl         1505\n",
       "motorcycle_fl          157315\n",
       "bicycle_fl             158699\n",
       "other_fl               156694\n",
       "point                    5832\n",
       "onsys_fl                    5\n",
       "private_dr_fl               5\n",
       "rain_1h                146925\n",
       "rain_3h                159638\n",
       "snow_1h                161665\n",
       "snow_3h                161810\n",
       "dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()[df.isna().sum()>0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# will go ahead and drop rows missing 'street_name' as there are only 5 entries and they're also missing most useful\n",
    "# data.\n",
    "\n",
    "df = df[df['street_name'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32333    US0290\n",
       "Name: street_name, dtype: object"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['street_name'][df['rpt_street_name'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>utc_unix</th>\n",
       "      <th>dt_iso</th>\n",
       "      <th>timezone</th>\n",
       "      <th>city_name</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>...</th>\n",
       "      <th>sea_level</th>\n",
       "      <th>grnd_level</th>\n",
       "      <th>humidity</th>\n",
       "      <th>wind_speed</th>\n",
       "      <th>wind_deg</th>\n",
       "      <th>rain_1h</th>\n",
       "      <th>rain_3h</th>\n",
       "      <th>snow_1h</th>\n",
       "      <th>snow_3h</th>\n",
       "      <th>clouds_all</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>1199764800</td>\n",
       "      <td>2008-01-08 04:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>70.61</td>\n",
       "      <td>72.68</td>\n",
       "      <td>69.98</td>\n",
       "      <td>71.57</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87</td>\n",
       "      <td>6.93</td>\n",
       "      <td>160</td>\n",
       "      <td>0.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>1199797200</td>\n",
       "      <td>2008-01-08 13:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>69.12</td>\n",
       "      <td>69.44</td>\n",
       "      <td>68.23</td>\n",
       "      <td>70.36</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>86</td>\n",
       "      <td>8.61</td>\n",
       "      <td>228</td>\n",
       "      <td>0.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>1199808000</td>\n",
       "      <td>2008-01-08 16:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>68.05</td>\n",
       "      <td>66.60</td>\n",
       "      <td>67.09</td>\n",
       "      <td>69.14</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65</td>\n",
       "      <td>5.82</td>\n",
       "      <td>330</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>1200434400</td>\n",
       "      <td>2008-01-15 22:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>52.07</td>\n",
       "      <td>47.39</td>\n",
       "      <td>51.46</td>\n",
       "      <td>52.78</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56</td>\n",
       "      <td>3.36</td>\n",
       "      <td>230</td>\n",
       "      <td>0.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>1200438000</td>\n",
       "      <td>2008-01-15 23:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>49.77</td>\n",
       "      <td>45.81</td>\n",
       "      <td>48.02</td>\n",
       "      <td>52.07</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71</td>\n",
       "      <td>3.36</td>\n",
       "      <td>210</td>\n",
       "      <td>0.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115220</th>\n",
       "      <td>1602244800</td>\n",
       "      <td>2020-10-09 12:00:00 +0000 UTC</td>\n",
       "      <td>-18000</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>72.16</td>\n",
       "      <td>76.35</td>\n",
       "      <td>71.01</td>\n",
       "      <td>73.40</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88</td>\n",
       "      <td>4.70</td>\n",
       "      <td>0</td>\n",
       "      <td>3.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115414</th>\n",
       "      <td>1602943200</td>\n",
       "      <td>2020-10-17 14:00:00 +0000 UTC</td>\n",
       "      <td>-18000</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>60.31</td>\n",
       "      <td>59.16</td>\n",
       "      <td>57.99</td>\n",
       "      <td>62.55</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>82</td>\n",
       "      <td>4.70</td>\n",
       "      <td>0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115415</th>\n",
       "      <td>1602946800</td>\n",
       "      <td>2020-10-17 15:00:00 +0000 UTC</td>\n",
       "      <td>-18000</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>60.33</td>\n",
       "      <td>57.72</td>\n",
       "      <td>59.00</td>\n",
       "      <td>62.55</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87</td>\n",
       "      <td>8.23</td>\n",
       "      <td>159</td>\n",
       "      <td>0.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115444</th>\n",
       "      <td>1603047600</td>\n",
       "      <td>2020-10-18 19:00:00 +0000 UTC</td>\n",
       "      <td>-18000</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>87.19</td>\n",
       "      <td>86.67</td>\n",
       "      <td>84.00</td>\n",
       "      <td>90.00</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43</td>\n",
       "      <td>8.05</td>\n",
       "      <td>200</td>\n",
       "      <td>1.02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115445</th>\n",
       "      <td>1603051200</td>\n",
       "      <td>2020-10-18 20:00:00 +0000 UTC</td>\n",
       "      <td>-18000</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>88.79</td>\n",
       "      <td>87.03</td>\n",
       "      <td>87.01</td>\n",
       "      <td>91.00</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46</td>\n",
       "      <td>12.75</td>\n",
       "      <td>170</td>\n",
       "      <td>0.51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6991 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          utc_unix                         dt_iso  timezone city_name  \\\n",
       "172     1199764800  2008-01-08 04:00:00 +0000 UTC    -21600    Austin   \n",
       "182     1199797200  2008-01-08 13:00:00 +0000 UTC    -21600    Austin   \n",
       "185     1199808000  2008-01-08 16:00:00 +0000 UTC    -21600    Austin   \n",
       "359     1200434400  2008-01-15 22:00:00 +0000 UTC    -21600    Austin   \n",
       "360     1200438000  2008-01-15 23:00:00 +0000 UTC    -21600    Austin   \n",
       "...            ...                            ...       ...       ...   \n",
       "115220  1602244800  2020-10-09 12:00:00 +0000 UTC    -18000    Austin   \n",
       "115414  1602943200  2020-10-17 14:00:00 +0000 UTC    -18000    Austin   \n",
       "115415  1602946800  2020-10-17 15:00:00 +0000 UTC    -18000    Austin   \n",
       "115444  1603047600  2020-10-18 19:00:00 +0000 UTC    -18000    Austin   \n",
       "115445  1603051200  2020-10-18 20:00:00 +0000 UTC    -18000    Austin   \n",
       "\n",
       "              lat        lon   temp  feels_like  temp_min  temp_max  ...  \\\n",
       "172     30.267153 -97.743061  70.61       72.68     69.98     71.57  ...   \n",
       "182     30.267153 -97.743061  69.12       69.44     68.23     70.36  ...   \n",
       "185     30.267153 -97.743061  68.05       66.60     67.09     69.14  ...   \n",
       "359     30.267153 -97.743061  52.07       47.39     51.46     52.78  ...   \n",
       "360     30.267153 -97.743061  49.77       45.81     48.02     52.07  ...   \n",
       "...           ...        ...    ...         ...       ...       ...  ...   \n",
       "115220  30.267153 -97.743061  72.16       76.35     71.01     73.40  ...   \n",
       "115414  30.267153 -97.743061  60.31       59.16     57.99     62.55  ...   \n",
       "115415  30.267153 -97.743061  60.33       57.72     59.00     62.55  ...   \n",
       "115444  30.267153 -97.743061  87.19       86.67     84.00     90.00  ...   \n",
       "115445  30.267153 -97.743061  88.79       87.03     87.01     91.00  ...   \n",
       "\n",
       "        sea_level  grnd_level  humidity  wind_speed  wind_deg  rain_1h  \\\n",
       "172           NaN         NaN        87        6.93       160     0.30   \n",
       "182           NaN         NaN        86        8.61       228     0.50   \n",
       "185           NaN         NaN        65        5.82       330     1.00   \n",
       "359           NaN         NaN        56        3.36       230     0.50   \n",
       "360           NaN         NaN        71        3.36       210     0.50   \n",
       "...           ...         ...       ...         ...       ...      ...   \n",
       "115220        NaN         NaN        88        4.70         0     3.30   \n",
       "115414        NaN         NaN        82        4.70         0     0.50   \n",
       "115415        NaN         NaN        87        8.23       159     0.25   \n",
       "115444        NaN         NaN        43        8.05       200     1.02   \n",
       "115445        NaN         NaN        46       12.75       170     0.51   \n",
       "\n",
       "        rain_3h  snow_1h  snow_3h  clouds_all  \n",
       "172         NaN      NaN      NaN          90  \n",
       "182         NaN      NaN      NaN          75  \n",
       "185         NaN      NaN      NaN          40  \n",
       "359         NaN      NaN      NaN          90  \n",
       "360         NaN      NaN      NaN          75  \n",
       "...         ...      ...      ...         ...  \n",
       "115220      NaN      NaN      NaN          90  \n",
       "115414      NaN      NaN      NaN          90  \n",
       "115415      NaN      NaN      NaN          90  \n",
       "115444      NaN      NaN      NaN           1  \n",
       "115445      NaN      NaN      NaN          20  \n",
       "\n",
       "[6991 rows x 21 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# My hypothesis is that most of the missing snow/rain data actually represents 0.\n",
    "\n",
    "weather_data[weather_data['rain_1h'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>utc_unix</th>\n",
       "      <th>dt_iso</th>\n",
       "      <th>timezone</th>\n",
       "      <th>city_name</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>...</th>\n",
       "      <th>sea_level</th>\n",
       "      <th>grnd_level</th>\n",
       "      <th>humidity</th>\n",
       "      <th>wind_speed</th>\n",
       "      <th>wind_deg</th>\n",
       "      <th>rain_1h</th>\n",
       "      <th>rain_3h</th>\n",
       "      <th>snow_1h</th>\n",
       "      <th>snow_3h</th>\n",
       "      <th>clouds_all</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>1199790000</td>\n",
       "      <td>2008-01-08 11:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>70.30</td>\n",
       "      <td>71.53</td>\n",
       "      <td>69.12</td>\n",
       "      <td>71.06</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78</td>\n",
       "      <td>5.82</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>1199793600</td>\n",
       "      <td>2008-01-08 12:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>70.92</td>\n",
       "      <td>69.96</td>\n",
       "      <td>69.23</td>\n",
       "      <td>71.60</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78</td>\n",
       "      <td>10.09</td>\n",
       "      <td>208</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>1199797200</td>\n",
       "      <td>2008-01-08 13:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>69.12</td>\n",
       "      <td>69.44</td>\n",
       "      <td>68.23</td>\n",
       "      <td>70.36</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>86</td>\n",
       "      <td>8.61</td>\n",
       "      <td>228</td>\n",
       "      <td>0.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>1199800800</td>\n",
       "      <td>2008-01-08 14:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>68.63</td>\n",
       "      <td>69.51</td>\n",
       "      <td>68.00</td>\n",
       "      <td>69.47</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>83</td>\n",
       "      <td>6.51</td>\n",
       "      <td>253</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>1199804400</td>\n",
       "      <td>2008-01-08 15:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>70.03</td>\n",
       "      <td>71.06</td>\n",
       "      <td>67.77</td>\n",
       "      <td>71.06</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>81</td>\n",
       "      <td>6.76</td>\n",
       "      <td>287</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>1199808000</td>\n",
       "      <td>2008-01-08 16:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>68.05</td>\n",
       "      <td>66.60</td>\n",
       "      <td>67.09</td>\n",
       "      <td>69.14</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65</td>\n",
       "      <td>5.82</td>\n",
       "      <td>330</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>1199811600</td>\n",
       "      <td>2008-01-08 17:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>68.88</td>\n",
       "      <td>62.85</td>\n",
       "      <td>68.00</td>\n",
       "      <td>70.61</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40</td>\n",
       "      <td>8.05</td>\n",
       "      <td>330</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>1199815200</td>\n",
       "      <td>2008-01-08 18:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>69.91</td>\n",
       "      <td>61.83</td>\n",
       "      <td>69.64</td>\n",
       "      <td>69.98</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29</td>\n",
       "      <td>9.17</td>\n",
       "      <td>330</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>1199818800</td>\n",
       "      <td>2008-01-08 19:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>70.65</td>\n",
       "      <td>59.27</td>\n",
       "      <td>69.98</td>\n",
       "      <td>71.68</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24</td>\n",
       "      <td>13.87</td>\n",
       "      <td>330</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>1199822400</td>\n",
       "      <td>2008-01-08 20:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>71.02</td>\n",
       "      <td>60.93</td>\n",
       "      <td>69.98</td>\n",
       "      <td>72.85</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23</td>\n",
       "      <td>11.41</td>\n",
       "      <td>360</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>1199826000</td>\n",
       "      <td>2008-01-08 21:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>70.75</td>\n",
       "      <td>58.15</td>\n",
       "      <td>69.98</td>\n",
       "      <td>72.28</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "      <td>14.99</td>\n",
       "      <td>360</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       utc_unix                         dt_iso  timezone city_name        lat  \\\n",
       "180  1199790000  2008-01-08 11:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "181  1199793600  2008-01-08 12:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "182  1199797200  2008-01-08 13:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "183  1199800800  2008-01-08 14:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "184  1199804400  2008-01-08 15:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "185  1199808000  2008-01-08 16:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "186  1199811600  2008-01-08 17:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "187  1199815200  2008-01-08 18:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "188  1199818800  2008-01-08 19:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "189  1199822400  2008-01-08 20:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "190  1199826000  2008-01-08 21:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "\n",
       "           lon   temp  feels_like  temp_min  temp_max  ...  sea_level  \\\n",
       "180 -97.743061  70.30       71.53     69.12     71.06  ...        NaN   \n",
       "181 -97.743061  70.92       69.96     69.23     71.60  ...        NaN   \n",
       "182 -97.743061  69.12       69.44     68.23     70.36  ...        NaN   \n",
       "183 -97.743061  68.63       69.51     68.00     69.47  ...        NaN   \n",
       "184 -97.743061  70.03       71.06     67.77     71.06  ...        NaN   \n",
       "185 -97.743061  68.05       66.60     67.09     69.14  ...        NaN   \n",
       "186 -97.743061  68.88       62.85     68.00     70.61  ...        NaN   \n",
       "187 -97.743061  69.91       61.83     69.64     69.98  ...        NaN   \n",
       "188 -97.743061  70.65       59.27     69.98     71.68  ...        NaN   \n",
       "189 -97.743061  71.02       60.93     69.98     72.85  ...        NaN   \n",
       "190 -97.743061  70.75       58.15     69.98     72.28  ...        NaN   \n",
       "\n",
       "     grnd_level  humidity  wind_speed  wind_deg  rain_1h  rain_3h  snow_1h  \\\n",
       "180         NaN        78        5.82         0      NaN      NaN      NaN   \n",
       "181         NaN        78       10.09       208      NaN      NaN      NaN   \n",
       "182         NaN        86        8.61       228      0.5      NaN      NaN   \n",
       "183         NaN        83        6.51       253      NaN      NaN      NaN   \n",
       "184         NaN        81        6.76       287      NaN      0.8      NaN   \n",
       "185         NaN        65        5.82       330      1.0      NaN      NaN   \n",
       "186         NaN        40        8.05       330      NaN      NaN      NaN   \n",
       "187         NaN        29        9.17       330      NaN      NaN      NaN   \n",
       "188         NaN        24       13.87       330      NaN      NaN      NaN   \n",
       "189         NaN        23       11.41       360      NaN      NaN      NaN   \n",
       "190         NaN        20       14.99       360      NaN      NaN      NaN   \n",
       "\n",
       "     snow_3h  clouds_all  \n",
       "180      NaN          90  \n",
       "181      NaN          90  \n",
       "182      NaN          75  \n",
       "183      NaN          40  \n",
       "184      NaN          90  \n",
       "185      NaN          40  \n",
       "186      NaN           1  \n",
       "187      NaN          90  \n",
       "188      NaN          90  \n",
       "189      NaN          90  \n",
       "190      NaN          75  \n",
       "\n",
       "[11 rows x 21 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Will check the values 3 hours back and forward from an entry where 'rain_1h' is not null. Hoping to see how \n",
    "# 'rain_3h' correlates with these values.\n",
    "\n",
    "weather_data.loc[180:190, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Confused what 'rain_3h' actually represents because in the multiple instances I checked it doesn't correlate\n",
    "# with 'rain_1h'. Will probably just have to lean more heavily on 'rain_1h' in modeling. Can pretty safely\n",
    "# assume that NaN in snow values correspond with zeros though. Will go ahead and replace all with zeroes and be\n",
    "# cognizant of this choice should anything strange come up in EDA.\n",
    "\n",
    "zero_map= {'rain_1h': 0, 'rain_3h': 0, 'snow_1h': 0, 'snow_3h': 0}\n",
    "\n",
    "df= df.fillna(zero_map)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "rpt_block_num           21408\n",
       "rpt_street_pfx          71301\n",
       "rpt_street_name             1\n",
       "rpt_street_sfx          43117\n",
       "latitude                 5832\n",
       "longitude                5832\n",
       "street_nbr              94264\n",
       "street_name_2           91323\n",
       "contrib_factr_p1_id    130935\n",
       "contrib_factr_p2_id    155521\n",
       "pedestrian_fl          157430\n",
       "motor_vehicle_fl         1503\n",
       "motorcycle_fl          157310\n",
       "bicycle_fl             158696\n",
       "other_fl               156690\n",
       "point                    5832\n",
       "dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()[df.isna().sum()>0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# will drop 'point' column as it is redundant information with 'latitude' and 'longitude'\n",
    "df = df.drop('point', axis= 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "rpt_block_num           21408\n",
       "rpt_street_pfx          71301\n",
       "rpt_street_name             1\n",
       "rpt_street_sfx          43117\n",
       "latitude                 5832\n",
       "longitude                5832\n",
       "street_nbr              94264\n",
       "street_name_2           91323\n",
       "contrib_factr_p1_id    130935\n",
       "contrib_factr_p2_id    155521\n",
       "pedestrian_fl          157430\n",
       "motor_vehicle_fl         1503\n",
       "motorcycle_fl          157310\n",
       "bicycle_fl             158696\n",
       "other_fl               156690\n",
       "dtype: int64"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()[df.isna().sum()>0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "# the last thing I would like to do is retain information regarding all of the moments where a crash was not\n",
    "# happening. \n",
    "\n",
    "hourly_crash_count = crash_data['utc_unix'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "hourly_crash_count = pd.DataFrame(hourly_crash_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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>utc_unix</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1368133200</th>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1304373600</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1562626800</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1389225600</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1484697600</th>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            utc_unix\n",
       "1368133200        20\n",
       "1304373600        17\n",
       "1562626800        15\n",
       "1389225600        15\n",
       "1484697600        14"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hourly_crash_count.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "hourly_crash_count.reset_index(inplace=True)\n",
    "hourly_crash_count = hourly_crash_count.rename(columns = {'utc_unix': 'count','index':'utc_unix'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "hourly_df = pd.merge(weather_data, hourly_crash_count, how= 'left', on = ['utc_unix'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['utc_unix', 'dt_iso', 'timezone', 'city_name', 'lat', 'lon', 'temp',\n",
       "       'feels_like', 'temp_min', 'temp_max', 'pressure', 'sea_level',\n",
       "       'grnd_level', 'humidity', 'wind_speed', 'wind_deg', 'rain_1h',\n",
       "       'rain_3h', 'snow_1h', 'snow_3h', 'clouds_all', 'count'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hourly_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "43559"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hourly_df['count'].isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "hourly_df = hourly_df.fillna({'count':0})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0     43559\n",
       "1.0     25325\n",
       "2.0     18522\n",
       "3.0     11837\n",
       "4.0      6550\n",
       "5.0      3334\n",
       "6.0      1646\n",
       "7.0       773\n",
       "8.0       364\n",
       "9.0       156\n",
       "10.0       72\n",
       "11.0       30\n",
       "12.0       14\n",
       "13.0       10\n",
       "14.0        4\n",
       "15.0        2\n",
       "17.0        1\n",
       "20.0        1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hourly_df['count'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1262325600\n",
      "1598932800\n"
     ]
    }
   ],
   "source": [
    "print(hourly_df['utc_unix'][hourly_df['count'] > 0].min())\n",
    "print(hourly_df['utc_unix'][hourly_df['count'] > 0].max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "# need to trim dates before first crash on record and after last since we don't have crash data for that time.\n",
    "first = hourly_df['utc_unix'][hourly_df['count'] > 0].min()\n",
    "last = hourly_df['utc_unix'][hourly_df['count'] > 0].max()\n",
    "\n",
    "hourly_df = hourly_df[(hourly_df['utc_unix'] >= first) & (hourly_df['utc_unix'] <= last)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0     25325\n",
       "0.0     24862\n",
       "2.0     18522\n",
       "3.0     11837\n",
       "4.0      6550\n",
       "5.0      3334\n",
       "6.0      1646\n",
       "7.0       773\n",
       "8.0       364\n",
       "9.0       156\n",
       "10.0       72\n",
       "11.0       30\n",
       "12.0       14\n",
       "13.0       10\n",
       "14.0        4\n",
       "15.0        2\n",
       "17.0        1\n",
       "20.0        1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hourly_df['count'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "hourly_df= hourly_df.fillna(zero_map)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sea_level     93503\n",
       "grnd_level    93503\n",
       "dtype: int64"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hourly_df.isna().sum()[hourly_df.isna().sum()>0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "hourly_df = hourly_df.drop(['sea_level', 'grnd_level'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 93503 entries, 17550 to 111052\n",
      "Data columns (total 20 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   utc_unix    93503 non-null  int64  \n",
      " 1   dt_iso      93503 non-null  object \n",
      " 2   timezone    93503 non-null  int64  \n",
      " 3   city_name   93503 non-null  object \n",
      " 4   lat         93503 non-null  float64\n",
      " 5   lon         93503 non-null  float64\n",
      " 6   temp        93503 non-null  float64\n",
      " 7   feels_like  93503 non-null  float64\n",
      " 8   temp_min    93503 non-null  float64\n",
      " 9   temp_max    93503 non-null  float64\n",
      " 10  pressure    93503 non-null  int64  \n",
      " 11  humidity    93503 non-null  int64  \n",
      " 12  wind_speed  93503 non-null  float64\n",
      " 13  wind_deg    93503 non-null  int64  \n",
      " 14  rain_1h     93503 non-null  float64\n",
      " 15  rain_3h     93503 non-null  float64\n",
      " 16  snow_1h     93503 non-null  float64\n",
      " 17  snow_3h     93503 non-null  float64\n",
      " 18  clouds_all  93503 non-null  int64  \n",
      " 19  count       93503 non-null  float64\n",
      "dtypes: float64(12), int64(6), object(2)\n",
      "memory usage: 15.0+ MB\n"
     ]
    }
   ],
   "source": [
    "hourly_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "hourly_df['LocalTime'] = hourly_df['utc_unix'] + hourly_df['timezone']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "def time_from_unix(time):\n",
    "    return datetime.utcfromtimestamp(time).strftime('%Y-%m-%d %H:%M:%S')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "hourly_df['LocalTime'] = [time_from_unix(time) for time in hourly_df['LocalTime']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17550    2010-01-01 00:00:00\n",
       "17551    2010-01-01 01:00:00\n",
       "17552    2010-01-01 02:00:00\n",
       "17553    2010-01-01 03:00:00\n",
       "17554    2010-01-01 04:00:00\n",
       "Name: LocalTime, dtype: object"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hourly_df['LocalTime'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2014-11-02 01:00:00    2\n",
       "2012-11-04 01:00:00    2\n",
       "2010-11-07 01:00:00    2\n",
       "2016-11-06 01:00:00    2\n",
       "2013-11-03 01:00:00    2\n",
       "                      ..\n",
       "2017-02-04 06:00:00    1\n",
       "2010-03-16 03:00:00    1\n",
       "2020-01-23 00:00:00    1\n",
       "2016-10-16 00:00:00    1\n",
       "2017-03-30 03:00:00    1\n",
       "Name: LocalTime, Length: 93493, dtype: int64"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hourly_df['LocalTime'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 93503 entries, 17550 to 111052\n",
      "Data columns (total 21 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   utc_unix    93503 non-null  int64  \n",
      " 1   dt_iso      93503 non-null  object \n",
      " 2   timezone    93503 non-null  int64  \n",
      " 3   city_name   93503 non-null  object \n",
      " 4   lat         93503 non-null  float64\n",
      " 5   lon         93503 non-null  float64\n",
      " 6   temp        93503 non-null  float64\n",
      " 7   feels_like  93503 non-null  float64\n",
      " 8   temp_min    93503 non-null  float64\n",
      " 9   temp_max    93503 non-null  float64\n",
      " 10  pressure    93503 non-null  int64  \n",
      " 11  humidity    93503 non-null  int64  \n",
      " 12  wind_speed  93503 non-null  float64\n",
      " 13  wind_deg    93503 non-null  int64  \n",
      " 14  rain_1h     93503 non-null  float64\n",
      " 15  rain_3h     93503 non-null  float64\n",
      " 16  snow_1h     93503 non-null  float64\n",
      " 17  snow_3h     93503 non-null  float64\n",
      " 18  clouds_all  93503 non-null  int64  \n",
      " 19  count       93503 non-null  float64\n",
      " 20  LocalTime   93503 non-null  object \n",
      "dtypes: float64(12), int64(6), object(3)\n",
      "memory usage: 18.2+ MB\n"
     ]
    }
   ],
   "source": [
    "hourly_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'Timestamp' object has no attribute 'dt'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-100-ed5c199193cb>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0mhourly_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'LocalTime'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_datetime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtime\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mtime\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mhourly_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'LocalTime'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mhourly_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Weekday'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mget_weekday\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdt\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mtime\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mhourly_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'LocalTime'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m<ipython-input-100-ed5c199193cb>\u001b[0m in \u001b[0;36m<listcomp>\u001b[0;34m(.0)\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0mhourly_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'LocalTime'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_datetime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtime\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mtime\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mhourly_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'LocalTime'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mhourly_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Weekday'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mget_weekday\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdt\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mtime\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mhourly_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'LocalTime'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m: 'Timestamp' object has no attribute 'dt'"
     ]
    }
   ],
   "source": [
    "hourly_df['LocalTime'] = [pd.to_datetime(time) for time in hourly_df['LocalTime']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "hourly_df['Weekday'] = [get_weekday(time) for time in hourly_df['LocalTime']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "hourly_df['Weekday'] = hourly_df['Weekday'].replace(weekdays)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "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>utc_unix</th>\n",
       "      <th>dt_iso</th>\n",
       "      <th>timezone</th>\n",
       "      <th>city_name</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>...</th>\n",
       "      <th>wind_speed</th>\n",
       "      <th>wind_deg</th>\n",
       "      <th>rain_1h</th>\n",
       "      <th>rain_3h</th>\n",
       "      <th>snow_1h</th>\n",
       "      <th>snow_3h</th>\n",
       "      <th>clouds_all</th>\n",
       "      <th>count</th>\n",
       "      <th>LocalTime</th>\n",
       "      <th>Weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17550</th>\n",
       "      <td>1262325600</td>\n",
       "      <td>2010-01-01 06:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>42.49</td>\n",
       "      <td>35.33</td>\n",
       "      <td>41.00</td>\n",
       "      <td>44.49</td>\n",
       "      <td>...</td>\n",
       "      <td>6.93</td>\n",
       "      <td>330</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>90</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2010-01-01 00:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17551</th>\n",
       "      <td>1262329200</td>\n",
       "      <td>2010-01-01 07:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>42.53</td>\n",
       "      <td>36.01</td>\n",
       "      <td>41.00</td>\n",
       "      <td>44.75</td>\n",
       "      <td>...</td>\n",
       "      <td>5.82</td>\n",
       "      <td>340</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>90</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2010-01-01 01:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17552</th>\n",
       "      <td>1262332800</td>\n",
       "      <td>2010-01-01 08:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>40.69</td>\n",
       "      <td>31.86</td>\n",
       "      <td>39.20</td>\n",
       "      <td>43.77</td>\n",
       "      <td>...</td>\n",
       "      <td>9.17</td>\n",
       "      <td>320</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2010-01-01 02:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17553</th>\n",
       "      <td>1262336400</td>\n",
       "      <td>2010-01-01 09:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>38.52</td>\n",
       "      <td>31.28</td>\n",
       "      <td>37.40</td>\n",
       "      <td>40.90</td>\n",
       "      <td>...</td>\n",
       "      <td>5.82</td>\n",
       "      <td>330</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2010-01-01 03:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17554</th>\n",
       "      <td>1262340000</td>\n",
       "      <td>2010-01-01 10:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>36.30</td>\n",
       "      <td>27.30</td>\n",
       "      <td>35.60</td>\n",
       "      <td>37.21</td>\n",
       "      <td>...</td>\n",
       "      <td>8.93</td>\n",
       "      <td>350</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2010-01-01 04:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17555</th>\n",
       "      <td>1262343600</td>\n",
       "      <td>2010-01-01 11:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>36.32</td>\n",
       "      <td>28.45</td>\n",
       "      <td>35.60</td>\n",
       "      <td>37.04</td>\n",
       "      <td>...</td>\n",
       "      <td>6.93</td>\n",
       "      <td>300</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2010-01-01 05:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17556</th>\n",
       "      <td>1262347200</td>\n",
       "      <td>2010-01-01 12:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>35.89</td>\n",
       "      <td>26.83</td>\n",
       "      <td>35.29</td>\n",
       "      <td>36.36</td>\n",
       "      <td>...</td>\n",
       "      <td>9.17</td>\n",
       "      <td>330</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-01 06:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17557</th>\n",
       "      <td>1262350800</td>\n",
       "      <td>2010-01-01 13:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>35.26</td>\n",
       "      <td>25.36</td>\n",
       "      <td>33.80</td>\n",
       "      <td>35.96</td>\n",
       "      <td>...</td>\n",
       "      <td>10.29</td>\n",
       "      <td>330</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-01 07:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17558</th>\n",
       "      <td>1262354400</td>\n",
       "      <td>2010-01-01 14:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>35.37</td>\n",
       "      <td>26.87</td>\n",
       "      <td>35.06</td>\n",
       "      <td>36.09</td>\n",
       "      <td>...</td>\n",
       "      <td>8.05</td>\n",
       "      <td>330</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-01 08:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17559</th>\n",
       "      <td>1262358000</td>\n",
       "      <td>2010-01-01 15:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>40.15</td>\n",
       "      <td>32.11</td>\n",
       "      <td>38.80</td>\n",
       "      <td>41.00</td>\n",
       "      <td>...</td>\n",
       "      <td>6.93</td>\n",
       "      <td>340</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-01 09:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17560</th>\n",
       "      <td>1262361600</td>\n",
       "      <td>2010-01-01 16:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>44.67</td>\n",
       "      <td>37.06</td>\n",
       "      <td>43.60</td>\n",
       "      <td>44.96</td>\n",
       "      <td>...</td>\n",
       "      <td>5.82</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-01 10:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17561</th>\n",
       "      <td>1262365200</td>\n",
       "      <td>2010-01-01 17:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>48.25</td>\n",
       "      <td>39.88</td>\n",
       "      <td>47.35</td>\n",
       "      <td>48.92</td>\n",
       "      <td>...</td>\n",
       "      <td>6.93</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-01 11:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17562</th>\n",
       "      <td>1262368800</td>\n",
       "      <td>2010-01-01 18:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>50.25</td>\n",
       "      <td>41.63</td>\n",
       "      <td>46.40</td>\n",
       "      <td>51.98</td>\n",
       "      <td>...</td>\n",
       "      <td>6.58</td>\n",
       "      <td>27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2010-01-01 12:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17563</th>\n",
       "      <td>1262372400</td>\n",
       "      <td>2010-01-01 19:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>51.64</td>\n",
       "      <td>44.04</td>\n",
       "      <td>50.00</td>\n",
       "      <td>53.06</td>\n",
       "      <td>...</td>\n",
       "      <td>4.70</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-01 13:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17564</th>\n",
       "      <td>1262376000</td>\n",
       "      <td>2010-01-01 20:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>53.33</td>\n",
       "      <td>45.88</td>\n",
       "      <td>52.54</td>\n",
       "      <td>53.96</td>\n",
       "      <td>...</td>\n",
       "      <td>4.70</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-01 14:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17565</th>\n",
       "      <td>1262379600</td>\n",
       "      <td>2010-01-01 21:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>54.03</td>\n",
       "      <td>47.39</td>\n",
       "      <td>53.84</td>\n",
       "      <td>54.55</td>\n",
       "      <td>...</td>\n",
       "      <td>3.36</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-01 15:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17566</th>\n",
       "      <td>1262383200</td>\n",
       "      <td>2010-01-01 22:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>52.63</td>\n",
       "      <td>44.47</td>\n",
       "      <td>51.06</td>\n",
       "      <td>53.96</td>\n",
       "      <td>...</td>\n",
       "      <td>5.82</td>\n",
       "      <td>100</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2010-01-01 16:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17567</th>\n",
       "      <td>1262386800</td>\n",
       "      <td>2010-01-01 23:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>52.09</td>\n",
       "      <td>45.91</td>\n",
       "      <td>50.00</td>\n",
       "      <td>53.96</td>\n",
       "      <td>...</td>\n",
       "      <td>2.08</td>\n",
       "      <td>119</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-01 17:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17568</th>\n",
       "      <td>1262390400</td>\n",
       "      <td>2010-01-02 00:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>48.11</td>\n",
       "      <td>40.28</td>\n",
       "      <td>46.04</td>\n",
       "      <td>51.32</td>\n",
       "      <td>...</td>\n",
       "      <td>5.46</td>\n",
       "      <td>125</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-01 18:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17569</th>\n",
       "      <td>1262394000</td>\n",
       "      <td>2010-01-02 01:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>43.81</td>\n",
       "      <td>36.39</td>\n",
       "      <td>39.02</td>\n",
       "      <td>48.20</td>\n",
       "      <td>...</td>\n",
       "      <td>5.50</td>\n",
       "      <td>134</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-01 19:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17570</th>\n",
       "      <td>1262397600</td>\n",
       "      <td>2010-01-02 02:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>43.05</td>\n",
       "      <td>35.58</td>\n",
       "      <td>42.08</td>\n",
       "      <td>44.60</td>\n",
       "      <td>...</td>\n",
       "      <td>5.86</td>\n",
       "      <td>140</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-01 20:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17571</th>\n",
       "      <td>1262401200</td>\n",
       "      <td>2010-01-02 03:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>40.12</td>\n",
       "      <td>32.04</td>\n",
       "      <td>37.91</td>\n",
       "      <td>41.00</td>\n",
       "      <td>...</td>\n",
       "      <td>6.53</td>\n",
       "      <td>126</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2010-01-01 21:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17572</th>\n",
       "      <td>1262404800</td>\n",
       "      <td>2010-01-02 04:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>38.82</td>\n",
       "      <td>30.97</td>\n",
       "      <td>37.63</td>\n",
       "      <td>39.20</td>\n",
       "      <td>...</td>\n",
       "      <td>6.31</td>\n",
       "      <td>116</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2010-01-01 22:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17573</th>\n",
       "      <td>1262408400</td>\n",
       "      <td>2010-01-02 05:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>36.70</td>\n",
       "      <td>28.96</td>\n",
       "      <td>34.13</td>\n",
       "      <td>37.94</td>\n",
       "      <td>...</td>\n",
       "      <td>6.15</td>\n",
       "      <td>134</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2010-01-01 23:00:00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17574</th>\n",
       "      <td>1262412000</td>\n",
       "      <td>2010-01-02 06:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>35.83</td>\n",
       "      <td>28.22</td>\n",
       "      <td>33.71</td>\n",
       "      <td>37.40</td>\n",
       "      <td>...</td>\n",
       "      <td>6.15</td>\n",
       "      <td>157</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-02 00:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17575</th>\n",
       "      <td>1262415600</td>\n",
       "      <td>2010-01-02 07:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>34.56</td>\n",
       "      <td>27.59</td>\n",
       "      <td>32.70</td>\n",
       "      <td>37.40</td>\n",
       "      <td>...</td>\n",
       "      <td>5.37</td>\n",
       "      <td>177</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-02 01:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17576</th>\n",
       "      <td>1262419200</td>\n",
       "      <td>2010-01-02 08:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>34.52</td>\n",
       "      <td>28.35</td>\n",
       "      <td>32.43</td>\n",
       "      <td>35.60</td>\n",
       "      <td>...</td>\n",
       "      <td>3.74</td>\n",
       "      <td>198</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2010-01-02 02:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17577</th>\n",
       "      <td>1262422800</td>\n",
       "      <td>2010-01-02 09:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>33.89</td>\n",
       "      <td>28.47</td>\n",
       "      <td>31.79</td>\n",
       "      <td>35.81</td>\n",
       "      <td>...</td>\n",
       "      <td>2.75</td>\n",
       "      <td>210</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2010-01-02 03:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17578</th>\n",
       "      <td>1262426400</td>\n",
       "      <td>2010-01-02 10:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>31.62</td>\n",
       "      <td>25.63</td>\n",
       "      <td>26.96</td>\n",
       "      <td>35.60</td>\n",
       "      <td>...</td>\n",
       "      <td>3.24</td>\n",
       "      <td>182</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2010-01-02 04:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17579</th>\n",
       "      <td>1262430000</td>\n",
       "      <td>2010-01-02 11:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>30.33</td>\n",
       "      <td>24.06</td>\n",
       "      <td>26.06</td>\n",
       "      <td>34.85</td>\n",
       "      <td>...</td>\n",
       "      <td>3.44</td>\n",
       "      <td>179</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010-01-02 05:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>30 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         utc_unix                         dt_iso  timezone city_name  \\\n",
       "17550  1262325600  2010-01-01 06:00:00 +0000 UTC    -21600    Austin   \n",
       "17551  1262329200  2010-01-01 07:00:00 +0000 UTC    -21600    Austin   \n",
       "17552  1262332800  2010-01-01 08:00:00 +0000 UTC    -21600    Austin   \n",
       "17553  1262336400  2010-01-01 09:00:00 +0000 UTC    -21600    Austin   \n",
       "17554  1262340000  2010-01-01 10:00:00 +0000 UTC    -21600    Austin   \n",
       "17555  1262343600  2010-01-01 11:00:00 +0000 UTC    -21600    Austin   \n",
       "17556  1262347200  2010-01-01 12:00:00 +0000 UTC    -21600    Austin   \n",
       "17557  1262350800  2010-01-01 13:00:00 +0000 UTC    -21600    Austin   \n",
       "17558  1262354400  2010-01-01 14:00:00 +0000 UTC    -21600    Austin   \n",
       "17559  1262358000  2010-01-01 15:00:00 +0000 UTC    -21600    Austin   \n",
       "17560  1262361600  2010-01-01 16:00:00 +0000 UTC    -21600    Austin   \n",
       "17561  1262365200  2010-01-01 17:00:00 +0000 UTC    -21600    Austin   \n",
       "17562  1262368800  2010-01-01 18:00:00 +0000 UTC    -21600    Austin   \n",
       "17563  1262372400  2010-01-01 19:00:00 +0000 UTC    -21600    Austin   \n",
       "17564  1262376000  2010-01-01 20:00:00 +0000 UTC    -21600    Austin   \n",
       "17565  1262379600  2010-01-01 21:00:00 +0000 UTC    -21600    Austin   \n",
       "17566  1262383200  2010-01-01 22:00:00 +0000 UTC    -21600    Austin   \n",
       "17567  1262386800  2010-01-01 23:00:00 +0000 UTC    -21600    Austin   \n",
       "17568  1262390400  2010-01-02 00:00:00 +0000 UTC    -21600    Austin   \n",
       "17569  1262394000  2010-01-02 01:00:00 +0000 UTC    -21600    Austin   \n",
       "17570  1262397600  2010-01-02 02:00:00 +0000 UTC    -21600    Austin   \n",
       "17571  1262401200  2010-01-02 03:00:00 +0000 UTC    -21600    Austin   \n",
       "17572  1262404800  2010-01-02 04:00:00 +0000 UTC    -21600    Austin   \n",
       "17573  1262408400  2010-01-02 05:00:00 +0000 UTC    -21600    Austin   \n",
       "17574  1262412000  2010-01-02 06:00:00 +0000 UTC    -21600    Austin   \n",
       "17575  1262415600  2010-01-02 07:00:00 +0000 UTC    -21600    Austin   \n",
       "17576  1262419200  2010-01-02 08:00:00 +0000 UTC    -21600    Austin   \n",
       "17577  1262422800  2010-01-02 09:00:00 +0000 UTC    -21600    Austin   \n",
       "17578  1262426400  2010-01-02 10:00:00 +0000 UTC    -21600    Austin   \n",
       "17579  1262430000  2010-01-02 11:00:00 +0000 UTC    -21600    Austin   \n",
       "\n",
       "             lat        lon   temp  feels_like  temp_min  temp_max  ...  \\\n",
       "17550  30.267153 -97.743061  42.49       35.33     41.00     44.49  ...   \n",
       "17551  30.267153 -97.743061  42.53       36.01     41.00     44.75  ...   \n",
       "17552  30.267153 -97.743061  40.69       31.86     39.20     43.77  ...   \n",
       "17553  30.267153 -97.743061  38.52       31.28     37.40     40.90  ...   \n",
       "17554  30.267153 -97.743061  36.30       27.30     35.60     37.21  ...   \n",
       "17555  30.267153 -97.743061  36.32       28.45     35.60     37.04  ...   \n",
       "17556  30.267153 -97.743061  35.89       26.83     35.29     36.36  ...   \n",
       "17557  30.267153 -97.743061  35.26       25.36     33.80     35.96  ...   \n",
       "17558  30.267153 -97.743061  35.37       26.87     35.06     36.09  ...   \n",
       "17559  30.267153 -97.743061  40.15       32.11     38.80     41.00  ...   \n",
       "17560  30.267153 -97.743061  44.67       37.06     43.60     44.96  ...   \n",
       "17561  30.267153 -97.743061  48.25       39.88     47.35     48.92  ...   \n",
       "17562  30.267153 -97.743061  50.25       41.63     46.40     51.98  ...   \n",
       "17563  30.267153 -97.743061  51.64       44.04     50.00     53.06  ...   \n",
       "17564  30.267153 -97.743061  53.33       45.88     52.54     53.96  ...   \n",
       "17565  30.267153 -97.743061  54.03       47.39     53.84     54.55  ...   \n",
       "17566  30.267153 -97.743061  52.63       44.47     51.06     53.96  ...   \n",
       "17567  30.267153 -97.743061  52.09       45.91     50.00     53.96  ...   \n",
       "17568  30.267153 -97.743061  48.11       40.28     46.04     51.32  ...   \n",
       "17569  30.267153 -97.743061  43.81       36.39     39.02     48.20  ...   \n",
       "17570  30.267153 -97.743061  43.05       35.58     42.08     44.60  ...   \n",
       "17571  30.267153 -97.743061  40.12       32.04     37.91     41.00  ...   \n",
       "17572  30.267153 -97.743061  38.82       30.97     37.63     39.20  ...   \n",
       "17573  30.267153 -97.743061  36.70       28.96     34.13     37.94  ...   \n",
       "17574  30.267153 -97.743061  35.83       28.22     33.71     37.40  ...   \n",
       "17575  30.267153 -97.743061  34.56       27.59     32.70     37.40  ...   \n",
       "17576  30.267153 -97.743061  34.52       28.35     32.43     35.60  ...   \n",
       "17577  30.267153 -97.743061  33.89       28.47     31.79     35.81  ...   \n",
       "17578  30.267153 -97.743061  31.62       25.63     26.96     35.60  ...   \n",
       "17579  30.267153 -97.743061  30.33       24.06     26.06     34.85  ...   \n",
       "\n",
       "       wind_speed  wind_deg  rain_1h  rain_3h  snow_1h  snow_3h  clouds_all  \\\n",
       "17550        6.93       330      0.0      0.0      0.0      0.0          90   \n",
       "17551        5.82       340      0.0      0.0      0.0      0.0          90   \n",
       "17552        9.17       320      0.0      0.0      0.0      0.0           1   \n",
       "17553        5.82       330      0.0      0.0      0.0      0.0           1   \n",
       "17554        8.93       350      0.0      0.0      0.0      0.0           1   \n",
       "17555        6.93       300      0.0      0.0      0.0      0.0           1   \n",
       "17556        9.17       330      0.0      0.0      0.0      0.0           1   \n",
       "17557       10.29       330      0.0      0.0      0.0      0.0           1   \n",
       "17558        8.05       330      0.0      0.0      0.0      0.0           1   \n",
       "17559        6.93       340      0.0      0.0      0.0      0.0           1   \n",
       "17560        5.82         0      0.0      0.0      0.0      0.0           1   \n",
       "17561        6.93         0      0.0      0.0      0.0      0.0           1   \n",
       "17562        6.58        27      0.0      0.0      0.0      0.0           1   \n",
       "17563        4.70         0      0.0      0.0      0.0      0.0           1   \n",
       "17564        4.70         0      0.0      0.0      0.0      0.0           1   \n",
       "17565        3.36         0      0.0      0.0      0.0      0.0           1   \n",
       "17566        5.82       100      0.0      0.0      0.0      0.0           1   \n",
       "17567        2.08       119      0.0      0.0      0.0      0.0           1   \n",
       "17568        5.46       125      0.0      0.0      0.0      0.0           1   \n",
       "17569        5.50       134      0.0      0.0      0.0      0.0           1   \n",
       "17570        5.86       140      0.0      0.0      0.0      0.0           1   \n",
       "17571        6.53       126      0.0      0.0      0.0      0.0           1   \n",
       "17572        6.31       116      0.0      0.0      0.0      0.0           1   \n",
       "17573        6.15       134      0.0      0.0      0.0      0.0           1   \n",
       "17574        6.15       157      0.0      0.0      0.0      0.0           1   \n",
       "17575        5.37       177      0.0      0.0      0.0      0.0           1   \n",
       "17576        3.74       198      0.0      0.0      0.0      0.0           1   \n",
       "17577        2.75       210      0.0      0.0      0.0      0.0           1   \n",
       "17578        3.24       182      0.0      0.0      0.0      0.0           1   \n",
       "17579        3.44       179      0.0      0.0      0.0      0.0           1   \n",
       "\n",
       "       count           LocalTime   Weekday  \n",
       "17550    1.0 2010-01-01 00:00:00    Friday  \n",
       "17551    4.0 2010-01-01 01:00:00    Friday  \n",
       "17552    6.0 2010-01-01 02:00:00    Friday  \n",
       "17553    1.0 2010-01-01 03:00:00    Friday  \n",
       "17554    3.0 2010-01-01 04:00:00    Friday  \n",
       "17555    2.0 2010-01-01 05:00:00    Friday  \n",
       "17556    0.0 2010-01-01 06:00:00    Friday  \n",
       "17557    0.0 2010-01-01 07:00:00    Friday  \n",
       "17558    0.0 2010-01-01 08:00:00    Friday  \n",
       "17559    0.0 2010-01-01 09:00:00    Friday  \n",
       "17560    0.0 2010-01-01 10:00:00    Friday  \n",
       "17561    0.0 2010-01-01 11:00:00    Friday  \n",
       "17562    2.0 2010-01-01 12:00:00    Friday  \n",
       "17563    0.0 2010-01-01 13:00:00    Friday  \n",
       "17564    0.0 2010-01-01 14:00:00    Friday  \n",
       "17565    0.0 2010-01-01 15:00:00    Friday  \n",
       "17566    2.0 2010-01-01 16:00:00    Friday  \n",
       "17567    0.0 2010-01-01 17:00:00    Friday  \n",
       "17568    0.0 2010-01-01 18:00:00    Friday  \n",
       "17569    0.0 2010-01-01 19:00:00    Friday  \n",
       "17570    0.0 2010-01-01 20:00:00    Friday  \n",
       "17571    1.0 2010-01-01 21:00:00    Friday  \n",
       "17572    2.0 2010-01-01 22:00:00    Friday  \n",
       "17573    2.0 2010-01-01 23:00:00    Friday  \n",
       "17574    0.0 2010-01-02 00:00:00  Saturday  \n",
       "17575    0.0 2010-01-02 01:00:00  Saturday  \n",
       "17576    1.0 2010-01-02 02:00:00  Saturday  \n",
       "17577    1.0 2010-01-02 03:00:00  Saturday  \n",
       "17578    1.0 2010-01-02 04:00:00  Saturday  \n",
       "17579    0.0 2010-01-02 05:00:00  Saturday  \n",
       "\n",
       "[30 rows x 22 columns]"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hourly_df.head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "datapath = '/Users/jacksonpeoples/Desktop/Springboard/CapstoneTwo/Data'\n",
    "\n",
    "datapath_crash_data = os.path.join(datapath, 'crash_cleaned.csv')\n",
    "if not os.path.exists(datapath_crash_data):\n",
    "    df.to_csv(datapath_crash_data, index=False)\n",
    "\n",
    "datapath_hourly_data = os.path.join(datapath, 'hourly_crash_cleaned.csv')\n",
    "if not os.path.exists(datapath_hourly_data):\n",
    "    hourly_df.to_csv(datapath_hourly_data, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}