Data Cleaning Pipeline: Wrangling a Messy Real-World CSV

What We're Building

A clean_pipeline(df) function that takes a raw, messy customer-orders CSV, returns a cleaned DataFrame, and generates a human-readable cleaning report — a running log of every fix it made and why.

# customer_orders.csv — real-world messy, on purpose
order_id,customer_name,email,order_date,amount,country
1001," john smith ",john@example.com,2026-01-04,"$45.00",USA
1002,JANE DOE,jane@example.com,01/05/2026,"$120",uk
1003,Bob Lee,bob@example.com,2026-01-06,,USA
1001," john smith ",john@example.com,2026-01-04,"$45.00",USA
1004,Amy Chen,amy@example.com,2026-01-07,"$999999.99",
1005,dan miller,,2026-01-08,"eror",USA

Step 1: Load & Inspect First

import pandas as pd

df = pd.read_csv("customer_orders.csv")
print(df.info())
print(df.isna().sum())
print(df.duplicated().sum())

Same diagnostic reflex ds1-3/ds1-4 already established — .info() for dtypes, .isna().sum() for missing values per column, .duplicated().sum() for exact duplicate rows. Never clean before looking; the shape of the mess determines which fixes actually apply.

Step 2: Duplicates

def remove_duplicates(df, report):
    before = len(df)
    df = df.drop_duplicates()
    removed = before - len(df)
    report.append(f"Removed {removed} exact duplicate row(s).")
    return df

Every cleaning function here follows the same shape: do the fix, measure what changed, append a plain-English line to a shared report list. Row 1001 above is an exact duplicate — same order, same everything — the safest kind of row to simply drop.

Step 3: Fix Types & Formats

def fix_amount(df, report):
    before_missing = df["amount"].isna().sum()
    df["amount"] = (
        df["amount"].astype(str)
        .str.replace("$", "", regex=False)
    )
    df["amount"] = pd.to_numeric(df["amount"], errors="coerce")   # "eror" -> NaN
    coerced = df["amount"].isna().sum() - before_missing
    report.append(f"Converted amount to numeric; {coerced} unparseable value(s) became missing.")
    return df

def fix_dates(df, report):
    df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
    report.append("Standardized order_date to a single datetime format.")
    return df

errors="coerce" is the load-bearing choice in both functions — instead of crashing on "eror" or an inconsistent date format, pandas turns anything it can't parse into NaN, which Step 5 then handles deliberately rather than letting a bad value silently corrupt a calculation.

Step 4: Standardize Text

def standardize_names(df, report):
    df["customer_name"] = df["customer_name"].str.strip().str.title()
    df["country"] = df["country"].str.strip().str.upper()
    report.append("Trimmed whitespace and standardized casing on customer_name and country.")
    return df

" john smith " and "JANE DOE" are two customers written two very different ways — .str.strip() removes stray whitespace, .str.title() gives every name the same casing convention, so two records for the same person don't silently look like two different customers to anything downstream.

Step 5: Missing Values — A Real Decision, Per Column

def handle_missing(df, report):
    df = df.dropna(subset=["customer_name"])   # can't analyze an order with no customer
    report.append("Dropped rows with a missing customer_name (unrecoverable identifier).")

    median_amount = df["amount"].median()
    n_filled = df["amount"].isna().sum()
    df["amount"] = df["amount"].fillna(median_amount)
    report.append(f"Filled {n_filled} missing amount value(s) with the median (${median_amount:.2f}).")

    n_unknown = df["country"].isna().sum()
    df["country"] = df["country"].fillna("UNKNOWN")
    report.append(f"Filled {n_unknown} missing country value(s) with 'UNKNOWN'.")
    return df

Step 6: Flagging (Not Silently Removing) Outliers

def flag_outliers(df, report):
    q1, q3 = df["amount"].quantile([0.25, 0.75])
    iqr = q3 - q1
    upper_bound = q3 + 1.5 * iqr   # the same IQR rule ds1-6 covers in full
    flagged = df[df["amount"] > upper_bound]
    report.append(f"Flagged {len(flagged)} row(s) as statistical outliers (amount > ${upper_bound:.2f}) for manual review — not removed.")
    return df, flagged

$999999.99 is almost certainly a data-entry error, not a genuine order — but this pipeline flags it for a human to check rather than deleting it automatically. ds1-6 covers the IQR rule's own full statistical reasoning; here it's applied as a practical, reusable check.

The Complete Pipeline

def clean_pipeline(input_path):
    df = pd.read_csv(input_path)
    report = [f"Started with {len(df)} rows."]

    df = remove_duplicates(df, report)
    df = fix_amount(df, report)
    df = fix_dates(df, report)
    df = standardize_names(df, report)
    df = handle_missing(df, report)
    df, flagged = flag_outliers(df, report)

    report.append(f"Finished with {len(df)} rows.")
    return df, flagged, report

cleaned, flagged, report = clean_pipeline("customer_orders.csv")
cleaned.to_csv("customer_orders_cleaned.csv", index=False)
print("\n".join(report))

What's Next

Chapter 3: Weather Data Dashboard — the first project pulling live data from an API instead of a static file, applying ds1-3's own JSON-handling material to real-time responses.