In this tutorial, we will cover an efficient and straightforward method for finding the percentage of missing values in a Pandas DataFrame. This tutorial is available as a video on YouTube.
Take my free Intro to Pandas course to begin your journey mastering data analysis with Python.
The final solution to this problem is not quite intuitive for most people when they first encounter it. We will slowly build up to it and also provide some other methods that get us a result that is close but not exactly what we want.
We begin by reading in the flights dataset, which contains US domestic flight information during the year 2015. Pandas defaults the number of visible columns to 20. Since there are 31 columns in this DataFrame, we change this option below.
>>> import pandas as pd
>>> pd.options.display.max_columns = 100
After inspecting the first few rows of the DataFrame, it is generally a good idea to find the total number of rows and columns with the
Pandas comes with a couple methods that get us close to what we want without getting us all the way there. The
info method prints to the screen the number of non-missing values of each column, along with the data types of each column and some other meta-data.
count method returns the number of non-missing values for each column or row. By default, it operates column-wise. It doesn’t give us any more information that is already available with the
info method. Below, we just output the last 5 values.
count method doesn’t yield us any extra information over the
info method, it returns a Pandas
Series which can be used for further processing. The
info method prints its output to the screen and returns the object
None. Let’s verify this below:
>>> info_return = flights.info()
>>> count_return = flights.count()
>>> info_return is None
isna method returns a DataFrame of all boolean values (True/False). The shape of the DataFrame does not change from the original. Each value is tested whether it is missing or not. If it is, then its new value is
True otherwise it is
>>> flights_missing = flights.isna()
The first several columns don’t have any missing values in their first few rows, but if we scroll to the end, we can see many missing values do exist.
If you are enjoying this article, consider purchasing the Master Data Analysis with Python which is an extremely comprehensive course that will help you learn pandas to do data analysis.
We can verify that result of calling the
isna method is indeed a new DataFrame with all columns having the boolean data type. Let’s call the
This guarantees us that every single value in the entire is either True of False.
Booleans in Python are treated as numeric when doing arithmetic operations. False evaluates as 0 and True evaluates as 1. Therefore, we can call the
sum method on the DataFrame, which by default sums each column independently.
>>> flights_num_missing = flights_missing.sum()
We summed up each column in the boolean DataFrame, which is summing up just False and True values. This result simply returns the number of values that are True. In our case the True values represent missing values in our original DataFrame, so we now have the number of missing values in each column.
Now that we have the total number of missing values in each column, we can divide each value in the Series by the number of rows. The built-in
len function returns the number of rows in the DataFrame.
>>> flights_num_missing / len(flights)
There is a name for the operation that we just completed. Summing up all the values in a column and then dividing by the total number is the mean.
Instead of calling the
sum method and dividing by the number of rows, we can call the
mean method directly on the
flights_missing DataFrame. This produces the exact same result as the last output.
The output isn’t shown because it is the exact same as the last.
We can put all our work together in a single line of code beginning with our flights DataFrame. We remove excess decimal noise by rounding and then multiply each value by 100 to get a percentage.
>>> flights.isna().mean().round(4) * 100
Alternatively, we can get the same result by taking the result of the
count method and dividing by the number of rows. This gives us the percentage of non-missing values in each column.
>>> flights.count() / len(flights)
From here, we can subtract each value in the Series from 1 to get the same result as the one-line solution from above. Again, the output has not been shown.
>>> 1 - flights.count() / len(flights)
Immerse yourself in my comprehensive path for mastering data science and machine learning with Python. Purchase the All Access Pass to get lifetime access to all current and future courses. Some of the courses it contains:
This same idea can be used for any boolean Series/DataFrame. Let’s see how we can find the number and percentage of flights that have arrival delays longer than 60 minutes.
First, let’s determine whether each flight has an arrival delay greater than 60 minutes by using a boolean comparison and assigning the result to a variable.
>>> gt_60 = flights['departure_delay'] > 60
Once we create a boolean Series/DataFrame, we can use the
mean methods to find the total and percentage of values that are True. In this case, it’s the number and percentage of flights with arrival delays greater than one hour.
We can now report that we have 3,626 flights or 6.2% that have arrival delays greater than one hour.
We can boil the idea down to two steps.
sumto find the number of True values and
meanto find the percentage of True values
An efficient and straightforward way exists to calculate the percentage of missing values in each column of a Pandas DataFrame. It can be non-intuitive at first, but once we break down the idea into summing booleans and dividing by the number of rows, it’s clear that we can use the
mean method to provide a direct result. This idea can be generalized to any boolean Series or DataFrame.