DATA607 Lab: Sports API

Author

Andre Thomson

Sources and documentation

Every dataset in this lab is retrieved from Wikipedia through the documented MediaWiki Action API. The links below identify both the API documentation and the exact source pages used in the code.

API and R documentation

Wikipedia source pages retrieved through the API

Purpose

In this lab, we will use httr2 to request sports-history data from the documented MediaWiki API. We will:

  1. pull all-time NBA regular-season scoring leaders and MLB home-run leaders;
  2. save raw API responses and tidy tables in SQLite;
  3. retrieve championship tables for the NBA, NFL, NHL, and MLB;
  4. aggregate championships by the city where the team won; and
  5. create an interactive U.S. state heat map whose hover text lists championship cities.

Wikipedia is a community-maintained source. The reproducibility rule for this lab is to save the API URL, revision ID, retrieval time, and raw response before analyzing the data.

Packages

A reusable httr2 JSON request

MediaWiki asks API clients to identify themselves with a useful User-Agent. The function below sends a documented HTTP request, checks the response, and returns both parsed JSON and the raw text needed for a reproducible SQLite snapshot.

Save a raw API snapshot in SQLite

Pull all-time NBA points leaders

The MediaWiki Parse 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.

NBA API snapshot
Saved item Value
Wikipedia revision ID 1384655873

Turn API-returned HTML into tables

The API returns rendered HTML inside its JSON body. We parse only that returned API content and then inspect the candidate tables before choosing one.

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

Select and tidy the NBA top 10

On the current source page, table 2 contains the career leaderboard. Inspect the previous output whenever Wikipedia revises its page layout.

Columns in the selected NBA leaderboard
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
Top 10 NBA career scoring leaders
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

Pull and tidy all-time MLB home-run leaders

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
Top 10 MLB career home-run leaders
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

The source page labels active players for the upcoming 2027 season, but it says the statistics are updated through the 2026 season. I used only the career home-run totals in the HR column.

Championship sources for four leagues

The next code retrieves four championship-history pages through the same HTTP API. The page formats differ, so we inspect the tables first rather than assuming every page uses the same column names.

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

Challenges for a two-point final-grade boost

Complete both challenges below. The lab gives you the API data; the work in these blocks is yours.

Challenge 1: Create the tables

Create a table named championships_clean from the NBA, NFL, NHL, and MLB championship data. Each row must be one championship, with these columns: league, season, winning_team, winning_city, and state_abbr.

Then display two tables:

  1. championship totals by winning_city, winning_team, and league; and
  2. the three cities with the most championships.

Use the city where the team played when it won. For example, a Minneapolis Lakers title belongs to Minneapolis, MN.

For this lab, I treated winning_city as the city or home market represented by the team during the championship season. I did not replace it with a separate suburb where the stadium happened to be located.

** First table:**

league season winning_team winning_city state_abbr
NBA 1985 Los Angeles Lakers Los Angeles CA
NBA 1953 Minneapolis Lakers Minneapolis MN
Challenge 1 data checks
Check Result
Championship records 361
Duplicate league-season rows 0
Missing city or region codes 0
Raw API snapshots saved 6
Championship totals by city, team, and league
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
Three cities with the most championships
City State or province Championships
New York NY 46
Boston MA 34
Los Angeles CA 24

Challenge 2: Create the map

Use championships_clean to create an interactive U.S. heat map. When someone hovers over a state, the tooltip must show the state abbreviation, its championship total, and the cities in that state that won championships.

Example tooltip:

CA
Championships: 45
Cities: Anaheim, Los Angeles, Oakland, San Francisco

I used championships_clean for the table, chart, and map. I grouped the same records by state so the totals match. The table uses the same Viridis colors as the map, but the numbers are also shown.

Ten states with the largest championship totals. Colors match the map below.
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.

Horizontal bar chart of the ten states with the most championships. New York has 53, California has 45, and Massachusetts has 40. Bar colors match the table and heat map.

Close the database connection

Final reflection

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.

Sources, textbook, and AI-use disclosure

I used ChatGPT and VS Code to help troubleshoot the code and organize the tables. I ran the file and checked the results against the MediaWiki API output.