Cleaning Messy CSVs in pandas: A Practical Cheat Sheet

Real-world CSVs are almost never clean. Column names have trailing spaces, dates come in five different formats, numbers arrive as text with dollar signs, and there's always a "N/A" hiding where a number should be. Before you can analyze anything, you have to fix all of that.

This is the set of pandas moves I run through on nearly every messy file. Keep it handy — it covers maybe 80% of the cleaning you'll ever need.

Load it defensively

Half of cleaning is loading the file without pandas silently guessing wrong.

import pandas as pd

df = pd.read_csv(
    "data.csv",
    dtype=str,              # read everything as text first, convert on purpose later
    skipinitialspace=True,  # strip spaces after delimiters
    na_values=["", "NA", "N/A", "null", "-", "None"],
)

Reading everything as strings up front sounds counterintuitive, but it stops pandas from turning a ZIP code like 07001 into the number 7001. You convert types deliberately once you know what each column actually is.

Tidy up column names

Messy headers break everything downstream. Normalize them once.

df.columns = (
    df.columns
    .str.strip()          # remove leading/trailing spaces
    .str.lower()          # consistent casing
    .str.replace(r"\s+", "_", regex=True)  # spaces -> underscores
    .str.replace(r"[^\w]", "", regex=True) # drop punctuation
)

Now " Customer Name " becomes customer_name, and you can use clean attribute access like df.customer_name.

Strip whitespace from the values too

Header spaces are obvious; value spaces are the ones that silently ruin your groupby.

str_cols = df.select_dtypes(include="object").columns
df[str_cols] = df[str_cols].apply(lambda s: s.str.strip())

"New York" and "New York " look identical on screen but count as two different groups until you do this.

Fix numbers stored as text

Currency and percentages usually arrive as strings.

df["price"] = (
    df["price"]
    .str.replace(r"[$,]", "", regex=True)  # remove $ and thousands commas
    .astype(float)
)

df["rate"] = df["rate"].str.rstrip("%").astype(float) / 100

"$1,299.00" becomes 1299.0, and "8.5%" becomes 0.085. If some rows won't convert, use pd.to_numeric(df["price"], errors="coerce") to turn the bad ones into NaN instead of crashing.

Parse dates that come in many formats

df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")

errors="coerce" is the important part — anything unparseable becomes NaT (missing) instead of raising an exception. After parsing, check how many failed:

print(df["order_date"].isna().sum(), "dates failed to parse")

If that number is high, your column probably mixes formats and you may need format= or dayfirst=True.

Handle missing values on purpose

Don't blindly drop or fill. Decide per column.

# See where the gaps are
print(df.isna().sum())

# Fill categorical gaps with a real label
df["region"] = df["region"].fillna("unknown")

# Fill numeric gaps with the median (robust to outliers)
df["age"] = df["age"].fillna(df["age"].median())

# Drop rows only when a truly required field is missing
df = df.dropna(subset=["customer_id"])

Filling a category with "unknown" is honest; filling it with a made-up number is not. Keep the two straight.

Kill duplicate rows

before = len(df)
df = df.drop_duplicates()
print(f"Removed {before - len(df)} exact duplicates")

# Or dedupe on a business key, keeping the most recent
df = df.sort_values("order_date").drop_duplicates(subset="order_id", keep="last")

Exact-duplicate removal is safe. Key-based dedup is where you decide which copy survives — usually the newest.

Standardize categorical text

The same category often shows up spelled three ways.

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

df["status"] = df["status"].replace({
    "cancelled": "canceled",
    "complete": "completed",
    "in progress": "in_progress",
})

print(df["status"].value_counts())

That last value_counts() is your friend — it instantly reveals "Active", "active", and "ACTIVE " all masquerading as different values.

Validate before you trust it

Cleaning without checking is just hoping. A few quick asserts catch problems early.

assert df["customer_id"].notna().all(), "Missing customer IDs remain"
assert (df["price"] >= 0).all(), "Negative prices found"
assert df["order_date"].between("2020-01-01", "2030-01-01").all(), "Dates out of range"

If any of these trip, you found a data problem before it silently corrupted a report.

A reusable skeleton

Wrap the whole thing so you can rerun it on next month's file without thinking.

def clean(path):
    df = pd.read_csv(path, dtype=str, skipinitialspace=True,
                     na_values=["", "NA", "N/A", "null", "-"])
    df.columns = df.columns.str.strip().str.lower().str.replace(r"\s+", "_", regex=True)
    str_cols = df.select_dtypes(include="object").columns
    df[str_cols] = df[str_cols].apply(lambda s: s.str.strip())
    df = df.drop_duplicates()
    return df

The real takeaway

Data cleaning feels like a slog because people treat it as a one-off scramble every time. It isn't. The tasks repeat — strip headers, fix types, coerce dates, dedupe, validate — so turn them into functions you rerun instead of steps you redo. Your future self, staring at next quarter's export, will thank you.

If you'd like these patterns already assembled into tested, parameterized scripts — column normalizers, type coercers, a dedupe helper, a validation harness — I bundled eight of them into a Python Data Cleaning Toolkit. It's the same techniques from this post, packaged so you can point them at a file and go. And if you'd rather build your own from the snippets above, that works just as well — the important thing is to stop cleaning by hand.