Chapter 10. Before analysis — handing cleaning to AI

Key points

• Data preparation, which blocks the way before analysis, is where AI takes the most labour off you
• Missing values, inconsistent notation, duplicate records and mismatched formats, cleared with AI
• Clean data is the precondition for analysis that is both accurate and fast

Eighty per cent of analysis is preparation

There is a well-known rule of thumb in data work.

Most of the time an analysis takes goes not into calculation but into preparing the data.

Freshly collected data is almost always dirty: blank cells, inconsistent notation (male / M / man all present), dates in different formats, the same person duplicated under different spellings. In that state neither human nor AI can analyse accurately. This is exactly what the earlier part meant by "the more organised the information, the more accurately and quickly AI handles it".

What data cleaning involves

• Checking and handling missing values (deciding what to do with blanks)
• Standardising notation (aligning male / M / man)
• Standardising formats (dates, units, decimal places)
• Removing duplicates (finding the same subject entered twice)
• Record linkage (matching the same person or item written differently)

Unglamorous, patience-testing work. Researchers and practitioners have been dissolving enormous amounts of time here for years.

And this is where AI shines

This tedious, time-consuming work is exactly where AI takes over most brilliantly. As the previous part noted, AI can write code and actually process data (the data analysis and code execution features in ChatGPT and Claude).

Hand over anonymised data and ask it to standardise the notation, list the missing values, find the duplicates, and it writes the code, runs it, and returns clean data. You write no code at all. The idea of having AI do the work, learned in the previous part, pays off here.

Do not forget the Chapter 2 principles. Raw personal data never goes over. Anonymised material, or simply a conversation about "here is the structure of the data, here is how I want it cleaned", is enough for AI to be genuinely useful.

💡 When AI cleans data for you, always have it report what it changed and how, and keep a backup of the original. Unintended conversions do creep in. The "check before large changes" spirit from the previous part applies here too.

A prompt to try

You are a data scientist who knows preprocessing well. Below is the structure of anonymised data I want to analyse — the column names and a few sample rows from each.

1. Point out the problems this data probably contains (missing values, inconsistent notation, mismatched formats, duplicates).
2. Propose how each should be cleaned.
3. If you perform the cleaning, always report what you changed and how, as a list.
4. Tell me what information the cleaning will lose, and anything else to watch for.

[Data structure] (paste anonymised column names and sample rows here)

Chapter 10 summary

• Most analysis time is preparation, and it is where AI is strongest
• Missing values, inconsistent notation, duplicates and record linkage, cleared by having AI write the code
• Never hand over raw data; always have the changes reported and keep a backup