Why this matters
Real data is messy: blank cells, a value ten times too big, the same row twice, dates written three ways. There is an old rule in computing, garbage in, garbage out. A careful calculation on flawed data still gives a flawed answer, and it gives it confidently.
The idea
Data cleaning fixes problems in the data; data transformation reshapes it to fit the question. Both happen after collection and before analysis.
Missing values have three possible responses, and choosing is a judgment call:
| Response | What it does | When |
|---|---|---|
| Deletion | Drop the rows or columns | Few missing, little effect on the whole |
| Imputation | Fill with the mean, median or nearby values | Missing values scattered without bias |
| Flagging | Mark it as missing and analyse as is | The missingness itself means something, like a refusal to answer |
There is no universally correct answer. Imputing the mean for a column that is mostly empty invents data that never existed.
Outliers are values far from the rest, and they are not automatically errors. An abnormal value comes from an input or measurement mistake; other outliers reflect reality. One remarkable sales day might be a typo, or might be the day of the sale. So start by finding the cause: correct or delete genuine errors, and in principle keep real values, deciding what to do from the purpose.
Duplicates are normally removed, but only once you have checked they really are duplicates. If the same customer bought the same product twice on the same day, those are two transactions, and merging them makes the sales total wrong.
Data type conversion puts formats right: "123" as text becomes the number 123
(otherwise sums and means cannot be calculated); 2026/4/1 and 01 Apr 2026 both
become 2026-04-01 (otherwise one day is treated as several); M and F are
unified with Male and Female.
When variables have very different scales, the big numbers dominate. Two fixes:
| Method | What it does | Purpose |
|---|---|---|
| Normalization | Rescales into 0 to 1 | Compare variables with different units |
| Standardization | Rescales to mean 0, standard deviation 1 | Position each value against the spread |
Normalising heights from 160 to 180 cm sends 160 to 0, 170 to 0.5 and 180 to 1. Standardising test scores lets you compare a student's position across subjects whose means and spreads differ.
Finally, reshaping: pivot turns vertical records into a row-by-column table, join connects datasets on a shared key, aggregation summarises by group, such as monthly totals from daily sales.
Picture it
flowchart LR R[Raw data] --> C["Clean: missing values, outliers, duplicates"] C --> T["Convert types: numbers, dates, labels"] T --> S["Rescale: normalize or standardize"] S --> H["Reshape: pivot, join, aggregate"] H --> A[Analysis]
Worked example
A shop's daily sales file has some blank days, one day at ten times the usual figure, and what look like duplicate rows. What do you do?
Start with the outlier, because its cause decides everything. Look it up: if the shop ran a sale that day, it is real and deleting it would hide a genuine effect. If the amount is exactly ten times a plausible figure, that smells like a typed extra zero, an abnormal value to correct.
For the blanks, ask why they are blank. If the shop was closed, that is not a missing value at all, it is a real zero, and imputing the mean would invent sales that never happened. If the till simply failed to report, and it is a handful of days scattered through the year, imputation is reasonable.
For the apparent duplicates, check whether two identical rows are one record entered twice or two genuine transactions. Removing a real second sale understates revenue as surely as counting a phantom one overstates it.
Only then transform. Monthly trends need aggregation by month; comparing sales against footfall, measured in completely different units, needs normalization first.
Notice that every one of these was a judgment about the world, not a rule about spreadsheets. That is why cleaning cannot be fully automated.
Your turn
Try the practice questions for this lesson. Judge statements about cleaning and outliers, match each operation to its description, and pick out the inappropriate way to handle missing values.
Recap
- Data cleaning fixes the data; data transformation fits it to the question. Both come after collection.
- Missing values: delete, impute, or flag, chosen from the pattern and the purpose.
- Outliers are not automatically errors; find the cause before deciding.
- Normalization gives 0 to 1; standardization gives mean 0 and standard deviation 1.
- Pivot reshapes, join connects, aggregation summarises.