How to Clean Messy Excel Data Using Python

import pandas as pd
import numpy as np

# Load
df = pd.read_excel("messy_sales.xlsx")

# Inspect
print(f"Shape: {df.shape}")
print(f"\nColumn types:\n{df.dtypes}")
print(f"\nMissing values:\n{df.isnull().sum()}")
print(f"\nFirst rows:\n{df.head(10)}")

def data_quality_report(df):
    """Generate a quick data quality summary."""
    report = pd.DataFrame({
        "column": df.columns,
        "dtype": df.dtypes.values,
        "non_null": df.count().values,
        "null_count": df.isnull().sum().values,
        "null_pct": (df.isnull().sum() / len(df) * 100).round(1).values,
        "unique": df.nunique().values,
        "duplicates": [df[col].duplicated().sum() for col in df.columns],
    })
    return report

print(data_quality_report(df).to_string(index=False))

    column   dtype  non_null  null_count  null_pct  unique  duplicates
      Date  object        7           1      12.5       5           2
    Region  object        8           0       0.0       5           3
   Product  object        8           0       0.0       4           4
       Qty  object        7           1      12.5       6           1
   Revenue  object        6           2      25.0       5           1
  Rep Name  object        7           1      12.5       5           2

# Strip whitespace and normalise to snake_case
df.columns = (
    df.columns
    .str.strip()
    .str.lower()
    .str.replace(" ", "_")
    .str.replace(r"[^\w]", "", regex=True)
)

print(df.columns.tolist())
# ['date', 'region', 'product', 'qty', 'revenue', 'rep_name']

# Strip all string columns
str_cols = df.select_dtypes(include="object").columns
for col in str_cols:
    df[col] = df[col].astype(str).str.strip()

# Replace 'nan' strings (from NaN → str conversion) back to NaN
df = df.replace({"nan": np.nan, "": np.nan, "N/A": np.nan, "n/a": np.nan, "None": np.nan})

# Title case for names and products
df["region"] = df["region"].str.title()
df["product"] = df["product"].str.title()
df["rep_name"] = df["rep_name"].str.title()

print(df[["region", "product", "rep_name"]].drop_duplicates())

  region     product     rep_name
   North  Widget Pro   John Smith
   South  Widget Lite    Jane Doe
    West  Widget Pro   Bob Wilson
    East  Widget Lite          NaN

# pandas handles multiple formats automatically when errors='coerce'
df["date"] = pd.to_datetime(df["date"], errors="coerce", dayfirst=False)

# Check what could not be parsed
unparsed = df[df["date"].isna()]
if len(unparsed) > 0:
    print(f"Warning: {len(unparsed)} dates could not be parsed")
    print(unparsed)

# Convert to numeric, coercing errors (like 'N/A') to NaN
df["qty"] = pd.to_numeric(df["qty"], errors="coerce")
df["revenue"] = pd.to_numeric(df["revenue"], errors="coerce")

# Handle invalid values
df.loc[df["qty"] < 0, "qty"] = np.nan  # Negative quantities are invalid
df.loc[df["revenue"] < 0, "revenue"] = np.nan  # Negative revenue invalid

# Strategy 1: Drop rows where critical fields are missing
critical_columns = ["date", "region", "product"]
before = len(df)
df = df.dropna(subset=critical_columns)
print(f"Dropped {before - len(df)} rows with missing critical fields")

# Strategy 2: Fill numeric columns with 0
df["qty"] = df["qty"].fillna(0).astype(int)

# Strategy 3: Fill text with 'Unknown'
df["rep_name"] = df["rep_name"].fillna("Unknown")

# Strategy 4: Leave revenue as NaN (better to have no data than wrong data)
# Will be excluded from calculations automatically

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

# Business-key duplicates (same date + region + product + rep)
before = len(df)
df = df.drop_duplicates(subset=["date", "region", "product", "rep_name"], keep="last")
print(f"Removed {before - len(df)} business-key duplicates")

def validate_clean_data(df):
    """Run validation checks on cleaned data."""
    checks = {
        "No null dates": df["date"].notna().all(),
        "No null regions": df["region"].notna().all(),
        "No null products": df["product"].notna().all(),
        "No negative quantities": (df["qty"] >= 0).all(),
        "No duplicate keys": not df.duplicated(subset=["date", "region", "product", "rep_name"]).any(),
        "Dates in valid range": df["date"].between("2020-01-01", "2030-12-31").all(),
        "Revenue non-negative (where present)": df["revenue"].dropna().ge(0).all(),
    }

    print("\nValidation Results:")
    all_passed = True
    for check, passed in checks.items():
        status = "✓" if passed else "✗"
        print(f"  {status} {check}")
        if not passed:
            all_passed = False

    return all_passed

validate_clean_data(df)

Validation Results:
  ✓ No null dates
  ✓ No null regions
  ✓ No null products
  ✓ No negative quantities
  ✓ No duplicate keys
  ✓ Dates in valid range
  ✓ Revenue non-negative (where present)

"""
Data Cleaning Pipeline
Reads messy Excel data, cleans all common issues, validates, and exports.
"""
import pandas as pd
import numpy as np
from datetime import datetime


def clean_excel_data(input_path, output_path):
    """Full cleaning pipeline: load → clean → validate → export."""

    # Load
    df = pd.read_excel(input_path)
    print(f"Loaded: {len(df)} rows, {len(df.columns)} columns")

    # Standardise columns
    df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")

    # Strip whitespace from strings
    str_cols = df.select_dtypes(include="object").columns
    for col in str_cols:
        df[col] = df[col].astype(str).str.strip()

    # Replace placeholder values with NaN
    df = df.replace({"nan": np.nan, "": np.nan, "N/A": np.nan, "n/a": np.nan, "None": np.nan})

    # Standardise text casing
    for col in ["region", "product", "rep_name"]:
        if col in df.columns:
            df[col] = df[col].str.title()

    # Parse dates
    if "date" in df.columns:
        df["date"] = pd.to_datetime(df["date"], errors="coerce")

    # Fix numeric columns
    for col in ["qty", "revenue"]:
        if col in df.columns:
            df[col] = pd.to_numeric(df[col], errors="coerce")
            df.loc[df[col] < 0, col] = np.nan

    # Handle missing values
    df = df.dropna(subset=["date", "region", "product"])
    df["qty"] = df["qty"].fillna(0).astype(int)
    df["rep_name"] = df["rep_name"].fillna("Unknown")

    # Remove duplicates
    df = df.drop_duplicates()
    df = df.drop_duplicates(
        subset=["date", "region", "product", "rep_name"], keep="last"
    )

    # Sort
    df = df.sort_values(["date", "region"]).reset_index(drop=True)

    # Export
    df.to_excel(output_path, index=False)
    print(f"Cleaned: {len(df)} rows exported to {output_path}")

    return df


if __name__ == "__main__":
    clean_df = clean_excel_data("messy_sales.xlsx", "clean_sales.xlsx")