Section 5.2, Dealing with Missing Values.
Oftentimes you'll come across datasets that have missing values due to deficiencies in the data collection process,
evolving needs over time, or data processing and storage issues.
Whatever the cause, you'll need to decide what to do to eliminate these data quality issues.
When fixing missing values, you have three options to choose from.
Remove the rows, columns with missing data from your dataset.
Assign the missing values a default value.
Impute the missing values.
For example, imagine that you have a dataset containing height measurements of various people,
and a few of those height measurements are missing.
Depending on the objectives of your analysis, you could decide to either throw out the records with missing height measurements,
or assume that those people are about average height by finding the arithmetic mean of the measurements you have.
Unfortunately, there isn't a silver bullet approach to selecting the best method to deal with missing values.
It largely depends on the context and domain of the missing data.
A good rule of thumb is to work with the stakeholders who will be interpreting and using your analyses
to come up with an agreed-upon approach that makes the most sense in the context of the problem you're trying to solve.
However, to give you some options, we'll cover how to do all three in this section.
Section 5.2.1, Counting Missing Values in a Dataframe.
We'll start by having a look at what columns in the NYC parking ticket data have missing values.
Listing 5.11, Calculating the Percentage of Missing Values by Column.
Listing 5.11 should look a bit familiar.
We did the same thing on just the 2017 data in Chapter 2.
To recap what's going on here, the first line creates a new series that contains a count of missing values by column.
The isNull method scans each row and returns true if a missing value is found and false if a missing value isn't found.
The sum method counts up all the true values to give us a total count of missing rows per column.
We then take that series of counts and divide it by the number of rows in the dataframe using NYCDataRaw.index.size and multiply each value by 100.
Calling the compute method triggers the calculation and stores the result as a Pandas series named Percent Missing.
Section 5.2.2, Dropping Columns with Missing Values.
Now that we know what we have to work with, we'll start by dropping any columns that have more than 50% of their values missing.
Listing 5.12, Dropping Columns that Have More Than 50% Missing Values.
In Listing 5.12, we start by filtering the percent missing series to find the names of the columns that have 50% or more missing values.
That produces a list that looks like this.
See this list?
We then use the drop method you learned in the last section to drop the columns from the dataframe and save the result as the dataframe called NYCDataCleanStage1.
We picked 50% arbitrarily here, but it's quite typical to drop columns with a very high amount of missing data.
Take the double parking violation column for example. It's missing 99.9% of its values.
We don't likely stand to gain much information by keeping such a sparse column around, so we'll remove it from our dataset.
Section 5.2.3, Imputing Missing Values.
When you have columns that only have a small amount of missing data, it's more appropriate to discard the rows that have missing data.
Before we do that though, we'll impute a value for the vehicle color column.
Imputing means we will use the data that we do have to make a reasonable guess at what the missing data might be.
In this instance, we'll find the most frequently occurring color in the dataset.
While this assumption might not always hold true, using the most frequently occurring value in a dataset maximizes the probability that you chose correctly.
Listing 5.13, Imputing Missing Values.
Listing 5.13 aims to fill in missing values in the vehicle color column by assuming that they were the most common color in the dataset.
Filling in missing values by using the most common element of a categorical variable, or the arithmetic mean of a continuous variable,
is a common way to deal with missing values in a way that minimizes the effect on the statistical distribution of the data.
On the first line of listing 5.13, we select the vehicle color column using the column selector and use the value counts method,
which counts the unique occurrences of data.
Here's what the contents of Count of Vehicle Colors look like.
See this code.
As you can see, the results of value counts give us a series containing each color on the index
and how many times the color appeared in the data as values.
In the second line of listing 5.13, we sort all the vehicle colors from most occurrences to fewest
and grab the name of the most common occurring color.
As you can see, GY, gray, is the most commonly occurring color code with over 6.2 million occurrences.
On the last line of listing 5.13, we use the fillInA method to replace the missing colors with GY.
FillInA takes a dictionary of key-value pairs where the name of each column you want to fill is used as a key
and what you want to fill the occurrences of missing values with is used as a value.
Columns that you don't specify in the dictionary will not be modified.
Now that we've filled in missing values in the vehicle color column,
we'll take care of the other low-missing value columns by dropping the rows that are missing values in those columns.
Listing 5.14, Dropping Rows with Missing Data
Listing 5.14 starts by finding all the columns that have missing values, but no more than 5% missing values.
We put this result in a list called Rows to Drop.
The contents of the list look like this.
See this code.
Note that we're not going to drop these columns.
We're just going to drop any rows from our data frame that have missing values in these columns.
Also notice that vehicle color shows up.
However, because we're going to apply our dropping function to NYC Data Clean Stage 2,
no rows will be dropped on account of missing vehicle colors because they've already been filled in.
To do the actual dropping, we use the DropNA method on the data frame.
If we don't specify any arguments, DropNA will drop all rows with any missing values, so use with caution.
The subset argument allows us to specify the columns Dask will check for missing values.
If a row has missing values in columns that aren't specified, Dask won't drop them.
Section 5.2.5, Imputing Multiple Columns with Missing Values
We're almost done now.
The last thing we'll do is fill in our remaining columns that have missing data with default values.
One thing we'll need to make sure of is that the default value we set for a column is appropriate for that column's data type.
Let's check what columns we have left to clean up and what their data types are.
Listing 5.15, Finding the Data Types of the Remaining Columns
The first thing we'll do in Listing 5.15, like some of the listings before it, is find the columns that we still need to clean up.
For any columns that have more than 5% missing values and less than 50% missing values, we'll fill the missing values with a default value.
That list of columns is stored in the Remaining Columns to Clean variable,
and we use that with the dtypes parameter of the NYCDataRaw data frame to find the data type of each column.
Here's what the output looks like.
See this output.
As you can see, all the columns we have left to clean up are strings.
You may be wondering why they are displayed as object instead of np.str.
This is just cosmetic. Dask only explicitly shows numeric, int, float, and so on, data types.
Any non-numeric data types will show as object.
We'll fill in each column with the string unknown to signify that the value was missing.
We'll use fillna again to fill in the values, so we need to prepare a dictionary with the values for each column.
Listing 5.16, making a dictionary of values for fillna.
Listing 5.16 show how to build this dictionary.
We're simply taking each value in the Remaining Columns to Clean list and spitting out a tuple of the column name and the string unknown.
Finally, we convert the list of tuples to a dictionary, which yields an object that looks like this.
See this code?
Now that we have a dictionary representing each column we want to fill in and the value to fill with, we can pass it to fillna.
Listing 5.17, filling the data frame with default values.
Nice and simple. We've now built up our final data frame, NYC Data Clean Stage 4,
which has been sequentially built up by starting from NYC Data Raw and lazily applying each of the four missing value techniques on various columns.
Now it's time to check our work.
Listing 5.18, checking the success of the filling, dropping operations.
In Listing 5.18, we kick off the computation and get back a count of how many missing values remain after applying all our transformations.
Looks like we got everything.
If you're running the code as you're reading, you might have noticed that the computation took some time to complete.
Now would be an opportune time to persist the data frame.
Remember that persisting the data frame will pre-compute the work you've done up to this point and store it in a processed state in memory.
This will make sure that we don't have to re-compute all those transformations as we continue our analysis.
The last line of Listing 5.18 reviews how to do that.
Now that we've taken care of all the missing values, we'll look at a few methods for cleaning up erroneous-looking values.