Enrollment planning and trends are hot topics in public education in Tennessee, especially in Rutherford County Schools where I serve as Chief Communications Officer. As such, I wanted to pursue a reseaarch project that examines enrollment trends over the past 15 years.
This project will examine whether Tennessee’s public school enrollment has kept pace with population growth and whether enrollment trends are associated with changes in news coverage of school-choice options. It will also investigate whether interstate migration brings Tennessee residents from states with greater school-choice participation or availability.
The study will address four reseach questions:
| Source | What the project uses |
|---|---|
| Tennessee Department of Education district profile workbooks | District IDs, names, school years, enrollment counts, and published statewide totals when available. The graphics read a prepared district-year CSV and consult the original annual workbooks for statewide totals. |
| U.S. Census Bureau American Community Survey (ACS), one-year estimates | Tennessee’s population ages 5–17 from table B01001 and private-school and homeschool enrollment ages 5–17 as grouped in the report’s use of table B14003. A separate prepared state-year ACS file supplies public and private/home enrollment counts for the migration comparison. |
| U.S. Census Bureau state-to-state migration tables | A prepared CSV of estimates of people arriving in Tennessee from the other 49 states and the District of Columbia. Where needed and available, the code attempts to retrieve a published Tennessee total from the supplemental workbook. |
| U.S. Census Bureau cartographic boundary files | Tennessee’s 95 county boundaries from the 2024 release, accessed
through the R package tigris. The map uses this same
geography across school years. |
| Media Cloud | Counts from Tennessee and U.S. national online news collections. The national search adds a Tennessee term to the topic query. The report tracks both the matching counts and the API’s total indexed-story counts. |
Part 1 is an interactive HTML report with four visuals: a county enrollment map paired with a top-ten district ranking, a statewide trend with indexed and raw-count views, an interstate arrivals comparison with a shaded range for missing data, and a two-panel news coverage chart with rate, count, and archive-size views. The code also writes a Media Cloud counts CSV and caches successful API responses. If Tennessee ACS estimates have to be fetched, it saves a local copy for later runs.
I defined the Tennessee research questions, chose the sources I wanted to compare, and decided which views readers should see. I ran and reviewed the report in RStudio. For example, I spotted crowded chart labels and a dropdown that overlapped a chart title and asked for changes after seeing the rendered output. I used AI to help write and revise the R code that imports and checks the data, calculates the comparisons, and builds the interactive graphics. I also used AI to help describe how the code works for this document.
enrollment_graphics.R. The script checks for its R
packages and attempts to install any missing ones. The report then calls
four functions, passing its configured CSV paths, migration lag, cache
directory, and Media Cloud collection IDs.build_enrollment_report() reads the enrollment CSV with
district IDs stored as text, checks the required fields, rejects
duplicate district-year keys and negative counts, and loads the 95
county polygons. It assigns local LEAs to counties using blocks of ten
in the numeric district IDs and checks the mapping against recognizable
county district names. Statewide and special agencies without a local
assignment stay out of county totals.read_statewide_totals() reads each annual workbook and
prefers its published statewide count. If the statewide count is
unavailable, it sums the district records only when all counts in that
year’s CSV rows are finite and labels the method.
read_tn_acs() looks for a compatible local ACS extract
before using CENSUS_API_KEY to fetch data. It combines
age-group estimates and approximates their 90% margins of error using
the square root of the sum of squared component margins. The 2020 ACS
values are left missing. build_statewide_trend() divides
each series by its own first-year value and multiplies by 100, then
offers a toggle back to counts. It also reports absolute and percentage
changes between the endpoints.calculate_migration_choice() computes a
state’s private/home share as private/home enrollment divided by public
plus private/home enrollment for ages 5–17. It compares each origin with
Tennessee using the previous year’s rate by default, or the same year’s
rate with lag zero. It sums arrivals from higher-rate origins and
divides by the number of arrivals it could classify for the plotted
line. When a total is available, the lower bound divides the known
higher-rate arrivals by all arrivals. The upper bound adds all
unclassified arrivals to the numerator. These bounds reflect missing
information. They are separate from sampling uncertainty. The results
describe where people lived before moving and cannot establish why they
moved or which schools their children attended. The hover text also
flags a Census caution for 2022.media_topics() defines voucher/ESA, charter, homeschool,
and general school-choice queries and creates a combined OR query so
overlapping topic counts are not added together.
collect_media_counts() checks the collection names and
retrieves results for July 1 through June 30 of each school year. It
stores responses and query provenance locally, throttles and retries
uncached requests, and calculates
1,000 × matching stories / indexed stories when the
denominator is positive. build_media_coverage() draws the
Tennessee and national panels and adds a dropdown for rates, matching
counts, and archive size. The chart measures coverage in the selected
indexed collections, and results depend on archive coverage and search
wording.The appendix includes the complete graphics script, the report setup, and all four calls that display the visuals. Each step explains one operation and displays the exact lines from the supplied script. Some steps continue a function started in the preceding block. To rebuild Part 1, use its original parameter values and accompanying data files. The supplied code starts from prepared enrollment, migration, and state ACS inputs. Earlier scripts that produced those inputs were outside the supplied appendix. The appendix below is displayed as source so this methods document can knit without running the graphics, querying APIs, or reading the project datasets.
Jump to a code step
Lists the R libraries the report needs. Later code accesses their
functions using package-qualified names such as
leaflet::leaflet().
packages <- c("httr2", "jsonlite", "rstudioapi", "plotly", "tidycensus", "readxl", "ggplot2", "sf", "tigris", "leaflet", "htmlwidgets",
"htmltools", "base64enc", "scales")
============================================================
Compares the package list with what is installed and installs any missing dependencies.
missing <- packages[!vapply(packages, requireNamespace, logical(1), quietly = TRUE)]
if (length(missing)) install.packages(missing, repos = "https://cloud.r-project.org")
============================================================
Validates the enrollment CSV, assigns local district IDs to counties, and builds a Leaflet map. The school-year selector updates the county totals, popups, and top-ten district chart together.
build_enrollment_report <- function(enrollment_csv) {
packages <- c("httr2", "jsonlite", "rstudioapi", "plotly", "tidycensus", "readxl", "ggplot2", "sf", "tigris", "leaflet", "htmlwidgets",
"htmltools", "base64enc", "scales")
missing <- packages[!vapply(packages, requireNamespace, logical(1), quietly = TRUE)]
if (length(missing)) stop("Install these packages: ", paste(missing, collapse = ", "))
csv <- path.expand(enrollment_csv)
if (!file.exists(csv)) stop("Enrollment CSV not found: ", csv)
d <- read.csv(csv, colClasses = c(district_id = "character"))
needed <- c("year", "school_year", "district_id", "district", "profile_enrollment")
if (!all(needed %in% names(d))) stop("The CSV is missing required columns.")
if (anyNA(d[c("year", "school_year", "district_id", "district")]) ||
anyDuplicated(d[c("year", "district_id")])) stop("Check district-year identifiers.")
if (!is.numeric(d$profile_enrollment) ||
any(d$profile_enrollment < 0, na.rm = TRUE)) stop("Invalid enrollment counts.")
============================================================
Downloads or reuses the Tennessee county geometry, converts it to latitude and longitude, and checks that it contains 95 counties.
options(tigris_use_cache = TRUE)
counties <- tigris::counties("TN", cb = TRUE, year = 2024, progress_bar = FALSE)
counties <- sf::st_transform(counties, 4326)
counties <- counties[order(counties$NAME), ]
stopifnot(nrow(counties) == 95L)
============================================================
Uses district ID blocks of ten to assign counties, then checks the assignments against recognizable county district names.
# Local LEA numbers use county blocks of ten.
county_names <- sort(counties$NAME)
county_names[54:60] <- c("McMinn", "McNairy", "Macon", "Madison",
"Marion", "Marshall", "Maury")
id <- suppressWarnings(as.integer(d$district_id))
local <- !is.na(id) & id >= 10L & id <= 959L
d$county <- NA_character_
d$county[local] <- county_names[id[local] %/% 10L]
normalized <- gsub("[^a-z]", "", tolower(d$district))
for (county in county_names) {
key <- gsub("[^a-z]", "", tolower(county))
named <- normalized %in% paste0(key, c("county", "countyschools", "countypublicschools"))
if (any(named & (is.na(d$county) | d$county != county), na.rm = TRUE)) {
stop("Review county assignment for ", county)
}
}
============================================================
Defines formatting, HTML escaping, and fixed color bins so the same enrollment range uses the same color across years.
fmt <- scales::label_comma()
esc <- function(x) as.character(htmltools::htmlEscape(x))
years <- sort(unique(d$year))
colors <- c("#ffffcc", "#c7e9b4", "#7fcdbb", "#41b6c4", "#1d91c0", "#225ea8", "#0c2c84")
pal <- leaflet::colorBin(colors, domain = c(0, 200000),
bins = c(0, 2500, 5000, 10000, 25000, 50000, 100000, Inf),
right = FALSE, na.color = "#bdbdbd")
============================================================
Ranks the reported district counts and calculates county totals when all available local district records have usable counts. It also prepares map labels and popups.
year_data <- lapply(years, function(year) {
records <- d[d$year == year, ]
ranked <- records[is.finite(records$profile_enrollment), ]
ranked <- ranked[order(-ranked$profile_enrollment, ranked$district), ]
if (!nrow(ranked)) stop("No reported enrollment for ", year)
totals <- rep(NA_real_, nrow(counties))
popups <- labels <- character(nrow(counties))
for (i in seq_len(nrow(counties))) {
county <- counties$NAME[i]
rows <- records[which(records$county == county), ]
complete <- nrow(rows) > 0 && all(is.finite(rows$profile_enrollment))
if (complete) totals[i] <- sum(rows$profile_enrollment)
value <- if (complete) fmt(totals[i]) else "Incomplete or unavailable"
labels[i] <- paste0(county, " County: ", value)
details <- paste0(esc(rows$district), ": ",
ifelse(is.finite(rows$profile_enrollment), fmt(rows$profile_enrollment), "Missing"))
popups[i] <- paste0("<strong>", esc(county), " County</strong><br>",
esc(records$school_year[1]), "<br>Local district total: ", value,
"<br><br>", paste(details, collapse = "<br>"))
}
============================================================
Builds a labeled horizontal bar chart for the selected year, converts the temporary PNG to an embedded image, and packages it with that year’s map data.
top <- head(ranked, 10)
home <- ifelse(is.na(top$county), "Statewide or special agency", paste0(top$county, " County"))
text <- paste0(top$district, " (", home, ")")
top$chart_label <- factor(text, levels = rev(text))
bars <- ggplot2::ggplot(top, ggplot2::aes(profile_enrollment, chart_label)) +
ggplot2::geom_col(fill = "#17657b", width = .72) +
ggplot2::geom_text(ggplot2::aes(label = fmt(profile_enrollment)), hjust = -.1, size = 3.2) +
ggplot2::scale_x_continuous(labels = fmt, expand = ggplot2::expansion(mult = c(0, .2))) +
ggplot2::labs(title = paste("Ten largest districts", records$school_year[1]),
x = "Reported students enrolled", y = NULL) +
ggplot2::theme_minimal(base_size = 11) +
ggplot2::theme(panel.grid.major.y = ggplot2::element_blank(),
panel.grid.minor = ggplot2::element_blank())
image <- tempfile(fileext = ".png")
ggplot2::ggsave(image, bars, width = 11, height = 6, dpi = 130, bg = "white")
uri <- base64enc::dataURI(file = image, mime = "image/png")
unlink(image)
list(year = as.character(year), schoolYear = records$school_year[1],
colors = unname(pal(totals)), labels = labels, popups = popups, bar = uri,
summary = sprintf(
"%s: %d of %d district counts reported. Complete county totals: %d of 95. Records outside the county aggregation: %d.",
records$school_year[1], nrow(ranked), nrow(records),
sum(is.finite(totals)), sum(is.na(records$county))))
})
names(year_data) <- as.character(years)
============================================================
Assigns each county polygon an index and writes it to GeoJSON so the browser can match the polygon with its enrollment values.
counties$county_index <- seq_len(nrow(counties)) - 1L
geometry_file <- tempfile(fileext = ".geojson")
sf::st_write(counties[c("NAME", "county_index")], geometry_file, quiet = TRUE)
geojson <- paste(readLines(geometry_file, warn = FALSE), collapse = "")
unlink(geometry_file)
============================================================
Sets the map bounds and adds the legend and Census boundary credit.
map <- leaflet::leaflet(height = 410, elementId = "enrollment-map")
map <- leaflet::fitBounds(map, -90.4, 34.9, -81.6, 36.75)
map <- leaflet::addLegend(map, colors = c(colors, "#bdbdbd"),
labels = c("0–2,499", "2,500–4,999", "5,000–9,999", "10,000–24,999",
"25,000–49,999", "50,000–99,999", "100,000+", "Incomplete / unavailable"),
title = "Local district enrollment", opacity = .85)
map <- leaflet::addControl(map, "County boundaries: U.S. Census Bureau, 2024",
position = "bottomleft")
============================================================
Uses JavaScript to swap the county fills, labels, popups, summary text, and ranking image whenever a reader selects a different year.
map <- htmlwidgets::onRender(map, "
function(el, x, data) {
var picker = document.getElementById('school-year');
var layers = L.geoJSON(JSON.parse(data.geography), {
style: {color: 'white', weight: 1, fillOpacity: 0.85},
onEachFeature: function(feature, layer) {
layer.on('mouseover', function() {
layer.setStyle({weight: 3, color: '#444'});
});
layer.on('mouseout', function() {
layer.setStyle({weight: 1, color: 'white'});
});
}
}).addTo(this);
function updateYear() {
var selected = data.years[picker.value];
layers.eachLayer(function(layer) {
var i = layer.feature.properties.county_index;
layer.setStyle({fillColor: selected.colors[i]});
layer.unbindTooltip();
layer.unbindPopup();
layer.bindTooltip(selected.labels[i]);
layer.bindPopup(selected.popups[i], {maxWidth: 430});
});
document.getElementById('year-summary').textContent = selected.summary;
var image = document.getElementById('enrollment-bars');
image.src = selected.bar;
image.alt = 'District enrollment rankings for ' + selected.schoolYear;
}
picker.addEventListener('change', updateYear);
updateYear();
}
", data = list(geography = geojson, years = year_data))
============================================================
Returns the year selector, map, summary, and ranking image as one HTML component and defaults to the latest year.
tags <- htmltools::tags
latest <- year_data[[length(year_data)]]
htmltools::tagList(
tags$p(tags$label("School year", tags$select(id = "school-year",
lapply(year_data, function(y) {
tags$option(value = y$year,
selected = if (y$year == latest$year) "selected", y$schoolYear)
})))),
tags$p(id = "year-summary", latest$summary),
map,
tags$h3("District enrollment rankings"),
tags$img(id = "enrollment-bars", src = latest$bar,
alt = paste("District enrollment rankings for", latest$schoolYear),
style = "width:100%;height:auto")
)
}
============================================================
Looks for a published statewide total in each annual workbook. If the total is unavailable, it sums the district records only when every count in that year’s CSV rows is available.
read_statewide_totals <- function(csv, d) {
count <- function(x) {
n <- suppressWarnings(as.numeric(gsub(",", "", trimws(as.character(x)), fixed = TRUE)))
n[!is.finite(n) | n < 0] <- NA_real_
n
}
============================================================
Finds the statewide row and total column. It uses the published value when available and labels any fallback district sum.
do.call(rbind, lapply(sort(unique(d$year)), function(year) {
records <- d[d$year == year, ]
path <- file.path(dirname(csv), "raw_workbooks",
paste0("district_profile_", year, ".xlsx"))
if (!file.exists(path)) stop("Statewide source workbook missing: ", path)
x <- readxl::read_excel(path, col_types = "text")
keys <- gsub("[^a-z0-9]", "", tolower(names(x)))
name_col <- which(keys == "districtname")
total_col <- which(keys == "total")
if (length(name_col) != 1L || length(total_col) != 1L) {
stop("Review statewide columns for ", year)
}
state <- which(tolower(trimws(x[[name_col]])) %in%
c("state of tennessee", "tennessee", "state", "statewide", "state total"))
if (length(state) > 1L) stop("Multiple statewide rows for ", year)
total <- if (length(state)) count(x[[total_col]][state]) else NA_real_
method <- "Published statewide"
if (!is.finite(total)) {
total <- if (all(is.finite(records$profile_enrollment))) {
sum(records$profile_enrollment)
} else NA_real_
method <- if (is.finite(total)) "Complete district sum" else "Unavailable"
}
data.frame(year = year, school_year = records$school_year[1],
enrollment = total, method = method)
}))
}
============================================================
Uses a compatible local extract when possible. Otherwise, it retrieves Census estimates for ages 5–17 and combines the age-group estimates and margins of error. The 2020 ACS values remain missing.
read_tn_acs <- function(csv, years) {
candidates <- c(
file.path(dirname(csv), "acs_tn_trend.csv"),
file.path(dirname(dirname(csv)), "Census_ACS", "acs_tn_statewide_1year.csv")
)
required <- c("year", "population_5_17", "population_5_17_moe90",
"private_enrollment_5_17", "private_enrollment_5_17_moe90")
for (path in candidates[file.exists(candidates)]) {
x <- read.csv(path)
if (!all(required %in% names(x)) || anyDuplicated(x$year)) next
if (!all(setdiff(years, 2020) %in% x$year)) next
if ("name" %in% names(x) && !all(x$name == "Tennessee")) next
x <- x[match(years, x$year), required]
x$year <- years
x[x$year == 2020, required[-1]] <- NA_real_
if (anyNA(x[x$year != 2020, required[-1]])) next
return(x)
}
============================================================
Reads the Census API key from the environment, combines estimates and margins of error for the selected ages, and saves a local CSV. It leaves 2020 missing.
key <- Sys.getenv("CENSUS_API_KEY")
if (!nzchar(key)) stop("Set CENSUS_API_KEY locally before knitting. Keep it out of the Rmd.")
population <- sprintf("B01001_%03d", c(4:6, 28:30))
nonpublic <- sprintf("B14003_%03d", c(14:16, 42:44))
variables <- c(population, nonpublic)
x <- do.call(rbind, lapply(years, function(year) {
row <- data.frame(year = year, population_5_17 = NA_real_,
population_5_17_moe90 = NA_real_, private_enrollment_5_17 = NA_real_,
private_enrollment_5_17_moe90 = NA_real_)
if (year == 2020) return(row)
a <- tidycensus::get_acs(geography = "state", state = "TN",
variables = variables, year = year, survey = "acs1", key = key,
cache_table = FALSE, show_call = FALSE)
if (!setequal(a$variable, variables) ||
anyDuplicated(a$variable) || anyNA(a[c("estimate", "moe")]) ||
any(a$estimate < 0) || any(a$moe < 0)) stop("Incomplete ACS data for ", year)
row$population_5_17 <- sum(a$estimate[a$variable %in% population])
row$population_5_17_moe90 <- sqrt(sum(a$moe[a$variable %in% population]^2))
row$private_enrollment_5_17 <- sum(a$estimate[a$variable %in% nonpublic])
row$private_enrollment_5_17_moe90 <- sqrt(sum(a$moe[a$variable %in% nonpublic]^2))
row
}))
write.csv(x, candidates[1], row.names = FALSE, na = "")
x
}
============================================================
Compares public enrollment, school-age population, and private/home enrollment. Readers can switch between raw counts and an index that sets each series to 100 in its first year.
build_statewide_trend <- function(enrollment_csv) {
csv <- path.expand(enrollment_csv)
d <- read.csv(csv)
state <- read_statewide_totals(csv, d)
acs <- read_tn_acs(csv, state$year)
series <- list(
"Public enrollment (TDOE, includes charter)" = state$enrollment,
"Children ages 5–17 (ACS)" = acs$population_5_17,
"Private + homeschool ages 5–17 (ACS)" = acs$private_enrollment_5_17
)
moe <- list(rep(NA_real_, nrow(state)), acs$population_5_17_moe90,
acs$private_enrollment_5_17_moe90)
source <- list(state$method, rep("ACS B01001", nrow(state)),
rep("ACS B14003, private category includes homeschool", nrow(state)))
============================================================
Checks that each series has usable endpoint values and normalizes each one to 100 in its first year.
first <- 1L
last <- nrow(state)
if (!all(vapply(series, function(v) all(is.finite(v[c(first, last)])) &&
v[first] > 0, logical(1)))) stop("Missing endpoint values.")
indexed <- lapply(series, function(v) 100 * v / v[first])
colors <- c("#17657b", "#d07816", "#7e4898")
fmt <- scales::label_comma()
figure <- plotly::plot_ly()
============================================================
Creates a line for each measure and includes the original counts, source, and available margins of error in the hover text.
for (i in seq_along(series)) {
hover <- paste0("Year: ", state$year,
"<br>Enrollment school year: ", state$school_year,
"<br>", names(series)[i], ": ", fmt(series[[i]]),
"<br>Index: ", round(indexed[[i]], 1),
"<br>Source: ", source[[i]],
ifelse(is.finite(moe[[i]]), paste0("<br>Approx. 90% MOE: ±", fmt(round(moe[[i]]))), ""))
figure <- plotly::add_trace(figure, x = state$year, y = indexed[[i]],
type = "scatter", mode = "lines+markers", name = names(series)[i],
text = hover, hoverinfo = "text", connectgaps = FALSE,
line = list(color = colors[i]), marker = list(color = colors[i]))
}
============================================================
Adds a dropdown to switch between indexed values and raw counts.
figure <- plotly::layout(figure,
title = list(text = "Tennessee enrollment and school-age population"),
xaxis = list(title = "Calendar year / school-year start", tickvals = state$year,
tickangle = -45),
yaxis = list(title = paste0("Index (", state$year[first], " = 100)")),
legend = list(orientation = "h", x = 0, y = -.3),
margin = list(t = 100, b = 150),
updatemenus = list(list(type = "dropdown", x = 0, y = 1.18,
buttons = list(
list(label = paste0("Index: ", state$year[first], " = 100"),
method = "update",
args = list(list(y = unname(indexed)),
list(yaxis = list(title = paste0("Index (", state$year[first], " = 100)"))))),
list(label = "Counts", method = "update",
args = list(list(y = unname(series)),
list(yaxis = list(title = "Students / children", rangemode = "tozero"))))
))))
figure <- plotly::config(figure, displaylogo = FALSE, responsive = TRUE)
============================================================
Calculates the numeric and percentage changes between the first and last years and displays the methods used for the enrollment endpoints.
changes <- vapply(seq_along(series), function(i) {
v <- series[[i]]
change <- v[last] - v[first]
sprintf("%s: %s in %d → %s in %d; change %s%s (%+.2f%%).",
names(series)[i], fmt(v[first]), state$year[first], fmt(v[last]), state$year[last],
if (change >= 0) "+" else "−", fmt(abs(change)), 100 * change / v[first])
}, character(1))
htmltools::tagList(
htmltools::tags$p("Use the chart dropdown to switch between counts and an index. Click a legend entry to hide or show a line. Hover over a point for its count, source, and ACS margin of error."),
figure,
htmltools::tags$ul(lapply(changes, htmltools::tags$li)),
htmltools::tags$p(sprintf("TDOE endpoint sources: %s uses %s; %s uses %s.",
state$school_year[first], tolower(state$method[first]),
state$school_year[last], tolower(state$method[last])))
)
}
============================================================
Compares each origin’s private/home enrollment share with Tennessee’s. It counts arrivals from higher-share origins and calculates lower and upper bounds when some arrivals cannot be classified.
calculate_migration_choice <- function(migration_csv, acs_states_csv, lag = 1L) {
if (!lag %in% c(0L, 1L)) stop("Use lag = 0 or lag = 1.")
migration_csv <- path.expand(migration_csv)
acs_states_csv <- path.expand(acs_states_csv)
if (!file.exists(migration_csv) || !file.exists(acs_states_csv)) {
stop("Check migration_csv and acs_states_csv in the R Markdown header.")
}
flows <- read.csv(migration_csv)
acs <- read.csv(acs_states_csv)
if ("origin_state" %in% names(flows)) flows$origin <- flows$origin_state
if ("private_enrollment_5_17" %in% names(acs)) {
acs$private_5_17 <- acs$private_enrollment_5_17
acs$public_5_17 <- acs$public_enrollment_5_17
}
if (!all(c("year", "origin", "arrivals") %in% names(flows)) ||
!all(c("year", "name", "public_5_17", "private_5_17") %in% names(acs))) {
stop("The migration or ACS file has unexpected column names.")
}
states <- c(state.name, "District of Columbia")
if (anyNA(flows[c("year", "origin")]) ||
anyDuplicated(flows[c("year", "origin")]) ||
anyDuplicated(acs[c("year", "name")]) ||
!all(flows$origin %in% setdiff(states, "Tennessee")) ||
!is.numeric(flows$arrivals) || any(flows$arrivals < 0, na.rm = TRUE)) {
stop("Check origin names, counts, and duplicate records.")
}
============================================================
Divides private and home enrollment by total public plus private and home enrollment for each state and year.
denominator <- acs$public_5_17 + acs$private_5_17
acs$rate <- ifelse(denominator > 0, acs$private_5_17 / denominator, NA_real_)
acs$rate[acs$year == 2020] <- NA_real_
years <- seq.int(min(flows$year), max(flows$year))
============================================================
Matches each origin’s schooling rate to the selected year and compares it with Tennessee. It sums arrivals with enough information for classification.
summary <- do.call(rbind, lapply(years, function(year) {
x <- flows[flows$year == year, ]
if (nrow(x) && !setequal(x$origin, setdiff(states, "Tennessee"))) {
stop("Expected 49 other states and DC for ", year)
}
metric_year <- year - lag
rates <- acs[acs$year == metric_year, ]
tn <- rates$rate[rates$name == "Tennessee"]
if (!length(tn)) tn <- NA_real_
origin_rate <- rates$rate[match(x$origin, rates$name)]
higher <- origin_rate > tn
known <- is.finite(x$arrivals)
classified <- known & !is.na(higher)
classified_count <- sum(x$arrivals[classified])
higher_count <- sum(x$arrivals[which(classified & higher)])
total <- if (nrow(x) && all(known)) sum(x$arrivals) else NA_real_
total_source <- if (is.finite(total)) "Sum of all 50 origin estimates" else "Unavailable"
============================================================
Looks for a published statewide arrivals total in the supplemental migration workbook when individual origins cannot all be summed.
# Recover a published total when detailed origins are suppressed.
if (nrow(x) && !all(known)) {
raw_dir <- file.path(dirname(dirname(migration_csv)), "raw", "migration")
workbook <- file.path(raw_dir, paste0(year, ".xlsx"))
if (file.exists(workbook)) {
sheet <- "Supplemental - Current Res"
if (sheet %in% readxl::excel_sheets(workbook)) {
table <- as.matrix(readxl::read_excel(workbook, sheet = sheet,
col_names = FALSE, col_types = "text", .name_repair = "minimal"))
column <- which(matrix(grepl("Movers to current residence.*different state",
gsub("[[:space:]]+", " ", table), ignore.case = TRUE),
nrow = nrow(table)), arr.ind = TRUE)
row <- which(trimws(table[, 1]) == "Tennessee")
if (nrow(column) == 1L && length(row) == 1L) {
total <- suppressWarnings(as.numeric(gsub(",", "",
table[row, column[1, 2]], fixed = TRUE)))
if (is.finite(total)) total_source <- "Published Tennessee interstate arrivals"
}
}
}
}
============================================================
Computes the share among classified arrivals and the range for all arrivals by assigning the unclassified group to either end of the comparison.
if (is.finite(total) && total < sum(x$arrivals[known])) {
stop("Published total is smaller than known origin estimates for ", year)
}
available <- is.finite(total) && total > 0
data.frame(
year = year, metric_year = metric_year, tn_rate = tn,
total = total, classified = classified_count, higher = higher_count,
coverage = if (available) classified_count / total else NA_real_,
classified_share = if (classified_count > 0) higher_count / classified_count else NA_real_,
lower = if (available) higher_count / total else NA_real_,
upper = if (available) (higher_count + total - classified_count) / total else NA_real_,
total_source = total_source
)
}))
summary
}
============================================================
Plots the share among classified arrivals and shades the possible range for all arrivals. Hover text identifies the schooling-data year and how much of the total could be classified.
build_migration_choice <- function(migration_csv, acs_states_csv, lag = 1L) {
s <- calculate_migration_choice(migration_csv, acs_states_csv, lag)
fmt <- scales::label_comma()
percent <- scales::label_percent(accuracy = .1)
hover <- paste0(
"Year people moved to Tennessee: ", s$year, "<br>Schooling data from: ", s$metric_year,
"<br>Tennessee children enrolled in private/home school: ", percent(s$tn_rate),
"<br>People arriving from other states or DC: ", fmt(s$total),
"<br>Arrivals with enough data for comparison: ", fmt(s$classified),
"<br>Percentage of arrivals we could compare: ", percent(s$coverage),
"<br>Share arriving from higher-rate states, among arrivals compared: ", percent(s$classified_share),
"<br>Possible share of all arrivals when some data are missing: ", percent(s$lower), " to ", percent(s$upper),
"<br>Source of total arrivals: ", s$total_source,
ifelse(s$year == 2022, "<br>Census 2022 migration-data caution applies.", "")
)
============================================================
Plots the share among classified arrivals, then shades the possible range for all arrivals when the available data provide informative bounds.
# Hide wholly uninformative 0–100% bounds when no arrivals can be classified.
low <- ifelse(s$classified > 0, s$lower, NA_real_)
high <- ifelse(s$classified > 0, s$upper, NA_real_)
top <- plotly::plot_ly()
top <- plotly::add_trace(top, x = s$year, y = low,
type = "scatter", mode = "lines", line = list(width = 0),
name = "Lower end of possible range", showlegend = FALSE, hoverinfo = "skip",
connectgaps = FALSE)
top <- plotly::add_trace(top, x = s$year, y = high,
type = "scatter", mode = "lines", line = list(width = 0),
fill = "tonexty", fillcolor = "rgba(23,101,123,0.18)",
name = "Possible range when some data are missing", text = hover, hoverinfo = "text",
connectgaps = FALSE)
top <- plotly::add_trace(top, x = s$year, y = s$classified_share,
type = "scatter", mode = "lines+markers", name = "Share among arrivals we could compare",
line = list(color = "#17657b"), marker = list(color = "#17657b"),
text = hover, hoverinfo = "text", connectgaps = FALSE)
figure <- plotly::layout(top,
title = list(text = "Where Tennessee’s new arrivals previously lived"),
yaxis = list(title = "Share of arrivals compared", tickformat = ".0%",
range = c(0, 1)),
xaxis = list(title = "Year people moved to Tennessee",
tickvals = s$year, tickangle = -45),
legend = list(orientation = "h", x = 0, y = -.25),
margin = list(t = 70, b = 140))
figure <- plotly::config(figure, displaylogo = FALSE, responsive = TRUE)
============================================================
Uses the first and last complete years for the trend summary and describes the latest year’s coverage and bounds where available.
comparable <- which(is.finite(s$classified_share) & is.finite(s$lower) &
abs(s$upper - s$lower) < 1e-10)
if (length(comparable) >= 2L) {
a <- s[comparable[1], ]
b <- s[tail(comparable, 1), ]
comparison <- sprintf(
"For years when every arrival could be compared, the share from states where private schooling or homeschooling was more common changed from %.1f%% in %d to %.1f%% in %d (%+.1f percentage points).",
100 * a$classified_share, a$year, 100 * b$classified_share,
b$year, 100 * (b$classified_share - a$classified_share))
} else comparison <- "There are too few years with enough data for a full comparison."
latest <- s[nrow(s), ]
latest_text <- if (is.finite(latest$lower) && latest$classified > 0) {
sprintf(
"In %d, between %.2f%% and %.2f%% of all arrivals came from states where private schooling or homeschooling was more common than in Tennessee. Enough data were available to compare %.2f%% of arrivals.",
latest$year, 100 * latest$lower, 100 * latest$upper, 100 * latest$coverage)
} else "The latest year has too little information to calculate the share of all arrivals."
htmltools::tagList(
htmltools::tags$p("The line shows the share arriving from states where private schooling or homeschooling was more common than in Tennessee. Hover over a point for details."),
figure,
htmltools::tags$p(latest_text),
htmltools::tags$p(comparison),
htmltools::tags$p("The shaded area shows how missing data could affect the share of all arrivals. The percentage axis counts arrivals; Tennessee’s schooling rate is used as the comparison benchmark.")
)
}
============================================================
Creates separate queries for vouchers and ESAs, charter schools, homeschooling, and general school choice. It also creates one combined OR query so overlapping topic counts are not added together.
media_topics <- function() {
topics <- c(
vouchers_esa = '("school voucher" OR "school vouchers" OR "education savings account" OR "education savings accounts" OR "education freedom scholarship" OR "education freedom scholarships")',
charter = '("charter school" OR "charter schools")',
homeschool = '(homeschool OR homeschooling OR homeschooled OR "home school" OR "home schooling")',
general = '("school choice" OR "education choice" OR "educational choice")'
)
c(school_choice_union = paste0("(", paste(topics, collapse = " OR "), ")"), topics)
}
============================================================
Checks the outlet collections, runs each topic search in July–June windows, and caches successful responses. It computes matches per 1,000 indexed stories when the archive count is positive.
collect_media_counts <- function(cache_dir, years = 2009:2024,
tn_collection = "38381366",
national_collection = "34412234") {
cache_dir <- path.expand(cache_dir)
dir.create(cache_dir, recursive = TRUE, showWarnings = FALSE)
collections <- c(tn_outlets = tn_collection,
us_national_mentioning_tn = national_collection)
if (any(!grepl("^[0-9]+$", collections))) stop("Use numeric Media Cloud collection IDs.")
topics <- media_topics()
token <- trimws(Sys.getenv("MEDIACLOUD_API_KEY"))
if (!nzchar(token)) token <- trimws(Sys.getenv("MC_API_KEY"))
# The key is requested only if an uncached request is needed.
============================================================
Keys the cache by endpoint and parameters. For uncached calls, it uses an API token, throttles requests, retries failures, and saves the response and its provenance.
request_json <- function(endpoint, params) {
signature <- tempfile()
on.exit(unlink(signature), add = TRUE)
writeLines(jsonlite::toJSON(list(endpoint = endpoint, params = params),
auto_unbox = TRUE), signature)
hash <- unname(tools::md5sum(signature))
path <- file.path(cache_dir, paste0(hash, ".json"))
if (file.exists(path)) return(jsonlite::fromJSON(path))
if (!nzchar(token)) {
if (requireNamespace("rstudioapi", quietly = TRUE) && rstudioapi::isAvailable()) {
answer <- rstudioapi::askForPassword("Enter your Media Cloud API key")
if (!is.null(answer)) token <<- trimws(answer)
}
if (!nzchar(token)) {
stop("Set MEDIACLOUD_API_KEY in your local .Renviron, restart RStudio, and knit again.")
}
}
request <- httr2::request(paste0("https://search.mediacloud.org/api/", endpoint))
request <- do.call(httr2::req_url_query, c(list(request), params))
request <- httr2::req_headers(request, Authorization = paste("Token", token),
Accept = "application/json")
request <- httr2::req_timeout(request, 120)
# Share one throttle across all uncached requests in this R session.
request <- httr2::req_throttle(
request, capacity = 1, fill_time_s = 31,
realm = "mediacloud-enrollment-report"
)
request <- httr2::req_retry(
request,
max_tries = 10,
retry_on_failure = TRUE,
backoff = function(tries) min(900, 60 * 2^(tries - 1))
)
response <- tryCatch(httr2::req_perform(request), error = function(e) {
status <- grep("^httr2_http_[0-9]+$", class(e), value = TRUE)
detail <- if (length(status)) sub("httr2_http_", "HTTP ", status[1]) else "connection failure"
stop("Media Cloud request failed: ", detail,
". Successful requests were cached. ",
if (identical(detail, "HTTP 429")) {
"Media Cloud is still limiting requests after repeated waits. Resume later or check your account quota."
} else {
"Check access and rerun."
},
call. = FALSE)
})
raw <- httr2::resp_body_raw(response)
payload <- jsonlite::fromJSON(rawToChar(raw))
temp <- tempfile(tmpdir = cache_dir)
on.exit(unlink(temp), add = TRUE)
writeBin(raw, temp)
if (!file.rename(temp, path)) stop("Could not save the Media Cloud cache.")
jsonlite::write_json(list(endpoint = endpoint, parameters = params,
retrieved_utc = format(Sys.time(), tz = "UTC", usetz = TRUE)),
paste0(path, ".provenance.json"), auto_unbox = TRUE, pretty = TRUE)
payload
}
============================================================
Validates that counts are nonnegative whole numbers, checks the collection names, and searches each topic for each July–June school year.
number <- function(x) {
n <- suppressWarnings(as.numeric(x))
if (length(n) != 1L || !is.finite(n) || n < 0 || n != floor(n)) {
stop("Media Cloud returned an invalid count.")
}
n
}
output <- list()
for (scope in names(collections)) {
# Verify that the requested collection is still the intended group.
metadata <- request_json(paste0("sources/collections/", collections[[scope]], "/"), list())
collection_name <- metadata$name
if (is.null(collection_name) && is.list(metadata$collection)) {
collection_name <- metadata$collection$name
}
expected <- if (scope == "tn_outlets") "Tennessee" else "National"
if (length(collection_name) != 1L ||
!grepl(expected, collection_name, ignore.case = TRUE)) {
stop("Review the Media Cloud collection ID for ", scope)
}
for (year in years) {
for (topic in names(topics)) {
query <- topics[[topic]]
if (scope == "us_national_mentioning_tn") query <- paste0(query, " AND Tennessee")
params <- list(start = sprintf("%d-07-01", year),
end = sprintf("%d-06-30", year + 1L),
platform = "onlinenews-mediacloud", cs = collections[[scope]],
q = query)
message("News coverage: ", year, " / ", scope, " / ", topic)
answer <- request_json("search/total-count", params)
matched <- number(answer$count$relevant)
indexed <- number(answer$count$total)
if (matched > indexed) stop("Matching count exceeds indexed count.")
output[[length(output) + 1L]] <- data.frame(
year = year, school_year = sprintf("%d-%02d", year, (year + 1L) %% 100L),
scope = scope, collection_id = collections[[scope]],
collection_name = collection_name, topic = topic, query = query,
period_start = params$start, period_end = params$end,
matching_stories = matched, indexed_stories = indexed,
matches_per_1000 = if (indexed > 0) 1000 * matched / indexed else NA_real_,
status = if (indexed > 0) "processed" else "No indexed stories"
)
}
}
}
============================================================
Combines the topic-year results and writes the aggregate counts and rates to a CSV.
result <- do.call(rbind, output)
# Keep aggregate files next to this report-specific cache.
path <- file.path(cache_dir, "media_counts_2009_2024.csv")
temp <- tempfile(tmpdir = cache_dir)
on.exit(unlink(temp), add = TRUE)
write.csv(result, temp, row.names = FALSE, na = "")
if (file.exists(path)) {
# Individual responses remain the authoritative cache for resuming.
file.copy(temp, path, overwrite = TRUE)
} else if (!file.rename(temp, path)) stop("Could not save news coverage counts.")
result
}
============================================================
Displays Tennessee outlets and national outlets mentioning Tennessee in two panels. Readers can switch between coverage rates, matching story counts, and archive size.
build_media_coverage <- function(cache_dir, tn_collection = "38381366",
national_collection = "34412234") {
d <- collect_media_counts(cache_dir, tn_collection = tn_collection,
national_collection = national_collection)
topic_labels <- c(
school_choice_union = "Any school-choice topic",
vouchers_esa = "Vouchers and education savings accounts",
charter = "Charter schools",
homeschool = "Homeschooling",
general = "General school-choice terms"
)
colors <- c("#17657b", "#d07816", "#7e4898", "#358044", "#bb4863")
figures <- list()
rates <- counts <- indexed <- list()
trace_index <- 0L
============================================================
Creates a series for each topic and outlet group. Years without indexed stories are gaps, while zero matching stories in a populated archive are valid counts.
for (scope in c("tn_outlets", "us_national_mentioning_tn")) {
figure <- plotly::plot_ly()
for (i in seq_along(topic_labels)) {
x <- d[d$scope == scope & d$topic == names(topic_labels)[i], ]
x <- x[order(x$year), ]
trace_index <- trace_index + 1L
rates[[trace_index]] <- x$matches_per_1000
# A missing archive is a gap in both display modes.
counts[[trace_index]] <- ifelse(x$indexed_stories > 0, x$matching_stories, NA_real_)
indexed[[trace_index]] <- x$indexed_stories
hover <- paste0(
"School year: ", x$school_year, "<br>Outlet group: ", x$collection_name,
"<br>Topic: ", topic_labels[i],
"<br>Matching indexed stories: ", scales::comma(x$matching_stories),
"<br>All indexed stories in this outlet group: ", scales::comma(x$indexed_stories),
"<br>Matches per 1,000 indexed stories: ", round(x$matches_per_1000, 3),
"<br>Search period: ", x$period_start, " through ", x$period_end,
"<br>Status: ", x$status
)
figure <- plotly::add_trace(figure, x = x$year, y = x$matches_per_1000,
type = "scatter", mode = "lines+markers", name = topic_labels[i],
legendgroup = names(topic_labels)[i], showlegend = scope == "tn_outlets",
line = list(color = colors[i], width = if (i == 1) 3 else 1.5),
marker = list(color = colors[i]), text = hover, hoverinfo = "text",
connectgaps = FALSE)
}
figure <- plotly::layout(figure,
yaxis = list(title = list(text = "Matches / 1,000", standoff = 12),
automargin = TRUE, rangemode = "tozero"))
figures[[length(figures) + 1L]] <- figure
}
============================================================
Combines the two panels and lets readers switch between matches per 1,000 indexed stories, raw matching counts, and archive size.
figure <- plotly::subplot(figures[[1]], figures[[2]], nrows = 2,
shareX = TRUE, heights = c(.5, .5), titleY = TRUE, margin = .14)
figure$height <- 800
figure <- plotly::layout(figure,
title = list(text = ""),
annotations = list(
list(text = "<b>Tennessee outlets</b>", x = 0, y = 1.01,
xref = "paper", yref = "paper", xanchor = "left",
yanchor = "bottom", showarrow = FALSE),
list(text = "<b>National outlets mentioning Tennessee</b>", x = 0, y = .44,
xref = "paper", yref = "paper", xanchor = "left",
yanchor = "bottom", showarrow = FALSE)
),
yaxis = list(title = list(text = "Matches / 1,000", standoff = 12),
automargin = TRUE),
yaxis2 = list(title = list(text = "Matches / 1,000", standoff = 12),
automargin = TRUE),
xaxis2 = list(title = list(text = "School-year start", standoff = 15),
tickvals = seq(2009, 2024, by = 2), tickangle = -45, automargin = TRUE),
legend = list(orientation = "h", x = 0, y = -.22, xanchor = "left", yanchor = "top"),
margin = list(l = 95, r = 25, t = 145, b = 180),
updatemenus = list(list(type = "dropdown", x = 0, y = 1.17,
xanchor = "left", yanchor = "bottom",
buttons = list(
list(label = "Matches per 1,000 indexed stories", method = "update",
args = list(list(y = rates),
list(yaxis = list(title = "Matches / 1,000", rangemode = "tozero"),
yaxis2 = list(title = "Matches / 1,000", rangemode = "tozero")))),
list(label = "Matching story counts", method = "update",
args = list(list(y = counts),
list(yaxis = list(title = "Matching stories", rangemode = "tozero"),
yaxis2 = list(title = "Matching stories", rangemode = "tozero")))),
list(label = "Archive size (same within each outlet group)", method = "update",
args = list(list(y = indexed),
list(yaxis = list(title = "Indexed stories", rangemode = "tozero"),
yaxis2 = list(title = "Indexed stories", rangemode = "tozero"))))
))))
figure <- plotly::config(figure, displaylogo = FALSE, responsive = TRUE)
htmltools::tagList(
htmltools::tags$h3("School-choice topics in indexed news coverage"),
htmltools::tags$p("Switch between coverage rates, story counts, and archive size. Click a topic in the legend to hide or show it in both panels. Hover over a point for its counts and outlet group."),
figure,
htmltools::tags$p(sprintf(
"%d of %d topic-year results have no indexed stories and appear as gaps. A zero matching count with a positive archive size is a measured zero within this archive.",
sum(d$indexed_stories == 0), nrow(d)))
)
}
============================================================
Sources the helper script in the original report so the chart-building functions are available to its later chunks.
source("enrollment_graphics.R", local = TRUE)
============================================================
Passes the enrollment CSV path from the report parameters to the map and ranking function.
build_enrollment_report(params$enrollment_csv)
============================================================
Uses the same enrollment CSV path to find the statewide enrollment records and compare them with Tennessee ACS estimates.
build_statewide_trend(params$enrollment_csv)
============================================================
Passes the migration CSV, the state ACS CSV, and the configured schooling-data lag to the comparison function.
build_migration_choice(
params$migration_csv,
params$acs_states_csv,
lag = params$choice_lag
)
============================================================
Passes the cache directory and both Media Cloud collection IDs to the news chart function.
build_media_coverage(
params$media_cache_dir,
tn_collection = params$tn_media_collection,
national_collection = params$national_media_collection
)