NBA Box Scores: Data Cleaning with Pandas (Part 2 of?)

I’ll use Pandas to clean the 2018-2019 Regular Season NBA Team Box Score data scraped from ESPN.com. The web scraping code can be found here and the CSV file of the raw data can be found here.

TL/DR: To prepare the data for future data analysis and predictive modeling, I’ll be doing the following:

1. Drop redundant/unnecessary columns.
2. Create new columns for ‘shots made’ and ‘shots attempted’ for Field Goals, 3 Pointers, and Free Throws.
3. Create boolean columns for game wins and game losses.
4. Create boolean columns for home games and away games.

import pandas as pd
import numpy as np

Looking at the Data

Let’s inspect the data to get a general overview of what was scraped from the web.

def display_df(df):
    """ Set column and row display options for a data frame. """
    with pd.option_context("display.min_rows", 10, "display.max_rows", 20, "display.max_columns", 100):
        display(df)

Read in the CSV file of the scraped NBA data and load it as a Pandas data frame.

year = '2019'
df = pd.read_csv('../data/nba_team_box_scores_'+str(year)+'.csv')

Let’s look at all of the columns of the first 5 and the last 5 rows of the data.

display_df(df)

2458 rows × 51 columns

Let’s see if there are any missing values, the data type of each column, the shape of the data frame, and the size of the data.

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2458 entries, 0 to 2457
Data columns (total 51 columns):
 #   Column       Non-Null Count  Dtype
---  ------       --------------  -----
 0   Unnamed: 0   2458 non-null   int64
 1   teamABBR     2458 non-null   object
 2   teamName     2458 non-null   object
 3   gameID       2458 non-null   int64
 4   gameDate     2458 non-null   object
 5   gameLoc      2458 non-null   object
 6   teamResult   2458 non-null   object
 7   teamPTS      2458 non-null   int64
 8   teamFG       2458 non-null   object
 9   teamFG%      2458 non-null   float64
 10  team3PT      2458 non-null   object
 11  team3PT%     2458 non-null   float64
 12  teamFT       2458 non-null   object
 13  teamFT%      2458 non-null   float64
 14  teamTREB     2458 non-null   int64
 15  teamASST     2458 non-null   int64
 16  teamSTL      2458 non-null   int64
 17  teamBLK      2458 non-null   int64
 18  teamTO       2458 non-null   int64
 19  teamFB_PTS   2458 non-null   int64
 20  teamPNT_PTS  2458 non-null   int64
 21  teamFOUL     2458 non-null   int64
 22  teamLG_LEAD  2458 non-null   int64
 23  teamOREB     2458 non-null   int64
 24  teamDREB     2458 non-null   int64
 25  teamTO_PTS   2458 non-null   int64
 26  teamFOUL_T   2458 non-null   int64
 27  teamFOUL_F   2458 non-null   int64
 28  opptABBR     2458 non-null   object
 29  opptName     2458 non-null   object
 30  opptPTS      2458 non-null   int64
 31  opptFG       2458 non-null   object
 32  opptFG%      2458 non-null   float64
 33  oppt3PT      2458 non-null   object
 34  oppt3PT%     2458 non-null   float64
 35  opptFT       2458 non-null   object
 36  opptFT%      2458 non-null   float64
 37  opptTREB     2458 non-null   int64
 38  opptASST     2458 non-null   int64
 39  opptSTL      2458 non-null   int64
 40  opptBLK      2458 non-null   int64
 41  opptTO       2458 non-null   int64
 42  opptFB_PTS   2458 non-null   int64
 43  opptPNT_PTS  2458 non-null   int64
 44  opptFOUL     2458 non-null   int64
 45  opptLG_LEAD  2458 non-null   int64
 46  opptOREB     2458 non-null   int64
 47  opptDREB     2458 non-null   int64
 48  opptTO_PTS   2458 non-null   int64
 49  opptFOUL_T   2458 non-null   int64
 50  opptFOUL_F   2458 non-null   int64
dtypes: float64(6), int64(32), object(13)
memory usage: 979.5+ KB

Doesn’t look like there are any missing values, which is nice.

Now let’s get an idea of the distributions of the data columns.

display_df(df.describe())

1. Drop “Unnamed: 0” Column

The “Unnamed: 0” column appears to only contain row numbers which is redundant with the data frame index. So we can drop that column.

df = df.drop(columns='Unnamed: 0')

df.head()

5 rows × 50 columns

2. Create new columns for Shots Made and Shots Attempted

The stats scraped from ESPN.com list shots made and shots attempted as a single string delienated by a hyphen. Field Goals (teamFG or opptFG), for example:

df.teamFG[0], df.opptFG[0]

('42-97', '34-87')

We’ll split on the hyphen and create new columns for shots made and attempted for Field Goals, 3 Pointers, and Free Throws. We’ll also convert the new columns to integers.

df[['teamFGM', 'teamFGA']] = df.teamFG.str.split('-', expand=True).astype(int)

Check to make sure the new columns were created.

df.T.tail(3).T

2458 rows × 3 columns

The new columns have been appended to the end of the data frame. Also, we’ll check that the data type was converted.

df.teamFGM.dtype

dtype('int64')

Looks good, so we’ll split the remaining columns.

df[['opptFGM', 'opptFGA']] = df.opptFG.str.split('-', expand=True).astype(int)

df[['team3PTM', 'team3PTA']] = df.team3PT.str.split('-', expand=True).astype(int)
df[['oppt3PTM', 'oppt3PTA']] = df.oppt3PT.str.split('-', expand=True).astype(int)

df[['teamFTM', 'teamFTA']] = df.teamFT.str.split('-', expand=True).astype(int)
df[['opptFTM', 'opptFTA']] = df.opptFT.str.split('-', expand=True).astype(int)

df.T.tail(12).T

2458 rows × 12 columns

3. Handling Categorical Variables with One-Hot Encoding

Categorical variables are variables that have two or more discrete values. They come in 2 flavors: nominal, where there is no particular order (e.g. ‘red’, ‘green’, ‘blue’), and ordinal, where there is order (e.g. ‘small’, ‘medium’, ‘large’).

Most machine learning models require numerical data as input. So, it’s necessary to convert categorical data to numerical data using one of two methods:

1. Label Encoding assigns an integer value to each category (e.g. red = 1, green = 2, blue =3). One draw back to this type of encoding is that the machine learning model may assume there is some kind of rank amongst the variables (e.g. red > green > blue). But sometimes a ranked order does not exist.
2. One-Hot Encoding can circumvent the implicit ranking by adding new features (e.g. is_red = 0, is_green = 1, is_blue = 0). If a data point is green, then a boolean value of 1 (True) is listed as an is_green feature, while is_red and is_blue are listed as 0 (False). It’s important to note that if the cardinality were high (i.e. if there were a lot of different values, think lots of postal codes), one-hot encoding could lead to high memory consumption and poor modeling efficiency.

For this data we’ll convert the categorical variables (game location and team result) to numerical data using one-hot encoding.

df = pd.get_dummies(df, columns=['gameLoc', 'teamResult'])

df.T.tail(5).T

Now we’ll save our cleaned data frame.

df.to_csv('../data/nba_team_box_scores_'+str(year)+'_cleaned.csv')