Missing Data In Pandas Dataframes

Want to learn more? I recommend these Python books: Python for Data Analysis, Python Data Science Handbook, and Introduction to Machine Learning with Python.

import modules

import pandas as pd
import numpy as np

Create dataframe with missing values

raw_data = {'first_name': ['Jason', np.nan, 'Tina', 'Jake', 'Amy'],
        'last_name': ['Miller', np.nan, 'Ali', 'Milner', 'Cooze'],
        'age': [42, np.nan, 36, 24, 73],
        'sex': ['m', np.nan, 'f', 'm', 'f'],
        'preTestScore': [4, np.nan, np.nan, 2, 3],
        'postTestScore': [25, np.nan, np.nan, 62, 70]}
df = pd.DataFrame(raw_data, columns = ['first_name', 'last_name', 'age', 'sex', 'preTestScore', 'postTestScore'])
df
first_name last_name age sex preTestScore postTestScore
0 Jason Miller 42.0 m 4.0 25.0
1 NaN NaN NaN NaN NaN NaN
2 Tina Ali 36.0 f NaN NaN
3 Jake Milner 24.0 m 2.0 62.0
4 Amy Cooze 73.0 f 3.0 70.0

Drop missing observations

df_no_missing = df.dropna()
df_no_missing
first_name last_name age sex preTestScore postTestScore
0 Jason Miller 42.0 m 4.0 25.0
3 Jake Milner 24.0 m 2.0 62.0
4 Amy Cooze 73.0 f 3.0 70.0

Drop rows where all cells in that row is NA

df_cleaned = df.dropna(how='all')
df_cleaned
first_name last_name age sex preTestScore postTestScore
0 Jason Miller 42.0 m 4.0 25.0
2 Tina Ali 36.0 f NaN NaN
3 Jake Milner 24.0 m 2.0 62.0
4 Amy Cooze 73.0 f 3.0 70.0

Create a new column full of missing values

df['location'] = np.nan
df
first_name last_name age sex preTestScore postTestScore location
0 Jason Miller 42.0 m 4.0 25.0 NaN
1 NaN NaN NaN NaN NaN NaN NaN
2 Tina Ali 36.0 f NaN NaN NaN
3 Jake Milner 24.0 m 2.0 62.0 NaN
4 Amy Cooze 73.0 f 3.0 70.0 NaN

Drop column if they only contain missing values

df.dropna(axis=1, how='all')
first_name last_name age sex preTestScore postTestScore
0 Jason Miller 42.0 m 4.0 25.0
1 NaN NaN NaN NaN NaN NaN
2 Tina Ali 36.0 f NaN NaN
3 Jake Milner 24.0 m 2.0 62.0
4 Amy Cooze 73.0 f 3.0 70.0

Drop rows that contain less than five observations

This is really mostly useful for time series

df.dropna(thresh=5)
first_name last_name age sex preTestScore postTestScore location
0 Jason Miller 42.0 m 4.0 25.0 NaN
3 Jake Milner 24.0 m 2.0 62.0 NaN
4 Amy Cooze 73.0 f 3.0 70.0 NaN

Fill in missing data with zeros

df.fillna(0)
first_name last_name age sex preTestScore postTestScore location
0 Jason Miller 42.0 m 4.0 25.0 0.0
1 0 0 0.0 0 0.0 0.0 0.0
2 Tina Ali 36.0 f 0.0 0.0 0.0
3 Jake Milner 24.0 m 2.0 62.0 0.0
4 Amy Cooze 73.0 f 3.0 70.0 0.0

Fill in missing in preTestScore with the mean value of preTestScore

inplace=True means that the changes are saved to the df right away

df["preTestScore"].fillna(df["preTestScore"].mean(), inplace=True)
df
first_name last_name age sex preTestScore postTestScore location
0 Jason Miller 42.0 m 4.0 25.0 NaN
1 NaN NaN NaN NaN 3.0 NaN NaN
2 Tina Ali 36.0 f 3.0 NaN NaN
3 Jake Milner 24.0 m 2.0 62.0 NaN
4 Amy Cooze 73.0 f 3.0 70.0 NaN

Fill in missing in postTestScore with each sex's mean value of postTestScore

df["postTestScore"].fillna(df.groupby("sex")["postTestScore"].transform("mean"), inplace=True)
df
first_name last_name age sex preTestScore postTestScore location
0 Jason Miller 42.0 m 4.0 25.0 NaN
1 NaN NaN NaN NaN 3.0 NaN NaN
2 Tina Ali 36.0 f 3.0 70.0 NaN
3 Jake Milner 24.0 m 2.0 62.0 NaN
4 Amy Cooze 73.0 f 3.0 70.0 NaN

Select some raws but ignore the missing data points

# Select the rows of df where age is not NaN and sex is not NaN
df[df['age'].notnull() & df['sex'].notnull()]
first_name last_name age sex preTestScore postTestScore location
0 Jason Miller 42.0 m 4.0 25.0 NaN
2 Tina Ali 36.0 f 3.0 70.0 NaN
3 Jake Milner 24.0 m 2.0 62.0 NaN
4 Amy Cooze 73.0 f 3.0 70.0 NaN