Making Dataset.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e406a907",
   "metadata": {},
   "source": [
    "# Final Project (Playing with data)\n",
    "### Group Member:\n",
    "### Akshant Churi, Smit Malik, Samruddhi Choudhari\n",
    "\n",
    "Our group project is to analyze Chicago road crashes by comparing general crashes information, vehicle crashes, crashes were people are involved. We further plan to add speed camera violation and red light camera violation to see if this areas are prone to crashes.\n",
    "\n",
    "We plan to use data from Chicago Govt Portal website\n",
    "\n",
    "All Data sources which we plan to use during this project:\n",
    "1. crashes dataset https://data.cityofchicago.org/Transportation/Traffic-Crashes-Crashes/85ca-t3if\n",
    "\n",
    "2. vehicle dataset\n",
    "https://data.cityofchicago.org/Transportation/Traffic-Crashes-Vehicles/68nd-jvt3\n",
    "\n",
    "3. people dataset\n",
    "https://data.cityofchicago.org/Transportation/Traffic-Crashes-People/u6pd-qa9d\n",
    "\n",
    "4. red light camera violation\n",
    "https://data.cityofchicago.org/Transportation/Red-Light-Camera-Violations/spqx-js37\n",
    "\n",
    "5. speed camera violation\n",
    "https://data.cityofchicago.org/Transportation/Speed-Camera-Violations/hhkd-xvj4\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09403452",
   "metadata": {},
   "source": [
    "### How this project will be understood by people not an expert in this dataset:\n",
    "The project aims to analyze the areas that are the most prone to accidents in Chicago based on red light and speed violations. Furthermore, vehicle datasets can be used to understand the crashes better. The dashboard has easy drag and drop functions that the user can use to understand more about the project. It also has colorful charts which are clearly labeled for anyone not an expert in the dataset.\n",
    "\n",
    "The plot 1 shows the relationship between red light violation and frequency over years from 2014 to 2019. Here the chart has a drop down function for x axis which is the red light violation year. The other interactive function is the slider for the year till which the user can see.\n",
    "\n",
    "The plot 2 shows relationship between weather condition and count over the 12  months ranging from 1-12. The user can simply drag on the area in the first chart to see changes in the next one. \n",
    "\n",
    "The Plot 3 shows how the age distribution regarding the crashes and how crashes changed according to the years\n",
    "\n",
    "The plot 4 shows the relation between datasets red light and speed violations based on the crashes. The user can simply use the drop down to select a street he/she is interested in and see the relation between all 3 attributes. The street is highlighted in yellow while the red lights in red and speed violation in green color. This gives the user a good visualization on the areas that are the most affected by the any of the two scenarios.\n",
    "\n",
    "### Our Plan for large dataset:\n",
    "\n",
    "The datasets that we are planning to use are majorly bigger than 300MB which goes way beyond the github’s threshold. Our first priority is to divide the dataset into multiple chunks depending on the plot. Each dataset will be dedicated to a specific or a few plots. \n",
    "If this plan does not come into effect, we will be creating a folder that would consist of the links to the datasets which are stored in the Google drive. Anyone with the link can access those datasets.\n",
    "For example we made a file called as viz1, viz2 etc which are chunks or aggragated information. \n",
    "Note: we have included chunk datasets as well\n",
    "\n",
    "### Contextual Dataset:\n",
    "‘Red-Light-Camera-Violation’ and ‘Speed-Camera-Violation. These datasets will help in better analysis of Chicago crashes and support the main datasets. Our main goal here is to assess the relation between the location of crashes and the location of speed camera violations as well as the location of red light camera violations. This will help us analyze streets that are more prone to crashes.  \n",
    "##### source to the contextual datasets:\n",
    "1. red light camera violation\n",
    "https://data.cityofchicago.org/Transportation/Red-Light-Camera-Violations/spqx-js37\n",
    "\n",
    "2. speed camera violation\n",
    "https://data.cityofchicago.org/Transportation/Speed-Camera-Violations/hhkd-xvj4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "186441d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#libraries and packages\n",
    "import pandas as pd\n",
    "from dateutil import parser\n",
    "import ipywidgets\n",
    "from ipywidgets import interactive\n",
    "import matplotlib.pyplot as plt\n",
    "import altair as alt\n",
    "import geopandas as gdp"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97329d63",
   "metadata": {},
   "source": [
    "###  Reading the datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "3f023284",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/c5/3sw9j_052v3bcylbyvj179m40000gn/T/ipykernel_69616/2696632773.py:4: DtypeWarning: Columns (19,21,40,41,42,44,48,49,50,53,55,58,59,60,61,71) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df2 = pd.read_csv('Traffic_Crashes_-_Vehicles.csv') #vehicles\n",
      "/var/folders/c5/3sw9j_052v3bcylbyvj179m40000gn/T/ipykernel_69616/2696632773.py:5: DtypeWarning: Columns (20,24,25,26,29) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df3 = pd.read_csv('Traffic_Crashes_-_People.csv') #people\n"
     ]
    }
   ],
   "source": [
    "#reading datasets\n",
    "#this files can be downloaded from the above links\n",
    "df1 = pd.read_csv('Traffic_Crashes_-_Crashes.csv') #crashes\n",
    "df2 = pd.read_csv('Traffic_Crashes_-_Vehicles.csv') #vehicles\n",
    "df3 = pd.read_csv('Traffic_Crashes_-_People.csv') #people\n",
    "df4 = pd.read_csv('Red_Light_Camera_Violations.csv') #red_light violation\n",
    "df5 = pd.read_csv('Speed_Camera_Violations.csv') #speed camera violation\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bcd8825",
   "metadata": {},
   "source": [
    "# Exploratory Data Analysis for all the datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14aa6d14",
   "metadata": {},
   "source": [
    "### Dataframe 1, Crashes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "dafbcd1c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CRASH_RECORD_ID</th>\n",
       "      <th>RD_NO</th>\n",
       "      <th>CRASH_DATE_EST_I</th>\n",
       "      <th>CRASH_DATE</th>\n",
       "      <th>POSTED_SPEED_LIMIT</th>\n",
       "      <th>TRAFFIC_CONTROL_DEVICE</th>\n",
       "      <th>DEVICE_CONDITION</th>\n",
       "      <th>WEATHER_CONDITION</th>\n",
       "      <th>LIGHTING_CONDITION</th>\n",
       "      <th>FIRST_CRASH_TYPE</th>\n",
       "      <th>...</th>\n",
       "      <th>INJURIES_NON_INCAPACITATING</th>\n",
       "      <th>INJURIES_REPORTED_NOT_EVIDENT</th>\n",
       "      <th>INJURIES_NO_INDICATION</th>\n",
       "      <th>INJURIES_UNKNOWN</th>\n",
       "      <th>CRASH_HOUR</th>\n",
       "      <th>CRASH_DAY_OF_WEEK</th>\n",
       "      <th>CRASH_MONTH</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>LOCATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>79c7a2ce89f446262efd86df3d72d18b04ba487024b7c4...</td>\n",
       "      <td>JC199149</td>\n",
       "      <td>NaN</td>\n",
       "      <td>03/25/2019 02:43:00 PM</td>\n",
       "      <td>30</td>\n",
       "      <td>TRAFFIC SIGNAL</td>\n",
       "      <td>FUNCTIONING PROPERLY</td>\n",
       "      <td>CLEAR</td>\n",
       "      <td>DAYLIGHT</td>\n",
       "      <td>TURNING</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>41.884547</td>\n",
       "      <td>-87.641201</td>\n",
       "      <td>POINT (-87.64120093714 41.884547224337)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>792b539deaaad65ee5b4a9691d927a34d298eb33d42af0...</td>\n",
       "      <td>JB422857</td>\n",
       "      <td>NaN</td>\n",
       "      <td>09/05/2018 08:40:00 AM</td>\n",
       "      <td>30</td>\n",
       "      <td>NO CONTROLS</td>\n",
       "      <td>NO CONTROLS</td>\n",
       "      <td>CLEAR</td>\n",
       "      <td>DAYLIGHT</td>\n",
       "      <td>ANGLE</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>41.968562</td>\n",
       "      <td>-87.740659</td>\n",
       "      <td>POINT (-87.740659314632 41.968562453871)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0115ade9a755e835255508463f7e9c4a9a0b47e9304238...</td>\n",
       "      <td>JF318029</td>\n",
       "      <td>NaN</td>\n",
       "      <td>07/15/2022 12:45:00 AM</td>\n",
       "      <td>30</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>CLEAR</td>\n",
       "      <td>DARKNESS, LIGHTED ROAD</td>\n",
       "      <td>ANGLE</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>41.886336</td>\n",
       "      <td>-87.716203</td>\n",
       "      <td>POINT (-87.716203130599 41.886336409761)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05b1982cdba5d8a00e7e76ad1ecdab0e598429f78481d2...</td>\n",
       "      <td>JF378711</td>\n",
       "      <td>NaN</td>\n",
       "      <td>08/29/2022 11:30:00 AM</td>\n",
       "      <td>30</td>\n",
       "      <td>TRAFFIC SIGNAL</td>\n",
       "      <td>FUNCTIONING PROPERLY</td>\n",
       "      <td>CLEAR</td>\n",
       "      <td>DAYLIGHT</td>\n",
       "      <td>REAR END</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>41.749348</td>\n",
       "      <td>-87.721097</td>\n",
       "      <td>POINT (-87.721096727406 41.749348170421)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>017040c61958d2fa977c956b2bd2d6759ef7754496dc96...</td>\n",
       "      <td>JF324552</td>\n",
       "      <td>NaN</td>\n",
       "      <td>07/15/2022 06:50:00 PM</td>\n",
       "      <td>30</td>\n",
       "      <td>TRAFFIC SIGNAL</td>\n",
       "      <td>FUNCTIONING PROPERLY</td>\n",
       "      <td>CLEAR</td>\n",
       "      <td>DAYLIGHT</td>\n",
       "      <td>REAR END</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>41.925111</td>\n",
       "      <td>-87.667997</td>\n",
       "      <td>POINT (-87.667997321599 41.925110815832)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 49 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     CRASH_RECORD_ID     RD_NO  \\\n",
       "0  79c7a2ce89f446262efd86df3d72d18b04ba487024b7c4...  JC199149   \n",
       "1  792b539deaaad65ee5b4a9691d927a34d298eb33d42af0...  JB422857   \n",
       "2  0115ade9a755e835255508463f7e9c4a9a0b47e9304238...  JF318029   \n",
       "3  05b1982cdba5d8a00e7e76ad1ecdab0e598429f78481d2...  JF378711   \n",
       "4  017040c61958d2fa977c956b2bd2d6759ef7754496dc96...  JF324552   \n",
       "\n",
       "  CRASH_DATE_EST_I              CRASH_DATE  POSTED_SPEED_LIMIT  \\\n",
       "0              NaN  03/25/2019 02:43:00 PM                  30   \n",
       "1              NaN  09/05/2018 08:40:00 AM                  30   \n",
       "2              NaN  07/15/2022 12:45:00 AM                  30   \n",
       "3              NaN  08/29/2022 11:30:00 AM                  30   \n",
       "4              NaN  07/15/2022 06:50:00 PM                  30   \n",
       "\n",
       "  TRAFFIC_CONTROL_DEVICE      DEVICE_CONDITION WEATHER_CONDITION  \\\n",
       "0         TRAFFIC SIGNAL  FUNCTIONING PROPERLY             CLEAR   \n",
       "1            NO CONTROLS           NO CONTROLS             CLEAR   \n",
       "2                UNKNOWN               UNKNOWN             CLEAR   \n",
       "3         TRAFFIC SIGNAL  FUNCTIONING PROPERLY             CLEAR   \n",
       "4         TRAFFIC SIGNAL  FUNCTIONING PROPERLY             CLEAR   \n",
       "\n",
       "       LIGHTING_CONDITION FIRST_CRASH_TYPE  ... INJURIES_NON_INCAPACITATING  \\\n",
       "0                DAYLIGHT          TURNING  ...                         0.0   \n",
       "1                DAYLIGHT            ANGLE  ...                         0.0   \n",
       "2  DARKNESS, LIGHTED ROAD            ANGLE  ...                         0.0   \n",
       "3                DAYLIGHT         REAR END  ...                         0.0   \n",
       "4                DAYLIGHT         REAR END  ...                         0.0   \n",
       "\n",
       "   INJURIES_REPORTED_NOT_EVIDENT INJURIES_NO_INDICATION INJURIES_UNKNOWN  \\\n",
       "0                            1.0                    2.0              0.0   \n",
       "1                            0.0                    2.0              0.0   \n",
       "2                            0.0                    2.0              0.0   \n",
       "3                            0.0                    3.0              0.0   \n",
       "4                            0.0                    2.0              0.0   \n",
       "\n",
       "  CRASH_HOUR CRASH_DAY_OF_WEEK CRASH_MONTH   LATITUDE  LONGITUDE  \\\n",
       "0         14                 2           3  41.884547 -87.641201   \n",
       "1          8                 4           9  41.968562 -87.740659   \n",
       "2          0                 6           7  41.886336 -87.716203   \n",
       "3         11                 2           8  41.749348 -87.721097   \n",
       "4         18                 6           7  41.925111 -87.667997   \n",
       "\n",
       "                                   LOCATION  \n",
       "0   POINT (-87.64120093714 41.884547224337)  \n",
       "1  POINT (-87.740659314632 41.968562453871)  \n",
       "2  POINT (-87.716203130599 41.886336409761)  \n",
       "3  POINT (-87.721096727406 41.749348170421)  \n",
       "4  POINT (-87.667997321599 41.925110815832)  \n",
       "\n",
       "[5 rows x 49 columns]"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head() #top 5 elements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "39bcee18",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(671657, 49)"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.shape #dimension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "873a8827",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "column:  CRASH_RECORD_ID\n",
      "unique values:  671657\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  RD_NO\n",
      "unique values:  667344\n",
      "null values:  4314\n",
      "----------------------\n",
      "column:  CRASH_DATE_EST_I\n",
      "unique values:  3\n",
      "null values:  620780\n",
      "----------------------\n",
      "column:  CRASH_DATE\n",
      "unique values:  439050\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  POSTED_SPEED_LIMIT\n",
      "unique values:  44\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  TRAFFIC_CONTROL_DEVICE\n",
      "unique values:  19\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  DEVICE_CONDITION\n",
      "unique values:  8\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  WEATHER_CONDITION\n",
      "unique values:  12\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  LIGHTING_CONDITION\n",
      "unique values:  6\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  FIRST_CRASH_TYPE\n",
      "unique values:  18\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  TRAFFICWAY_TYPE\n",
      "unique values:  20\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  LANE_CNT\n",
      "unique values:  42\n",
      "null values:  472663\n",
      "----------------------\n",
      "column:  ALIGNMENT\n",
      "unique values:  6\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  ROADWAY_SURFACE_COND\n",
      "unique values:  7\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  ROAD_DEFECT\n",
      "unique values:  7\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  REPORT_TYPE\n",
      "unique values:  4\n",
      "null values:  18120\n",
      "----------------------\n",
      "column:  CRASH_TYPE\n",
      "unique values:  2\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  INTERSECTION_RELATED_I\n",
      "unique values:  3\n",
      "null values:  517872\n",
      "----------------------\n",
      "column:  NOT_RIGHT_OF_WAY_I\n",
      "unique values:  3\n",
      "null values:  640126\n",
      "----------------------\n",
      "column:  HIT_AND_RUN_I\n",
      "unique values:  3\n",
      "null values:  463966\n",
      "----------------------\n",
      "column:  DAMAGE\n",
      "unique values:  3\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  DATE_POLICE_NOTIFIED\n",
      "unique values:  511090\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  PRIM_CONTRIBUTORY_CAUSE\n",
      "unique values:  40\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  SEC_CONTRIBUTORY_CAUSE\n",
      "unique values:  40\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  STREET_NO\n",
      "unique values:  11494\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  STREET_DIRECTION\n",
      "unique values:  5\n",
      "null values:  4\n",
      "----------------------\n",
      "column:  STREET_NAME\n",
      "unique values:  1613\n",
      "null values:  1\n",
      "----------------------\n",
      "column:  BEAT_OF_OCCURRENCE\n",
      "unique values:  276\n",
      "null values:  5\n",
      "----------------------\n",
      "column:  PHOTOS_TAKEN_I\n",
      "unique values:  3\n",
      "null values:  663374\n",
      "----------------------\n",
      "column:  STATEMENTS_TAKEN_I\n",
      "unique values:  3\n",
      "null values:  657712\n",
      "----------------------\n",
      "column:  DOORING_I\n",
      "unique values:  3\n",
      "null values:  669558\n",
      "----------------------\n",
      "column:  WORK_ZONE_I\n",
      "unique values:  3\n",
      "null values:  667689\n",
      "----------------------\n",
      "column:  WORK_ZONE_TYPE\n",
      "unique values:  5\n",
      "null values:  668542\n",
      "----------------------\n",
      "column:  WORKERS_PRESENT_I\n",
      "unique values:  3\n",
      "null values:  670632\n",
      "----------------------\n",
      "column:  NUM_UNITS\n",
      "unique values:  16\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  MOST_SEVERE_INJURY\n",
      "unique values:  6\n",
      "null values:  1454\n",
      "----------------------\n",
      "column:  INJURIES_TOTAL\n",
      "unique values:  19\n",
      "null values:  1443\n",
      "----------------------\n",
      "column:  INJURIES_FATAL\n",
      "unique values:  6\n",
      "null values:  1443\n",
      "----------------------\n",
      "column:  INJURIES_INCAPACITATING\n",
      "unique values:  9\n",
      "null values:  1443\n",
      "----------------------\n",
      "column:  INJURIES_NON_INCAPACITATING\n",
      "unique values:  19\n",
      "null values:  1443\n",
      "----------------------\n",
      "column:  INJURIES_REPORTED_NOT_EVIDENT\n",
      "unique values:  14\n",
      "null values:  1443\n",
      "----------------------\n",
      "column:  INJURIES_NO_INDICATION\n",
      "unique values:  44\n",
      "null values:  1443\n",
      "----------------------\n",
      "column:  INJURIES_UNKNOWN\n",
      "unique values:  2\n",
      "null values:  1443\n",
      "----------------------\n",
      "column:  CRASH_HOUR\n",
      "unique values:  24\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  CRASH_DAY_OF_WEEK\n",
      "unique values:  7\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  CRASH_MONTH\n",
      "unique values:  12\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  LATITUDE\n",
      "unique values:  264346\n",
      "null values:  4214\n",
      "----------------------\n",
      "column:  LONGITUDE\n",
      "unique values:  264322\n",
      "null values:  4214\n",
      "----------------------\n",
      "column:  LOCATION\n",
      "unique values:  264488\n",
      "null values:  4214\n",
      "----------------------\n"
     ]
    }
   ],
   "source": [
    "#for loop to analyze each column\n",
    "\n",
    "for i in list(df1.columns):\n",
    "    print('column: ',i)\n",
    "    print('unique values: ',len(df1[i].unique()))\n",
    "    print('null values: ',df1[i].isnull().sum())\n",
    "    print('----------------------')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "76128190",
   "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>POSTED_SPEED_LIMIT</th>\n",
       "      <th>LANE_CNT</th>\n",
       "      <th>STREET_NO</th>\n",
       "      <th>BEAT_OF_OCCURRENCE</th>\n",
       "      <th>NUM_UNITS</th>\n",
       "      <th>INJURIES_TOTAL</th>\n",
       "      <th>INJURIES_FATAL</th>\n",
       "      <th>INJURIES_INCAPACITATING</th>\n",
       "      <th>INJURIES_NON_INCAPACITATING</th>\n",
       "      <th>INJURIES_REPORTED_NOT_EVIDENT</th>\n",
       "      <th>INJURIES_NO_INDICATION</th>\n",
       "      <th>INJURIES_UNKNOWN</th>\n",
       "      <th>CRASH_HOUR</th>\n",
       "      <th>CRASH_DAY_OF_WEEK</th>\n",
       "      <th>CRASH_MONTH</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>671657.000000</td>\n",
       "      <td>1.989940e+05</td>\n",
       "      <td>671657.000000</td>\n",
       "      <td>671652.000000</td>\n",
       "      <td>671657.000000</td>\n",
       "      <td>670214.000000</td>\n",
       "      <td>670214.000000</td>\n",
       "      <td>670214.000000</td>\n",
       "      <td>670214.000000</td>\n",
       "      <td>670214.000000</td>\n",
       "      <td>670214.000000</td>\n",
       "      <td>670214.0</td>\n",
       "      <td>671657.000000</td>\n",
       "      <td>671657.000000</td>\n",
       "      <td>671657.000000</td>\n",
       "      <td>667443.000000</td>\n",
       "      <td>667443.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>28.364278</td>\n",
       "      <td>1.333096e+01</td>\n",
       "      <td>3681.166478</td>\n",
       "      <td>1238.830256</td>\n",
       "      <td>2.034588</td>\n",
       "      <td>0.185408</td>\n",
       "      <td>0.001164</td>\n",
       "      <td>0.019974</td>\n",
       "      <td>0.104488</td>\n",
       "      <td>0.059782</td>\n",
       "      <td>2.008363</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.217578</td>\n",
       "      <td>4.126329</td>\n",
       "      <td>6.730943</td>\n",
       "      <td>41.854498</td>\n",
       "      <td>-87.673352</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>6.280285</td>\n",
       "      <td>2.961713e+03</td>\n",
       "      <td>2899.280216</td>\n",
       "      <td>705.464563</td>\n",
       "      <td>0.451561</td>\n",
       "      <td>0.558534</td>\n",
       "      <td>0.037112</td>\n",
       "      <td>0.164951</td>\n",
       "      <td>0.416064</td>\n",
       "      <td>0.314412</td>\n",
       "      <td>1.158862</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.550830</td>\n",
       "      <td>1.980133</td>\n",
       "      <td>3.372872</td>\n",
       "      <td>0.331316</td>\n",
       "      <td>0.672790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>111.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-87.936193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>30.000000</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>1232.000000</td>\n",
       "      <td>712.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>41.781228</td>\n",
       "      <td>-87.721426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>30.000000</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>3200.000000</td>\n",
       "      <td>1135.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>41.874516</td>\n",
       "      <td>-87.673667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>30.000000</td>\n",
       "      <td>4.000000e+00</td>\n",
       "      <td>5600.000000</td>\n",
       "      <td>1822.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>41.924021</td>\n",
       "      <td>-87.632994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>99.000000</td>\n",
       "      <td>1.191625e+06</td>\n",
       "      <td>451100.000000</td>\n",
       "      <td>6100.000000</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>61.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>42.022780</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       POSTED_SPEED_LIMIT      LANE_CNT      STREET_NO  BEAT_OF_OCCURRENCE  \\\n",
       "count       671657.000000  1.989940e+05  671657.000000       671652.000000   \n",
       "mean            28.364278  1.333096e+01    3681.166478         1238.830256   \n",
       "std              6.280285  2.961713e+03    2899.280216          705.464563   \n",
       "min              0.000000  0.000000e+00       0.000000          111.000000   \n",
       "25%             30.000000  2.000000e+00    1232.000000          712.000000   \n",
       "50%             30.000000  2.000000e+00    3200.000000         1135.000000   \n",
       "75%             30.000000  4.000000e+00    5600.000000         1822.000000   \n",
       "max             99.000000  1.191625e+06  451100.000000         6100.000000   \n",
       "\n",
       "           NUM_UNITS  INJURIES_TOTAL  INJURIES_FATAL  INJURIES_INCAPACITATING  \\\n",
       "count  671657.000000   670214.000000   670214.000000            670214.000000   \n",
       "mean        2.034588        0.185408        0.001164                 0.019974   \n",
       "std         0.451561        0.558534        0.037112                 0.164951   \n",
       "min         1.000000        0.000000        0.000000                 0.000000   \n",
       "25%         2.000000        0.000000        0.000000                 0.000000   \n",
       "50%         2.000000        0.000000        0.000000                 0.000000   \n",
       "75%         2.000000        0.000000        0.000000                 0.000000   \n",
       "max        18.000000       21.000000        4.000000                 7.000000   \n",
       "\n",
       "       INJURIES_NON_INCAPACITATING  INJURIES_REPORTED_NOT_EVIDENT  \\\n",
       "count                670214.000000                  670214.000000   \n",
       "mean                      0.104488                       0.059782   \n",
       "std                       0.416064                       0.314412   \n",
       "min                       0.000000                       0.000000   \n",
       "25%                       0.000000                       0.000000   \n",
       "50%                       0.000000                       0.000000   \n",
       "75%                       0.000000                       0.000000   \n",
       "max                      21.000000                      15.000000   \n",
       "\n",
       "       INJURIES_NO_INDICATION  INJURIES_UNKNOWN     CRASH_HOUR  \\\n",
       "count           670214.000000          670214.0  671657.000000   \n",
       "mean                 2.008363               0.0      13.217578   \n",
       "std                  1.158862               0.0       5.550830   \n",
       "min                  0.000000               0.0       0.000000   \n",
       "25%                  1.000000               0.0       9.000000   \n",
       "50%                  2.000000               0.0      14.000000   \n",
       "75%                  2.000000               0.0      17.000000   \n",
       "max                 61.000000               0.0      23.000000   \n",
       "\n",
       "       CRASH_DAY_OF_WEEK    CRASH_MONTH       LATITUDE      LONGITUDE  \n",
       "count      671657.000000  671657.000000  667443.000000  667443.000000  \n",
       "mean            4.126329       6.730943      41.854498     -87.673352  \n",
       "std             1.980133       3.372872       0.331316       0.672790  \n",
       "min             1.000000       1.000000       0.000000     -87.936193  \n",
       "25%             2.000000       4.000000      41.781228     -87.721426  \n",
       "50%             4.000000       7.000000      41.874516     -87.673667  \n",
       "75%             6.000000      10.000000      41.924021     -87.632994  \n",
       "max             7.000000      12.000000      42.022780       0.000000  "
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.describe() "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ddd507d",
   "metadata": {},
   "source": [
    "we are further eliminating columns which have more than 30% of the data not available"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "5949253b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['CRASH_DATE_EST_I',\n",
       " 'LANE_CNT',\n",
       " 'INTERSECTION_RELATED_I',\n",
       " 'NOT_RIGHT_OF_WAY_I',\n",
       " 'HIT_AND_RUN_I',\n",
       " 'PHOTOS_TAKEN_I',\n",
       " 'STATEMENTS_TAKEN_I',\n",
       " 'DOORING_I',\n",
       " 'WORK_ZONE_I',\n",
       " 'WORK_ZONE_TYPE',\n",
       " 'WORKERS_PRESENT_I']"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bd_1 = [x for x in df1.columns if df1[x].isnull().mean() >= 0.3]\n",
    "bd_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "02d2f51d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(671657, 38)"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.drop(bd_1, inplace= True, axis = 1)# remove columns with a lot of null values\n",
    "df1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "93c6b167",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CRASH_RECORD_ID</th>\n",
       "      <th>STREET_NAME</th>\n",
       "      <th>CRASH_DATE</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>POSTED_SPEED_LIMIT</th>\n",
       "      <th>WEATHER_CONDITION</th>\n",
       "      <th>LIGHTING_CONDITION</th>\n",
       "      <th>ROADWAY_SURFACE_COND</th>\n",
       "      <th>DAMAGE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>79c7a2ce89f446262efd86df3d72d18b04ba487024b7c4...</td>\n",
       "      <td>RANDOLPH ST</td>\n",
       "      <td>03/25/2019 02:43:00 PM</td>\n",
       "      <td>41.884547</td>\n",
       "      <td>-87.641201</td>\n",
       "      <td>30</td>\n",
       "      <td>CLEAR</td>\n",
       "      <td>DAYLIGHT</td>\n",
       "      <td>DRY</td>\n",
       "      <td>OVER $1,500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>792b539deaaad65ee5b4a9691d927a34d298eb33d42af0...</td>\n",
       "      <td>ELSTON AVE</td>\n",
       "      <td>09/05/2018 08:40:00 AM</td>\n",
       "      <td>41.968562</td>\n",
       "      <td>-87.740659</td>\n",
       "      <td>30</td>\n",
       "      <td>CLEAR</td>\n",
       "      <td>DAYLIGHT</td>\n",
       "      <td>DRY</td>\n",
       "      <td>OVER $1,500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0115ade9a755e835255508463f7e9c4a9a0b47e9304238...</td>\n",
       "      <td>CENTRAL PARK AVE</td>\n",
       "      <td>07/15/2022 12:45:00 AM</td>\n",
       "      <td>41.886336</td>\n",
       "      <td>-87.716203</td>\n",
       "      <td>30</td>\n",
       "      <td>CLEAR</td>\n",
       "      <td>DARKNESS, LIGHTED ROAD</td>\n",
       "      <td>DRY</td>\n",
       "      <td>OVER $1,500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05b1982cdba5d8a00e7e76ad1ecdab0e598429f78481d2...</td>\n",
       "      <td>79TH ST</td>\n",
       "      <td>08/29/2022 11:30:00 AM</td>\n",
       "      <td>41.749348</td>\n",
       "      <td>-87.721097</td>\n",
       "      <td>30</td>\n",
       "      <td>CLEAR</td>\n",
       "      <td>DAYLIGHT</td>\n",
       "      <td>DRY</td>\n",
       "      <td>$501 - $1,500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>017040c61958d2fa977c956b2bd2d6759ef7754496dc96...</td>\n",
       "      <td>ASHLAND AVE</td>\n",
       "      <td>07/15/2022 06:50:00 PM</td>\n",
       "      <td>41.925111</td>\n",
       "      <td>-87.667997</td>\n",
       "      <td>30</td>\n",
       "      <td>CLEAR</td>\n",
       "      <td>DAYLIGHT</td>\n",
       "      <td>DRY</td>\n",
       "      <td>OVER $1,500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     CRASH_RECORD_ID       STREET_NAME  \\\n",
       "0  79c7a2ce89f446262efd86df3d72d18b04ba487024b7c4...       RANDOLPH ST   \n",
       "1  792b539deaaad65ee5b4a9691d927a34d298eb33d42af0...        ELSTON AVE   \n",
       "2  0115ade9a755e835255508463f7e9c4a9a0b47e9304238...  CENTRAL PARK AVE   \n",
       "3  05b1982cdba5d8a00e7e76ad1ecdab0e598429f78481d2...           79TH ST   \n",
       "4  017040c61958d2fa977c956b2bd2d6759ef7754496dc96...       ASHLAND AVE   \n",
       "\n",
       "               CRASH_DATE   LATITUDE  LONGITUDE  POSTED_SPEED_LIMIT  \\\n",
       "0  03/25/2019 02:43:00 PM  41.884547 -87.641201                  30   \n",
       "1  09/05/2018 08:40:00 AM  41.968562 -87.740659                  30   \n",
       "2  07/15/2022 12:45:00 AM  41.886336 -87.716203                  30   \n",
       "3  08/29/2022 11:30:00 AM  41.749348 -87.721097                  30   \n",
       "4  07/15/2022 06:50:00 PM  41.925111 -87.667997                  30   \n",
       "\n",
       "  WEATHER_CONDITION      LIGHTING_CONDITION ROADWAY_SURFACE_COND  \\\n",
       "0             CLEAR                DAYLIGHT                  DRY   \n",
       "1             CLEAR                DAYLIGHT                  DRY   \n",
       "2             CLEAR  DARKNESS, LIGHTED ROAD                  DRY   \n",
       "3             CLEAR                DAYLIGHT                  DRY   \n",
       "4             CLEAR                DAYLIGHT                  DRY   \n",
       "\n",
       "          DAMAGE  \n",
       "0    OVER $1,500  \n",
       "1    OVER $1,500  \n",
       "2    OVER $1,500  \n",
       "3  $501 - $1,500  \n",
       "4    OVER $1,500  "
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#selecting useful columns for further analysis\n",
    "df1_1=df1[['CRASH_RECORD_ID','STREET_NAME','CRASH_DATE','LATITUDE', 'LONGITUDE','POSTED_SPEED_LIMIT','WEATHER_CONDITION', 'LIGHTING_CONDITION','ROADWAY_SURFACE_COND','DAMAGE']]\n",
    "df1_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "ba9f3017",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(671657, 10)"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1_1.shape #shape after modification"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "887590cd",
   "metadata": {},
   "source": [
    "### Dataframe 2, Vehicle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "c8d7aed4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CRASH_UNIT_ID</th>\n",
       "      <th>CRASH_RECORD_ID</th>\n",
       "      <th>RD_NO</th>\n",
       "      <th>CRASH_DATE</th>\n",
       "      <th>UNIT_NO</th>\n",
       "      <th>UNIT_TYPE</th>\n",
       "      <th>NUM_PASSENGERS</th>\n",
       "      <th>VEHICLE_ID</th>\n",
       "      <th>CMRC_VEH_I</th>\n",
       "      <th>MAKE</th>\n",
       "      <th>...</th>\n",
       "      <th>TRAILER1_LENGTH</th>\n",
       "      <th>TRAILER2_LENGTH</th>\n",
       "      <th>TOTAL_VEHICLE_LENGTH</th>\n",
       "      <th>AXLE_CNT</th>\n",
       "      <th>VEHICLE_CONFIG</th>\n",
       "      <th>CARGO_BODY_TYPE</th>\n",
       "      <th>LOAD_TYPE</th>\n",
       "      <th>HAZMAT_OUT_OF_SERVICE_I</th>\n",
       "      <th>MCS_OUT_OF_SERVICE_I</th>\n",
       "      <th>HAZMAT_CLASS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>829999</td>\n",
       "      <td>24ddf9fd8542199d832e1c223cc474e5601b356f1d77a6...</td>\n",
       "      <td>JD124535</td>\n",
       "      <td>01/22/2020 06:25:00 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>796949.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INFINITI</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>749947</td>\n",
       "      <td>81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...</td>\n",
       "      <td>JC451435</td>\n",
       "      <td>09/28/2019 03:30:00 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>834816.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HONDA</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>749949</td>\n",
       "      <td>81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...</td>\n",
       "      <td>JC451435</td>\n",
       "      <td>09/28/2019 03:30:00 AM</td>\n",
       "      <td>2</td>\n",
       "      <td>PARKED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>834819.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TOYOTA</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>749950</td>\n",
       "      <td>81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...</td>\n",
       "      <td>JC451435</td>\n",
       "      <td>09/28/2019 03:30:00 AM</td>\n",
       "      <td>3</td>\n",
       "      <td>PARKED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>834817.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GENERAL MOTORS CORPORATION (GMC)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>871921</td>\n",
       "      <td>af84fb5c8d996fcd3aefd36593c3a02e6e7509eeb27568...</td>\n",
       "      <td>JD208731</td>\n",
       "      <td>04/13/2020 10:50:00 PM</td>\n",
       "      <td>2</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>827212.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BUICK</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 72 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   CRASH_UNIT_ID                                    CRASH_RECORD_ID     RD_NO  \\\n",
       "0         829999  24ddf9fd8542199d832e1c223cc474e5601b356f1d77a6...  JD124535   \n",
       "1         749947  81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...  JC451435   \n",
       "2         749949  81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...  JC451435   \n",
       "3         749950  81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...  JC451435   \n",
       "4         871921  af84fb5c8d996fcd3aefd36593c3a02e6e7509eeb27568...  JD208731   \n",
       "\n",
       "               CRASH_DATE  UNIT_NO UNIT_TYPE  NUM_PASSENGERS  VEHICLE_ID  \\\n",
       "0  01/22/2020 06:25:00 AM        1    DRIVER             NaN    796949.0   \n",
       "1  09/28/2019 03:30:00 AM        1    DRIVER             NaN    834816.0   \n",
       "2  09/28/2019 03:30:00 AM        2    PARKED             NaN    834819.0   \n",
       "3  09/28/2019 03:30:00 AM        3    PARKED             NaN    834817.0   \n",
       "4  04/13/2020 10:50:00 PM        2    DRIVER             NaN    827212.0   \n",
       "\n",
       "  CMRC_VEH_I                              MAKE  ... TRAILER1_LENGTH  \\\n",
       "0        NaN                          INFINITI  ...             NaN   \n",
       "1        NaN                             HONDA  ...             NaN   \n",
       "2        NaN                            TOYOTA  ...             NaN   \n",
       "3        NaN  GENERAL MOTORS CORPORATION (GMC)  ...             NaN   \n",
       "4        NaN                             BUICK  ...             NaN   \n",
       "\n",
       "  TRAILER2_LENGTH  TOTAL_VEHICLE_LENGTH AXLE_CNT VEHICLE_CONFIG  \\\n",
       "0             NaN                   NaN      NaN            NaN   \n",
       "1             NaN                   NaN      NaN            NaN   \n",
       "2             NaN                   NaN      NaN            NaN   \n",
       "3             NaN                   NaN      NaN            NaN   \n",
       "4             NaN                   NaN      NaN            NaN   \n",
       "\n",
       "  CARGO_BODY_TYPE LOAD_TYPE HAZMAT_OUT_OF_SERVICE_I MCS_OUT_OF_SERVICE_I  \\\n",
       "0             NaN       NaN                     NaN                  NaN   \n",
       "1             NaN       NaN                     NaN                  NaN   \n",
       "2             NaN       NaN                     NaN                  NaN   \n",
       "3             NaN       NaN                     NaN                  NaN   \n",
       "4             NaN       NaN                     NaN                  NaN   \n",
       "\n",
       "  HAZMAT_CLASS  \n",
       "0          NaN  \n",
       "1          NaN  \n",
       "2          NaN  \n",
       "3          NaN  \n",
       "4          NaN  \n",
       "\n",
       "[5 rows x 72 columns]"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head() #top 5 elements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "4b0f93a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1368826, 72)"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.shape #dimension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "bddc4627",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "column:  CRASH_UNIT_ID\n",
      "unique values:  1368826\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  CRASH_RECORD_ID\n",
      "unique values:  671657\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  RD_NO\n",
      "unique values:  667344\n",
      "null values:  8845\n",
      "----------------------\n",
      "column:  CRASH_DATE\n",
      "unique values:  439069\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  UNIT_NO\n",
      "unique values:  20\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  UNIT_TYPE\n",
      "unique values:  10\n",
      "null values:  1893\n",
      "----------------------\n",
      "column:  NUM_PASSENGERS\n",
      "unique values:  43\n",
      "null values:  1165798\n",
      "----------------------\n",
      "column:  VEHICLE_ID\n",
      "unique values:  1337741\n",
      "null values:  31086\n",
      "----------------------\n",
      "column:  CMRC_VEH_I\n",
      "unique values:  3\n",
      "null values:  1343253\n",
      "----------------------\n",
      "column:  MAKE\n",
      "unique values:  1243\n",
      "null values:  31091\n",
      "----------------------\n",
      "column:  MODEL\n",
      "unique values:  2607\n",
      "null values:  31235\n",
      "----------------------\n",
      "column:  LIC_PLATE_STATE\n",
      "unique values:  53\n",
      "null values:  152246\n",
      "----------------------\n",
      "column:  VEHICLE_YEAR\n",
      "unique values:  209\n",
      "null values:  248663\n",
      "----------------------\n",
      "column:  VEHICLE_DEFECT\n",
      "unique values:  18\n",
      "null values:  31086\n",
      "----------------------\n",
      "column:  VEHICLE_TYPE\n",
      "unique values:  23\n",
      "null values:  31086\n",
      "----------------------\n",
      "column:  VEHICLE_USE\n",
      "unique values:  26\n",
      "null values:  31086\n",
      "----------------------\n",
      "column:  TRAVEL_DIRECTION\n",
      "unique values:  10\n",
      "null values:  31086\n",
      "----------------------\n",
      "column:  MANEUVER\n",
      "unique values:  29\n",
      "null values:  31086\n",
      "----------------------\n",
      "column:  TOWED_I\n",
      "unique values:  3\n",
      "null values:  1202167\n",
      "----------------------\n",
      "column:  FIRE_I\n",
      "unique values:  3\n",
      "null values:  1367733\n",
      "----------------------\n",
      "column:  OCCUPANT_CNT\n",
      "unique values:  46\n",
      "null values:  31086\n",
      "----------------------\n",
      "column:  EXCEED_SPEED_LIMIT_I\n",
      "unique values:  3\n",
      "null values:  1366429\n",
      "----------------------\n",
      "column:  TOWED_BY\n",
      "unique values:  12257\n",
      "null values:  1244815\n",
      "----------------------\n",
      "column:  TOWED_TO\n",
      "unique values:  12912\n",
      "null values:  1292524\n",
      "----------------------\n",
      "column:  AREA_00_I\n",
      "unique values:  3\n",
      "null values:  1319971\n",
      "----------------------\n",
      "column:  AREA_01_I\n",
      "unique values:  3\n",
      "null values:  1007717\n",
      "----------------------\n",
      "column:  AREA_02_I\n",
      "unique values:  3\n",
      "null values:  1147625\n",
      "----------------------\n",
      "column:  AREA_03_I\n",
      "unique values:  3\n",
      "null values:  1238827\n",
      "----------------------\n",
      "column:  AREA_04_I\n",
      "unique values:  3\n",
      "null values:  1237054\n",
      "----------------------\n",
      "column:  AREA_05_I\n",
      "unique values:  3\n",
      "null values:  1164878\n",
      "----------------------\n",
      "column:  AREA_06_I\n",
      "unique values:  3\n",
      "null values:  1157160\n",
      "----------------------\n",
      "column:  AREA_07_I\n",
      "unique values:  3\n",
      "null values:  1177932\n",
      "----------------------\n",
      "column:  AREA_08_I\n",
      "unique values:  3\n",
      "null values:  1162701\n",
      "----------------------\n",
      "column:  AREA_09_I\n",
      "unique values:  3\n",
      "null values:  1288417\n",
      "----------------------\n",
      "column:  AREA_10_I\n",
      "unique values:  3\n",
      "null values:  1252830\n",
      "----------------------\n",
      "column:  AREA_11_I\n",
      "unique values:  3\n",
      "null values:  1141728\n",
      "----------------------\n",
      "column:  AREA_12_I\n",
      "unique values:  3\n",
      "null values:  1145398\n",
      "----------------------\n",
      "column:  AREA_99_I\n",
      "unique values:  3\n",
      "null values:  1217979\n",
      "----------------------\n",
      "column:  FIRST_CONTACT_POINT\n",
      "unique values:  15\n",
      "null values:  43033\n",
      "----------------------\n",
      "column:  CMV_ID\n",
      "unique values:  14375\n",
      "null values:  1354452\n",
      "----------------------\n",
      "column:  USDOT_NO\n",
      "unique values:  4543\n",
      "null values:  1360601\n",
      "----------------------\n",
      "column:  CCMC_NO\n",
      "unique values:  1372\n",
      "null values:  1367026\n",
      "----------------------\n",
      "column:  ILCC_NO\n",
      "unique values:  866\n",
      "null values:  1367569\n",
      "----------------------\n",
      "column:  COMMERCIAL_SRC\n",
      "unique values:  5\n",
      "null values:  1359073\n",
      "----------------------\n",
      "column:  GVWR\n",
      "unique values:  461\n",
      "null values:  1360667\n",
      "----------------------\n",
      "column:  CARRIER_NAME\n",
      "unique values:  8985\n",
      "null values:  1355069\n",
      "----------------------\n",
      "column:  CARRIER_STATE\n",
      "unique values:  51\n",
      "null values:  1355883\n",
      "----------------------\n",
      "column:  CARRIER_CITY\n",
      "unique values:  1870\n",
      "null values:  1356113\n",
      "----------------------\n",
      "column:  HAZMAT_PLACARDS_I\n",
      "unique values:  3\n",
      "null values:  1368544\n",
      "----------------------\n",
      "column:  HAZMAT_NAME\n",
      "unique values:  38\n",
      "null values:  1368773\n",
      "----------------------\n",
      "column:  UN_NO\n",
      "unique values:  365\n",
      "null values:  1368328\n",
      "----------------------\n",
      "column:  HAZMAT_PRESENT_I\n",
      "unique values:  4\n",
      "null values:  1358305\n",
      "----------------------\n",
      "column:  HAZMAT_REPORT_I\n",
      "unique values:  4\n",
      "null values:  1358619\n",
      "----------------------\n",
      "column:  HAZMAT_REPORT_NO\n",
      "unique values:  2\n",
      "null values:  1368825\n",
      "----------------------\n",
      "column:  MCS_REPORT_I\n",
      "unique values:  4\n",
      "null values:  1358560\n",
      "----------------------\n",
      "column:  MCS_REPORT_NO\n",
      "unique values:  7\n",
      "null values:  1368820\n",
      "----------------------\n",
      "column:  HAZMAT_VIO_CAUSE_CRASH_I\n",
      "unique values:  4\n",
      "null values:  1358458\n",
      "----------------------\n",
      "column:  MCS_VIO_CAUSE_CRASH_I\n",
      "unique values:  4\n",
      "null values:  1358653\n",
      "----------------------\n",
      "column:  IDOT_PERMIT_NO\n",
      "unique values:  551\n",
      "null values:  1368016\n",
      "----------------------\n",
      "column:  WIDE_LOAD_I\n",
      "unique values:  3\n",
      "null values:  1368702\n",
      "----------------------\n",
      "column:  TRAILER1_WIDTH\n",
      "unique values:  4\n",
      "null values:  1366201\n",
      "----------------------\n",
      "column:  TRAILER2_WIDTH\n",
      "unique values:  4\n",
      "null values:  1368511\n",
      "----------------------\n",
      "column:  TRAILER1_LENGTH\n",
      "unique values:  83\n",
      "null values:  1366680\n",
      "----------------------\n",
      "column:  TRAILER2_LENGTH\n",
      "unique values:  34\n",
      "null values:  1368762\n",
      "----------------------\n",
      "column:  TOTAL_VEHICLE_LENGTH\n",
      "unique values:  123\n",
      "null values:  1366213\n",
      "----------------------\n",
      "column:  AXLE_CNT\n",
      "unique values:  21\n",
      "null values:  1364971\n",
      "----------------------\n",
      "column:  VEHICLE_CONFIG\n",
      "unique values:  9\n",
      "null values:  1356916\n",
      "----------------------\n",
      "column:  CARGO_BODY_TYPE\n",
      "unique values:  10\n",
      "null values:  1357427\n",
      "----------------------\n",
      "column:  LOAD_TYPE\n",
      "unique values:  7\n",
      "null values:  1357927\n",
      "----------------------\n",
      "column:  HAZMAT_OUT_OF_SERVICE_I\n",
      "unique values:  3\n",
      "null values:  1358920\n",
      "----------------------\n",
      "column:  MCS_OUT_OF_SERVICE_I\n",
      "unique values:  3\n",
      "null values:  1358678\n",
      "----------------------\n",
      "column:  HAZMAT_CLASS\n",
      "unique values:  9\n",
      "null values:  1367870\n",
      "----------------------\n"
     ]
    }
   ],
   "source": [
    "#for loop to analyze each column\n",
    "\n",
    "for i in list(df2.columns):\n",
    "    print('column: ',i)\n",
    "    print('unique values: ',len(df2[i].unique()))\n",
    "    print('null values: ',df2[i].isnull().sum())\n",
    "    print('----------------------')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "5e917200",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CRASH_UNIT_ID</th>\n",
       "      <th>UNIT_NO</th>\n",
       "      <th>NUM_PASSENGERS</th>\n",
       "      <th>VEHICLE_ID</th>\n",
       "      <th>VEHICLE_YEAR</th>\n",
       "      <th>OCCUPANT_CNT</th>\n",
       "      <th>CMV_ID</th>\n",
       "      <th>TRAILER1_LENGTH</th>\n",
       "      <th>TRAILER2_LENGTH</th>\n",
       "      <th>TOTAL_VEHICLE_LENGTH</th>\n",
       "      <th>AXLE_CNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1.368826e+06</td>\n",
       "      <td>1.368826e+06</td>\n",
       "      <td>203028.000000</td>\n",
       "      <td>1.337740e+06</td>\n",
       "      <td>1.120163e+06</td>\n",
       "      <td>1.337740e+06</td>\n",
       "      <td>14374.000000</td>\n",
       "      <td>2146.000000</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>2613.000000</td>\n",
       "      <td>3855.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>7.317602e+05</td>\n",
       "      <td>4.324157e+00</td>\n",
       "      <td>1.471364</td>\n",
       "      <td>6.960622e+05</td>\n",
       "      <td>2.013650e+03</td>\n",
       "      <td>1.079349e+00</td>\n",
       "      <td>8021.900306</td>\n",
       "      <td>48.432898</td>\n",
       "      <td>45.468750</td>\n",
       "      <td>53.176043</td>\n",
       "      <td>10.481971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>4.244287e+05</td>\n",
       "      <td>3.229176e+03</td>\n",
       "      <td>1.051095</td>\n",
       "      <td>4.016065e+05</td>\n",
       "      <td>1.429367e+02</td>\n",
       "      <td>7.835435e-01</td>\n",
       "      <td>4651.599949</td>\n",
       "      <td>21.449337</td>\n",
       "      <td>28.114826</td>\n",
       "      <td>31.777479</td>\n",
       "      <td>418.851275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>1.900000e+03</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>3.629262e+05</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.485688e+05</td>\n",
       "      <td>2.007000e+03</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>3969.250000</td>\n",
       "      <td>44.250000</td>\n",
       "      <td>24.750000</td>\n",
       "      <td>35.000000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>7.329115e+05</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>6.963695e+05</td>\n",
       "      <td>2.012000e+03</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>8047.500000</td>\n",
       "      <td>53.000000</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>53.000000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.099914e+06</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.043438e+06</td>\n",
       "      <td>2.016000e+03</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>12070.750000</td>\n",
       "      <td>53.000000</td>\n",
       "      <td>53.500000</td>\n",
       "      <td>66.000000</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.465752e+06</td>\n",
       "      <td>3.778035e+06</td>\n",
       "      <td>59.000000</td>\n",
       "      <td>1.393194e+06</td>\n",
       "      <td>9.999000e+03</td>\n",
       "      <td>9.900000e+01</td>\n",
       "      <td>16040.000000</td>\n",
       "      <td>740.000000</td>\n",
       "      <td>123.000000</td>\n",
       "      <td>999.000000</td>\n",
       "      <td>26009.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       CRASH_UNIT_ID       UNIT_NO  NUM_PASSENGERS    VEHICLE_ID  \\\n",
       "count   1.368826e+06  1.368826e+06   203028.000000  1.337740e+06   \n",
       "mean    7.317602e+05  4.324157e+00        1.471364  6.960622e+05   \n",
       "std     4.244287e+05  3.229176e+03        1.051095  4.016065e+05   \n",
       "min     2.000000e+00  0.000000e+00        1.000000  2.000000e+00   \n",
       "25%     3.629262e+05  1.000000e+00        1.000000  3.485688e+05   \n",
       "50%     7.329115e+05  2.000000e+00        1.000000  6.963695e+05   \n",
       "75%     1.099914e+06  2.000000e+00        2.000000  1.043438e+06   \n",
       "max     1.465752e+06  3.778035e+06       59.000000  1.393194e+06   \n",
       "\n",
       "       VEHICLE_YEAR  OCCUPANT_CNT        CMV_ID  TRAILER1_LENGTH  \\\n",
       "count  1.120163e+06  1.337740e+06  14374.000000      2146.000000   \n",
       "mean   2.013650e+03  1.079349e+00   8021.900306        48.432898   \n",
       "std    1.429367e+02  7.835435e-01   4651.599949        21.449337   \n",
       "min    1.900000e+03  0.000000e+00      1.000000         1.000000   \n",
       "25%    2.007000e+03  1.000000e+00   3969.250000        44.250000   \n",
       "50%    2.012000e+03  1.000000e+00   8047.500000        53.000000   \n",
       "75%    2.016000e+03  1.000000e+00  12070.750000        53.000000   \n",
       "max    9.999000e+03  9.900000e+01  16040.000000       740.000000   \n",
       "\n",
       "       TRAILER2_LENGTH  TOTAL_VEHICLE_LENGTH      AXLE_CNT  \n",
       "count        64.000000           2613.000000   3855.000000  \n",
       "mean         45.468750             53.176043     10.481971  \n",
       "std          28.114826             31.777479    418.851275  \n",
       "min           2.000000              1.000000      1.000000  \n",
       "25%          24.750000             35.000000      2.000000  \n",
       "50%          50.000000             53.000000      3.000000  \n",
       "75%          53.500000             66.000000      5.000000  \n",
       "max         123.000000            999.000000  26009.000000  "
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.describe() "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92fdb75a",
   "metadata": {},
   "source": [
    "we are further eliminating columns which have more than 30% of the data not available"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "27aab5b3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['NUM_PASSENGERS',\n",
       " 'CMRC_VEH_I',\n",
       " 'TOWED_I',\n",
       " 'FIRE_I',\n",
       " 'EXCEED_SPEED_LIMIT_I',\n",
       " 'TOWED_BY',\n",
       " 'TOWED_TO',\n",
       " 'AREA_00_I',\n",
       " 'AREA_01_I',\n",
       " 'AREA_02_I',\n",
       " 'AREA_03_I',\n",
       " 'AREA_04_I',\n",
       " 'AREA_05_I',\n",
       " 'AREA_06_I',\n",
       " 'AREA_07_I',\n",
       " 'AREA_08_I',\n",
       " 'AREA_09_I',\n",
       " 'AREA_10_I',\n",
       " 'AREA_11_I',\n",
       " 'AREA_12_I',\n",
       " 'AREA_99_I',\n",
       " 'CMV_ID',\n",
       " 'USDOT_NO',\n",
       " 'CCMC_NO',\n",
       " 'ILCC_NO',\n",
       " 'COMMERCIAL_SRC',\n",
       " 'GVWR',\n",
       " 'CARRIER_NAME',\n",
       " 'CARRIER_STATE',\n",
       " 'CARRIER_CITY',\n",
       " 'HAZMAT_PLACARDS_I',\n",
       " 'HAZMAT_NAME',\n",
       " 'UN_NO',\n",
       " 'HAZMAT_PRESENT_I',\n",
       " 'HAZMAT_REPORT_I',\n",
       " 'HAZMAT_REPORT_NO',\n",
       " 'MCS_REPORT_I',\n",
       " 'MCS_REPORT_NO',\n",
       " 'HAZMAT_VIO_CAUSE_CRASH_I',\n",
       " 'MCS_VIO_CAUSE_CRASH_I',\n",
       " 'IDOT_PERMIT_NO',\n",
       " 'WIDE_LOAD_I',\n",
       " 'TRAILER1_WIDTH',\n",
       " 'TRAILER2_WIDTH',\n",
       " 'TRAILER1_LENGTH',\n",
       " 'TRAILER2_LENGTH',\n",
       " 'TOTAL_VEHICLE_LENGTH',\n",
       " 'AXLE_CNT',\n",
       " 'VEHICLE_CONFIG',\n",
       " 'CARGO_BODY_TYPE',\n",
       " 'LOAD_TYPE',\n",
       " 'HAZMAT_OUT_OF_SERVICE_I',\n",
       " 'MCS_OUT_OF_SERVICE_I',\n",
       " 'HAZMAT_CLASS']"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bd_2 = [x for x in df2.columns if df2[x].isnull().mean() >= 0.3]\n",
    "bd_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "82643aab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1368826, 18)"
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.drop(bd_2, inplace= True, axis = 1)# remove columns with a lot of null values\n",
    "df2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "7363ccac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CRASH_RECORD_ID</th>\n",
       "      <th>VEHICLE_ID</th>\n",
       "      <th>CRASH_DATE</th>\n",
       "      <th>UNIT_TYPE</th>\n",
       "      <th>MAKE</th>\n",
       "      <th>MODEL</th>\n",
       "      <th>VEHICLE_TYPE</th>\n",
       "      <th>VEHICLE_USE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24ddf9fd8542199d832e1c223cc474e5601b356f1d77a6...</td>\n",
       "      <td>796949.0</td>\n",
       "      <td>01/22/2020 06:25:00 AM</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>INFINITI</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>PASSENGER</td>\n",
       "      <td>PERSONAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...</td>\n",
       "      <td>834816.0</td>\n",
       "      <td>09/28/2019 03:30:00 AM</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>HONDA</td>\n",
       "      <td>CIVIC</td>\n",
       "      <td>PASSENGER</td>\n",
       "      <td>PERSONAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...</td>\n",
       "      <td>834819.0</td>\n",
       "      <td>09/28/2019 03:30:00 AM</td>\n",
       "      <td>PARKED</td>\n",
       "      <td>TOYOTA</td>\n",
       "      <td>YARIS</td>\n",
       "      <td>UNKNOWN/NA</td>\n",
       "      <td>PERSONAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...</td>\n",
       "      <td>834817.0</td>\n",
       "      <td>09/28/2019 03:30:00 AM</td>\n",
       "      <td>PARKED</td>\n",
       "      <td>GENERAL MOTORS CORPORATION (GMC)</td>\n",
       "      <td>SIERRA</td>\n",
       "      <td>UNKNOWN/NA</td>\n",
       "      <td>UNKNOWN/NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>af84fb5c8d996fcd3aefd36593c3a02e6e7509eeb27568...</td>\n",
       "      <td>827212.0</td>\n",
       "      <td>04/13/2020 10:50:00 PM</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>BUICK</td>\n",
       "      <td>ENCORE</td>\n",
       "      <td>PASSENGER</td>\n",
       "      <td>PERSONAL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     CRASH_RECORD_ID  VEHICLE_ID  \\\n",
       "0  24ddf9fd8542199d832e1c223cc474e5601b356f1d77a6...    796949.0   \n",
       "1  81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...    834816.0   \n",
       "2  81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...    834819.0   \n",
       "3  81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...    834817.0   \n",
       "4  af84fb5c8d996fcd3aefd36593c3a02e6e7509eeb27568...    827212.0   \n",
       "\n",
       "               CRASH_DATE UNIT_TYPE                              MAKE  \\\n",
       "0  01/22/2020 06:25:00 AM    DRIVER                          INFINITI   \n",
       "1  09/28/2019 03:30:00 AM    DRIVER                             HONDA   \n",
       "2  09/28/2019 03:30:00 AM    PARKED                            TOYOTA   \n",
       "3  09/28/2019 03:30:00 AM    PARKED  GENERAL MOTORS CORPORATION (GMC)   \n",
       "4  04/13/2020 10:50:00 PM    DRIVER                             BUICK   \n",
       "\n",
       "     MODEL VEHICLE_TYPE VEHICLE_USE  \n",
       "0  UNKNOWN    PASSENGER    PERSONAL  \n",
       "1    CIVIC    PASSENGER    PERSONAL  \n",
       "2    YARIS   UNKNOWN/NA    PERSONAL  \n",
       "3   SIERRA   UNKNOWN/NA  UNKNOWN/NA  \n",
       "4   ENCORE    PASSENGER    PERSONAL  "
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#useful columns for further analysis\n",
    "df2_2=df2[['CRASH_RECORD_ID','VEHICLE_ID','CRASH_DATE','UNIT_TYPE','MAKE','MODEL','VEHICLE_TYPE', 'VEHICLE_USE']]\n",
    "df2_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "9c0c30a4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1368826, 8)"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2_2.shape #final dimension after modification"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c902bb7e",
   "metadata": {},
   "source": [
    "### Dataframe 3, People"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "b10e2e3f",
   "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>PERSON_ID</th>\n",
       "      <th>PERSON_TYPE</th>\n",
       "      <th>CRASH_RECORD_ID</th>\n",
       "      <th>RD_NO</th>\n",
       "      <th>VEHICLE_ID</th>\n",
       "      <th>CRASH_DATE</th>\n",
       "      <th>SEAT_NO</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>ZIPCODE</th>\n",
       "      <th>...</th>\n",
       "      <th>EMS_RUN_NO</th>\n",
       "      <th>DRIVER_ACTION</th>\n",
       "      <th>DRIVER_VISION</th>\n",
       "      <th>PHYSICAL_CONDITION</th>\n",
       "      <th>PEDPEDAL_ACTION</th>\n",
       "      <th>PEDPEDAL_VISIBILITY</th>\n",
       "      <th>PEDPEDAL_LOCATION</th>\n",
       "      <th>BAC_RESULT</th>\n",
       "      <th>BAC_RESULT VALUE</th>\n",
       "      <th>CELL_PHONE_USE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>O749947</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...</td>\n",
       "      <td>JC451435</td>\n",
       "      <td>834816.0</td>\n",
       "      <td>09/28/2019 03:30:00 AM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>IL</td>\n",
       "      <td>60651</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TEST NOT OFFERED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>O871921</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>af84fb5c8d996fcd3aefd36593c3a02e6e7509eeb27568...</td>\n",
       "      <td>JD208731</td>\n",
       "      <td>827212.0</td>\n",
       "      <td>04/13/2020 10:50:00 PM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>IL</td>\n",
       "      <td>60620</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NONE</td>\n",
       "      <td>NOT OBSCURED</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TEST NOT OFFERED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>O10018</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>71162af7bf22799b776547132ebf134b5b438dcf3dac6b...</td>\n",
       "      <td>HY484534</td>\n",
       "      <td>9579.0</td>\n",
       "      <td>11/01/2015 05:00:00 AM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IMPROPER BACKING</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TEST NOT OFFERED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>O10038</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>c21c476e2ccc41af550b5d858d22aaac4ffc88745a1700...</td>\n",
       "      <td>HY484750</td>\n",
       "      <td>9598.0</td>\n",
       "      <td>11/01/2015 08:00:00 AM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TEST NOT OFFERED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>O10039</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>eb390a4c8e114c69488f5fb8a097fe629f5a92fd528cf4...</td>\n",
       "      <td>HY484778</td>\n",
       "      <td>9600.0</td>\n",
       "      <td>11/01/2015 10:15:00 AM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TEST NOT OFFERED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  PERSON_ID PERSON_TYPE                                    CRASH_RECORD_ID  \\\n",
       "0   O749947      DRIVER  81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...   \n",
       "1   O871921      DRIVER  af84fb5c8d996fcd3aefd36593c3a02e6e7509eeb27568...   \n",
       "2    O10018      DRIVER  71162af7bf22799b776547132ebf134b5b438dcf3dac6b...   \n",
       "3    O10038      DRIVER  c21c476e2ccc41af550b5d858d22aaac4ffc88745a1700...   \n",
       "4    O10039      DRIVER  eb390a4c8e114c69488f5fb8a097fe629f5a92fd528cf4...   \n",
       "\n",
       "      RD_NO  VEHICLE_ID              CRASH_DATE  SEAT_NO     CITY STATE  \\\n",
       "0  JC451435    834816.0  09/28/2019 03:30:00 AM      NaN  CHICAGO    IL   \n",
       "1  JD208731    827212.0  04/13/2020 10:50:00 PM      NaN  CHICAGO    IL   \n",
       "2  HY484534      9579.0  11/01/2015 05:00:00 AM      NaN      NaN   NaN   \n",
       "3  HY484750      9598.0  11/01/2015 08:00:00 AM      NaN      NaN   NaN   \n",
       "4  HY484778      9600.0  11/01/2015 10:15:00 AM      NaN      NaN   NaN   \n",
       "\n",
       "  ZIPCODE  ... EMS_RUN_NO     DRIVER_ACTION DRIVER_VISION PHYSICAL_CONDITION  \\\n",
       "0   60651  ...        NaN           UNKNOWN       UNKNOWN            UNKNOWN   \n",
       "1   60620  ...        NaN              NONE  NOT OBSCURED             NORMAL   \n",
       "2     NaN  ...        NaN  IMPROPER BACKING       UNKNOWN            UNKNOWN   \n",
       "3     NaN  ...        NaN           UNKNOWN       UNKNOWN            UNKNOWN   \n",
       "4     NaN  ...        NaN           UNKNOWN       UNKNOWN            UNKNOWN   \n",
       "\n",
       "  PEDPEDAL_ACTION PEDPEDAL_VISIBILITY PEDPEDAL_LOCATION        BAC_RESULT  \\\n",
       "0             NaN                 NaN               NaN  TEST NOT OFFERED   \n",
       "1             NaN                 NaN               NaN  TEST NOT OFFERED   \n",
       "2             NaN                 NaN               NaN  TEST NOT OFFERED   \n",
       "3             NaN                 NaN               NaN  TEST NOT OFFERED   \n",
       "4             NaN                 NaN               NaN  TEST NOT OFFERED   \n",
       "\n",
       "  BAC_RESULT VALUE CELL_PHONE_USE  \n",
       "0              NaN            NaN  \n",
       "1              NaN            NaN  \n",
       "2              NaN            NaN  \n",
       "3              NaN            NaN  \n",
       "4              NaN            NaN  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head() #top 5 elements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "c9e14d8e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1473483, 30)"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.shape #dimension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "73df3774",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "column:  PERSON_ID\n",
      "unique values:  1473483\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  PERSON_TYPE\n",
      "unique values:  6\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  CRASH_RECORD_ID\n",
      "unique values:  670214\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  RD_NO\n",
      "unique values:  665918\n",
      "null values:  9355\n",
      "----------------------\n",
      "column:  VEHICLE_ID\n",
      "unique values:  1160164\n",
      "null values:  29050\n",
      "----------------------\n",
      "column:  CRASH_DATE\n",
      "unique values:  438300\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  SEAT_NO\n",
      "unique values:  12\n",
      "null values:  1174755\n",
      "----------------------\n",
      "column:  CITY\n",
      "unique values:  11967\n",
      "null values:  396853\n",
      "----------------------\n",
      "column:  STATE\n",
      "unique values:  53\n",
      "null values:  382747\n",
      "----------------------\n",
      "column:  ZIPCODE\n",
      "unique values:  12994\n",
      "null values:  489836\n",
      "----------------------\n",
      "column:  SEX\n",
      "unique values:  4\n",
      "null values:  22910\n",
      "----------------------\n",
      "column:  AGE\n",
      "unique values:  117\n",
      "null values:  427845\n",
      "----------------------\n",
      "column:  DRIVERS_LICENSE_STATE\n",
      "unique values:  198\n",
      "null values:  607132\n",
      "----------------------\n",
      "column:  DRIVERS_LICENSE_CLASS\n",
      "unique values:  266\n",
      "null values:  739036\n",
      "----------------------\n",
      "column:  SAFETY_EQUIPMENT\n",
      "unique values:  20\n",
      "null values:  4189\n",
      "----------------------\n",
      "column:  AIRBAG_DEPLOYED\n",
      "unique values:  8\n",
      "null values:  27890\n",
      "----------------------\n",
      "column:  EJECTION\n",
      "unique values:  6\n",
      "null values:  17873\n",
      "----------------------\n",
      "column:  INJURY_CLASSIFICATION\n",
      "unique values:  6\n",
      "null values:  643\n",
      "----------------------\n",
      "column:  HOSPITAL\n",
      "unique values:  6428\n",
      "null values:  1217980\n",
      "----------------------\n",
      "column:  EMS_AGENCY\n",
      "unique values:  7318\n",
      "null values:  1314264\n",
      "----------------------\n",
      "column:  EMS_RUN_NO\n",
      "unique values:  1240\n",
      "null values:  1447319\n",
      "----------------------\n",
      "column:  DRIVER_ACTION\n",
      "unique values:  21\n",
      "null values:  301402\n",
      "----------------------\n",
      "column:  DRIVER_VISION\n",
      "unique values:  15\n",
      "null values:  301823\n",
      "----------------------\n",
      "column:  PHYSICAL_CONDITION\n",
      "unique values:  13\n",
      "null values:  300579\n",
      "----------------------\n",
      "column:  PEDPEDAL_ACTION\n",
      "unique values:  24\n",
      "null values:  1445978\n",
      "----------------------\n",
      "column:  PEDPEDAL_VISIBILITY\n",
      "unique values:  5\n",
      "null values:  1446032\n",
      "----------------------\n",
      "column:  PEDPEDAL_LOCATION\n",
      "unique values:  9\n",
      "null values:  1445981\n",
      "----------------------\n",
      "column:  BAC_RESULT\n",
      "unique values:  5\n",
      "null values:  300395\n",
      "----------------------\n",
      "column:  BAC_RESULT VALUE\n",
      "unique values:  56\n",
      "null values:  1471706\n",
      "----------------------\n",
      "column:  CELL_PHONE_USE\n",
      "unique values:  3\n",
      "null values:  1472325\n",
      "----------------------\n"
     ]
    }
   ],
   "source": [
    "#for loop to analyze each column\n",
    "\n",
    "for i in list(df3.columns):\n",
    "    print('column: ',i)\n",
    "    print('unique values: ',len(df3[i].unique()))\n",
    "    print('null values: ',df3[i].isnull().sum())\n",
    "    print('----------------------')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "8431b590",
   "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>VEHICLE_ID</th>\n",
       "      <th>SEAT_NO</th>\n",
       "      <th>AGE</th>\n",
       "      <th>BAC_RESULT VALUE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1.444433e+06</td>\n",
       "      <td>298728.000000</td>\n",
       "      <td>1.045638e+06</td>\n",
       "      <td>1777.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>6.910865e+05</td>\n",
       "      <td>4.160952</td>\n",
       "      <td>3.783059e+01</td>\n",
       "      <td>0.169527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>4.007051e+05</td>\n",
       "      <td>2.198519</td>\n",
       "      <td>1.710561e+01</td>\n",
       "      <td>0.102325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-1.770000e+02</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>3.458810e+05</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.500000e+01</td>\n",
       "      <td>0.120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>6.855450e+05</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.500000e+01</td>\n",
       "      <td>0.170000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.038584e+06</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>5.000000e+01</td>\n",
       "      <td>0.220000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.393183e+06</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>1.100000e+02</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         VEHICLE_ID        SEAT_NO           AGE  BAC_RESULT VALUE\n",
       "count  1.444433e+06  298728.000000  1.045638e+06       1777.000000\n",
       "mean   6.910865e+05       4.160952  3.783059e+01          0.169527\n",
       "std    4.007051e+05       2.198519  1.710561e+01          0.102325\n",
       "min    2.000000e+00       1.000000 -1.770000e+02          0.000000\n",
       "25%    3.458810e+05       3.000000  2.500000e+01          0.120000\n",
       "50%    6.855450e+05       3.000000  3.500000e+01          0.170000\n",
       "75%    1.038584e+06       5.000000  5.000000e+01          0.220000\n",
       "max    1.393183e+06      12.000000  1.100000e+02          1.000000"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.describe() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "15731e01",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3=df3.loc[df3['AGE']>0] #age is always +ve"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c33e22a4",
   "metadata": {},
   "source": [
    "we are further eliminating columns which have more than 30% of the data not available"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "5c73a435",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['SEAT_NO',\n",
       " 'DRIVERS_LICENSE_CLASS',\n",
       " 'HOSPITAL',\n",
       " 'EMS_AGENCY',\n",
       " 'EMS_RUN_NO',\n",
       " 'PEDPEDAL_ACTION',\n",
       " 'PEDPEDAL_VISIBILITY',\n",
       " 'PEDPEDAL_LOCATION',\n",
       " 'BAC_RESULT VALUE',\n",
       " 'CELL_PHONE_USE']"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bd_3 = [x for x in df3.columns if df3[x].isnull().mean() >= 0.3]\n",
    "bd_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "c0fcd6aa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1033406, 20)"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.drop(bd_3, inplace= True, axis = 1)# remove columns with a lot of null values\n",
    "df3.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "id": "9209d893",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CRASH_RECORD_ID</th>\n",
       "      <th>VEHICLE_ID</th>\n",
       "      <th>CRASH_DATE</th>\n",
       "      <th>PERSON_TYPE</th>\n",
       "      <th>SEX</th>\n",
       "      <th>AGE</th>\n",
       "      <th>SAFETY_EQUIPMENT</th>\n",
       "      <th>AIRBAG_DEPLOYED</th>\n",
       "      <th>INJURY_CLASSIFICATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...</td>\n",
       "      <td>834816.0</td>\n",
       "      <td>09/28/2019 03:30:00 AM</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>M</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NONE PRESENT</td>\n",
       "      <td>DEPLOYMENT UNKNOWN</td>\n",
       "      <td>NO INDICATION OF INJURY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>af84fb5c8d996fcd3aefd36593c3a02e6e7509eeb27568...</td>\n",
       "      <td>827212.0</td>\n",
       "      <td>04/13/2020 10:50:00 PM</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>M</td>\n",
       "      <td>37.0</td>\n",
       "      <td>SAFETY BELT USED</td>\n",
       "      <td>DID NOT DEPLOY</td>\n",
       "      <td>NO INDICATION OF INJURY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>f25f09798b51603bde602ded37fea826fc8b7f962fc3b0...</td>\n",
       "      <td>805348.0</td>\n",
       "      <td>02/23/2020 06:15:00 PM</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>F</td>\n",
       "      <td>34.0</td>\n",
       "      <td>SAFETY BELT USED</td>\n",
       "      <td>DID NOT DEPLOY</td>\n",
       "      <td>NO INDICATION OF INJURY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>e9146986f4b0884d00ff3a54da5249263b4b36c15d01ce...</td>\n",
       "      <td>832624.0</td>\n",
       "      <td>04/30/2020 03:05:00 PM</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>M</td>\n",
       "      <td>53.0</td>\n",
       "      <td>SAFETY BELT USED</td>\n",
       "      <td>DID NOT DEPLOY</td>\n",
       "      <td>NO INDICATION OF INJURY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>f15ccbd94a8e29ce8424882ce93061d4e1d0deb214acfe...</td>\n",
       "      <td>833984.0</td>\n",
       "      <td>05/03/2020 10:30:00 PM</td>\n",
       "      <td>DRIVER</td>\n",
       "      <td>M</td>\n",
       "      <td>65.0</td>\n",
       "      <td>USAGE UNKNOWN</td>\n",
       "      <td>DID NOT DEPLOY</td>\n",
       "      <td>NO INDICATION OF INJURY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      CRASH_RECORD_ID  VEHICLE_ID  \\\n",
       "0   81dc0de2ed92aa62baccab641fa377be7feb1cc47e6554...    834816.0   \n",
       "1   af84fb5c8d996fcd3aefd36593c3a02e6e7509eeb27568...    827212.0   \n",
       "12  f25f09798b51603bde602ded37fea826fc8b7f962fc3b0...    805348.0   \n",
       "13  e9146986f4b0884d00ff3a54da5249263b4b36c15d01ce...    832624.0   \n",
       "14  f15ccbd94a8e29ce8424882ce93061d4e1d0deb214acfe...    833984.0   \n",
       "\n",
       "                CRASH_DATE PERSON_TYPE SEX   AGE  SAFETY_EQUIPMENT  \\\n",
       "0   09/28/2019 03:30:00 AM      DRIVER   M  25.0      NONE PRESENT   \n",
       "1   04/13/2020 10:50:00 PM      DRIVER   M  37.0  SAFETY BELT USED   \n",
       "12  02/23/2020 06:15:00 PM      DRIVER   F  34.0  SAFETY BELT USED   \n",
       "13  04/30/2020 03:05:00 PM      DRIVER   M  53.0  SAFETY BELT USED   \n",
       "14  05/03/2020 10:30:00 PM      DRIVER   M  65.0     USAGE UNKNOWN   \n",
       "\n",
       "       AIRBAG_DEPLOYED    INJURY_CLASSIFICATION  \n",
       "0   DEPLOYMENT UNKNOWN  NO INDICATION OF INJURY  \n",
       "1       DID NOT DEPLOY  NO INDICATION OF INJURY  \n",
       "12      DID NOT DEPLOY  NO INDICATION OF INJURY  \n",
       "13      DID NOT DEPLOY  NO INDICATION OF INJURY  \n",
       "14      DID NOT DEPLOY  NO INDICATION OF INJURY  "
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3_3=df3[['CRASH_RECORD_ID','VEHICLE_ID','CRASH_DATE','PERSON_TYPE','SEX', 'AGE','SAFETY_EQUIPMENT','AIRBAG_DEPLOYED','INJURY_CLASSIFICATION']]\n",
    "df3_3.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "id": "1aef282f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1033406, 9)"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3_3.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a851e058",
   "metadata": {},
   "source": [
    "### Dataframe 4, Red Light Violation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "81a71fc0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INTERSECTION</th>\n",
       "      <th>CAMERA ID</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>VIOLATION DATE</th>\n",
       "      <th>VIOLATIONS</th>\n",
       "      <th>X COORDINATE</th>\n",
       "      <th>Y COORDINATE</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>LOCATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IRVING PARK AND KILPATRICK</td>\n",
       "      <td>2763.0</td>\n",
       "      <td>4700 W IRVING PARK ROA</td>\n",
       "      <td>04/09/2015</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>VAN BUREN AND WESTERN</td>\n",
       "      <td>2054.0</td>\n",
       "      <td>2400 W VAN BUREN STREE</td>\n",
       "      <td>04/14/2015</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>115TH AND HALSTED</td>\n",
       "      <td>2552.0</td>\n",
       "      <td>11500 S HALSTED STREE</td>\n",
       "      <td>04/08/2015</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>IRVING PARK AND KILPATRICK</td>\n",
       "      <td>2764.0</td>\n",
       "      <td>4700 W IRVING PARK ROA</td>\n",
       "      <td>04/19/2015</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ELSTON AND IRVING PARK</td>\n",
       "      <td>1503.0</td>\n",
       "      <td>3700 W IRVING PARK ROA</td>\n",
       "      <td>04/23/2015</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 INTERSECTION  CAMERA ID                 ADDRESS  \\\n",
       "0  IRVING PARK AND KILPATRICK     2763.0  4700 W IRVING PARK ROA   \n",
       "1       VAN BUREN AND WESTERN     2054.0  2400 W VAN BUREN STREE   \n",
       "2           115TH AND HALSTED     2552.0   11500 S HALSTED STREE   \n",
       "3  IRVING PARK AND KILPATRICK     2764.0  4700 W IRVING PARK ROA   \n",
       "4      ELSTON AND IRVING PARK     1503.0  3700 W IRVING PARK ROA   \n",
       "\n",
       "  VIOLATION DATE  VIOLATIONS  X COORDINATE  Y COORDINATE  LATITUDE  LONGITUDE  \\\n",
       "0     04/09/2015           4           NaN           NaN       NaN        NaN   \n",
       "1     04/14/2015           5           NaN           NaN       NaN        NaN   \n",
       "2     04/08/2015           5           NaN           NaN       NaN        NaN   \n",
       "3     04/19/2015           4           NaN           NaN       NaN        NaN   \n",
       "4     04/23/2015           3           NaN           NaN       NaN        NaN   \n",
       "\n",
       "  LOCATION  \n",
       "0      NaN  \n",
       "1      NaN  \n",
       "2      NaN  \n",
       "3      NaN  \n",
       "4      NaN  "
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "id": "c4c6f867",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(801605, 10)"
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "id": "e3f2800e",
   "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>CAMERA ID</th>\n",
       "      <th>VIOLATIONS</th>\n",
       "      <th>X COORDINATE</th>\n",
       "      <th>Y COORDINATE</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>801316.000000</td>\n",
       "      <td>801605.000000</td>\n",
       "      <td>7.597310e+05</td>\n",
       "      <td>7.597310e+05</td>\n",
       "      <td>759731.000000</td>\n",
       "      <td>759731.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1876.287387</td>\n",
       "      <td>6.030142</td>\n",
       "      <td>1.157352e+06</td>\n",
       "      <td>1.899250e+06</td>\n",
       "      <td>41.879295</td>\n",
       "      <td>-87.697724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>515.090994</td>\n",
       "      <td>7.838444</td>\n",
       "      <td>1.402888e+04</td>\n",
       "      <td>3.028840e+04</td>\n",
       "      <td>0.083255</td>\n",
       "      <td>0.051107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1002.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.125717e+06</td>\n",
       "      <td>1.825911e+06</td>\n",
       "      <td>41.677721</td>\n",
       "      <td>-87.812781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1454.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.146774e+06</td>\n",
       "      <td>1.873078e+06</td>\n",
       "      <td>41.807727</td>\n",
       "      <td>-87.736249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1871.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>1.157189e+06</td>\n",
       "      <td>1.907723e+06</td>\n",
       "      <td>41.902714</td>\n",
       "      <td>-87.697765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2314.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>1.167172e+06</td>\n",
       "      <td>1.921577e+06</td>\n",
       "      <td>41.940241</td>\n",
       "      <td>-87.662293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>8313.000000</td>\n",
       "      <td>186.000000</td>\n",
       "      <td>1.191188e+06</td>\n",
       "      <td>1.947754e+06</td>\n",
       "      <td>42.012371</td>\n",
       "      <td>-87.575293</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           CAMERA ID     VIOLATIONS  X COORDINATE  Y COORDINATE  \\\n",
       "count  801316.000000  801605.000000  7.597310e+05  7.597310e+05   \n",
       "mean     1876.287387       6.030142  1.157352e+06  1.899250e+06   \n",
       "std       515.090994       7.838444  1.402888e+04  3.028840e+04   \n",
       "min      1002.000000       1.000000  1.125717e+06  1.825911e+06   \n",
       "25%      1454.000000       2.000000  1.146774e+06  1.873078e+06   \n",
       "50%      1871.000000       4.000000  1.157189e+06  1.907723e+06   \n",
       "75%      2314.000000       7.000000  1.167172e+06  1.921577e+06   \n",
       "max      8313.000000     186.000000  1.191188e+06  1.947754e+06   \n",
       "\n",
       "            LATITUDE      LONGITUDE  \n",
       "count  759731.000000  759731.000000  \n",
       "mean       41.879295     -87.697724  \n",
       "std         0.083255       0.051107  \n",
       "min        41.677721     -87.812781  \n",
       "25%        41.807727     -87.736249  \n",
       "50%        41.902714     -87.697765  \n",
       "75%        41.940241     -87.662293  \n",
       "max        42.012371     -87.575293  "
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "id": "2df7c0cd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "column:  INTERSECTION\n",
      "unique values:  183\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  CAMERA ID\n",
      "unique values:  366\n",
      "null values:  289\n",
      "----------------------\n",
      "column:  ADDRESS\n",
      "unique values:  317\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  VIOLATION DATE\n",
      "unique values:  3051\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  VIOLATIONS\n",
      "unique values:  161\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  X COORDINATE\n",
      "unique values:  298\n",
      "null values:  41874\n",
      "----------------------\n",
      "column:  Y COORDINATE\n",
      "unique values:  298\n",
      "null values:  41874\n",
      "----------------------\n",
      "column:  LATITUDE\n",
      "unique values:  298\n",
      "null values:  41874\n",
      "----------------------\n",
      "column:  LONGITUDE\n",
      "unique values:  298\n",
      "null values:  41874\n",
      "----------------------\n",
      "column:  LOCATION\n",
      "unique values:  298\n",
      "null values:  41874\n",
      "----------------------\n"
     ]
    }
   ],
   "source": [
    "#for loop to analyze each column\n",
    "\n",
    "for i in list(df4.columns):\n",
    "    print('column: ',i)\n",
    "    print('unique values: ',len(df4[i].unique()))\n",
    "    print('null values: ',df4[i].isnull().sum())\n",
    "    print('----------------------')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c1d5f3b",
   "metadata": {},
   "source": [
    "we are further eliminating columns which have more than 30% of the data not available"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "id": "d1c8e428",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bd_4 = [x for x in df4.columns if df4[x].isnull().mean() >= 0.3]\n",
    "bd_4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "f71115fe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(801605, 10)"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4.drop(bd_4, inplace= True, axis = 1)# remove columns with a lot of null values\n",
    "df4.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "ff1be521",
   "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>CAMERA ID</th>\n",
       "      <th>VIOLATION DATE</th>\n",
       "      <th>VIOLATIONS</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2763.0</td>\n",
       "      <td>04/09/2015</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2054.0</td>\n",
       "      <td>04/14/2015</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2552.0</td>\n",
       "      <td>04/08/2015</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2764.0</td>\n",
       "      <td>04/19/2015</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1503.0</td>\n",
       "      <td>04/23/2015</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CAMERA ID VIOLATION DATE  VIOLATIONS  LATITUDE  LONGITUDE\n",
       "0     2763.0     04/09/2015           4       NaN        NaN\n",
       "1     2054.0     04/14/2015           5       NaN        NaN\n",
       "2     2552.0     04/08/2015           5       NaN        NaN\n",
       "3     2764.0     04/19/2015           4       NaN        NaN\n",
       "4     1503.0     04/23/2015           3       NaN        NaN"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4_4=df4[['CAMERA ID','VIOLATION DATE','VIOLATIONS','LATITUDE','LONGITUDE']]\n",
    "df4_4.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a981c5a9",
   "metadata": {},
   "source": [
    "### Dataframe 5, Speed Camera Violation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "id": "e0e2618a",
   "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>ADDRESS</th>\n",
       "      <th>CAMERA ID</th>\n",
       "      <th>VIOLATION DATE</th>\n",
       "      <th>VIOLATIONS</th>\n",
       "      <th>X COORDINATE</th>\n",
       "      <th>Y COORDINATE</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>LOCATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1111 N HUMBOLDT</td>\n",
       "      <td>CHI010</td>\n",
       "      <td>04/10/2015</td>\n",
       "      <td>67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1111 N HUMBOLDT</td>\n",
       "      <td>CHI010</td>\n",
       "      <td>04/25/2015</td>\n",
       "      <td>71</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1111 N HUMBOLDT</td>\n",
       "      <td>CHI010</td>\n",
       "      <td>04/14/2015</td>\n",
       "      <td>38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1111 N HUMBOLDT</td>\n",
       "      <td>CHI010</td>\n",
       "      <td>04/16/2015</td>\n",
       "      <td>55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1111 N HUMBOLDT</td>\n",
       "      <td>CHI010</td>\n",
       "      <td>04/24/2015</td>\n",
       "      <td>54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           ADDRESS CAMERA ID VIOLATION DATE  VIOLATIONS  X COORDINATE  \\\n",
       "0  1111 N HUMBOLDT    CHI010     04/10/2015          67           NaN   \n",
       "1  1111 N HUMBOLDT    CHI010     04/25/2015          71           NaN   \n",
       "2  1111 N HUMBOLDT    CHI010     04/14/2015          38           NaN   \n",
       "3  1111 N HUMBOLDT    CHI010     04/16/2015          55           NaN   \n",
       "4  1111 N HUMBOLDT    CHI010     04/24/2015          54           NaN   \n",
       "\n",
       "   Y COORDINATE  LATITUDE  LONGITUDE LOCATION  \n",
       "0           NaN       NaN        NaN      NaN  \n",
       "1           NaN       NaN        NaN      NaN  \n",
       "2           NaN       NaN        NaN      NaN  \n",
       "3           NaN       NaN        NaN      NaN  \n",
       "4           NaN       NaN        NaN      NaN  "
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "id": "741cb87e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(317468, 9)"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "id": "7cf12a37",
   "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>VIOLATIONS</th>\n",
       "      <th>X COORDINATE</th>\n",
       "      <th>Y COORDINATE</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>317468.000000</td>\n",
       "      <td>3.054070e+05</td>\n",
       "      <td>3.054070e+05</td>\n",
       "      <td>305407.000000</td>\n",
       "      <td>305407.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>38.011337</td>\n",
       "      <td>1.159566e+06</td>\n",
       "      <td>1.893262e+06</td>\n",
       "      <td>41.862813</td>\n",
       "      <td>-87.689772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>52.751942</td>\n",
       "      <td>1.627575e+04</td>\n",
       "      <td>3.219442e+04</td>\n",
       "      <td>0.088541</td>\n",
       "      <td>0.059221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.121638e+06</td>\n",
       "      <td>1.820629e+06</td>\n",
       "      <td>41.663174</td>\n",
       "      <td>-87.828317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>8.000000</td>\n",
       "      <td>1.149548e+06</td>\n",
       "      <td>1.868078e+06</td>\n",
       "      <td>41.793493</td>\n",
       "      <td>-87.726205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>20.000000</td>\n",
       "      <td>1.159529e+06</td>\n",
       "      <td>1.898488e+06</td>\n",
       "      <td>41.877243</td>\n",
       "      <td>-87.688862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>47.000000</td>\n",
       "      <td>1.167695e+06</td>\n",
       "      <td>1.920991e+06</td>\n",
       "      <td>41.939040</td>\n",
       "      <td>-87.661138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>827.000000</td>\n",
       "      <td>1.203645e+06</td>\n",
       "      <td>1.943342e+06</td>\n",
       "      <td>42.000260</td>\n",
       "      <td>-87.529848</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          VIOLATIONS  X COORDINATE  Y COORDINATE       LATITUDE      LONGITUDE\n",
       "count  317468.000000  3.054070e+05  3.054070e+05  305407.000000  305407.000000\n",
       "mean       38.011337  1.159566e+06  1.893262e+06      41.862813     -87.689772\n",
       "std        52.751942  1.627575e+04  3.219442e+04       0.088541       0.059221\n",
       "min         1.000000  1.121638e+06  1.820629e+06      41.663174     -87.828317\n",
       "25%         8.000000  1.149548e+06  1.868078e+06      41.793493     -87.726205\n",
       "50%        20.000000  1.159529e+06  1.898488e+06      41.877243     -87.688862\n",
       "75%        47.000000  1.167695e+06  1.920991e+06      41.939040     -87.661138\n",
       "max       827.000000  1.203645e+06  1.943342e+06      42.000260     -87.529848"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "id": "88a0c661",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "column:  ADDRESS\n",
      "unique values:  165\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  CAMERA ID\n",
      "unique values:  163\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  VIOLATION DATE\n",
      "unique values:  3049\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  VIOLATIONS\n",
      "unique values:  626\n",
      "null values:  0\n",
      "----------------------\n",
      "column:  X COORDINATE\n",
      "unique values:  161\n",
      "null values:  12061\n",
      "----------------------\n",
      "column:  Y COORDINATE\n",
      "unique values:  161\n",
      "null values:  12061\n",
      "----------------------\n",
      "column:  LATITUDE\n",
      "unique values:  161\n",
      "null values:  12061\n",
      "----------------------\n",
      "column:  LONGITUDE\n",
      "unique values:  161\n",
      "null values:  12061\n",
      "----------------------\n",
      "column:  LOCATION\n",
      "unique values:  161\n",
      "null values:  12061\n",
      "----------------------\n"
     ]
    }
   ],
   "source": [
    "#for loop to analyze each column\n",
    "\n",
    "for i in list(df5.columns):\n",
    "    print('column: ',i)\n",
    "    print('unique values: ',len(df5[i].unique()))\n",
    "    print('null values: ',df5[i].isnull().sum())\n",
    "    print('----------------------')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea73ee97",
   "metadata": {},
   "source": [
    "we are further eliminating columns which have more than 30% of the data not available"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "id": "d56ee0c9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bd_5 = [x for x in df5.columns if df5[x].isnull().mean() >= 0.3]\n",
    "bd_5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "id": "fe2582e9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(317468, 9)"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5.drop(bd_5, inplace= True, axis = 1)# remove columns with a lot of null values\n",
    "df5.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "id": "0b8cd594",
   "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>CAMERA ID</th>\n",
       "      <th>VIOLATION DATE</th>\n",
       "      <th>VIOLATIONS</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CHI010</td>\n",
       "      <td>04/10/2015</td>\n",
       "      <td>67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CHI010</td>\n",
       "      <td>04/25/2015</td>\n",
       "      <td>71</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CHI010</td>\n",
       "      <td>04/14/2015</td>\n",
       "      <td>38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CHI010</td>\n",
       "      <td>04/16/2015</td>\n",
       "      <td>55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CHI010</td>\n",
       "      <td>04/24/2015</td>\n",
       "      <td>54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  CAMERA ID VIOLATION DATE  VIOLATIONS  LATITUDE  LONGITUDE\n",
       "0    CHI010     04/10/2015          67       NaN        NaN\n",
       "1    CHI010     04/25/2015          71       NaN        NaN\n",
       "2    CHI010     04/14/2015          38       NaN        NaN\n",
       "3    CHI010     04/16/2015          55       NaN        NaN\n",
       "4    CHI010     04/24/2015          54       NaN        NaN"
      ]
     },
     "execution_count": 189,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5_5=df5[['CAMERA ID','VIOLATION DATE','VIOLATIONS','LATITUDE','LONGITUDE']]\n",
    "df5_5.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "id": "b1419f97",
   "metadata": {},
   "outputs": [],
   "source": [
    "col = ['LATITUDE', 'LONGITUDE'] #remove rows where there are null values\n",
    "df5_5 = df5_5.dropna(axis=0, subset= col)\n",
    "df4_4 = df4_4.dropna(axis=0, subset= col)\n",
    "df1_1 = df1_1.dropna(axis=0, subset= col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "id": "fc784c70",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((305407, 5), (759731, 5), (667443, 10))"
      ]
     },
     "execution_count": 191,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5_5.shape, df4_4.shape, df1_1.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f148bed",
   "metadata": {},
   "source": [
    "# Maintaining Uniform Date Range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "id": "e3c6afcc",
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert column to datetime\n",
    "\n",
    "def date_format(df):\n",
    "    year=[]\n",
    "    month=[]\n",
    "    day_of_month=[]\n",
    "    day=[]\n",
    "    hour=[]\n",
    "    minute=[]\n",
    "    second=[]\n",
    "\n",
    "    dayss= ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday','Sunday']\n",
    "    for i in list(df['DATE']): #new column\n",
    "            d=parser.parse(i)\n",
    "            year.append(d.year)\n",
    "            month.append(d.month)\n",
    "            day_of_month.append(d.day)\n",
    "            day.append(dayss[d.weekday()])\n",
    "            hour.append(d.hour)\n",
    "            minute.append(d.minute)\n",
    "            second.append(d.second)\n",
    "\n",
    "    df['year']=year\n",
    "    df['month']=month\n",
    "    df['day_of_month']=day_of_month\n",
    "    df['hour']=hour\n",
    "    df['minute']=minute\n",
    "    df['second']=second\n",
    "    df['day']=day #mon,tue,wed..etc\n",
    "    #adding to the main dataset\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "id": "d89f8e65",
   "metadata": {},
   "outputs": [],
   "source": [
    "#change the name of date column in all the dataframe by making it uniform\n",
    "\n",
    "df11=df1_1.rename(columns={'CRASH_DATE': 'DATE'})\n",
    "df21=df2_2.rename(columns={'CRASH_DATE': 'DATE'})\n",
    "df31=df3_3.rename(columns={'CRASH_DATE': 'DATE'})\n",
    "df41=df4_4.rename(columns={'VIOLATION DATE': 'DATE'})\n",
    "df51=df5_5.rename(columns={'VIOLATION DATE': 'DATE'})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "id": "2fa41e15",
   "metadata": {},
   "outputs": [],
   "source": [
    "#calling the function\n",
    "date_format(df11)\n",
    "date_format(df21)\n",
    "date_format(df31)\n",
    "date_format(df41)\n",
    "date_format(df51)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "id": "5cd47cfd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df1 traffic crashes: 2013 1 1\n",
      "df2 traffic crashes vehicle: 2013 1 1\n",
      "df3 traffic crashes people: 2013 1 1\n",
      "df4 traffic crashes red violation: 2014 1 1\n",
      "df5 traffic crashes speed violation: 2014 1 1\n"
     ]
    }
   ],
   "source": [
    "#finding minimum date:\n",
    "print('df1 traffic crashes:', min(df11['year']),min(df11['month']),min(df11['day_of_month']) )\n",
    "print('df2 traffic crashes vehicle:', min(df21['year']),min(df21['month']),min(df21['day_of_month']) )\n",
    "print('df3 traffic crashes people:', min(df31['year']),min(df31['month']),min(df31['day_of_month']) )\n",
    "print('df4 traffic crashes red violation:', min(df41['year']),min(df41['month']),min(df41['day_of_month']) )\n",
    "print('df5 traffic crashes speed violation:', min(df51['year']),min(df51['month']),min(df51['day_of_month']) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "id": "eba3ffe1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# as seen the traffic crashes dataset starts from 2013 while violation dataset starts from 2014\n",
    "#by using LOC filter\n",
    "df11=df11.loc[df11['year']>2013]\n",
    "df21=df21.loc[df21['year']>2013]\n",
    "df31=df31.loc[df31['year']>2013]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "id": "263c8d9a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['CRASH_RECORD_ID', 'STREET_NAME', 'DATE', 'LATITUDE', 'LONGITUDE',\n",
      "       'POSTED_SPEED_LIMIT', 'WEATHER_CONDITION', 'LIGHTING_CONDITION',\n",
      "       'ROADWAY_SURFACE_COND', 'DAMAGE', 'year', 'month', 'day_of_month',\n",
      "       'hour', 'minute', 'second', 'day'],\n",
      "      dtype='object')\n",
      "\n",
      "Index(['CRASH_RECORD_ID', 'VEHICLE_ID', 'DATE', 'UNIT_TYPE', 'MAKE', 'MODEL',\n",
      "       'VEHICLE_TYPE', 'VEHICLE_USE', 'year', 'month', 'day_of_month', 'hour',\n",
      "       'minute', 'second', 'day'],\n",
      "      dtype='object')\n",
      "\n",
      "Index(['CRASH_RECORD_ID', 'VEHICLE_ID', 'DATE', 'PERSON_TYPE', 'SEX', 'AGE',\n",
      "       'SAFETY_EQUIPMENT', 'AIRBAG_DEPLOYED', 'INJURY_CLASSIFICATION', 'year',\n",
      "       'month', 'day_of_month', 'hour', 'minute', 'second', 'day'],\n",
      "      dtype='object')\n",
      "\n",
      "Index(['CAMERA ID', 'DATE', 'VIOLATIONS', 'LATITUDE', 'LONGITUDE', 'year',\n",
      "       'month', 'day_of_month', 'hour', 'minute', 'second', 'day'],\n",
      "      dtype='object')\n",
      "\n",
      "Index(['CAMERA ID', 'DATE', 'VIOLATIONS', 'LATITUDE', 'LONGITUDE', 'year',\n",
      "       'month', 'day_of_month', 'hour', 'minute', 'second', 'day'],\n",
      "      dtype='object')\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#final columns\n",
    "x=[df11,df21,df31,df41,df51]\n",
    "for i in x:\n",
    "    print(i.columns)\n",
    "    print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "id": "ddd16e72",
   "metadata": {},
   "outputs": [],
   "source": [
    "df11 = df11.loc[df11['LONGITUDE'] < 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "id": "471c2ab4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=df11\n",
    "df2=df21\n",
    "df3=df31\n",
    "df4=df41\n",
    "df5=df51\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "id": "b2ef0e33",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=df1.loc[(df1['LATITUDE']!=0) | (df1[\"LATITUDE\"]!=0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "id": "62a1e01e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3=df3.loc[df3['SEX']!='X']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "id": "9af2d399",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=df2.loc[df2['MODEL']!='UNKNOWN']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "id": "ee974775",
   "metadata": {},
   "outputs": [],
   "source": [
    "#using particular columns only\n",
    "\n",
    "df3=df3[['CRASH_RECORD_ID', 'VEHICLE_ID', 'DATE', 'PERSON_TYPE', 'SEX', 'AGE',\n",
    "       'SAFETY_EQUIPMENT', 'AIRBAG_DEPLOYED', 'INJURY_CLASSIFICATION','year']]\n",
    "\n",
    "df2=df2[['VEHICLE_ID', 'UNIT_TYPE', 'MAKE', 'MODEL','VEHICLE_TYPE', 'VEHICLE_USE','year']]\n",
    "\n",
    "df1=df1[['CRASH_RECORD_ID', 'STREET_NAME', 'LATITUDE', 'LONGITUDE','POSTED_SPEED_LIMIT', 'WEATHER_CONDITION', 'LIGHTING_CONDITION','ROADWAY_SURFACE_COND', 'DAMAGE', 'year', 'month', 'day_of_month','hour', 'minute', 'second', 'day']]\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "id": "29eee3ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df1.dropna(axis = 0, how ='any')\n",
    "df2 = df2.dropna(axis = 0, how ='any')\n",
    "df3 = df3.dropna(axis = 0, how ='any')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "id": "f1553f9d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(12669, 31) 2015\n",
      "(56453, 31) 2016\n",
      "(111382, 31) 2017\n",
      "(160463, 31) 2018\n",
      "(157522, 31) 2019\n",
      "(130452, 31) 2020\n",
      "(154082, 31) 2021\n",
      "(137989, 31) 2022\n"
     ]
    }
   ],
   "source": [
    "\n",
    "t=[]\n",
    "for i in list(range(2015,2023)):\n",
    "    df2_x=df2.loc[df2['year']==(i)]\n",
    "    df3_x=df3.loc[df3['year']==(i)]\n",
    "    df1_x=df1.loc[df1['year']==(i)]\n",
    "    \n",
    "    merge_d23=pd.merge(df2_x,df3_x, how='inner',on='VEHICLE_ID').rename(columns={'CRASH_RECORD_ID_x': 'CRASH_RECORD_ID'}) #merge people and vehicle\n",
    "    \n",
    "    merge_d31=pd.merge(merge_d23,df1_x, how='inner',on='CRASH_RECORD_ID')\n",
    "    print(merge_d31.shape,i)\n",
    "    t.append(merge_d31)\n",
    "\n",
    "    merge_d31.to_csv(str(i)+'.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "id": "58134aa9",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_dataset=pd.concat(t)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "id": "6a55b64f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop columns\n",
    "merge_dataset = merge_dataset.drop(columns=['year_x', 'year_y'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "id": "0b6cab62",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_dataset.to_csv('merge_dataset.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "id": "6983488d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#saving speed and violation data\n",
    "\n",
    "df4.to_csv('clean_red.csv', index=False) #red light\n",
    "df5.to_csv('clean_speed.csv', index=False) #speed"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}