Find all Columns with NaN Values in Pandas DataFrame

Here are 4 ways to find all columns that contain NaN values in Pandas DataFrame:

(1) Use isna() to find all columns with NaN values:

df.isna().any()

(2) Use isnull() to find all columns with NaN values:

df.isnull().any()

(3) Use isna() to select all columns with NaN values:

df[df.columns[df.isna().any()]]

(4) Use isnull() to select all columns with NaN values:

df[df.columns[df.isnull().any()]]

In the next section, you’ll see how to apply the above approaches in practice.

Steps to Find all Columns with NaN Values in Pandas DataFrame

Step 1: Create a DataFrame

For example, let’s create a DataFrame with 4 columns:

import pandas as pd
import numpy as np

data = {'Column_A': [1,2,3,4,5,np.nan,6,7,np.nan],
        'Column_B': [11,22,33,44,55,66,77,88,99],
        'Column_C': ['a','b',np.nan,np.nan,'c','d','e',np.nan,'f'],
        'Column_D': ['aa','bb','cc','dd','ee','ff','gg','hh','ii']
        }

df = pd.DataFrame(data,columns=['Column_A','Column_B','Column_C','Column_D'])

print (df)

Notice that some of the columns in the DataFrame contain NaN values:

DataFrame with NaN values

In the next step, you’ll see how to automatically (rather than visually) find all the columns with the NaN values.

Step 2: Find all Columns with NaN Values in Pandas DataFrame

You can use isna() to find all the columns with the NaN values:

df.isna().any()

For our example, the complete Python code would look as follows:

import pandas as pd
import numpy as np

data = {'Column_A': [1,2,3,4,5,np.nan,6,7,np.nan],
        'Column_B': [11,22,33,44,55,66,77,88,99],
        'Column_C': ['a','b',np.nan,np.nan,'c','d','e',np.nan,'f'],
        'Column_D': ['aa','bb','cc','dd','ee','ff','gg','hh','ii']
        }

df = pd.DataFrame(data,columns=['Column_A','Column_B','Column_C','Column_D'])

nan_values = df.isna().any()

print (nan_values)

As you can see, for both ‘Column_A‘ and ‘Column_C‘ the outcome is ‘True’ which means that those two columns contain NaNs:

Find all Columns with NaN Values in Pandas DataFrame

Alternatively, you’ll get the same results by using isnull():

df.isnull().any()

Here is the complete code:

import pandas as pd
import numpy as np

data = {'Column_A': [1,2,3,4,5,np.nan,6,7,np.nan],
        'Column_B': [11,22,33,44,55,66,77,88,99],
        'Column_C': ['a','b',np.nan,np.nan,'c','d','e',np.nan,'f'],
        'Column_D': ['aa','bb','cc','dd','ee','ff','gg','hh','ii']
        }

df = pd.DataFrame(data,columns=['Column_A','Column_B','Column_C','Column_D'])

nan_values = df.isnull().any()

print (nan_values)

As before, both ‘Column_A’ and ‘Column_C’ contain NaN values:

How to Find all Columns with NaN Values in Pandas DataFrame

Select all Columns with NaN Values in Pandas DataFrame

What if you’d like to select all the columns with the NaN values?

In that case, you can use the following approach to select all those columns with NaNs:

df[df.columns[df.isna().any()]]

Therefore, the new Python code would look as follows:

import pandas as pd
import numpy as np

data = {'Column_A': [1,2,3,4,5,np.nan,6,7,np.nan],
        'Column_B': [11,22,33,44,55,66,77,88,99],
        'Column_C': ['a','b',np.nan,np.nan,'c','d','e',np.nan,'f'],
        'Column_D': ['aa','bb','cc','dd','ee','ff','gg','hh','ii']
        }

df = pd.DataFrame(data,columns=['Column_A','Column_B','Column_C','Column_D'])

nan_values = df[df.columns[df.isna().any()]]

print (nan_values)

You’ll now get the complete two columns that contain the NaN values:

Columns

Optionally, you can use isnull() to get the same results:

import pandas as pd
import numpy as np

data = {'Column_A': [1,2,3,4,5,np.nan,6,7,np.nan],
        'Column_B': [11,22,33,44,55,66,77,88,99],
        'Column_C': ['a','b',np.nan,np.nan,'c','d','e',np.nan,'f'],
        'Column_D': ['aa','bb','cc','dd','ee','ff','gg','hh','ii']
        }

df = pd.DataFrame(data,columns=['Column_A','Column_B','Column_C','Column_D'])

nan_values = df[df.columns[df.isnull().any()]]

print (nan_values)

Run the code, and you’ll get the same two columns with the NaN values:

Columns with values

You can visit the Pandas Documentation to learn more about isna.