Data Cleaning With Pandas: A Practical Guide With Real-World Examples

Real-world data is messy: inconsistent formats, duplicate rows, missing values, dates in five different styles, prices stored as text with currency symbols. Data scientists often say most of their time goes into cleaning and preparing data. Pandas is the workhorse for that work in Python. This guide covers the techniques you'll use constantly, with patterns that keep your cleaning code readable and reproducible.

#Key takeaways

• Profile first: understand shape, types, missingness and distributions before changing anything.
• Fix data types early (numbers, dates, categories); many downstream bugs come from wrong dtypes.
• Handle missing values deliberately; the right strategy depends on why they're missing.
• Define what a duplicate means for your data before dropping anything.
• Write cleaning as a pipeline of functions with validation checks, not ad-hoc notebook cells.

#Step 1: Load carefully

pythonimport pandas as pd

df = pd.read_csv(
    "orders.csv",
    dtype={"order_id": "string", "zip_code": "string"},   # keep leading zeros
    parse_dates=["created_at"],
    na_values=["", "NA", "N/A", "null", "-"],
)

• Read IDs, phone numbers and zip codes as strings; numeric parsing drops leading zeros.
• Declare custom NA markers that your source uses.
• For large files, select only the columns you need (usecols) and consider efficient formats like Parquet.

#Step 2: Profile the data

pythondf.shape
df.info()                          # dtypes, non-null counts
df.describe(include="all").T       # summary statistics
df.isna().mean().sort_values(ascending=False)   # missing rate per column
df["status"].value_counts(dropna=False)
df.sample(10)

Look for: unexpected dtypes (numbers stored as object), impossible values (negative quantities, dates in 1900 or 2099), inconsistent categories ("Shipped", "shipped ", "SHIPPED"), and suspiciously high missingness.

#Step 3: Clean column names

pythondf.columns = (
    df.columns.str.strip()
              .str.lower()
              .str.replace(r"[^a-z0-9]+", "_", regex=True)
              .str.strip("_")
)

Consistent snake_case names make everything downstream easier.

#Step 4: Fix data types

#Numbers stored as text

pythondf["price"] = (
    df["price"].astype("string")
               .str.replace(r"[^\d.\-]", "", regex=True)   # remove $, commas, spaces
               .pipe(pd.to_numeric, errors="coerce")        # invalid → NaN
)

Using errors="coerce" turns unparseable values into NaN, so count them afterward, rather than silently losing data.

#Dates and time zones

pythondf["created_at"] = pd.to_datetime(df["created_at"], errors="coerce", utc=True)
df["created_at_local"] = df["created_at"].dt.tz_convert("America/New_York")

• Store timestamps in UTC; convert for display or local-day aggregations.
• Be explicit about ambiguous formats (dayfirst=True for 03/04/2026 meaning 3 April).

#Categories

pythondf["status"] = df["status"].str.strip().str.lower().astype("category")

Categoricals save memory and make valid values explicit.

#Step 5: Clean strings

pythondf["email"] = df["email"].str.strip().str.lower()
df["country"] = df["country"].str.strip().str.upper().replace({
    "UNITED STATES": "US", "USA": "US", "U.S.": "US", "UK": "GB",
})
df["phone"] = df["phone"].str.replace(r"\D", "", regex=True)

For messy free-text categories, build an explicit mapping table and review unmapped values. Fuzzy matching can suggest mappings, but review them before applying.

#Step 6: Handle missing values

First ask why values are missing:

• Missing completely at random: dropping or simple imputation is usually fine.
• Missing for a reason (no phone because the user skipped an optional field): the missingness itself carries information.
• Missing due to a pipeline bug: fix the source, don't impute.

Options:

pythondf = df.dropna(subset=["order_id", "customer_id"])        # required fields
df["discount"] = df["discount"].fillna(0)                   # domain default
df["age_missing"] = df["age"].isna()                        # keep the signal
df["age"] = df["age"].fillna(df["age"].median())            # simple imputation
df["region"] = df["region"].fillna("unknown")

When preparing data for machine learning, fit imputation statistics on the training set only to avoid leakage (feature engineering guide).

#Step 7: Remove duplicates

Define duplicates by business keys, not entire rows:

pythondupes = df[df.duplicated(subset=["order_id"], keep=False)]
print(len(dupes), "rows share an order_id")

df = (df.sort_values("updated_at")
        .drop_duplicates(subset=["order_id"], keep="last"))   # keep latest version

Investigate before dropping: duplicates often reveal upstream problems like retried events or double-counting joins (data quality guide).

#Step 8: Outliers and invalid values

pythoninvalid = df[(df.quantity <= 0) | (df.price < 0)]
q1, q3 = df.price.quantile([0.25, 0.75])
iqr = q3 - q1
outliers = df[(df.price < q1 - 1.5 * iqr) | (df.price > q3 + 1.5 * iqr)]

Decide per case:

• Errors (negative quantity, age 212): fix if you can, otherwise remove or null them.
• Legitimate extremes (a genuine enterprise order): keep them; maybe cap them for specific models.
• Document every rule you apply.

#Step 9: Validate

Turn assumptions into assertions that fail loudly:

pythondef validate(df: pd.DataFrame) -> pd.DataFrame:
    assert df["order_id"].is_unique, "duplicate order_id"
    assert df["created_at"].notna().all(), "missing created_at"
    assert (df["quantity"] > 0).all(), "non-positive quantity"
    assert df["status"].isin(["pending", "paid", "shipped", "cancelled"]).all()
    return df

For production pipelines, schema validation libraries provide richer, declarative checks.

#Step 10: Make it a reusable pipeline

Chain small, named functions with .pipe() so the cleaning logic reads top to bottom and can be tested:

pythondef clean_orders(raw: pd.DataFrame) -> pd.DataFrame:
    return (
        raw.pipe(standardize_columns)
           .pipe(parse_types)
           .pipe(normalize_strings)
           .pipe(handle_missing)
           .pipe(dedupe_orders)
           .pipe(remove_invalid)
           .pipe(validate)
    )

Put these functions in a module (not only a notebook), add unit tests for tricky ones (testing strategy guide), and log how many rows each step changes.

#Performance tips

• Use vectorized operations instead of apply with Python functions where possible.
• Use category dtype for repeated strings and nullable dtypes (Int64, string, boolean).
• Process large files in chunks or use columnar formats (Parquet).
• For data larger than memory, consider libraries built for it, or push the work to a SQL engine (what is data engineering).

#FAQ

#Should I drop rows with missing values?

Only when the missing fields are essential and the missingness is random or rare. Dropping can bias your data if values are missing for systematic reasons. Consider imputation plus a missing-indicator column instead.

#How do I handle inconsistent category labels?

Normalize case and whitespace first, then map variants to canonical values with an explicit dictionary. Review the remaining unmapped values periodically and extend the mapping.

#Pandas or SQL for data cleaning?

Use SQL (in the warehouse) for large-scale, repeatable transformations in production pipelines, and pandas for exploration, smaller datasets, and complex Python-specific logic. Many teams prototype in pandas and productionize in SQL or dbt (dbt explained).

#How do I clean data scraped from websites?

The same principles apply, with extra attention to encoding issues, HTML remnants, inconsistent units and currencies, and duplicate pages. Validate scraped data against schemas at ingestion (scraping pipeline architecture).