For this week’s project I wanted to work with pulling data using python, turning that data into a CSV, and then creating an interesting dashboard with that data. The end result is this dashboard and I explain the process of building it below.
Sports data is great because it’s usually pretty clean and everyone is familiar with how sports work. Economic data from FRED, while great, has a learning curve. So I decided to venture into pulling some NFL data to get started.
My first attempt at pulling the data was to use Python’s Beautifulsoup library to scrape it off of https://www.pro-football-reference.com/. It’s a great site with all the stats you could want. It’s also very difficult to scrape data from. And when I did some research, it looks like no one has been able to scrape data from it for a few years. All the GitHub repositories are from around 2019. This makes sense. If I were Pro-Football-Reference I would also design my site to be impossible/difficult to scrape. So i had to find another solution and I did.
Thank goodness Python has a robust community. Because I was able to find the NFL-data-py library and use it to import data. It’s a library that has tons of NFL stats going back to 1999, so twenty-five years. So with that library found I was able to quickly whip up a python script in Repl.
Below is the Python script I used to pull the data.
import pandas as pd
import nfl_data_py as nfl
import pandasql as ps
print(nfl.see_weekly_cols())
##Variables
years = list(range(1999, 2025))
QBcolumns =['player_id', 'headshot_url', 'recent_team',
'player_display_name','passing_yards','position','season',
'week','completions','attempts','passing_tds','interceptions',
'sacks', 'sack_yards', 'sack_fumbles', 'sack_fumbles_lost',
'passing_air_yards', 'passing_yards_after_catch', 'passing_first_downs',
'rushing_yards', 'rushing_tds']
##########################
##QB Table
##########################
QBstats = nfl.import_weekly_data(years, QBcolumns)
QBquery = "SELECT * FROM QBstats WHERE position = 'QB'"
QB2000s = ps.sqldf(QBquery, locals())
baseline_season = QB2000s['season'].min()
# Calculate a continuous variable: total weeks since baseline
QB2000s['season_week_index'] = (QB2000s['season'] - baseline_season) * 17 + QB2000s['week']
QB2000s.to_csv("QB-data-2000s.csv", index=False)
print("QB CSV exported")So let me walk through this code quickly. First I imported all the necessary libraries. Pandas for working with data. nfl_data_py to pull the NFL data. And, pandasql because I wanted to throw some SQL in there just to get some practice. Originally I thought the query I’d be doing would be more interesting, but ultimately it turned out to be a rather lame SQL query and is entirely superfluous. Regular pandas could have easily handled it.
I pull the NFL data into QBstats using nfl.import_weekly_data() function. It takes two inputs a list of years and a list of columns. Both of those variables were created under the variables section.
After that I used SQL to select on the the Quarterbacks.
For this data I had Seasons and Weeks, which sounds like dates, but actually isn’t. It’s more just the nomenclature the NFL uses. Next I had to create a week index. Dealing with dates is always a pain and it’s more of pain when the dates aren’t tired to a calendar. But the work around I came up with was to create a week index.
To create an index, I took a baseline(aka the minimum) of the seasons, and then subtracted that value from all the season in the data set. Next, I multiplied that by 17, because there are 17 weeks in an NFL season. And then added the week to the respective season. This created a continuous index that would allow me to post the QB passing data in a time series.
Last I exported it all to a CSV and loaded it into Tableau.
In Tableau, I used a table calculation on Passing Yards to get a running total. This was how I made the main graph. And then I used Dashboard actions to make the the graph filter when you clicked on a specific line. Similar to my last Tableau Dashboard(The Meijer Superstore), I created a parameter and a calculated field so that when you clicked on the lines the selected line would turn red.
Wrap Up
Overall, the end result is not has impressive as I hoped it would be. I spent way too much time on trying to scrape the data and failing and then looking for an alternative way to acquire NFL data. I also spend too much time fighting with date data, and realizing I needed to make an index of weeks.
Once I had it, the dashboard came together rather quickly.
It was good experience even if the end result is a little lack luster. When working with data the hardest parts are often getting the data and transforming the data, and this project was right in line with that. But I do think rediscovering date indexes the hard way was a valuable lesson and a strategy I won’t soon forget.
Anyways, on to the next!