Cleaning & Transforming Data

Real datasets arrive with wrong dtypes, missing values, and messy strings. Cleaning makes aggregates trustworthy before you merge or model.

Recipe

Quick-reference recipe card - copy-paste ready.

import pandas as pd
 
df = pd.read_csv("raw.csv", dtype={"region": "category"})
df["revenue"] = pd.to_numeric(df["revenue"], errors="coerce")
df["sku"] = df["sku"].str.strip().str.upper()
df = df.dropna(subset=["revenue"])

When to reach for this:

• First pass on any CSV, API export, or spreadsheet ingest
• Before joins (keys must be normalized)
• Before train/test splits in ML pipelines
• When aggregates look impossible (strings summing as zero)

Working Example

import pandas as pd
import numpy as np
from io import StringIO
 
raw = """order_id,region,revenue,sku,ordered_at
1, east , $120 , abc-1 , 01/15/2025
2,WEST,340,def-2,2025-01-16
3,EAST,N/A,abc-1,2025-01-17
"""
df = pd.read_csv(StringIO(raw), dtype={"region": "string[pyarrow]"})
 
# Strip and normalize text keys
df["region"] = df["region"].str.strip().str.title()
df["sku"] = df["sku"].str.upper()
 
# Parse money and dates
df["revenue"] = (
    df["revenue"]
    .str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
    .replace("N/A", np.nan)
)
df["revenue"] = pd.to_numeric(df["revenue"], errors="coerce")
df["ordered_at"] = pd.to_datetime(df["ordered_at"], format="mixed", utc=True)
 
# Impute with documented policy: median by region
df["revenue"] = df.groupby("region", observed=True)["revenue"].transform(
    lambda s: s.fillna(s.median())
)
 
# Drop rows still missing critical keys
clean = df.dropna(subset=["order_id", "ordered_at"]).astype({"order_id": "int64"})
 
print(clean.dtypes)
print(clean)

What this demonstrates:

• Arrow-backed strings for memory-efficient text ops
• str accessor chains for whitespace and casing
• to_numeric(errors="coerce") turning bad tokens into NA
• Group-wise imputation instead of a global mean

Deep Dive

How It Works

• Missing values map to NaN for floats, pd.NA for nullable dtypes, or None in object columns.
• Vectorized string methods run per element without Python loops.
• astype may copy; convert_dtypes() upgrades to nullable pandas extension types.
• Categorical dtype stores levels once - great for repeated region names.

Common Transforms

Python Notes

import pandas as pd
 
# Prefer nullable Int64 when NA is possible
df["units"] = pd.array([1, None, 3], dtype="Int64")
 
# map for small domain replacements
df["status"] = df["status"].map({"A": "active", "I": "inactive"})

Gotchas

• Global fillna(0) on missing revenue - treats "unknown" as zero sale. Fix: separate NA policy per column; use group medians or flags.
• In-place mutation on a view - df.dropna(inplace=True) on a slice corrupts parent. Fix: assign back: df = df.dropna().
• Regex special chars in str.replace - $ and . need regex=False or escaping. Fix: pass regex=False for literal replacements.
• Locale date ambiguity - 01/02/2025 parses differently by locale. Fix: enforce ISO "%Y-%m-%d" at source or use format="mixed" with audit.
• Category typos - "East" and "east " become different categories before strip. Fix: normalize strings before astype("category").

Alternatives

FAQs

dupes = df[df.duplicated("order_id", keep=False)]

df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")

df[["city", "state"]] = df["location"].str.split(",", expand=True)

for chunk in pd.read_csv("huge.csv", chunksize=100_000):
    process(chunk)

Related

• pandas Series & DataFrames - selection basics
• Joins & Merges - clean keys before joining
• Data Validation & Quality - Pandera and Great Expectations
• Performance & Memory - categoricals and Arrow strings

Stack versions: This page was written for Python 3.14.0 (stable 3.14, maintenance 3.13), FastAPI 0.115+, Django 5.2, Flask 3.1, Pydantic 2, PyTorch 2.6+, pandas 2.2+, Polars 1.x, ruff 0.9+, and uv 0.6+.