Clean a messy CSV with pandas before you train anything

Most of the time you spend on a machine learning project is not modelling. It is turning a messy CSV into a table a model can read.

Here are ten fixes, in the order you should apply them, for the problems real data actually has.

Step 1 — Look before you clean

Never start editing before you know what you have:

import pandas as pd

df = pd.read_csv("sales.csv")

print(df.shape)
print(df.head())
print(df.info())
print(df.describe(include="all").T)

info() shows dtypes and non-null counts — the fastest way to spot a numeric column that pandas read as text. describe(include="all").T transposes the summary so wide tables stay readable.

One more, and it is the most useful line in this article:

for col in df.select_dtypes(include="object").columns:
    print(col, df[col].unique()[:8])

This prints the real values in every text column. Ninety percent of data problems are visible in that output.

Step 2 — Fix the headers

Column names with spaces, capitals and symbols make every later line uglier:

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

Total Sales (₹) becomes total_sales. Now df.total_sales works and nothing needs quoting.

Step 3 — Drop duplicate rows

Duplicates inflate your scores and cause leakage across a train/test split:

print("Exact duplicates:", df.duplicated().sum())
df = df.drop_duplicates()

# Same order but re-entered — dedupe on the real identity columns
df = df.drop_duplicates(subset=["order_id"], keep="last")

keep="last" matters when later rows are corrections of earlier ones.

Step 4 — Convert types properly

Text that should be numbers is the most common issue:

df["price"] = pd.to_numeric(
    df["price"].astype(str).str.replace(r"[^0-9.\-]", "", regex=True),
    errors="coerce",
)

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

df["is_member"] = (
    df["is_member"].astype(str).str.strip().str.lower()
    .map({"yes": 1, "y": 1, "true": 1, "1": 1,
          "no": 0, "n": 0, "false": 0, "0": 0})
)

errors="coerce" turns anything unconvertible into NaN instead of crashing. If you would rather see the failures first, our guide to that exact error shows how to list the bad rows.

Keep identifiers as strings so leading zeros survive:

df = pd.read_csv("sales.csv", dtype={"zip_code": str, "phone": str})

Step 5 — Tidy up categories

The same category written five ways becomes five categories:

df["city"] = (
    df["city"].astype(str)
    .str.strip()
    .str.title()
    .replace({"Bangalore": "Bengaluru", "Bombay": "Mumbai"})
)
print(df["city"].value_counts())

Rare categories create noise. Fold anything below a threshold into "Other":

counts = df["city"].value_counts()
rare = counts[counts < 10].index
df["city"] = df["city"].where(~df["city"].isin(rare), "Other")

Step 6 — Handle missing values deliberately

First measure, then decide:

missing = df.isna().mean().sort_values(ascending=False)
print((missing[missing > 0] * 100).round(1))

Then choose per column, not globally:

# Column is mostly empty — drop it
df = df.drop(columns=missing[missing > 0.6].index)

# Numeric — median is robust to outliers
df["age"] = df["age"].fillna(df["age"].median())

# Category — its own level is more honest than a guess
df["channel"] = df["channel"].fillna("unknown")

# Missingness itself may be a signal
df["income_missing"] = df["income"].isna().astype(int)
df["income"] = df["income"].fillna(df["income"].median())

That last pattern is often the strongest. A blank income field frequently predicts more than any imputed number.

Fill from training statistics only, or you leak test information — the trap covered in train/test split and data leakage.

Step 7 — Find impossible values

Missing data is loud. Wrong data is quiet:

print(df[["age", "price", "quantity"]].describe())

print("Negative prices:", (df["price"] < 0).sum())
print("Age over 120:", (df["age"] > 120).sum())
print("Future dates:", (df["order_date"] > pd.Timestamp.now()).sum())

Sentinel values are the classic trap. An age of 999 or a coordinate of 0, 0 means "unknown" in many systems, and the mean quietly absorbs it:

import numpy as np

df["age"] = df["age"].replace({999: np.nan, -1: np.nan})

Step 8 — Deal with outliers on purpose

Not every outlier is an error. A ₹5,00,000 order may be your best customer:

q1, q3 = df["price"].quantile([0.25, 0.75])
iqr = q3 - q1
low, high = q1 - 1.5 * iqr, q3 + 1.5 * iqr

outliers = df[(df["price"] < low) | (df["price"] > high)]
print(f"{len(outliers)} outliers outside {low:.0f} to {high:.0f}")

Look at them before doing anything. Then pick:

# Cap extreme values rather than deleting rows
df["price"] = df["price"].clip(lower=low, upper=high)

# Or compress a skewed distribution
df["price_log"] = np.log1p(df["price"])

np.log1p handles zeros safely, unlike plain log.

Step 9 — Encode for the model

Text must become numbers before training:

X = pd.get_dummies(
    df.drop(columns=["target", "order_id", "order_date"]),
    columns=["city", "channel"],
    drop_first=True,
)
y = df["target"]

drop_first=True avoids one redundant column per category. Do not map categories to 1, 2, 3 unless the order is genuinely real — "Mumbai = 2" tells the model Mumbai sits between two other cities, which is nonsense.

Step 10 — Make it repeatable

Cleaning done by hand in a notebook is lost the moment you get a new file. Put it in a function:

def clean_sales(path):
    df = pd.read_csv(
        path,
        thousands=",",
        na_values=["", "N/A", "-", "NA", "null"],
        dtype={"zip_code": str},
    )

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

    df = df.drop_duplicates()
    df["price"] = pd.to_numeric(df["price"], errors="coerce")
    df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
    df["city"] = df["city"].astype(str).str.strip().str.title()
    df["price"] = df["price"].fillna(df["price"].median())

    return df


if __name__ == "__main__":
    clean = clean_sales("sales.csv")
    clean.to_parquet("sales_clean.parquet", index=False)
    print(clean.shape)

Save to Parquet rather than CSV: it keeps dtypes, loads far faster, and takes less space. Your model script then starts from clean data every time.

The checklist

1. Inspect with info, describe, and unique values per text column
2. Normalise column names
3. Drop duplicates
4. Convert types with errors="coerce"
5. Standardise category spellings
6. Handle missing values column by column
7. Hunt impossible values and sentinels
8. Decide on outliers deliberately
9. One-hot encode categories
10. Wrap it in a function and save Parquet

FAQ

How do I clean a messy CSV in pandas?

Inspect it with info() and describe(), normalise the column names, drop duplicates, convert types with pd.to_numeric(..., errors="coerce"), standardise category spellings, then handle missing values column by column.

Should I drop or fill missing values?

Drop a column when most of it is empty. For a numeric column with scattered gaps, fill with the median and add a 0/1 flag marking where the value was missing, since missingness is often informative.

How do I fix mixed data types in a pandas column?

Strip non-numeric characters with str.replace and a regex, then call pd.to_numeric with errors="coerce" so unconvertible values become NaN instead of raising.

How do I detect outliers in pandas?

Compute the interquartile range from the 25th and 75th percentiles and flag values outside 1.5 × IQR from those quartiles. Inspect them before deciding whether to cap, transform or keep them.

Why should I save cleaned data as Parquet instead of CSV?

Parquet preserves data types, loads much faster, and compresses better. A CSV round-trip loses dtypes and re-introduces the parsing problems you just fixed.

Next reading on Sythra Articles

• ValueError: could not convert string to float
• Train your first ML model in 20 lines
• Train/test split and the accuracy lie