Cleaning data

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

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

Definitions

clean data

Fill in the Blanks

Complete each line of code by filling in the missing parts.

=_____(A2)

=COUNTIF(A2:A50, A2)_1

=__________(B2:B30)

=IF(C2_0, "Error", "OK")

=IF(B2_"", "Missing", B2)

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A names = ['Sam', 'sam', 'Mia']

B clean = []

C for n in names:

D clean.add(n.title())

Question 2

A rows = ['Ali', 'Bo', 'Ali']

B unique = list(rows)

C print(len(unique))

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)

Question 4

A age = 250

B if age > 0 or age < 120:

C print('Keep')

D else:

E print('Fix this age')

Question 5

A ages = [10, None, 11]

B for i in range(len(ages)):

C if ages[i] = None:

D ages[i] = 10

Answer Key

Cleaning data

Section A: Quick Questions

A — Change them all to the same spelling, 'Banana'

B — Delete one of the duplicate rows

A — Find the missing value and fill it in, or mark it as missing

A — An impossible value, which should be checked and fixed (maybe it is 12)

B — So the results and the chart are correct

Section B: 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.

Section C: Definitions

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

Section D: Fill in the Blanks

=LOWER(A2)

=COUNTIF(A2:A50, A2)>1

=COUNTBLANK(B2:B30)

=IF(C2<0, "Error", "OK")

=IF(B2="", "Missing", B2)

Section E: Spot the Error

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()).

B — list(rows) keeps the duplicate 'Ali'. To remove duplicate rows, turn the list into a set first: list(set(rows)).

A — The list starts with [ so it must end with ], not ). It should be scores = [8, None, 7, 9].

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.

C — A single = stores a value. To check for a missing value, use 'is None' (or ==). It should be: if ages[i] is None: