Chapter 10 — Data Cleaning and Preparation

Real-world data is messy. Data cleaning — handling missing values, duplicates, wrong types, and outliers — typically consumes the majority of a data scientist's time. This chapter equips you with the essential techniques.

Learning Objectives

Prerequisites / Imports

In [1]:
import pandas as pd
import numpy as np
import seaborn as sns

1 A Synthetic Messy Dataset

We construct a small, messy table to practice on.

In [1]:
df = pd.DataFrame({
    'name': ['  Ada ','Bo',' Cy ','Bo','Di', np.nan],
    'age': [36, 28, 44, 28, np.nan, 31],
    'email': ['ada@x.com','BO@x.com','cy@x.com','bo@x.com','di@x.com','di@x.com'],
    'joined': ['2021-05-01','2020/06/15','2022-01-20','2020-06-15','2023-03-03','2023-03-03'],
})
df
name age email joined
0 Ada 36.0 ada@x.com 2021-05-01
1 Bo 28.0 BO@x.com 2020/06/15
2 Cy 44.0 cy@x.com 2022-01-20
3 Bo 28.0 bo@x.com 2020-06-15
4 Di NaN di@x.com 2023-03-03
5 NaN 31.0 di@x.com 2023-03-03

2 Missing Values

isna() finds missing values; fillna or dropna handles them.

In [1]:
print('missing counts:'); print(df.isna().sum())
print('\nrows with any missing:'); print(df[df.isna().any(axis=1)])

missing counts:
name      1
age       1
email     0
joined    0
dtype: int64

rows with any missing:
  name   age     email      joined
4   Di   NaN  di@x.com  2023-03-03
5  NaN  31.0  di@x.com  2023-03-03
In [1]:
df['age'] = df['age'].fillna(df['age'].median())
df
name age email joined
0 Ada 36.0 ada@x.com 2021-05-01
1 Bo 28.0 BO@x.com 2020/06/15
2 Cy 44.0 cy@x.com 2022-01-20
3 Bo 28.0 bo@x.com 2020-06-15
4 Di 31.0 di@x.com 2023-03-03
5 NaN 31.0 di@x.com 2023-03-03

3 Duplicates

Identify and drop duplicate rows.

In [1]:
print('duplicates:', df.duplicated().sum())
df = df.drop_duplicates().reset_index(drop=True)
df

duplicates: 0
name age email joined
0 Ada 36.0 ada@x.com 2021-05-01
1 Bo 28.0 BO@x.com 2020/06/15
2 Cy 44.0 cy@x.com 2022-01-20
3 Bo 28.0 bo@x.com 2020-06-15
4 Di 31.0 di@x.com 2023-03-03
5 NaN 31.0 di@x.com 2023-03-03

4 Cleaning Strings

Strip whitespace, standardize case, and normalize categories.

In [1]:
df['name'] = df['name'].str.strip().str.title()
df['email'] = df['email'].str.lower()
df
name age email joined
0 Ada 36.0 ada@x.com 2021-05-01
1 Bo 28.0 bo@x.com 2020/06/15
2 Cy 44.0 cy@x.com 2022-01-20
3 Bo 28.0 bo@x.com 2020-06-15
4 Di 31.0 di@x.com 2023-03-03
5 NaN 31.0 di@x.com 2023-03-03

5 Type Conversion

Convert strings to datetime and numbers to the right type.

In [1]:
df['joined'] = pd.to_datetime(df['joined'], errors='coerce')
print(df.dtypes)
df

name              object
age              float64
email             object
joined    datetime64[ns]
dtype: object
name age email joined
0 Ada 36.0 ada@x.com 2021-05-01
1 Bo 28.0 bo@x.com NaT
2 Cy 44.0 cy@x.com 2022-01-20
3 Bo 28.0 bo@x.com 2020-06-15
4 Di 31.0 di@x.com 2023-03-03
5 NaN 31.0 di@x.com 2023-03-03

6 Detecting Outliers with IQR

Values below $Q1 - 1.5\cdot IQR$ or above $Q3 + 1.5\cdot IQR$ are outliers.

In [1]:
temps = pd.DataFrame({'temp':[20,21,22,21,23,20,55,22,21,-30]})
q1, q3 = temps['temp'].quantile([0.25, 0.75])
iqr = q3 - q1
lo, hi = q1 - 1.5*iqr, q3 + 1.5*iqr
mask = (temps['temp'] < lo) | (temps['temp'] > hi)
print(f'bounds: [{lo}, {hi}]')
print('outliers:'); print(temps[mask])
temps.loc[mask, 'temp'] = temps['temp'].median()
print('after replacing with median:'); print(temps)

bounds: [17.625, 24.625]
outliers:
   temp
6    55
9   -30
after replacing with median:
   temp
0    20
1    21
2    22
3    21
4    23
5    20
6    21
7    22
8    21
9    21

7 Reshaping: Melt and Pivot

Tidy data has one observation per row. melt widens→long; pivot long→wide.

In [1]:
wide = pd.DataFrame({'product':['A','B'], 'Q1':[100,150], 'Q2':[120,170]})
print('wide:'); print(wide)
long = wide.melt(id_vars='product', var_name='quarter', value_name='sales')
print('\nlong (tidy):'); print(long)

wide:
  product   Q1   Q2
0       A  100  120
1       B  150  170

long (tidy):
  product quarter  sales
0       A      Q1    100
1       B      Q1    150
2       A      Q2    120
3       B      Q2    170
In [1]:
back = long.pivot(index='product', columns='quarter', values='sales').reset_index()
print('pivoted back:'); print(back)

pivoted back:
quarter product   Q1   Q2
0             A  100  120
1             B  150  170

Case Study: Cleaning the Titanic Dataset

Apply the pipeline to a real dataset: assess missingness, fill or drop, and encode a categorical column.

In [1]:
titanic = sns.load_dataset('titanic')
print('shape:', titanic.shape)
print('missing (top 5):'); print(titanic.isna().sum().sort_values(ascending=False).head())

shape: (891, 15)
missing (top 5):
deck           688
age            177
embarked         2
embark_town      2
survived         0
dtype: int64
In [1]:
t = titanic.copy()
t['age'] = t['age'].fillna(t['age'].median())
t['embark_town'] = t['embark_town'].fillna(t['embark_town'].mode()[0])
t = t.drop(columns=['deck'])
print('remaining missing:', t.isna().sum().sum())
print('survival rate by class:'); print(t.groupby('class')['survived'].mean().round(3))

remaining missing: 2
survival rate by class:
class
First     0.630
Second    0.473
Third     0.242
Name: survived, dtype: float64

<cell-expr>:1: FutureWarning:

The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning.

Exercises

  1. Create a DataFrame with some NaN values and fill them with the column mean.
  2. Find and drop duplicate rows in a DataFrame.
  3. Strip whitespace and title-case a column of names.
  4. Convert a column of date strings to datetime with errors='coerce'.
  5. Detect outliers in a numeric column using the IQR rule.
  6. Melt a wide sales table into long (tidy) form.
  7. Pivot a long table back to wide form.
  8. Replace outliers with the median and verify.
  9. Build a function that cleans a messy DataFrame end-to-end.
  10. Explain why tidy data makes downstream analysis easier.

Python Data Science: From Foundations to Applications — Chapter 10