Clean a messy CSV export with pandas

The script

"""
clean_csv.py - tidy up a messy CSV export in one pass.

Usage:
    python clean_csv.py input.csv                 # writes input_clean.csv
    python clean_csv.py input.csv -o tidy.csv     # choose the output name
"""
import argparse
import re
from pathlib import Path

import pandas as pd


def clean_header(name: str) -> str:
    """'  Order Date (UTC) ' -> 'order_date_utc'"""
    name = re.sub(r"[^0-9a-zA-Z]+", "_", str(name).strip().lower())
    return name.strip("_") or "column"


def clean(df: pd.DataFrame) -> pd.DataFrame:
    # 1. Consistent, code-friendly column names
    df.columns = [clean_header(c) for c in df.columns]

    # 2. Trim stray whitespace and turn blank strings into real missing values
    for col in df.select_dtypes(include="object"):
        df[col] = df[col].str.strip().replace("", pd.NA)

    # 3. Drop rows and columns that are completely empty
    df = df.dropna(how="all").dropna(axis=1, how="all")

    # 4. Remove exact duplicate rows
    df = df.drop_duplicates()

    # 5. Convert text columns that are really numbers or dates
    for col in df.select_dtypes(include="object"):
        as_num = pd.to_numeric(df[col].str.replace(r"[,$%]", "", regex=True), errors="coerce")
        if as_num.notna().sum() >= 0.9 * df[col].notna().sum():
            df[col] = as_num
            continue
        as_date = pd.to_datetime(df[col], errors="coerce", format="mixed")
        if as_date.notna().sum() >= 0.9 * df[col].notna().sum():
            df[col] = as_date

    return df.reset_index(drop=True)


def main() -> None:
    parser = argparse.ArgumentParser(description="Clean a messy CSV file.")
    parser.add_argument("input", type=Path)
    parser.add_argument("-o", "--output", type=Path)
    args = parser.parse_args()

    out = args.output or args.input.with_name(f"{args.input.stem}_clean.csv")

    raw = pd.read_csv(args.input, dtype=str, keep_default_na=False)
    tidy = clean(raw.copy())
    tidy.to_csv(out, index=False)

    print(f"Rows:    {len(raw):>6} -> {len(tidy)}")
    print(f"Columns: {raw.shape[1]:>6} -> {tidy.shape[1]}")
    print("Types:")
    for col, dtype in tidy.dtypes.items():
        print(f"  {col:<24} {dtype}")
    print(f"Saved to {out}")


if __name__ == "__main__":
    main()

How it works

clean_header() lowercases each column name and replaces every run of non-alphanumeric characters with a single underscore, so " Order Date (UTC) " becomes order_date_utc. That means you can write df.order_date_utc instead of df[" Order Date (UTC) "].

Reading as text first. pd.read_csv(..., dtype=str, keep_default_na=False) loads everything as plain strings. That sounds backwards, but it stops pandas from guessing types too early (and getting "$89.99" wrong). The script decides types itself in step 5, after cleaning.

Step 2: whitespace. .str.strip() removes leading and trailing spaces, then .replace("", pd.NA) turns empty cells into proper missing values so pandas can recognise them.

Steps 3 and 4: empty rows, columns and duplicates. dropna(how="all") only drops a row if every cell is empty, so partly filled rows survive. Deduplication runs after trimming, so "Grace Hopper" and "Grace Hopper " count as the same row.

Step 5: type detection. For each text column it tries:

• Numbers: strip ,, $ and %, then pd.to_numeric(errors="coerce"). Anything that fails becomes NaN instead of crashing.
• Dates: pd.to_datetime(format="mixed") handles 2026-01-05 and 01/07/2026 in the same column.

A conversion is only kept if at least 90% of the non-empty values convert successfully. That protects real text columns that happen to contain a few numbers.

Running it

pip install pandas
python clean_csv.py orders.csv

Example output:

Rows:         5 -> 3
Columns:      6 -> 5
Types:
  order_id                 int64
  customer_name            object
  order_date_utc           datetime64[ns]
  total                    float64
  notes                    object
Saved to orders_clean.csv

Variations

Day-first dates (UK/EU). 01/07/2026 is read as January 7 by default. For 1 July, add dayfirst=True:

as_date = pd.to_datetime(df[col], errors="coerce", format="mixed", dayfirst=True)

Write to Excel instead. Swap the last write for tidy.to_excel(out.with_suffix(".xlsx"), index=False) (needs pip install openpyxl).

Clean a whole folder. Wrap the body of main() in a loop:

for path in Path("exports").glob("*.csv"):
    tidy = clean(pd.read_csv(path, dtype=str, keep_default_na=False))
    tidy.to_csv(path.with_name(f"{path.stem}_clean.csv"), index=False)

Dedupe on a key, not the whole row. If the same order can appear with slightly different notes, use df.drop_duplicates(subset=["order_id"], keep="last").

Stricter or looser typing. Change 0.9 to 1.0 to convert only perfectly clean columns, or to 0.75 for messier data.