{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "17ca2acb",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": [],
    "ExecuteTime": {
     "end_time": "2023-11-16T16:20:49.426349Z",
     "start_time": "2023-11-16T16:20:16.117316Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data for year 2012:\n",
      "       MSID     MSName  ZSID                              ZSName       Achse  \\\n",
      "0  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "1  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "2  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "3  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "4  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "\n",
      "   HNr      Hoehe      EKoord     NKoord  Richtung  Knummer  \\\n",
      "0  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "1  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "2  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "3  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "4  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "\n",
      "                    Kname  AnzDetektoren  D1ID       D2ID       D3ID  \\\n",
      "0  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "1  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "2  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "3  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "4  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "\n",
      "        D4ID       MessungDatZeit   LieferDat  AnzFahrzeuge AnzFahrzeugeStatus  \n",
      "0  Unbekannt  2012-01-01T00:00:00  2021-02-03           NaN            Fehlend  \n",
      "1  Unbekannt  2012-01-01T01:00:00  2021-02-03         256.0           Gemessen  \n",
      "2  Unbekannt  2012-01-01T02:00:00  2021-02-03         186.0           Gemessen  \n",
      "3  Unbekannt  2012-01-01T03:00:00  2021-02-03         142.0           Gemessen  \n",
      "4  Unbekannt  2012-01-01T04:00:00  2021-02-03         116.0           Gemessen  \n",
      "Data for year 2013:\n",
      "       MSID     MSName  ZSID                              ZSName       Achse  \\\n",
      "0  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "1  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "2  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "3  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "4  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "\n",
      "   HNr      Hoehe      EKoord     NKoord  Richtung  Knummer  \\\n",
      "0  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "1  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "2  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "3  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "4  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "\n",
      "                    Kname  AnzDetektoren  D1ID       D2ID       D3ID  \\\n",
      "0  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "1  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "2  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "3  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "4  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "\n",
      "        D4ID       MessungDatZeit   LieferDat  AnzFahrzeuge AnzFahrzeugeStatus  \n",
      "0  Unbekannt  2013-01-01T00:00:00  2021-02-03         224.0           Gemessen  \n",
      "1  Unbekannt  2013-01-01T01:00:00  2021-02-03         547.0           Gemessen  \n",
      "2  Unbekannt  2013-01-01T02:00:00  2021-02-03         249.0           Gemessen  \n",
      "3  Unbekannt  2013-01-01T03:00:00  2021-02-03         123.0           Gemessen  \n",
      "4  Unbekannt  2013-01-01T04:00:00  2021-02-03          95.0           Gemessen  \n",
      "Data for year 2014:\n",
      "       MSID     MSName  ZSID                              ZSName       Achse  \\\n",
      "0  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "1  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "2  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "3  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "4  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "\n",
      "   HNr      Hoehe      EKoord     NKoord  Richtung  Knummer  \\\n",
      "0  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "1  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "2  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "3  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "4  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "\n",
      "                    Kname  AnzDetektoren  D1ID       D2ID       D3ID  \\\n",
      "0  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "1  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "2  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "3  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "4  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "\n",
      "        D4ID       MessungDatZeit   LieferDat  AnzFahrzeuge AnzFahrzeugeStatus  \n",
      "0  Unbekannt  2014-01-01T00:00:00  2021-02-03         229.0           Gemessen  \n",
      "1  Unbekannt  2014-01-01T01:00:00  2021-02-03         441.0           Gemessen  \n",
      "2  Unbekannt  2014-01-01T02:00:00  2021-02-03         195.0           Gemessen  \n",
      "3  Unbekannt  2014-01-01T03:00:00  2021-02-03         142.0           Gemessen  \n",
      "4  Unbekannt  2014-01-01T04:00:00  2021-02-03          98.0           Gemessen  \n",
      "Data for year 2015:\n",
      "       MSID     MSName  ZSID                              ZSName       Achse  \\\n",
      "0  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "1  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "2  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "3  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "4  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "\n",
      "   HNr      Hoehe      EKoord     NKoord  Richtung  Knummer  \\\n",
      "0  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "1  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "2  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "3  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "4  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "\n",
      "                    Kname  AnzDetektoren  D1ID       D2ID       D3ID  \\\n",
      "0  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "1  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "2  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "3  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "4  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "\n",
      "        D4ID       MessungDatZeit   LieferDat  AnzFahrzeuge AnzFahrzeugeStatus  \n",
      "0  Unbekannt  2015-01-01T00:00:00  2021-02-03         228.0           Gemessen  \n",
      "1  Unbekannt  2015-01-01T01:00:00  2021-02-03         338.0           Gemessen  \n",
      "2  Unbekannt  2015-01-01T02:00:00  2021-02-03         170.0           Gemessen  \n",
      "3  Unbekannt  2015-01-01T03:00:00  2021-02-03         120.0           Gemessen  \n",
      "4  Unbekannt  2015-01-01T04:00:00  2021-02-03          84.0           Gemessen  \n",
      "Data for year 2016:\n",
      "       MSID     MSName  ZSID                              ZSName       Achse  \\\n",
      "0  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "1  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "2  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "3  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "4  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "\n",
      "   HNr      Hoehe      EKoord     NKoord  Richtung  Knummer  \\\n",
      "0  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "1  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "2  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "3  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "4  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "\n",
      "                    Kname  AnzDetektoren  D1ID       D2ID       D3ID  \\\n",
      "0  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "1  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "2  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "3  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "4  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "\n",
      "        D4ID       MessungDatZeit   LieferDat  AnzFahrzeuge AnzFahrzeugeStatus  \n",
      "0  Unbekannt  2016-01-01T00:00:00  2021-02-03         236.0           Gemessen  \n",
      "1  Unbekannt  2016-01-01T01:00:00  2021-02-03         335.0           Gemessen  \n",
      "2  Unbekannt  2016-01-01T02:00:00  2021-02-03         213.0           Gemessen  \n",
      "3  Unbekannt  2016-01-01T03:00:00  2021-02-03         112.0           Gemessen  \n",
      "4  Unbekannt  2016-01-01T04:00:00  2021-02-03          80.0           Gemessen  \n",
      "File not found for year 2017: datasets/sid_dav_verkehrszaehlung_miv_OD2031_2017.csv\n",
      "File not found for year 2018: datasets/sid_dav_verkehrszaehlung_miv_OD2031_2018.csv\n",
      "File not found for year 2019: datasets/sid_dav_verkehrszaehlung_miv_OD2031_2019.csv\n",
      "File not found for year 2020: datasets/sid_dav_verkehrszaehlung_miv_OD2031_2020.csv\n",
      "Data for year 2021:\n",
      "       MSID     MSName  ZSID                              ZSName       Achse  \\\n",
      "0  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "1  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "2  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "3  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "4  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "\n",
      "   HNr      Hoehe      EKoord     NKoord  Richtung  Knummer  \\\n",
      "0  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "1  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "2  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "3  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "4  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "\n",
      "                    Kname  AnzDetektoren  D1ID       D2ID       D3ID  \\\n",
      "0  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "1  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "2  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "3  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "4  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "\n",
      "        D4ID       MessungDatZeit   LieferDat  AnzFahrzeuge AnzFahrzeugeStatus  \n",
      "0  Unbekannt  2021-01-01T00:00:00  2021-12-30         122.0           Gemessen  \n",
      "1  Unbekannt  2021-01-01T01:00:00  2021-12-30         177.0           Gemessen  \n",
      "2  Unbekannt  2021-01-01T02:00:00  2021-12-30         125.0           Gemessen  \n",
      "3  Unbekannt  2021-01-01T03:00:00  2021-12-30          84.0           Gemessen  \n",
      "4  Unbekannt  2021-01-01T04:00:00  2021-12-30          49.0           Gemessen  \n",
      "Data for year 2022:\n",
      "       MSID     MSName  ZSID                              ZSName       Achse  \\\n",
      "0  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "1  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "2  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "3  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "4  Z001M001  Unbekannt  Z001  Seestrasse (Strandbad Wollishofen)  Seestrasse   \n",
      "\n",
      "   HNr      Hoehe      EKoord     NKoord  Richtung  Knummer  \\\n",
      "0  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "1  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "2  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "3  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "4  451  Unbekannt  2683009.89  1243936.2  auswärts      789   \n",
      "\n",
      "                    Kname  AnzDetektoren  D1ID       D2ID       D3ID  \\\n",
      "0  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "1  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "2  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "3  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "4  Badanstalt Wollishofen              1     2  Unbekannt  Unbekannt   \n",
      "\n",
      "        D4ID       MessungDatZeit   LieferDat  AnzFahrzeuge AnzFahrzeugeStatus  \n",
      "0  Unbekannt  2022-01-01T00:00:00  2022-12-30         166.0           Gemessen  \n",
      "1  Unbekannt  2022-01-01T01:00:00  2022-12-30         255.0           Gemessen  \n",
      "2  Unbekannt  2022-01-01T02:00:00  2022-12-30         168.0           Gemessen  \n",
      "3  Unbekannt  2022-01-01T03:00:00  2022-12-30          96.0           Gemessen  \n",
      "4  Unbekannt  2022-01-01T04:00:00  2022-12-30          63.0           Gemessen  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "from io import StringIO\n",
    "import csv\n",
    "import os\n",
    "\n",
    "tables_dict = {}\n",
    "pd.set_option(\"display.max_columns\", None)\n",
    "data_dir = 'datasets/'\n",
    "\n",
    "for year in range(2012, 2023):\n",
    "    file_name = f'sid_dav_verkehrszaehlung_miv_OD2031_{year}.csv'\n",
    "    file_path = os.path.join(data_dir, file_name)\n",
    "\n",
    "    if os.path.exists(file_path):\n",
    "        df = pd.read_csv(file_path, low_memory=False)\n",
    "\n",
    "        table_key = f\"miv_{year}\"\n",
    "        tables_dict[table_key] = df\n",
    "\n",
    "        print(f\"Data for year {year}:\")\n",
    "        print(df.head())\n",
    "    else:\n",
    "        print(f\"File not found for year {year}: {file_path}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "8c86b235",
   "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>AccidentUID</th>\n",
       "      <th>AccidentType</th>\n",
       "      <th>AccidentType_de</th>\n",
       "      <th>AccidentType_fr</th>\n",
       "      <th>AccidentType_it</th>\n",
       "      <th>AccidentType_en</th>\n",
       "      <th>AccidentSeverityCategory</th>\n",
       "      <th>AccidentSeverityCategory_de</th>\n",
       "      <th>AccidentSeverityCategory_fr</th>\n",
       "      <th>AccidentSeverityCategory_it</th>\n",
       "      <th>AccidentSeverityCategory_en</th>\n",
       "      <th>AccidentInvolvingPedestrian</th>\n",
       "      <th>AccidentInvolvingBicycle</th>\n",
       "      <th>AccidentInvolvingMotorcycle</th>\n",
       "      <th>RoadType</th>\n",
       "      <th>RoadType_de</th>\n",
       "      <th>RoadType_fr</th>\n",
       "      <th>RoadType_it</th>\n",
       "      <th>RoadType_en</th>\n",
       "      <th>AccidentLocation_CHLV95_E</th>\n",
       "      <th>AccidentLocation_CHLV95_N</th>\n",
       "      <th>CantonCode</th>\n",
       "      <th>MunicipalityCode</th>\n",
       "      <th>AccidentYear</th>\n",
       "      <th>AccidentMonth</th>\n",
       "      <th>AccidentMonth_de</th>\n",
       "      <th>AccidentMonth_fr</th>\n",
       "      <th>AccidentMonth_it</th>\n",
       "      <th>AccidentMonth_en</th>\n",
       "      <th>AccidentWeekDay</th>\n",
       "      <th>AccidentWeekDay_de</th>\n",
       "      <th>AccidentWeekDay_fr</th>\n",
       "      <th>AccidentWeekDay_it</th>\n",
       "      <th>AccidentWeekDay_en</th>\n",
       "      <th>AccidentHour</th>\n",
       "      <th>AccidentHour_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A2D2677533867004E0430A865E337004</td>\n",
       "      <td>at0</td>\n",
       "      <td>Schleuder- oder Selbstunfall</td>\n",
       "      <td>dérapage ou perte de maîtrise</td>\n",
       "      <td>Incidente di sbandamento o per colpa propria</td>\n",
       "      <td>Accident with skidding or self-accident</td>\n",
       "      <td>as4</td>\n",
       "      <td>Unfall mit Sachschaden</td>\n",
       "      <td>accident avec dommages matériels</td>\n",
       "      <td>Incidente con danni materiali</td>\n",
       "      <td>Accident with property damage</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>rt433</td>\n",
       "      <td>Nebenstrasse</td>\n",
       "      <td>route secondaire</td>\n",
       "      <td>Strada secondaria</td>\n",
       "      <td>Minor road</td>\n",
       "      <td>2684605</td>\n",
       "      <td>1245194</td>\n",
       "      <td>ZH</td>\n",
       "      <td>261</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>Januar</td>\n",
       "      <td>janvier</td>\n",
       "      <td>Gennaio</td>\n",
       "      <td>January</td>\n",
       "      <td>aw406</td>\n",
       "      <td>Samstag</td>\n",
       "      <td>samedi</td>\n",
       "      <td>Sabato</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>0</td>\n",
       "      <td>00h-01h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9FD6441F802C20A6E0430A865E3320A6</td>\n",
       "      <td>at0</td>\n",
       "      <td>Schleuder- oder Selbstunfall</td>\n",
       "      <td>dérapage ou perte de maîtrise</td>\n",
       "      <td>Incidente di sbandamento o per colpa propria</td>\n",
       "      <td>Accident with skidding or self-accident</td>\n",
       "      <td>as3</td>\n",
       "      <td>Unfall mit Leichtverletzten</td>\n",
       "      <td>accident avec blessés légers</td>\n",
       "      <td>Incidente con feriti leggeri</td>\n",
       "      <td>Accident with light injuries</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>rt433</td>\n",
       "      <td>Nebenstrasse</td>\n",
       "      <td>route secondaire</td>\n",
       "      <td>Strada secondaria</td>\n",
       "      <td>Minor road</td>\n",
       "      <td>2682382</td>\n",
       "      <td>1246980</td>\n",
       "      <td>ZH</td>\n",
       "      <td>261</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>Januar</td>\n",
       "      <td>janvier</td>\n",
       "      <td>Gennaio</td>\n",
       "      <td>January</td>\n",
       "      <td>aw406</td>\n",
       "      <td>Samstag</td>\n",
       "      <td>samedi</td>\n",
       "      <td>Sabato</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>1</td>\n",
       "      <td>01h-02h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9FDA0DC4856A6094E0430A865E336094</td>\n",
       "      <td>at0</td>\n",
       "      <td>Schleuder- oder Selbstunfall</td>\n",
       "      <td>dérapage ou perte de maîtrise</td>\n",
       "      <td>Incidente di sbandamento o per colpa propria</td>\n",
       "      <td>Accident with skidding or self-accident</td>\n",
       "      <td>as4</td>\n",
       "      <td>Unfall mit Sachschaden</td>\n",
       "      <td>accident avec dommages matériels</td>\n",
       "      <td>Incidente con danni materiali</td>\n",
       "      <td>Accident with property damage</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>rt439</td>\n",
       "      <td>andere</td>\n",
       "      <td>autre</td>\n",
       "      <td>Altro</td>\n",
       "      <td>Other</td>\n",
       "      <td>2682791</td>\n",
       "      <td>1247749</td>\n",
       "      <td>ZH</td>\n",
       "      <td>261</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>Januar</td>\n",
       "      <td>janvier</td>\n",
       "      <td>Gennaio</td>\n",
       "      <td>January</td>\n",
       "      <td>aw406</td>\n",
       "      <td>Samstag</td>\n",
       "      <td>samedi</td>\n",
       "      <td>Sabato</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2</td>\n",
       "      <td>02h-03h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3B66E42396E6000E0430A865E336000</td>\n",
       "      <td>at5</td>\n",
       "      <td>Überqueren der Fahrbahn</td>\n",
       "      <td>accident en traversant une route</td>\n",
       "      <td>Incidente nell'attraversare la carreggiata</td>\n",
       "      <td>Accident when crossing the lane(s)</td>\n",
       "      <td>as3</td>\n",
       "      <td>Unfall mit Leichtverletzten</td>\n",
       "      <td>accident avec blessés légers</td>\n",
       "      <td>Incidente con feriti leggeri</td>\n",
       "      <td>Accident with light injuries</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>rt433</td>\n",
       "      <td>Nebenstrasse</td>\n",
       "      <td>route secondaire</td>\n",
       "      <td>Strada secondaria</td>\n",
       "      <td>Minor road</td>\n",
       "      <td>2681199</td>\n",
       "      <td>1247102</td>\n",
       "      <td>ZH</td>\n",
       "      <td>261</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>Januar</td>\n",
       "      <td>janvier</td>\n",
       "      <td>Gennaio</td>\n",
       "      <td>January</td>\n",
       "      <td>aw406</td>\n",
       "      <td>Samstag</td>\n",
       "      <td>samedi</td>\n",
       "      <td>Sabato</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2</td>\n",
       "      <td>02h-03h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9FDA0DBE8CCE9096E0430A865E339096</td>\n",
       "      <td>at0</td>\n",
       "      <td>Schleuder- oder Selbstunfall</td>\n",
       "      <td>dérapage ou perte de maîtrise</td>\n",
       "      <td>Incidente di sbandamento o per colpa propria</td>\n",
       "      <td>Accident with skidding or self-accident</td>\n",
       "      <td>as4</td>\n",
       "      <td>Unfall mit Sachschaden</td>\n",
       "      <td>accident avec dommages matériels</td>\n",
       "      <td>Incidente con danni materiali</td>\n",
       "      <td>Accident with property damage</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>rt433</td>\n",
       "      <td>Nebenstrasse</td>\n",
       "      <td>route secondaire</td>\n",
       "      <td>Strada secondaria</td>\n",
       "      <td>Minor road</td>\n",
       "      <td>2682479</td>\n",
       "      <td>1250690</td>\n",
       "      <td>ZH</td>\n",
       "      <td>261</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>Januar</td>\n",
       "      <td>janvier</td>\n",
       "      <td>Gennaio</td>\n",
       "      <td>January</td>\n",
       "      <td>aw406</td>\n",
       "      <td>Samstag</td>\n",
       "      <td>samedi</td>\n",
       "      <td>Sabato</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>3</td>\n",
       "      <td>03h-04h</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        AccidentUID AccidentType  \\\n",
       "0  A2D2677533867004E0430A865E337004          at0   \n",
       "1  9FD6441F802C20A6E0430A865E3320A6          at0   \n",
       "2  9FDA0DC4856A6094E0430A865E336094          at0   \n",
       "3  A3B66E42396E6000E0430A865E336000          at5   \n",
       "4  9FDA0DBE8CCE9096E0430A865E339096          at0   \n",
       "\n",
       "                AccidentType_de                   AccidentType_fr  \\\n",
       "0  Schleuder- oder Selbstunfall     dérapage ou perte de maîtrise   \n",
       "1  Schleuder- oder Selbstunfall     dérapage ou perte de maîtrise   \n",
       "2  Schleuder- oder Selbstunfall     dérapage ou perte de maîtrise   \n",
       "3       Überqueren der Fahrbahn  accident en traversant une route   \n",
       "4  Schleuder- oder Selbstunfall     dérapage ou perte de maîtrise   \n",
       "\n",
       "                                AccidentType_it  \\\n",
       "0  Incidente di sbandamento o per colpa propria   \n",
       "1  Incidente di sbandamento o per colpa propria   \n",
       "2  Incidente di sbandamento o per colpa propria   \n",
       "3    Incidente nell'attraversare la carreggiata   \n",
       "4  Incidente di sbandamento o per colpa propria   \n",
       "\n",
       "                           AccidentType_en AccidentSeverityCategory  \\\n",
       "0  Accident with skidding or self-accident                      as4   \n",
       "1  Accident with skidding or self-accident                      as3   \n",
       "2  Accident with skidding or self-accident                      as4   \n",
       "3       Accident when crossing the lane(s)                      as3   \n",
       "4  Accident with skidding or self-accident                      as4   \n",
       "\n",
       "   AccidentSeverityCategory_de       AccidentSeverityCategory_fr  \\\n",
       "0       Unfall mit Sachschaden  accident avec dommages matériels   \n",
       "1  Unfall mit Leichtverletzten      accident avec blessés légers   \n",
       "2       Unfall mit Sachschaden  accident avec dommages matériels   \n",
       "3  Unfall mit Leichtverletzten      accident avec blessés légers   \n",
       "4       Unfall mit Sachschaden  accident avec dommages matériels   \n",
       "\n",
       "     AccidentSeverityCategory_it    AccidentSeverityCategory_en  \\\n",
       "0  Incidente con danni materiali  Accident with property damage   \n",
       "1   Incidente con feriti leggeri   Accident with light injuries   \n",
       "2  Incidente con danni materiali  Accident with property damage   \n",
       "3   Incidente con feriti leggeri   Accident with light injuries   \n",
       "4  Incidente con danni materiali  Accident with property damage   \n",
       "\n",
       "   AccidentInvolvingPedestrian  AccidentInvolvingBicycle  \\\n",
       "0                        False                     False   \n",
       "1                        False                      True   \n",
       "2                        False                     False   \n",
       "3                        False                     False   \n",
       "4                        False                     False   \n",
       "\n",
       "   AccidentInvolvingMotorcycle RoadType   RoadType_de       RoadType_fr  \\\n",
       "0                        False    rt433  Nebenstrasse  route secondaire   \n",
       "1                        False    rt433  Nebenstrasse  route secondaire   \n",
       "2                        False    rt439        andere             autre   \n",
       "3                        False    rt433  Nebenstrasse  route secondaire   \n",
       "4                        False    rt433  Nebenstrasse  route secondaire   \n",
       "\n",
       "         RoadType_it RoadType_en  AccidentLocation_CHLV95_E  \\\n",
       "0  Strada secondaria  Minor road                    2684605   \n",
       "1  Strada secondaria  Minor road                    2682382   \n",
       "2              Altro       Other                    2682791   \n",
       "3  Strada secondaria  Minor road                    2681199   \n",
       "4  Strada secondaria  Minor road                    2682479   \n",
       "\n",
       "   AccidentLocation_CHLV95_N CantonCode  MunicipalityCode  AccidentYear  \\\n",
       "0                    1245194         ZH               261          2011   \n",
       "1                    1246980         ZH               261          2011   \n",
       "2                    1247749         ZH               261          2011   \n",
       "3                    1247102         ZH               261          2011   \n",
       "4                    1250690         ZH               261          2011   \n",
       "\n",
       "   AccidentMonth AccidentMonth_de AccidentMonth_fr AccidentMonth_it  \\\n",
       "0              1           Januar          janvier          Gennaio   \n",
       "1              1           Januar          janvier          Gennaio   \n",
       "2              1           Januar          janvier          Gennaio   \n",
       "3              1           Januar          janvier          Gennaio   \n",
       "4              1           Januar          janvier          Gennaio   \n",
       "\n",
       "  AccidentMonth_en AccidentWeekDay AccidentWeekDay_de AccidentWeekDay_fr  \\\n",
       "0          January           aw406            Samstag             samedi   \n",
       "1          January           aw406            Samstag             samedi   \n",
       "2          January           aw406            Samstag             samedi   \n",
       "3          January           aw406            Samstag             samedi   \n",
       "4          January           aw406            Samstag             samedi   \n",
       "\n",
       "  AccidentWeekDay_it AccidentWeekDay_en  AccidentHour AccidentHour_text  \n",
       "0             Sabato           Saturday             0           00h-01h  \n",
       "1             Sabato           Saturday             1           01h-02h  \n",
       "2             Sabato           Saturday             2           02h-03h  \n",
       "3             Sabato           Saturday             2           02h-03h  \n",
       "4             Sabato           Saturday             3           03h-04h  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "application/javascript": [
       "\n",
       "            setTimeout(function() {\n",
       "                var nbb_cell_id = 17;\n",
       "                var nbb_unformatted_code = \"\\n\\naloc_file_path = \\\"/home/slnopriv/projects/db23-data-exploration/data/RoadTrafficAccidentLocations.csv\\\"\\nalocs = pd.read_csv(file_path, low_memory=False)\\n\\nalocs.head()\";\n",
       "                var nbb_formatted_code = \"aloc_file_path = \\\"/home/slnopriv/projects/db23-data-exploration/data/RoadTrafficAccidentLocations.csv\\\"\\nalocs = pd.read_csv(file_path, low_memory=False)\\n\\nalocs.head()\";\n",
       "                var nbb_cells = Jupyter.notebook.get_cells();\n",
       "                for (var i = 0; i < nbb_cells.length; ++i) {\n",
       "                    if (nbb_cells[i].input_prompt_number == nbb_cell_id) {\n",
       "                        if (nbb_cells[i].get_text() == nbb_unformatted_code) {\n",
       "                             nbb_cells[i].set_text(nbb_formatted_code);\n",
       "                        }\n",
       "                        break;\n",
       "                    }\n",
       "                }\n",
       "            }, 500);\n",
       "            "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "aloc_file_path = \"/home/slnopriv/projects/db23-data-exploration/data/RoadTrafficAccidentLocations.csv\"\n",
    "alocs = pd.read_csv(file_path, low_memory=False)\n",
    "\n",
    "alocs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f6941bd7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "            setTimeout(function() {\n",
       "                var nbb_cell_id = 11;\n",
       "                var nbb_unformatted_code = \"%load_ext nb_black\";\n",
       "                var nbb_formatted_code = \"%load_ext nb_black\";\n",
       "                var nbb_cells = Jupyter.notebook.get_cells();\n",
       "                for (var i = 0; i < nbb_cells.length; ++i) {\n",
       "                    if (nbb_cells[i].input_prompt_number == nbb_cell_id) {\n",
       "                        if (nbb_cells[i].get_text() == nbb_unformatted_code) {\n",
       "                             nbb_cells[i].set_text(nbb_formatted_code);\n",
       "                        }\n",
       "                        break;\n",
       "                    }\n",
       "                }\n",
       "            }, 500);\n",
       "            "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%load_ext nb_black"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "808aa85f-fc18-4fca-8268-40f2ecc777e8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CSV file downloaded successfully.\n"
     ]
    }
   ],
   "source": [
    "# Specify the URL of the CSV file\n",
    "csv_file_url = 'https://data.stadt-zuerich.ch/dataset/sid_dav_verkehrszaehlung_miv_od2031/download/sid_dav_verkehrszaehlung_miv_OD2031_2020.csv'\n",
    "csv_rtal = 'https://data.stadt-zuerich.ch/dataset/sid_dav_strassenverkehrsunfallorte/download/RoadTrafficAccidentLocations.csv'\n",
    "# Send an HTTP GET request\n",
    "response = requests.get(csv_rtal)\n",
    "\n",
    "# Check the response status\n",
    "if response.status_code == 200:\n",
    "    # Save the CSV content to a local file\n",
    "    with open('local_file2.csv', 'wb') as local_file:\n",
    "        local_file.write(response.content)\n",
    "    print(\"CSV file downloaded successfully.\")\n",
    "else:\n",
    "    print(f\"Failed to download data. Status code: {response.status_code}\")\n",
    "\n",
    "accidents_file_path = os.path.join(data_dir)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
