Working with Missing Values in Pandas

Table of Contents

• Load and Prepare Data
• Visualize Data

Here we’ll see how to work with missing values in a Pandas DataFrame on a data set from the World Bank Open Data of the Global Economic Monitor.

Load and Prepare Data

import pandas as pd

path = 'data/GemDataEXTR/Emerging Market Bond Index (JPM Total Return Index).xlsx'
df = pd.read_excel(path, sheetname='annual')

# Drop first row
df = df.iloc[1:]

# Convert index column to integer
df.index = df.index.map(int)
df.head(12)

As you can see the data set is filled with quite a few NaN values. In order for us to work properly with the data set we will drop all rows where all values missing by using the pandas.DataFrame.dropna function.

df.dropna(axis=0, how='all', inplace=True)

We can check with Pandas how many values are still null by summing over result of the pandas.isnull function. For the summing we use pandas.DataFrame.sum function to sum over an axis (by default axis 0) which returns us a pandas.Series. Note that in the sum all True values are viewed as 1 and all False values as 0. Now we can see the first 10 countries with their number of missing values.

df.isnull().sum()[:10]

Argentina                0
Bulgaria                 4
Belarus                 19
Belize                  18
Brazil                   0
Chile                   18
China                   18
Cote d'Ivoire           18
Colombia                 0
Developing Countries    18
dtype: int64

This function is also a great way to get a quick overview in which columns are missing values hidden.

Say, we want to get all the countries that have no missing values. We get those by first getting the Series with the number of each of the missing values from before and then applying a condition with the pandas.Series.where function followerd by dropping all the resulting countries which are NaN. Another way is to filter the Series is by using boolean variables s == 0 which evaluates the whole Series if each value is equals to zero by a boolean value. This boolean Series can be then in turn used to filter the Series by applying s[s == 0] which does the same thing as the previous example.

s = df.isnull().sum()
countries = s.where(s == 0).dropna(how='any') # eqivalent
countries = s[s == 0]                         # eqivalent

Visualize Data

Finally we will plot part of the data set with the pandas.DataFrame.plot function and we will compare the Emerging Market Bond Index (JPM Total Return Index) for all countries that have no missing values in the data set. We do that by filtering this time the DataFrame by the columns with the country names that we extracted perviously.

%matplotlib inline
import matplotlib

# Specify figure size
matplotlib.rcParams['figure.figsize'] = (16, 9)

ax = df[countries.index].plot(title='Emerging Market Bond Index (JPM Total Return Index)');
ax.set_xlabel('Year');
ax.set_ylabel('JPM Total Return Index');

# Force integer labels for x-axis
ax.xaxis.set_major_locator(matplotlib.ticker.MaxNLocator(integer=True));