Unit 5 · Data Collection and Cleaning
5-2 Data Cleaning and Transformation
Real data arrives messy: blank cells, a value ten times too large, the same row entered twice, dates written three different ways. Before any analysis can be trusted, the data must be cleaned and transformed to fit the question. There is a rule in computing — “garbage in, garbage out” — which means careful calculation on flawed data still gives a flawed answer.
Guiding question
Why must data be cleaned and transformed before analysis, and what choices does each fix involve?
Learning outcome. Explain how missing values, outliers and duplicate data are handled when cleaning a dataset, and describe data type conversion, normalization and standardization, and the reshaping of data to fit a purpose.
1 · What the two words mean
Collected data often cannot be used for analysis as it is. Adjusting issues such as missing values, outliers and duplicate data is data cleaning. Arranging the data into a format that fits the purpose of the analysis is data transformation.
2 · Why it is not optional
If issues are left unaddressed during analysis, incorrect conclusions may be drawn. Cleaning and transformation are the stages that support the accuracy and reliability of the analysis.
1 · The three responses
Using data containing missing values (values where data is absent) directly can stop calculations from working, or give biased results. One of three responses is chosen.
| Response | Description | When to apply |
|---|---|---|
| Deletion | Remove rows or columns containing missing values | When there are few missing values and the impact on the whole is small |
| Imputation | Fill the missing parts with values such as the mean, median, or surrounding values | When missing values are scattered without bias and replacing them does not significantly change the overall trend |
| Flagging | Leave a marker indicating that the value was missing, and analyze the data as is | When the missingness itself has meaning (for example, the fact that someone refused to answer) |
2 · There is no rule to memorise here
Which response to choose is decided by the proportion and pattern of the missing values, and by the purpose of the analysis. For example, imputing the mean for an item where missing values are a large proportion of all data may produce values far from reality. There is no single correct response; judgment according to the purpose is required.
1 · An outlier is not automatically a mistake
Outliers — values that deviate significantly from other data — are not necessarily errors. They may be abnormal values caused by input or measurement errors, or they may be values that reflect reality. If a store’s sales data shows one day with remarkably high sales, it may be an input error — or it may genuinely reflect a sale or an event on that day.
2 · So start with the cause
Handling outliers begins with identifying the cause.
① An input or measurement error — correct or delete the value.
② A value that reflects reality — in principle keep it, and decide how to handle it based on the purpose of the analysis.
3 · And a duplicate may not be a duplicate
Duplicate data — the same content existing in multiple records — affects the results of aggregation and is, in principle, removed. But it is important to confirm the data is genuinely duplicated. Even if records look identical, they may be data that should be kept as separate records: if the same customer buys the same product twice on the same day, those are two separate transactions, and combining them would make the sales aggregation wrong.
Every fix costs something — find out what
Eight students were asked how many hours a day they spend on a screen.
This is what came back. Find the average — and watch what each fix costs you.
| Who | Date | Hours | Problem |
|---|---|---|---|
| Student A | 2026/4/1 | 3 | |
| Student B | 2026-04-01 | 5 | date written differently |
| Student C | 01 Apr 2026 | blank | missing value |
| Student D | 2026/4/1 | 4 | |
| Student E | 2026/4/1 | 9999 | outlier — 9999 hours? |
| Student F | 2026/4/1 | blank | missing value |
| Student G | 2026/4/1 | 6 | |
| Student G | 2026/4/1 | 6 | looks like a duplicate |
Choose a fix. Then try another one and compare what each answer costs.
1 · Making the format usable
Arranging the format to make data usable for analysis is data type conversion.
| Type of conversion | Description | Example |
|---|---|---|
| String to numeric | Convert numbers stored as strings into numeric values that can be used in calculations | “123” → 123 |
| Unification of date format | Align dates with different notations into a single format | “2026/4/1”, “2026-04-01”, “01 Apr 2026” → 2026-04-01 |
| Unification of notation | Combine values with the same meaning into a single notation | A mix of “Male/Female” and “M/F” → standardized to “Male/Female” |
2 · What goes wrong without it
If formats are not aligned, data cannot be aggregated correctly. Numbers stored as strings cannot be summed or averaged. And if date notations are not unified, data from the same day will be treated as belonging to different days — which is why the three dates in the messy table above are the same date written three ways.
1 · Two ways to put columns on the same footing
When the units or ranges (scales) of data differ greatly between variables, variables with larger values may strongly influence the result. To prevent this, processing is performed to align the scale.
| Method | Description | Main purpose |
|---|---|---|
| Normalization | Convert data to a range from 0 to 1 | Align data with different units into a comparable form |
| Standardization | Convert data to a form with mean 0 and standard deviation 1 | Position values relative to the spread of the data |
Watch the same five students under all three scales
| Student | Height (cm) | Weight (kg) |
|---|---|---|
| 1 | 160 | 52 |
| 2 | 165 | 61 |
| 3 | 170 | 58 |
| 4 | 175 | 74 |
| 5 | 180 | 70 |
Height: mean 170.00, standard deviation 7.07. Weight: mean 63.00, standard deviation 8.00.
Height runs in the hundreds and weight in the tens. Put them side by side in an analysis and height will dominate, purely because its numbers are bigger — not because it matters more.
2 · The book’s own example
When analysing variables with different units and scales together, such as height (cm) and weight (kg), normalization can align both into the range 0 to 1. If height data ranges from 160 cm to 180 cm, normalizing converts 160 cm → 0, 170 cm → 0.5, 180 cm → 1: the minimum becomes 0 and the maximum becomes 1.
3 · And what standardization is for
Standardization aligns data based on how far each value is from the mean. For example, when test scores from multiple subjects are standardized, even if the mean and spread differ between subjects, the position of each student can be compared.
You now know that normalization gives 0 to 1 and standardization gives mean 0 and standard deviation 1. The question worth asking is the one the book answers in eight words: why bother? Here is what “no variable dominates by size” actually looks like when it goes wrong.
🧮 What happens when you skip it — finding the most similar house
- 1
A buyer likes a house of 100 m² with 3 bedrooms. Two others are available: house A is 110 m² with 3 bedrooms, house B is 100 m² with 5 bedrooms. Which is more like the one they liked?
Why this step: Answer this with your own judgment first, before any arithmetic. Almost everybody says house A — ten square metres is a small change, while two extra bedrooms is a different house altogether.
- 2
Now let a computer do it the obvious way: add up the differences. House A: |110 − 100| + |3 − 3| = 10 + 0 = 10. House B: |100 − 100| + |5 − 3| = 0 + 2 = 2.
Why this step: This is the same “count how different they are” idea used by the recommendation system in lesson 1-3, and it is how a great many real systems compare things. Smaller total means more similar.
Most people slip here: Read the result carefully: B scores 2 and A scores 10, so the computer says house B is FIVE TIMES more similar. That is the opposite of what you just said, and the computer is not broken.
- 3
Find the cause: the two columns are not measured on comparable scales. Size runs from about 60 to 200 m² — a span of 140. Bedrooms run from 1 to 5 — a span of 4.
Why this step: A change of “1” means something completely different in each column. One square metre is nothing; one bedroom is a great deal. Adding them straight together silently treats them as equal, so the column with the bigger numbers wins every time — not because it matters more, but because its units are smaller.
- 4
Normalize both columns to 0–1 first, using (value − min) ÷ (max − min). Size difference becomes 10 ÷ 140 = 0.071. Bedroom difference becomes 2 ÷ 4 = 0.500.
Why this step: Dividing each difference by that column’s full span converts it into “what fraction of the whole range is this?”. Now both columns speak the same language and can honestly be added together.
- 5
Compare again: house A scores 0.071, house B scores 0.500. House A is now the more similar — by a factor of about seven.
Why this step: The data did not change and the method did not change. Only the scale did, and the answer flipped to the one you gave at the start. This is exactly what the book means by “so no variable dominates by size”, and it is why rescaling is a step you do BEFORE the analysis, not a decoration afterwards.
🧮 Both formulas by hand, on eight values
- 1
Take eight values: 2, 4, 4, 4, 5, 5, 7, 9
Why this step: These are chosen so every answer below comes out exactly, with no rounding to hide behind. You can check each line on paper.
- 2
For NORMALIZATION, find the smallest and largest: min = 2, max = 9, so the span is 9 − 2 = 7.
Why this step: Normalization stretches the data so the smallest becomes 0 and the largest becomes 1. To do that you need to know where the ends are — which is also its weakness, as the last step explains.
- 3
Apply (value − min) ÷ span to each: (2−2)/7 = 0, (4−2)/7 = 0.286, (5−2)/7 = 0.429, (7−2)/7 = 0.714, (9−2)/7 = 1
Why this step: Subtracting the minimum slides the data so it starts at 0; dividing by the span squeezes it so it ends at 1. Check the two ends came out exactly 0 and 1 — if they did not, you have made an arithmetic slip.
- 4
For STANDARDIZATION, find the mean: (2+4+4+4+5+5+7+9) ÷ 8 = 40 ÷ 8 = 5. Then the standard deviation, which is 2.
Why this step: Standardization measures each value in “how many typical distances from average is it?”, so it needs the average and the typical distance — the mean and the standard deviation.
- 5
Apply (value − mean) ÷ standard deviation: (2−5)/2 = −1.5, (4−5)/2 = −0.5, (5−5)/2 = 0, (7−5)/2 = 1, (9−5)/2 = 2
Why this step: Subtracting the mean re-centres the data on 0; dividing by the standard deviation puts it in units of typical spread. A z of 2 means “two typical distances above average”, which means the same thing whether the original was marks, metres or money.
Most people slip here: Negative results are correct and expected — they are the values below average. Roughly half of them should be negative. If none are, you have subtracted the wrong way round.
- 6
Check it worked: the new values are −1.5, −0.5, −0.5, −0.5, 0, 0, 1, 2. They add to 0, so their mean is 0. Their squares add to 8, and 8 ÷ 8 = 1, so their standard deviation is 1.
Why this step: This is the definition of standardization, so the check is not optional — it is the thing you claimed to have done. If the mean is not 0 and the standard deviation is not 1, something went wrong earlier.
Where the comparison breaks down: Currencies have a fixed exchange rate you can look up; columns of data do not. There is no true rate that says how many square metres a bedroom is worth, so rescaling is a judgment about what to treat as comparable, not a conversion with a right answer — which is why the book keeps saying these choices depend on the purpose of the analysis.
There is a good reason it dominates, and the book states it as a rule of computing: garbage in, garbage out. A brilliant model on badly prepared data produces a confident wrong answer, and — this is the dangerous part — it looks exactly like a confident right one. Every skill in Unit 6 and Unit 7 rests on this unit having been done properly.
Consistently reported in surveys of practising data scientists.
1 · The three operations
Processing that changes the arrangement or grouping of data according to the purpose is data reshaping.
| Operation | Description | Example |
|---|---|---|
| Pivot | Reshape vertically arranged data into a table of row × column combinations | Date, product and sales arranged vertically → a “date × product” table |
| Join | Connect multiple datasets via a common item | Connect a student roster and a grade sheet by “student ID” |
| Aggregation | Calculate sums, means and so on for each group and summarize | Compute monthly totals from daily sales |
2 · Which one depends on the question
Which operation to perform depends on the purpose. If you want to see monthly sales trends, you need to aggregate daily sales by month. Clarify the purpose of the analysis first, then plan how to reshape the data.
How the real paper is built
The ministry sets half multiple choice and half written answer in every subject. The two halves are not the same skill. In the multiple-choice half you have to spot the wrong options quickly — and the wrong options are built out of the mistakes students really make. In the written half nobody gives you the answer to recognise; you have to produce it, in your own words, with a reason.
Practise both. Being good at one half and weak at the other costs you exactly half the paper.
- data cleaning
- — fixing missing values, outliers, duplicates.
- data transformation
- — reshaping or rescaling data to fit the purpose.
- missing value
- — a value that is absent.
- outlier
- — a value far from the others.
- abnormal value
- — an outlier caused by an error.
- data type conversion
- — aligning the format so data can be used.
- normalization
- — convert to the range 0 to 1.
- standardization
- — convert to mean 0, standard deviation 1.
- pivot
- — long rows → a row × column table.
- join
- — link datasets by a common item.
- aggregation
- — summarize by group.
Worked Example
(1) Mark each statement with ○ if it is true or × if it is false.
A Data cleaning is work performed before data collection.
B The responses to missing values are deletion, imputation and flagging — three in total.
C All outliers are caused by input or measurement errors, so they must always be deleted.
D Standardization is processing that converts data to a form with mean 0 and standard deviation 1.
(2) Match each description with the processing: A Aggregation, B Join, C Normalization.
a Conversion of data to a range from 0 to 1
b Processing that connects different datasets via a common item
c Processing that calculates sums, means, etc. for each group and summarizes
1 · Answer the following questions.
2(1) · Which is NOT an appropriate response to missing values?
Mark ○ for the appropriate responses, and × for the one that is not.
When there are few missing values and the impact on the whole is small, delete rows containing missing values.
When missing values are scattered randomly, fill the missing parts with values such as the mean.
When the missingness itself has meaning, leave a marker indicating that the value was missing and analyze the data.
Impute the mean and analyze data on items where most of the data is missing.
2(2) · Choose the most appropriate processing for each situation.
Match each description with the choice that fits it.
You want to align date data with mixed notations such as “2026/4/1”, “2026-04-01” and “01 Apr 2026”.
You want to compare variables with different units and scales together, such as height (cm) and weight (kg).
2(3) · Which most appropriately describes the handling of outliers?
Mark ○ for the correct description, and × for the ones that are wrong.
Outliers are all errors in the data, so delete all of them as soon as they are found.
For outliers, identify the cause first; correct or delete them if they are due to input errors, and in principle keep them if they reflect reality.
Outliers do not affect analysis, so no special handling is needed.
Outliers can always be resolved by replacing them with the mean.
Think as an Engineer — Investigate & Decide
Your class collected a survey on daily screen time, but the raw table is messy: some cells are blank, one entry reads “9999” hours, dates are written three different ways, and two rows look identical. You prepare the data for analysis.
1 · Inspect the data.
List each problem you can find; for each, say which category it is (missing value, outlier, duplicate, or format issue) and note in one phrase how it would distort the analysis if left unfixed.2 · Decide a fix and its risk.
For the blank cells, choose deletion, imputation or flagging, and justify it from the proportion of blanks. For “9999”, say how you would check its cause — a typo, a wrong unit (minutes entered as hours), or a code entered for a refused answer — before removing it. Name one respondent whose true answer a careless fix might erase.3 · Plan the transformation.
State the one format conversion you must run so the dates aggregate correctly, and whether you would normalize the screen-time column. In pairs, compare cleaning plans and agree on which single fix carries the greatest risk of distorting the results — and why.
In a New Context
A shop’s sales table lists each day’s sales for products A, B and C on separate rows, but the manager wants one row per day with a column per product. Name the reshaping operation needed — and name the operation instead required to get each product’s monthly total.
Lesson Question — Answered
Collected data is rarely usable as it is, and flawed data yields flawed conclusions no matter how careful the later calculation — garbage in, garbage out.
Cleaning handles missing values (by deletion, imputation or flagging, chosen from their proportion and meaning), outliers (judged by cause: correct or delete errors, keep real values), and duplicates (removed only when genuinely duplicated).
Transformation makes data comparable and analyzable: data type conversion unifies formats (string to numeric, date formats, notation), normalization rescales to 0–1 and standardization to mean 0 and standard deviation 1 so no variable dominates by size, and reshaping — pivot, join, aggregation — arranges data to fit the question.
Each step is a judgment guided by the purpose, not a fixed rule.
2 · Read the passage and fill in the blanks (a)–(e).
If collected data is used directly in analysis, incorrect conclusions may be drawn. For this reason, ( a ), which addresses issues, and ( b ), which arranges the format to fit the purpose of the analysis, are necessary. For missing values, an appropriate response is chosen from deletion, ( c ) and flagging, depending on the proportion of missing values and the purpose of the analysis. Also, when units or ranges differ greatly between variables, the scale is aligned through ( d ), which converts data to a range from 0 to 1, or ( e ), which converts data to a form with mean 0 and standard deviation 1.
3(1) · Which corresponds to data type conversion?
Mark ○ for the one that is data type conversion, and × for the others.
Filling in missing values with the mean.
Standardizing notations such as “2026/4/1” and “01 Apr 2026” into “2026-04-01”.
Aligning the scales of height and weight to between 0 and 1.
Aggregating daily sales into monthly totals.
3(2) · Which most appropriately describes normalization and standardization?
Mark ○ for the correct description, and × for the ones that have swapped something.
Normalization is processing that converts data to a form with mean 0 and standard deviation 1.
Standardization is processing that converts data to a range from 0 to 1.
Normalization is used to align data with different units into a comparable form.
Standardization is used to enlarge the scale of variables.
3(3) · For each processing, is it pivot, join or aggregation?
Match each description with the choice that fits it.
Connecting a student roster and a grade sheet by “student ID” into a single table.
Calculating monthly totals from daily sales data.
Reshaping daily product sales into the form of a “date × product” table.
Key takeaway
Clean first (missing values, outliers, duplicates), then transform (convert types, normalize or standardize, reshape by pivot, join, aggregation). Every fix is a judgment set by the purpose — garbage in, garbage out.
Take any small table you both have — marks, scores, times — and each write down one fix you would apply to it. Then swap and argue the other way: what does your partner’s fix quietly throw away?
Sign In to Keep Going
You've reached the end of the free preview. Sign in — and upgrade to Plus or Pro — to keep going with Programming and Artificial Intelligence.
Full Access
Unlock all 30 lessons