Pandas Data Cleaning: Remove rows with empty data or missing values
A trivia task that for data cleaning or machine learning model preparation is to remove rows or columns that have empty data or missing values.
Example of removing rows.
Here are two quick ways to do it.
- Use dropna() that when you don’t care which rows, just drop them.
2. Use drop() with a list of row numbers. Sometimes you do care which rows. Keep a copy of row numbers that has NaN. So we define a custom empty_rows() helper function first.
If readability is really not what you concern, I also make a one liner function for you as below: (Remeber ‘Readability counts’ from Zen? I would say it is ok for a quick helper function as long as you documented properly)
(if the dataset is hugh, runing in iterrows() loops may cause performance issue. You should consider subset to chunk then proceed and merge end results)
Then we use drop() to take list of row number to remove. We keep a copy of the list, in case we want to audit it later or manually alter it. For example, some rows are still valuable even they were missing some data. (Always case by case, depends on your business logic)
A more ‘pandas’ way is to to subset based on that condition, then get index list of that subset. .e.g
This approach utilizes numpy vectorization feature. It actually performs much faster than above manual iteration. Recommended.
And another one-liner to subset any row(s) contain missing / empty value:
Drop rows that with particular colums contain missing value:
It will remove rows whose ‘costPrice’ and/or ‘SuggessedPrice’ missing.
Dropping is quick, always bear in mind how to drop, whether to drop, should I drop.
Unnecessry dropping may impact your ultimate model test data and outcome.
pandas.DataFrame.dropna#
See the User Guide for more on which values are considered missing, and how to work with missing data.
Parameters axis <0 or ‘index’, 1 or ‘columns’>, default 0
Determine if rows or columns which contain missing values are removed.
0, or ‘index’ : Drop rows which contain missing values.
1, or ‘columns’ : Drop columns which contain missing value.
Changed in version 1.0.0: Pass tuple or list to drop on multiple axes. Only a single axis is allowed.
Determine if row or column is removed from DataFrame, when we have at least one NA or all NA.
Drop rows containing empty cells from a pandas DataFrame
I have a pd.DataFrame that was created by parsing some excel spreadsheets. A column of which has empty cells. For example, below is the output for the frequency of that column, 32320 records have missing values for Tenant.
I am trying to drop rows where Tenant is missing, however .isnull() option does not recognize the missing values.
The column has data type «Object». What is happening in this case? How can I drop records where Tenant is missing?
8 Answers 8
Pandas will recognise a value as null if it is a np.nan object, which will print as NaN in the DataFrame. Your missing values are probably empty strings, which Pandas doesn’t recognise as null. To fix this, you can convert the empty stings (or whatever is in your empty cells) to np.nan objects using replace() , and then call dropna() on your DataFrame to delete rows with null tenants.
To demonstrate, we create a DataFrame with some random values and some empty strings in a Tenants column:
Pandas Dropna – How to drop missing values?
In reality, majority of the datasets collected contain missing values due to manual errors, unavailability of information, etc. Although there are different ways for handling missing values, sometimes you have no other option but to drop those rows from the dataset. A common method for dropping rows and columns is using the pandas `dropna` function.
In this article, you will learn about the different functionalities of this method for dropping rows with missing values followed by some practical tips for using pandas dropna method.
Creating a Basic DataFrame
The pandas dropna function
- Syntax: pandas.DataFrame.dropna(axis = 0, how =’any’, thresh = None, subset = None, inplace=False)
- Purpose: To remove the missing values from a DataFrame.
- Parameters:
- axis:0 or 1 (default: 0). Specifies the orientation in which the missing values should be looked for. Pass the value 0 to this parameter search down the rows. Pass the value 1 to this parameter to look across columns.
- how:‘any’ or ‘all’ (default:’any’). If it is set to ‘any’, the row/column that has atleast one missing value will be dropped. If it is set to ‘all’, only the rows/columns in which all values are missing will be dropped.
- thresh:Integer (default: None). Maximum number of missing values in a row or column which will be ignored by this function.
- subset:array (default: None). It is used to specify the particular labels along the axis which is not specified in the axis parameter in which missing values should be looked for.
- inplace:Boolean (default: False). Denotes if the missing values should be dropped in the original DataFrame or if a new DataFrame having the missing values dropped should be returned.
Dropping rows having at least 1 missing value
For removing all rows which have at least one missing value, the value of the axis parameter should be 0 and the how parameter should be set to ‘any’. Since these are the default values of the parameter, you do not need to pass any arguments to the function. This is the simplest usecase of pandas dropna function.
Dropping columns having at least 1 missing values
For removing all columns which have at least one missing value, pass the value 1 to the axis parameter to dropna() .
Dropping rows or columns only when all values are null
With the help of this function, you can also drop all the rows and columns where all the values are null values.
Dropping all rows where all the values are null values
To drop all the rows which contain only missing values, pass the value 0 to the axis parameter and set the value how='all' .
Here, none of them contained missing values in all columns. Hence, no rows were dropped.
Dropping all columns where all the values are null values
In particular cases, you might encounter columns full of null values (information not collected). These definitely have to be dropped
For dropping all the columns which contain only missing values, pass the value 1 to the axis parameter and the value ‘all’ to the how parameter.
How to drop rows/columns that contain missing values above a certain threshold?
In certain cases, you don’t want to drop a row that has very few missing values, so pandas dropna gives you an option to set threshold. To remove only those rows or columns which have missing values above a certain threshold, you need to pass a threshold value to the thresh parameter.
The `thresh` parameter represent the number of non-missing values needed to retain the row/column.
Dropping rows if missing values are present only in specific columns
DataFrame.dropna() also gives you the option to remove the rows by searching for null or missing values on specified columns.
To search for null values in specific columns, pass the column names to the subset parameter. It can take a list of column names or column positions.
Let’s look at the column names.
Practical Tips
- In case of memory constraints, use the inplace parameter. Set its value as True so that the changes will take place in the original DataFrame itself and a new DataFrame will not be created.
- Please keep in mind that while dropping rows or columns using the how parameter and setting its value as ‘all’ , the function will remove only those labels where all its values are missing or null values.
- While removing columns, you can also pass row labels to the subset parameter to search for rows that contain missing values.
Example:
Complete Machine Learning Mastery
Do you want to learn AI / ML like experienced Data Scientists?
Go from Zero to Job-ready with recognized certification. Solve projects with real company data and get proficient in AI/ML it's true sense.
Complete Machine Learning Mastery
Go from Zero to Job-ready with recognized certification. Get the mindset, the confidence and the skills that make Data Scientist highly valuable.
Conclusion
In this article, you learned about pandas dropna using the DataFrame.dropna() and using its various parameters such as subset, how and thresh. If you would like to learn more about various functions of pandas library, checkout 101 Pandas Exercises for Data Analysis.
We have also have the most comprehensive Pandas for Data Science course that covers Pandas in depth.
Test Your Knowledge
Q1: The default configuration of DataFrame.dropna() removes all the rows having missing values from the DataFrame. True or False?
Answer: True
Q2: Which parameter is used to specify the row or column labels to be included while removing the missing value?
Answer: The subset function.
Q3: Write the code to drop the rows that have more than two missing values from the DataFrame df.
Answer: df.dropna(axis=0,thresh=2)
Q4: Write the code to remove only those columns from the DataFrame that contain only null values.
Answer: df.dropna(axis=1,how='all')
Q5: Write the code to remove rows from the DataFrame df especially in those rows where the value of the column ‘col_3’ is null.