12  Olympics Data

The new challenge: medallists are not medals

One row associates one athlete with one medal result. A team medal therefore appears once for every team member, and one athlete can appear in several medal rows. We must distinguish athlete-medal rows, unique medallists, and official event-level medals before making a ranking.

12.1 Learning objectives

By the end of this case, you should be able to:

  • distinguish athlete-medal rows from unique medallists;
  • use an athlete identifier rather than a displayed name;
  • distinguish team-member rows from event-level medals;
  • create a simple bar chart of the result; and
  • recognize that different sources use different country codes.

12.2 Set up the case

Continue in the same project

Open djr.Rproj and create 10-olympics.Rmd. Download medallists.csv and save it in data/.

library(tidyverse)

medallists <- read_csv("data/medallists.csv")

glimpse(medallists)
Rows: 2,315
Columns: 18
$ medal_date   <date> 2024-07-27, 2024-07-27, 2024-07-27, 2024-07-27, 2024-07-…
$ medal_type   <chr> "Gold Medal", "Silver Medal", "Bronze Medal", "Gold Medal…
$ medal_code   <dbl> 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 3, 1, 2, 3, …
$ name         <chr> "EVENEPOEL Remco", "GANNA Filippo", "van AERT Wout", "BRO…
$ gender       <chr> "Male", "Male", "Male", "Female", "Female", "Female", "Ma…
$ country_code <chr> "BEL", "ITA", "BEL", "AUS", "GBR", "USA", "KOR", "TUN", "…
$ country      <chr> "Belgium", "Italy", "Belgium", "Australia", "Great Britai…
$ country_long <chr> "Belgium", "Italy", "Belgium", "Australia", "Great Britai…
$ nationality  <chr> "Belgium", "Italy", "Belgium", "Australia", "Great Britai…
$ team         <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N…
$ team_gender  <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N…
$ discipline   <chr> "Cycling Road", "Cycling Road", "Cycling Road", "Cycling …
$ event        <chr> "Men's Individual Time Trial", "Men's Individual Time Tri…
$ event_type   <chr> "ATH", "ATH", "ATH", "ATH", "ATH", "ATH", "HATH", "HATH",…
$ url_event    <chr> "/en/paris-2024/results/cycling-road/men-s-individual-tim…
$ birth_date   <date> 2000-01-25, 1996-07-25, 1994-09-15, 1992-07-07, 1998-11-…
$ code_athlete <dbl> 1903136, 1923520, 1903147, 1940173, 1912525, 1955079, 192…
$ code_team    <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N…

One row represents one athlete associated with one medal result. An athlete with several medals appears several times. In a team event, one official medal creates a row for every team member.

These are therefore different measures:

Measure Meaning
Athlete-medal rows Athlete appearances in medal results
Unique medallists People with at least one medal row
Official medals Event-level awards, which are not counted directly by athlete rows

12.3 Strategy 1: separate rows from people

Begin by comparing the number of rows with the number of unique athlete identifiers:

medallists |>
  summarise(
    athlete_medal_rows = n(),
    unique_medallists = n_distinct(code_athlete)
  )

The two totals answer different questions. n() counts records, while n_distinct(code_athlete) counts people. The identifier is safer than the displayed name because names can be repeated or formatted inconsistently.

To find athletes associated with the most medal results, keep the identifier and readable name together:

athlete_medals <- medallists |>
  count(code_athlete, name, country) |>
  arrange(desc(n))

athlete_medals |>
  slice_head(n = 10)

The careful label is “athlete-medal rows,” not simply “medals.”

12.4 Strategy 2: expose team-event duplication

Count rows within each event, country, and medal type. The largest counts make team events visible:

medallists |>
  count(discipline, event, country, medal_type) |>
  arrange(desc(n)) |>
  slice_head(n = 10)

For a football or hockey result, n is close to the number of listed team members. Those rows describe medallists, but the team received one official medal for that event.

12.5 Strategy 3: create an event-level medal table

To approximate official medal awards, keep each event, country, and medal type once. event_type is retained because similarly named events can have different forms:

official_medals <- medallists |>
  distinct(
    discipline,
    event,
    event_type,
    country_code,
    country,
    medal_type
  )

official_medals |>
  summarise(number_of_official_medals = n())

This changes the unit from an athlete associated with an award to one country-medal result in one event. For publication, compare the resulting total with the official medal source and investigate any ties, mixed teams, or missing event identifiers.

12.6 Strategy 4: compare country measures

First count athlete records and unique people within each country:

country_medallists <- medallists |>
  group_by(country_code, country) |>
  summarise(
    unique_medallists = n_distinct(code_athlete),
    athlete_medal_rows = n()
  )

country_official_medals <- official_medals |>
  count(country_code, country, name = "official_medals")

country_measures <- country_medallists |>
  left_join(
    country_official_medals,
    by = c("country_code", "country")
  ) |>
  arrange(desc(official_medals))

country_measures |>
  slice_head(n = 10)

The three columns should not be used interchangeably. A country with many large successful teams can have far more athlete-medal rows than official medals.

12.7 Visualize the chosen measure

country_measures |>
  slice_head(n = 10) |>
  ggplot(
    aes(
      x = official_medals,
      y = fct_reorder(country, official_medals)
    )
  ) +
  geom_col() +
  labs(
    title = "Countries winning the most official medals at Paris 2024",
    x = "Official event-level medals",
    y = NULL,
    caption = "Source: supplied Paris 2024 medallists data"
  ) +
  theme_minimal()

If we charted unique_medallists instead, both the title and axis would need to say medallists. The wording follows the unit actually counted.

12.8 Country codes are not universal

The Olympics file uses International Olympic Committee codes. Many map sources use ISO-style codes instead. For example, Germany can appear as GER in one system and DEU in another.

Before joining to a map:

  1. identify the coding system used by each source;
  2. inspect unmatched countries with anti_join();
  3. create a documented lookup table for differences; and
  4. do not force non-country delegations onto a country polygon.

The Refugee Olympic Team and Individual Neutral Athletes are important examples: they should not automatically be assigned to one national map area.

12.9 Practice

Choose one discipline and calculate:

  1. athlete-medal rows;
  2. unique medallists; and
  3. official event-level medals.

Find one team event and show how many athlete rows represent one medal result. Then create a top-five table and chart for one measure, using the exact unit in the title.

12.10 Takeaways

Function or idea What it does in this case
count() Counts athlete-medal rows at the selected grouping
n_distinct() Counts different athlete identifiers
distinct() Keeps one event-country-medal combination
left_join() Places country measures in one comparison table
fct_reorder() Orders the countries by the chosen measure
Join-key check Prevents different country-code systems from silently dropping places
Want more control?

See the official dplyr reference for other ways to count and rank, and the ggplot2 reference for additional chart controls.