Clean Spreadsheet and Tabular Data…

Do not ask AI to analyze a spreadsheet until the table has a documented unit of observation, stable column meanings, parsed types, duplicate rules, missing-value policy, and reconciliation checks. A model can explain patterns in bad data just as fluently as patterns in good data. The professional workflow preserves raw input, creates a reproducible cleaned copy, reports every quality issue, and only then permits analysis.

This lesson uses Python and pandas because the code is inspectable and rerunnable. The same controls apply in Excel, Google Sheets, SQL, or another tool: never silently overwrite the source, never “fix” unknown values by guessing, and keep a record count and totals before and after each transformation.

Define One Row Before Cleaning Columns

Write the unit of observation in one sentence. For an order table it might be “one row is one order-line item,” not “one customer” or “one invoice.” This choice determines what counts as a duplicate and which totals are valid. If an order contains three items, order_id alone may repeat legitimately; the true key might be (order_id, line_number).

Create a data dictionary with:

column name and plain-language meaning;
expected type and unit;
permitted values or range where justified;
whether missing is allowed and what missing means;
whether the field is sensitive;
authoritative source and owner;
transformation rule and output name.

Identifiers are usually strings even when they contain only digits. Turning 00127 into the number 127 changes the identifier. Currency needs an explicit currency column and a decimal-safe representation. Dates require an explicit input format; 03/04/2026 is ambiguous without one.

Worked Example: Audit and Clean a Synthetic Sales File

Create sales_raw.csv with synthetic data:

order_id,order_date,city,revenue_pkr,status,customer_phone
0001,2026-07-01,Lahore,"2,500",delivered,0300-0000001
0002,2026-07-02,lahore,1800,Delivered,0300-0000002
0002,2026-07-02,lahore,1800,Delivered,0300-0000002
0003,2026-07-03,ISB,,pending,0300-0000003
0004,not-a-date,Karachi,3200,returned,0300-0000004
0005,2026-07-05,KHI,-500,delivered,0300-0000005

The phone values are fictional and unnecessary for sales analysis. The cleaned analytic table should remove them, not merely hide the column on screen.

Install pandas and create clean_sales.py:

from pathlib import Path
import json
import pandas as pd

SOURCE = Path("sales_raw.csv")
OUTPUT = Path("sales_clean.csv")
REPORT = Path("quality_report.json")

# Read as text first so identifiers and original spellings survive inspection.
raw = pd.read_csv(SOURCE, dtype=str, keep_default_na=False)
df = raw.copy()

required = {"order_id", "order_date", "city", "revenue_pkr", "status"}
missing_columns = sorted(required - set(df.columns))
if missing_columns:
    raise ValueError(f"Missing required columns: {missing_columns}")

df.columns = [c.strip().lower() for c in df.columns]
df["order_id"] = df["order_id"].str.strip()
df["city_original"] = df["city"]

city_map = {
    "lahore": "Lahore",
    "isb": "Islamabad",
    "islamabad": "Islamabad",
    "khi": "Karachi",
    "karachi": "Karachi",
}
df["city"] = df["city"].str.strip().str.casefold().map(city_map)
df["status"] = df["status"].str.strip().str.casefold()
allowed_statuses = {"delivered", "pending", "returned"}

df["order_date_parsed"] = pd.to_datetime(
    df["order_date"], format="%Y-%m-%d", errors="coerce"
)
df["revenue_pkr_parsed"] = pd.to_numeric(
    df["revenue_pkr"].str.replace(",", "", regex=False).str.strip(),
    errors="coerce",
)

# Remove only later copies of fully identical rows. If the same order ID has
# conflicting content, keep every version and quarantine the whole ID group.
duplicate_id_mask = df.duplicated(subset=["order_id"], keep=False)
full_row_duplicate_mask = df.duplicated(keep=False)
conflicting_ids = df.loc[
    duplicate_id_mask & ~full_row_duplicate_mask, "order_id"
].unique()
conflicting_id_mask = df["order_id"].isin(conflicting_ids)
exact_duplicate_drop_mask = df.duplicated(keep="first") & ~conflicting_id_mask
missing_order_id_mask = df["order_id"].eq("")
invalid_date_mask = df["order_date_parsed"].isna()
invalid_city_mask = df["city"].isna()
invalid_status_mask = ~df["status"].isin(allowed_statuses)
missing_revenue_mask = df["revenue_pkr_parsed"].isna()
negative_revenue_mask = df["revenue_pkr_parsed"].lt(0).fillna(False)

report = {
    "source_file": SOURCE.name,
    "rows_in": int(len(raw)),
    "exact_duplicate_rows_removed": int(exact_duplicate_drop_mask.sum()),
    "conflicting_duplicate_id_rows": int(conflicting_id_mask.sum()),
    "missing_order_ids": int(missing_order_id_mask.sum()),
    "invalid_dates": int(invalid_date_mask.sum()),
    "unmapped_cities": int(invalid_city_mask.sum()),
    "unmapped_statuses": int(invalid_status_mask.sum()),
    "missing_or_invalid_revenue": int(missing_revenue_mask.sum()),
    "negative_revenue_needing_review": int(negative_revenue_mask.sum()),
    "raw_parseable_revenue_total_pkr": float(df["revenue_pkr_parsed"].sum()),
}

# Deduplication is justified here only for fully identical copies. Conflicting
# versions remain visible and are marked for source-owner review.
clean = df.loc[~exact_duplicate_drop_mask].copy()

# Do not impute questionable business facts. Exclude unresolved rows from analysis.
review_mask = (
    clean["order_id"].eq("")
    | clean["order_date_parsed"].isna()
    | clean["city"].isna()
    | ~clean["status"].isin(allowed_statuses)
    | clean["revenue_pkr_parsed"].isna()
    | clean["revenue_pkr_parsed"].lt(0)
    | clean["order_id"].isin(conflicting_ids)
)
clean["review_status"] = review_mask.map({True: "needs_review", False: "analysis_ready"})

# Remove the unnecessary phone field from the analytic export.
clean = clean.drop(columns=["customer_phone"], errors="ignore")
clean.to_csv(OUTPUT, index=False)

report["rows_out"] = int(len(clean))
report["analysis_ready_rows"] = int((clean["review_status"] == "analysis_ready").sum())
REPORT.write_text(json.dumps(report, indent=2), encoding="utf-8")
print(json.dumps(report, indent=2))

Run python clean_sales.py. Inspect both outputs. The script preserves original city text, adds parsed columns, checks the lab's explicit status set, marks blank keys and unresolved records, removes an unnecessary personal field, and removes only the later copy of a fully identical row. If one order ID appears with conflicting content, every version stays visible and is quarantined for source-owner review. It does not turn the blank revenue into zero, convert a negative value into a positive sale, guess the bad date, or assume an unknown city or status without an explicit mapping.

The negative amount might be an error, refund, adjustment, or legitimate convention. Only the source owner can resolve it. Marking it for review is better than a confident “cleaning” rule that changes the business meaning.

Reconcile Before and After

At every material step, compare:

row count and unique-key count;
missingness by column;
sums for monetary or quantity fields, separated by status;
minimum, maximum, median, and suspicious outliers;
category values before and after normalization;
excluded rows and an explicit reason for each;
a sample of changed values with source pointers.

Do not use one grand total without checking grain. Joining an order table to a many-row customer-interaction table can multiply revenue. Aggregate each table to the intended grain before joining and verify row cardinality. A clean-looking spreadsheet can still be structurally wrong.

Failure Cases to Catch

Merged headers or totals inside data: export a rectangular table with one header row and keep summary formulas separate.
Leading zeros lost: ingest identifiers as strings and compare source values.
Dates parsed by locale guess: specify the format and quarantine failures.
Blank converted to zero: missing, zero, not applicable, and not yet reported have different meanings.
Silent deduplication: define the key and preserve a duplicate report; identical rows may still represent valid repeated events.
Category over-normalization: “Islamabad” and “Rawalpindi” are not interchangeable because a model says both are nearby.
Outlier deletion: investigate and document; an extreme value may be the most important transaction or a unit error.
PII in analytic extracts: minimize at ingestion. Hashing a phone number may still permit linking and may not be needed at all.
Spreadsheet formula injection: cells beginning with formula characters can become active when opened in spreadsheet software. Treat external CSV cells as untrusted and follow the target tool's safe-import guidance.
AI-written cleaning code not tested: run on fixtures with known expected failures and review diffs before touching real data.

🇵🇰 Pakistan Angle

Pakistan-facing tables often mix English, Urdu script, Roman Urdu, abbreviations, and city spelling variants. Preserve the original text and map only approved variants to stable codes. Use UTF-8 end to end. Treat PKR amounts as money with explicit units; commas, parentheses, “Rs.” prefixes, tax, delivery charges, refunds, and discounts need documented parsing rather than deletion.

Customer phone numbers, addresses, CNIC values, bank details, and private order notes should not enter an AI analysis merely because they share a spreadsheet with sales columns. Create the smallest authorized extract, work locally for cleaning where practical, and confirm a provider's current data controls before any upload. On low bandwidth, a small cleaned CSV plus quality report is usually safer and cheaper than repeatedly uploading a workbook containing images, formulas, and hidden sheets.

Hands-On Exercise: Produce a Reproducible Data Quality Pack

Create a synthetic table with at least 25 rows and deliberately include a duplicate, blank number, invalid date, category variant, leading-zero identifier, outlier, and unnecessary personal column. Deliver:

raw/ containing the untouched source fixture.
DATA-DICTIONARY.md defining row grain, key, type, unit, missing policy, sensitivity, and owner for every column.
clean_data.py or an equivalent script that creates—not overwrites—a cleaned export.
quality_report.json with row counts, unique keys, missingness, invalid types, unmapped categories, outliers, and pre/post reconciliation totals.
review_queue.csv containing unresolved rows and explicit reason codes.
sales_clean.csv or equivalent with unnecessary personal columns removed.
Three assertions that fail when the key duplicates, an unknown category slips through, or analysis-ready revenue is missing.

Completion Rubric

Pass — grain and dictionary: row meaning, key, units, types, missingness, sensitivity, and source ownership are explicit.
Pass — reproducibility: one command regenerates cleaned data and the report without editing the raw file.
Pass — reconciliation: row/key counts and material totals explain every exclusion or transformation.
Pass — uncertainty: unresolved values are quarantined, not guessed or silently imputed.
Pass — privacy: unnecessary personal fields are absent from analytic output and repository fixtures are synthetic.
Needs revision: source files are overwritten, leading zeros or currencies are corrupted, or AI analysis begins before quality gates pass.

Sources

Key takeaway: Make the table defensible before making it intelligent: preserve raw data, define row grain and types, quarantine uncertainty, reconcile every transformation, minimize personal fields, and give AI only the analysis-ready evidence.

Clean Spreadsheet and Tabular Data Before AI Analysis

Turn this lesson into one checked practice output