How do you clean a dataset before analysis?

This table looks fine. It is not. Five rows from sales_export_final_v2.csv: an "Ada Lovelace" and an "ada lovelace" that are the same person, a 2026-03-14 next to a 14/03/2026, an amount of N/A, an empty amount, and a 1,204 that is a string. Four problems in five rows, and a quick glance catches none of them.

The fix is not talent, it is a checklist. Here is the 26-point pass.

1. Structure

• Row count: matches the source system?
• One row = one entity, stated out loud.
• Id unique: COUNT(*) = COUNT(DISTINCT id).
• Dtypes: numbers numeric, dates dates.

2. Missing values

• Real NULLs: count them per column.
• Fake NULLs: N/A, "", "none", 0, -999.
• Pattern: random, or one source/period?
• The rule: drop, impute or flag: write it down.

3. Duplicates

Exact duplicates are easy. The soft ones are the ones that survive:

SELECT LOWER(TRIM(customer)) AS c,
       COUNT(*) AS n
FROM sales
GROUP BY 1
HAVING COUNT(*) > 1
ORDER BY n DESC;

Normalize first, then group. "Ada Lovelace" and "ada lovelace " are the same person. Also check the business key (same email, two customer ids), and document the keep rule: latest? richest?

4. Values and types

• Ranges: age 214, amount -50: possible?
• Categories: FR, fr, France = one value.
• String numbers: strip "1,204" and "$" then cast.
• Units: cents or euros? k or absolute?
• Outliers: inspect before deleting. Ever.

5. Dates

• One format: parse all to ISO before anything.
• Impossible: signups in 1970 or next year.
• Timezones: UTC or local? Pick and convert.
• Gaps: missing days = missing loads.

6. Sanity

• Totals: reconcile one number to the source.
• Eyeball 20: random rows, read them slowly.
• Log it: rows in, rows out, rows dropped and why.
• Rerunnable: script, not hand edits in Excel.

The trap: the silent cast

The column loaded fine. As text.

One df.dtypes or one information_schema look costs ten seconds and catches all four.

The takeaway

Cleaning is not a chore before the analysis. It usually IS the analysis: the fake nulls, soft duplicates and silent casts you catch here are exactly the errors that would have shipped in the chart. The full 26-point checklist is a print-ready PDF above. Run it on every dataset before the first chart.