Code
library(tidyverse)
library(readr)Project 2 - Data Tidying and Transformation
Approach
For Project 2, I will work with three separate wide-format datasets selected from the Week 5 Discussion 5A options. My goal will be to preserve each dataset in its original wide format first and then use R, mainly the tidyr and dplyr packages, to transform each dataset into a tidy format that is easier to analyze. For each dataset, I will examine the original structure, identify the columns that need to be reorganized, rename variables when necessary, and address missing or inconsistent values. I will document the transformation process so that each step can be reproduced from the original raw data.
Some challenges I expect include determining the correct variables to keep as identifiers, deciding which columns should be pivoted from wide to long format, and handling missing or inconsistently formatted values without losing important information. I will use summary tables and visualizations when appropriate and will explain the results for each dataset in context.
Introduction
The purpose of this project is to practice cleaning, tidying, transforming, and analyzing three different wide-format datasets. The datasets include U.S. average tuition by state, population estimates for the ten most populous U.S. states, and World Bank life expectancy data. Although the datasets cover different topics, each provides an opportunity to transform data from a wide structure into a tidy format that is easier to analyze.
For each dataset, I will first examine the original structure, then use functions from tidyr and dplyr to reshape and clean the data. After the transformation is complete, I will use the tidy data to create summary statistics and visualizations. This process demonstrates how data preparation can make different types of datasets more useful for analysis.
Dataset 1 | U.S. Average Tuition
Code
tuition_url <- "https://raw.githubusercontent.com/yeimiperez14/Data_607/refs/heads/main/Project%202/us_avg_tuition(Table%205).csv"
tuition_raw <- read_csv(tuition_url)The first dataset contains average tuition information for U.S. states across multiple academic years. The original dataset was obtained from other student, Mubin Ejaz, on 5A discussion and was saved as a CSV file on my github in its original wide format: https://github.com/yeimiperez14/Data_607/tree/main/Project%202.
Data structure before Tidying
Code
dim(tuition_raw)[1] 50 13Code
names(tuition_raw) [1] "State" "2004-05" "2005-06" "2006-07" "2007-08" "2008-09" "2009-10"
[8] "2010-11" "2011-12" "2012-13" "2013-14" "2014-15" "2015-16"Code
glimpse(tuition_raw)Rows: 50
Columns: 13
$ State <chr> "Alabama", "Alaska", "Arizona", "Arkansas", "California", "C…
$ `2004-05` <chr> "$5,683", "$4,328", "$5,138", "$5,772", "$5,286", "$4,704", …
$ `2005-06` <chr> "$5,841", "$4,633", "$5,416", "$6,082", "$5,528", "$5,407", …
$ `2006-07` <chr> "$5,753", "$4,919", "$5,481", "$6,232", "$5,335", "$5,596", …
$ `2007-08` <chr> "$6,008", "$5,070", "$5,682", "$6,415", "$5,672", "$6,227", …
$ `2008-09` <chr> "$6,475", "$5,075", "$6,058", "$6,417", "$5,898", "$6,284", …
$ `2009-10` <chr> "$7,189", "$5,455", "$7,263", "$6,627", "$7,259", "$6,948", …
$ `2010-11` <chr> "$8,071", "$5,759", "$8,840", "$6,901", "$8,194", "$7,748", …
$ `2011-12` <chr> "$8,452", "$5,762", "$9,967", "$7,029", "$9,436", "$8,316", …
$ `2012-13` <chr> "$9,098", "$6,026", "$10,134", "$7,287", "$9,361", "$8,793",…
$ `2013-14` <chr> "$9,359", "$6,012", "$10,296", "$7,408", "$9,274", "$9,293",…
$ `2014-15` <chr> "$9,496", "$6,149", "$10,414", "$7,606", "$9,187", "$9,299",…
$ `2015-16` <chr> "$9,751", "$6,571", "$10,646", "$7,867", "$9,270", "$9,748",…We have 50 rows and 13 columns. Each row represents a state, while each academic year is stored in a separate column. The dataset is therefore in wide format because academic year is represented across multiple columns instead of being stored as one variable. The tuition columns were also imported as character values and will need to be converted to numeric values before analysis.
Transformation steps
Code
tuition_long <- tuition_raw %>%
pivot_longer(
cols = -State,
names_to = "academic_year",
values_to = "tuition"
)
head(tuition_long)# A tibble: 6 × 3
State academic_year tuition
<chr> <chr> <chr>
1 Alabama 2004-05 $5,683
2 Alabama 2005-06 $5,841
3 Alabama 2006-07 $5,753
4 Alabama 2007-08 $6,008
5 Alabama 2008-09 $6,475
6 Alabama 2009-10 $7,189 I transformed every column except state and placed the old year column names and corresponding tuition amount into one variable.
Cleaning the variables
Code
tuition_tidy <- tuition_long %>% rename( state = State ) %>% mutate( tuition = parse_number(tuition) )Tuition was imported as character/text. We need numeric values before calculating means.
Addressing missing values
Code
colSums(is.na(tuition_tidy)) state academic_year tuition
0 0 0 No missing values were identified, so no observations needed to be removed.
Analysis
Code
tuition_summary <- tuition_tidy %>%
group_by(academic_year) %>%
summarise(
average_tuition = mean(tuition, na.rm = TRUE)
)
tuition_summary# A tibble: 12 × 2
academic_year average_tuition
<chr> <dbl>
1 2004-05 6410.
2 2005-06 6654.
3 2006-07 6810.
4 2007-08 7086.
5 2008-09 7157.
6 2009-10 7762.
7 2010-11 8229.
8 2011-12 8539.
9 2012-13 8842.
10 2013-14 8948.
11 2014-15 9037.
12 2015-16 9318.group_by(academic_year) organizes the observations by year, summarise() creates one result for each academic year, mean() calculates average tuition across states and na.rm = TRUE prevents missing tuition values from breaking the calculation.
Visualization
Code
ggplot(
tuition_summary,
aes(
x = academic_year,
y = average_tuition,
group = 1
)
) +
geom_line() +
geom_point() +
labs(
title = "Average U.S. Tuition by Academic Year",
x = "Academic Year",
y = "Average Tuition ($)"
) +
scale_y_continuous(
labels = scales::dollar_format()
) +
theme_minimal() +
theme(
axis.text.x = element_text(
angle = 45,
hjust = 1
)
)
Interpretation
The results show a consistent increase in average U.S. tuition over the academic years analyzed. Average tuition increased from approximately $6,410 in 2004–05 to $8,948 in 2013–14, an increase of about $2,538. The largest increases appear in the later years of the dataset, particularly between 2008–09 and 2012–13. Overall, the analysis shows a clear upward trend in average tuition over time.
Code
highest_tuition <- tuition_tidy %>%
arrange(desc(tuition)) %>%
select(state, academic_year, tuition) %>%
slice(1)
highest_tuition# A tibble: 1 × 3
state academic_year tuition
<chr> <chr> <dbl>
1 New Hampshire 2012-13 15224Code
tuition_change <- tuition_tidy %>%
group_by(state) %>%
summarise(
starting_tuition = first(tuition),
ending_tuition = last(tuition),
tuition_change = ending_tuition - starting_tuition
)
tuition_change# A tibble: 50 × 4
state starting_tuition ending_tuition tuition_change
<chr> <dbl> <dbl> <dbl>
1 Alabama 5683 9751 4068
2 Alaska 4328 6571 2243
3 Arizona 5138 10646 5508
4 Arkansas 5772 7867 2095
5 California 5286 9270 3984
6 Colorado 4704 9748 5044
7 Connecticut 7984 11397 3413
8 Delaware 8353 11676 3323
9 Florida 3848 6360 2512
10 Georgia 4298 8447 4149
# ℹ 40 more rowsClassifying the direction of change
Code
tuition_change <- tuition_change %>%
mutate(
trend = case_when(
tuition_change > 0 ~ "Lower to Higher",
tuition_change < 0 ~ "Higher to Lower",
TRUE ~ "No Change"
)
)
tuition_change# A tibble: 50 × 5
state starting_tuition ending_tuition tuition_change trend
<chr> <dbl> <dbl> <dbl> <chr>
1 Alabama 5683 9751 4068 Lower to Higher
2 Alaska 4328 6571 2243 Lower to Higher
3 Arizona 5138 10646 5508 Lower to Higher
4 Arkansas 5772 7867 2095 Lower to Higher
5 California 5286 9270 3984 Lower to Higher
6 Colorado 4704 9748 5044 Lower to Higher
7 Connecticut 7984 11397 3413 Lower to Higher
8 Delaware 8353 11676 3323 Lower to Higher
9 Florida 3848 6360 2512 Lower to Higher
10 Georgia 4298 8447 4149 Lower to Higher
# ℹ 40 more rowsWhich state increased the most
Code
tuition_change %>%
arrange(desc(tuition_change)) %>%
head(10)# A tibble: 10 × 5
state starting_tuition ending_tuition tuition_change trend
<chr> <dbl> <dbl> <dbl> <chr>
1 Hawaii 4267 10175 5908 Lower to Higher
2 Arizona 5138 10646 5508 Lower to Higher
3 Colorado 4704 9748 5044 Lower to Higher
4 Illinois 8183 13189 5006 Lower to Higher
5 New Hampshire 10188 15160 4972 Lower to Higher
6 Virginia 7030 11819 4789 Lower to Higher
7 Georgia 4298 8447 4149 Lower to Higher
8 Washington 6192 10288 4096 Lower to Higher
9 Alabama 5683 9751 4068 Lower to Higher
10 Michigan 7931 11991 4060 Lower to HigherWhich one decreased the most
Code
tuition_change %>%
filter(tuition_change < 0) %>%
arrange(tuition_change)# A tibble: 1 × 5
state starting_tuition ending_tuition tuition_change trend
<chr> <dbl> <dbl> <dbl> <chr>
1 Ohio 10378 10196 -182 Higher to LowerCode
tuition_change %>%
count(trend)# A tibble: 2 × 2
trend n
<chr> <int>
1 Higher to Lower 1
2 Lower to Higher 49Visualization
Code
ggplot(
tuition_change,
aes(
x = tuition_change,
y = reorder(state, tuition_change)
)
) +
geom_col() +
scale_x_continuous(
labels = scales::dollar_format()
) +
labs(
title = "Change in Average Tuition by State",
subtitle = "2004-05 to 2015-16",
x = "Change in Tuition ($)",
y = NULL
) +
theme_minimal() +
theme(
axis.text.y = element_text(size = 8),
plot.title = element_text(size = 16),
plot.subtitle = element_text(size = 11)
)
Code
top_10_tuition_change <- tuition_change %>%
arrange(desc(tuition_change)) %>%
slice_head(n = 10)
ggplot(
top_10_tuition_change,
aes(
x = tuition_change,
y = reorder(state, tuition_change)
)
) +
geom_col() +
scale_x_continuous(
labels = scales::dollar_format()
) +
labs(
title = "Top 10 States with the Largest Tuition Increases",
subtitle = "2004-05 to 2015-16",
x = "Increase in Tuition ($)",
y = "State"
) +
theme_minimal()
This part sorts the states by tuition_change and keeps only the 10 largest increases. The graph then compares those 10 states rather than trying to squeeze all 50 state names into one figure.
Interpretation
The results show that tuition increased substantially in several states between 2004–05 and 2015–16. Hawaii had the largest increase, rising from $4,267 to $10,175, an increase of $5,908. Arizona had the second-largest increase at $5,508, followed by Colorado at $5,044 and Illinois at $5,006. All 10 states with the largest changes showed a lower-to-higher tuition trend over the period.
Dataset 2 | Census top 10 States
Code
population_url <- "https://raw.githubusercontent.com/yeimiperez14/Data_607/refs/heads/main/Project%202/census_top10_states_2022_untidy.csv"
population_raw <- read_csv(population_url)This dataset contains population estimates for the 10 most populous U.S. states. It includes each state’s rank and geographic area, along with population estimates for April 1, 2020, July 1, 2021, and July 1, 2022. The population dates are stored in separate columns, making this a wide-format dataset that can be transformed into a tidy long format for analysis. The original dataset was obtained from other student, Anastasia Gmyrina, on 5A discussion and was saved as a CSV file on my github in its original wide format: https://github.com/yeimiperez14/Data_607/tree/main/Project%202.
Data structure before tidying
Code
dim(population_raw)[1] 10 5Code
names(population_raw)[1] "Rank" "Geographic Area"
[3] "April 1, 2020 (Estimates Base)" "July 1, 2021"
[5] "July 1, 2022" Code
head(population_raw)# A tibble: 6 × 5
Rank `Geographic Area` April 1, 2020 (Estimat…¹ `July 1, 2021` `July 1, 2022`
<dbl> <chr> <dbl> <dbl> <dbl>
1 1 California 39538245 39142991 39029342
2 2 Texas 29145428 29558864 30029572
3 3 Florida 21538226 21828069 22244823
4 4 New York 20201230 19857492 19677151
5 5 Pennsylvania 13002689 13012059 12972008
6 6 Illinois 12812545 12686469 12582032
# ℹ abbreviated name: ¹`April 1, 2020 (Estimates Base)`Code
glimpse(population_raw)Rows: 10
Columns: 5
$ Rank <dbl> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
$ `Geographic Area` <chr> "California", "Texas", "Florida", "Ne…
$ `April 1, 2020 (Estimates Base)` <dbl> 39538245, 29145428, 21538226, 2020123…
$ `July 1, 2021` <dbl> 39142991, 29558864, 21828069, 1985749…
$ `July 1, 2022` <dbl> 39029342, 30029572, 22244823, 1967715…Code
names(population_raw)[1] "Rank" "Geographic Area"
[3] "April 1, 2020 (Estimates Base)" "July 1, 2021"
[5] "July 1, 2022" Transformation steps
Code
population_long <- population_raw %>%
pivot_longer(
cols = c(
`April 1, 2020 (Estimates Base)`,
`July 1, 2021`,
`July 1, 2022`
),
names_to = "population_date",
values_to = "population"
)
head(population_long)# A tibble: 6 × 4
Rank `Geographic Area` population_date population
<dbl> <chr> <chr> <dbl>
1 1 California April 1, 2020 (Estimates Base) 39538245
2 1 California July 1, 2021 39142991
3 1 California July 1, 2022 39029342
4 2 Texas April 1, 2020 (Estimates Base) 29145428
5 2 Texas July 1, 2021 29558864
6 2 Texas July 1, 2022 30029572Before, one state occupied one row, with three different population columns. After pivot_longer(), each state will have three rows
I stored population as a number.
Code
population_tidy <- population_long %>%
rename(
rank = Rank,
state = `Geographic Area`
)
names(population_tidy)[1] "rank" "state" "population_date" "population" Code
population_tidy <- population_tidy %>%
mutate(
population = parse_number(as.character(population))
)Code
glimpse(population_tidy)Rows: 30
Columns: 4
$ rank <dbl> 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4, 5, 5, 5, 6, 6, 6, …
$ state <chr> "California", "California", "California", "Texas", "Te…
$ population_date <chr> "April 1, 2020 (Estimates Base)", "July 1, 2021", "Jul…
$ population <dbl> 39538245, 39142991, 39029342, 29145428, 29558864, 3002…Code
head(population_tidy)# A tibble: 6 × 4
rank state population_date population
<dbl> <chr> <chr> <dbl>
1 1 California April 1, 2020 (Estimates Base) 39538245
2 1 California July 1, 2021 39142991
3 1 California July 1, 2022 39029342
4 2 Texas April 1, 2020 (Estimates Base) 29145428
5 2 Texas July 1, 2021 29558864
6 2 Texas July 1, 2022 30029572Addressing missing values
Code
colSums(is.na(population_tidy)) rank state population_date population
0 0 0 0 No missing values identified.
Analysis
Code
population_change <- population_tidy %>%
filter(
population_date %in% c(
"April 1, 2020 (Estimates Base)",
"July 1, 2022"
)
) %>%
select(state, population_date, population) %>%
pivot_wider(
names_from = population_date,
values_from = population
)
population_change# A tibble: 10 × 3
state `April 1, 2020 (Estimates Base)` `July 1, 2022`
<chr> <dbl> <dbl>
1 California 39538245 39029342
2 Texas 29145428 30029572
3 Florida 21538226 22244823
4 New York 20201230 19677151
5 Pennsylvania 13002689 12972008
6 Illinois 12812545 12582032
7 Ohio 11799374 11756058
8 Georgia 10711937 10912876
9 North Carolina 10439414 10698973
10 Michigan 10077325 10034113I compared each state’s 2020 population estimate with its 2022 population estimate. I calculated both the numerical change and the percentage change to determine which states gained or lost population.
Code
population_change <- population_change %>%
mutate(
population_change =
`July 1, 2022` - `April 1, 2020 (Estimates Base)`,
percent_change =
(population_change / `April 1, 2020 (Estimates Base)`) * 100
)
population_change# A tibble: 10 × 5
state April 1, 2020 (Estim…¹ `July 1, 2022` population_change percent_change
<chr> <dbl> <dbl> <dbl> <dbl>
1 Calif… 39538245 39029342 -508903 -1.29
2 Texas 29145428 30029572 884144 3.03
3 Flori… 21538226 22244823 706597 3.28
4 New Y… 20201230 19677151 -524079 -2.59
5 Penns… 13002689 12972008 -30681 -0.236
6 Illin… 12812545 12582032 -230513 -1.80
7 Ohio 11799374 11756058 -43316 -0.367
8 Georg… 10711937 10912876 200939 1.88
9 North… 10439414 10698973 259559 2.49
10 Michi… 10077325 10034113 -43212 -0.429
# ℹ abbreviated name: ¹`April 1, 2020 (Estimates Base)`A positive percentage means the state’s population increased. A negative percentage means it decreased.
Code
population_change <- population_change %>%
mutate(
population_trend = case_when(
percent_change > 0 ~ "Population Gain",
percent_change < 0 ~ "Population Loss",
TRUE ~ "No Change"
)
)
population_change# A tibble: 10 × 6
state April 1, 2020 (Estim…¹ `July 1, 2022` population_change percent_change
<chr> <dbl> <dbl> <dbl> <dbl>
1 Calif… 39538245 39029342 -508903 -1.29
2 Texas 29145428 30029572 884144 3.03
3 Flori… 21538226 22244823 706597 3.28
4 New Y… 20201230 19677151 -524079 -2.59
5 Penns… 13002689 12972008 -30681 -0.236
6 Illin… 12812545 12582032 -230513 -1.80
7 Ohio 11799374 11756058 -43316 -0.367
8 Georg… 10711937 10912876 200939 1.88
9 North… 10439414 10698973 259559 2.49
10 Michi… 10077325 10034113 -43212 -0.429
# ℹ abbreviated name: ¹`April 1, 2020 (Estimates Base)`
# ℹ 1 more variable: population_trend <chr>Added population trend label
Code
population_change %>%
arrange(desc(percent_change))# A tibble: 10 × 6
state April 1, 2020 (Estim…¹ `July 1, 2022` population_change percent_change
<chr> <dbl> <dbl> <dbl> <dbl>
1 Flori… 21538226 22244823 706597 3.28
2 Texas 29145428 30029572 884144 3.03
3 North… 10439414 10698973 259559 2.49
4 Georg… 10711937 10912876 200939 1.88
5 Penns… 13002689 12972008 -30681 -0.236
6 Ohio 11799374 11756058 -43316 -0.367
7 Michi… 10077325 10034113 -43212 -0.429
8 Calif… 39538245 39029342 -508903 -1.29
9 Illin… 12812545 12582032 -230513 -1.80
10 New Y… 20201230 19677151 -524079 -2.59
# ℹ abbreviated name: ¹`April 1, 2020 (Estimates Base)`
# ℹ 1 more variable: population_trend <chr>Sorted by percentage change
Visualization
Code
ggplot(
population_change,
aes(
x = percent_change,
y = reorder(state, percent_change)
)
) +
geom_col() +
geom_vline(
xintercept = 0,
linetype = "dashed"
) +
labs(
title = "Population Change in the\n10 Most Populous U.S. States",
subtitle = "2020 to 2022",
x = "Population Change (%)",
y = "State"
) +
scale_x_continuous(
labels = scales::label_number(
accuracy = 0.1,
suffix = "%"
)
) +
theme_minimal() +
theme(
plot.title = element_text(
size = 16,
face = "bold"
),
plot.subtitle = element_text(
size = 11
),
axis.title = element_text(
size = 11
),
axis.text = element_text(
size = 10
)
)
Code
population_summary <- population_change %>%
select(
state,
population_change,
percent_change,
population_trend
) %>%
mutate(
percent_change = round(percent_change, 2)
) %>%
arrange(desc(percent_change))
population_summary# A tibble: 10 × 4
state population_change percent_change population_trend
<chr> <dbl> <dbl> <chr>
1 Florida 706597 3.28 Population Gain
2 Texas 884144 3.03 Population Gain
3 North Carolina 259559 2.49 Population Gain
4 Georgia 200939 1.88 Population Gain
5 Pennsylvania -30681 -0.24 Population Loss
6 Ohio -43316 -0.37 Population Loss
7 Michigan -43212 -0.43 Population Loss
8 California -508903 -1.29 Population Loss
9 Illinois -230513 -1.8 Population Loss
10 New York -524079 -2.59 Population Loss Interpretation
From 2020 to 2022, four of the 10 states experienced population growth, while six experienced population loss. Florida had the largest percentage increase at approximately 3.28%, followed by Texas at 3.03%, North Carolina at 2.49%, and Georgia at 1.88%. New York experienced the largest percentage decline at approximately 2.59%, followed by Illinois at 1.80% and California at 1.29%. In terms of the number of residents, Texas had the largest population gain with approximately 884,144 additional residents, while New York had the largest population loss with approximately 524,079 fewer residents.
Dataset 3 | World Bank life Expectancy
Code
life_url <- "https://raw.githubusercontent.com/yeimiperez14/Data_607/refs/heads/main/Project%202/Life_Expectancy_API_SP.DYN.LE00.IN_DS2_en_csv_v2_460061.csv"
life_raw <- read_csv(
life_url,
skip = 4
)The third dataset contains life expectancy at birth data from the World Bank for countries and regions beginning in 1960. The original dataset is in wide format because each year is stored in a separate column.
The goal of this analysis is to transform the dataset into a tidy long format with separate variables for country, year, and life expectancy. I will then compare changes in life expectancy between Bangladesh and the United States and examine whether the life expectancy gapbetween the two countries has increased or decreased over time. The original dataset was obtained from other student, Ummay Rukiya, on 5A discussion and was saved as a CSV file on my github in its original wide format: https://github.com/yeimiperez14/Data_607/tree/main/Project%202.
Data structure before tidying
Code
names(life_raw) [1] "Country Name" "Country Code" "Indicator Name" "Indicator Code"
[5] "1960" "1961" "1962" "1963"
[9] "1964" "1965" "1966" "1967"
[13] "1968" "1969" "1970" "1971"
[17] "1972" "1973" "1974" "1975"
[21] "1976" "1977" "1978" "1979"
[25] "1980" "1981" "1982" "1983"
[29] "1984" "1985" "1986" "1987"
[33] "1988" "1989" "1990" "1991"
[37] "1992" "1993" "1994" "1995"
[41] "1996" "1997" "1998" "1999"
[45] "2000" "2001" "2002" "2003"
[49] "2004" "2005" "2006" "2007"
[53] "2008" "2009" "2010" "2011"
[57] "2012" "2013" "2014" "2015"
[61] "2016" "2017" "2018" "2019"
[65] "2020" "2021" "2022" "2023"
[69] "2024" "2025" "...71" Code
dim(life_raw)[1] 265 71Code
names(life_raw) [1] "Country Name" "Country Code" "Indicator Name" "Indicator Code"
[5] "1960" "1961" "1962" "1963"
[9] "1964" "1965" "1966" "1967"
[13] "1968" "1969" "1970" "1971"
[17] "1972" "1973" "1974" "1975"
[21] "1976" "1977" "1978" "1979"
[25] "1980" "1981" "1982" "1983"
[29] "1984" "1985" "1986" "1987"
[33] "1988" "1989" "1990" "1991"
[37] "1992" "1993" "1994" "1995"
[41] "1996" "1997" "1998" "1999"
[45] "2000" "2001" "2002" "2003"
[49] "2004" "2005" "2006" "2007"
[53] "2008" "2009" "2010" "2011"
[57] "2012" "2013" "2014" "2015"
[61] "2016" "2017" "2018" "2019"
[65] "2020" "2021" "2022" "2023"
[69] "2024" "2025" "...71" Code
head(life_raw)# A tibble: 6 × 71
`Country Name` `Country Code` `Indicator Name` `Indicator Code` `1960` `1961`
<chr> <chr> <chr> <chr> <dbl> <dbl>
1 Aruba ABW Life expectancy… SP.DYN.LE00.IN 64.0 64.2
2 Africa Eastern… AFE Life expectancy… SP.DYN.LE00.IN 44.2 44.5
3 Afghanistan AFG Life expectancy… SP.DYN.LE00.IN 32.8 33.3
4 Africa Western… AFW Life expectancy… SP.DYN.LE00.IN 37.8 38.1
5 Angola AGO Life expectancy… SP.DYN.LE00.IN 37.9 36.9
6 Albania ALB Life expectancy… SP.DYN.LE00.IN 56.4 57.5
# ℹ 65 more variables: `1962` <dbl>, `1963` <dbl>, `1964` <dbl>, `1965` <dbl>,
# `1966` <dbl>, `1967` <dbl>, `1968` <dbl>, `1969` <dbl>, `1970` <dbl>,
# `1971` <dbl>, `1972` <dbl>, `1973` <dbl>, `1974` <dbl>, `1975` <dbl>,
# `1976` <dbl>, `1977` <dbl>, `1978` <dbl>, `1979` <dbl>, `1980` <dbl>,
# `1981` <dbl>, `1982` <dbl>, `1983` <dbl>, `1984` <dbl>, `1985` <dbl>,
# `1986` <dbl>, `1987` <dbl>, `1988` <dbl>, `1989` <dbl>, `1990` <dbl>,
# `1991` <dbl>, `1992` <dbl>, `1993` <dbl>, `1994` <dbl>, `1995` <dbl>, …Code
glimpse(life_raw)Rows: 265
Columns: 71
$ `Country Name` <chr> "Aruba", "Africa Eastern and Southern", "Afghanistan"…
$ `Country Code` <chr> "ABW", "AFE", "AFG", "AFW", "AGO", "ALB", "AND", "ARB…
$ `Indicator Name` <chr> "Life expectancy at birth, total (years)", "Life expe…
$ `Indicator Code` <chr> "SP.DYN.LE00.IN", "SP.DYN.LE00.IN", "SP.DYN.LE00.IN",…
$ `1960` <dbl> 64.04900, 44.16966, 32.79900, 37.77964, 37.93300, 56.…
$ `1961` <dbl> 64.21500, 44.46884, 33.29100, 38.05897, 36.90200, 57.…
$ `1962` <dbl> 64.60200, 44.87789, 33.75700, 38.68179, 37.16800, 58.…
$ `1963` <dbl> 64.94400, 45.16058, 34.20100, 38.93692, 37.41900, 59.…
$ `1964` <dbl> 65.30300, 45.53570, 34.67300, 39.19438, 37.70400, 60.…
$ `1965` <dbl> 65.61500, 45.77072, 35.12400, 39.47888, 37.96800, 61.…
$ `1966` <dbl> 66.12600, 45.76573, 35.58300, 39.71703, 38.25800, 62.…
$ `1967` <dbl> 66.38500, 46.44075, 36.04200, 39.52491, 38.61600, 62.…
$ `1968` <dbl> 66.74400, 46.73863, 36.51000, 40.25261, 38.96800, 63.…
$ `1969` <dbl> 67.09800, 46.97748, 36.97900, 40.56192, 39.32900, 64.…
$ `1970` <dbl> 67.44000, 47.24801, 37.46000, 41.27364, 39.68800, 65.…
$ `1971` <dbl> 67.78300, 47.46402, 37.93200, 41.72578, 40.07600, 65.…
$ `1972` <dbl> 68.33500, 47.16679, 38.42300, 42.35192, 40.46900, 66.…
$ `1973` <dbl> 68.86200, 47.74661, 38.95100, 42.86392, 40.87100, 67.…
$ `1974` <dbl> 69.27800, 47.74433, 39.46900, 43.43305, 41.26000, 67.…
$ `1975` <dbl> 69.56400, 47.96855, 39.99400, 43.96301, 40.81700, 68.…
$ `1976` <dbl> 69.80800, 48.56814, 40.51800, 44.74875, 40.81200, 68.…
$ `1977` <dbl> 70.05400, 48.86072, 41.08200, 45.35839, 41.21500, 68.…
$ `1978` <dbl> 70.27100, 48.98392, 40.08600, 45.87221, 41.57300, 69.…
$ `1979` <dbl> 70.50700, 49.41699, 38.84400, 46.32430, 41.91300, 69.…
$ `1980` <dbl> 70.77100, 49.81671, 39.25800, 46.69179, 42.24200, 69.…
$ `1981` <dbl> 71.34400, 50.02049, 39.40600, 47.07056, 42.55600, 70.…
$ `1982` <dbl> 71.48500, 50.29083, 36.05800, 47.36857, 42.85700, 70.…
$ `1983` <dbl> 71.60600, 48.90410, 36.51700, 47.61281, 41.97200, 70.…
$ `1984` <dbl> 71.71100, 48.99796, 31.47300, 47.78959, 42.24500, 70.…
$ `1985` <dbl> 71.79200, 49.39290, 32.13200, 47.93580, 42.49500, 71.…
$ `1986` <dbl> 71.83100, 50.00976, 38.40000, 48.02933, 42.73900, 71.…
$ `1987` <dbl> 72.44800, 51.06894, 38.83100, 48.13055, 40.78600, 71.…
$ `1988` <dbl> 72.51900, 50.87661, 43.23800, 48.36268, 41.47100, 72.…
$ `1989` <dbl> 72.53100, 51.26587, 44.49600, 48.48768, 41.69800, 72.…
$ `1990` <dbl> 72.54600, 51.09633, 45.11800, 48.45116, 41.85400, 72.…
$ `1991` <dbl> 72.59200, 50.87324, 45.52100, 48.52342, 43.81200, 73.…
$ `1992` <dbl> 72.71700, 50.71595, 46.56900, 48.68137, 42.26700, 73.…
$ `1993` <dbl> 72.77700, 51.07376, 51.02100, 48.84801, 42.19000, 73.…
$ `1994` <dbl> 72.79600, 50.96659, 50.96900, 48.85305, 43.56700, 73.…
$ `1995` <dbl> 72.83200, 51.48206, 52.10300, 49.02143, 46.13900, 74.…
$ `1996` <dbl> 72.85600, 51.35319, 52.83000, 49.10629, 46.41800, 74.…
$ `1997` <dbl> 72.90400, 51.57089, 53.21200, 49.21372, 46.68800, 73.…
$ `1998` <dbl> 72.94000, 51.31796, 52.48700, 49.44722, 45.45200, 74.…
$ `1999` <dbl> 72.85700, 51.80708, 54.53200, 49.81829, 45.80800, 74.…
$ `2000` <dbl> 72.93900, 52.55734, 55.00500, 50.29261, 46.50100, 74.…
$ `2001` <dbl> 73.04400, 52.87554, 55.51100, 50.67260, 47.03200, 75.…
$ `2002` <dbl> 73.13500, 53.20674, 56.22500, 51.10301, 47.87400, 75.…
$ `2003` <dbl> 73.23600, 53.67830, 57.17100, 51.57339, 50.21800, 75.…
$ `2004` <dbl> 73.22300, 54.19293, 57.81000, 52.10161, 51.12300, 75.…
$ `2005` <dbl> 73.41500, 54.80768, 58.24700, 52.55142, 52.13000, 76.…
$ `2006` <dbl> 73.49800, 55.69352, 58.55300, 52.97463, 52.96500, 76.…
$ `2007` <dbl> 73.64200, 56.48435, 58.95600, 53.47884, 54.20000, 77.…
$ `2008` <dbl> 73.78300, 57.24011, 59.70800, 53.91303, 55.28100, 78.…
$ `2009` <dbl> 73.97400, 58.09553, 60.24800, 53.84881, 56.22500, 78.…
$ `2010` <dbl> 74.29700, 58.82728, 60.70200, 54.60065, 57.24200, 78.…
$ `2011` <dbl> 74.57800, 59.20000, 61.25000, 54.96124, 58.09300, 78.…
$ `2012` <dbl> 74.84100, 60.24951, 61.73500, 55.28986, 58.91600, 78.…
$ `2013` <dbl> 75.07200, 60.89574, 62.18800, 55.55166, 59.70500, 77.…
$ `2014` <dbl> 75.26100, 61.25144, 62.26000, 55.69158, 60.39600, 78.…
$ `2015` <dbl> 75.40500, 61.71303, 62.27000, 56.03400, 61.04200, 78.…
$ `2016` <dbl> 75.54000, 62.16798, 62.64600, 56.38756, 61.61900, 78.…
$ `2017` <dbl> 75.62000, 62.59128, 62.40600, 56.62076, 62.12200, 78.…
$ `2018` <dbl> 75.88000, 63.33069, 62.44300, 57.03077, 62.62200, 79.…
$ `2019` <dbl> 76.01900, 63.85726, 62.94100, 57.14283, 63.05100, 79.…
$ `2020` <dbl> 75.40600, 63.76648, 61.45400, 57.35771, 63.11600, 77.…
$ `2021` <dbl> 73.65500, 62.98000, 60.41700, 57.35502, 62.95800, 76.…
$ `2022` <dbl> 76.22600, 64.48715, 65.61700, 57.97900, 64.24600, 78.…
$ `2023` <dbl> 76.35300, 65.14623, 66.03500, 58.84748, 64.61700, 79.…
$ `2024` <dbl> 76.50000, 65.34993, 66.28900, 59.04154, 64.80500, 79.…
$ `2025` <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N…
$ ...71 <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N…Transformation steps
Code
life_clean <- life_raw %>%
select(-where(~ all(is.na(.))))
dim(life_clean)[1] 265 69all(is.na(.)) checks whether every value in a column is missing. select(-where(...)) removes any completely empty columns. I removed any completely empty columns because they contained no information that could be used in the analysis. No country observations were removed during this step.
Code
life_long <- life_clean %>%
pivot_longer(
cols = matches("^\\d{4}$"),
names_to = "year",
values_to = "life_expectancy"
)
head(life_long)# A tibble: 6 × 6
`Country Name` `Country Code` `Indicator Name` `Indicator Code` year
<chr> <chr> <chr> <chr> <chr>
1 Aruba ABW Life expectancy at birth… SP.DYN.LE00.IN 1960
2 Aruba ABW Life expectancy at birth… SP.DYN.LE00.IN 1961
3 Aruba ABW Life expectancy at birth… SP.DYN.LE00.IN 1962
4 Aruba ABW Life expectancy at birth… SP.DYN.LE00.IN 1963
5 Aruba ABW Life expectancy at birth… SP.DYN.LE00.IN 1964
6 Aruba ABW Life expectancy at birth… SP.DYN.LE00.IN 1965
# ℹ 1 more variable: life_expectancy <dbl>matches(“^\\d{4}$”) selects column names containing exactly four digits, then pivot_longer() moves those years into one year column and puts the corresponding values into one life_expectancy column.
Code
life_tidy <- life_long %>%
rename(
country = `Country Name`,
country_code = `Country Code`,
indicator = `Indicator Name`,
indicator_code = `Indicator Code`
) %>%
mutate(
year = as.integer(year)
)
glimpse(life_tidy)Rows: 17,225
Columns: 6
$ country <chr> "Aruba", "Aruba", "Aruba", "Aruba", "Aruba", "Aruba", …
$ country_code <chr> "ABW", "ABW", "ABW", "ABW", "ABW", "ABW", "ABW", "ABW"…
$ indicator <chr> "Life expectancy at birth, total (years)", "Life expec…
$ indicator_code <chr> "SP.DYN.LE00.IN", "SP.DYN.LE00.IN", "SP.DYN.LE00.IN", …
$ year <int> 1960, 1961, 1962, 1963, 1964, 1965, 1966, 1967, 1968, …
$ life_expectancy <dbl> 64.049, 64.215, 64.602, 64.944, 65.303, 65.615, 66.126…We now have one variable per column and one observation per row.
Addressing missing values
Code
colSums(is.na(life_tidy)) country country_code indicator indicator_code year
0 0 0 0 0
life_expectancy
99 Code
life_tidy %>%
filter(
country %in% c("Bangladesh", "United States")
) %>%
summarise(
total_rows = n(),
missing_life_expectancy = sum(is.na(life_expectancy)),
first_year_available = min(year[!is.na(life_expectancy)]),
last_year_available = max(year[!is.na(life_expectancy)])
)# A tibble: 1 × 4
total_rows missing_life_expectancy first_year_available last_year_available
<int> <int> <int> <int>
1 130 0 1960 2024Bangladesh and the United States both have complete life-expectancy data from 1960 through 2024. You have 130 total observations because there are 65 years for each of the two countries: 65 years × 2 countries = 130 observations with no missing life expectancy values.
Analysis
Code
life_comparison <- life_tidy %>%
filter(
country %in% c("Bangladesh", "United States")
) %>%
select(
country,
year,
life_expectancy
)
life_comparison# A tibble: 130 × 3
country year life_expectancy
<chr> <int> <dbl>
1 Bangladesh 1960 44.0
2 Bangladesh 1961 44.9
3 Bangladesh 1962 45.8
4 Bangladesh 1963 45.7
5 Bangladesh 1964 46.8
6 Bangladesh 1965 46.0
7 Bangladesh 1966 47.6
8 Bangladesh 1967 48.1
9 Bangladesh 1968 48.4
10 Bangladesh 1969 48.7
# ℹ 120 more rowsHere I filtered the tidy dataset to our two countries.
Visualization
Code
ggplot(
life_comparison,
aes(
x = year,
y = life_expectancy,
color = country
)
) +
geom_line(linewidth = 1) +
labs(
title = "Life Expectancy Over Time",
subtitle = "Bangladesh and the United States, 1960-2024",
x = "Year",
y = "Life Expectancy (Years)",
color = "Country"
) +
theme_minimal() +
theme(
plot.title = element_text(
size = 16,
face = "bold"
),
plot.subtitle = element_text(
size = 11
)
)
Code
life_gap <- life_comparison %>%
pivot_wider(
names_from = country,
values_from = life_expectancy
) %>%
mutate(
gap = `United States` - Bangladesh
)
life_gap# A tibble: 65 × 4
year Bangladesh `United States` gap
<int> <dbl> <dbl> <dbl>
1 1960 44.0 69.8 25.8
2 1961 44.9 70.3 25.4
3 1962 45.8 70.1 24.4
4 1963 45.7 69.9 24.2
5 1964 46.8 70.2 23.4
6 1965 46.0 70.2 24.2
7 1966 47.6 70.2 22.6
8 1967 48.1 70.6 22.5
9 1968 48.4 70.0 21.6
10 1969 48.7 70.5 21.8
# ℹ 55 more rowsThe gap variable tells us how many years higher U.S. life expectancy was than Bangladesh’s in each year.
Code
life_gap %>%
filter(year %in% c(1960, 2024))# A tibble: 2 × 4
year Bangladesh `United States` gap
<int> <dbl> <dbl> <dbl>
1 1960 44.0 69.8 25.8
2 2024 74.9 78.9 3.96Here I compared the beginning and end values for 1960 and 2024. 1960: Bangladesh = 43.98 years; U.S. = 69.77 years → gap = 25.79 years. 2024: Bangladesh = 74.93 years; U.S. = 78.89 years → gap = 3.96 years. Therefore, the gap narrowed by about 21.83 years.
Code
gap_change <- life_gap %>%
filter(year %in% c(1960, 2024)) %>%
summarise(
gap_1960 = gap[year == 1960],
gap_2024 = gap[year == 2024],
decrease_in_gap = gap_1960 - gap_2024
)
gap_change# A tibble: 1 × 3
gap_1960 gap_2024 decrease_in_gap
<dbl> <dbl> <dbl>
1 25.8 3.96 21.8Here I calculated how much the gap decreased, the life expectancy between the two countries narrowed by approximately 21.83 years.
Code
ggplot(
life_gap,
aes(
x = year,
y = gap
)
) +
geom_line(linewidth = 1) +
labs(
title = "Life Expectancy Gap Over Time",
subtitle = "United States compared with Bangladesh, 1960-2024",
x = "Year",
y = "Life Expectancy Gap (Years)"
) +
theme_minimal() +
theme(
plot.title = element_text(
size = 16,
face = "bold"
),
plot.subtitle = element_text(
size = 11
)
)
Each point along the line represents the difference between U.S. and Bangladesh life expectancy for that year. A declining line indicates that the difference between the two countries is becoming smaller.
Interpretation
The analysis shows that life expectancy increased in both Bangladesh and the United States between 1960 and 2024, but Bangladesh experienced a much larger improvement. In 1960, life expectancy was approximately 43.98 years in Bangladesh compared with 69.77 years in the United States, a gap of about 25.79 years. By 2024, life expectancy had increased to approximately 74.93 years in Bangladesh and 78.89 years in the United States. The gap decreased to approximately 3.96 years, meaning the difference between the two countries narrowed by about 21.83 years over the period. The results show that Bangladesh moved substantially closer to the United States in life expectancy.
Conclusion
This project demonstrated how three different wide-format datasets can be transformed into tidy formats using tidyr and dplyr and then used for analysis. The tuition data showed an overall increase in tuition over time, with Hawaii experiencing the largest increase among the states analyzed. The Census population data showed different population trends from 2020 to 2022, with Florida having the largest percentage increase and New York having the largest percentage decrease among the 10 states. Finally, the World Bank life expectancy data showed that although life expectancy increased in both Bangladesh and the United States, the gap between the two countries decreased substantially from about 25.79 years in 1960 to 3.96 years in 2024. Overall, tidying each dataset made it easier to calculate changes, compare groups, identify trends, and create clear visualizations.