> ## Content Index
> Fetch the complete content index at: https://www.bambooweekly.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Bamboo Weekly #29: Auto accidents
- URL: https://www.bambooweekly.com/bw-29-auto-accidents/
- Published: 2023-08-23T15:00:23.000Z
- Updated: 2026-08-06T10:45:50.000Z
- Description: Get practice with CSV, dates and times, grouping, window functions, pivot tables, and plotting.
- Author: Reuven M. Lerner
- Tags: csv, datetime, grouping, window-functions, pivot-table, plotting

This week, I heard so much about automated vehicles, the problems they’re having, and the promise that they’ll be safer than regular human-driven cars, that I got to thinking about regular cars, and how safe (or not) they are.

I decided to look through data from the OECD (Organization for Economic Co-operation and Development), summarizing road-accident data from their 38 member countries. I was particularly interested in seeing if countries had reduced their accident rate over the years, and if all countries have done similarly well (or poorly) at reducing accidents.

I’ve traveled quite a bit, and while I’m certainly able to drive, I generally try to avoid it if I can. I’ve driven in the US (where I grew up), in Israel (where I live), and also in Portugal (on vacation several years ago), and was curious to know how these three countries fare in terms of accident rates. (I often say that I’m glad that I learned to drive in Boston before coming to Israel, since both places have aggressive drivers, and lots of poorly labeled, narrow roads.)

![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/image/fetch/f_auto,q_auto:good,fl_progressive:steep/https-3a-2f-2fsubstack-post-media.s3.amazonaws.com-2fpublic-2fimages-2f49d7affe-f301-4260-8687-abcaf21c0e53_512x512.jpg)

From Stable Diffusion: “A whimsical auto accident, where the cars are driven by robots, in the style of Dali”

### Data … and nine questions

This week, I asked you to look at two different data sources. The main one is the OECD’s report on road accidents, a CSV file you can download from:

[https://stats.oecd.org/sdmx-json/data/DP\_LIVE/.ROADACCID.../OECD?contentType=csv&detail=code&separator=comma&csv-lang=en](https://stats.oecd.org/sdmx-json/data/DP%5FLIVE/.ROADACCID.../OECD?contentType=csv&detail=code&separator=comma&csv-lang=en&ref=bambooweekly.com)

I also We'll also make use of the Wikipedia page that translates ISO 3-letter country codes into country names:

[https://en.wikipedia.org/wiki/List\_of\_ISO\_3166\_country\_codes](https://en.wikipedia.org/wiki/List%5Fof%5FISO%5F3166%5Fcountry%5Fcodes?ref=bambooweekly.com)

Here are my nine questions and tasks for this week. Learning goals include using selecting rows and columns with .loc, calculating percentage differences, plotting with selected parts of the data, creating data frames from HTML pages, and joining:

- Load the data from OECD into a data frame. We won't look at the columns named "INDICATOR", "FREQUENCY", or "Flag Codes."
- What is the most recent year for which we have data? Are there any countries for which the latest data isn't from that year?
- Which five countries have the greatest number of road deaths, on average, from 2017-2022, when measured per 1m *vehicles*?
- Which five countries have the greatest number of road deaths, on average, from 2017-2022, when measured per 1m *inhabitants*?
- In which five countries have injuries increased the most, percentagewise, over the years? In which five countries has the percentage gone down most?
- Find the year in which each country had the greatest increase, per 1m inhabitants, of automative deaths. What about the year in which each country had the greatest decrease? Are these numbers related?
- Create a line plot showing the number of car-related deaths per year, as measured by 1m inhabitants, for the following countries: USA, France, Germany, Canada, the UK, Australia, Italy, Portugal, and Israel. Have they all reduced the number of automotive deaths in the last decade or two? Do any countries stand out as doing particularly worse than the others?
- Create a data frame with 3-letter country abbreviations as the index and country names as the data, from the Wikipedia page of ISO 3166 country codes
- Use this country-info data frame to display the graph in question 7 with country names, rather than three-letter codes.

I’ll be back tomorrow with my detailed solution and Jupyter notebook.

Until then,

Reuven