Comet is ready to code the Q3 column. She reads the first answer and writes 3. She reads the second and stops.
"Longer evening hours with a capital L," she says. "And here is longer evenings. Are those the same answer?"
Wren looks over her shoulder. "And here is bike rack with an exclamation mark. And one that just says bikes. What do you notice?"
Comet sighs. "I printed Q3 as an open box. Everyone wrote it their own way."
"That was a fair mistake," Wren says. "Now we get to fix it carefully."
Nova hovers over the column. "Would you like a hint?" she asks. "Before you change anything, sort the answers you see into kinds of problems."
How do we fix messy data without changing what it means?
This week you will spot messy answers, write cleaning rules, clean all 40 Q3 answers and filter the sheet.
When people type answers into an open box, they spell, shorten and capitalize things in different ways. So the data may not be uniform.
Data sets of any size have challenges. They need cleaning. They can be incomplete or invalid, or need combining with other data.
| ID | Raw Q3 answer |
|---|---|
| M01 | repair table |
| M02 | Longer evening hours |
| M03 | a quiet room to study |
| M04 | longer evenings |
| M05 | a quiet room to study |
| M06 | repair bench |
| M07 | Repair Bench |
| M08 | quiet study room |
| M09 | bike rack |
| M10 | open later |
| M12 | Bike rack |
| M14 | bike rack! |
| M18 | (blank) |
| M19 | a launch pad |
| M22 | a rack for bikes |
This is made-up data from the Commons Count story: 15 of the 40 raw answers.
| Statement | True or false? |
|---|---|
| People who type into an open box often spell the same answer in different ways. | ? |
| A blank answer is an example of incomplete data. | ? |
| Only very large data sets need cleaning. | ? |
| "bike rack!" and "Bike rack" mean the same thing. | ? |
Strong start, data analyst. Tomorrow you will write the rules for cleaning without changing meaning.