How to check missing values in pandas
Tags: pandas, python, numpy
Published: 2018-08-14 01:13:18

---
title: How to check missing values in pandas
published: true
description: An article describing how to find missing values in pandas DataFrames
tags: pandas, python, numpy
---


In this article I would like to describe how to find NaN values in a pandas DataFrame. This kind of operation can be very useful given that is common to find datasets with missing or incorrect data values.

I will be using the numpy package to generate some data with NaN values.

#### Import necessary packages


```python
import pandas as pd
import numpy as np
import platform
```


```python
print(f'Python version: {platform.python_version()} ({platform.python_implementation()})')
print(f'Pandas version: {pd.__version__}')
print(f'Numpy version: {np.__version__}')
```

    Python version: 3.6.4 (CPython)
    Pandas version: 0.23.1
    Numpy version: 1.14.5


### Generate data with NaN values


```python
num_nan = 25 # number of NaN values wanted in the generated data
np.random.seed(6765431)  # set a seed for reproducibility
A = np.random.randn(10, 10)
print(A)
```

    [[-1.56132314 -0.16954058 -0.17845422 -1.33689111 -0.19185078 -1.18617765
       0.44499302 -0.61209568  0.31170935  1.4127548 ]
     [ 0.85330488  0.68517546 -1.10140989  0.84918019  0.72802961 -0.35161197
       0.73519152  1.13145412  0.53231247  0.78103143]
     [-0.81614324  0.15906898  0.49940119 -0.09319255 -1.07837721 -0.76053341
       0.73622083 -0.45518154 -0.69194032  1.02550409]
     [-1.96339975  0.07593331 -0.16798377 -1.20398958  0.88333656  1.17908422
       0.26324698 -2.65442248 -0.31583796 -0.16065732]
     [-1.24321376 -0.89816898  0.02824671  0.15304093  0.56505667 -0.78115883
       0.74504467  1.14025258 -0.04518221 -0.83908358]
     [ 1.00967019  0.84240102  1.15043436 -0.40120489  0.00664105 -1.23247563
       0.64738343  1.66096762 -0.92556683  0.47575796]
     [ 0.96516278  1.11158059 -0.82155143  0.88900313  2.16943761 -2.05250161
       2.40156233  0.92453867 -0.24437783 -2.91029265]
     [-0.86492662  0.82443151 -0.48246862 -1.05183143 -1.15272524 -0.77170733
       0.07177233  1.02820181 -2.08947076  0.89859677]
     [-0.07263982 -0.56840867  1.30910275 -0.52846822  0.06019191 -0.61000727
       0.40782356 -0.36124333 -1.54522486 -0.07891861]
     [-1.96361682 -1.06315325 -0.45582138 -0.74566868  1.27579529 -2.46306005
       0.57022673 -0.02793746  0.78652775  1.27690195]]



```python
# Set random values to nan
A.ravel()[np.random.choice(A.size, num_nan, replace=False)] = np.nan
print(A)
```

    [[-1.56132314 -0.16954058 -0.17845422 -1.33689111 -0.19185078 -1.18617765
              nan -0.61209568  0.31170935  1.4127548 ]
     [ 0.85330488  0.68517546         nan  0.84918019         nan -0.35161197
       0.73519152         nan  0.53231247  0.78103143]
     [-0.81614324  0.15906898  0.49940119         nan -1.07837721 -0.76053341
       0.73622083         nan -0.69194032  1.02550409]
     [-1.96339975  0.07593331         nan -1.20398958  0.88333656         nan
       0.26324698         nan -0.31583796 -0.16065732]
     [-1.24321376 -0.89816898  0.02824671  0.15304093  0.56505667 -0.78115883
       0.74504467  1.14025258 -0.04518221 -0.83908358]
     [ 1.00967019  0.84240102         nan -0.40120489  0.00664105         nan
       0.64738343  1.66096762 -0.92556683  0.47575796]
     [ 0.96516278         nan -0.82155143  0.88900313  2.16943761         nan
       2.40156233         nan -0.24437783         nan]
     [-0.86492662  0.82443151 -0.48246862 -1.05183143 -1.15272524 -0.77170733
       0.07177233  1.02820181 -2.08947076         nan]
     [-0.07263982         nan  1.30910275 -0.52846822  0.06019191 -0.61000727
       0.40782356 -0.36124333         nan         nan]
     [        nan         nan         nan         nan  1.27579529 -2.46306005
              nan         nan  0.78652775  1.27690195]]



```python
# Create a DataFrame from the generated data
df = pd.DataFrame(A)
df
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>0</th>
      <th>1</th>
      <th>2</th>
      <th>3</th>
      <th>4</th>
      <th>5</th>
      <th>6</th>
      <th>7</th>
      <th>8</th>
      <th>9</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>-1.561323</td>
      <td>-0.169541</td>
      <td>-0.178454</td>
      <td>-1.336891</td>
      <td>-0.191851</td>
      <td>-1.186178</td>
      <td>NaN</td>
      <td>-0.612096</td>
      <td>0.311709</td>
      <td>1.412755</td>
    </tr>
    <tr>
      <th>1</th>
      <td>0.853305</td>
      <td>0.685175</td>
      <td>NaN</td>
      <td>0.849180</td>
      <td>NaN</td>
      <td>-0.351612</td>
      <td>0.735192</td>
      <td>NaN</td>
      <td>0.532312</td>
      <td>0.781031</td>
    </tr>
    <tr>
      <th>2</th>
      <td>-0.816143</td>
      <td>0.159069</td>
      <td>0.499401</td>
      <td>NaN</td>
      <td>-1.078377</td>
      <td>-0.760533</td>
      <td>0.736221</td>
      <td>NaN</td>
      <td>-0.691940</td>
      <td>1.025504</td>
    </tr>
    <tr>
      <th>3</th>
      <td>-1.963400</td>
      <td>0.075933</td>
      <td>NaN</td>
      <td>-1.203990</td>
      <td>0.883337</td>
      <td>NaN</td>
      <td>0.263247</td>
      <td>NaN</td>
      <td>-0.315838</td>
      <td>-0.160657</td>
    </tr>
    <tr>
      <th>4</th>
      <td>-1.243214</td>
      <td>-0.898169</td>
      <td>0.028247</td>
      <td>0.153041</td>
      <td>0.565057</td>
      <td>-0.781159</td>
      <td>0.745045</td>
      <td>1.140253</td>
      <td>-0.045182</td>
      <td>-0.839084</td>
    </tr>
    <tr>
      <th>5</th>
      <td>1.009670</td>
      <td>0.842401</td>
      <td>NaN</td>
      <td>-0.401205</td>
      <td>0.006641</td>
      <td>NaN</td>
      <td>0.647383</td>
      <td>1.660968</td>
      <td>-0.925567</td>
      <td>0.475758</td>
    </tr>
    <tr>
      <th>6</th>
      <td>0.965163</td>
      <td>NaN</td>
      <td>-0.821551</td>
      <td>0.889003</td>
      <td>2.169438</td>
      <td>NaN</td>
      <td>2.401562</td>
      <td>NaN</td>
      <td>-0.244378</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>7</th>
      <td>-0.864927</td>
      <td>0.824432</td>
      <td>-0.482469</td>
      <td>-1.051831</td>
      <td>-1.152725</td>
      <td>-0.771707</td>
      <td>0.071772</td>
      <td>1.028202</td>
      <td>-2.089471</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>8</th>
      <td>-0.072640</td>
      <td>NaN</td>
      <td>1.309103</td>
      <td>-0.528468</td>
      <td>0.060192</td>
      <td>-0.610007</td>
      <td>0.407824</td>
      <td>-0.361243</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>9</th>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>1.275795</td>
      <td>-2.463060</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>0.786528</td>
      <td>1.276902</td>
    </tr>
  </tbody>
</table>
</div>



### Check for NaN values

Now that we have some data to operate on let's see the different ways we can check for missing values.

There are two methods of the DataFrame object that can be used: `DataFrame#isna()` and `DataFrame#isnull()`. But if you check the source code it seems that `isnull()` is only an alias for the `isna()` method. To keep it simple I will only use the `isna()` method as we would get the same result using `isnull()`.


```python
df.isna()
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>0</th>
      <th>1</th>
      <th>2</th>
      <th>3</th>
      <th>4</th>
      <th>5</th>
      <th>6</th>
      <th>7</th>
      <th>8</th>
      <th>9</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
    </tr>
    <tr>
      <th>1</th>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>False</td>
    </tr>
    <tr>
      <th>2</th>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>False</td>
    </tr>
    <tr>
      <th>3</th>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>False</td>
    </tr>
    <tr>
      <th>4</th>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
    </tr>
    <tr>
      <th>5</th>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
    </tr>
    <tr>
      <th>6</th>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>True</td>
    </tr>
    <tr>
      <th>7</th>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>True</td>
    </tr>
    <tr>
      <th>8</th>
      <td>False</td>
      <td>True</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>True</td>
    </tr>
    <tr>
      <th>9</th>
      <td>True</td>
      <td>True</td>
      <td>True</td>
      <td>True</td>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>True</td>
      <td>False</td>
      <td>False</td>
    </tr>
  </tbody>
</table>
</div>



As it can be seen above when we use the `isna()` method it returns a DataFrame with boolean values, where `True` indicates NaN values and `False` otherwise.

If we wanted to know how many missing values there are on each row or column we could use the `DataFrame#sum()` method:


```python
df.isna().sum(axis='rows')  # 'rows' or 0
```




    0    1
    1    3
    2    4
    3    2
    4    1
    5    3
    6    2
    7    5
    8    1
    9    3
    dtype: int64




```python
df.isna().sum(axis='columns')  # 'columns' or 1
```




    0    1
    1    3
    2    2
    3    3
    4    0
    5    2
    6    4
    7    1
    8    3
    9    6
    dtype: int64



To simply know the total number of missing values we can call `sum()` again:


```python
df.isna().sum().sum()
```




    25



If we simply wanna know if there is any missing value with no care for the quantity we can simply use the `any()` method:


```python
df.isna().any()  # can also receive axis='rows' or 'columns'
```




    0    True
    1    True
    2    True
    3    True
    4    True
    5    True
    6    True
    7    True
    8    True
    9    True
    dtype: bool



Calling it again we have a single boolean output:


```python
df.isna().any().any()
```




    True



Besides the `isna()` method we also have the `notna()` method which is its boolean inverse. Applying it we can get the number of values that are not missing or simply if all values are not missing (but using the `all()` method instead of `any()`).


```python
print(df.notna().sum().sum())  # not missing
print(df.notna().all().all())
```

    75
    False


Note 1: in the examples, it was used the DataFrame methods to check for missing values, but the pandas package has its own functions with the same purpose that can be applied to other objects. Example:


```python
print(pd.isna([1, 2, np.nan]))
print(pd.notna([1, 2, np.nan]))
```

    [False False  True]
    [ True  True False]


Note 2: the methods applied here on DataFrame objects are also available for Series and Index objects.

### Time comparison

Comparing the time taken by the two methods we can see that using `any()` is faster but `sum()` will give us the additional information about how many missing values there are.


```python
%timeit df.isna().any().any()
```

    333 µs ± 33.1 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)



```python
%timeit df.isna().sum().sum()
```

    561 µs ± 97.7 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)


### Dealing with missing values

Two easy ways to deal with missing values are removing them or filling them with some value. These can be achieved with the `dropna()` and `fillna()` methods.

The `dropna()` method will return a DataFrame without the rows and columns containing missing values.


```python
df.dropna()
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>0</th>
      <th>1</th>
      <th>2</th>
      <th>3</th>
      <th>4</th>
      <th>5</th>
      <th>6</th>
      <th>7</th>
      <th>8</th>
      <th>9</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>4</th>
      <td>-1.243214</td>
      <td>-0.898169</td>
      <td>0.028247</td>
      <td>0.153041</td>
      <td>0.565057</td>
      <td>-0.781159</td>
      <td>0.745045</td>
      <td>1.140253</td>
      <td>-0.045182</td>
      <td>-0.839084</td>
    </tr>
  </tbody>
</table>
</div>



The `fillna()` method will return a DataFrame with the missing values filled with a specified value.


```python
df.fillna(value=5)
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>0</th>
      <th>1</th>
      <th>2</th>
      <th>3</th>
      <th>4</th>
      <th>5</th>
      <th>6</th>
      <th>7</th>
      <th>8</th>
      <th>9</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>-1.561323</td>
      <td>-0.169541</td>
      <td>-0.178454</td>
      <td>-1.336891</td>
      <td>-0.191851</td>
      <td>-1.186178</td>
      <td>5.000000</td>
      <td>-0.612096</td>
      <td>0.311709</td>
      <td>1.412755</td>
    </tr>
    <tr>
      <th>1</th>
      <td>0.853305</td>
      <td>0.685175</td>
      <td>5.000000</td>
      <td>0.849180</td>
      <td>5.000000</td>
      <td>-0.351612</td>
      <td>0.735192</td>
      <td>5.000000</td>
      <td>0.532312</td>
      <td>0.781031</td>
    </tr>
    <tr>
      <th>2</th>
      <td>-0.816143</td>
      <td>0.159069</td>
      <td>0.499401</td>
      <td>5.000000</td>
      <td>-1.078377</td>
      <td>-0.760533</td>
      <td>0.736221</td>
      <td>5.000000</td>
      <td>-0.691940</td>
      <td>1.025504</td>
    </tr>
    <tr>
      <th>3</th>
      <td>-1.963400</td>
      <td>0.075933</td>
      <td>5.000000</td>
      <td>-1.203990</td>
      <td>0.883337</td>
      <td>5.000000</td>
      <td>0.263247</td>
      <td>5.000000</td>
      <td>-0.315838</td>
      <td>-0.160657</td>
    </tr>
    <tr>
      <th>4</th>
      <td>-1.243214</td>
      <td>-0.898169</td>
      <td>0.028247</td>
      <td>0.153041</td>
      <td>0.565057</td>
      <td>-0.781159</td>
      <td>0.745045</td>
      <td>1.140253</td>
      <td>-0.045182</td>
      <td>-0.839084</td>
    </tr>
    <tr>
      <th>5</th>
      <td>1.009670</td>
      <td>0.842401</td>
      <td>5.000000</td>
      <td>-0.401205</td>
      <td>0.006641</td>
      <td>5.000000</td>
      <td>0.647383</td>
      <td>1.660968</td>
      <td>-0.925567</td>
      <td>0.475758</td>
    </tr>
    <tr>
      <th>6</th>
      <td>0.965163</td>
      <td>5.000000</td>
      <td>-0.821551</td>
      <td>0.889003</td>
      <td>2.169438</td>
      <td>5.000000</td>
      <td>2.401562</td>
      <td>5.000000</td>
      <td>-0.244378</td>
      <td>5.000000</td>
    </tr>
    <tr>
      <th>7</th>
      <td>-0.864927</td>
      <td>0.824432</td>
      <td>-0.482469</td>
      <td>-1.051831</td>
      <td>-1.152725</td>
      <td>-0.771707</td>
      <td>0.071772</td>
      <td>1.028202</td>
      <td>-2.089471</td>
      <td>5.000000</td>
    </tr>
    <tr>
      <th>8</th>
      <td>-0.072640</td>
      <td>5.000000</td>
      <td>1.309103</td>
      <td>-0.528468</td>
      <td>0.060192</td>
      <td>-0.610007</td>
      <td>0.407824</td>
      <td>-0.361243</td>
      <td>5.000000</td>
      <td>5.000000</td>
    </tr>
    <tr>
      <th>9</th>
      <td>5.000000</td>
      <td>5.000000</td>
      <td>5.000000</td>
      <td>5.000000</td>
      <td>1.275795</td>
      <td>-2.463060</td>
      <td>5.000000</td>
      <td>5.000000</td>
      <td>0.786528</td>
      <td>1.276902</td>
    </tr>
  </tbody>
</table>
</div>



### References:

- [Create sample numpy array with randomly placed NaNs (StackOverflow)](https://stackoverflow.com/questions/32182409/create-sample-numpy-array-with-randomly-placed-nans)
- [How to check if any value is NaN in a Pandas DataFrame (StackOverflow)](https://stackoverflow.com/questions/29530232/how-to-check-if-any-value-is-nan-in-a-pandas-dataframe)
- [pandas.isnull](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.isnull.html)
- [pandas.isna](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.isna.html)
- [pandas.notna](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.notna.html)
- [pandas.DataFrame.dropna](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.dropna.html)
- [pandas.DataFrame.fillna](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.fillna.html)