Cleaning Messy Data

The 80% Nobody Talks About

Surveys of data scientists regularly find that cleaning and preparing data takes most of their time. Models trained on dirty data give confidently wrong answers, a problem known as garbage in, garbage out. A credit scoring model at a Nigerian digital lender that treats "Lagos", "lagos " and "LAG" as three different cities is quietly making worse decisions.

This lesson covers the five problems you will meet in almost every dataset, and the pandas tools that fix them.

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "customer_id": [101, 102, 103, 103, 104, 105, 106],
    "city": ["Lagos", "lagos ", "Abuja", "Abuja", "LAGOS", "Kano", None],
    "age": [29, 34, np.nan, np.nan, 41, 250, 23],
    "monthly_spend": ["45,000", "120000", "78000", "78000", "53,500", "91000", "66000"],
    "signup_date": ["2024-01-15", "2024-02-03", "2024-02-20", "2024-02-20", "2024-03-01", "2024-03-12", "2024-04-02"],
})

Problem 1: Missing Values

Find them first:

df.isnull().sum()            # count per column
df.isnull().mean() * 100     # percentage per column

Then decide on a strategy. There is no single right answer: it depends on why the data is missing.

Prefer the median over the mean for skewed data such as income, because a few very high earners pull the mean up.

Problem 2: Duplicates

Duplicate rows inflate counts and totals. They often come from joins, retries in data pipelines, or users submitting a form twice.

df.duplicated().sum()                          # fully identical rows
df.duplicated(subset=["customer_id"]).sum()    # same ID
df = df.drop_duplicates(subset=["customer_id"], keep="first")

Always decide which columns define a "true" duplicate. Two transactions of N5,000 from the same customer on the same day could be real.

Problem 3: Inconsistent Categories

Humans type the same thing in many ways: "Lagos", "lagos ", "LAGOS", "Lagos State", "LAG".

df["city"] = df["city"].str.strip().str.title()
df["city"] = df["city"].replace({"Lagos State": "Lagos", "Lag": "Lagos", "Fct": "Abuja"})
df["city"].value_counts()

value_counts() is your best detective here. Scan it for near-duplicates after every cleaning step.

Problem 4: Wrong Data Types

Numbers stored as text cannot be summed or averaged. Dates stored as text cannot be sorted or split into months.

df.dtypes

df["monthly_spend"] = (
    df["monthly_spend"].str.replace(",", "", regex=False).astype(float)
)
df["signup_date"] = pd.to_datetime(df["signup_date"])
df["signup_month"] = df["signup_date"].dt.month_name()

For messy numeric columns, pd.to_numeric(df["col"], errors="coerce") converts what it can and turns the rest into NaN, which you can then inspect.

Problem 5: Outliers

An age of 250 is clearly a data entry error. A transaction of N50 million might be an error, fraud, or a genuine corporate payment. Outliers need judgement, not automatic deletion.

The IQR rule is a standard way to flag them:

q1, q3 = df["age"].quantile([0.25, 0.75])
iqr = q3 - q1
lower, upper = q1 - 1.5 * iqr, q3 + 1.5 * iqr
outliers = df[(df["age"] < lower) | (df["age"] > upper)]

Options once you have found them:

• Remove clear errors (age 250), or set them to NaN and impute
• Cap extreme but valid values at a percentile (winsorising): df["spend"].clip(upper=df["spend"].quantile(0.99))
• Keep them when they are the point, as in fraud detection, where outliers are exactly what you want to find

A Repeatable Cleaning Function

Wrap your cleaning steps in a function so that you can rerun them on next month's data:

def clean_customers(raw):
    df = raw.copy()
    df = df.drop_duplicates(subset=["customer_id"])
    df["city"] = df["city"].str.strip().str.title().fillna("Unknown")
    df["monthly_spend"] = pd.to_numeric(df["monthly_spend"].str.replace(",", ""), errors="coerce")
    df.loc[(df["age"] < 16) | (df["age"] > 100), "age"] = np.nan
    df["age"] = df["age"].fillna(df["age"].median())
    df["signup_date"] = pd.to_datetime(df["signup_date"])
    return df

Golden rule: never overwrite your raw data. Keep the original file, and make every cleaning step reproducible in code.

Try it: The browser lab below gives you a dirty dataset of seven customers. Find and fix all five data quality issues.