Fifty-one players, or thirty-eight?
A made-up football club joins three sign-up lists into one. The new list says 51 players. The coach knows there are 38.
Nothing is wrong with the maths. Some players are on two lists, and a few are spelt two ways. The data was not ready.
Key words
| Word | What it means | Example |
|---|---|---|
| Sourcing | Finding out where the data you need is held and who owns it. | Three sign-up lists |
| Collecting | Getting a copy of the data into one place. | Save each list in one folder |
| Cleansing | Fixing or removing data that is wrong, odd or repeated. | Sam and sam become one name |
| Blending | Combining data from different sources into one consistent set. | Three lists become one |
| Duplicate | The same thing recorded more than once. | A player on two lists |
| Standardise | Write every value in the same style. | All names in small letters |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can say why data is prepared, and put sourcing, collecting, cleansing and blending in order with a job for each.
Starter: last lesson
Have a go
Starter: last lesson
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Why we prepare data
Data from real life is messy. People type names in different ways. Two systems record the same thing twice. A cell is left empty.
Prepare data first and the analysis uses it well. Skip that and the answer is wrong, and the answer still looks neat. Bad data in gives a bad answer out.
Worked example: the club, step by step
| Step | The job | The club example | If you skip it |
|---|---|---|---|
| Sourcing | Find where the data is and who owns it | Paper form, web form and an old sheet | You miss a list |
| Collecting | Copy it all into one place | Type the paper form. Save all three files together | Data stays scattered |
| Cleansing | Fix wrong, odd and repeated values | Sam and sam become one name | You count Sam twice |
| Blending | Combine into one consistent set | One list of 38 players | Three lists that do not fit |
The cells students fill in are left blank in this preview.
Try it: the cost of not cleansing
raw = ["Sam", "sam ", "Ana", "ANA", "Lee", "Sam"]
print("Rows in the list:", len(raw))
clean = []
for name in raw:
tidy = name.strip().lower()
if tidy not in clean:
clean.append(tidy)
print("Different players:", len(clean))
print(clean)
# Try it: add " lee" to the end of raw and run again.