01-cyclist_fsi.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "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\n",
    "from sklearn.utils import shuffle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data_path = r'/Users/alexandraszenti/Documents/GitHub Projects/ARRBHack-2018/_data'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(os.path.join(data_path,'road_crash_locations.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Crash_Ref_Number', 'Crash_Severity', 'Crash_Year', 'Crash_Month',\n",
       "       'Crash_Day_Of_Week', 'Crash_Hour', 'Crash_Nature', 'Crash_Type',\n",
       "       'Crash_Longitude_GDA94', 'Crash_Latitude_GDA94', 'Crash_Street',\n",
       "       'Crash_Street_Intersecting', 'Loc_Suburb', 'Loc_Local_Government_Area',\n",
       "       'Loc_Post_Code', 'Loc_Police_Division', 'Loc_Police_District',\n",
       "       'Loc_Police_Region', 'Loc_Queensland_Transport_Region',\n",
       "       'Loc_Main_Roads_Region', 'Loc_ABS_Statistical_Area_2',\n",
       "       'Loc_ABS_Statistical_Area_3', 'Loc_ABS_Statistical_Area_4',\n",
       "       'Loc_ABS_Remoteness', 'Loc_State_Electorate', 'Loc_Federal_Electorate',\n",
       "       'Crash_Controlling_Authority', 'Crash_Roadway_Feature',\n",
       "       'Crash_Traffic_Control', 'Crash_Speed_Limit',\n",
       "       'Crash_Road_Surface_Condition', 'Crash_Atmospheric_Condition',\n",
       "       'Crash_Lighting_Condition', 'Crash_Road_Horiz_Align',\n",
       "       'Crash_Road_Vert_Align', 'Crash_DCA_Code', 'Crash_DCA_Description',\n",
       "       'Crash_DCA_Group_Description', 'Count_Casualty_Fatality',\n",
       "       'Count_Casualty_Hospitalised', 'Count_Casualty_MedicallyTreated',\n",
       "       'Count_Casualty_MinorInjury', 'Count_Casualty_Total', 'Count_Unit_Car',\n",
       "       'Count_Unit_Motorcycle_Moped', 'Count_Unit_Truck', 'Count_Unit_Bus',\n",
       "       'Count_Unit_Bicycle', 'Count_Unit_Pedestrian', 'Count_Unit_Other'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(306558, 50)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cleaned = df[['Crash_Severity', 'Crash_Month', 'Crash_Day_Of_Week', 'Crash_Hour',\n",
    "             'Crash_Longitude_GDA94', 'Crash_Latitude_GDA94',\n",
    "             'Loc_Suburb',\n",
    "             'Loc_ABS_Remoteness',\n",
    "             'Crash_Roadway_Feature',\n",
    "             'Crash_Traffic_Control', 'Crash_Speed_Limit',\n",
    "             'Crash_Road_Surface_Condition', 'Crash_Atmospheric_Condition',\n",
    "             'Crash_Lighting_Condition', 'Crash_Road_Horiz_Align', 'Crash_Road_Vert_Align',\n",
    "             'Count_Casualty_Fatality',\n",
    "             'Count_Casualty_Hospitalised', 'Count_Casualty_MedicallyTreated',\n",
    "             'Count_Casualty_MinorInjury', 'Count_Casualty_Total', 'Count_Unit_Car',\n",
    "             'Count_Unit_Motorcycle_Moped', 'Count_Unit_Truck', 'Count_Unit_Bus',\n",
    "             'Count_Unit_Bicycle', 'Count_Unit_Pedestrian', 'Count_Unit_Other' \n",
    "             ]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Removing rows without valid GPS coordinates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "isValidLng = cleaned['Crash_Longitude_GDA94'] != 0\n",
    "isValidLat = cleaned['Crash_Latitude_GDA94'] != 0\n",
    "cleaned = cleaned[isValidLng][isValidLat]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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_Severity</th>\n",
       "      <th>Crash_Month</th>\n",
       "      <th>Crash_Day_Of_Week</th>\n",
       "      <th>Crash_Hour</th>\n",
       "      <th>Crash_Longitude_GDA94</th>\n",
       "      <th>Crash_Latitude_GDA94</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>...</th>\n",
       "      <th>Count_Casualty_MedicallyTreated</th>\n",
       "      <th>Count_Casualty_MinorInjury</th>\n",
       "      <th>Count_Casualty_Total</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>153.028793</td>\n",
       "      <td>-27.449659</td>\n",
       "      <td>Herston</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>Intersection - T-Junction</td>\n",
       "      <td>Operating traffic lights</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>152.966657</td>\n",
       "      <td>-27.049444</td>\n",
       "      <td>Caboolture</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>No traffic control</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>152.821788</td>\n",
       "      <td>-27.573498</td>\n",
       "      <td>Karalee</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>No traffic control</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>152.974398</td>\n",
       "      <td>-27.225505</td>\n",
       "      <td>Dakabin</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>Intersection - T-Junction</td>\n",
       "      <td>No traffic control</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>153.002385</td>\n",
       "      <td>-27.248969</td>\n",
       "      <td>Kallangur</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>Intersection - Cross</td>\n",
       "      <td>Operating traffic lights</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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 × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Crash_Severity Crash_Month Crash_Day_Of_Week  Crash_Hour  \\\n",
       "0  Property damage only     January            Monday          22   \n",
       "1  Property damage only     January            Monday           2   \n",
       "2     Medical treatment     January            Monday          14   \n",
       "3          Minor injury     January            Monday           0   \n",
       "4     Medical treatment     January            Monday          15   \n",
       "\n",
       "   Crash_Longitude_GDA94  Crash_Latitude_GDA94  Loc_Suburb Loc_ABS_Remoteness  \\\n",
       "0             153.028793            -27.449659     Herston       Major cities   \n",
       "1             152.966657            -27.049444  Caboolture       Major cities   \n",
       "2             152.821788            -27.573498     Karalee       Major cities   \n",
       "3             152.974398            -27.225505     Dakabin       Major cities   \n",
       "4             153.002385            -27.248969   Kallangur       Major cities   \n",
       "\n",
       "       Crash_Roadway_Feature     Crash_Traffic_Control        ...         \\\n",
       "0  Intersection - T-Junction  Operating traffic lights        ...          \n",
       "1         No Roadway Feature        No traffic control        ...          \n",
       "2         No Roadway Feature        No traffic control        ...          \n",
       "3  Intersection - T-Junction        No traffic control        ...          \n",
       "4       Intersection - Cross  Operating traffic lights        ...          \n",
       "\n",
       "  Count_Casualty_MedicallyTreated Count_Casualty_MinorInjury  \\\n",
       "0                               0                          0   \n",
       "1                               0                          0   \n",
       "2                               1                          0   \n",
       "3                               0                          1   \n",
       "4                               1                          0   \n",
       "\n",
       "  Count_Casualty_Total Count_Unit_Car Count_Unit_Motorcycle_Moped  \\\n",
       "0                    0            0.0                         0.0   \n",
       "1                    0            1.0                         0.0   \n",
       "2                    1            2.0                         0.0   \n",
       "3                    1            1.0                         0.0   \n",
       "4                    1            3.0                         0.0   \n",
       "\n",
       "  Count_Unit_Truck  Count_Unit_Bus  Count_Unit_Bicycle  Count_Unit_Pedestrian  \\\n",
       "0              1.0             0.0                 0.0                    0.0   \n",
       "1              0.0             0.0                 0.0                    0.0   \n",
       "2              0.0             0.0                 0.0                    0.0   \n",
       "3              0.0             0.0                 0.0                    0.0   \n",
       "4              0.0             0.0                 0.0                    0.0   \n",
       "\n",
       "   Count_Unit_Other  \n",
       "0               0.0  \n",
       "1               0.0  \n",
       "2               0.0  \n",
       "3               0.0  \n",
       "4               0.0  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(305846, 28)"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cleaning features with conditions data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "isCrashRoadSurfaceConditionNotUnknown = cleaned['Crash_Road_Surface_Condition'] != 'Unknown'\n",
    "isCrashAtmosphericConditionNotUnknown = cleaned['Crash_Atmospheric_Condition'] != 'Unknown'\n",
    "isCrashLightingConditionNotUnknown = cleaned['Crash_Lighting_Condition'] != 'Unknown'\n",
    "cleaned = cleaned[isCrashRoadSurfaceConditionNotUnknown & isCrashAtmosphericConditionNotUnknown & isCrashLightingConditionNotUnknown]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(305134, 28)"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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_Severity</th>\n",
       "      <th>Crash_Month</th>\n",
       "      <th>Crash_Day_Of_Week</th>\n",
       "      <th>Crash_Hour</th>\n",
       "      <th>Crash_Longitude_GDA94</th>\n",
       "      <th>Crash_Latitude_GDA94</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>...</th>\n",
       "      <th>Count_Casualty_MedicallyTreated</th>\n",
       "      <th>Count_Casualty_MinorInjury</th>\n",
       "      <th>Count_Casualty_Total</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>1</th>\n",
       "      <td>Property damage only</td>\n",
       "      <td>January</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2</td>\n",
       "      <td>152.966657</td>\n",
       "      <td>-27.049444</td>\n",
       "      <td>Caboolture</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>No traffic control</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>152.821788</td>\n",
       "      <td>-27.573498</td>\n",
       "      <td>Karalee</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>No traffic control</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>152.974398</td>\n",
       "      <td>-27.225505</td>\n",
       "      <td>Dakabin</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>Intersection - T-Junction</td>\n",
       "      <td>No traffic control</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>153.002385</td>\n",
       "      <td>-27.248969</td>\n",
       "      <td>Kallangur</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>Intersection - Cross</td>\n",
       "      <td>Operating traffic lights</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Minor injury</td>\n",
       "      <td>January</td>\n",
       "      <td>Monday</td>\n",
       "      <td>11</td>\n",
       "      <td>153.088907</td>\n",
       "      <td>-27.680827</td>\n",
       "      <td>Crestmead</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>Intersection - Cross</td>\n",
       "      <td>Operating traffic lights</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Crash_Severity Crash_Month Crash_Day_Of_Week  Crash_Hour  \\\n",
       "1  Property damage only     January            Monday           2   \n",
       "2     Medical treatment     January            Monday          14   \n",
       "3          Minor injury     January            Monday           0   \n",
       "4     Medical treatment     January            Monday          15   \n",
       "5          Minor injury     January            Monday          11   \n",
       "\n",
       "   Crash_Longitude_GDA94  Crash_Latitude_GDA94  Loc_Suburb Loc_ABS_Remoteness  \\\n",
       "1             152.966657            -27.049444  Caboolture       Major cities   \n",
       "2             152.821788            -27.573498     Karalee       Major cities   \n",
       "3             152.974398            -27.225505     Dakabin       Major cities   \n",
       "4             153.002385            -27.248969   Kallangur       Major cities   \n",
       "5             153.088907            -27.680827   Crestmead       Major cities   \n",
       "\n",
       "       Crash_Roadway_Feature     Crash_Traffic_Control        ...         \\\n",
       "1         No Roadway Feature        No traffic control        ...          \n",
       "2         No Roadway Feature        No traffic control        ...          \n",
       "3  Intersection - T-Junction        No traffic control        ...          \n",
       "4       Intersection - Cross  Operating traffic lights        ...          \n",
       "5       Intersection - Cross  Operating traffic lights        ...          \n",
       "\n",
       "  Count_Casualty_MedicallyTreated Count_Casualty_MinorInjury  \\\n",
       "1                               0                          0   \n",
       "2                               1                          0   \n",
       "3                               0                          1   \n",
       "4                               1                          0   \n",
       "5                               0                          1   \n",
       "\n",
       "  Count_Casualty_Total Count_Unit_Car Count_Unit_Motorcycle_Moped  \\\n",
       "1                    0            1.0                         0.0   \n",
       "2                    1            2.0                         0.0   \n",
       "3                    1            1.0                         0.0   \n",
       "4                    1            3.0                         0.0   \n",
       "5                    1            2.0                         0.0   \n",
       "\n",
       "  Count_Unit_Truck  Count_Unit_Bus  Count_Unit_Bicycle  Count_Unit_Pedestrian  \\\n",
       "1              0.0             0.0                 0.0                    0.0   \n",
       "2              0.0             0.0                 0.0                    0.0   \n",
       "3              0.0             0.0                 0.0                    0.0   \n",
       "4              0.0             0.0                 0.0                    0.0   \n",
       "5              0.0             0.0                 0.0                    0.0   \n",
       "\n",
       "   Count_Unit_Other  \n",
       "1               0.0  \n",
       "2               0.0  \n",
       "3               0.0  \n",
       "4               0.0  \n",
       "5               0.0  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Only dealing with incidents in major cities\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(182492, 28)"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "isMajorCities = cleaned['Loc_ABS_Remoteness'] == 'Major cities'\n",
    "cleaned = cleaned[isMajorCities]\n",
    "cleaned.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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_Severity</th>\n",
       "      <th>Crash_Month</th>\n",
       "      <th>Crash_Day_Of_Week</th>\n",
       "      <th>Crash_Hour</th>\n",
       "      <th>Crash_Longitude_GDA94</th>\n",
       "      <th>Crash_Latitude_GDA94</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>...</th>\n",
       "      <th>Count_Casualty_MedicallyTreated</th>\n",
       "      <th>Count_Casualty_MinorInjury</th>\n",
       "      <th>Count_Casualty_Total</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>1</th>\n",
       "      <td>Property damage only</td>\n",
       "      <td>January</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2</td>\n",
       "      <td>152.966657</td>\n",
       "      <td>-27.049444</td>\n",
       "      <td>Caboolture</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>No traffic control</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>152.821788</td>\n",
       "      <td>-27.573498</td>\n",
       "      <td>Karalee</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>No traffic control</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>152.974398</td>\n",
       "      <td>-27.225505</td>\n",
       "      <td>Dakabin</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>Intersection - T-Junction</td>\n",
       "      <td>No traffic control</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>153.002385</td>\n",
       "      <td>-27.248969</td>\n",
       "      <td>Kallangur</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>Intersection - Cross</td>\n",
       "      <td>Operating traffic lights</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Minor injury</td>\n",
       "      <td>January</td>\n",
       "      <td>Monday</td>\n",
       "      <td>11</td>\n",
       "      <td>153.088907</td>\n",
       "      <td>-27.680827</td>\n",
       "      <td>Crestmead</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>Intersection - Cross</td>\n",
       "      <td>Operating traffic lights</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Crash_Severity Crash_Month Crash_Day_Of_Week  Crash_Hour  \\\n",
       "1  Property damage only     January            Monday           2   \n",
       "2     Medical treatment     January            Monday          14   \n",
       "3          Minor injury     January            Monday           0   \n",
       "4     Medical treatment     January            Monday          15   \n",
       "5          Minor injury     January            Monday          11   \n",
       "\n",
       "   Crash_Longitude_GDA94  Crash_Latitude_GDA94  Loc_Suburb Loc_ABS_Remoteness  \\\n",
       "1             152.966657            -27.049444  Caboolture       Major cities   \n",
       "2             152.821788            -27.573498     Karalee       Major cities   \n",
       "3             152.974398            -27.225505     Dakabin       Major cities   \n",
       "4             153.002385            -27.248969   Kallangur       Major cities   \n",
       "5             153.088907            -27.680827   Crestmead       Major cities   \n",
       "\n",
       "       Crash_Roadway_Feature     Crash_Traffic_Control        ...         \\\n",
       "1         No Roadway Feature        No traffic control        ...          \n",
       "2         No Roadway Feature        No traffic control        ...          \n",
       "3  Intersection - T-Junction        No traffic control        ...          \n",
       "4       Intersection - Cross  Operating traffic lights        ...          \n",
       "5       Intersection - Cross  Operating traffic lights        ...          \n",
       "\n",
       "  Count_Casualty_MedicallyTreated Count_Casualty_MinorInjury  \\\n",
       "1                               0                          0   \n",
       "2                               1                          0   \n",
       "3                               0                          1   \n",
       "4                               1                          0   \n",
       "5                               0                          1   \n",
       "\n",
       "  Count_Casualty_Total Count_Unit_Car Count_Unit_Motorcycle_Moped  \\\n",
       "1                    0            1.0                         0.0   \n",
       "2                    1            2.0                         0.0   \n",
       "3                    1            1.0                         0.0   \n",
       "4                    1            3.0                         0.0   \n",
       "5                    1            2.0                         0.0   \n",
       "\n",
       "  Count_Unit_Truck  Count_Unit_Bus  Count_Unit_Bicycle  Count_Unit_Pedestrian  \\\n",
       "1              0.0             0.0                 0.0                    0.0   \n",
       "2              0.0             0.0                 0.0                    0.0   \n",
       "3              0.0             0.0                 0.0                    0.0   \n",
       "4              0.0             0.0                 0.0                    0.0   \n",
       "5              0.0             0.0                 0.0                    0.0   \n",
       "\n",
       "   Count_Unit_Other  \n",
       "1               0.0  \n",
       "2               0.0  \n",
       "3               0.0  \n",
       "4               0.0  \n",
       "5               0.0  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Straight                  148032\n",
       "Curved - view open         28074\n",
       "Curved - view obscured      6385\n",
       "Unknown                        1\n",
       "Name: Crash_Road_Horiz_Align, dtype: int64"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned['Crash_Road_Horiz_Align'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Level      135678\n",
       "Grade       30868\n",
       "Crest        9797\n",
       "Dip          6148\n",
       "Unknown         1\n",
       "Name: Crash_Road_Vert_Align, dtype: int64"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned['Crash_Road_Vert_Align'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Removing 'Unkown' values from Crash_Road_Horiz_Align and Crash_Road_Vert_Align"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(182491, 28)"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "isHorizAlignKnown = cleaned['Crash_Road_Horiz_Align'] != 'Unknown'\n",
    "isVertAlignKnown = cleaned['Crash_Road_Vert_Align'] != 'Unknown'\n",
    "cleaned = cleaned[isHorizAlignKnown & isVertAlignKnown]\n",
    "cleaned.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    179066\n",
       "True       3425\n",
       "Name: Cyclist_FSI, dtype: int64"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned['Cyclist_FSI'] = cleaned.apply(lambda row: (row.Crash_Severity in ['Fatal', 'Hospitalisation'] and row.Count_Unit_Bicycle > 0), axis=1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    179066\n",
       "True       3425\n",
       "Name: Cyclist_FSI, dtype: int64"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned['Cyclist_FSI'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "shuffled = shuffle(cleaned)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "shuffled.to_csv(os.path.join(data_path,'cyclist_fsi_shuffled.csv'),\n",
    "                          index=False,\n",
    "                         columns=['Crash_Month', 'Crash_Day_Of_Week', 'Crash_Hour',\n",
    "                                 'Crash_Longitude_GDA94', 'Crash_Latitude_GDA94',\n",
    "                                 'Loc_Suburb',\n",
    "                                 'Crash_Roadway_Feature',\n",
    "                                 'Crash_Traffic_Control', 'Crash_Speed_Limit',\n",
    "                                 'Crash_Road_Surface_Condition', 'Crash_Atmospheric_Condition',\n",
    "                                 'Crash_Lighting_Condition', 'Crash_Road_Horiz_Align', 'Crash_Road_Vert_Align',\n",
    "                                 'Cyclist_FSI' \n",
    "                                 ])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    179066\n",
       "True       3425\n",
       "Name: Cyclist_FSI, dtype: int64"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned['Cyclist_FSI'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    9810\n",
       "True      190\n",
       "Name: Cyclist_FSI, dtype: int64"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "shuffled[:10000]['Cyclist_FSI'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "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_Hour</th>\n",
       "      <th>Crash_Longitude_GDA94</th>\n",
       "      <th>Crash_Latitude_GDA94</th>\n",
       "      <th>Cyclist_FSI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Crash_Hour</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.011897</td>\n",
       "      <td>-0.012481</td>\n",
       "      <td>-0.029247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Crash_Longitude_GDA94</th>\n",
       "      <td>0.011897</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.528998</td>\n",
       "      <td>0.022021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Crash_Latitude_GDA94</th>\n",
       "      <td>-0.012481</td>\n",
       "      <td>-0.528998</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cyclist_FSI</th>\n",
       "      <td>-0.029247</td>\n",
       "      <td>0.022021</td>\n",
       "      <td>0.000617</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       Crash_Hour  Crash_Longitude_GDA94  \\\n",
       "Crash_Hour               1.000000               0.011897   \n",
       "Crash_Longitude_GDA94    0.011897               1.000000   \n",
       "Crash_Latitude_GDA94    -0.012481              -0.528998   \n",
       "Cyclist_FSI             -0.029247               0.022021   \n",
       "\n",
       "                       Crash_Latitude_GDA94  Cyclist_FSI  \n",
       "Crash_Hour                        -0.012481    -0.029247  \n",
       "Crash_Longitude_GDA94             -0.528998     0.022021  \n",
       "Crash_Latitude_GDA94               1.000000     0.000617  \n",
       "Cyclist_FSI                        0.000617     1.000000  "
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "AWS Results: \n",
    "* Model AUC: 0.744\n",
    "* Precision: 0.4286\n",
    "* Recall: 0.003\n"
   ]
  },
  {
   "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
}