library(tidyverse)
library(tidyr)
library(dplyr)
library(ggplot2)
library(tibble)Project 2 — Data Tidying
Introduction
Project Overview
For this project, I plan to work with three independent education-related datasets that can be linked together using the state as a common identifier. The datasets will focus on high school graduation rates, state-level SAT scores, and state-level public education funding. Although each dataset will be analyzed separately as required by the assignment, they all relate to educational outcomes and provide an opportunity to explore broader relationships between academic performance, graduation rates, and school funding.
The goal of this project is to demonstrate how to transform wide-format data into tidy data using the tidyr and dplyr packages in R. After tidying each dataset, I will perform exploratory analyses, create summary tables, and develop visualizations that highlight patterns within each dataset. Finally, I will combine the tidy datasets to investigate whether states with higher education funding also tend to have higher SAT scores and graduation rates.
Proposed Approach
Dataset 1: High School Graduation Rates
The first dataset will contain high school graduation rates by state. Since educational datasets are often stored in wide format, with years as separate columns, I expect to use pivot_longer() to transform the data into a tidy structure, where each row represents a state-year observation.After tidying the data, I will create summary tables showing graduation rates by state and identify the states with the highest graduation rates. I will also develop visualizations, such as bar charts, to compare graduation rates across states and highlight the highest-performing states.
Dataset 2: SAT Scores by State
The second dataset will contain SAT scores by state. Similar to the graduation dataset, I expect the data may require reshaping and standardizing variable names before analysis.Once the data has been tidied, I will calculate summary statistics and identify which states have the highest average SAT scores. I will then create visualizations to compare SAT performance across states and identify notable patterns or trends.
Dataset 3: Public Education Funding
The third dataset will focus on public education funding by state, including per-pupil expenditures and total education spending. This dataset may contain multiple funding categories that will need to be transformed into a tidy format. After cleaning and restructuring the data, I will create summary tables showing the states with the highest education spending and generate visualizations that compare funding levels across states.
Combined Analysis
After completing the required analysis for each dataset, I plan to merge the three tidy datasets using the state as the common key. This additional analysis will allow me to explore potential relationships among education funding, SAT scores, and graduation rates.
Specifically, I will investigate questions such as:
Do states with higher education funding tend to have higher SAT scores?
Do states with higher graduation rates also have higher SAT scores?
Is there a relationship between education spending and graduation rates?.
Expected Challenges
One challenge will be ensuring that all three datasets use a consistent state identifier. Some datasets may use full state names while others may use abbreviations, requiring additional data cleaning before the datasets can be merged.
.
Running Code
Dataset 1: High School Graduation Rates
The original graduation-rate dataset was stored in a wide format where states appeared as column names and graduation years appeared as rows. This structure made analysis difficult because years were stored as observations rather than variables. To create a tidy dataset, the data was first transposed so that each state became a row and each graduation year became a column. The dataset was then reshaped into a long format using pivot_longer(), producing one row per state-year combination.
library(readr)
HS_grad_rate <- read_csv("https://raw.githubusercontent.com/lioneljr17/LDATA607/refs/heads/main/Project2/HS_grad_rate.csv")
#View(HS_grad_rate)
dim(HS_grad_rate)[1] 5 53names(HS_grad_rate) [1] "state" "Vermont" "Maine"
[4] "Montana" "New Hampshire" "Wyoming"
[7] "North Dakota" "Minnesota" "Alaska"
[10] "Wisconsin" "Utah" "Iowa"
[13] "Hawaii" "South Dakota" "Colorado"
[16] "District of Columbia" "Washington" "Nebraska"
[19] "Michigan" "Pennsylvania" "Kansas"
[22] "Idaho" "Missouri" "Ohio"
[25] "Oregon" "Delaware" "Virginia"
[28] "Connecticut" "Massachusetts" "Maryland"
[31] "New Jersey" "Illinois" "Indiana"
[34] "North Carolina" "South Carolina" "Tennessee"
[37] "Florida" "Rhode Island" "Arizona"
[40] "Oklahoma" "West Virginia" "Georgia"
[43] "Kentucky" "Arkansas" "Alabama"
[46] "New Mexico" "New York" "Nevada"
[49] "Louisiana" "Mississippi" "Texas"
[52] "California" "United States" head(HS_grad_rate)# A tibble: 5 × 53
state Vermont Maine Montana `New Hampshire` Wyoming `North Dakota` Minnesota
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 HighSc… 94.9 94.8 94.6 94.4 94.2 94 93.9
2 HighSc… 94.5 94.5 94.6 94.1 94.1 93.8 93.9
3 HighSc… 94.2 94.1 94.5 93.8 93.9 93.5 93.7
4 HighSc… 93.9 93.7 94.4 93.6 93.7 93.3 93.6
5 HighSc… 93.5 93.2 94 93.3 93.6 93.1 93.4
# ℹ 45 more variables: Alaska <dbl>, Wisconsin <dbl>, Utah <dbl>, Iowa <dbl>,
# Hawaii <dbl>, `South Dakota` <dbl>, Colorado <dbl>,
# `District of Columbia` <dbl>, Washington <dbl>, Nebraska <dbl>,
# Michigan <dbl>, Pennsylvania <dbl>, Kansas <dbl>, Idaho <dbl>,
# Missouri <dbl>, Ohio <dbl>, Oregon <dbl>, Delaware <dbl>, Virginia <dbl>,
# Connecticut <dbl>, Massachusetts <dbl>, Maryland <dbl>, `New Jersey` <dbl>,
# Illinois <dbl>, Indiana <dbl>, `North Carolina` <dbl>, …grad_clean <- HS_grad_rate %>%
column_to_rownames("state") %>%
t() %>%
as.data.frame() %>%
rownames_to_column("state")
names(grad_clean) <- c(
"state",
"highschool_2024",
"highschool_2023",
"highschool_2022",
"highschool_2021",
"highschool_2020"
)
grad_tidy <- grad_clean %>%
pivot_longer(
starts_with("highschool_"),
names_to = "year",
values_to = "graduation_rate"
) %>%
mutate(
year = gsub("highschool_", "", year),
graduation_rate = as.numeric(graduation_rate)
)top5_grad <- grad_clean %>%
arrange(desc(highschool_2024)) %>%
slice_head(n = 5)
top5_grad state highschool_2024 highschool_2023 highschool_2022 highschool_2021
1 Vermont 94.9 94.5 94.2 93.9
2 Maine 94.8 94.5 94.1 93.7
3 Montana 94.6 94.6 94.5 94.4
4 New Hampshire 94.4 94.1 93.8 93.6
5 Wyoming 94.2 94.1 93.9 93.7
highschool_2020
1 93.5
2 93.2
3 94.0
4 93.3
5 93.6bottom5_grad <- grad_clean %>%
arrange(highschool_2024) %>%
slice_head(n = 5)
bottom5_grad state highschool_2024 highschool_2023 highschool_2022 highschool_2021
1 California 84.7 84.6 84.4 84.2
2 Texas 86.0 85.7 85.2 84.8
3 Mississippi 87.1 86.6 86.2 85.6
4 Louisiana 87.3 86.9 86.7 86.2
5 Nevada 87.5 87.4 87.1 87.0
highschool_2020
1 83.9
2 84.4
3 85.3
4 85.9
5 86.9grad_clean %>%
arrange(desc(highschool_2024)) %>%
slice_head(n = 10) state highschool_2024 highschool_2023 highschool_2022
1 Vermont 94.9 94.5 94.2
2 Maine 94.8 94.5 94.1
3 Montana 94.6 94.6 94.5
4 New Hampshire 94.4 94.1 93.8
5 Wyoming 94.2 94.1 93.9
6 North Dakota 94.0 93.8 93.5
7 Minnesota 93.9 93.9 93.7
8 Alaska 93.6 93.5 93.5
9 Wisconsin 93.6 93.4 93.1
10 Utah 93.4 93.3 93.2
highschool_2021 highschool_2020
1 93.9 93.5
2 93.7 93.2
3 94.4 94.0
4 93.6 93.3
5 93.7 93.6
6 93.3 93.1
7 93.6 93.4
8 93.3 93.1
9 92.9 92.6
10 93.1 93.0 grad_clean %>%
arrange(desc(highschool_2024)) %>%
slice_head(n = 10) %>%
ggplot(
aes(
reorder(state, highschool_2024),
highschool_2024
)
) +
geom_col(fill = "steelblue") +
coord_flip()
grad_chart <- bind_rows(
top5_grad %>% mutate(group = "Top 5"),
bottom5_grad %>% mutate(group = "Bottom 5")
)
ggplot(
grad_chart,
aes(
reorder(state, highschool_2024),
highschool_2024,
fill = group
)
) +
geom_col() +
coord_flip() +
labs(
title = "Top and Bottom 5 States by Graduation Rate (2024)",
x = "State",
y = "Graduation Rate (%)"
)
Dataset 2: SAT Scores
The SAT dataset was also provided in a wide format where states were stored as columns and SAT measures were stored as rows. The dataset was transposed so that each state became an observation. The resulting dataset was then converted into a tidy format where SAT metrics and values were represented consistently.
library(readr)
SAT_score <- read_csv("https://raw.githubusercontent.com/lioneljr17/LDATA607/refs/heads/main/Project2/SAT_score.csv")
#View(SAT_score)
head(SAT_score)# A tibble: 4 × 54
State Alabama Alaska Arizona Arkansas California Colorado Connecticut Delaware
<chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 SAT … 3% 27% 10% 2% 26% 90% 91% 94%
2 Tota… 1172 1097 1194 1177 1096 987 989 933
3 Mean… 601 567 606 609 555 505 505 480
4 Mean… 570 530 588 568 541 482 484 453
# ℹ 45 more variables: `District of Columbia` <chr>, Florida <chr>,
# Georgia <chr>, Hawaii <chr>, Idaho <chr>, Illinois <chr>, Indiana <chr>,
# Iowa <chr>, Kansas <chr>, Kentucky <chr>, Louisiana <chr>, Maine <chr>,
# Maryland <chr>, Massachusetts <chr>, Michigan <chr>, Minnesota <chr>,
# Mississippi <chr>, Missouri <chr>, Montana <chr>, Nebraska <chr>,
# Nevada <chr>, `New Hampshire` <chr>, `New Jersey` <chr>,
# `New Mexico` <chr>, `New York` <chr>, `North Carolina` <chr>, …sat_clean <- SAT_score %>%
column_to_rownames("State") %>%
t() %>%
as.data.frame() %>%
rownames_to_column("state")
names(sat_clean) <- c(
"state",
"participation_rate",
"total_mean_score",
"reading_score",
"math_score"
)
head(sat_clean) state participation_rate total_mean_score reading_score math_score
1 Alabama 3% 1172 601 570
2 Alaska 27% 1097 567 530
3 Arizona 10% 1194 606 588
4 Arkansas 2% 1177 609 568
5 California 26% 1096 555 541
6 Colorado 90% 987 505 482sat_clean <- sat_clean %>%
mutate(
participation_rate =
as.numeric(gsub("%","",na_if(participation_rate,"N/A"))),
total_mean_score =
as.numeric(total_mean_score),
reading_score =
as.numeric(reading_score),
math_score =
as.numeric(math_score)
)
sat_tidy <- sat_clean %>%
pivot_longer(
-state,
names_to = "metric",
values_to = "value"
)top5_sat <- sat_clean %>%
arrange(desc(total_mean_score)) %>%
slice_head(n = 5)
top5_sat state participation_rate total_mean_score reading_score math_score
1 North Dakota 1 1254 631 623
2 Nebraska 1 1249 630 619
3 Wisconsin 2 1240 624 616
4 Kansas 2 1238 626 612
5 Wyoming 1 1234 638 597bottom5_sat <- sat_clean %>%
arrange(total_mean_score) %>%
slice_head(n = 5)
sat_chart <- bind_rows(
top5_sat %>% mutate(group = "Top 5"),
bottom5_sat %>% mutate(group = "Bottom 5")
)
bottom5_sat state participation_rate total_mean_score reading_score math_score
1 New Mexico 100 875 451 424
2 West Virginia 93 911 474 437
3 Delaware 94 933 480 453
4 Rhode Island 99 935 481 455
5 Indiana 100 950 485 464sat_clean %>%
arrange(desc(total_mean_score)) %>%
slice_head(n = 10) state participation_rate total_mean_score reading_score math_score
1 North Dakota 1 1254 631 623
2 Nebraska 1 1249 630 619
3 Wisconsin 2 1240 624 616
4 Kansas 2 1238 626 612
5 Wyoming 1 1234 638 597
6 Utah 2 1229 622 606
7 Mississippi 1 1223 624 598
8 South Dakota 2 1214 619 595
9 Iowa 2 1211 616 595
10 Minnesota 3 1210 612 698sat_clean %>%
arrange(desc(total_mean_score)) %>%
slice_head(n = 10) %>%
ggplot(
aes(
reorder(state,total_mean_score),
total_mean_score
)
) +
geom_col(fill = "darkgreen") +
coord_flip()
ggplot(
sat_chart,
aes(
reorder(state, total_mean_score),
total_mean_score,
fill = group
)
) +
geom_col() +
coord_flip() +
labs(
title = "Top and Bottom 5 States by SAT Score",
x = "State",
y = "Average SAT Score"
)
Dataset 3: School Funding
The education funding dataset contained states as columns and funding categories as rows. Similar to the previous datasets, the data was transposed so that states became observations. The funding measures were then standardized and converted to numeric values after removing currency symbols.
library(readr)
states_school_funding <- read_csv("https://raw.githubusercontent.com/lioneljr17/LDATA607/refs/heads/main/Project2/states_school_funding.csv")
#View(states_school_funding)
head(states_school_funding)# A tibble: 2 × 52
State Alabama Alaska Arizona Arkansas California Colorado Connecticut Delaware
<chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 Tota… $2,866… $426.… $6,716… $1,467.… $19,701.13 $3,092.… $2,180.38 $503.58
2 Dire… $1,526… $60.31 $3,844… $686.77 $8,854.55 $1,806.… $1,244.52 $248.95
# ℹ 43 more variables: `District of Columbia` <chr>, Florida <chr>,
# Georgia <chr>, Hawaii <chr>, Idaho <chr>, Illinois <chr>, Indiana <chr>,
# Iowa <chr>, Kansas <chr>, Kentucky <chr>, Louisiana <chr>, Maine <chr>,
# Maryland <chr>, Massachusetts <chr>, Michigan <chr>, Minnesota <chr>,
# Mississippi <chr>, Missouri <chr>, Montana <chr>, Nebraska <chr>,
# Nevada <chr>, `New Hampshire` <chr>, `New Jersey` <chr>,
# `New Mexico` <chr>, `New York` <chr>, `North Carolina` <chr>, …school_funding_clean <- states_school_funding %>%
column_to_rownames("State") %>%
t() %>%
as.data.frame() %>%
rownames_to_column("state")
names(school_funding_clean) <- c(
"state",
"total_budget",
"direct_student_loans"
)
school_funding_clean <- school_funding_clean %>%
mutate(
total_budget =
as.numeric(gsub("[$,]","",total_budget)),
direct_student_loans =
as.numeric(gsub("[$,]","",direct_student_loans))
)
funding_tidy <- school_funding_clean %>%
pivot_longer(
-state,
names_to = "funding_type",
values_to = "amount"
)top5_funding <- school_funding_clean %>%
arrange(desc(total_budget)) %>%
slice_head(n = 5)
top5_funding state total_budget direct_student_loans
1 California 19701.13 8854.55
2 Texas 13509.50 5213.89
3 New York 12087.25 6463.10
4 Florida 10104.82 4836.90
5 Pennsylvania 7552.27 4698.04bottom5_funding <- school_funding_clean %>%
arrange(total_budget) %>%
slice_head(n = 5)
bottom5_funding state total_budget direct_student_loans
1 Wyoming 281.36 77.06
2 Vermont 417.89 222.86
3 Alaska 426.36 60.31
4 North Dakota 451.90 202.73
5 Hawaii 500.48 193.92funding_chart <- bind_rows(
top5_funding %>% mutate(group = "Top 5"),
bottom5_funding %>% mutate(group = "Bottom 5")
)school_funding_clean %>%
arrange(desc(total_budget)) %>%
slice_head(n = 10) state total_budget direct_student_loans
1 California 19701.13 8854.55
2 Texas 13509.50 5213.89
3 New York 12087.25 6463.10
4 Florida 10104.82 4836.90
5 Pennsylvania 7552.27 4698.04
6 Illinois 7514.58 4036.74
7 Arizona 6716.04 3844.44
8 Ohio 5563.95 2898.07
9 Georgia 5405.03 2599.57
10 Virginia 4962.27 3022.01school_funding_clean %>%
arrange(desc(total_budget)) %>%
slice_head(n = 10) %>%
ggplot(
aes(
reorder(state,total_budget),
total_budget
)
) +
geom_col(fill = "orange") +
coord_flip()
ggplot(
funding_chart,
aes(
reorder(state, total_budget),
total_budget,
fill = group
)
) +
geom_col() +
coord_flip() +
labs(
title = "Top and Bottom 5 States by Education Funding",
x = "State",
y = "Funding (Millions)"
)
Final Combined Analysis
combined_data <- grad_clean %>%
select(
state,
highschool_2024
) %>%
inner_join(
sat_clean %>%
select(
state,
total_mean_score
),
by = "state"
) %>%
inner_join(
school_funding_clean %>%
select(
state,
total_budget
),
by = "state"
)combined_data %>%
arrange(desc(total_mean_score)) state highschool_2024 total_mean_score total_budget
1 North Dakota 94.0 1254 451.90
2 Nebraska 92.2 1249 1160.46
3 Wisconsin 93.6 1240 2394.88
4 Kansas 92.0 1238 1452.06
5 Wyoming 94.2 1234 281.36
6 Utah 93.4 1229 2728.52
7 Mississippi 87.1 1223 1693.30
8 South Dakota 93.2 1214 533.42
9 Iowa 93.3 1211 1703.16
10 Minnesota 93.9 1210 3948.58
11 Montana 94.6 1205 505.06
12 Louisiana 87.3 1195 2914.63
13 Arizona 89.4 1194 6716.04
14 Tennessee 90.0 1189 3595.99
15 Kentucky 88.9 1181 2609.96
16 Arkansas 88.7 1177 1467.94
17 Nevada 87.5 1177 1001.79
18 Missouri 91.9 1174 3093.75
19 Alabama 88.6 1172 2866.48
20 North Carolina 90.1 1165 4720.18
21 Oklahoma 89.4 1150 2017.39
22 Oregon 91.8 1127 1855.19
23 Hawaii 93.2 1125 500.48
24 Vermont 94.9 1115 417.89
25 Massachusetts 91.4 1114 4107.66
26 Virginia 91.6 1112 4962.27
27 Alaska 93.6 1097 426.36
28 California 84.7 1096 19701.13
29 Washington 92.3 1095 2565.71
30 Pennsylvania 92.1 1084 7552.27
31 Maine 94.8 1078 737.69
32 New Jersey 90.7 1061 3817.12
33 New York 88.0 1045 12087.25
34 Georgia 89.2 1038 5405.03
35 Ohio 91.8 1018 5563.95
36 New Hampshire 94.4 1017 1951.70
37 South Carolina 90.1 1017 2419.65
38 Idaho 91.9 1003 730.48
39 Maryland 91.1 1003 2708.83
40 Connecticut 91.5 989 2180.38
41 Colorado 92.9 987 3092.33
42 Florida 89.9 970 10104.82
43 Texas 86.0 964 13509.50
44 Illinois 90.5 961 7514.58
45 District of Columbia 92.7 958 1888.82
46 Michigan 92.1 953 4775.90
47 Indiana 90.4 950 3497.89
48 Rhode Island 89.7 935 746.06
49 Delaware 91.7 933 503.58
50 West Virginia 89.3 911 1110.80
51 New Mexico 88.0 875 933.79 combined_data %>%
select(
highschool_2024,
total_mean_score,
total_budget
) %>%
cor(
use = "complete.obs"
) highschool_2024 total_mean_score total_budget
highschool_2024 1.0000000 0.1966690 -0.5515099
total_mean_score 0.1966690 1.0000000 -0.2010645
total_budget -0.5515099 -0.2010645 1.0000000ggplot(
combined_data,
aes(
total_budget,
total_mean_score
)
) +
geom_point(
color = "darkgreen",
size = 3
) +
geom_smooth(
method = "lm",
se = FALSE,
color = "black"
) +
labs(
title = "Education Funding vs SAT Scores",
x = "Total Budget",
y = "SAT Score"
)
ggplot(
combined_data,
aes(
total_budget,
highschool_2024
)
) +
geom_point(
color = "blue",
size = 3
) +
geom_smooth(
method = "lm",
se = FALSE
) +
labs(
title = "Education Funding vs Graduation Rate",
x = "Total Budget",
y = "Graduation Rate"
)
ggplot(
combined_data,
aes(
total_mean_score,
highschool_2024
)
) +
geom_point(
color = "red",
size = 3
) +
geom_smooth(
method = "lm",
se = FALSE
) +
labs(
title = "SAT Score vs Graduation Rate",
x = "SAT Score",
y = "Graduation Rate"
)
combined_scaled <- combined_data %>%
mutate(
graduation_scaled =
scale(highschool_2024)[,1],
sat_scaled =
scale(total_mean_score)[,1],
funding_scaled =
scale(total_budget)[,1]
)
combined_scaled_long <- combined_scaled %>%
select(
state,
graduation_scaled,
sat_scaled,
funding_scaled
) %>%
pivot_longer(
-state,
names_to = "metric",
values_to = "value"
)
ggplot(
combined_scaled_long,
aes(
state,
value,
fill = metric
)
) +
geom_col(position = "dodge") +
theme(
axis.text.x =
element_text(
angle = 90,
hjust = 1
)
) +
labs(
title = "Standardized Comparison of Graduation Rates, SAT Scores, and Education Funding",
x = "State",
y = "Standardized Value"
)
After analyzing each dataset individually, the three datasets were combined to explore relationships among education funding, SAT performance, and graduation rates. Correlation analysis and visualizations suggest that states with higher educational investment often exhibit stronger educational outcomes. However, the relationship is not perfect, indicating that additional factors beyond funding contribute to student success. The combined analysis demonstrates how tidy datasets can be integrated to answer broader questions that would be difficult to investigate with a single dataset.
Conclusion
The independent analyses identified states with the highest graduation rates, SAT scores, and education funding levels. After combining the datasets, the results suggest that some relationship may exist between educational investment and academic outcomes. However, the strength of these relationships varies across states, suggesting that funding alone may not fully account for differences in graduation rates and SAT performance. Additional factors such as demographics, participation rates, and educational policies likely contribute to student outcomes.