This project asks how local MTSU Football’s roster is and how that compares to the other nine Conference USA programs. As Assistant Creative Director for MTSU Football, I produce a lot of content built around where our players come from, including our Middle Made series on our players who attended Middle Tennessee high schools. I wanted to replace my instinct about how “local” our roster is with real numbers, and to see whether the “Middle Tennessee’s team” story holds up against our peers.
All data comes from the College Football Data API, accessed with the
cfbfastR R package. The API is free but requires a personal
API key.
I pulled two datasets:
cfbd_team_roster()): one row
per player per season for all 10 2026 CUSA teams, 2022–2026. That’s
5,327 rows, including each player’s position and hometown city, state
and coordinates.cfbd_team_info()): one row
per team, including each home stadium’s coordinates.Because the API needs a key, I saved both datasets as CSV files in a public GitHub repository so anyone can load them without one.
Hometown coordinates are available for 83–99% of players in every team-season. Delaware (2023–24), Kennesaw State (2023) and Missouri State (2023–24) have incomplete rosters in the API, likely because those programs were moving up from FCS. For that reason, the team comparisons use only the 2025 and 2026 seasons.
The audience-facing report includes:
plotly bar chart of each team’s in-state
shareplotly bar chart of each team’s median
distance from homeleaflet map of every 2026 CUSA player’s
hometown, with MTSU highlightedWhat I did:
.Renviron fileWhat I delegated to AI (Claude):
.Renviron instead of in my codedplyrplotly and maps
with leaflet1. Get the data from the API (one time only). The
first script loads cfbfastR and reads my API key from
.Renviron. It calls cfbd_team_info() to get
the 10 CUSA teams and their stadium coordinates. It then loops through
every team and season from 2022 to 2026, calling
cfbd_team_roster() for each, and stacks the results into
one data frame. It keeps only the variables listed in the codebook,
checks how many players have hometown coordinates, and saves both
datasets as CSV files that I uploaded to GitHub.
2. Load the data from GitHub. The report reads both
CSV files straight from GitHub with read_csv(), so no API
key is needed. athlete_id and home_county_fips
are read as text so leading zeros aren’t lost.
3. Calculate distance from home. Each player is
joined to his team’s stadium coordinates with left_join().
distHaversine() from the geosphere package
calculates the straight-line distance between the hometown and stadium
in meters, which is divided by 1,609.34 to convert to miles. The code
also flags whether each player’s home state matches his school’s
state.
4. Summarize by team. The code keeps only 2025 and 2026 players with known hometowns. It then groups by team and calculates the percent in-state, the percent within 300 miles and the median distance.
5. Build the charts. Each plotly chart
starts as a ggplot2 bar chart, with MTSU colored blue, and
is converted to an interactive chart with ggplotly().
6. Build the map. The leaflet map plots
every 2026 player’s hometown as a dot, using separate layers for MTSU
and the other teams so viewers can toggle them. Clicking a dot shows a
popup with the player’s name, team, position and hometown.
7. Build the tables. MTSU’s feeder states,
five-season trend and the full team comparison are displayed with
datatable() from the DT package.
8. Write numbers into the text. The key numbers in the report’s summary use inline R code, so they update automatically if the data change.
library(cfbfastR)
library(dplyr)
library(tidyr)
library(purrr)
library(readr)
readRenviron("~/.Renviron")
if (!nzchar(Sys.getenv("CFBD_API_KEY"))) {
stop("No CFBD_API_KEY found. Add it to .Renviron and restart R.")
}
# 2026 CUSA teams and stadium coordinates
campuses <- cfbd_team_info(conference = "CUSA", year = 2026) |>
as_tibble() |>
select(any_of(c("team_id", "school", "abbreviation", "conference",
"venue_name", "city", "state", "latitude", "longitude"))) |>
mutate(latitude = as.numeric(latitude),
longitude = as.numeric(longitude))
# Rosters for each team, 2022-2026
seasons <- 2022:2026
get_roster <- function(team, season) {
Sys.sleep(0.5)
df <- tryCatch(cfbd_team_roster(year = season, team = team),
error = function(e) NULL)
if (is.null(df) || nrow(df) == 0) {
message("No roster returned: ", team, " ", season)
return(NULL)
}
df <- as_tibble(df)
df$season <- season
df
}
rosters_raw <- expand_grid(team = campuses$school, season = seasons) |>
pmap(get_roster) |>
list_rbind()
# Keep codebook variables
rosters <- rosters_raw |>
select(any_of(c("season", "team", "athlete_id", "first_name", "last_name",
"position", class_year = "year", "height", "weight", "jersey",
"home_city", "home_state", "home_country",
"home_latitude", "home_longitude", "home_county_fips"))) |>
mutate(
athlete_id = as.character(athlete_id),
home_latitude = as.numeric(home_latitude),
home_longitude = as.numeric(home_longitude),
home_county_fips = if_else(is.na(home_county_fips), NA_character_,
sprintf("%05d", as.integer(home_county_fips)))
) |>
arrange(team, season, last_name)
# Coverage check
rosters |>
group_by(team, season) |>
summarise(players = n(),
pct_with_coords = round(mean(!is.na(home_latitude)) * 100, 1),
.groups = "drop") |>
print(n = Inf)
# Save CSVs for GitHub
dir.create("data", showWarnings = FALSE)
write_csv(rosters, "data/cusa_rosters.csv")
write_csv(campuses, "data/cusa_campuses.csv")
library(tidyverse)
library(geosphere)
library(DT)
library(plotly)
library(leaflet)
base_url <- "https://raw.githubusercontent.com/johndlafrance-png/cusa-hometowns/main/data/"
rosters <- read_csv(
paste0(base_url, "cusa_rosters.csv"),
col_types = cols(athlete_id = col_character(),
home_county_fips = col_character())
)
campuses <- read_csv(paste0(base_url, "cusa_campuses.csv"))
# Distance from home and in-state flag
players <- rosters |>
left_join(campuses |>
select(school, campus_state = state,
campus_lat = latitude, campus_lon = longitude),
by = c("team" = "school")) |>
mutate(
miles_from_home = distHaversine(
cbind(home_longitude, home_latitude),
cbind(campus_lon, campus_lat)
) / 1609.34,
in_state = home_state == campus_state,
is_mtsu = team == "Middle Tennessee"
)
recent <- players |>
filter(season %in% c(2025, 2026), !is.na(home_latitude))
# Team summary
team_summary <- recent |>
group_by(team) |>
summarise(
players = n(),
pct_in_state = round(mean(in_state, na.rm = TRUE) * 100, 1),
pct_within_300 = round(mean(miles_from_home <= 300) * 100, 1),
median_miles = round(median(miles_from_home)),
.groups = "drop"
) |>
arrange(desc(pct_in_state)) |>
mutate(rank = row_number())
mtsu <- team_summary |> filter(team == "Middle Tennessee")
top_team <- team_summary |> slice_max(pct_in_state, n = 1)
low_team <- team_summary |> slice_min(pct_in_state, n = 1)
mtsu_states <- recent |>
filter(is_mtsu) |>
count(home_state, sort = TRUE, name = "players") |>
mutate(pct = round(players / sum(players) * 100, 1))
mtsu_trend <- players |>
filter(is_mtsu, !is.na(home_latitude)) |>
group_by(season) |>
summarise(
players = n(),
pct_in_state = round(mean(in_state, na.rm = TRUE) * 100, 1),
median_miles = round(median(miles_from_home)),
.groups = "drop"
)
# In-state chart
chart_data <- team_summary |>
mutate(team = fct_reorder(team, pct_in_state),
group = if_else(team == "Middle Tennessee", "MTSU", "Other CUSA"))
p1 <- ggplot(chart_data,
aes(x = pct_in_state, y = team, fill = group,
text = paste0(team, ": ", pct_in_state, "% in-state"))) +
geom_col() +
scale_fill_manual(values = c("MTSU" = "#0066CC", "Other CUSA" = "grey70")) +
labs(x = "% of players from the school's home state", y = NULL, fill = NULL) +
theme_minimal()
ggplotly(p1, tooltip = "text")
# Distance chart
dist_data <- team_summary |>
mutate(team = fct_reorder(team, -median_miles),
group = if_else(team == "Middle Tennessee", "MTSU", "Other CUSA"))
p2 <- ggplot(dist_data,
aes(x = median_miles, y = team, fill = group,
text = paste0(team, ": ", median_miles, " miles"))) +
geom_col() +
scale_fill_manual(values = c("MTSU" = "#0066CC", "Other CUSA" = "grey70")) +
labs(x = "Median miles from hometown to campus", y = NULL, fill = NULL) +
theme_minimal()
ggplotly(p2, tooltip = "text")
# Map
map_data <- players |>
filter(season == 2026, !is.na(home_latitude))
popup_text <- function(df) {
paste0("<b>", df$first_name, " ", df$last_name, "</b><br>",
df$team, " (", df$position, ")<br>",
df$home_city, ", ", df$home_state)
}
others <- map_data |> filter(!is_mtsu)
mtsu_pts <- map_data |> filter(is_mtsu)
leaflet() |>
addProviderTiles(providers$Esri.WorldGrayCanvas) |>
addCircleMarkers(data = others,
lng = ~home_longitude, lat = ~home_latitude,
radius = 3, color = "grey", stroke = FALSE,
fillOpacity = 0.6, popup = popup_text(others),
group = "Other CUSA") |>
addCircleMarkers(data = mtsu_pts,
lng = ~home_longitude, lat = ~home_latitude,
radius = 5, color = "#0066CC", stroke = FALSE,
fillOpacity = 0.85, popup = popup_text(mtsu_pts),
group = "MTSU") |>
addLayersControl(overlayGroups = c("MTSU", "Other CUSA"),
options = layersControlOptions(collapsed = FALSE)) |>
setView(lng = -88, lat = 34, zoom = 4)
# Tables
mtsu_states |>
head(10) |>
datatable(colnames = c("Home state", "Player-seasons", "% of MTSU roster"),
rownames = FALSE, options = list(dom = "t"))
mtsu_trend |>
datatable(colnames = c("Season", "Players", "% from Tennessee",
"Median miles from home"),
rownames = FALSE, options = list(dom = "t"))
team_summary |>
select(-rank) |>
datatable(colnames = c("Team", "Player-seasons", "% in-state",
"% within 300 mi", "Median miles from home"),
rownames = FALSE, options = list(pageLength = 10, dom = "t"))