Pandas Data Cleaning

Real-World Scenario

A data engineer receives a CSV export from a legacy CRM system: revenue stored as “$1,200.00” strings, birth dates in three different formats, phone numbers with country codes or without, duplicate records from a bad ETL, and nulls encoded as “N/A”, “null”, ”-”, and empty strings. Before any analysis can start, the data needs to be cleaned. This tutorial covers every technique you’ll need.

Detecting and Counting Missing Values

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "customer_id": [1, 2, 3, 4, 5],
    "name":        ["Alice", "Bob", None, "Dave", "Eve"],
    "age":         [28, None, 35, 42, None],
    "email":       ["[email protected]", "[email protected]", "[email protected]", None, "[email protected]"],
    "revenue":     [1200.0, 450.0, None, 3200.0, 890.0],
})

# isnull() / isna() — returns boolean DataFrame
print(df.isnull())

# Count nulls per column
print(df.isnull().sum())

# Null rate per column
print((df.isnull().sum() / len(df) * 100).round(1))

# Which rows have any null?
print(df[df.isnull().any(axis=1)])

# Count of non-null values per column
print(df.count())  # equivalent to df.notnull().sum()

Filling Missing Values

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "age":     [25, np.nan, 32, np.nan, 40, 28, np.nan],
    "salary":  [50000, 62000, np.nan, 48000, 75000, np.nan, 68000],
    "dept":    ["Eng", "Sales", "Eng", np.nan, "Sales", "Eng", "Sales"],
    "score":   [8.5, np.nan, 7.2, 9.1, np.nan, 6.8, 8.0],
})

# Fill with a constant
df["dept"].fillna("Unknown", inplace=True)

# Fill numeric columns with mean/median (avoid mean for skewed data)
df["age"].fillna(df["age"].mean(), inplace=True)
df["salary"].fillna(df["salary"].median(), inplace=True)

# Fill with forward-fill (use previous valid value — useful for time series)
df["score"].ffill(inplace=True)

# Fill with backward-fill
df["score"].bfill(inplace=True)

# Group-wise fill — fill with the group mean (better than global mean)
df2 = pd.DataFrame({
    "dept":   ["Eng", "Sales", "Eng", "Sales", "Eng"],
    "salary": [80000, np.nan, 90000, 65000, np.nan],
})
df2["salary"] = df2.groupby("dept")["salary"].transform(
    lambda x: x.fillna(x.mean())
)
print(df2)

Dropping Missing Values

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "a": [1, np.nan, 3, np.nan],
    "b": [4, 5, np.nan, np.nan],
    "c": [7, 8, 9, np.nan],
})

# Drop rows with ANY null
print(df.dropna())

# Drop rows where ALL values are null
print(df.dropna(how="all"))

# Drop rows with nulls in specific columns
print(df.dropna(subset=["a", "b"]))

# Drop columns with more than 50% nulls
threshold = len(df) * 0.5
df_clean = df.dropna(axis=1, thresh=int(threshold))

Fixing Data Types

import pandas as pd
import numpy as np

# Common real-world scenario: everything came in as strings
raw = pd.DataFrame({
    "id":         ["1", "2", "3", "4"],
    "revenue":    ["$1,200.00", "$450.50", "N/A", "$3,200.00"],
    "created_at": ["2024-01-15", "2024-02-20", "2024-03-10", "invalid"],
    "active":     ["True", "False", "True", "True"],
})

# Remove currency formatting and convert to float
raw["revenue"] = (
    raw["revenue"]
    .str.replace(r"[$,]", "", regex=True)  # remove $ and commas
    .replace("N/A", np.nan)
    .astype(float)
)

# Parse dates — errors='coerce' replaces invalid dates with NaT
raw["created_at"] = pd.to_datetime(raw["created_at"], errors="coerce")

# Convert boolean strings
raw["active"] = raw["active"].map({"True": True, "False": False})

# Convert id to integer
raw["id"] = raw["id"].astype(int)

# Downcast numeric columns to reduce memory
raw["id"] = pd.to_numeric(raw["id"], downcast="integer")  # int64 → int8/int16

print(raw.dtypes)
print(raw)

Removing Duplicates

import pandas as pd

df = pd.DataFrame({
    "email":    ["[email protected]", "[email protected]", "[email protected]", "[email protected]", "[email protected]"],
    "name":     ["Alice", "Bob", "Alice", "Carol", "Bobby"],
    "revenue":  [1200, 450, 1200, 800, 600],
})

# Find duplicates
print(df.duplicated())            # True for each duplicate row
print(df.duplicated().sum())      # total count of duplicate rows

# Find duplicates based on specific columns
print(df.duplicated(subset=["email"]))

# Drop duplicates — keep first occurrence by default
df_dedup = df.drop_duplicates()
print(df_dedup)

# Keep last occurrence
df_dedup_last = df.drop_duplicates(subset=["email"], keep="last")

# Mark duplicates rather than dropping
df["is_duplicate"] = df.duplicated(subset=["email"])

Standardizing String Data

import pandas as pd

df = pd.DataFrame({
    "name":    ["  alice ", "BOB", "Carol  ", "  dave"],
    "city":    ["new york", "NEW YORK", "New York", "Brooklyn"],
    "phone":   ["+1-555-1234", "555.5678", "(555) 9012", "5553456"],
})

# Strip whitespace
df["name"] = df["name"].str.strip()

# Standardize case
df["name"] = df["name"].str.title()    # Title Case
df["city"] = df["city"].str.lower()   # lowercase for comparisons

# Normalize phone numbers — keep digits only
df["phone"] = df["phone"].str.replace(r"[^\d]", "", regex=True)

# City normalization with a mapping
city_map = {"new york": "New York", "brooklyn": "New York"}
df["city"] = df["city"].map(city_map).fillna(df["city"])

print(df)

Outlier Detection and Treatment

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
df = pd.DataFrame({
    "salary": np.concatenate([
        rng.normal(75000, 15000, 950),  # normal salaries
        rng.normal(500000, 50000, 50),  # outliers
    ])
})

# IQR method
Q1 = df["salary"].quantile(0.25)
Q3 = df["salary"].quantile(0.75)
IQR = Q3 - Q1
lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR

outlier_mask = (df["salary"] < lower) | (df["salary"] > upper)
print(f"Outliers: {outlier_mask.sum()}")  # ~50

# Option 1: Remove outliers
df_clean = df[~outlier_mask]

# Option 2: Clip to IQR bounds
df["salary_capped"] = df["salary"].clip(lower=lower, upper=upper)

# Z-score method — flag values more than 3 std from mean
z_scores = (df["salary"] - df["salary"].mean()) / df["salary"].std()
outliers_z = df[z_scores.abs() > 3]
print(f"Z-score outliers: {len(outliers_z)}")