Projects

The data

France publishes every injury road accident each year on data.gouv.fr: around 50,000 accidents a year, split into four linked tables covering the circumstances, the location, the vehicles and the people involved. I used 2019 to 2023.

The pipeline

Five Docker containers run in order, so anyone with Docker and a Google Cloud key can reproduce the whole analysis with make setup, make build and make up. When a new year of data is published, the same commands pick it up.

1. infra Terraform creates the Cloud Storage bucket and BigQuery dataset as code, so the setup can be rebuilt or torn down in one command.
2. ingestion Python downloads each year’s files from data.gouv.fr using a small manifest, and only fetches what is new.
3. transformation dbt turns four raw tables into a star schema: staging models, one fact table with a row per person per accident, and marts for analysis, machine learning and bike accidents in Île-de-France.
4. eda Jupyter compares severity rates across 14 factors such as helmet use, lighting, road type, weather and age.
5. ml scikit-learn trains a severity classifier, tunes its threshold to catch at least 30% of severe accidents, and breaks down feature importance.

Real-world data is messy

Some of the problems I had to solve before any analysis could start:

• Two years of files were misspelled at the source (carcteristiques-2021.csv), so a strict filename pattern silently dropped them.
• A latitude column contained letters, which broke BigQuery's type detection. Everything now loads as text and is cast with custom dbt macros.
• Person IDs like "203 851 184" contain spaces and turned into nulls when cast to numbers.

What the data says

• Helmet use is the key safety factor: the severe-accident rate is 6.7% without a helmet and 4.2% with one.
• Teenagers wear helmets the least.
• Among micromobility vehicles, scooters lead to the most severe accidents.

From findings to policy

Laws alone don't do it: even in Finland, where helmets are mandatory, only 58% of cyclists wear one. Drawing on Nudge, I proposed light-touch interventions instead, such as making a helmet the default in bike subsidy programs and running peer-led campaigns for teenagers, modelled on Helsinki's “Pidä pääsi” (Keep Your Head Safe).