Clean and analyse data with pandas

Most business data arrives dirty: dates in three formats, "£1,200.50" as text, the same customer spelled four ways, duplicate rows from a double export. This skill gets it clean without losing anything, then answers the question with numbers you can defend. The standard: raw data never overwritten, every cleaning step logged with row counts, every result reconciled to the source, and the method and caveats stated next to the findings.

When to use

• "Here's our sales export, what's going on?"
• "Clean up this contact list and remove the duplicates."
• "Why did revenue drop in August?"
• "Merge these two spreadsheets and tell me who's missing."
• "Which products / regions / channels are doing best?"

What you need

• The files, saved in the job folder's inputs/ (download from the connected app or the browser, or ask the owner to drop them in).
• The question and the decision it feeds. Write down the definitions before you start: what counts as a sale, an active customer, a month (calendar or rolling), which currency, gross or net of refunds and tax. If a definition is ambiguous and changes the answer, ask with clarify.
• Any known reference totals (for example, revenue from the finance report) to reconcile against.

Set up once

python3 -c "import pandas, openpyxl" 2>/dev/null || python3 -m pip install --user --break-system-packages pandas openpyxl

(Debian needs --break-system-packages with --user; it installs only into your home folder.) Recent pandas (3.x) uses copy-on-write: always assign with df.loc[mask, "col"] = value, never through chained indexing.

Method

1. Set up the job folder. inputs/ (raw, never edited), working/ (scripts, intermediate files), outputs/ (what the owner gets). Save your analysis as a script in working/ so it can be rerun when the data changes.

2. Load as text first. Read every column as a string so nothing is silently converted (IDs keep leading zeros, "N/A" stays visible), then convert column by column on purpose.

3. Profile before touching anything. Rows, columns, blanks per column, distinct values, examples, exact duplicates, date ranges, min and max of numbers. Read the profile and list the problems you see.

4. Clean in logged steps. For each step record the rows before and after and a note. Typical steps, in this order:

   • Normalise column names (lowercase, underscores).
   • Trim whitespace; standardise case for categories; map variants to one value with an explicit dictionary ({"N.": "North", "north ": "North"}).
   • Money: strip symbols and thousands separators, convert to numbers; keep blanks blank, do not turn them into 0.
   • Dates: parse with explicit formats (%d/%m/%Y for UK style, %m/%d/%Y for US); never let the library guess, because 03/07 is 3 July in London and March 7 in New York. List rows that fail to parse.
   • Duplicates: exact duplicates first; then duplicates by business key (order ID, email) with a stated rule for which one to keep (for example, the latest updated_at).
   • Outliers: flag, do not delete. Use the 1.5 x IQR rule or a domain rule ("orders over £10,000 are unusual for us") and look at each flagged row.
   • Time zones and currencies: convert to one, and say which.

5. Shape it tidy. One row per observation, one column per variable. Months spread across columns ("Jan", "Feb", ...) become rows with melt.

6. Join safely. Check key uniqueness first, use validate="many_to_one" (or the right shape) and indicator=True to count what matched. Report unmatched rows; never let an inner join silently drop them.

7. Analyse to answer the question. Patterns in references/pandas-patterns.md. Core rules:

   • Show the base: every rate or average comes with its count (n).
   • Compare against something: previous period, same period last year, target, another segment.
   • Percent change = (new - old) / old. A change between two percentages is in percentage points.
   • Weighted averages, not averages of averages.
   • Medians for skewed things (order values, time to close); means hide outliers.
   • Small numbers: under about 30 records per group, treat differences as indicative, not proven.
   • Correlation is not cause. Say what else could explain a pattern (seasonality, a price change, a tracking change).

8. Reconcile. Your totals must match the source total (and any reference figure) to the penny or within a stated rounding. Row counts must add up across groups. If they do not, find out why before reporting.

9. Report. Findings first, then method and caveats. Save clean data, results and the cleaning log together.

Tested core snippet

from pathlib import Path
import re
import pandas as pd

JOB = Path("/home/user/Desktop/Ada - Work space/orders-analysis")
RAW = JOB / "inputs" / "orders_export.csv"
(JOB / "outputs").mkdir(parents=True, exist_ok=True)

df = pd.read_csv(RAW, dtype=str, keep_default_na=False, encoding="utf-8-sig")
df.columns = [re.sub(r"\W+", "_", c.strip().lower()).strip("_") for c in df.columns]

def profile(frame):
    print(f"{len(frame):,} rows x {frame.shape[1]} cols; exact duplicate rows: {frame.duplicated().sum()}")
    return pd.DataFrame({
        "dtype": frame.dtypes.astype(str),
        "blank": frame.apply(lambda s: (s.astype(str).str.strip() == "").sum() + s.isna().sum()),
        "distinct": frame.nunique(dropna=True),
        "examples": frame.apply(lambda s: " | ".join(map(str, s.dropna().unique()[:3]))),
    })
print(profile(df).to_string())

log = []
def step(name, before, after, note=""):
    log.append({"step": name, "rows_before": before, "rows_after": after, "note": note})

n = len(df); df = df.drop_duplicates(); step("drop exact duplicates", n, len(df))
for col in ("customer_email", "region", "status"):
    df[col] = df[col].str.strip()
df["customer_email"] = df["customer_email"].str.lower()
df["region"], df["status"] = df["region"].str.title(), df["status"].str.lower()
step("trim and standardise case", len(df), len(df))

money = df["amount"].str.replace(r"[£$€,\s]", "", regex=True)
df["amount"] = pd.to_numeric(money.where(money != ""), errors="coerce")
step("amount to number", len(df), len(df), f"{df['amount'].isna().sum()} blank, left blank")

iso = pd.to_datetime(df["order_date"], format="%Y-%m-%d", errors="coerce")
uk = pd.to_datetime(df["order_date"], format="%d/%m/%Y", errors="coerce")
df["order_date"] = iso.fillna(uk)
bad = df.loc[df["order_date"].isna(), "order_id"].tolist()
step("parse dates", len(df), len(df), f"unparseable: {bad}")

paid = df[df["status"] == "paid"]
by_region = (paid.groupby("region", as_index=False)
                 .agg(orders=("order_id", "nunique"), revenue=("amount", "sum"))
                 .sort_values("revenue", ascending=False))
by_region["share"] = by_region["revenue"] / by_region["revenue"].sum()
assert round(by_region["revenue"].sum(), 2) == round(paid["amount"].sum(), 2)   # reconcile

with pd.ExcelWriter(JOB / "outputs" / "orders_clean-and-results_v01.xlsx", engine="openpyxl") as xw:
    by_region.to_excel(xw, sheet_name="By region", index=False)
    df.to_excel(xw, sheet_name="Clean data", index=False)
    pd.DataFrame(log).to_excel(xw, sheet_name="Cleaning log", index=False)
print(by_region.to_string(index=False)); print(pd.DataFrame(log).to_string(index=False))

More cleaning recipes (phone numbers, emails, near-duplicate names, Excel serial dates, wide-to-long, large files) are in references/cleaning-recipes.md.

Output

• In chat: the answer in two or three sentences, then a show_card (metrics for headline numbers, table for a breakdown).
• Files in outputs/: one workbook with results, clean data and the cleaning log; charts if useful (see make-clear-charts); the script in working/.
• A short note with: question, definitions used, data source and period, row counts in and out, findings, caveats.

Checks before you finish

• Raw files untouched; the script reruns end to end from inputs/.
• Cleaning log complete; rows removed are explained.
• Totals reconcile to the source and to any reference figure.
• Every rate has its n; every comparison has a baseline; small groups flagged.
• Definitions stated; anything that would change with a different definition called out.

Pitfalls

• Letting Excel or pandas guess types. Leading zeros, long IDs and day/month order get corrupted silently. Load as text, convert deliberately.
• Filling blanks with 0. A missing amount is not a zero sale. Keep it missing and count it.
• Join explosions. A duplicate key on the "one" side multiplies rows and inflates totals. Validate the join shape.
• Average of percentages across groups of different sizes. Recompute from totals.
• Deleting outliers because they are inconvenient. Investigate; they are often the story (or a data error worth reporting).
• Mixing gross and net, or tax-inclusive and exclusive figures. State which.
• Personal data. Customer lists contain personal information. Keep it on your computer and in the owner's systems, share only what the task needs, and do not upload it to random online tools.

See also: build-excel-spreadsheet (a maintained workbook), make-clear-charts, write-structured-report (writing up findings).