An export nobody can use
The help desk system exports a file. One line says "12; High ;a4". The next says "13;HIGH;A4 ". Another says "14;urgent;a5".
The same priority is spelt three ways, and one word is not in the list. Before anybody can report on this, somebody must tidy it. That job has five steps.
By the end of this lesson
Note
I can put the five steps for tidying messy data in order and say what each does.
Key words
| Word | What it means | Example |
|---|---|---|
| Data wrangling | Turning messy raw data into data that is ready to use. | Tidying a help desk export |
| Structure | To put data into fields and rows. | Split a line at each semicolon |
| Clean | To fix or remove wrong, odd or repeated values. | High and HIGH become high |
| Validate | To check that data is sensible and allowed. | Priority must be low, medium or high |
| Enrich | To add useful extra data from somewhere else. | Add the hours allowed to reply |
| Output | To save or show the finished data. | A report line |
The cells students fill in are left blank in this preview.
Starter: what you already know
Have a go
Starter: what you already know
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Wrangling makes raw data ready
Data wrangling turns messy raw data into data that is ready to use. Use it when data comes from many places, or in different shapes, or with mistakes.
It is not one job. It is five steps, done in a set order.
What each step does
- Structure. Put the data in fields and rows.
- Clean. Fix spaces, capitals, repeats and gaps.
- Validate. Check each value is allowed. Flag or remove the rest.
- Enrich. Add useful facts, such as how many hours a reply is allowed.
- Output. Save or show the finished data in the form people need.
Worked example: one messy line
| Step | What we have | What happened |
|---|---|---|
| Raw line | 13;HIGH;A4 | Messy export |
| Structure | 13 | HIGH | A4 | Three fields |
| Clean | 13 | high | a4 | Spaces and capitals fixed |
| Validate | high is allowed | Passes the check |
| Enrich | high: reply in 4 hours | Extra fact added |
| Output | 13: high (reply in 4 hours) | Ready for a report |
The cells students fill in are left blank in this preview.