Year 9 · Spring 1 · Data Science and AI

Cleaning: missing values and outliers

What this lesson covers

Topic 18 Data, spreadsheets and AI

KS3 Computing · Data is raw facts. Information is data with meaning. Spreadsheets calculate, databases store and search, and AI learns patterns from examples.

Managing DataCambridge Lower Secondary Computing 0860

You will learn to

Identify missing values, wrong formats, impossible values, spelling differences and duplicates in a dataset.
Use COUNTBLANK, sorting and filters to find problems quickly.
Decide for each problem whether to fix it, blank it or keep it, and give a reason.
Record every change in a cleaning log: the cell, what it was, what it is now, and why.

Success criteria

I can find all the planted errors in the class sheet.
I can explain why '7h' cannot be averaged but 7 can.
I can show a cleaning log with a reason for every change.

Key words

Say each word, then read what it means. Every one is in the glossary too.

Formula: A spreadsheet calculation. Always starts with =.
Function: A subroutine that returns a value.
Record / field: One row in a database table: everything stored about one thing.
Primary key: A field whose value is unique for every record.
SQL: Structured Query Language: asks databases questions.
Machine learning: The computer finds rules itself from labelled examples.

The explanation

Read it here, or open the study page below to go one step at a time with things to try.

Why data needs cleaning

Dirty data is data with mistakes or gaps. Real survey data is almost always dirty.

People skip questions, make typos, or type answers in their own way. One bad value can wreck a whole answer.

Hours of sleep	Mean
8, 7, 9, 300, 8	332 ÷ 5 = 66.4 hours
8, 7, 9, 8	32 ÷ 4 = 8 hours

One wrong value, 300, made the mean more than eight times too big.

★Remember

Cleaning means finding the problems and deciding what to do with each one. You clean before you analyse.

Five common problems

Problem	What it looks like	Why it matters
Missing value	A blank cell	Some formulas skip it. Others count it as 0.
Wrong format	'7h' or 'seven' in a number column	Text cannot be added up or averaged.
Impossible value	300 hours of sleep in one night	A night has 24 hours at most, so it must be a mistake.
Spelling differences	'bus', 'Bus' and 'bus ' (with a space)	A program may count them as different answers.
Duplicate	The same person's answers twice	That person counts twice.

An outlier is a value far away from the rest. Check every outlier, but do not assume it is wrong.

e.g.Example

11 hours of sleep is unusual, but possible. Keep it. 300 hours is impossible. It cannot stay.

Find, then decide

Spreadsheet tools find problems fast. Here the sleep data is in B2 to B31.

Tool	What it finds	Spanish Excel
`=COUNTBLANK(B2:B31)`	How many cells are empty	CONTAR.BLANCO
`=COUNTA(B2:B31)-COUNT(B2:B31)`	How many cells hold text, like '7h'. COUNTA counts every filled cell; COUNT counts only numbers.	CONTARA, CONTAR
Sort the column	The smallest and biggest values move to the top and bottom	Ordenar
Filter the column	A list of every different answer, so spelling differences stand out	Filtro

Then decide what to do with each problem:

Fix it when you know the true value: '7h' becomes 7.
Blank it when it is impossible and you cannot check: 300 becomes a blank.
Keep it when it is unusual but possible: 11 hours stays.
Delete the row only when it is a true duplicate.

!Watch out

Never delete a value just because you do not like it. That changes the answer. It is not cleaning.

Log every change

Keep a cleaning log on a separate tab called 'cleaning'. It lists every change you made.

Cell	Was	Now	Why
B4	7h	7	Text in a number column. The person meant 7 hours.
B9	300	(blank)	Impossible: a night has 24 hours at most.
C12	'Bus ' (with a space)	bus	The same answer, typed differently.
Row 20	(whole row)	Deleted	The same person answered twice.

?Why?

Why keep a log?

Anyone can check your choices, or undo one. Honest data science always says what was changed, and why.

e.g.Worked example

The sleep column holds: 8, (blank), 7h, 9, 300, 7.5. Clean it, then find the mean.

The blank: we cannot know the answer, so leave it blank. AVERAGE skips blank cells.
'7h' is text. The person clearly meant 7, so change it to 7 and log it.
300 is impossible in one night. Blank it and log it.
The clean values are 8, 7, 9 and 7.5. That is 4 values.
Mean = (8 + 7 + 9 + 7.5) ÷ 4 = 31.5 ÷ 4 = 7.875. That is about 7.9 hours.
Before cleaning, AVERAGE skips '7h' and the blank, and gives 324.5 ÷ 4 ≈ 81 hours. Cleaning matters.

Study it

Open the study page and go one step at a time.

Topic 18 · Year 7

Data, spreadsheets and AI

Data is raw facts. Information is data with meaning. Spreadsheets calculate, databases store and search, AI learns patterns from examples.

The steps for this lesson

Topic 18, step 3: Formulas and functions (A tiny spreadsheet)

Try it

Check yourself

One question at a time. The reason shows after each answer.

Saved on this device.