{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "Hbol",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "import polars as pl\n",
    "from enum import IntEnum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "MJUe",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.offline\n",
    "plotly.offline.init_notebook_mode()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "vblA",
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://www.data.gouv.fr/fr/datasets/r/8ef4c2a3-91a0-4d98-ae3a-989bde87b62a\n",
    "class Gravity(IntEnum):\n",
    "    UNINJURED=1\n",
    "    KILLED = 2\n",
    "    HOSPITALIZED = 3\n",
    "    LIGHTLY_INJURED = 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bkHC",
   "metadata": {},
   "outputs": [],
   "source": [
    "usagers_df = pl.read_parquet(\n",
    "    \"data/usagers_2023.parquet\"\n",
    ")\n",
    "usagers_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "lEQa",
   "metadata": {},
   "outputs": [],
   "source": [
    "caracs_df = pl.read_parquet(\"data/caracs_2023.parquet\")\n",
    "caracs_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "PKri",
   "metadata": {},
   "outputs": [],
   "source": [
    "recensement_df = pl.read_csv(\"data/recensement_2022.csv\", separator=\";\")\n",
    "recensement_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "Xref",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = usagers_df.select(\n",
    "    acc_id=\"Num_Acc\",\n",
    "    gravity=\"grav\",\n",
    ").filter(\n",
    "    pl.col(\"gravity\") == Gravity.KILLED.value\n",
    ").join(\n",
    "    caracs_df.select(\n",
    "        acc_id=\"Num_Acc\", \n",
    "        department=\"dep\"\n",
    "    ),\n",
    "    on=\"acc_id\"\n",
    ").select(\n",
    "    \"department\"\n",
    ").group_by(\n",
    "    \"department\"\n",
    ").agg(\n",
    "    accident_count=pl.len()\n",
    ").join(\n",
    "    recensement_df.select(\n",
    "        department=\"DEP\",\n",
    "        population=\"PTOT\"\n",
    "    ),\n",
    "    on=\"department\"\n",
    ").with_columns(\n",
    "    accident_rate=pl.col(\"accident_count\") / pl.col(\"population\")\n",
    ")\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "SFPL",
   "metadata": {},
   "outputs": [],
   "source": [
    "px.bar(\n",
    "    df.sort(\"accident_count\", descending=True),\n",
    "    x=\"department\",\n",
    "    y=\"accident_count\",\n",
    "    title=\"Number of fatal accidents per department\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "BYtC",
   "metadata": {},
   "outputs": [],
   "source": [
    "px.bar(\n",
    "    df.sort(\"accident_rate\", descending=True),\n",
    "    x=\"department\",\n",
    "    y=\"accident_rate\",\n",
    "    title=\"Fatal accidents per per capita per department\"\n",
    ")"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}