The idea
Cleaning starts with reading, not editing. Before you trim or convert anything, profile each column: how many cells are missing, how many are empty text, how many carry leading or trailing spaces, and how many distinct values there are. Those four numbers already tell you which rules the data needs.
Profile the whole column, not the first screen of rows. Problems hide in the tail: one timestamp without an offset in row 3,812 breaks a load just as surely as one in row 2.
Write down open questions instead of guessing the answers. If status has both "Shipped" and "shipped ", ask whether case and spacing carry meaning. If a code is missing, ask whether that means unknown or not applicable. The brief or data dictionary should answer; if it doesn't, the question goes back to the data owner.
Worked example
Input: orders.customer_code — 43 rows
- Visual
Select the customer_code column heading: the inspector shows missing, empty, padded and distinct counts for all rows.
- SQL (DuckDB)
SELECT count(*) - count(customer_code) AS missing, count(*) FILTER (WHERE customer_code = '') AS empty, count(*) FILTER (WHERE customer_code <> trim(customer_code, ' ' || chr(9) || chr(13) || chr(10))) AS padded, count(DISTINCT customer_code) AS distinct_values FROM orders
- pandas
c = df["customer_code"] c.isna().sum(), (c == "").sum(), (c != c.str.strip(" \t\r\n")).sum(), c.nunique()
Result: On the published dataset: 1 missing, 0 empty, 9 padded, 13 distinct values. Padding must be trimmed before the customer lookup, and the missing code needs a rule.
Common mistakes
- Editing before profiling, then not knowing which rows a step changed.
- Profiling only the visible first rows.
- Answering open questions with guesses instead of asking the data owner.
Check yourself
Why profile all rows instead of a sample?
Rare problems (one bad timestamp, one padded key) break loads and joins; a sample can miss them entirely.
A code column has 2 missing and 3 empty values. Are those the same problem?
Not necessarily. Missing means nothing was recorded; empty text was recorded as empty. The rules decide whether they are treated alike.
Practice it
Profile customer_code, order_ts, amount and status in the input view before adding a single step. List what each rule in the task protects against.