Purpose

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.

Data source

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:

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.

Output

The audience-facing report includes:

What I did vs. what I delegated to AI

What I did:

What I delegated to AI (Claude):

What I learned

How the code works

1. 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.

Full script

Part 1: Retrieve the data from the API

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")

Part 2: Analysis, charts and map

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"))