crash-locations-data-wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import sys\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import math\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data_path = r'/Users/alexandraszenti/Documents/GitHub Projects/ARRBHack-2018/_data'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(os.path.join(data_path,'cleaned_crash_locations_all.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Crash_Severity</th>\n",
       "      <th>Crash_Month</th>\n",
       "      <th>Crash_Day_Of_Week</th>\n",
       "      <th>Crash_Hour</th>\n",
       "      <th>Loc_Suburb</th>\n",
       "      <th>Loc_ABS_Remoteness</th>\n",
       "      <th>Crash_Roadway_Feature</th>\n",
       "      <th>Crash_Traffic_Control</th>\n",
       "      <th>Crash_Speed_Limit</th>\n",
       "      <th>Crash_Road_Surface_Condition</th>\n",
       "      <th>...</th>\n",
       "      <th>Count_Casualty_Hospitalised</th>\n",
       "      <th>Count_Casualty_MedicallyTreated</th>\n",
       "      <th>Count_Casualty_MinorInjury</th>\n",
       "      <th>Count_Unit_Car</th>\n",
       "      <th>Count_Unit_Motorcycle_Moped</th>\n",
       "      <th>Count_Unit_Truck</th>\n",
       "      <th>Count_Unit_Bus</th>\n",
       "      <th>Count_Unit_Bicycle</th>\n",
       "      <th>Count_Unit_Pedestrian</th>\n",
       "      <th>Count_Unit_Other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Property damage only</td>\n",
       "      <td>January</td>\n",
       "      <td>Monday</td>\n",
       "      <td>22</td>\n",
       "      <td>Herston</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>Intersection - T-Junction</td>\n",
       "      <td>Operating traffic lights</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Property damage only</td>\n",
       "      <td>January</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2</td>\n",
       "      <td>Caboolture</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>No traffic control</td>\n",
       "      <td>100 - 110 km/h</td>\n",
       "      <td>Sealed - Wet</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Medical treatment</td>\n",
       "      <td>January</td>\n",
       "      <td>Monday</td>\n",
       "      <td>14</td>\n",
       "      <td>Karalee</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>No traffic control</td>\n",
       "      <td>0 - 50 km/h</td>\n",
       "      <td>Sealed - Dry</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Minor injury</td>\n",
       "      <td>January</td>\n",
       "      <td>Monday</td>\n",
       "      <td>0</td>\n",
       "      <td>Dakabin</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>Intersection - T-Junction</td>\n",
       "      <td>No traffic control</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>Sealed - Dry</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Medical treatment</td>\n",
       "      <td>January</td>\n",
       "      <td>Monday</td>\n",
       "      <td>15</td>\n",
       "      <td>Kallangur</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>Intersection - Cross</td>\n",
       "      <td>Operating traffic lights</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>Sealed - Dry</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Crash_Severity Crash_Month Crash_Day_Of_Week  Crash_Hour  Loc_Suburb  \\\n",
       "0  Property damage only     January            Monday          22     Herston   \n",
       "1  Property damage only     January            Monday           2  Caboolture   \n",
       "2     Medical treatment     January            Monday          14     Karalee   \n",
       "3          Minor injury     January            Monday           0     Dakabin   \n",
       "4     Medical treatment     January            Monday          15   Kallangur   \n",
       "\n",
       "  Loc_ABS_Remoteness      Crash_Roadway_Feature     Crash_Traffic_Control  \\\n",
       "0       Major cities  Intersection - T-Junction  Operating traffic lights   \n",
       "1       Major cities         No Roadway Feature        No traffic control   \n",
       "2       Major cities         No Roadway Feature        No traffic control   \n",
       "3       Major cities  Intersection - T-Junction        No traffic control   \n",
       "4       Major cities       Intersection - Cross  Operating traffic lights   \n",
       "\n",
       "  Crash_Speed_Limit Crash_Road_Surface_Condition        ...         \\\n",
       "0           60 km/h                      Unknown        ...          \n",
       "1    100 - 110 km/h                 Sealed - Wet        ...          \n",
       "2       0 - 50 km/h                 Sealed - Dry        ...          \n",
       "3           60 km/h                 Sealed - Dry        ...          \n",
       "4           60 km/h                 Sealed - Dry        ...          \n",
       "\n",
       "  Count_Casualty_Hospitalised Count_Casualty_MedicallyTreated  \\\n",
       "0                           0                               0   \n",
       "1                           0                               0   \n",
       "2                           0                               1   \n",
       "3                           0                               0   \n",
       "4                           0                               1   \n",
       "\n",
       "  Count_Casualty_MinorInjury Count_Unit_Car  Count_Unit_Motorcycle_Moped  \\\n",
       "0                          0            0.0                          0.0   \n",
       "1                          0            1.0                          0.0   \n",
       "2                          0            2.0                          0.0   \n",
       "3                          1            1.0                          0.0   \n",
       "4                          0            3.0                          0.0   \n",
       "\n",
       "   Count_Unit_Truck  Count_Unit_Bus  Count_Unit_Bicycle  \\\n",
       "0               1.0             0.0                 0.0   \n",
       "1               0.0             0.0                 0.0   \n",
       "2               0.0             0.0                 0.0   \n",
       "3               0.0             0.0                 0.0   \n",
       "4               0.0             0.0                 0.0   \n",
       "\n",
       "   Count_Unit_Pedestrian  Count_Unit_Other  \n",
       "0                    0.0               0.0  \n",
       "1                    0.0               0.0  \n",
       "2                    0.0               0.0  \n",
       "3                    0.0               0.0  \n",
       "4                    0.0               0.0  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(306558, 25)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Cyclists involved in accidents\n",
    "cyclist = df['Count_Unit_Bicycle'] > 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Crash_Severity</th>\n",
       "      <th>Crash_Month</th>\n",
       "      <th>Crash_Day_Of_Week</th>\n",
       "      <th>Crash_Hour</th>\n",
       "      <th>Loc_Suburb</th>\n",
       "      <th>Loc_ABS_Remoteness</th>\n",
       "      <th>Crash_Roadway_Feature</th>\n",
       "      <th>Crash_Traffic_Control</th>\n",
       "      <th>Crash_Speed_Limit</th>\n",
       "      <th>Crash_Road_Surface_Condition</th>\n",
       "      <th>...</th>\n",
       "      <th>Count_Casualty_Hospitalised</th>\n",
       "      <th>Count_Casualty_MedicallyTreated</th>\n",
       "      <th>Count_Casualty_MinorInjury</th>\n",
       "      <th>Count_Unit_Car</th>\n",
       "      <th>Count_Unit_Motorcycle_Moped</th>\n",
       "      <th>Count_Unit_Truck</th>\n",
       "      <th>Count_Unit_Bus</th>\n",
       "      <th>Count_Unit_Bicycle</th>\n",
       "      <th>Count_Unit_Pedestrian</th>\n",
       "      <th>Count_Unit_Other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Hospitalisation</td>\n",
       "      <td>January</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>9</td>\n",
       "      <td>Camp Hill</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>No traffic control</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>Sealed - Dry</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Medical treatment</td>\n",
       "      <td>January</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>17</td>\n",
       "      <td>Bracken Ridge</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>Intersection - T-Junction</td>\n",
       "      <td>No traffic control</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>Sealed - Dry</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Hospitalisation</td>\n",
       "      <td>January</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>17</td>\n",
       "      <td>Coorparoo</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>No traffic control</td>\n",
       "      <td>0 - 50 km/h</td>\n",
       "      <td>Sealed - Dry</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>Hospitalisation</td>\n",
       "      <td>January</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>7</td>\n",
       "      <td>Highgate Hill</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>Intersection - T-Junction</td>\n",
       "      <td>No traffic control</td>\n",
       "      <td>0 - 50 km/h</td>\n",
       "      <td>Sealed - Dry</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>Hospitalisation</td>\n",
       "      <td>January</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>12</td>\n",
       "      <td>Kingston</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>No traffic control</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>Sealed - Dry</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Crash_Severity Crash_Month Crash_Day_Of_Week  Crash_Hour  \\\n",
       "32    Hospitalisation     January           Tuesday           9   \n",
       "36  Medical treatment     January           Tuesday          17   \n",
       "79    Hospitalisation     January         Wednesday          17   \n",
       "85    Hospitalisation     January         Wednesday           7   \n",
       "91    Hospitalisation     January         Wednesday          12   \n",
       "\n",
       "       Loc_Suburb Loc_ABS_Remoteness      Crash_Roadway_Feature  \\\n",
       "32      Camp Hill       Major cities         No Roadway Feature   \n",
       "36  Bracken Ridge       Major cities  Intersection - T-Junction   \n",
       "79      Coorparoo       Major cities         No Roadway Feature   \n",
       "85  Highgate Hill       Major cities  Intersection - T-Junction   \n",
       "91       Kingston       Major cities         No Roadway Feature   \n",
       "\n",
       "   Crash_Traffic_Control Crash_Speed_Limit Crash_Road_Surface_Condition  \\\n",
       "32    No traffic control           60 km/h                 Sealed - Dry   \n",
       "36    No traffic control           60 km/h                 Sealed - Dry   \n",
       "79    No traffic control       0 - 50 km/h                 Sealed - Dry   \n",
       "85    No traffic control       0 - 50 km/h                 Sealed - Dry   \n",
       "91    No traffic control           60 km/h                 Sealed - Dry   \n",
       "\n",
       "          ...        Count_Casualty_Hospitalised  \\\n",
       "32        ...                                  1   \n",
       "36        ...                                  0   \n",
       "79        ...                                  1   \n",
       "85        ...                                  1   \n",
       "91        ...                                  1   \n",
       "\n",
       "   Count_Casualty_MedicallyTreated Count_Casualty_MinorInjury Count_Unit_Car  \\\n",
       "32                               0                          0            1.0   \n",
       "36                               1                          0            1.0   \n",
       "79                               0                          0            1.0   \n",
       "85                               0                          0            0.0   \n",
       "91                               0                          0            1.0   \n",
       "\n",
       "    Count_Unit_Motorcycle_Moped  Count_Unit_Truck  Count_Unit_Bus  \\\n",
       "32                          0.0               0.0             1.0   \n",
       "36                          0.0               0.0             0.0   \n",
       "79                          0.0               0.0             0.0   \n",
       "85                          0.0               1.0             0.0   \n",
       "91                          0.0               0.0             0.0   \n",
       "\n",
       "    Count_Unit_Bicycle  Count_Unit_Pedestrian  Count_Unit_Other  \n",
       "32                 1.0                    0.0               0.0  \n",
       "36                 1.0                    0.0               0.0  \n",
       "79                 1.0                    0.0               0.0  \n",
       "85                 1.0                    0.0               0.0  \n",
       "91                 1.0                    0.0               0.0  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[cyclist].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Medical treatment       5397\n",
       "Hospitalisation         5116\n",
       "Minor injury            2448\n",
       "Fatal                    142\n",
       "Property damage only      27\n",
       "Name: Crash_Severity, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[cyclist]['Crash_Severity'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(13130, 25)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[cyclist].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.40045696877380044\n"
     ]
    }
   ],
   "source": [
    "# probability of a cyclist having a fatal or serious injuries after and accident\n",
    "print((5116+142)/13130)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Pedestrians involved in accidents.\n",
    "pedestrian = df['Count_Unit_Pedestrian'] > 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Hospitalisation         6408\n",
       "Medical treatment       4158\n",
       "Minor injury            1506\n",
       "Fatal                    569\n",
       "Property damage only       4\n",
       "Name: Crash_Severity, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[pedestrian]['Crash_Severity'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(12645, 25)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[pedestrian].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.5518386714116251\n"
     ]
    }
   ],
   "source": [
    "# probability of a pedestrian having a fatal or serious injuries after and accident\n",
    "print(6978/12645)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}