← Years 5–6 Data & Information

Cleaning data

Finding and fixing mistakes in data, like typos, spelling variations, duplicate rows, missing values and impossible values, so the results are correct.

Part of: 5-6 Digital Technologies Vic Curric - Data, Information and Privacy — Module 21, VCE Applied Computing - U1O1 - Data analysis — Module 27, VCE Data Analytics - U3O1 - Data Analytics — Module 73, VCE Data Analytics - U3O2 - Analysis and Design — Module 66, VCE Data Analytics - U4O1 - Development and Evaluation — Module 4, 7-8 Digital Technologies: Acquiring, storing and validating data — Module 18, 9-10 Digital Technologies: Acquiring, storing and validating data — Module 9, ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 54, ACARA Digital Technologies Years 7–8 — Analysing and Visualising Data — Module 9, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 92

Understanding Cleaning data in Years 5–6 Data & Information

What it does: An empty cell is a missing value. Cleaning data means spotting it and then fixing it or marking it clearly. No Year 5 student can be 150 years old. Checking for values that cannot be true is part of cleaning data. Spelling variations get counted as different answers. Cleaning them to one spelling makes the counts correct. Duplicate rows count the same person twice, so totals and averages will be wrong. Remove the extra row. Missing values should be found and fixed properly. Guessing a random number adds a new mistake. A duplicate row repeats information already in the table. Cleaning data removes it so totals are not too big.

Common mistakes: Lists do not have an add method. To put the fixed name (with the same capital letter) into the list, use clean.append(n.title()). list(rows) keeps the duplicate 'Ali'. To remove duplicate rows, turn the list into a set first: list(set(rows)).

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 21–30) and word search (Modules 21–56).

Take this worksheet further — free with Cloodit

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

Learning
Duo
A class survey lists favourite fruit as 'Banana', 'banana' and 'Bananna'. What should you do when cleaning the data?
Change them all to the same spelling, 'Banana'Leave them, because the computer will know they are the same
Matches
Counting Red, red and RED as three different colours
↓
The spelling variations were not cleaned, so one colour is counted as three. Change them all to Red.
Applying
Meanings
Term
clean data
Meaning
Finding and fixing mistakes in data, like typos, spelling variations, duplicate rows, missing values and impossible values, so the results are correct.
Practice
Clean data checklist
Testing
Gaps
=(A2)
Check Answer
Spots
3for n in names:
4 clean.add(n.title())

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 class survey lists favourite fruit as 'Banana', 'banana' and 'Bananna'. What should you do when cleaning the data?
A. Change them all to the same spelling, 'Banana' ✓
B. Leave them, because the computer will know they are the same
Spelling variations get counted as different answers. Cleaning them to one spelling makes the counts correct.
2. Two rows in your spreadsheet are exactly the same: 'Mia, Year 5, 10'. What is the best way to clean them?
A. Keep both so there is more data
B. Delete one of the duplicate rows ✓
Duplicate rows count the same person twice, so totals and averages will be wrong. Remove the extra row.
3. A cell in the 'Age' column is empty. Which is a good cleaning step?
A. Find the missing value and fill it in, or mark it as missing ✓
B. Type in any number so the cell is not empty
Missing values should be found and fixed properly. Guessing a random number adds a new mistake.
4. A table of Year 6 students shows one student's age as 112. What kind of mistake is this?
A. An impossible value, which should be checked and fixed (maybe it is 12) ✓
B. A correct value, because computers never make mistakes
No Year 6 student is 112 years old. Impossible values are probably typing mistakes, so check and correct them.
5. Why do we clean data before making a chart?
A. So the chart looks more colourful
B. So the results and the chart are correct ✓
Mistakes in data lead to wrong totals, averages and charts. Cleaning data helps make the results correct.

Match the Pairs

Syndey -> Sydney → Fixing a spelling variation or typo so every city is written the same way
Delete the second 'Mia, 10, Blue' row → Removing a duplicate row so the same person is not counted twice
Cleaning data means finding a missing value, such as an empty age cell, and filling it in or marking it → Age: 11, 10, (empty), 9 -> check cell C4
Cleaning data means spotting an impossible value, such as a student who is 150 years old, and correcting it → Age: 10, 11, 150, 9 -> fix 150
Counting Red, red and RED as three different colours → The spelling variations were not cleaned, so one colour is counted as three. Change them all to Red.
Average height = 1.4, 1.3, 14, 1.5 (not checked) → The impossible value 14 was left in. It should be 1.4, so the average is wrong until it is fixed.

Key Term

clean data
Finding and fixing mistakes in data, like typos, spelling variations, duplicate rows, missing values and impossible values, so the results are correct.

Fill in the Blanks

1. =LOWER(A2)
Hint: Type the function name that turns text into small letters so spelling variations like Cat and cat match.
2. =COUNTIF(A2:A50, A2)>1
Hint: Type the operator meaning 'more than', so a value found more than once is flagged as a duplicate.
3. =COUNTBLANK(B2:B30)
Hint: Type the function name that counts empty cells so you can find missing values.
4. =IF(C2<0, "Error", "OK")
Hint: Type the operator that checks if an age is below zero, which is an impossible value.
5. =IF(B2="", "Missing", B2)
Hint: Type the comparison operator that checks whether the cell is equal to empty text.

Spot the Error

Question 1
A names = ['Sam', 'sam', 'Mia']
B clean = []
C for n in names:
D clean.add(n.title())
Line D — Lists do not have an add method. To put the fixed name (with the same capital letter) into the list, use clean.append(n.title()).
Question 2
A rows = ['Ali', 'Bo', 'Ali']
B unique = list(rows)
C print(len(unique))
Line B — list(rows) keeps the duplicate 'Ali'. To remove duplicate rows, turn the list into a set first: list(set(rows)).
Question 3
A scores = [8, None, 7, 9)
B good = []
C for s in scores:
D if s is not None:
E good.append(s)
Line A — The list starts with [ so it must end with ], not ). It should be scores = [8, None, 7, 9].
Question 4
A age = 250
B if age > 0 or age < 120:
C print('Keep')
D else:
E print('Fix this age')
Line B — With 'or', the impossible age 250 still passes because it is bigger than 0. To catch impossible values use 'and': age > 0 and age < 120.
Question 5
A ages = [10, None, 11]
B for i in range(len(ages)):
C if ages[i] = None:
D ages[i] = 10
Line C — A single = stores a value. To check for a missing value, use 'is None' (or ==). It should be: if ages[i] is None:

Student with a class code? Join your class →