ETL_clean_CA_only.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# EXTRACT TRANSFORM LOAD (ETL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Import and Load "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import dependencies\n",
    "\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy\n",
    "import psycopg2\n",
    "from sqlalchemy import *\n",
    "from sqlalchemy import create_engine, func\n",
    "from sqlalchemy.ext.automap import automap_base\n",
    "from sqlalchemy.orm import Session\n",
    "\n",
    "import sys\n",
    "sys.path.insert(1, '..')\n",
    "\n",
    "from config import username, password, port, db_name\n",
    "\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Source</th>\n",
       "      <th>TMC</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Start_Time</th>\n",
       "      <th>End_Time</th>\n",
       "      <th>Start_Lat</th>\n",
       "      <th>Start_Lng</th>\n",
       "      <th>End_Lat</th>\n",
       "      <th>End_Lng</th>\n",
       "      <th>...</th>\n",
       "      <th>Roundabout</th>\n",
       "      <th>Station</th>\n",
       "      <th>Stop</th>\n",
       "      <th>Traffic_Calming</th>\n",
       "      <th>Traffic_Signal</th>\n",
       "      <th>Turning_Loop</th>\n",
       "      <th>Sunrise_Sunset</th>\n",
       "      <th>Civil_Twilight</th>\n",
       "      <th>Nautical_Twilight</th>\n",
       "      <th>Astronomical_Twilight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A-1</td>\n",
       "      <td>MapQuest</td>\n",
       "      <td>201.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-02-08 05:46:00</td>\n",
       "      <td>2016-02-08 11:00:00</td>\n",
       "      <td>39.865147</td>\n",
       "      <td>-84.058723</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Night</td>\n",
       "      <td>Night</td>\n",
       "      <td>Night</td>\n",
       "      <td>Night</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A-2</td>\n",
       "      <td>MapQuest</td>\n",
       "      <td>201.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 06:07:59</td>\n",
       "      <td>2016-02-08 06:37:59</td>\n",
       "      <td>39.928059</td>\n",
       "      <td>-82.831184</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Night</td>\n",
       "      <td>Night</td>\n",
       "      <td>Night</td>\n",
       "      <td>Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A-3</td>\n",
       "      <td>MapQuest</td>\n",
       "      <td>201.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 06:49:27</td>\n",
       "      <td>2016-02-08 07:19:27</td>\n",
       "      <td>39.063148</td>\n",
       "      <td>-84.032608</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>Night</td>\n",
       "      <td>Night</td>\n",
       "      <td>Day</td>\n",
       "      <td>Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A-4</td>\n",
       "      <td>MapQuest</td>\n",
       "      <td>201.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-02-08 07:23:34</td>\n",
       "      <td>2016-02-08 07:53:34</td>\n",
       "      <td>39.747753</td>\n",
       "      <td>-84.205582</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Night</td>\n",
       "      <td>Day</td>\n",
       "      <td>Day</td>\n",
       "      <td>Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A-5</td>\n",
       "      <td>MapQuest</td>\n",
       "      <td>201.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 07:39:07</td>\n",
       "      <td>2016-02-08 08:09:07</td>\n",
       "      <td>39.627781</td>\n",
       "      <td>-84.188354</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>Day</td>\n",
       "      <td>Day</td>\n",
       "      <td>Day</td>\n",
       "      <td>Day</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 49 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID    Source    TMC  Severity           Start_Time             End_Time  \\\n",
       "0  A-1  MapQuest  201.0         3  2016-02-08 05:46:00  2016-02-08 11:00:00   \n",
       "1  A-2  MapQuest  201.0         2  2016-02-08 06:07:59  2016-02-08 06:37:59   \n",
       "2  A-3  MapQuest  201.0         2  2016-02-08 06:49:27  2016-02-08 07:19:27   \n",
       "3  A-4  MapQuest  201.0         3  2016-02-08 07:23:34  2016-02-08 07:53:34   \n",
       "4  A-5  MapQuest  201.0         2  2016-02-08 07:39:07  2016-02-08 08:09:07   \n",
       "\n",
       "   Start_Lat  Start_Lng  End_Lat  End_Lng  ...  Roundabout Station   Stop  \\\n",
       "0  39.865147 -84.058723      NaN      NaN  ...       False   False  False   \n",
       "1  39.928059 -82.831184      NaN      NaN  ...       False   False  False   \n",
       "2  39.063148 -84.032608      NaN      NaN  ...       False   False  False   \n",
       "3  39.747753 -84.205582      NaN      NaN  ...       False   False  False   \n",
       "4  39.627781 -84.188354      NaN      NaN  ...       False   False  False   \n",
       "\n",
       "  Traffic_Calming Traffic_Signal Turning_Loop Sunrise_Sunset Civil_Twilight  \\\n",
       "0           False          False        False          Night          Night   \n",
       "1           False          False        False          Night          Night   \n",
       "2           False           True        False          Night          Night   \n",
       "3           False          False        False          Night            Day   \n",
       "4           False           True        False            Day            Day   \n",
       "\n",
       "  Nautical_Twilight Astronomical_Twilight  \n",
       "0             Night                 Night  \n",
       "1             Night                   Day  \n",
       "2               Day                   Day  \n",
       "3               Day                   Day  \n",
       "4               Day                   Day  \n",
       "\n",
       "[5 rows x 49 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import the Kaggle Dataset with correct folder location\n",
    "\n",
    "file_path = \"../resources/US_Accidents_June20.csv\"\n",
    "accident_df = pd.read_csv(file_path)\n",
    "accident_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column ID has 0 null values\n",
      "Column Source has 0 null values\n",
      "Column TMC has 1034799 null values\n",
      "Column Severity has 0 null values\n",
      "Column Start_Time has 0 null values\n",
      "Column End_Time has 0 null values\n",
      "Column Start_Lat has 0 null values\n",
      "Column Start_Lng has 0 null values\n",
      "Column End_Lat has 2478818 null values\n",
      "Column End_Lng has 2478818 null values\n",
      "Column Distance(mi) has 0 null values\n",
      "Column Description has 1 null values\n",
      "Column Number has 2262864 null values\n",
      "Column Street has 0 null values\n",
      "Column Side has 0 null values\n",
      "Column City has 112 null values\n",
      "Column County has 0 null values\n",
      "Column State has 0 null values\n",
      "Column Zipcode has 1069 null values\n",
      "Column Country has 0 null values\n",
      "Column Timezone has 3880 null values\n",
      "Column Airport_Code has 6758 null values\n",
      "Column Weather_Timestamp has 43323 null values\n",
      "Column Temperature(F) has 65732 null values\n",
      "Column Wind_Chill(F) has 1868249 null values\n",
      "Column Humidity(%) has 69687 null values\n",
      "Column Pressure(in) has 55882 null values\n",
      "Column Visibility(mi) has 75856 null values\n",
      "Column Wind_Direction has 58874 null values\n",
      "Column Wind_Speed(mph) has 454609 null values\n",
      "Column Precipitation(in) has 2025874 null values\n",
      "Column Weather_Condition has 76138 null values\n",
      "Column Amenity has 0 null values\n",
      "Column Bump has 0 null values\n",
      "Column Crossing has 0 null values\n",
      "Column Give_Way has 0 null values\n",
      "Column Junction has 0 null values\n",
      "Column No_Exit has 0 null values\n",
      "Column Railway has 0 null values\n",
      "Column Roundabout has 0 null values\n",
      "Column Station has 0 null values\n",
      "Column Stop has 0 null values\n",
      "Column Traffic_Calming has 0 null values\n",
      "Column Traffic_Signal has 0 null values\n",
      "Column Turning_Loop has 0 null values\n",
      "Column Sunrise_Sunset has 115 null values\n",
      "Column Civil_Twilight has 115 null values\n",
      "Column Nautical_Twilight has 115 null values\n",
      "Column Astronomical_Twilight has 115 null values\n"
     ]
    }
   ],
   "source": [
    "#Print count of  null values in all columns \n",
    "\n",
    "for column in accident_df.columns:\n",
    "    print(f\"Column {column} has {accident_df[column].isnull().sum()} null values\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Drop, Transform and Clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Drop null values from certain (important) rows\n",
    "\n",
    "accident_dropna_df = accident_df.dropna(subset=['City', 'Zipcode', 'County', 'Temperature(F)', 'Weather_Condition'])"
   ]
  },
  {
   "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>ID</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Start_Time</th>\n",
       "      <th>Start_Lat</th>\n",
       "      <th>Start_Lng</th>\n",
       "      <th>City</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>Zipcode</th>\n",
       "      <th>Temperature(F)</th>\n",
       "      <th>...</th>\n",
       "      <th>Give_Way</th>\n",
       "      <th>Junction</th>\n",
       "      <th>No_Exit</th>\n",
       "      <th>Railway</th>\n",
       "      <th>Roundabout</th>\n",
       "      <th>Station</th>\n",
       "      <th>Stop</th>\n",
       "      <th>Traffic_Calming</th>\n",
       "      <th>Traffic_Signal</th>\n",
       "      <th>Turning_Loop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A-1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-02-08 05:46:00</td>\n",
       "      <td>39.865147</td>\n",
       "      <td>-84.058723</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45424</td>\n",
       "      <td>36.9</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A-2</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 06:07:59</td>\n",
       "      <td>39.928059</td>\n",
       "      <td>-82.831184</td>\n",
       "      <td>Reynoldsburg</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>OH</td>\n",
       "      <td>43068-3402</td>\n",
       "      <td>37.9</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A-3</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 06:49:27</td>\n",
       "      <td>39.063148</td>\n",
       "      <td>-84.032608</td>\n",
       "      <td>Williamsburg</td>\n",
       "      <td>Clermont</td>\n",
       "      <td>OH</td>\n",
       "      <td>45176</td>\n",
       "      <td>36.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A-4</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-02-08 07:23:34</td>\n",
       "      <td>39.747753</td>\n",
       "      <td>-84.205582</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45417</td>\n",
       "      <td>35.1</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A-5</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 07:39:07</td>\n",
       "      <td>39.627781</td>\n",
       "      <td>-84.188354</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45459</td>\n",
       "      <td>36.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID  Severity           Start_Time  Start_Lat  Start_Lng          City  \\\n",
       "0  A-1         3  2016-02-08 05:46:00  39.865147 -84.058723        Dayton   \n",
       "1  A-2         2  2016-02-08 06:07:59  39.928059 -82.831184  Reynoldsburg   \n",
       "2  A-3         2  2016-02-08 06:49:27  39.063148 -84.032608  Williamsburg   \n",
       "3  A-4         3  2016-02-08 07:23:34  39.747753 -84.205582        Dayton   \n",
       "4  A-5         2  2016-02-08 07:39:07  39.627781 -84.188354        Dayton   \n",
       "\n",
       "       County State     Zipcode  Temperature(F)  ...  Give_Way  Junction  \\\n",
       "0  Montgomery    OH       45424            36.9  ...     False     False   \n",
       "1    Franklin    OH  43068-3402            37.9  ...     False     False   \n",
       "2    Clermont    OH       45176            36.0  ...     False     False   \n",
       "3  Montgomery    OH       45417            35.1  ...     False     False   \n",
       "4  Montgomery    OH       45459            36.0  ...     False     False   \n",
       "\n",
       "   No_Exit Railway  Roundabout  Station   Stop  Traffic_Calming  \\\n",
       "0    False   False       False    False  False            False   \n",
       "1    False   False       False    False  False            False   \n",
       "2    False   False       False    False  False            False   \n",
       "3    False   False       False    False  False            False   \n",
       "4    False   False       False    False  False            False   \n",
       "\n",
       "   Traffic_Signal  Turning_Loop  \n",
       "0           False         False  \n",
       "1           False         False  \n",
       "2            True         False  \n",
       "3           False         False  \n",
       "4            True         False  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Drop unneccessary columns\n",
    "\n",
    "new_accident_df = accident_dropna_df.drop(['Source', 'TMC', 'End_Time', 'Airport_Code', 'End_Lat', 'End_Lng', 'Distance(mi)', 'Wind_Chill(F)', 'Wind_Direction', 'Humidity(%)', 'Pressure(in)', 'Description', 'Number', 'Street', 'Side', 'Country', 'Timezone', 'Weather_Timestamp', 'Sunrise_Sunset', 'Civil_Twilight', 'Nautical_Twilight', 'Astronomical_Twilight'], axis=1)\n",
    "new_accident_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID                    object\n",
       "Severity               int64\n",
       "Start_Time            object\n",
       "Start_Lat            float64\n",
       "Start_Lng            float64\n",
       "City                  object\n",
       "County                object\n",
       "State                 object\n",
       "Zipcode               object\n",
       "Temperature(F)       float64\n",
       "Visibility(mi)       float64\n",
       "Wind_Speed(mph)      float64\n",
       "Precipitation(in)    float64\n",
       "Weather_Condition     object\n",
       "Amenity                 bool\n",
       "Bump                    bool\n",
       "Crossing                bool\n",
       "Give_Way                bool\n",
       "Junction                bool\n",
       "No_Exit                 bool\n",
       "Railway                 bool\n",
       "Roundabout              bool\n",
       "Station                 bool\n",
       "Stop                    bool\n",
       "Traffic_Calming         bool\n",
       "Traffic_Signal          bool\n",
       "Turning_Loop            bool\n",
       "dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_accident_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check for duplicates in ID\n",
    "\n",
    "new_accident_df.duplicated(subset=['ID']).any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert Start_Time to datetypes\n",
    "\n",
    "new_accident_df['Start_Time'] = pd.to_datetime(new_accident_df['Start_Time'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract year, month, day, hour, and weekday\n",
    "# %a = weekdays\n",
    "# %H = 24-hours time\n",
    "\n",
    "new_accident_df['Year'] = new_accident_df['Start_Time'].dt.year\n",
    "new_accident_df['Month'] = new_accident_df['Start_Time'].dt.strftime('%B')\n",
    "new_accident_df['Time'] = new_accident_df['Start_Time'].dt.strftime(\"%H\")\n",
    "new_accident_df['Part_of_Week'] = new_accident_df['Start_Time'].dt.strftime('%a')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Convert Time column as integer, which will serve as a number for the day of the week\n",
    "\n",
    "new_accident_df['Time'] = new_accident_df['Time'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Start_Time</th>\n",
       "      <th>Start_Lat</th>\n",
       "      <th>Start_Lng</th>\n",
       "      <th>City</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>Zipcode</th>\n",
       "      <th>Temperature(F)</th>\n",
       "      <th>...</th>\n",
       "      <th>Roundabout</th>\n",
       "      <th>Station</th>\n",
       "      <th>Stop</th>\n",
       "      <th>Traffic_Calming</th>\n",
       "      <th>Traffic_Signal</th>\n",
       "      <th>Turning_Loop</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Time</th>\n",
       "      <th>Part_of_Week</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A-1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-02-08 05:46:00</td>\n",
       "      <td>39.865147</td>\n",
       "      <td>-84.058723</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45424</td>\n",
       "      <td>36.9</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>5</td>\n",
       "      <td>Mon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A-2</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 06:07:59</td>\n",
       "      <td>39.928059</td>\n",
       "      <td>-82.831184</td>\n",
       "      <td>Reynoldsburg</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>OH</td>\n",
       "      <td>43068-3402</td>\n",
       "      <td>37.9</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>6</td>\n",
       "      <td>Mon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A-3</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 06:49:27</td>\n",
       "      <td>39.063148</td>\n",
       "      <td>-84.032608</td>\n",
       "      <td>Williamsburg</td>\n",
       "      <td>Clermont</td>\n",
       "      <td>OH</td>\n",
       "      <td>45176</td>\n",
       "      <td>36.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>6</td>\n",
       "      <td>Mon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A-4</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-02-08 07:23:34</td>\n",
       "      <td>39.747753</td>\n",
       "      <td>-84.205582</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45417</td>\n",
       "      <td>35.1</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>7</td>\n",
       "      <td>Mon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A-5</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 07:39:07</td>\n",
       "      <td>39.627781</td>\n",
       "      <td>-84.188354</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45459</td>\n",
       "      <td>36.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>7</td>\n",
       "      <td>Mon</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>3513612</th>\n",
       "      <td>A-3513776</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-08-23 18:03:25</td>\n",
       "      <td>34.002480</td>\n",
       "      <td>-117.379360</td>\n",
       "      <td>Riverside</td>\n",
       "      <td>Riverside</td>\n",
       "      <td>CA</td>\n",
       "      <td>92501</td>\n",
       "      <td>86.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>18</td>\n",
       "      <td>Fri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513613</th>\n",
       "      <td>A-3513777</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-08-23 19:11:30</td>\n",
       "      <td>32.766960</td>\n",
       "      <td>-117.148060</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>CA</td>\n",
       "      <td>92108</td>\n",
       "      <td>70.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>19</td>\n",
       "      <td>Fri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513614</th>\n",
       "      <td>A-3513778</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-08-23 19:00:21</td>\n",
       "      <td>33.775450</td>\n",
       "      <td>-117.847790</td>\n",
       "      <td>Orange</td>\n",
       "      <td>Orange</td>\n",
       "      <td>CA</td>\n",
       "      <td>92866</td>\n",
       "      <td>73.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>19</td>\n",
       "      <td>Fri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513615</th>\n",
       "      <td>A-3513779</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-08-23 19:00:21</td>\n",
       "      <td>33.992460</td>\n",
       "      <td>-118.403020</td>\n",
       "      <td>Culver City</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>90230</td>\n",
       "      <td>71.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>19</td>\n",
       "      <td>Fri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513616</th>\n",
       "      <td>A-3513780</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-08-23 18:52:06</td>\n",
       "      <td>34.133930</td>\n",
       "      <td>-117.230920</td>\n",
       "      <td>Highland</td>\n",
       "      <td>San Bernardino</td>\n",
       "      <td>CA</td>\n",
       "      <td>92346</td>\n",
       "      <td>79.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>18</td>\n",
       "      <td>Fri</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3425344 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                ID  Severity          Start_Time  Start_Lat   Start_Lng  \\\n",
       "0              A-1         3 2016-02-08 05:46:00  39.865147  -84.058723   \n",
       "1              A-2         2 2016-02-08 06:07:59  39.928059  -82.831184   \n",
       "2              A-3         2 2016-02-08 06:49:27  39.063148  -84.032608   \n",
       "3              A-4         3 2016-02-08 07:23:34  39.747753  -84.205582   \n",
       "4              A-5         2 2016-02-08 07:39:07  39.627781  -84.188354   \n",
       "...            ...       ...                 ...        ...         ...   \n",
       "3513612  A-3513776         2 2019-08-23 18:03:25  34.002480 -117.379360   \n",
       "3513613  A-3513777         2 2019-08-23 19:11:30  32.766960 -117.148060   \n",
       "3513614  A-3513778         2 2019-08-23 19:00:21  33.775450 -117.847790   \n",
       "3513615  A-3513779         2 2019-08-23 19:00:21  33.992460 -118.403020   \n",
       "3513616  A-3513780         2 2019-08-23 18:52:06  34.133930 -117.230920   \n",
       "\n",
       "                 City          County State     Zipcode  Temperature(F)  ...  \\\n",
       "0              Dayton      Montgomery    OH       45424            36.9  ...   \n",
       "1        Reynoldsburg        Franklin    OH  43068-3402            37.9  ...   \n",
       "2        Williamsburg        Clermont    OH       45176            36.0  ...   \n",
       "3              Dayton      Montgomery    OH       45417            35.1  ...   \n",
       "4              Dayton      Montgomery    OH       45459            36.0  ...   \n",
       "...               ...             ...   ...         ...             ...  ...   \n",
       "3513612     Riverside       Riverside    CA       92501            86.0  ...   \n",
       "3513613     San Diego       San Diego    CA       92108            70.0  ...   \n",
       "3513614        Orange          Orange    CA       92866            73.0  ...   \n",
       "3513615   Culver City     Los Angeles    CA       90230            71.0  ...   \n",
       "3513616      Highland  San Bernardino    CA       92346            79.0  ...   \n",
       "\n",
       "         Roundabout  Station   Stop Traffic_Calming  Traffic_Signal  \\\n",
       "0             False    False  False           False           False   \n",
       "1             False    False  False           False           False   \n",
       "2             False    False  False           False            True   \n",
       "3             False    False  False           False           False   \n",
       "4             False    False  False           False            True   \n",
       "...             ...      ...    ...             ...             ...   \n",
       "3513612       False    False  False           False           False   \n",
       "3513613       False    False  False           False           False   \n",
       "3513614       False    False  False           False           False   \n",
       "3513615       False    False  False           False           False   \n",
       "3513616       False    False  False           False           False   \n",
       "\n",
       "         Turning_Loop  Year     Month  Time  Part_of_Week  \n",
       "0               False  2016  February     5           Mon  \n",
       "1               False  2016  February     6           Mon  \n",
       "2               False  2016  February     6           Mon  \n",
       "3               False  2016  February     7           Mon  \n",
       "4               False  2016  February     7           Mon  \n",
       "...               ...   ...       ...   ...           ...  \n",
       "3513612         False  2019    August    18           Fri  \n",
       "3513613         False  2019    August    19           Fri  \n",
       "3513614         False  2019    August    19           Fri  \n",
       "3513615         False  2019    August    19           Fri  \n",
       "3513616         False  2019    August    18           Fri  \n",
       "\n",
       "[3425344 rows x 31 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_accident_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cut 24 hour Time coloumn from a continuous variable (24 hours) to a categorical variable (1-5) for ease in ML\n",
    "# 1 = Early Morning (3 am to 6:59 am)\n",
    "# 2 = Morning (7 am to 10:59 am),\n",
    "# 3 = Afternoon (11 am to 2:59 pm),\n",
    "# 4 = Late Afternoon (3 pm to 6:59 pm),\n",
    "# 5 = Night (7 pm to 10:59 pm),\n",
    "# 6 = Late Night (11 pm to 2:59 am)\n",
    "\n",
    "bins = [0,4,8,12,16,20,24]\n",
    "labels = ['Late Night', 'Early Morning','Morning','Afteroon','Late Afternoon','Night']\n",
    "new_accident_df['Time_of_Day'] = pd.cut(new_accident_df['Time'], bins= bins, labels=labels, include_lowest=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function for definning labels for 24 hour time coloumn\n",
    "\n",
    "def f(x):\n",
    "    if (x > 4) and (x <= 8):\n",
    "        return 'Early Morning'\n",
    "    elif (x > 8) and (x <= 12 ):\n",
    "        return 'Morning'\n",
    "    elif (x > 12) and (x <= 16):\n",
    "        return'Afteroon'\n",
    "    elif (x > 16) and (x <= 20) :\n",
    "        return 'Late Afternoon'\n",
    "    elif (x > 20) and (x <= 24):\n",
    "        return'Night'\n",
    "    elif (x <= 4):\n",
    "        return'Late Night'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Start_Time</th>\n",
       "      <th>Start_Lat</th>\n",
       "      <th>Start_Lng</th>\n",
       "      <th>City</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>Zipcode</th>\n",
       "      <th>Temperature(F)</th>\n",
       "      <th>...</th>\n",
       "      <th>Station</th>\n",
       "      <th>Stop</th>\n",
       "      <th>Traffic_Calming</th>\n",
       "      <th>Traffic_Signal</th>\n",
       "      <th>Turning_Loop</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Time</th>\n",
       "      <th>Part_of_Week</th>\n",
       "      <th>Time_of_Day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A-1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-02-08 05:46:00</td>\n",
       "      <td>39.865147</td>\n",
       "      <td>-84.058723</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45424</td>\n",
       "      <td>36.9</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>5</td>\n",
       "      <td>Mon</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A-2</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 06:07:59</td>\n",
       "      <td>39.928059</td>\n",
       "      <td>-82.831184</td>\n",
       "      <td>Reynoldsburg</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>OH</td>\n",
       "      <td>43068-3402</td>\n",
       "      <td>37.9</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>6</td>\n",
       "      <td>Mon</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A-3</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 06:49:27</td>\n",
       "      <td>39.063148</td>\n",
       "      <td>-84.032608</td>\n",
       "      <td>Williamsburg</td>\n",
       "      <td>Clermont</td>\n",
       "      <td>OH</td>\n",
       "      <td>45176</td>\n",
       "      <td>36.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>6</td>\n",
       "      <td>Mon</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A-4</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-02-08 07:23:34</td>\n",
       "      <td>39.747753</td>\n",
       "      <td>-84.205582</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45417</td>\n",
       "      <td>35.1</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>7</td>\n",
       "      <td>Mon</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A-5</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 07:39:07</td>\n",
       "      <td>39.627781</td>\n",
       "      <td>-84.188354</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45459</td>\n",
       "      <td>36.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>7</td>\n",
       "      <td>Mon</td>\n",
       "      <td>Early Morning</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>3513612</th>\n",
       "      <td>A-3513776</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-08-23 18:03:25</td>\n",
       "      <td>34.002480</td>\n",
       "      <td>-117.379360</td>\n",
       "      <td>Riverside</td>\n",
       "      <td>Riverside</td>\n",
       "      <td>CA</td>\n",
       "      <td>92501</td>\n",
       "      <td>86.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>18</td>\n",
       "      <td>Fri</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513613</th>\n",
       "      <td>A-3513777</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-08-23 19:11:30</td>\n",
       "      <td>32.766960</td>\n",
       "      <td>-117.148060</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>CA</td>\n",
       "      <td>92108</td>\n",
       "      <td>70.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>19</td>\n",
       "      <td>Fri</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513614</th>\n",
       "      <td>A-3513778</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-08-23 19:00:21</td>\n",
       "      <td>33.775450</td>\n",
       "      <td>-117.847790</td>\n",
       "      <td>Orange</td>\n",
       "      <td>Orange</td>\n",
       "      <td>CA</td>\n",
       "      <td>92866</td>\n",
       "      <td>73.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>19</td>\n",
       "      <td>Fri</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513615</th>\n",
       "      <td>A-3513779</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-08-23 19:00:21</td>\n",
       "      <td>33.992460</td>\n",
       "      <td>-118.403020</td>\n",
       "      <td>Culver City</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>90230</td>\n",
       "      <td>71.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>19</td>\n",
       "      <td>Fri</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513616</th>\n",
       "      <td>A-3513780</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-08-23 18:52:06</td>\n",
       "      <td>34.133930</td>\n",
       "      <td>-117.230920</td>\n",
       "      <td>Highland</td>\n",
       "      <td>San Bernardino</td>\n",
       "      <td>CA</td>\n",
       "      <td>92346</td>\n",
       "      <td>79.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>18</td>\n",
       "      <td>Fri</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3425344 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                ID  Severity          Start_Time  Start_Lat   Start_Lng  \\\n",
       "0              A-1         3 2016-02-08 05:46:00  39.865147  -84.058723   \n",
       "1              A-2         2 2016-02-08 06:07:59  39.928059  -82.831184   \n",
       "2              A-3         2 2016-02-08 06:49:27  39.063148  -84.032608   \n",
       "3              A-4         3 2016-02-08 07:23:34  39.747753  -84.205582   \n",
       "4              A-5         2 2016-02-08 07:39:07  39.627781  -84.188354   \n",
       "...            ...       ...                 ...        ...         ...   \n",
       "3513612  A-3513776         2 2019-08-23 18:03:25  34.002480 -117.379360   \n",
       "3513613  A-3513777         2 2019-08-23 19:11:30  32.766960 -117.148060   \n",
       "3513614  A-3513778         2 2019-08-23 19:00:21  33.775450 -117.847790   \n",
       "3513615  A-3513779         2 2019-08-23 19:00:21  33.992460 -118.403020   \n",
       "3513616  A-3513780         2 2019-08-23 18:52:06  34.133930 -117.230920   \n",
       "\n",
       "                 City          County State     Zipcode  Temperature(F)  ...  \\\n",
       "0              Dayton      Montgomery    OH       45424            36.9  ...   \n",
       "1        Reynoldsburg        Franklin    OH  43068-3402            37.9  ...   \n",
       "2        Williamsburg        Clermont    OH       45176            36.0  ...   \n",
       "3              Dayton      Montgomery    OH       45417            35.1  ...   \n",
       "4              Dayton      Montgomery    OH       45459            36.0  ...   \n",
       "...               ...             ...   ...         ...             ...  ...   \n",
       "3513612     Riverside       Riverside    CA       92501            86.0  ...   \n",
       "3513613     San Diego       San Diego    CA       92108            70.0  ...   \n",
       "3513614        Orange          Orange    CA       92866            73.0  ...   \n",
       "3513615   Culver City     Los Angeles    CA       90230            71.0  ...   \n",
       "3513616      Highland  San Bernardino    CA       92346            79.0  ...   \n",
       "\n",
       "         Station   Stop  Traffic_Calming Traffic_Signal  Turning_Loop  Year  \\\n",
       "0          False  False            False          False         False  2016   \n",
       "1          False  False            False          False         False  2016   \n",
       "2          False  False            False           True         False  2016   \n",
       "3          False  False            False          False         False  2016   \n",
       "4          False  False            False           True         False  2016   \n",
       "...          ...    ...              ...            ...           ...   ...   \n",
       "3513612    False  False            False          False         False  2019   \n",
       "3513613    False  False            False          False         False  2019   \n",
       "3513614    False  False            False          False         False  2019   \n",
       "3513615    False  False            False          False         False  2019   \n",
       "3513616    False  False            False          False         False  2019   \n",
       "\n",
       "            Month  Time  Part_of_Week     Time_of_Day  \n",
       "0        February     5           Mon   Early Morning  \n",
       "1        February     6           Mon   Early Morning  \n",
       "2        February     6           Mon   Early Morning  \n",
       "3        February     7           Mon   Early Morning  \n",
       "4        February     7           Mon   Early Morning  \n",
       "...           ...   ...           ...             ...  \n",
       "3513612    August    18           Fri  Late Afternoon  \n",
       "3513613    August    19           Fri  Late Afternoon  \n",
       "3513614    August    19           Fri  Late Afternoon  \n",
       "3513615    August    19           Fri  Late Afternoon  \n",
       "3513616    August    18           Fri  Late Afternoon  \n",
       "\n",
       "[3425344 rows x 32 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Apply function for time sections\n",
    "\n",
    "new_accident_df['Time_of_Day'] = new_accident_df['Time'].apply(f)\n",
    "new_accident_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Early Morning     916231\n",
       "Afteroon          800840\n",
       "Morning           711613\n",
       "Late Afternoon    682635\n",
       "Late Night        157086\n",
       "Night             156939\n",
       "Name: Time_of_Day, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_accident_df['Time_of_Day'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill in all NA values with zeros (instead of mean values, or NA)\n",
    "\n",
    "new_accident_df = new_accident_df.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Replace original days of the week with \"weekdays\" and \"weekend\"\n",
    "\n",
    "new_accident_df['Part_of_Week'] = new_accident_df['Part_of_Week'].str.replace('Mon', 'Weekday')\n",
    "new_accident_df['Part_of_Week'] = new_accident_df['Part_of_Week'].str.replace('Tue', 'Weekday')\n",
    "new_accident_df['Part_of_Week'] = new_accident_df['Part_of_Week'].str.replace('Wed', 'Weekday')\n",
    "new_accident_df['Part_of_Week'] = new_accident_df['Part_of_Week'].str.replace('Thu', 'Weekday')\n",
    "new_accident_df['Part_of_Week'] = new_accident_df['Part_of_Week'].str.replace('Fri', 'Weekday')\n",
    "new_accident_df['Part_of_Week'] = new_accident_df['Part_of_Week'].str.replace('Sat', 'Weekend')\n",
    "new_accident_df['Part_of_Week'] = new_accident_df['Part_of_Week'].str.replace('Sun', 'Weekend')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>ID</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Start_Time</th>\n",
       "      <th>Start_Lat</th>\n",
       "      <th>Start_Lng</th>\n",
       "      <th>City</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>Zipcode</th>\n",
       "      <th>Temperature(F)</th>\n",
       "      <th>...</th>\n",
       "      <th>Station</th>\n",
       "      <th>Stop</th>\n",
       "      <th>Traffic_Calming</th>\n",
       "      <th>Traffic_Signal</th>\n",
       "      <th>Turning_Loop</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Time</th>\n",
       "      <th>Part_of_Week</th>\n",
       "      <th>Time_of_Day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A-1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-02-08 05:46:00</td>\n",
       "      <td>39.865147</td>\n",
       "      <td>-84.058723</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45424</td>\n",
       "      <td>36.9</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>5</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A-2</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 06:07:59</td>\n",
       "      <td>39.928059</td>\n",
       "      <td>-82.831184</td>\n",
       "      <td>Reynoldsburg</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>OH</td>\n",
       "      <td>43068-3402</td>\n",
       "      <td>37.9</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>6</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A-3</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 06:49:27</td>\n",
       "      <td>39.063148</td>\n",
       "      <td>-84.032608</td>\n",
       "      <td>Williamsburg</td>\n",
       "      <td>Clermont</td>\n",
       "      <td>OH</td>\n",
       "      <td>45176</td>\n",
       "      <td>36.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>6</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A-4</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-02-08 07:23:34</td>\n",
       "      <td>39.747753</td>\n",
       "      <td>-84.205582</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45417</td>\n",
       "      <td>35.1</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>7</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A-5</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-02-08 07:39:07</td>\n",
       "      <td>39.627781</td>\n",
       "      <td>-84.188354</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45459</td>\n",
       "      <td>36.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>7</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID  Severity          Start_Time  Start_Lat  Start_Lng          City  \\\n",
       "0  A-1         3 2016-02-08 05:46:00  39.865147 -84.058723        Dayton   \n",
       "1  A-2         2 2016-02-08 06:07:59  39.928059 -82.831184  Reynoldsburg   \n",
       "2  A-3         2 2016-02-08 06:49:27  39.063148 -84.032608  Williamsburg   \n",
       "3  A-4         3 2016-02-08 07:23:34  39.747753 -84.205582        Dayton   \n",
       "4  A-5         2 2016-02-08 07:39:07  39.627781 -84.188354        Dayton   \n",
       "\n",
       "       County State     Zipcode  Temperature(F)  ...  Station   Stop  \\\n",
       "0  Montgomery    OH       45424            36.9  ...    False  False   \n",
       "1    Franklin    OH  43068-3402            37.9  ...    False  False   \n",
       "2    Clermont    OH       45176            36.0  ...    False  False   \n",
       "3  Montgomery    OH       45417            35.1  ...    False  False   \n",
       "4  Montgomery    OH       45459            36.0  ...    False  False   \n",
       "\n",
       "   Traffic_Calming Traffic_Signal  Turning_Loop  Year     Month  Time  \\\n",
       "0            False          False         False  2016  February     5   \n",
       "1            False          False         False  2016  February     6   \n",
       "2            False           True         False  2016  February     6   \n",
       "3            False          False         False  2016  February     7   \n",
       "4            False           True         False  2016  February     7   \n",
       "\n",
       "   Part_of_Week    Time_of_Day  \n",
       "0       Weekday  Early Morning  \n",
       "1       Weekday  Early Morning  \n",
       "2       Weekday  Early Morning  \n",
       "3       Weekday  Early Morning  \n",
       "4       Weekday  Early Morning  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_accident_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Drop uneeded columns and disected coloumns\n",
    "\n",
    "cleaned_df = new_accident_df.drop(['Start_Time', 'Amenity', 'Station'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Start_Lat</th>\n",
       "      <th>Start_Lng</th>\n",
       "      <th>City</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>Zipcode</th>\n",
       "      <th>Temperature(F)</th>\n",
       "      <th>Visibility(mi)</th>\n",
       "      <th>...</th>\n",
       "      <th>Roundabout</th>\n",
       "      <th>Stop</th>\n",
       "      <th>Traffic_Calming</th>\n",
       "      <th>Traffic_Signal</th>\n",
       "      <th>Turning_Loop</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Time</th>\n",
       "      <th>Part_of_Week</th>\n",
       "      <th>Time_of_Day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A-1</td>\n",
       "      <td>3</td>\n",
       "      <td>39.865147</td>\n",
       "      <td>-84.058723</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45424</td>\n",
       "      <td>36.9</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>5</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A-2</td>\n",
       "      <td>2</td>\n",
       "      <td>39.928059</td>\n",
       "      <td>-82.831184</td>\n",
       "      <td>Reynoldsburg</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>OH</td>\n",
       "      <td>43068-3402</td>\n",
       "      <td>37.9</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>6</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A-3</td>\n",
       "      <td>2</td>\n",
       "      <td>39.063148</td>\n",
       "      <td>-84.032608</td>\n",
       "      <td>Williamsburg</td>\n",
       "      <td>Clermont</td>\n",
       "      <td>OH</td>\n",
       "      <td>45176</td>\n",
       "      <td>36.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>6</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A-4</td>\n",
       "      <td>3</td>\n",
       "      <td>39.747753</td>\n",
       "      <td>-84.205582</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45417</td>\n",
       "      <td>35.1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>7</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A-5</td>\n",
       "      <td>2</td>\n",
       "      <td>39.627781</td>\n",
       "      <td>-84.188354</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45459</td>\n",
       "      <td>36.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>7</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</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>3513612</th>\n",
       "      <td>A-3513776</td>\n",
       "      <td>2</td>\n",
       "      <td>34.002480</td>\n",
       "      <td>-117.379360</td>\n",
       "      <td>Riverside</td>\n",
       "      <td>Riverside</td>\n",
       "      <td>CA</td>\n",
       "      <td>92501</td>\n",
       "      <td>86.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>18</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513613</th>\n",
       "      <td>A-3513777</td>\n",
       "      <td>2</td>\n",
       "      <td>32.766960</td>\n",
       "      <td>-117.148060</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>CA</td>\n",
       "      <td>92108</td>\n",
       "      <td>70.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>19</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513614</th>\n",
       "      <td>A-3513778</td>\n",
       "      <td>2</td>\n",
       "      <td>33.775450</td>\n",
       "      <td>-117.847790</td>\n",
       "      <td>Orange</td>\n",
       "      <td>Orange</td>\n",
       "      <td>CA</td>\n",
       "      <td>92866</td>\n",
       "      <td>73.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>19</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513615</th>\n",
       "      <td>A-3513779</td>\n",
       "      <td>2</td>\n",
       "      <td>33.992460</td>\n",
       "      <td>-118.403020</td>\n",
       "      <td>Culver City</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>90230</td>\n",
       "      <td>71.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>19</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513616</th>\n",
       "      <td>A-3513780</td>\n",
       "      <td>2</td>\n",
       "      <td>34.133930</td>\n",
       "      <td>-117.230920</td>\n",
       "      <td>Highland</td>\n",
       "      <td>San Bernardino</td>\n",
       "      <td>CA</td>\n",
       "      <td>92346</td>\n",
       "      <td>79.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>18</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3425344 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                ID  Severity  Start_Lat   Start_Lng          City  \\\n",
       "0              A-1         3  39.865147  -84.058723        Dayton   \n",
       "1              A-2         2  39.928059  -82.831184  Reynoldsburg   \n",
       "2              A-3         2  39.063148  -84.032608  Williamsburg   \n",
       "3              A-4         3  39.747753  -84.205582        Dayton   \n",
       "4              A-5         2  39.627781  -84.188354        Dayton   \n",
       "...            ...       ...        ...         ...           ...   \n",
       "3513612  A-3513776         2  34.002480 -117.379360     Riverside   \n",
       "3513613  A-3513777         2  32.766960 -117.148060     San Diego   \n",
       "3513614  A-3513778         2  33.775450 -117.847790        Orange   \n",
       "3513615  A-3513779         2  33.992460 -118.403020   Culver City   \n",
       "3513616  A-3513780         2  34.133930 -117.230920      Highland   \n",
       "\n",
       "                 County State     Zipcode  Temperature(F)  Visibility(mi)  \\\n",
       "0            Montgomery    OH       45424            36.9            10.0   \n",
       "1              Franklin    OH  43068-3402            37.9            10.0   \n",
       "2              Clermont    OH       45176            36.0            10.0   \n",
       "3            Montgomery    OH       45417            35.1             9.0   \n",
       "4            Montgomery    OH       45459            36.0             6.0   \n",
       "...                 ...   ...         ...             ...             ...   \n",
       "3513612       Riverside    CA       92501            86.0            10.0   \n",
       "3513613       San Diego    CA       92108            70.0            10.0   \n",
       "3513614          Orange    CA       92866            73.0            10.0   \n",
       "3513615     Los Angeles    CA       90230            71.0            10.0   \n",
       "3513616  San Bernardino    CA       92346            79.0             7.0   \n",
       "\n",
       "         ...  Roundabout   Stop Traffic_Calming  Traffic_Signal  Turning_Loop  \\\n",
       "0        ...       False  False           False           False         False   \n",
       "1        ...       False  False           False           False         False   \n",
       "2        ...       False  False           False            True         False   \n",
       "3        ...       False  False           False           False         False   \n",
       "4        ...       False  False           False            True         False   \n",
       "...      ...         ...    ...             ...             ...           ...   \n",
       "3513612  ...       False  False           False           False         False   \n",
       "3513613  ...       False  False           False           False         False   \n",
       "3513614  ...       False  False           False           False         False   \n",
       "3513615  ...       False  False           False           False         False   \n",
       "3513616  ...       False  False           False           False         False   \n",
       "\n",
       "         Year     Month  Time  Part_of_Week     Time_of_Day  \n",
       "0        2016  February     5       Weekday   Early Morning  \n",
       "1        2016  February     6       Weekday   Early Morning  \n",
       "2        2016  February     6       Weekday   Early Morning  \n",
       "3        2016  February     7       Weekday   Early Morning  \n",
       "4        2016  February     7       Weekday   Early Morning  \n",
       "...       ...       ...   ...           ...             ...  \n",
       "3513612  2019    August    18       Weekday  Late Afternoon  \n",
       "3513613  2019    August    19       Weekday  Late Afternoon  \n",
       "3513614  2019    August    19       Weekday  Late Afternoon  \n",
       "3513615  2019    August    19       Weekday  Late Afternoon  \n",
       "3513616  2019    August    18       Weekday  Late Afternoon  \n",
       "\n",
       "[3425344 rows x 29 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Keep 5 digit zipcodes\n",
    "\n",
    "cleaned_df['Zipcode'] = cleaned_df['Zipcode'].str[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Rename coloumns and restructuring coloumn placement \n",
    "\n",
    "cleaned_df.rename(columns = {'Start_Lat':'Latitude', 'Start_Lng':'Longitude', 'Temperature(F)': 'Temperature_F', 'Wind_Speed(mph)': 'Wind_Speed_mph',\n",
    "                              'Visibility(mi)': 'Visibility_miles', 'Precipitation(in)': 'Precipitation_inches', 'Bump': 'Speed_Bump', 'Crossing': 'Cross_Walk', \n",
    "                              'Give_Way': 'Yield_Sign', 'Junction': 'Intersection', 'Stop': 'Stop_Sign', 'Traffic_Calming': 'Traffic', 'Traffic_Signal': 'Traffic_Light', \n",
    "                              }, inplace = True) "
   ]
  },
  {
   "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>ID</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>City</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>Zipcode</th>\n",
       "      <th>Temperature_F</th>\n",
       "      <th>Visibility_miles</th>\n",
       "      <th>...</th>\n",
       "      <th>Roundabout</th>\n",
       "      <th>Stop_Sign</th>\n",
       "      <th>Traffic</th>\n",
       "      <th>Traffic_Light</th>\n",
       "      <th>Turning_Loop</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Time</th>\n",
       "      <th>Part_of_Week</th>\n",
       "      <th>Time_of_Day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A-1</td>\n",
       "      <td>3</td>\n",
       "      <td>39.865147</td>\n",
       "      <td>-84.058723</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45424</td>\n",
       "      <td>36.9</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>5</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A-2</td>\n",
       "      <td>2</td>\n",
       "      <td>39.928059</td>\n",
       "      <td>-82.831184</td>\n",
       "      <td>Reynoldsburg</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>OH</td>\n",
       "      <td>43068</td>\n",
       "      <td>37.9</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>6</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A-3</td>\n",
       "      <td>2</td>\n",
       "      <td>39.063148</td>\n",
       "      <td>-84.032608</td>\n",
       "      <td>Williamsburg</td>\n",
       "      <td>Clermont</td>\n",
       "      <td>OH</td>\n",
       "      <td>45176</td>\n",
       "      <td>36.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>6</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A-4</td>\n",
       "      <td>3</td>\n",
       "      <td>39.747753</td>\n",
       "      <td>-84.205582</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45417</td>\n",
       "      <td>35.1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>7</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A-5</td>\n",
       "      <td>2</td>\n",
       "      <td>39.627781</td>\n",
       "      <td>-84.188354</td>\n",
       "      <td>Dayton</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>45459</td>\n",
       "      <td>36.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>February</td>\n",
       "      <td>7</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Early Morning</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>3513612</th>\n",
       "      <td>A-3513776</td>\n",
       "      <td>2</td>\n",
       "      <td>34.002480</td>\n",
       "      <td>-117.379360</td>\n",
       "      <td>Riverside</td>\n",
       "      <td>Riverside</td>\n",
       "      <td>CA</td>\n",
       "      <td>92501</td>\n",
       "      <td>86.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>18</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513613</th>\n",
       "      <td>A-3513777</td>\n",
       "      <td>2</td>\n",
       "      <td>32.766960</td>\n",
       "      <td>-117.148060</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>CA</td>\n",
       "      <td>92108</td>\n",
       "      <td>70.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>19</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513614</th>\n",
       "      <td>A-3513778</td>\n",
       "      <td>2</td>\n",
       "      <td>33.775450</td>\n",
       "      <td>-117.847790</td>\n",
       "      <td>Orange</td>\n",
       "      <td>Orange</td>\n",
       "      <td>CA</td>\n",
       "      <td>92866</td>\n",
       "      <td>73.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>19</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513615</th>\n",
       "      <td>A-3513779</td>\n",
       "      <td>2</td>\n",
       "      <td>33.992460</td>\n",
       "      <td>-118.403020</td>\n",
       "      <td>Culver City</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>90230</td>\n",
       "      <td>71.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>19</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3513616</th>\n",
       "      <td>A-3513780</td>\n",
       "      <td>2</td>\n",
       "      <td>34.133930</td>\n",
       "      <td>-117.230920</td>\n",
       "      <td>Highland</td>\n",
       "      <td>San Bernardino</td>\n",
       "      <td>CA</td>\n",
       "      <td>92346</td>\n",
       "      <td>79.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "      <td>18</td>\n",
       "      <td>Weekday</td>\n",
       "      <td>Late Afternoon</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3425344 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                ID  Severity   Latitude   Longitude          City  \\\n",
       "0              A-1         3  39.865147  -84.058723        Dayton   \n",
       "1              A-2         2  39.928059  -82.831184  Reynoldsburg   \n",
       "2              A-3         2  39.063148  -84.032608  Williamsburg   \n",
       "3              A-4         3  39.747753  -84.205582        Dayton   \n",
       "4              A-5         2  39.627781  -84.188354        Dayton   \n",
       "...            ...       ...        ...         ...           ...   \n",
       "3513612  A-3513776         2  34.002480 -117.379360     Riverside   \n",
       "3513613  A-3513777         2  32.766960 -117.148060     San Diego   \n",
       "3513614  A-3513778         2  33.775450 -117.847790        Orange   \n",
       "3513615  A-3513779         2  33.992460 -118.403020   Culver City   \n",
       "3513616  A-3513780         2  34.133930 -117.230920      Highland   \n",
       "\n",
       "                 County State Zipcode  Temperature_F  Visibility_miles  ...  \\\n",
       "0            Montgomery    OH   45424           36.9              10.0  ...   \n",
       "1              Franklin    OH   43068           37.9              10.0  ...   \n",
       "2              Clermont    OH   45176           36.0              10.0  ...   \n",
       "3            Montgomery    OH   45417           35.1               9.0  ...   \n",
       "4            Montgomery    OH   45459           36.0               6.0  ...   \n",
       "...                 ...   ...     ...            ...               ...  ...   \n",
       "3513612       Riverside    CA   92501           86.0              10.0  ...   \n",
       "3513613       San Diego    CA   92108           70.0              10.0  ...   \n",
       "3513614          Orange    CA   92866           73.0              10.0  ...   \n",
       "3513615     Los Angeles    CA   90230           71.0              10.0  ...   \n",
       "3513616  San Bernardino    CA   92346           79.0               7.0  ...   \n",
       "\n",
       "         Roundabout  Stop_Sign Traffic  Traffic_Light  Turning_Loop  Year  \\\n",
       "0             False      False   False          False         False  2016   \n",
       "1             False      False   False          False         False  2016   \n",
       "2             False      False   False           True         False  2016   \n",
       "3             False      False   False          False         False  2016   \n",
       "4             False      False   False           True         False  2016   \n",
       "...             ...        ...     ...            ...           ...   ...   \n",
       "3513612       False      False   False          False         False  2019   \n",
       "3513613       False      False   False          False         False  2019   \n",
       "3513614       False      False   False          False         False  2019   \n",
       "3513615       False      False   False          False         False  2019   \n",
       "3513616       False      False   False          False         False  2019   \n",
       "\n",
       "            Month  Time  Part_of_Week     Time_of_Day  \n",
       "0        February     5       Weekday   Early Morning  \n",
       "1        February     6       Weekday   Early Morning  \n",
       "2        February     6       Weekday   Early Morning  \n",
       "3        February     7       Weekday   Early Morning  \n",
       "4        February     7       Weekday   Early Morning  \n",
       "...           ...   ...           ...             ...  \n",
       "3513612    August    18       Weekday  Late Afternoon  \n",
       "3513613    August    19       Weekday  Late Afternoon  \n",
       "3513614    August    19       Weekday  Late Afternoon  \n",
       "3513615    August    19       Weekday  Late Afternoon  \n",
       "3513616    August    18       Weekday  Late Afternoon  \n",
       "\n",
       "[3425344 rows x 29 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 794937 entries, 728 to 3513616\n",
      "Data columns (total 28 columns):\n",
      " #   Column                Non-Null Count   Dtype  \n",
      "---  ------                --------------   -----  \n",
      " 0   ID                    794937 non-null  object \n",
      " 1   Severity              794937 non-null  int64  \n",
      " 2   Latitude              794937 non-null  float64\n",
      " 3   Longitude             794937 non-null  float64\n",
      " 4   City                  794937 non-null  object \n",
      " 5   County                794937 non-null  object \n",
      " 6   Zipcode               794937 non-null  object \n",
      " 7   Temperature_F         794937 non-null  float64\n",
      " 8   Visibility_miles      794937 non-null  float64\n",
      " 9   Wind_Speed_mph        794937 non-null  float64\n",
      " 10  Precipitation_inches  794937 non-null  float64\n",
      " 11  Weather_Condition     794937 non-null  object \n",
      " 12  Speed_Bump            794937 non-null  bool   \n",
      " 13  Cross_Walk            794937 non-null  bool   \n",
      " 14  Yield_Sign            794937 non-null  bool   \n",
      " 15  Intersection          794937 non-null  bool   \n",
      " 16  No_Exit               794937 non-null  bool   \n",
      " 17  Railway               794937 non-null  bool   \n",
      " 18  Roundabout            794937 non-null  bool   \n",
      " 19  Stop_Sign             794937 non-null  bool   \n",
      " 20  Traffic               794937 non-null  bool   \n",
      " 21  Traffic_Light         794937 non-null  bool   \n",
      " 22  Turning_Loop          794937 non-null  bool   \n",
      " 23  Year                  794937 non-null  int64  \n",
      " 24  Month                 794937 non-null  object \n",
      " 25  Time                  794937 non-null  int32  \n",
      " 26  Part_of_Week          794937 non-null  object \n",
      " 27  Time_of_Day           794937 non-null  object \n",
      "dtypes: bool(11), float64(6), int32(1), int64(2), object(8)\n",
      "memory usage: 114.5+ MB\n"
     ]
    }
   ],
   "source": [
    "# Keep on values from California, drop all other states\n",
    "\n",
    "state='CA'\n",
    "df_state=cleaned_df.loc[cleaned_df.State==state].copy()\n",
    "df_state.drop('State',axis=1, inplace=True)\n",
    "df_state.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Save and Import CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create cleaned output file (CSV)\n",
    "\n",
    "output_data_file = \"../resources/Cleaned_Accidents_Data_v4_CA_ONLY.csv\"\n",
    "df_state.to_csv(output_data_file, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Access postgresSQL database with URL\n",
    "\n",
    "db_string = f\"postgresql://{username}:{password}@127.0.0.1:{port}/{db_name}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "importing rows 0 to 50000...Done. 10.850847959518433 total seconds elapsed\n",
      "importing rows 50000 to 100000...Done. 21.16127872467041 total seconds elapsed\n",
      "importing rows 100000 to 150000...Done. 31.367558240890503 total seconds elapsed\n",
      "importing rows 150000 to 200000...Done. 41.83163833618164 total seconds elapsed\n",
      "importing rows 200000 to 250000...Done. 52.19134306907654 total seconds elapsed\n",
      "importing rows 250000 to 300000...Done. 62.7229790687561 total seconds elapsed\n",
      "importing rows 300000 to 350000...Done. 72.99222302436829 total seconds elapsed\n",
      "importing rows 350000 to 400000...Done. 83.56579375267029 total seconds elapsed\n",
      "importing rows 400000 to 450000...Done. 93.7238392829895 total seconds elapsed\n",
      "importing rows 450000 to 500000...Done. 103.9193320274353 total seconds elapsed\n",
      "importing rows 500000 to 550000...Done. 114.37866711616516 total seconds elapsed\n",
      "importing rows 550000 to 600000...Done. 125.32266116142273 total seconds elapsed\n",
      "importing rows 600000 to 650000...Done. 135.42476081848145 total seconds elapsed\n",
      "importing rows 650000 to 700000...Done. 145.45652651786804 total seconds elapsed\n",
      "importing rows 700000 to 750000...Done. 155.57762026786804 total seconds elapsed\n",
      "importing rows 750000 to 794937...Done. 164.44513988494873 total seconds elapsed\n"
     ]
    }
   ],
   "source": [
    "#Connect to postgresSQL via engine\n",
    "\n",
    "engine = create_engine(db_string)\n",
    "\n",
    "#Test postgres importing table via chunked sized rows\n",
    "\n",
    "rows_imported = 0\n",
    "\n",
    "start_time = time.time()\n",
    "\n",
    "for data in pd.read_csv(output_data_file, chunksize=50000):\n",
    "    print(f'importing rows {rows_imported} to {rows_imported + len(data)}...', end='')\n",
    "    data.to_sql(name=\"accidents_CA_only_v2_tbl\", con=engine, if_exists='append')\n",
    "    rows_imported += len(data)\n",
    "    print(f'Done. {time.time() - start_time} total seconds elapsed')"
   ]
  }
 ],
 "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
}