← QCE Digital Solutions Unit 3

Cleaning data

Finding and fixing mistakes in data, such as typos, spelling variations, duplicate rows, missing values and impossible values, so the data is accurate and ready to use.

Part of: QCE Digital Solutions Unit 3 — Real-World Problems and Solution Requirements — Module 85

Understanding Cleaning data in QCE Digital Solutions Unit 3

What it does: Checking whether an ID has already been seen stops the same record being added twice, which removes duplicates. Trimming spaces and using one letter case makes variations such as ' brisbane' and 'BRISBANE' match, which cleans the data. Cleaning corrects or removes faulty data. It does not throw everything away. Year levels at a school only go up to 12, so 27 is outside the valid range. It is probably a typo for 7 or 12. An empty cell where data should be is a missing value. You then decide whether to fill it, flag it or remove the row.

Common mistakes: The average should only use marks that exist. The condition must be mark ≠ blank. As written, the loop adds only the missing values and skips the real marks. The loop must go through the data rows to find duplicates. Looping over seen, which starts empty, checks nothing. It should be for each row in data.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Cleaning data to a class →

This module's key terms are also in a printable crossword (Modules 194–250) and word search (Modules 194–337).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A student survey spreadsheet lists 'Ava Nguyen' twice with identical answers. What is this an example of?
Two valid responses that should both be keptA duplicate row that cleaning should remove
Matches
IF mark > 100 THEN keep the row as valid
↓
A mark over 100 is an impossible value (if marks are out of 100), so the row should be flagged or fixed, not kept as valid.
Applying
Meanings
Term
data cleaning
Meaning
Finding and fixing mistakes in data, such as typos, spelling variations, duplicate rows, missing values and impossible values, so the data is accurate and ready to use.
Practice
for i from 1 to 8
Testing
Gaps
if age < 0 age > 120 then
Check Answer
Spots
3for each mark in marks
4 if mark = blank then

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Fennec Fox' badge — Listener, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A student survey spreadsheet lists 'Ava Nguyen' twice with identical answers. What is this an example of?
A. Two valid responses that should both be kept
B. A duplicate row that cleaning should remove ✓
Duplicate rows count the same person twice and skew results. Data cleaning removes the extra copy.
2. Which best describes data cleaning?
A. Finding and fixing mistakes in data so it is accurate and ready to use ✓
B. Deleting all of the data and starting a new collection
Cleaning corrects or removes faulty data. It does not throw everything away.
3. A Year level column contains the value 27. Which type of problem is this?
A. An impossible value ✓
B. A missing value
Year levels at a school only go up to 12, so 27 is outside the valid range. It is probably a typo for 7 or 12.
4. An Age cell in a customer list has been left empty. Which type of problem is this?
A. A duplicate row
B. A missing value ✓
An empty cell where data should be is a missing value. You then decide whether to fill it, flag it or remove the row.
5. A suburb column contains 'Toowoomba', 'Towoomba' and 'toowoomba'. What should cleaning do?
A. Standardise them to one correct spelling ✓
B. Leave them, because each one is a different suburb
Typos and spelling variations stop grouping and counting from working properly. Standardising gives one consistent value.

Match the Pairs

IF age < 0 OR age > 120 THEN flag the row as an error → Checks for impossible values (such as a negative age) and flags the row so it can be fixed or removed
IF name = "" THEN mark the value as missing → Finds a missing value (an empty field) so it can be filled in, replaced or excluded
Removing duplicate rows: only add a record to the clean list if its ID has not been seen before → IF id NOT IN seenIds THEN add record to cleanList
Fixing spelling variations by converting text to one consistent format before comparing → city <- UPPERCASE(TRIM(city))
IF mark > 100 THEN keep the row as valid → A mark over 100 is an impossible value (if marks are out of 100), so the row should be flagged or fixed, not kept as valid.
Delete every row that has a missing value, without checking how many rows are lost → Deleting all rows with missing values without checking can throw away lots of useful data and bias the results.

Key Term

data cleaning
Finding and fixing mistakes in data, such as typos, spelling variations, duplicate rows, missing values and impossible values, so the data is accurate and ready to use.

Fill in the Blanks

1. if age < 0 or age > 120 then delete row end if
Hint: Type the Boolean operator that makes the row get deleted when EITHER impossible age condition is true.
2. for each row in data if row.id in seen then delete row add row.id to seen
Hint: Type the action keyword that removes a duplicate row once its ID has already been seen.
3. total ← 0 for each mark in marks if mark ≠ "" then total ← total + mark
Hint: Type the comparison operator meaning 'not equal to', so empty (missing) values are skipped.
4. cleaned ← empty list for each row in data if valid(row) then add row to cleaned
Hint: Type the assignment symbol that sets up an empty list to hold the cleaned rows.
5. if mark > 100 then print "Impossible mark" end if
Hint: Type the comparison operator that checks whether a mark is above the highest possible value.

Spot the Error

Question 1
A total ← 0
B count ← 0
C for each mark in marks
D if mark = blank then
E total ← total + mark
F count ← count + 1
Line D — The average should only use marks that exist. The condition must be mark ≠ blank. As written, the loop adds only the missing values and skips the real marks.
Question 2
A seen ← empty list
B for each row in seen
C if row.id in seen then
D delete row
E else append row.id to seen
Line B — The loop must go through the data rows to find duplicates. Looping over seen, which starts empty, checks nothing. It should be for each row in data.
Question 3
A for each person in people
B age ← person.age
C if age<0 and age>120 then
D mark as invalid
E end if
Line C — No age can be both below 0 and above 120, so 'and' never flags anything. An impossible age is either too low or too high, so it must use 'or'.
Question 4
A for each row in data
B s ← trim(row.state)
C s ← upper(row.state)
D row.state ← s
Line C — This overwrites the trimmed value with the original row.state, so the trim is lost and stray spaces stay in the data. It should be s ← upper(s).
Question 5
A if row.score ≠ blank then
B row.score ← average
C end if
D print row.score
Line A — Only missing values should be replaced. With ≠ the code would overwrite scores that are already correct. The test must be row.score = blank.

Student with a class code? Join your class →