Data Cleaning — Sales Dashboard

Setup and Audit

import pandas as pd
import numpy as np

df = pd.read_csv("Sample - Superstore.csv", encoding="latin-1")

print(f"Shape: {df.shape}")
print(f"\nColumns: {df.columns.tolist()}")
print(f"\nMissing:\n{df.isnull().sum()[df.isnull().sum() > 0]}")
print(f"\nDuplicates: {df.duplicated().sum()}")
print(f"\nDtypes:\n{df.dtypes}")

---

Step 1 — Standardise Column Names

Spaces and inconsistent casing make column references painful. Standardise to snake_case.

df.columns = (
    df.columns
    .str.strip()
    .str.lower()
    .str.replace(" ", "_")
    .str.replace("-", "_")
)
print(df.columns.tolist())
# ['row_id', 'order_id', 'order_date', 'ship_date', 'ship_mode',
#  'customer_id', 'customer_name', 'segment', 'country', 'city',
#  'state', 'postal_code', 'region', 'product_id', 'category',
#  'sub_category', 'product_name', 'sales', 'quantity', 'discount', 'profit']

---

Step 2 — Parse Dates

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

# Verify no parsing failures
print(f"Order date NaT: {df['order_date'].isna().sum()}")
print(f"Date range: {df['order_date'].min().date()} to {df['order_date'].max().date()}")

---

Step 3 — Validate Numeric Columns

# Check for impossible values
print(f"Negative sales: {(df['sales'] < 0).sum()}")        # should be 0
print(f"Zero quantity: {(df['quantity'] <= 0).sum()}")     # should be 0
print(f"Discount range: {df['discount'].min()} to {df['discount'].max()}")  # 0 to 0.8
print(f"Negative profit rows: {(df['profit'] < 0).sum()}")  # expected — loss-making items

Negative profit is NOT an error here

Unlike negative sales (which would be a data error), negative profit is legitimate — it means a line item sold at a loss due to heavy discounting. We keep these and analyse them.

---

Step 4 — Feature Engineering

# Profit margin
df["profit_margin"] = df["profit"] / df["sales"]

# Shipping days
df["shipping_days"] = (df["ship_date"] - df["order_date"]).dt.days

# Date parts for time analysis
df["order_year"] = df["order_date"].dt.year
df["order_month"] = df["order_date"].dt.to_period("M").astype(str)
df["order_quarter"] = df["order_date"].dt.to_period("Q").astype(str)
df["order_dow"] = df["order_date"].dt.day_name()

# Profitability flag
df["is_profitable"] = (df["profit"] > 0).astype(int)

# Discount band
df["discount_band"] = pd.cut(
    df["discount"],
    bins=[-0.01, 0, 0.2, 0.4, 1.0],
    labels=["No Discount", "Low (0-20%)", "Medium (20-40%)", "High (40%+)"]
)

---

Step 5 — Validate Shipping Logic

# Ship date should be on or after order date
invalid_ship = df[df["shipping_days"] < 0]
print(f"Orders shipped before ordered: {len(invalid_ship)}")  # should be 0

# Reasonable shipping window?
print(df["shipping_days"].describe())

---

Step 6 — Final Audit and Export

def audit(df, label):
    print(f"\n{'='*50}\nAUDIT: {label}")
    print(f"Shape: {df.shape}")
    print(f"Missing: {df.isnull().sum().sum()} cells")
    print(f"Duplicates: {df.duplicated().sum()}")
    print(f"Total revenue: ${df['sales'].sum():,.0f}")
    print(f"Total profit:  ${df['profit'].sum():,.0f}")
    print(f"Overall margin: {df['profit'].sum() / df['sales'].sum():.1%}")
    print(f"Loss-making line items: {(df['profit'] < 0).sum()} ({(df['profit'] < 0).mean():.1%})")

audit(df, "Cleaned Superstore Data")

df.to_csv("superstore_clean.csv", index=False)
print("\nSaved to superstore_clean.csv")

Expected output:

Total revenue: $2,297,201
Total profit:  $286,397
Overall margin: 12.5%
Loss-making line items: 1871 (18.7%)

18.7% of line items lose money

Nearly one in five line items has negative profit. This is the single most important finding to flag in the dashboard — it points directly to a discounting problem.

---

← Dataset Guide · Next: SQL Analysis →