Keeping a cleaning log

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which log entry is better for a cleaning log?

A Removed 6 duplicate rows because the same survey was submitted twice

B Tidied up the data a bit

2. What is the main purpose of a cleaning log?

A So others can check the cleaning and repeat it

B So the dataset takes up less storage space

3. Which detail tells a reader how many rows were affected?

A Replaced 'Vic' with 'VIC' in some rows

B Replaced 'Vic' with 'VIC' in 14 rows

4. When should you write a cleaning log entry?

A Only once at the end, from memory

B Straight after making each change

5. Which entry gives the reason for a change?

A Deleted 3 rows with no age because age is needed for the research question

B Deleted 3 rows with no age

Match the Pairs

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

Left column		Right column
1Row 14: Changed 'Vic.' to 'VIC' (6 rows)		AA cleaning log entry that records what changed and how many rows were affected
2Reason: Removed duplicate survey entries		BThe 'why' part of a cleaning log entry, explaining the purpose of the change
3A log entry should show how many rows were affected, so the change can be checked		CThe log was not recorded as each change was made, so details may be forgotten or wrong
4A cleaning log lets another person repeat the cleaning in the same steps		DToo vague: it does not say what changed, how many rows were affected or why
5Log: Fixed some data		EDeleted blank rows: 12 rows affected
6Cleaned the data, then wrote the log a week later from memory		FStep 3: TRIM applied to column B (names), 40 rows

Definitions

cleaning log

Fill in the Blanks

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

CREATE TABLE CleaningLog (why ____);

INSERT INTO CleaningLog ______ ('Trim', 12);

=_______(B2:B101,"N/A")

SELECT ___(rowsAffected) FROM CleaningLog;

SELECT ___ FROM CleaningLog;

Spot the Error

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

Question 1

A Rows before: 120

B Rows removed: 18

C Rows after: 112

Question 2

A Change: trimmed Name spaces

B Rows affected: 14

C Reason: to delete outliers

Question 3

A Change: removed 9 duplicates

B Rows affected: 5

C Reason: stop double counting

Question 4

A Step,Change,Rows

B 1,Trim names,14,Fix spacing

C 2,Remove blanks,6,Missing data

Question 5

A SET log TO empty list

B REMOVE blank rows

C ADD 'Cleaned data' TO log

D SAVE log WITH the data

Answer Key

Keeping a cleaning log

Section A: Quick Questions

A — Removed 6 duplicate rows because the same survey was submitted twice

A — So others can check the cleaning and repeat it

B — Replaced 'Vic' with 'VIC' in 14 rows

B — Straight after making each change

A — Deleted 3 rows with no age because age is needed for the research question

Section B: Match the Pairs

Row 14: Changed 'Vic.' to 'VIC' (6 rows) → A cleaning log entry that records what changed and how many rows were affected

Reason: Removed duplicate survey entries → The 'why' part of a cleaning log entry, explaining the purpose of the change

A log entry should show how many rows were affected, so the change can be checked → Deleted blank rows: 12 rows affected

A cleaning log lets another person repeat the cleaning in the same steps → Step 3: TRIM applied to column B (names), 40 rows

Log: Fixed some data → Too vague: it does not say what changed, how many rows were affected or why

Cleaned the data, then wrote the log a week later from memory → The log was not recorded as each change was made, so details may be forgotten or wrong

Section C: Definitions

cleaning log — A record of each change made to the data: what changed, how many rows it affected and why. It lets other people check the cleaning and repeat it.

Section D: Fill in the Blanks

CREATE TABLE CleaningLog (why TEXT);

INSERT INTO CleaningLog VALUES ('Trim', 12);

=COUNTIF(B2:B101,"N/A")

SELECT SUM(rowsAffected) FROM CleaningLog;

SELECT why FROM CleaningLog;

Section E: Spot the Error

B — The log numbers do not add up. 120 - 18 = 102, not 112. The log should say 8 rows were removed so the before and after counts match.

C — The reason does not match the change. Trimming spaces is done so names match, not to delete outliers. A log reason must explain the change that was actually made.

A — The change says 9 duplicates but 'Rows affected' says 5. A log must be consistent. The change line should say 5 duplicates.

A — The header is missing the Reason column, but each row has four values. A cleaning log needs what changed, rows affected and why, so the header should be Step,Change,Rows,Reason.

C — The log entry is too vague. It must say what changed and how many rows were affected, for example 'Cut 6 blank rows'. Otherwise nobody can check or repeat the step.