Cleaning data

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

3. A Year level column contains the value 27. Which type of problem is this?

A An impossible value

B A missing value

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

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

Match the Pairs

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

Left column		Right column
1IF age < 0 OR age > 120 THEN flag the row as an error		AA 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.
2IF name = "" THEN mark the value as missing		BChecks for impossible values (such as a negative age) and flags the row so it can be fixed or removed
3Removing duplicate rows: only add a record to the clean list if its ID has not been seen before		CDeleting all rows with missing values without checking can throw away lots of useful data and bias the results.
4Fixing spelling variations by converting text to one consistent format before comparing		DFinds a missing value (an empty field) so it can be filled in, replaced or excluded
5IF mark > 100 THEN keep the row as valid		Ecity <- UPPERCASE(TRIM(city))
6Delete every row that has a missing value, without checking how many rows are lost		FIF id NOT IN seenIds THEN add record to cleanList

Definitions

data cleaning

Fill in the Blanks

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

if age < 0 __ age > 120 then delete row end if

for each row in data if row.id in seen then ______ row add row.id to seen

total ← 0 for each mark in marks if mark _ "" then total ← total + mark

cleaned _ empty list for each row in data if valid(row) then add row to cleaned

if mark _ 100 then print "Impossible mark" end if

Spot the Error

Shade in the letter of the line that contains an 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

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

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

Question 4

A for each row in data

B s ← trim(row.state)

C s ← upper(row.state)

D row.state ← s

Question 5

A if row.score ≠ blank then

B row.score ← average

C end if

D print row.score

Answer Key

Cleaning data

Section A: Quick Questions

B — A duplicate row that cleaning should remove

A — Finding and fixing mistakes in data so it is accurate and ready to use

A — An impossible value

B — A missing value

A — Standardise them to one correct spelling

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

Section C: Definitions

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.

Section D: Fill in the Blanks

if age < 0 or age > 120 then
  delete row
end if

for each row in data
  if row.id in seen then delete row
  add row.id to seen

total ← 0
for each mark in marks
  if mark ≠ "" then total ← total + mark

cleaned ← empty list
for each row in data
  if valid(row) then add row to cleaned

if mark > 100 then
  print "Impossible mark"
end if

Section E: Spot the Error

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.

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.

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

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

A — Only missing values should be replaced. With ≠ the code would overwrite scores that are already correct. The test must be row.score = blank.