I retrieved the data from Wikipedia through the MediaWiki Action API. These are the API documents and source pages I used.
API documentation
parse module documentationhttr2 documentationWikipedia source pages retrieved through the API
I used httr2 to request sports-history data from the MediaWiki API.
Because Wikipedia can change, I saved the API URL, revision ID, retrieval time, and raw response before I analyzed the data.
# I load the packages used to request, clean, store, and display the data.
library(httr2)
library(jsonlite)
library(rvest)
library(dplyr)
library(tibble)
library(stringr)
library(ggplot2)
library(DBI)
library(RSQLite)
library(plotly)
library(knitr)httr2 JSON requestI wrote one function so every request follows the same steps. It identifies the request, checks the response, and keeps both the parsed JSON and raw text.
# I send one MediaWiki API request and keep the parsed and raw response.
fetch_json <- function(base_url, ..., source_name) {
req <- request(base_url) |>
req_url_query(...) |>
req_user_agent("DATA607-week9-lab/1.0 (https://www.cuny.edu)") |>
req_headers(Accept = "application/json")
resp <- req |> req_perform()
raw_body <- resp_body_string(resp)
parsed_body <- jsonlite::fromJSON(raw_body, simplifyVector = FALSE)
list(
source_name = source_name,
request_url = resp_url(resp),
retrieved_at_utc = format(Sys.time(), tz = "UTC", usetz = TRUE),
raw_body = raw_body,
data = parsed_body
)
}# I open the SQLite database and create a table for the raw API responses.
con <- dbConnect(SQLite(), "week9_wikimedia_sports.sqlite")
invisible(dbExecute(
con,
"CREATE TABLE IF NOT EXISTS raw_api_snapshots (
source_name TEXT,
request_url TEXT,
retrieved_at_utc TEXT,
raw_body TEXT
)"
))
# I clear the old snapshots so a new run does not add duplicate copies.
invisible(dbExecute(con, "DELETE FROM raw_api_snapshots"))The API returns a page’s rendered HTML and revision ID in JSON. This request is an API request, not a direct scrape of a web page.
# Request the NBA career-scoring source and save its raw API response.
nba_page <- fetch_json(
base_url = "https://en.wikipedia.org/w/api.php",
action = "parse",
page = "List_of_NBA_career_scoring_leaders",
prop = "text|revid",
format = "json",
formatversion = "2",
source_name = "Wikipedia: List of NBA career scoring leaders"
)
nba_raw_snapshot <- tibble(
source_name = nba_page$source_name,
request_url = nba_page$request_url,
retrieved_at_utc = nba_page$retrieved_at_utc,
raw_body = nba_page$raw_body
)
invisible(dbWriteTable(
con,
"raw_api_snapshots",
nba_raw_snapshot,
append = TRUE
))
kable(
tibble(
item = "Wikipedia revision ID",
value = nba_page$data$parse$revid
),
col.names = c("Saved item", "Value"),
caption = "NBA API snapshot",
align = c("l", "r")
)| Saved item | Value |
|---|---|
| Wikipedia revision ID | 1384655873 |
The API returned HTML inside the JSON response. I parsed that HTML and inspected the available tables before choosing one.
# I turn the HTML returned by the API into a list of tables.
parse_wikitable_list <- function(api_page) {
document <- read_html(api_page$data$parse$text)
table_nodes <- html_elements(document, "table.wikitable")
tables <- lapply(table_nodes, html_table, convert = FALSE)
# Some Wikipedia tables repeat a column name, such as Coach.
# Unique names prevent dplyr from stopping when those tables are cleaned.
lapply(tables, function(table) {
names(table) <- make.unique(names(table))
table
})
}
nba_tables <- parse_wikitable_list(nba_page)
# I summarize each table so I can compare its rows and columns.
describe_tables <- function(tables) {
tibble(table_index = seq_along(tables)) |>
mutate(
rows = vapply(tables, nrow, integer(1)),
columns = vapply(
tables,
function(x) {
clean_names <- names(x) |>
str_replace("^\\.mw-parser-output.*$", "Reference") |>
str_remove_all("\\[[^]]*\\]") |>
str_squish()
paste(clean_names, collapse = "<br>")
},
character(1)
)
)
}
kable(
describe_tables(nba_tables) |>
mutate(selected = if_else(table_index == 2, "Used", "")),
col.names = c("Table", "Rows", "Columns found", "Selection"),
align = c("r", "r", "l", "l"),
escape = FALSE
)| Table | Rows | Columns found | Selection |
|---|---|---|---|
| 1 | 3 | X1 X2 |
|
| 2 | 50 | Rank Player Pos Team(s) played for (years) Total points Games played Points per game Field goalsmade Three-pointfield goalsmade Free throws made |
Used |
| 3 | 3 | X1 X2 |
|
| 4 | 8 | X1 X2 X3 X4 X5 X6 X7 X8 |
|
| 5 | 82 | Season Year-by-year leader Points Active player leader Total points Career record Total points.1 Single-season record Points.1 Season.1 |
On the current source page, table 2 contains the career leaderboard. Inspect the previous output whenever Wikipedia revises its page layout.
# Select, clean, display, and save the first ten NBA career scoring leaders.
nba_leaders_raw <- nba_tables[[2]]
kable(
tibble(
column = seq_along(names(nba_leaders_raw)),
name = names(nba_leaders_raw)
),
col.names = c("Column", "Name"),
caption = "Columns in the selected NBA leaderboard",
align = c("r", "l")
)| Column | Name |
|---|---|
| 1 | Rank |
| 2 | Player |
| 3 | Pos |
| 4 | Team(s) played for (years)[b] |
| 5 | Total points |
| 6 | Games played |
| 7 | Points per game[c] |
| 8 | Field goalsmade |
| 9 | Three-pointfield goalsmade[d] |
| 10 | Free throws made |
nba_points_top10 <- nba_leaders_raw |>
transmute(
rank = seq_len(n()),
player = str_remove_all(
.data[[names(nba_leaders_raw)[grepl("Player", names(nba_leaders_raw))][1]]],
"[*^]"
) |>
str_squish(),
points = as.numeric(gsub(
"[^0-9]",
"",
.data[[names(nba_leaders_raw)[grepl("Total points", names(nba_leaders_raw))][1]]]
))
) |>
slice_head(n = 10)
kable(
nba_points_top10,
col.names = c("Rank", "Player", "Points"),
caption = "Top 10 NBA career scoring leaders",
align = c("r", "l", "r"),
format.args = list(big.mark = ",")
)| Rank | Player | Points |
|---|---|---|
| 1 | LeBron James | 43,580 |
| 2 | Kareem Abdul-Jabbar | 38,387 |
| 3 | Karl Malone | 36,928 |
| 4 | Kobe Bryant | 33,643 |
| 5 | Kevin Durant | 32,805 |
| 6 | Michael Jordan | 32,292 |
| 7 | Dirk Nowitzki | 31,560 |
| 8 | Wilt Chamberlain | 31,419 |
| 9 | James Harden | 29,496 |
| 10 | Shaquille O’Neal | 28,596 |
invisible(dbWriteTable(
con,
"nba_career_points_top10",
nba_points_top10,
overwrite = TRUE
))# Request, clean, display, and save the first ten MLB career home-run leaders.
mlb_page <- fetch_json(
base_url = "https://en.wikipedia.org/w/api.php",
action = "parse",
page = "List_of_Major_League_Baseball_career_home_run_leaders",
prop = "text|revid",
format = "json",
formatversion = "2",
source_name = "Wikipedia: List of Major League Baseball career home run leaders"
)
invisible(dbWriteTable(
con,
"raw_api_snapshots",
tibble(
source_name = mlb_page$source_name,
request_url = mlb_page$request_url,
retrieved_at_utc = mlb_page$retrieved_at_utc,
raw_body = mlb_page$raw_body
),
append = TRUE
))
mlb_tables <- parse_wikitable_list(mlb_page)
kable(
describe_tables(mlb_tables) |>
mutate(selected = if_else(table_index == 2, "Used", "")),
col.names = c("Table", "Rows", "Columns found", "Selection"),
align = c("r", "r", "l", "l"),
escape = FALSE
)| Table | Rows | Columns found | Selection |
|---|---|---|---|
| 1 | 5 | X1 X2 |
|
| 2 | 75 | Rank Player (2027 HRs) HR |
Used |
| 3 | 75 | Rank Player (2027 HRs) HR |
|
| 4 | 75 | Rank Player (2027 HRs) HR |
|
| 5 | 78 | Rank Player (2027 HRs) HR |
mlb_leaders_raw <- mlb_tables[[2]]
mlb_home_runs_top10 <- mlb_leaders_raw |>
transmute(
rank = as.integer(Rank),
player = str_remove_all(
.data[[names(mlb_leaders_raw)[grepl("Player", names(mlb_leaders_raw))][1]]],
"[*^]"
) |>
str_squish(),
home_runs = as.integer(HR)
) |>
slice_head(n = 10)
kable(
mlb_home_runs_top10,
col.names = c("Rank", "Player", "Home runs"),
caption = "Top 10 MLB career home-run leaders",
align = c("r", "l", "r"),
format.args = list(big.mark = ",")
)| Rank | Player | Home runs |
|---|---|---|
| 1 | Barry Bonds | 762 |
| 2 | Hank Aaron | 755 |
| 3 | Babe Ruth | 714 |
| 4 | Albert Pujols | 703 |
| 5 | Alex Rodriguez | 696 |
| 6 | Willie Mays | 660 |
| 7 | Ken Griffey Jr. | 630 |
| 8 | Jim Thome | 612 |
| 9 | Sammy Sosa | 609 |
| 10 | Frank Robinson | 586 |
invisible(dbWriteTable(
con,
"mlb_career_home_runs_top10",
mlb_home_runs_top10,
overwrite = TRUE
))I requested four championship-history pages through the same API. Their layouts differ, so I inspected the tables before selecting the fields I needed.
# I request and inspect the championship tables for the four leagues.
championship_pages <- c(
NBA = "List_of_NBA_champions",
NFL = "List_of_Super_Bowl_champions",
NHL = "List_of_Stanley_Cup_champions",
MLB = "List_of_World_Series_champions"
)
championship_api_pages <- lapply(
names(championship_pages),
function(league) {
fetch_json(
base_url = "https://en.wikipedia.org/w/api.php",
action = "parse",
page = championship_pages[[league]],
prop = "text|revid",
format = "json",
formatversion = "2",
source_name = paste("Wikipedia championship history:", league)
)
}
)
names(championship_api_pages) <- names(championship_pages)
championship_tables <- lapply(championship_api_pages, parse_wikitable_list)
championship_table_index <- lapply(championship_tables, describe_tables)
kable(
championship_table_index$NBA |>
mutate(selected = if_else(table_index == 2, "Used", "")),
col.names = c("Table", "Rows", "Columns found", "Selection"),
align = c("r", "r", "l", "l"),
escape = FALSE
)| Table | Rows | Columns found | Selection |
|---|---|---|---|
| 1 | 4 | X1 X2 |
|
| 2 | 80 | Year Western champion Coach Result Eastern champion Coach.1 Finals MVP Reference |
Used |
| 3 | 28 | Team Wins Losses Apps Pct Year(s) won Year(s) lost |
|
| 4 | 13 | Count Matchup Record Years |
I combined the NBA, NFL, NHL, and MLB records into a table named championships_clean. Each row represents one championship and contains league, season, winning_team, winning_city, and state_abbr.
I then created two summaries:
winning_city, winning_team, and league; andI used the city where the team played when it won. For example, I counted a Minneapolis Lakers title for Minneapolis, Minnesota.
# Remove citations and record notes from team names before matching locations.
clean_team_name <- function(x) {
x |>
str_replace_all("\\[[^]]*\\]", "") |>
str_replace("\\s*\\([^)]*\\).*$", "") |>
str_replace("\\.mw-parser-output.*$", "") |>
str_replace("†.*$", "") |>
str_squish()
}
# The NBA table lists both finalists, so the series score identifies the winner.
nba_finals <- championship_tables$NBA[[2]]
nba_championships <- nba_finals |>
mutate(
west_wins = as.integer(str_extract(Result, "^[0-9]+")),
east_wins = as.integer(str_extract(Result, "[0-9]+$")),
winning_team = if_else(
west_wins > east_wins,
`Western champion`,
`Eastern champion`
)
) |>
transmute(
league = "NBA",
season = as.integer(str_extract(Year, "[0-9]{4}")),
winning_team = clean_team_name(winning_team)
)
# The Super Bowl table already identifies the winning team.
# Its final letter is a conference marker added by the source page.
nfl_championships <- championship_tables$NFL[[2]] |>
transmute(
league = "NFL",
season = as.integer(str_match(`Date (season)`, "\\(([0-9]{4})")[, 2]),
winning_team = clean_team_name(`Winning team`) |>
str_replace("[ANan]$", "") |>
str_squish()
)
# The NHL table includes one cancelled season, which was not a championship.
nhl_championships <- championship_tables$NHL[[3]] |>
transmute(
league = "NHL",
season = as.integer(str_extract(Year, "[0-9]{4}")),
winning_team = clean_team_name(`Winning team`)
) |>
filter(!str_detect(winning_team, regex("cancelled", ignore_case = TRUE)))
# The MLB table includes seasons when the World Series was not played.
mlb_championships <- championship_tables$MLB[[1]] |>
transmute(
league = "MLB",
season = as.integer(str_extract(Year, "[0-9]{4}")),
winning_team = clean_team_name(`Winning team`)
) |>
filter(
winning_team != "",
!str_detect(
winning_team,
regex("not played|cancelled", ignore_case = TRUE)
)
)
championships_raw <- bind_rows(
nba_championships,
nfl_championships,
nhl_championships,
mlb_championships
) |>
mutate(
# Golden State played in Oakland for its earlier titles and in
# San Francisco after moving to Chase Center.
team_key = case_when(
winning_team == "Golden State Warriors" & season >= 2020 ~
"Golden State Warriors - San Francisco",
winning_team == "Golden State Warriors" ~
"Golden State Warriors - Oakland",
TRUE ~ winning_team
)
)
# This lookup keeps historical team locations separate from the API data.
# Canadian province codes are retained, but only U.S. state codes enter the map.
team_locations <- tribble(
~team_key, ~winning_city, ~state_abbr,
"Anaheim Angels", "Anaheim", "CA",
"Anaheim Ducks", "Anaheim", "CA",
"Arizona Diamondbacks", "Phoenix", "AZ",
"Atlanta Braves", "Atlanta", "GA",
"Baltimore Bullets", "Baltimore", "MD",
"Baltimore Colts", "Baltimore", "MD",
"Baltimore Orioles", "Baltimore", "MD",
"Baltimore Ravens", "Baltimore", "MD",
"Boston Americans", "Boston", "MA",
"Boston Braves", "Boston", "MA",
"Boston Bruins", "Boston", "MA",
"Boston Celtics", "Boston", "MA",
"Boston Red Sox", "Boston", "MA",
"Brooklyn Dodgers", "Brooklyn", "NY",
"Calgary Flames", "Calgary", "AB",
"Carolina Hurricanes", "Raleigh", "NC",
"Chicago Bears", "Chicago", "IL",
"Chicago Black Hawks", "Chicago", "IL",
"Chicago Blackhawks", "Chicago", "IL",
"Chicago Bulls", "Chicago", "IL",
"Chicago Cubs", "Chicago", "IL",
"Chicago White Sox", "Chicago", "IL",
"Cincinnati Reds", "Cincinnati", "OH",
"Cleveland Cavaliers", "Cleveland", "OH",
"Cleveland Indians", "Cleveland", "OH",
"Colorado Avalanche", "Denver", "CO",
"Dallas Cowboys", "Dallas", "TX",
"Dallas Mavericks", "Dallas", "TX",
"Dallas Stars", "Dallas", "TX",
"Denver Broncos", "Denver", "CO",
"Denver Nuggets", "Denver", "CO",
"Detroit Pistons", "Detroit", "MI",
"Detroit Red Wings", "Detroit", "MI",
"Detroit Tigers", "Detroit", "MI",
"Edmonton Oilers", "Edmonton", "AB",
"Florida Marlins", "Miami", "FL",
"Florida Panthers", "Sunrise", "FL",
"Golden State Warriors - Oakland", "Oakland", "CA",
"Golden State Warriors - San Francisco", "San Francisco", "CA",
"Green Bay Packers", "Green Bay", "WI",
"Houston Astros", "Houston", "TX",
"Houston Rockets", "Houston", "TX",
"Indianapolis Colts", "Indianapolis", "IN",
"Kansas City Chiefs", "Kansas City", "MO",
"Kansas City Royals", "Kansas City", "MO",
"Los Angeles Dodgers", "Los Angeles", "CA",
"Los Angeles Kings", "Los Angeles", "CA",
"Los Angeles Lakers", "Los Angeles", "CA",
"Los Angeles Raiders", "Los Angeles", "CA",
"Los Angeles Rams", "Los Angeles", "CA",
"Miami Dolphins", "Miami", "FL",
"Miami Heat", "Miami", "FL",
"Milwaukee Braves", "Milwaukee", "WI",
"Milwaukee Brewers", "Milwaukee", "WI",
"Milwaukee Bucks", "Milwaukee", "WI",
"Minneapolis Lakers", "Minneapolis", "MN",
"Minnesota Twins", "Minneapolis", "MN",
"Montreal Canadiens", "Montreal", "QC",
"Montreal Maroons", "Montreal", "QC",
"New England Patriots", "Foxborough", "MA",
"New Jersey Devils", "East Rutherford", "NJ",
"New Orleans Saints", "New Orleans", "LA",
"New York Giants", "New York", "NY",
"New York Islanders", "Uniondale", "NY",
"New York Jets", "New York", "NY",
"New York Knicks", "New York", "NY",
"New York Mets", "New York", "NY",
"New York Rangers", "New York", "NY",
"New York Yankees", "New York", "NY",
"Oakland Athletics", "Oakland", "CA",
"Oakland Raiders", "Oakland", "CA",
"Oklahoma City Thunder", "Oklahoma City", "OK",
"Ottawa Senators", "Ottawa", "ON",
"Philadelphia 76ers", "Philadelphia", "PA",
"Philadelphia Athletics", "Philadelphia", "PA",
"Philadelphia Eagles", "Philadelphia", "PA",
"Philadelphia Flyers", "Philadelphia", "PA",
"Philadelphia Phillies", "Philadelphia", "PA",
"Philadelphia Warriors", "Philadelphia", "PA",
"Pittsburgh Penguins", "Pittsburgh", "PA",
"Pittsburgh Pirates", "Pittsburgh", "PA",
"Pittsburgh Steelers", "Pittsburgh", "PA",
"Portland Trail Blazers", "Portland", "OR",
"Rochester Royals", "Rochester", "NY",
"San Antonio Spurs", "San Antonio", "TX",
"San Francisco 49ers", "San Francisco", "CA",
"San Francisco Giants", "San Francisco", "CA",
"Seattle Seahawks", "Seattle", "WA",
"Seattle SuperSonics", "Seattle", "WA",
"St. Louis Blues", "St. Louis", "MO",
"St. Louis Cardinals", "St. Louis", "MO",
"St. Louis Hawks", "St. Louis", "MO",
"St. Louis Rams", "St. Louis", "MO",
"Syracuse Nationals", "Syracuse", "NY",
"Tampa Bay Buccaneers", "Tampa", "FL",
"Tampa Bay Lightning", "Tampa", "FL",
"Texas Rangers", "Arlington", "TX",
"Toronto Blue Jays", "Toronto", "ON",
"Toronto Maple Leafs", "Toronto", "ON",
"Toronto Raptors", "Toronto", "ON",
"Vegas Golden Knights", "Las Vegas", "NV",
"Washington Bullets", "Washington", "DC",
"Washington Capitals", "Washington", "DC",
"Washington Nationals", "Washington", "DC",
"Washington Redskins", "Washington", "DC",
"Washington Senators", "Washington", "DC"
)
unmatched_teams <- anti_join(championships_raw, team_locations, by = "team_key")
if (nrow(unmatched_teams) > 0) {
stop(
paste0(
"Missing location lookup: ",
paste(
unmatched_teams$league,
unmatched_teams$season,
shQuote(unmatched_teams$team_key),
collapse = "; "
)
)
)
}
championships_clean <- championships_raw |>
left_join(team_locations, by = "team_key") |>
select(league, season, winning_team, winning_city, state_abbr) |>
arrange(league, season)
# I saved the completed challenge table in the same SQLite database.
invisible(dbWriteTable(
con,
"championships_clean",
championships_clean,
overwrite = TRUE
))
# Confirming that every league-season is unique and every location was matched.
challenge_checks <- tibble(
check = c(
"Championship records",
"Duplicate league-season rows",
"Missing city or region codes"
),
result = c(
nrow(championships_clean),
sum(duplicated(championships_clean[c("league", "season")])),
sum(is.na(championships_clean$winning_city) |
is.na(championships_clean$state_abbr))
)
)
kable(
challenge_checks,
col.names = c("Check", "Result"),
caption = "Challenge 1 data checks",
align = c("l", "r")
)| Check | Result |
|---|---|
| Championship records | 361 |
| Duplicate league-season rows | 0 |
| Missing city or region codes | 0 |
championship_totals <- championships_clean |>
count(winning_city, winning_team, league, name = "championships") |>
arrange(desc(championships), winning_city, winning_team, league)
kable(
championship_totals,
col.names = c("Winning city", "Winning team", "League", "Championships"),
caption = "Championship totals by city, team, and league",
align = c("l", "l", "l", "r")
)| Winning city | Winning team | League | Championships |
|---|---|---|---|
| New York | New York Yankees | MLB | 27 |
| Montreal | Montreal Canadiens | NHL | 22 |
| Boston | Boston Celtics | NBA | 18 |
| Los Angeles | Los Angeles Lakers | NBA | 12 |
| Detroit | Detroit Red Wings | NHL | 11 |
| St. Louis | St. Louis Cardinals | MLB | 11 |
| Toronto | Toronto Maple Leafs | NHL | 11 |
| Boston | Boston Red Sox | MLB | 8 |
| Los Angeles | Los Angeles Dodgers | MLB | 8 |
| Boston | Boston Bruins | NHL | 6 |
| Chicago | Chicago Bulls | NBA | 6 |
| Foxborough | New England Patriots | NFL | 6 |
| Pittsburgh | Pittsburgh Steelers | NFL | 6 |
| Cincinnati | Cincinnati Reds | MLB | 5 |
| Dallas | Dallas Cowboys | NFL | 5 |
| Edmonton | Edmonton Oilers | NHL | 5 |
| Minneapolis | Minneapolis Lakers | NBA | 5 |
| New York | New York Giants | MLB | 5 |
| Philadelphia | Philadelphia Athletics | MLB | 5 |
| Pittsburgh | Pittsburgh Penguins | NHL | 5 |
| Pittsburgh | Pittsburgh Pirates | MLB | 5 |
| San Antonio | San Antonio Spurs | NBA | 5 |
| San Francisco | San Francisco 49ers | NFL | 5 |
| Detroit | Detroit Tigers | MLB | 4 |
| Green Bay | Green Bay Packers | NFL | 4 |
| Kansas City | Kansas City Chiefs | NFL | 4 |
| New York | New York Giants | NFL | 4 |
| New York | New York Rangers | NHL | 4 |
| Oakland | Golden State Warriors | NBA | 4 |
| Oakland | Oakland Athletics | MLB | 4 |
| Uniondale | New York Islanders | NHL | 4 |
| Baltimore | Baltimore Orioles | MLB | 3 |
| Chicago | Chicago Black Hawks | NHL | 3 |
| Chicago | Chicago Blackhawks | NHL | 3 |
| Chicago | Chicago Cubs | MLB | 3 |
| Chicago | Chicago White Sox | MLB | 3 |
| Denver | Colorado Avalanche | NHL | 3 |
| Denver | Denver Broncos | NFL | 3 |
| Detroit | Detroit Pistons | NBA | 3 |
| East Rutherford | New Jersey Devils | NHL | 3 |
| Miami | Miami Heat | NBA | 3 |
| New York | New York Knicks | NBA | 3 |
| San Francisco | San Francisco Giants | MLB | 3 |
| Tampa | Tampa Bay Lightning | NHL | 3 |
| Washington | Washington Redskins | NFL | 3 |
| Atlanta | Atlanta Braves | MLB | 2 |
| Baltimore | Baltimore Ravens | NFL | 2 |
| Cleveland | Cleveland Indians | MLB | 2 |
| Houston | Houston Astros | MLB | 2 |
| Houston | Houston Rockets | NBA | 2 |
| Kansas City | Kansas City Royals | MLB | 2 |
| Los Angeles | Los Angeles Kings | NHL | 2 |
| Miami | Florida Marlins | MLB | 2 |
| Miami | Miami Dolphins | NFL | 2 |
| Milwaukee | Milwaukee Bucks | NBA | 2 |
| Minneapolis | Minnesota Twins | MLB | 2 |
| New York | New York Mets | MLB | 2 |
| Oakland | Oakland Raiders | NFL | 2 |
| Philadelphia | Philadelphia 76ers | NBA | 2 |
| Philadelphia | Philadelphia Eagles | NFL | 2 |
| Philadelphia | Philadelphia Flyers | NHL | 2 |
| Philadelphia | Philadelphia Phillies | MLB | 2 |
| Philadelphia | Philadelphia Warriors | NBA | 2 |
| Raleigh | Carolina Hurricanes | NHL | 2 |
| Seattle | Seattle Seahawks | NFL | 2 |
| Sunrise | Florida Panthers | NHL | 2 |
| Tampa | Tampa Bay Buccaneers | NFL | 2 |
| Toronto | Toronto Blue Jays | MLB | 2 |
| Anaheim | Anaheim Angels | MLB | 1 |
| Anaheim | Anaheim Ducks | NHL | 1 |
| Arlington | Texas Rangers | MLB | 1 |
| Baltimore | Baltimore Bullets | NBA | 1 |
| Baltimore | Baltimore Colts | NFL | 1 |
| Boston | Boston Americans | MLB | 1 |
| Boston | Boston Braves | MLB | 1 |
| Brooklyn | Brooklyn Dodgers | MLB | 1 |
| Calgary | Calgary Flames | NHL | 1 |
| Chicago | Chicago Bears | NFL | 1 |
| Cleveland | Cleveland Cavaliers | NBA | 1 |
| Dallas | Dallas Mavericks | NBA | 1 |
| Dallas | Dallas Stars | NHL | 1 |
| Denver | Denver Nuggets | NBA | 1 |
| Indianapolis | Indianapolis Colts | NFL | 1 |
| Las Vegas | Vegas Golden Knights | NHL | 1 |
| Los Angeles | Los Angeles Raiders | NFL | 1 |
| Los Angeles | Los Angeles Rams | NFL | 1 |
| Milwaukee | Milwaukee Braves | MLB | 1 |
| Milwaukee | Milwaukee Brewers | MLB | 1 |
| Montreal | Montreal Maroons | NHL | 1 |
| New Orleans | New Orleans Saints | NFL | 1 |
| New York | New York Jets | NFL | 1 |
| Oklahoma City | Oklahoma City Thunder | NBA | 1 |
| Ottawa | Ottawa Senators | NHL | 1 |
| Phoenix | Arizona Diamondbacks | MLB | 1 |
| Portland | Portland Trail Blazers | NBA | 1 |
| Rochester | Rochester Royals | NBA | 1 |
| San Francisco | Golden State Warriors | NBA | 1 |
| Seattle | Seattle SuperSonics | NBA | 1 |
| St. Louis | St. Louis Blues | NHL | 1 |
| St. Louis | St. Louis Hawks | NBA | 1 |
| St. Louis | St. Louis Rams | NFL | 1 |
| Syracuse | Syracuse Nationals | NBA | 1 |
| Toronto | Toronto Raptors | NBA | 1 |
| Washington | Washington Bullets | NBA | 1 |
| Washington | Washington Capitals | NHL | 1 |
| Washington | Washington Nationals | MLB | 1 |
| Washington | Washington Senators | MLB | 1 |
top_three_cities <- championships_clean |>
count(winning_city, state_abbr, name = "championships") |>
arrange(desc(championships), winning_city) |>
slice_head(n = 3)
kable(
top_three_cities,
col.names = c("City", "State or province", "Championships"),
caption = "Three cities with the most championships",
align = c("l", "l", "r")
)| City | State or province | Championships |
|---|---|---|
| New York | NY | 46 |
| Boston | MA | 34 |
| Los Angeles | CA | 24 |
I used championships_clean to create the state table, bar chart, and interactive U.S. map. I grouped the same records by state so the totals match. The hover text shows the state abbreviation, championship total, and winning cities. I also printed the totals so the results do not depend on color alone.
# I count U.S. championships by state and keep the cities for the hover text.
state_championships <- championships_clean |>
filter(state_abbr %in% state.abb) |>
group_by(state_abbr) |>
summarise(
championship_total = n(),
cities = paste(sort(unique(winning_city)), collapse = ", "),
.groups = "drop"
)
# I include every state so states with zero championships still appear.
state_map <- tibble(state_abbr = state.abb) |>
left_join(state_championships, by = "state_abbr") |>
mutate(
championship_total = if_else(
is.na(championship_total),
0L,
championship_total
),
cities = if_else(is.na(cities), "None in this dataset", cities),
hover_text = paste0(
state_abbr,
"<br>Championships: ", championship_total,
"<br>Cities: ", cities
)
)
# I use the same Viridis color scale in the table and map.
viridis_colors <- viridisLite::viridis(256)
maximum_total <- max(state_map$championship_total)
state_table <- state_map |>
arrange(desc(championship_total), state_abbr) |>
slice_head(n = 10) |>
mutate(
color_index = 1L + round(255 * championship_total / maximum_total),
map_color = viridis_colors[color_index],
text_color = if_else(
championship_total / maximum_total > 0.55,
"#111111",
"#FFFFFF"
),
championship_display = paste0(
"<span style='display:inline-block;min-width:3rem;",
"padding:0.15rem 0.45rem;text-align:center;",
"border:1px solid #555;background:", map_color, ";",
"color:", text_color, ";font-weight:600;'>",
championship_total, "</span>"
)
)
# I print each total so the table does not depend only on color.
kable(
state_table |>
select(state_abbr, championship_display, cities),
col.names = c("State", "Championships (map color)", "Winning cities"),
caption = "Ten states with the largest championship totals. Colors match the map below.",
align = c("l", "r", "l"),
escape = FALSE
)| State | Championships (map color) | Winning cities |
|---|---|---|
| NY | 53 | Brooklyn, New York, Rochester, Syracuse, Uniondale |
| CA | 45 | Anaheim, Los Angeles, Oakland, San Francisco |
| MA | 40 | Boston, Foxborough |
| PA | 31 | Philadelphia, Pittsburgh |
| MO | 20 | Kansas City, St. Louis |
| IL | 19 | Chicago |
| MI | 18 | Detroit |
| TX | 17 | Arlington, Dallas, Houston, San Antonio |
| FL | 14 | Miami, Sunrise, Tampa |
| OH | 8 | Cincinnati, Cleveland |
The bar chart shows the same ten states as the table.
ggplot(
state_table,
aes(
x = reorder(state_abbr, championship_total),
y = championship_total,
fill = map_color
)
) +
geom_col(color = "#333333", width = 0.7) +
geom_text(
aes(label = championship_total),
hjust = -0.2,
size = 3.5
) +
coord_flip() +
scale_fill_identity() +
scale_y_continuous(expand = expansion(mult = c(0, 0.1))) +
labs(
title = "States with the Most Championships",
subtitle = "Same totals and colors as the table and map",
x = "State",
y = "Championships"
) +
theme_minimal(base_size = 12) +
theme(panel.grid.major.y = element_blank())
# I use Viridis because it is readable for common forms of color blindness.
plot_geo(state_map, locationmode = "USA-states") |>
add_trace(
z = ~championship_total,
locations = ~state_abbr,
text = ~hover_text,
hoverinfo = "text",
type = "choropleth",
colorscale = "Viridis",
marker = list(line = list(color = "white", width = 0.7)),
colorbar = list(
title = "Championships",
thickness = 14,
len = 0.65
)
) |>
layout(
title = list(text = "NBA, NFL, NHL, and MLB Championships by U.S. State"),
geo = list(
scope = "usa",
projection = list(type = "albers usa"),
showlakes = TRUE,
lakecolor = "white"
),
margin = list(l = 0, r = 0, t = 70, b = 0),
paper_bgcolor = "white",
font = list(color = "#222222")
)# I close the SQLite connection after the work is finished.
dbDisconnect(con)This lab showed me that getting data from an API is only the first step. The MediaWiki API returned JSON, but the championship records were stored inside HTML tables. I had to inspect each league separately because the tables did not use the same column names or layout. The hardest part was matching older team names to the correct city. This mattered for teams that moved, such as the Minneapolis Lakers and Golden State Warriors. I created a separate location lookup and checked for unmatched teams before calculating the totals. The final table contained 361 championship records. The checks also found zero duplicate league-season records and zero missing locations. If I rebuilt championships_clean, I would place the team-location history in a separate table with start and end years. This would make team moves easier to manage instead of handling them inside the cleaning code. My main takeaway is that an API can provide the data, but I still have to inspect, clean, and check it before using the results.
I used ChatGPT and VS Code to help review the code, troubleshoot errors, and organize the tables. I ran the file and checked the results against the MediaWiki API output.