dataset_curation/netherlands.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Netherlands"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Source of original dataset:** https://data.amsterdam.nl/datasets/G6xF0loDvppzog/verkeersongevallen-bron-landelijk/\n",
    "\n",
    "**Location of accidents:** Latitude, Longitude\n",
    "\n",
    "**Date of accidents:** Date\n",
    "\n",
    "**Outcome of accidents:** Fatality, Serious Injury, Injury, PDO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "pd.set_option('max_columns', None)\n",
    "pd.set_option('display.max_colwidth', -1)\n",
    "import numpy as np\n",
    "from plotly import graph_objects as go\n",
    "import plotly.express as px\n",
    "from itertools import chain\n",
    "import matplotlib.pyplot as plt\n",
    "from pyproj import Proj, transform"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Setup input files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dir = \"../data/netherlands/\"\n",
    "\n",
    "data_input = {\n",
    "    '2003':{\n",
    "        'locations': data_dir+'01-01-2003_31-12-2003/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2003_31-12-2003/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2003_31-12-2003/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2004':{\n",
    "        'locations': data_dir+'01-01-2004_31-12-2004/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2004_31-12-2004/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2004_31-12-2004/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2005':{\n",
    "        'locations': data_dir+'01-01-2005_31-12-2005/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2005_31-12-2005/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2005_31-12-2005/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2006':{\n",
    "        'locations': data_dir+'01-01-2006_31-12-2006/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2006_31-12-2006/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2006_31-12-2006/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2007':{\n",
    "        'locations': data_dir+'01-01-2007_31-12-2007/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2007_31-12-2007/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2007_31-12-2007/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2008':{\n",
    "        'locations': data_dir+'01-01-2008_31-12-2008/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2008_31-12-2008/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2008_31-12-2008/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2009':{\n",
    "        'locations': data_dir+'01-01-2009_31-12-2009/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2009_31-12-2009/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2009_31-12-2009/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2010':{\n",
    "        'locations': data_dir+'01-01-2010_31-12-2010/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2010_31-12-2010/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2010_31-12-2010/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2011':{\n",
    "        'locations': data_dir+'01-01-2011_31-12-2011/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2011_31-12-2011/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2011_31-12-2011/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2012':{\n",
    "        'locations': data_dir+'01-01-2012_31-12-2012/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2012_31-12-2012/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2012_31-12-2012/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2013':{\n",
    "        'locations': data_dir+'01-01-2013_31-12-2013/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2013_31-12-2013/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2013_31-12-2013/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2014':{\n",
    "        'locations': data_dir+'01-01-2014_31-12-2014/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2014_31-12-2014/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2014_31-12-2014/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2015':{\n",
    "        'locations': data_dir+'01-01-2015_31-12-2015/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2015_31-12-2015/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2015_31-12-2015/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2016':{\n",
    "        'locations': data_dir+'01-01-2016_31-12-2016/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2016_31-12-2016/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2016_31-12-2016/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2017':{\n",
    "        'locations': data_dir+'01-01-2017_31-12-2017/02 TOTNL J-N-J-N/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2017_31-12-2017/02 TOTNL J-N-J-N/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2017_31-12-2017/02 TOTNL J-N-J-N/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "    '2018':{\n",
    "        'locations': data_dir+'01-01-2018_31-12-2018/Netwerkgegevens/puntlocaties.txt',\n",
    "        'accidents': data_dir+'01-01-2018_31-12-2018/Ongevallengegevens/ongevallen.txt',\n",
    "        'parties'  : data_dir+'01-01-2018_31-12-2018/Ongevallengegevens/partijen.txt'\n",
    "    },\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def location_match(FK_VELD5):\n",
    "    return data_locations.loc[FK_VELD5]['X_COORD'].copy(), data_locations.loc[FK_VELD5]['Y_COORD'].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "data_aux = []\n",
    "\n",
    "for year, files in data_input.items():\n",
    "    print(year)\n",
    "    \n",
    "    # Read files\n",
    "    data_locations = pd.read_csv(files['locations'])\n",
    "    data_accidents = pd.read_csv(files['accidents'])\n",
    "    data_parties   = pd.read_csv(files['parties'])\n",
    "    \n",
    "    \n",
    "    # Condense parties file\n",
    "    data_parties['OTE_ID'] = data_parties['OTE_ID'].fillna(0).astype('int')\n",
    "    parties_only_vehicles = data_parties[['VKL_NUMMER','NUMMER','OTE_ID']].copy()\n",
    "    \n",
    "    parties_only_vehicles = parties_only_vehicles.groupby(['VKL_NUMMER']).OTE_ID.apply(list)\n",
    "    parties_only_vehicles = parties_only_vehicles.to_frame()\n",
    "    \n",
    "    parties_only_vehicles['bicycles'] = list(map(lambda j: 64 in j, parties_only_vehicles.OTE_ID))\n",
    "    parties_only_vehicles['pedestrians'] = list(map(lambda j: 71 in j, parties_only_vehicles.OTE_ID))\n",
    "    parties_only_vehicles.reset_index(level=0, inplace=True)\n",
    "    \n",
    "    # Merge accidents and involved parties files\n",
    "    data = pd.merge(data_accidents, parties_only_vehicles, on=\"VKL_NUMMER\", how=\"left\")\n",
    "    data = data.sort_values(by ='VKL_NUMMER')\n",
    "    \n",
    "    # Filter bicycles\n",
    "    data = data[data['bicycles'] == True]\n",
    "    \n",
    "    # Add locations to accidents\n",
    "    data_locations = data_locations.set_index('FK_VELD5')\n",
    "    data[['X_COORD', 'Y_COORD']] = data.apply(lambda x: location_match(x['FK_VELD5']), axis=1, result_type='expand')\n",
    "    \n",
    "    data_aux.append(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_of_dfs = data_aux\n",
    "list_of_dicts = [cur_df.T.to_dict().values() for cur_df in list_of_dfs]\n",
    "data = pd.DataFrame(list(chain(*list_of_dicts)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Transform locations to latitude & longitude\n",
    " (using the projection referenced here: https://gis.stackexchange.com/questions/122117/understanding-coordinates-in-netherlands-dataset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inProj = Proj('epsg:28992')\n",
    "outProj = Proj('epsg:4326')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def project_xy(X_COORD, Y_COORD):\n",
    "    return transform(inProj, outProj, X_COORD, Y_COORD)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[['Latitude', 'Longitude']] = data.apply(lambda x: project_xy(x['X_COORD'], x['Y_COORD']), axis=1, result_type='expand')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Setup outcomes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Fatalities'] = 0\n",
    "data.loc[(data['AP3_CODE'] == 'DOD') | (data['AP4_CODE'] == 'DOD') | (data['AP5_CODE'] == 'DOD'), 'Fatalities'] = 1\n",
    "\n",
    "data['Injuries'] = 0\n",
    "data.loc[(data['AP3_CODE'] == 'LET') | (data['AP4_CODE'] == 'LLI') | (data['AP4_CODE'] == 'LZW') | (data['AP5_CODE'] == 'LEH') | (data['AP5_CODE'] == 'LOV') | (data['AP5_CODE'] == 'LZH'), 'Injuries'] = 1\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Setup Datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Date'] = pd.to_datetime(data['JAAR_VKL'].astype(int), format='%Y')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some key statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_accidents = data.shape[0]\n",
    "print(\"There are a total of \"+str(total_accidents)+\" bicycles accidents.\")\n",
    "\n",
    "fatalities = data[\"Fatalities\"].sum()\n",
    "print(\"There are a total of \"+str(fatalities)+\" fatalities.\")\n",
    "\n",
    "injuries = data[\"Injuries\"].sum()\n",
    "print(\"There are a total of \"+str(injuries)+\" seriously injured.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Slice all bicycle accidents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_bicycles = data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_bicycles.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(data_bicycles.shape)\n",
    "data_bicycles.to_csv('cycling_safety_netherlands.csv')\n",
    "print('Wrote file to: cycling_safety_netherlands.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}