Getting Data Into a Shape You Can Trust

Nobody's data arrives clean. Dates in three formats, a currency column with symbols in it, names with trailing spaces, duplicates that are not quite duplicates, and a helpful colleague who typed 'N/A' in a numeric field.

AI is genuinely excellent at this work. It is also the stage where a wrong step is least visible — a bad clean does not throw an error, it just produces a confident number from the wrong rows.

Look before you clean

The single most common mistake is cleaning data you have not examined. Before any transformation, establish the ground truth you will check against afterwards.

1. Row count. Write it down — this is your control number
2. Column list with the type each one should be
3. How many blanks per column, and whether blank means zero or unknown. These are different and conflating them changes every average you calculate
4. The range of each numeric column: minimum, maximum. Impossible values show up immediately here
5. The distinct values of each categorical column. This is where you find 'Kathmandu', 'kathmandu' and 'KTM' as three cities

Ask for the script, not the cleaned file

This is the most important habit in the chapter. When a tool hands you cleaned output directly, you cannot see what it did, cannot re-run it next month, and cannot correct one step without redoing everything.

A script is inspectable, repeatable and reviewable — and the model writes it just as easily.

Here are the first 30 rows of a CSV (2,847 rows total).

Write a Python/pandas script that:
- Parses `order_date` — it has both DD/MM/YYYY and YYYY-MM-DD
- Converts `amount` to a number: strips "Rs.", commas, spaces
- Normalises `city`: trim, title case, and map the variants
  you can see to one form. LIST the mappings you chose so I
  can check them
- Treats "N/A", "-", "" and "null" as missing, not as zero
- Flags rows where amount <= 0 or order_date is in the future
  — flag, do not delete

Print before/after row counts and a count of rows changed per
step. Do not drop any row without printing why.

[paste rows]

Decisions the model must not make alone

• What to do with missing values. Dropping them, filling with zero and filling with the mean produce three different conclusions, and only you know which is honest for this dataset
• What counts as a duplicate. Same email? Same email and date? The definition is a business decision
• Outliers. A sale ten times larger than the rest may be an error or your best customer. Never remove one without looking at it
• Category groupings. Merging small categories into 'Other' can hide exactly the pattern you were looking for

Sensitive data in spreadsheets

Uploading a customer export is a disclosure of every column in it, including the ones irrelevant to your question. Delete the columns you do not need before uploading — usually names, emails, phone numbers and addresses — and keep the identifier you need to join back on locally.

Where the tool supports it, run analysis code against a local file rather than uploading the contents. Most of the value is in the script, and the script does not need the real data to be written.