Project 2 — Data Tidying: Life Expectancy by Country

Author

Kevin Villa

Published

October 5, 2026

4. Data Source

This dataset is the World Bank’s “Life expectancy at birth, total (years)” indicator (SP.DYN.LE00.IN), pulled from the World Bank API for 16 countries across 2000–2023. This is the same dataset a classmate picked for the Week 5 Discussion 5A post: each country is a row, and each year is stored as its own column, so year, which is really a variable, is spread across 24 separate column headers instead of living in one column.

Source: World Bank, “Life expectancy at birth, total (years),” https://data.worldbank.org/indicator/SP.DYN.LE00.IN.

The raw file, preserving the original wide structure with no tidying applied, is committed here:

https://raw.githubusercontent.com/nowhyporque/data607DataAcquisitionAndManagement/refs/heads/main/project%202/life_expectancy_raw.csv

5. Data Structure Before Tidying

Code

life_raw <- read_csv("https://raw.githubusercontent.com/nowhyporque/data607DataAcquisitionAndManagement/refs/heads/main/project%202/life_expectancy_raw.csv")

glimpse(life_raw)

Rows: 16
Columns: 25
$ Country <chr> "Australia", "Brazil", "Canada", "China", "Germany", "Egypt", …
$ `2000`  <dbl> 79.23, 69.58, 79.17, 72.29, 77.93, 67.33, 78.97, 79.06, 77.74,…
$ `2001`  <dbl> 79.63, 69.98, 79.40, 72.68, 78.33, 67.61, 79.37, 79.16, 77.99,…
$ `2002`  <dbl> 79.94, 70.40, 79.53, 73.03, 78.23, 67.85, 79.57, 79.26, 78.14,…
$ `2003`  <dbl> 80.24, 70.88, 79.72, 73.39, 78.38, 68.05, 79.62, 79.11, 78.45,…
$ `2004`  <dbl> 80.49, 71.36, 79.99, 73.73, 78.68, 68.21, 79.87, 80.16, 78.75,…
$ `2005`  <dbl> 80.84, 71.83, 80.11, 74.09, 78.93, 68.36, 80.17, 80.16, 79.05,…
$ `2006`  <dbl> 81.04, 72.30, 80.54, 74.40, 79.13, 68.49, 80.82, 80.81, 79.25,…
$ `2007`  <dbl> 81.29, 72.73, 80.54, 74.79, 79.53, 68.63, 80.87, 81.11, 79.45,…
$ `2008`  <dbl> 81.40, 73.11, 80.72, 74.83, 79.74, 68.76, 81.18, 81.21, 79.60,…
$ `2009`  <dbl> 81.54, 73.46, 81.07, 75.32, 79.84, 68.92, 81.48, 81.41, 80.05,…
$ `2010`  <dbl> 81.70, 73.78, 81.32, 75.67, 79.99, 69.08, 81.63, 81.66, 80.40,…
$ `2011`  <dbl> 81.90, 74.05, 81.48, 75.89, 80.44, 69.23, 82.48, 82.11, 80.95,…
$ `2012`  <dbl> 82.05, 74.34, 81.66, 76.20, 80.54, 69.46, 82.43, 81.97, 80.90,…
$ `2013`  <dbl> 82.15, 74.61, 81.74, 76.45, 80.49, 69.63, 83.08, 82.22, 81.00,…
$ `2014`  <dbl> 82.30, 74.82, 81.78, 76.71, 81.09, 69.91, 83.23, 82.72, 81.30,…
$ `2015`  <dbl> 82.40, 75.11, 81.83, 76.98, 80.64, 70.14, 82.83, 82.32, 80.96,…
$ `2016`  <dbl> 82.45, 75.08, 81.94, 77.21, 80.99, 70.44, 83.33, 82.57, 81.16,…
$ `2017`  <dbl> 82.50, 75.38, 81.81, 77.24, 80.99, 70.71, 83.28, 82.58, 81.26,…
$ `2018`  <dbl> 82.75, 75.63, 81.80, 77.71, 80.89, 70.97, 83.43, 82.68, 81.26,…
$ `2019`  <dbl> 82.90, 75.81, 82.16, 77.94, 81.29, 71.21, 83.83, 82.83, 81.37,…
$ `2020`  <dbl> 83.20, 74.51, 81.53, 78.02, 81.04, 69.79, 82.23, 82.18, 80.33,…
$ `2021`  <dbl> 83.30, 73.04, 81.45, 78.12, 80.79, 68.98, 83.18, 82.32, 80.65,…
$ `2022`  <dbl> 83.20, 74.87, 81.09, 78.20, 80.61, 71.01, 83.13, 82.13, 81.01,…
$ `2023`  <dbl> 83.05, 75.85, 81.63, 77.95, 81.04, 71.63, 83.93, 82.83, 81.24,…

Code

kable(life_raw[, 1:6])

Country	2000	2001	2002	2003	2004
Australia	79.23	79.63	79.94	80.24	80.49
Brazil	69.58	69.98	70.40	70.88	71.36
Canada	79.17	79.40	79.53	79.72	79.99
China	72.29	72.68	73.03	73.39	73.73
Germany	77.93	78.33	78.23	78.38	78.68
Egypt	67.33	67.61	67.85	68.05	68.21
Spain	78.97	79.37	79.57	79.62	79.87
France	79.06	79.16	79.26	79.11	80.16
United Kingdom	77.74	77.99	78.14	78.45	78.75
Indonesia	66.29	66.58	66.95	67.13	65.52
India	62.75	63.16	63.65	64.09	64.48
Italy	79.78	80.13	80.23	79.98	80.78
Japan	81.08	81.42	81.69	81.76	82.03
Kenya	56.08	56.50	56.76	57.37	57.95
South Korea	75.91	76.41	76.77	77.21	77.67
Mexico	72.24	72.68	72.97	73.26	73.43

There are 16 rows (one per country) and 25 columns (Country plus one column per year, 2000 through 2023). Year is a variable baked into the column names rather than stored as values, which is exactly the untidy pattern described in the discussion post. This particular slice of the data has no missing cells, but the transformation step below still documents how a gap would be handled if one existed.

6. Transformation Steps

Code

life_long <- life_raw %>%
  pivot_longer(
    cols = -Country,
    names_to = "year",
    values_to = "life_expectancy"
  ) %>%
 
  mutate(
    year = as.integer(year),
    country = Country
  ) %>%
  select(country, year, life_expectancy) %>%
  arrange(country, year)


sum(is.na(life_long$life_expectancy))

[1] 0

Code

kable(head(life_long, 8))

country	year	life_expectancy
Australia	2000	79.23
Australia	2001	79.63
Australia	2002	79.94
Australia	2003	80.24
Australia	2004	80.49
Australia	2005	80.84
Australia	2006	81.04
Australia	2007	81.29

7. Analytical Methods

The discussion post’s business questions were about comparing life expectancy across countries over time, finding which countries improved the most, and looking at the COVID-19 dip and recovery. I calculate the change in life expectancy from 2000 to 2023 for each country, and separately look at the dip from 2019 to 2021 (the pandemic years) and the recovery by 2023.

Code

change_2000_2023 <- life_long %>%
  filter(year %in% c(2000, 2023)) %>%
  pivot_wider(names_from = year, values_from = life_expectancy, names_prefix = "yr_") %>%
  mutate(change = round(yr_2023 - yr_2000, 2)) %>%
  arrange(desc(change))

kable(change_2000_2023, caption = "Change in life expectancy, 2000 to 2023")

Change in life expectancy, 2000 to 2023
country	yr_2000	yr_2023	change
India	62.75	72.00	9.25
Kenya	56.08	63.65	7.57
South Korea	75.91	83.43	7.52
Brazil	69.58	75.85	6.27
China	72.29	77.95	5.66
Spain	78.97	83.93	4.96
Indonesia	66.29	71.15	4.86
Egypt	67.33	71.63	4.30
Australia	79.23	83.05	3.82
France	79.06	82.83	3.77
Italy	79.78	83.35	3.57
United Kingdom	77.74	81.24	3.50
Germany	77.93	81.04	3.11
Japan	81.08	84.04	2.96
Mexico	72.24	75.07	2.83
Canada	79.17	81.63	2.46

Code

covid_impact <- life_long %>%
  filter(year %in% c(2019, 2021, 2023)) %>%
  pivot_wider(names_from = year, values_from = life_expectancy, names_prefix = "yr_") %>%
  mutate(
    covid_dip = round(yr_2019 - yr_2021, 2),
    recovery_by_2023 = round(yr_2023 - yr_2019, 2)
  ) %>%
  arrange(desc(covid_dip)) %>%
  select(country, yr_2019, yr_2021, covid_dip, yr_2023, recovery_by_2023)

kable(covid_impact, caption = "COVID-19 dip (2019-2021) and recovery by 2023")

COVID-19 dip (2019-2021) and recovery by 2023
country	yr_2019	yr_2021	covid_dip	yr_2023	recovery_by_2023
Mexico	74.53	69.75	4.78	75.07	0.54
India	70.75	67.28	3.47	72.00	1.25
Indonesia	70.35	67.45	2.90	71.15	0.80
Brazil	75.81	73.04	2.77	75.85	0.04
Egypt	71.21	68.98	2.23	71.63	0.42
Kenya	62.94	61.23	1.71	63.65	0.71
Italy	83.50	82.65	0.85	83.35	-0.15
United Kingdom	81.37	80.65	0.72	81.24	-0.13
Canada	82.16	81.45	0.71	81.63	-0.53
Spain	83.83	83.18	0.65	83.93	0.10
France	82.83	82.32	0.51	82.83	0.00
Germany	81.29	80.79	0.50	81.04	-0.25
Japan	84.36	84.45	-0.09	84.04	-0.32
China	77.94	78.12	-0.18	77.95	0.01
South Korea	83.23	83.53	-0.30	83.43	0.20
Australia	82.90	83.30	-0.40	83.05	0.15

Code

ggplot(life_long, aes(x = year, y = life_expectancy, color = country)) +
  geom_line(linewidth = 0.8) +
  labs(title = "Life Expectancy by Country, 2000-2023",
       x = "Year", y = "Life Expectancy (years)", color = "Country") +
  theme_minimal()

India improved the most over the full period, gaining 9.25 years of life expectancy from 2000 to 2023, followed by Kenya (+7.57) and South Korea (+7.52). Every country in the dataset shows a visible dip around 2020-2021 on the chart, but the size of the dip varies a lot: Mexico lost 4.78 years between 2019 and 2021, the worst in this dataset, while higher-income countries like Canada and Japan barely dipped at all. By 2023 most countries had mostly recovered, though Mexico’s rebound (+0.54 years from 2019 to 2023) is still far short of fully making up its pandemic-era loss.

8. Conclusions

Reshaping year out of the column headers and into its own variable made it possible to plot a proper time trend and calculate year-over-year changes, neither of which would be straightforward with year baked into 24 separate columns. The long-run trend is one of steady improvement almost everywhere, but the sharp, uneven COVID-era dip shows that “steady” isn’t the same as “guaranteed” — a few years of a shared global shock hit some countries (Mexico, India, Indonesia) far harder than others (Canada, Japan, China), and by 2023 not every country had fully recovered to its pre pandemic trajectory. A natural next step would be to pull in a wider set of countries and correlate the size of each country’s COVID-era dip with factors like healthcare spending or vaccination rates.