Data Cleaning With Python pandas: The 15 Fixes You Need for Real Excel Files

📎 This article includes 2 downloadable practice files ↓

Real exports are messy: title rows above the header, numbers stored as text, three spellings of the same city. This guide collects the pandas fixes you will use again and again, in the order you usually apply them.

import pandas as pd
import numpy as np

1. Skip title rows and use the right header

df = pd.read_excel("export.xlsx", sheet_name="Sheet1", skiprows=3)   # header is on row 4
df = df.dropna(how="all").dropna(axis=1, how="all")                   # remove fully blank rows/cols

2. Clean column names

df.columns = (df.columns.str.strip().str.lower()
              .str.replace(r"[^\w]+", "_", regex=True).str.strip("_"))
# "Invoice No." -> invoice_no, "Amount (INR)" -> amount_inr

3. Numbers stored as text

df["amount_inr"] = pd.to_numeric(
    df["amount_inr"].astype(str).str.replace(r"[₹,\s]", "", regex=True), errors="coerce")

errors="coerce" turns anything unreadable into NaN instead of crashing — then you can inspect those rows.

4. Dates in mixed formats

df["invoice_date"] = pd.to_datetime(df["invoice_date"], dayfirst=True, errors="coerce")
bad = df[df["invoice_date"].isna()]          # rows that need a look

dayfirst=True matters in India: “03/04/2020” is 3 April, not 4 March.

5. Trim and normalise text

for col in ["customer", "city", "region"]:
    df[col] = df[col].astype(str).str.strip().str.replace(r"\s+", " ", regex=True).str.title()

6. Fix spelling variants with a map

city_map = {"Bombay": "Mumbai", "Bangalore": "Bengaluru", "Gurgaon": "Gurugram", "Dilli": "Delhi"}
df["city"] = df["city"].replace(city_map)

7. Remove duplicates

df = df.drop_duplicates()                                   # identical rows
df = df.sort_values("updated_at").drop_duplicates("invoice_no", keep="last")  # latest per invoice

8. Missing values: decide, don’t guess

df.isna().sum()                                  # where are the gaps?
df["region"] = df["region"].fillna("Unassigned")
df = df.dropna(subset=["invoice_no", "amount_inr"])   # rows useless without these

9. Split one column into two

df[["first_name", "last_name"]] = df["customer_name"].str.split(" ", n=1, expand=True)

10. Extract with regex

df["pan"] = df["remarks"].str.extract(r"([A-Z]{5}\d{4}[A-Z])")
df["mobile"] = df["contact"].str.extract(r"([6-9]\d{9})")

11. Categorise numbers into bands

df["size_band"] = pd.cut(df["amount_inr"], bins=[0, 10_000, 1_00_000, np.inf], labels=["Small", "Medium", "Large"])

12. Flag outliers

q1, q3 = df["amount_inr"].quantile([0.25, 0.75])
df["outlier"] = ~df["amount_inr"].between(q1 - 3 * (q3 - q1), q3 + 3 * (q3 - q1))

13. Validate before saving

assert df["invoice_no"].is_unique, "duplicate invoices!"
assert (df["amount_inr"] >= 0).all(), "negative amounts found"

14. Save with a log of what changed

with pd.ExcelWriter("clean.xlsx") as xl:
    df.to_excel(xl, sheet_name="Clean", index=False)
    bad.to_excel(xl, sheet_name="Check_dates", index=False)

15. Wrap it in a reusable function

def clean_export(path):
    df = pd.read_excel(path, skiprows=3).dropna(how="all")
    df.columns = df.columns.str.strip().str.lower().str.replace(r"[^\w]+", "_", regex=True)
    df["amount_inr"] = pd.to_numeric(df["amount_inr"].astype(str).str.replace(r"[₹,\s]", "", regex=True), errors="coerce")
    df["invoice_date"] = pd.to_datetime(df["invoice_date"], dayfirst=True, errors="coerce")
    return df.drop_duplicates()