# census_api_key("c0f2bf604441a9e7c5e1a1bdc792c8499eb68788", install = TRUE)Percent of Poeple 25+ With Less Than High School Education in Travis, Hays, & Williamson Counties by Census Tracts (2016, 2021, 2023)
# load all variable metadata for ACS 2023 5-year subject tables
#vars <- load_variables(2023, "acs5/subject", cache = TRUE)
#View(vars)# years to pull
years <- c(2016, 2021, 2023)
selected_vars <- c(
"S1501_C01_006", # pop 25 years and over
"S1501_C01_007", # less than 9th grade
"S1501_C01_008", # 9th-12th, no diploma
### by race/eth ###
#Sex by Educational Attainment for the Population 25 Years and Over (Black or African American Alone)
"C15002B_001", # Estimate!!Total:
"C15002B_003", # Estimate!!Total:!!Male:!!Less than high school diploma
"C15002B_008", # Estimate!!Total:!!Female:!!Less than high school diploma
# Sex by Educational Attainment for the Population 25 Years and Over (American Indian and Alaska Native Alone)
"C15002C_001", # Estimate!!Total:
"C15002C_003", # Estimate!!Total:!!Male:!!Less than high school diploma
"C15002C_008", # Estimate!!Total:!!Female:!!Less than high school diploma
# Sex by Educational Attainment for the Population 25 Years and Over (Asian Alone)
"C15002D_001", # Estimate!!Total:
"C15002D_003", # Estimate!!Total:!!Male:!!Less than high school diploma
"C15002D_008", # Estimate!!Total:!!Female:!!Less than high school diploma
# Sex by Educational Attainment for the Population 25 Years and Over (Native Hawaiian and Other Pacific Islander Alone)
"C15002E_001", # Estimate!!Total:
"C15002E_003", # Estimate!!Total:!!Male:!!Less than high school diploma
"C15002E_008", # Estimate!!Total:!!Female:!!Less than high school diploma
# Sex by Educational Attainment for the Population 25 Years and Over (Some Other Race Alone)
"C15002F_001", # Estimate!!Total:
"C15002F_003", # Estimate!!Total:!!Male:!!Less than high school diploma
"C15002F_008", # Estimate!!Total:!!Female:!!Less than high school diploma
# Sex by Educational Attainment for the Population 25 Years and Over (Two or More Races)
"C15002G_001", # Estimate!!Total:
"C15002G_003", # Estimate!!Total:!!Male:!!Less than high school diploma
"C15002G_008", # Estimate!!Total:!!Female:!!Less than high school diploma
# Sex by Educational Attainment for the Population 25 Years and Over (White Alone, Not Hispanic or Latino)
"C15002H_001", # Estimate!!Total:
"C15002H_003", # Estimate!!Total:!!Male:!!Less than high school diploma
"C15002H_008", # Estimate!!Total:!!Female:!!Less than high school diploma
# Sex by Educational Attainment for the Population 25 Years and Over (Hispanic or Latino)
"C15002I_001", # Estimate!!Total:
"C15002I_003", # Estimate!!Total:!!Male:!!Less than high school diploma
"C15002I_008" # Estimate!!Total:!!Female:!!Less than high school diploma
)
# define counties in Austin city
austin_counties <- c("Travis", "Williamson", "Hays")# Function to pull and process data for one year
get_data <- function(yr) {
data <- get_acs(
geography = "tract",
variables = selected_vars,
year = yr,
survey = "acs5",
output = "wide",
state = "TX",
county = austin_counties,
cache_table = TRUE
)
final_data <- data %>%
mutate(
est_lt9_n = S1501_C01_007E,
moe_lt9 = S1501_C01_007M,
est_9to12_nodip_n = S1501_C01_008E,
moe_9to12_nodip = S1501_C01_008M
) %>%
rowwise() %>% # ensure rowwise MOE math
mutate(
pop_25plus_n = S1501_C01_006E,
pop_25plus_moe = S1501_C01_006M,
# combined < high school (count + MOE)
lths_n = est_lt9_n + est_9to12_nodip_n,
lths_moe = moe_sum(
moe = c(moe_lt9, moe_9to12_nodip),
estimate = c(est_lt9_n, est_9to12_nodip_n),
na.rm = TRUE
),
# percent (<HS / 25+) and its MOE
lths_pct = lths_n / pop_25plus_n,
lths_pct_moe = moe_ratio(
num = lths_n,
denom = pop_25plus_n,
moe_num = lths_moe,
moe_denom = pop_25plus_moe
),
### percents by race/eth ###
# Black
black_lths_n = C15002B_003E + C15002B_008E,
black_lths_moe = moe_sum(
moe = c(C15002B_003M, C15002B_008M),
estimate = c(C15002B_003E, C15002B_008E),
na.rm = TRUE
),
black_lths_pct = black_lths_n / C15002B_001E,
black_lths_pct_moe = moe_ratio(
num = black_lths_n,
denom = C15002B_001E,
moe_num = black_lths_moe,
moe_denom = C15002B_001M
),
# AIAN
aian_lths_n = C15002C_003E + C15002C_008E,
aian_lths_moe = moe_sum(
moe = c(C15002C_003M, C15002C_008M),
estimate = c(C15002C_003E, C15002C_008E),
na.rm = TRUE
),
aian_lths_pct = aian_lths_n / C15002C_001E,
aian_lths_pct_moe = moe_ratio(
num = aian_lths_n,
denom = C15002C_001E,
moe_num = aian_lths_moe,
moe_denom = C15002C_001M
),
# Asian
asian_lths_n = C15002D_003E + C15002D_008E,
asian_lths_moe = moe_sum(
moe = c(C15002D_003M, C15002D_008M),
estimate = c(C15002D_003E, C15002D_008E),
na.rm = TRUE
),
asian_lths_pct = asian_lths_n / C15002D_001E,
asian_lths_pct_moe = moe_ratio(
num = asian_lths_n,
denom = C15002D_001E,
moe_num = asian_lths_moe,
moe_denom = C15002D_001M
),
# NHPI
nhpi_lths_n = C15002E_003E + C15002E_008E,
nhpi_lths_moe = moe_sum(
moe = c(C15002E_003M, C15002E_008M),
estimate = c(C15002E_003E, C15002E_008E),
na.rm = TRUE
),
nhpi_lths_pct = nhpi_lths_n / C15002E_001E,
nhpi_lths_pct_moe = moe_ratio(
num = nhpi_lths_n,
denom = C15002E_001E,
moe_num = nhpi_lths_moe,
moe_denom = C15002E_001M
),
# Other
other_lths_n = C15002F_003E + C15002F_008E,
other_lths_moe = moe_sum(
moe = c(C15002F_003M, C15002F_008M),
estimate = c(C15002F_003E, C15002F_008E),
na.rm = TRUE
),
other_lths_pct = other_lths_n / C15002F_001E,
other_lths_pct_moe = moe_ratio(
num = other_lths_n,
denom = C15002F_001E,
moe_num = other_lths_moe,
moe_denom = C15002F_001M
),
# two or more races
multi_lths_n = C15002G_003E + C15002G_008E,
multi_lths_moe = moe_sum(
moe = c(C15002G_003M, C15002G_008M),
estimate = c(C15002G_003E, C15002G_008E),
na.rm = TRUE
),
multi_lths_pct = multi_lths_n / C15002G_001E,
multi_lths_pct_moe = moe_ratio(
num = multi_lths_n,
denom = C15002G_001E,
moe_num = multi_lths_moe,
moe_denom = C15002G_001M
),
# NH white
nhwhite_lths_n = C15002H_003E + C15002H_008E,
nhwhite_lths_moe = moe_sum(
moe = c(C15002H_003M, C15002H_008M),
estimate = c(C15002H_003E, C15002H_008E),
na.rm = TRUE
),
nhwhite_lths_pct = nhwhite_lths_n / C15002H_001E,
nhwhite_lths_pct_moe = moe_ratio(
num = nhwhite_lths_n,
denom = C15002H_001E,
moe_num = nhwhite_lths_moe,
moe_denom = C15002H_001M
),
# Hispanic
hisp_lths_n = C15002I_003E + C15002I_008E,
hisp_lths_moe = moe_sum(
moe = c(C15002I_003M, C15002I_008M),
estimate = c(C15002I_003E, C15002I_008E),
na.rm = TRUE
),
hisp_lths_pct = hisp_lths_n / C15002I_001E,
hisp_lths_pct_moe = moe_ratio(
num = hisp_lths_n,
denom = C15002I_001E,
moe_num = hisp_lths_moe,
moe_denom = C15002I_001M
)
) %>%
ungroup() %>%
select(
GEOID, NAME,
lths_n, lths_moe,
lths_pct, lths_pct_moe,
black_lths_pct, black_lths_pct_moe,
aian_lths_pct, aian_lths_pct_moe,
asian_lths_pct, asian_lths_pct_moe,
nhpi_lths_pct, nhpi_lths_pct_moe,
other_lths_pct, other_lths_pct_moe,
multi_lths_pct, multi_lths_pct_moe,
nhwhite_lths_pct, nhwhite_lths_pct_moe,
hisp_lths_pct, hisp_lths_pct_moe
)
}# Run for all years and combine
final_data <- bind_rows(lapply(years, get_data))Getting data from the 2012-2016 5-year ACSFetching data by table type ("B/C", "S", "DP") and combining the result.Getting data from the 2017-2021 5-year ACSFetching data by table type ("B/C", "S", "DP") and combining the result.Getting data from the 2019-2023 5-year ACSFetching data by table type ("B/C", "S", "DP") and combining the result.# preview first few rows
head(final_data)# A tibble: 6 × 22
GEOID NAME lths_n lths_moe lths_pct lths_pct_moe black_lths_pct
<chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 48209010100 Census Tract… 44 34.9 0.0260 0.0211 0.0690
2 48209010200 Census Tract… 18 19.7 0.0316 0.0353 0
3 48209010302 Census Tract… 524 215. 0.193 0.0841 0
4 48209010303 Census Tract… 374 184. 0.116 0.0592 0.158
5 48209010304 Census Tract… 443 182. 0.170 0.0744 0
6 48209010400 Census Tract… 993 289. 0.171 0.0521 0.0311
# ℹ 15 more variables: black_lths_pct_moe <dbl>, aian_lths_pct <dbl>,
# aian_lths_pct_moe <dbl>, asian_lths_pct <dbl>, asian_lths_pct_moe <dbl>,
# nhpi_lths_pct <dbl>, nhpi_lths_pct_moe <dbl>, other_lths_pct <dbl>,
# other_lths_pct_moe <dbl>, multi_lths_pct <dbl>, multi_lths_pct_moe <dbl>,
# nhwhite_lths_pct <dbl>, nhwhite_lths_pct_moe <dbl>, hisp_lths_pct <dbl>,
# hisp_lths_pct_moe <dbl># view full data
View(final_data)# save to excel
#run for all years, keep as a named list (one df per year)
data_by_year <- setNames(
lapply(years, get_data),
paste0(years) # sheet names
)Getting data from the 2012-2016 5-year ACSFetching data by table type ("B/C", "S", "DP") and combining the result.Getting data from the 2017-2021 5-year ACSFetching data by table type ("B/C", "S", "DP") and combining the result.Getting data from the 2019-2023 5-year ACSFetching data by table type ("B/C", "S", "DP") and combining the result.# Write to Excel with each year as its own tab
write_xlsx(
data_by_year,
"LessThanHS_RE_acs5yr_travis_hays_williamson_2016_2021_2023.xlsx"
)