Year 9 · Spring 1

Data Science and AI · Lesson 2 of 5

Cleaning: missing values and outliers

PracticalTopic 18 Data, spreadsheets and AI

Year 9 · Data Science and AI · Lesson 2 of 5

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.

Year 9 · Data Science and AI · Lesson 2 of 5

Success criteria

You have got it when you can say:

  • 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.

Year 9 · Data Science and AI · Lesson 2 of 5

Retrieval starter

Questions from before. Write your answers, then show them.

  1. Last lesson

    In Spanish Excel, =IF(B2>=25,"Pass","Retry") becomes…

    A =SI(B2>=25;"Aprobado";"Repetir")B =SI(B2>=25,"Aprobado","Repetir")C =IF(B2>=25;"Pass";"Retry")

    A. =SI(B2>=25;"Aprobado";"Repetir")

  2. Last unit

    Websites should store your password as…

    A A Caesar cipherB Plain textC A hash

    C. A hash

  3. Earlier this year

    open("log.txt", "w") on a file that already holds 100 lines…

    A Adds to the endB Gives an errorC Wipes it first

    C. Wipes it first

Year 9 · Data Science and AI · Lesson 2 of 5

Key words (1 of 2)

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.

Year 9 · Data Science and AI · Lesson 2 of 5

Key words (2 of 2)

SQL
Structured Query Language: asks databases questions.
Machine learning
The computer finds rules itself from labelled examples.

The explanation · step 1 of 4 · part 1 of 2

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 sleepMean
8, 7, 9, 300, 8332 ÷ 5 = 66.4 hours
8, 7, 9, 832 ÷ 4 = 8 hours

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

The explanation · step 1 of 4 · part 2 of 2

Why data needs cleaning (continued)

★Remember

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

The explanation · step 2 of 4 · part 1 of 2

Five common problems

ProblemWhat it looks likeWhy it matters
Missing valueA blank cellSome formulas skip it. Others count it as 0.
Wrong format'7h' or 'seven' in a number columnText cannot be added up or averaged.
Impossible value300 hours of sleep in one nightA 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.
DuplicateThe same person's answers twiceThat person counts twice.

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

The explanation · step 2 of 4 · part 2 of 2

Five common problems (continued)

e.g.Example

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

The explanation · step 3 of 4 · part 1 of 2

Find, then decide

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

ToolWhat it findsSpanish Excel
=COUNTBLANK(B2:B31)How many cells are emptyCONTAR.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 columnThe smallest and biggest values move to the top and bottomOrdenar
Filter the columnA list of every different answer, so spelling differences stand outFiltro

Then decide what to do with each problem:

The explanation · step 3 of 4 · part 2 of 2

Find, then decide (continued)

  • 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.

The explanation · step 4 of 4 · part 1 of 2

Log every change

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

CellWasNowWhy
B47h7Text in a number column. The person meant 7 hours.
B9300(blank)Impossible: a night has 24 hours at most.
C12'Bus ' (with a space)busThe same answer, typed differently.
Row 20(whole row)DeletedThe same person answered twice.

The explanation · step 4 of 4 · part 2 of 2

Log every change (continued)

?Why?

Why keep a log?

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

Year 9 · Data Science and AI · Lesson 2 of 5

Worked example (1 of 2)

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

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

Year 9 · Data Science and AI · Lesson 2 of 5

Worked example (2 of 2)

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

  1. Before cleaning, AVERAGE skips '7h' and the blank, and gives 324.5 ÷ 4 ≈ 81 hours. Cleaning matters.

Year 9 · Data Science and AI · Lesson 2 of 5

Try it: A tiny spreadsheet

Year 9 · Data Science and AI · Lesson 2 of 5

Quick quiz · 1 of 3

To show change over time, use a…

  1. A Scatter graph
  2. B Pie chart
  3. C Line chart

C. Line chart

A line joins the points in time order, so you see the trend.

Year 9 · Data Science and AI · Lesson 2 of 5

Quick quiz · 2 of 3

SELECT FirstName FROM Students WHERE Year = 8; returns…

  1. A The number of Year 8 students
  2. B Only the first names of Year 8 students
  3. C Every field of every student

B. Only the first names of Year 8 students

WHERE keeps the Year 8 records. SELECT keeps only the FirstName field.

Year 9 · Data Science and AI · Lesson 2 of 5

Quick quiz · 3 of 3

A face-recognition model works badly on faces unlike its training photos. This is…

  1. A Bias from unrepresentative training data
  2. B A syntax error
  3. C Overflow

A. Bias from unrepresentative training data

The model only learns what it is shown. Missing groups in the data means worse results for those groups.

Year 9 · Data Science and AI · Lesson 2 of 5

Exit ticket

Answer on paper before you go. Then check.

  1. Every spreadsheet formula starts with…

    A #B =C $

    B. = The = tells the spreadsheet: calculate this, don't just show it.

  2. =B2*C2 in D2 is filled down to D5. What is in D5?

    A =B2*C2B =E2*F2C =B5*C5

    C. =B5*C5 Relative references move with the formula: 3 rows down, so 2 becomes 5.