Exercise - Data cleansing part 2 - Drop columns and rows

• 10 minutes

One of the quickest ways to cleanse data is to drop columns and rows that don't add value to your data-discovery goals. In the previous unit, you discovered two columns that have only NaN values for each row. They were unnamed columns, so they were probably included in the original dataset by mistake. Those columns are straightforward to drop.

Drop columns

To drop columns, you'll use the dropna() method. Like isna(), dropna() looks for NaN values. But it goes a step further and removes either the rows or the columns that contain NaN values. Start by setting some parameters to the method:

• By default, dropna() removes rows, so specify that you want to remove columns by using the axis parameter.
• The dropna() method usually returns a new DataFrame. Use the inplace parameter to tell it to drop these columns in the original player_df DataFrame.
• You also want dropna() to remove only columns in which all of the values are missing. So set the how parameter to 'all'.

# Drop columns that have no values.
player_df.dropna(axis='columns', inplace=True, how='all')
player_df.isna().sum()

ID              0
points          3
possessions     3
team_pace       3
GP              7
MPG             6
TS%             1
AST             1
TO              1
USG             1
ORR             1
DRR             1
REBR            1
PER            10
dtype: int64

You successfully removed the empty columns. You see that if you call .info() again:

player_df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 46 entries, 0 to 45
Data columns (total 14 columns):
 #   Column       Non-Null Count  Dtype  
---  ------       --------------  -----  
 0   ID           46 non-null     int64  
 1   points       43 non-null     float64
 2   possessions  43 non-null     float64
 3   team_pace    43 non-null     float64
 4   GP           39 non-null     float64
 5   MPG          40 non-null     float64
 6   TS%          45 non-null     float64
 7   AST          45 non-null     float64
 8   TO           45 non-null     float64
 9   USG          45 non-null     float64
 10  ORR          45 non-null     float64
 11  DRR          45 non-null     float64
 12  REBR         45 non-null     float64
 13  PER          36 non-null     float64
dtypes: float64(13), int64(1)
memory usage: 5.2 KB

You now have only 14 columns instead of 16. Most of the rows have data for each column.

Drop rows

You should also address the possibility that an entire row is missing values. That is, one of the players in the DataFrame might have no stats. You can try to address this missing row as you did last time, by using dropna(). But this time, use the method's default row-based behavior.

# Drop rows that have no values.
player_df.dropna(inplace=True, how='all')
player_df.isna().sum()

ID              0
points          3
possessions     3
team_pace       3
GP              7
MPG             6
TS%             1
AST             1
TO              1
USG             1
ORR             1
DRR             1
REBR            1
PER            10
dtype: int64

Hmmm. No change. So you know that no row has all NaN values. That's great!

Now look at the DataFrame itself. It's large enough that you wouldn't want to print all of the rows all the time. But it's not so large that it's unmanageable to print it here once.

# Show the entire DataFrame.
player_df

Output

Now you see that three rows are missing the same three values. Another row is missing 10 values. This information indicates two things:

• The dataset likely comes from two datasets that were joined together, and both of these earlier datasets had missing rows.
• You'll need to use critical thinking to determine how to remove these rows.

The dataset is small enough that you could manually drop the problem rows. But that shortcut wouldn't give you practice dealing with larger datasets, where manual removal isn't practical. So use the built-in pandas methods instead.

The how parameter in dropna() can be set to only 'any' or 'all'. Neither of those settings will get you what you need. Instead, use the thresh parameter.

The thresh parameter refers to threshold. This parameter lets you set the minimum number of non-NaN values a row or column needs to avoid being dropped by dropna(). To remove specific rows from the DataFrame, set thresh to 12.

# Drop all rows that don't have at least 12 non-NaN values.
player_df.dropna(inplace=True, thresh=12)
player_df.isna().sum()

ID             0
points         0
possessions    0
team_pace      0
GP             6
MPG            5
TS%            0
AST            0
TO             0
USG            0
ORR            0
DRR            0
REBR           0
PER            9
dtype: int64

Now only three columns are missing values. But the rows have so few missing values that it's best to keep the rows and fill in the missing values with likely numbers. You'll address these rows in another unit.

Reset the DataFrame index

Because you've dropped rows, the index in the DataFrame is compromised. You see this problem if you print out the first 10 rows of the DataFrame:

# Print the first 10 rows of the player_df DataFrame.
player_df.head(10)

Output

You see that the index counts 0 through 10, skipping 8. The row that had the index of 8 was dropped because it had more than two NaN values. In the 14 columns, the rows that had three or more NaN values didn't meet the threshold of 12 you set when you dropped rows earlier.

To fix this problem, reset the index for the DataFrame. This fix saves you from problems down the road when you're working with the DataFrame. While you're at it, take a look at the now smaller DataFrame.

# Renumber the DataFrame index to account for the dropped rows.
player_df.reset_index(drop=True, inplace=True)
player_df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 42 entries, 0 to 41
Data columns (total 14 columns):
 #   Column       Non-Null Count  Dtype  
---  ------       --------------  -----  
 0   ID           42 non-null     int64  
 1   points       42 non-null     float64
 2   possessions  42 non-null     float64
 3   team_pace    42 non-null     float64
 4   GP           36 non-null     float64
 5   MPG          37 non-null     float64
 6   TS%          42 non-null     float64
 7   AST          42 non-null     float64
 8   TO           42 non-null     float64
 9   USG          42 non-null     float64
 10  ORR          42 non-null     float64
 11  DRR          42 non-null     float64
 12  REBR         42 non-null     float64
 13  PER          33 non-null     float64
dtypes: float64(13), int64(1)
memory usage: 4.7 KB

You see that you now have only 42 players in the DataFrame. Only three columns are missing some values.

© 2021 Warner Bros. Ent. All Rights Reserved.