dataset_curation/rome.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Rome, Italy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Source of original dataset:** https://dati.comune.roma.it/catalog/it/dataset?q=incidenti\\&sort=score+desc\\%2C+dataset_last_update+desc\n",
    "\n",
    "**Location of accidents:** Latitude, Longitude\n",
    "\n",
    "**Date of accidents:** Date\n",
    "\n",
    "**Outcome of accidents:** Fatality, 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",
    "import glob"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Setup input files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dir = \"../data/rome/\"\n",
    "\n",
    "files = glob.glob(data_dir+'*.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read original data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = []\n",
    "for x in files:\n",
    "    aux = pd.read_csv(x, sep=';', encoding = \"ISO-8859-1\")\n",
    "    data.append(aux)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in range(len(data)):\n",
    "    try:\n",
    "        data[x] = data[x].drop(['Unnamed: 37'], axis=1, errors='ignore')\n",
    "    except e:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Setup Longitude & Latitude columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(data)):\n",
    "    name1 = ['Latitudine',\n",
    "             'Longitudine',\n",
    "            ]\n",
    "    name2 = ['Latitude',\n",
    "             'Longitude',\n",
    "             ]\n",
    "    for key1, key2 in zip(name1, name2):\n",
    "        try:\n",
    "            data_aux[i] = data_aux[i].rename(columns={key1: key2})\n",
    "        except:\n",
    "            pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Join the different files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_of_dfs = data\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": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create Datetime column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Date'] = pd.to_datetime(data['DataOraIncidente'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Setup bicycles filter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_bicycles = data[(data['TipoVeicolo'] == 'Velocipede')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Setup Longitude & Latitude columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_bicycles['Longitude'] = data_bicycles['Longitude'].str.replace(',','.').astype(float)\n",
    "data_bicycles['Latitude'] = data_bicycles['Latitude'].str.replace(',','.').astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some key statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Accidents between '+str(data['Date'].min())+' and '+str(data['Date'].max()))\n",
    "\n",
    "total_accidents = data.shape[0]\n",
    "print(\"There are a total of \"+str(total_accidents)+\" accidents.\")\n",
    "\n",
    "fatalities = data_bicycles[\"NUM_MORTI\"].sum()\n",
    "print(\"There are a total of bicyle \"+str(fatalities)+\" fatalities.\")\n",
    "\n",
    "injuries = data_bicycles[\"NUM_FERITI\"].astype(float).sum()\n",
    "print(\"There are a total of bicyle \"+str(injuries)+\" major injured.\")\n",
    "\n",
    "bicycles = data_bicycles.shape[0]\n",
    "print(\"There are a total of \"+str(bicycles)+\" bicycles involved in all the accidents.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Slice all bicycle accidents"
   ]
  },
  {
   "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_rome.csv')\n",
    "print('Wrote file to: cycling_safety_rome.csv')"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}