One might want to filter the pandas dataframe based on a column such that we would like to keep the rows of data frame where the specific column don’t have data and not NA.
Let us consider a toy example to illustrate this. Let us first load the pandas library and create a pandas dataframe from multiple lists.
# import pandas import pandas as pd
Our toy dataframe contains three columns and three rows. The column Last_Name has one missing value, denoted as “None”. The column Age has one missing value as well.
# create a pandas dataframe from multiple lists
>df = pd.DataFrame({'Last_Name': ['Smith', None, 'Brown'],
'First_Name': ['John', 'Mike', 'Bill'],
'Age': [35, 45, None]})
Since the dataframe is small, we can print it and see the data and missing values. Note that pandas deal with missing data in two ways. The missing data in Last_Name is represented as None and the missing data in Age is represented as NaN, Not a Number. This is because pandas handles the missing values in numeric as NaN and other objects as None. Don’t worry, pandas deals with both of them as missing values.
>print(df) Age First_Name Last_Name 0 35.0 John Smith 1 45.0 Mike None 2 NaN Bill Brown
How to filter out rows based on missing values in a column?
To filter out the rows of pandas dataframe that has missing values in Last_Namecolumn,
we will first find the index of the column with non null values with pandas notnull() function. It will return a boolean series, where True for not null and False for null values or missing values.
>df.Last_Name.notnull() 0 True 1 False 2 True Name: Last_Name, dtype: bool
We can use this boolean series to filter the dataframe so that it keeps the rows with no missing data for the column ‘Last_Name’.
>df[df.Last_Name.notnull()] Age First_Name Last_Name 0 35.0 John Smith 2 NaN Bill Brown
How to filter out all rows with missing values?
If you want to filter out all rows containing one or more missing values, pandas’ dropna() function is useful for that
# drop rows with missing value
>df.dropna()
Age First_Name Last_Name
0 35.0 John Smith
Note that dropna() drops out all rows containing missing data. In this case there is only one row with no missing values. By default, dropna() drop rows with missing values. If you want to drop the columns with missing values, we can specify axis =1
#drop column with missing value >df.dropna(axis=1) First_Name 0 John 1 Mike 2 Bill
In this example, the only column with missing data is the First_Name column. So we end up with a dataframe with a single column after using axis=1 with dropna().
