cyclists_crash_locations.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "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": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data_path = r'/Users/alexandraszenti/Documents/GitHub Projects/ARRBHack-2018/_data'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(os.path.join(data_path,'cleaned_crash_locations_all.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(306558, 26)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analysing environments where cyclists are more likely to be in an accident\n",
    "\n",
    "In order to predict when a cyclist is heading towards a situation where an incident is more likely to happen, we need to make some assumptions regarding the current data:\n",
    "- Unknown data is not useful and if a feature has unkown fields we should exclude that from our model\n",
    "- We don't have any information regards who has been hospitalised if there were more than one casualties in an accidents.. so we will assume that always the weaker role gets the more serious injures: pedestrian > cyclist > moped > car > bus / truck\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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_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>284838</th>\n",
       "      <td>Minor injury</td>\n",
       "      <td>July</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>8</td>\n",
       "      <td>Trinity Beach</td>\n",
       "      <td>Outer regional</td>\n",
       "      <td>Intersection - Roundabout</td>\n",
       "      <td>Give way sign</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>Sealed - Dry</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</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>208774</th>\n",
       "      <td>Property damage only</td>\n",
       "      <td>March</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>23</td>\n",
       "      <td>Chermside</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 - Wet</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>203346</th>\n",
       "      <td>Hospitalisation</td>\n",
       "      <td>December</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>13</td>\n",
       "      <td>Mackay</td>\n",
       "      <td>Inner regional</td>\n",
       "      <td>Intersection - Cross</td>\n",
       "      <td>Give way sign</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>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>129596</th>\n",
       "      <td>Property damage only</td>\n",
       "      <td>September</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>11</td>\n",
       "      <td>Cairns City</td>\n",
       "      <td>Outer regional</td>\n",
       "      <td>Intersection - Cross</td>\n",
       "      <td>Operating traffic lights</td>\n",
       "      <td>0 - 50 km/h</td>\n",
       "      <td>Sealed - Dry</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>160471</th>\n",
       "      <td>Minor injury</td>\n",
       "      <td>February</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>22</td>\n",
       "      <td>Annerley</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 - Wet</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              Crash_Severity Crash_Month Crash_Day_Of_Week  Crash_Hour  \\\n",
       "284838          Minor injury        July           Tuesday           8   \n",
       "208774  Property damage only       March          Saturday          23   \n",
       "203346       Hospitalisation    December          Thursday          13   \n",
       "129596  Property damage only   September         Wednesday          11   \n",
       "160471          Minor injury    February            Sunday          22   \n",
       "\n",
       "           Loc_Suburb Loc_ABS_Remoteness      Crash_Roadway_Feature  \\\n",
       "284838  Trinity Beach     Outer regional  Intersection - Roundabout   \n",
       "208774      Chermside       Major cities       Intersection - Cross   \n",
       "203346         Mackay     Inner regional       Intersection - Cross   \n",
       "129596    Cairns City     Outer regional       Intersection - Cross   \n",
       "160471       Annerley       Major cities         No Roadway Feature   \n",
       "\n",
       "           Crash_Traffic_Control Crash_Speed_Limit  \\\n",
       "284838             Give way sign           60 km/h   \n",
       "208774  Operating traffic lights           60 km/h   \n",
       "203346             Give way sign           60 km/h   \n",
       "129596  Operating traffic lights       0 - 50 km/h   \n",
       "160471        No traffic control           60 km/h   \n",
       "\n",
       "       Crash_Road_Surface_Condition        ...         \\\n",
       "284838                 Sealed - Dry        ...          \n",
       "208774                 Sealed - Wet        ...          \n",
       "203346                 Sealed - Dry        ...          \n",
       "129596                 Sealed - Dry        ...          \n",
       "160471                 Sealed - Wet        ...          \n",
       "\n",
       "       Count_Casualty_MedicallyTreated Count_Casualty_MinorInjury  \\\n",
       "284838                               0                          2   \n",
       "208774                               0                          0   \n",
       "203346                               0                          0   \n",
       "129596                               0                          0   \n",
       "160471                               0                          1   \n",
       "\n",
       "       Count_Casualty_Total Count_Unit_Car  Count_Unit_Motorcycle_Moped  \\\n",
       "284838                    2            2.0                          0.0   \n",
       "208774                    0            2.0                          0.0   \n",
       "203346                    1            2.0                          0.0   \n",
       "129596                    0            2.0                          0.0   \n",
       "160471                    1            1.0                          0.0   \n",
       "\n",
       "        Count_Unit_Truck  Count_Unit_Bus  Count_Unit_Bicycle  \\\n",
       "284838               0.0             0.0                 0.0   \n",
       "208774               0.0             0.0                 0.0   \n",
       "203346               0.0             0.0                 0.0   \n",
       "129596               0.0             0.0                 0.0   \n",
       "160471               0.0             0.0                 0.0   \n",
       "\n",
       "        Count_Unit_Pedestrian  Count_Unit_Other  \n",
       "284838                    0.0               0.0  \n",
       "208774                    0.0               0.0  \n",
       "203346                    0.0               0.0  \n",
       "129596                    0.0               0.0  \n",
       "160471                    0.0               0.0  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = shuffle(df)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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_Longitude_GDA94</th>\n",
       "      <th>Crash_Latitude_GDA94</th>\n",
       "      <th>Crash_Roadway_Feature</th>\n",
       "      <th>Crash_Speed_Limit</th>\n",
       "      <th>...</th>\n",
       "      <th>Crash_Atmospheric_Condition</th>\n",
       "      <th>Crash_Lighting_Condition</th>\n",
       "      <th>Crash_Road_Horiz_Align</th>\n",
       "      <th>Crash_Road_Vert_Align</th>\n",
       "      <th>Count_Casualty_Fatality</th>\n",
       "      <th>Count_Casualty_Hospitalised</th>\n",
       "      <th>Count_Casualty_MedicallyTreated</th>\n",
       "      <th>Count_Casualty_MinorInjury</th>\n",
       "      <th>Count_Unit_Pedestrian</th>\n",
       "      <th>Count_Unit_Bicycle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>290215</th>\n",
       "      <td>Hospitalisation</td>\n",
       "      <td>December</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>16</td>\n",
       "      <td>Greenslopes</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>153.044295</td>\n",
       "      <td>-27.498582</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Daylight</td>\n",
       "      <td>Curved - view open</td>\n",
       "      <td>Level</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303412</th>\n",
       "      <td>Minor injury</td>\n",
       "      <td>January</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>15</td>\n",
       "      <td>Longreach</td>\n",
       "      <td>Very remote</td>\n",
       "      <td>144.259210</td>\n",
       "      <td>-23.437493</td>\n",
       "      <td>Intersection - Cross</td>\n",
       "      <td>0 - 50 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Daylight</td>\n",
       "      <td>Curved - view open</td>\n",
       "      <td>Level</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1721</th>\n",
       "      <td>Property damage only</td>\n",
       "      <td>February</td>\n",
       "      <td>Friday</td>\n",
       "      <td>3</td>\n",
       "      <td>Caboolture</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>152.949010</td>\n",
       "      <td>-27.084622</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Raining</td>\n",
       "      <td>Darkness - Not lighted</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Level</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99609</th>\n",
       "      <td>Property damage only</td>\n",
       "      <td>June</td>\n",
       "      <td>Friday</td>\n",
       "      <td>10</td>\n",
       "      <td>Rochedale</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>153.107294</td>\n",
       "      <td>-27.572057</td>\n",
       "      <td>Intersection - Interchange</td>\n",
       "      <td>100 - 110 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Daylight</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Level</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37085</th>\n",
       "      <td>Property damage only</td>\n",
       "      <td>September</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>18</td>\n",
       "      <td>Coominglah</td>\n",
       "      <td>Remote</td>\n",
       "      <td>150.848664</td>\n",
       "      <td>-24.765539</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>100 - 110 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Dawn/Dusk</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Level</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268308</th>\n",
       "      <td>Hospitalisation</td>\n",
       "      <td>March</td>\n",
       "      <td>Monday</td>\n",
       "      <td>1</td>\n",
       "      <td>Belmont</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>153.122800</td>\n",
       "      <td>-27.531760</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>100 - 110 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Darkness - Lighted</td>\n",
       "      <td>Curved - view open</td>\n",
       "      <td>Level</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191711</th>\n",
       "      <td>Minor injury</td>\n",
       "      <td>June</td>\n",
       "      <td>Monday</td>\n",
       "      <td>15</td>\n",
       "      <td>Verrierdale</td>\n",
       "      <td>Inner regional</td>\n",
       "      <td>152.994855</td>\n",
       "      <td>-26.476293</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>80 - 90 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Daylight</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Crest</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164101</th>\n",
       "      <td>Minor injury</td>\n",
       "      <td>March</td>\n",
       "      <td>Monday</td>\n",
       "      <td>9</td>\n",
       "      <td>Cannon Hill</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>153.095897</td>\n",
       "      <td>-27.472590</td>\n",
       "      <td>Median Opening</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Daylight</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Level</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278634</th>\n",
       "      <td>Medical treatment</td>\n",
       "      <td>January</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>14</td>\n",
       "      <td>Kuranda</td>\n",
       "      <td>Outer regional</td>\n",
       "      <td>145.662065</td>\n",
       "      <td>-16.831452</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Daylight</td>\n",
       "      <td>Curved - view open</td>\n",
       "      <td>Grade</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>293658</th>\n",
       "      <td>Medical treatment</td>\n",
       "      <td>April</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>10</td>\n",
       "      <td>Dicky Beach</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>153.134640</td>\n",
       "      <td>-26.782818</td>\n",
       "      <td>Intersection - T-Junction</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Daylight</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Level</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78199</th>\n",
       "      <td>Hospitalisation</td>\n",
       "      <td>July</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>20</td>\n",
       "      <td>Thabeban</td>\n",
       "      <td>Inner regional</td>\n",
       "      <td>152.364047</td>\n",
       "      <td>-24.899680</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>80 - 90 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Darkness - Not lighted</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Grade</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>286845</th>\n",
       "      <td>Hospitalisation</td>\n",
       "      <td>September</td>\n",
       "      <td>Friday</td>\n",
       "      <td>21</td>\n",
       "      <td>North Lakes</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>153.011630</td>\n",
       "      <td>-27.236688</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Raining</td>\n",
       "      <td>Darkness - Lighted</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Grade</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58885</th>\n",
       "      <td>Property damage only</td>\n",
       "      <td>August</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>20</td>\n",
       "      <td>Riverview</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>152.854331</td>\n",
       "      <td>-27.598006</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>100 - 110 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Darkness - Lighted</td>\n",
       "      <td>Curved - view open</td>\n",
       "      <td>Dip</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>284028</th>\n",
       "      <td>Medical treatment</td>\n",
       "      <td>June</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>19</td>\n",
       "      <td>Raglan</td>\n",
       "      <td>Outer regional</td>\n",
       "      <td>150.814744</td>\n",
       "      <td>-23.713410</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>100 - 110 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Raining</td>\n",
       "      <td>Darkness - Not lighted</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Level</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67246</th>\n",
       "      <td>Property damage only</td>\n",
       "      <td>January</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>7</td>\n",
       "      <td>Rangeville</td>\n",
       "      <td>Inner regional</td>\n",
       "      <td>151.983166</td>\n",
       "      <td>-27.591471</td>\n",
       "      <td>Intersection - T-Junction</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Daylight</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Grade</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25611</th>\n",
       "      <td>Minor injury</td>\n",
       "      <td>March</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>11</td>\n",
       "      <td>Tinana</td>\n",
       "      <td>Inner regional</td>\n",
       "      <td>152.674887</td>\n",
       "      <td>-25.560631</td>\n",
       "      <td>Intersection - Cross</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Daylight</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Level</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95515</th>\n",
       "      <td>Hospitalisation</td>\n",
       "      <td>April</td>\n",
       "      <td>Friday</td>\n",
       "      <td>15</td>\n",
       "      <td>Mount Warren Park</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>153.194333</td>\n",
       "      <td>-27.723207</td>\n",
       "      <td>Intersection - T-Junction</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Daylight</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Grade</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48418</th>\n",
       "      <td>Property damage only</td>\n",
       "      <td>March</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>15</td>\n",
       "      <td>Springwood</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>153.132383</td>\n",
       "      <td>-27.617409</td>\n",
       "      <td>Intersection - T-Junction</td>\n",
       "      <td>60 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Daylight</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Level</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103048</th>\n",
       "      <td>Minor injury</td>\n",
       "      <td>July</td>\n",
       "      <td>Monday</td>\n",
       "      <td>17</td>\n",
       "      <td>Dumbleton</td>\n",
       "      <td>Outer regional</td>\n",
       "      <td>149.082438</td>\n",
       "      <td>-21.133925</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>100 - 110 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Daylight</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Crest</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252956</th>\n",
       "      <td>Medical treatment</td>\n",
       "      <td>December</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>13</td>\n",
       "      <td>Southport</td>\n",
       "      <td>Major cities</td>\n",
       "      <td>153.414570</td>\n",
       "      <td>-27.969157</td>\n",
       "      <td>No Roadway Feature</td>\n",
       "      <td>0 - 50 km/h</td>\n",
       "      <td>...</td>\n",
       "      <td>Clear</td>\n",
       "      <td>Daylight</td>\n",
       "      <td>Straight</td>\n",
       "      <td>Level</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              Crash_Severity Crash_Month Crash_Day_Of_Week  Crash_Hour  \\\n",
       "290215       Hospitalisation    December           Tuesday          16   \n",
       "303412          Minor injury     January         Wednesday          15   \n",
       "1721    Property damage only    February            Friday           3   \n",
       "99609   Property damage only        June            Friday          10   \n",
       "37085   Property damage only   September            Sunday          18   \n",
       "268308       Hospitalisation       March            Monday           1   \n",
       "191711          Minor injury        June            Monday          15   \n",
       "164101          Minor injury       March            Monday           9   \n",
       "278634     Medical treatment     January          Thursday          14   \n",
       "293658     Medical treatment       April            Sunday          10   \n",
       "78199        Hospitalisation        July          Thursday          20   \n",
       "286845       Hospitalisation   September            Friday          21   \n",
       "58885   Property damage only      August          Thursday          20   \n",
       "284028     Medical treatment        June         Wednesday          19   \n",
       "67246   Property damage only     January          Saturday           7   \n",
       "25611           Minor injury       March         Wednesday          11   \n",
       "95515        Hospitalisation       April            Friday          15   \n",
       "48418   Property damage only       March          Thursday          15   \n",
       "103048          Minor injury        July            Monday          17   \n",
       "252956     Medical treatment    December          Thursday          13   \n",
       "\n",
       "               Loc_Suburb Loc_ABS_Remoteness  Crash_Longitude_GDA94  \\\n",
       "290215        Greenslopes       Major cities             153.044295   \n",
       "303412          Longreach        Very remote             144.259210   \n",
       "1721           Caboolture       Major cities             152.949010   \n",
       "99609           Rochedale       Major cities             153.107294   \n",
       "37085          Coominglah             Remote             150.848664   \n",
       "268308            Belmont       Major cities             153.122800   \n",
       "191711        Verrierdale     Inner regional             152.994855   \n",
       "164101        Cannon Hill       Major cities             153.095897   \n",
       "278634            Kuranda     Outer regional             145.662065   \n",
       "293658        Dicky Beach       Major cities             153.134640   \n",
       "78199            Thabeban     Inner regional             152.364047   \n",
       "286845        North Lakes       Major cities             153.011630   \n",
       "58885           Riverview       Major cities             152.854331   \n",
       "284028             Raglan     Outer regional             150.814744   \n",
       "67246          Rangeville     Inner regional             151.983166   \n",
       "25611              Tinana     Inner regional             152.674887   \n",
       "95515   Mount Warren Park       Major cities             153.194333   \n",
       "48418          Springwood       Major cities             153.132383   \n",
       "103048          Dumbleton     Outer regional             149.082438   \n",
       "252956          Southport       Major cities             153.414570   \n",
       "\n",
       "        Crash_Latitude_GDA94       Crash_Roadway_Feature Crash_Speed_Limit  \\\n",
       "290215            -27.498582          No Roadway Feature           60 km/h   \n",
       "303412            -23.437493        Intersection - Cross       0 - 50 km/h   \n",
       "1721              -27.084622          No Roadway Feature           60 km/h   \n",
       "99609             -27.572057  Intersection - Interchange    100 - 110 km/h   \n",
       "37085             -24.765539          No Roadway Feature    100 - 110 km/h   \n",
       "268308            -27.531760          No Roadway Feature    100 - 110 km/h   \n",
       "191711            -26.476293          No Roadway Feature      80 - 90 km/h   \n",
       "164101            -27.472590              Median Opening           60 km/h   \n",
       "278634            -16.831452          No Roadway Feature           60 km/h   \n",
       "293658            -26.782818   Intersection - T-Junction           60 km/h   \n",
       "78199             -24.899680          No Roadway Feature      80 - 90 km/h   \n",
       "286845            -27.236688          No Roadway Feature           60 km/h   \n",
       "58885             -27.598006          No Roadway Feature    100 - 110 km/h   \n",
       "284028            -23.713410          No Roadway Feature    100 - 110 km/h   \n",
       "67246             -27.591471   Intersection - T-Junction           60 km/h   \n",
       "25611             -25.560631        Intersection - Cross           60 km/h   \n",
       "95515             -27.723207   Intersection - T-Junction           60 km/h   \n",
       "48418             -27.617409   Intersection - T-Junction           60 km/h   \n",
       "103048            -21.133925          No Roadway Feature    100 - 110 km/h   \n",
       "252956            -27.969157          No Roadway Feature       0 - 50 km/h   \n",
       "\n",
       "               ...         Crash_Atmospheric_Condition  \\\n",
       "290215         ...                               Clear   \n",
       "303412         ...                               Clear   \n",
       "1721           ...                             Raining   \n",
       "99609          ...                               Clear   \n",
       "37085          ...                               Clear   \n",
       "268308         ...                               Clear   \n",
       "191711         ...                               Clear   \n",
       "164101         ...                               Clear   \n",
       "278634         ...                               Clear   \n",
       "293658         ...                               Clear   \n",
       "78199          ...                               Clear   \n",
       "286845         ...                             Raining   \n",
       "58885          ...                               Clear   \n",
       "284028         ...                             Raining   \n",
       "67246          ...                               Clear   \n",
       "25611          ...                               Clear   \n",
       "95515          ...                               Clear   \n",
       "48418          ...                               Clear   \n",
       "103048         ...                               Clear   \n",
       "252956         ...                               Clear   \n",
       "\n",
       "       Crash_Lighting_Condition Crash_Road_Horiz_Align Crash_Road_Vert_Align  \\\n",
       "290215                 Daylight     Curved - view open                 Level   \n",
       "303412                 Daylight     Curved - view open                 Level   \n",
       "1721     Darkness - Not lighted               Straight                 Level   \n",
       "99609                  Daylight               Straight                 Level   \n",
       "37085                 Dawn/Dusk               Straight                 Level   \n",
       "268308       Darkness - Lighted     Curved - view open                 Level   \n",
       "191711                 Daylight               Straight                 Crest   \n",
       "164101                 Daylight               Straight                 Level   \n",
       "278634                 Daylight     Curved - view open                 Grade   \n",
       "293658                 Daylight               Straight                 Level   \n",
       "78199    Darkness - Not lighted               Straight                 Grade   \n",
       "286845       Darkness - Lighted               Straight                 Grade   \n",
       "58885        Darkness - Lighted     Curved - view open                   Dip   \n",
       "284028   Darkness - Not lighted               Straight                 Level   \n",
       "67246                  Daylight               Straight                 Grade   \n",
       "25611                  Daylight               Straight                 Level   \n",
       "95515                  Daylight               Straight                 Grade   \n",
       "48418                  Daylight               Straight                 Level   \n",
       "103048                 Daylight               Straight                 Crest   \n",
       "252956                 Daylight               Straight                 Level   \n",
       "\n",
       "       Count_Casualty_Fatality  Count_Casualty_Hospitalised  \\\n",
       "290215                       0                            1   \n",
       "303412                       0                            0   \n",
       "1721                         0                            0   \n",
       "99609                        0                            0   \n",
       "37085                        0                            0   \n",
       "268308                       0                            1   \n",
       "191711                       0                            0   \n",
       "164101                       0                            0   \n",
       "278634                       0                            0   \n",
       "293658                       0                            0   \n",
       "78199                        0                            1   \n",
       "286845                       0                            1   \n",
       "58885                        0                            0   \n",
       "284028                       0                            0   \n",
       "67246                        0                            0   \n",
       "25611                        0                            0   \n",
       "95515                        0                            1   \n",
       "48418                        0                            0   \n",
       "103048                       0                            0   \n",
       "252956                       0                            0   \n",
       "\n",
       "        Count_Casualty_MedicallyTreated  Count_Casualty_MinorInjury  \\\n",
       "290215                                0                           0   \n",
       "303412                                0                           1   \n",
       "1721                                  0                           0   \n",
       "99609                                 0                           0   \n",
       "37085                                 0                           0   \n",
       "268308                                0                           0   \n",
       "191711                                0                           1   \n",
       "164101                                0                           2   \n",
       "278634                                1                           0   \n",
       "293658                                1                           0   \n",
       "78199                                 1                           0   \n",
       "286845                                1                           0   \n",
       "58885                                 0                           0   \n",
       "284028                                1                           0   \n",
       "67246                                 0                           0   \n",
       "25611                                 0                           2   \n",
       "95515                                 0                           0   \n",
       "48418                                 0                           0   \n",
       "103048                                0                           1   \n",
       "252956                                1                           0   \n",
       "\n",
       "        Count_Unit_Pedestrian  Count_Unit_Bicycle  \n",
       "290215                    1.0                 0.0  \n",
       "303412                    0.0                 0.0  \n",
       "1721                      0.0                 0.0  \n",
       "99609                     0.0                 0.0  \n",
       "37085                     0.0                 0.0  \n",
       "268308                    0.0                 0.0  \n",
       "191711                    0.0                 0.0  \n",
       "164101                    0.0                 0.0  \n",
       "278634                    0.0                 0.0  \n",
       "293658                    0.0                 0.0  \n",
       "78199                     0.0                 0.0  \n",
       "286845                    0.0                 0.0  \n",
       "58885                     0.0                 0.0  \n",
       "284028                    1.0                 0.0  \n",
       "67246                     0.0                 0.0  \n",
       "25611                     0.0                 0.0  \n",
       "95515                     0.0                 0.0  \n",
       "48418                     0.0                 0.0  \n",
       "103048                    0.0                 0.0  \n",
       "252956                    1.0                 0.0  \n",
       "\n",
       "[20 rows x 21 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What do we want to count?\n",
    "\n",
    "count_casualty_fatality + count_casualty_hospitalised + count_casualty_medcicallyTreated + count_casualty_minorInjury = count_unit_pedestrian + count_unit_bycicle + count_unit_bus + count_unit_other + count_unit_truck + count_unit_moped + count_unit_car ???? \n",
    "\n",
    "1 or 0  - Did the cyclist have an FSI? \n",
    "\n",
    "\n",
    "1 if cyclist was in fatality or hospitalised and no pedestrian was in fatality or hospitalised\n",
    "\n",
    "0 if more pedestrians were involved and no fsi remains for cyclist = (fatality + hospitalised) <= count_pedestrian\n",
    "OR\n",
    "Cyclist"
   ]
  },
  {
   "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
}