---
title: "Clean a messy CSV with pandas before you train anything"
description: "Ten pandas fixes for the problems every real dataset has: broken headers, duplicate rows, mixed types, silly outliers and inconsistent categories."
url: https://articles.sythra.ai/articles/clean-messy-csv-pandas
slug: clean-messy-csv-pandas
author: "vaibhavkothari"
author_url: https://articles.sythra.ai/writers/vaibhavkothari
date_published: 2026-08-26T08:34:22.039Z
date_modified: 2026-08-29T10:22:15.966Z
topics: ["Python", "Pandas", "Machine Learning", "Coding"]
reading_time_minutes: 6
publisher: "Sythra"
publisher_url: https://sythra.ai
access: free
language: en
---

# Clean a messy CSV with pandas before you train anything

> Ten pandas fixes for the problems every real dataset has: broken headers, duplicate rows, mixed types, silly outliers and inconsistent categories.

Source: https://articles.sythra.ai/articles/clean-messy-csv-pandas · Author: vaibhavkothari · Published: 2026-08-26 · Reading time: 6 min · Topics: Python, Pandas, Machine Learning, Coding

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:

```python
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:

```python
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:

```python
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:

```python
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:

```python
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](https://articles.sythra.ai/articles/could-not-convert-string-to-float-fix) shows how to list the bad rows.

Keep identifiers as strings so leading zeros survive:

```python
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:

```python
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":

```python
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:

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

Then choose per column, not globally:

```python
# 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](https://articles.sythra.ai/articles/train-test-split-data-leakage).

## Step 7 — Find impossible values

Missing data is loud. Wrong data is quiet:

```python
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:

```python
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:

```python
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:

```python
# 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:

```python
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:

```python
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

```remember
# Remember this
`df.info()` -> types and missing counts, before anything else
`errors="coerce"` -> turns unconvertible values into NaN instead of crashing
`.unique()` -> shows you the spellings you did not expect
---
Clean in a function, save to Parquet, and never clean by hand twice.
```


## 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](https://articles.sythra.ai/articles/could-not-convert-string-to-float-fix)
- [Train your first ML model in 20 lines](https://articles.sythra.ai/articles/first-machine-learning-model-scikit-learn)
- [Train/test split and the accuracy lie](https://articles.sythra.ai/articles/train-test-split-data-leakage)