The chart that counted one meal three times
Sam draws a bar chart of canteen sales straight from the till log. It shows Pasta bake, pasta bake and PASTA BAKE as three different meals.
The chart is wrong before it is drawn. Raw data must be prepared first.
Key words
| Word | What it means | Example |
|---|---|---|
| Raw data | Data as it was first collected, before any checking or tidying. | The till log |
| Cleanse | Remove or fix errors, repeats and missing values. | Drop a sale of minus 3 |
| Standardise | Make the same thing look the same everywhere. | One spelling for each meal |
| Format | The way data is laid out, such as case, units or date style. | Meal names in lower case |
| Context | Where, when and why the message will be used. | A wall poster seen at lunch |
| Requirement | Something the finished message must do or have. | Fits one slide |
| Duplicate | A row that appears more than once by mistake. | The same sale entered twice |
| Null | A missing value in a table. | A sale with no quantity |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can pick the raw data a message needs, clean and standardise it, check the requirements and show it as a visual.
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.
Step 1: identify the raw data
Start with the question and the requirements. Then pick only the data that answers it.
To compare meals sold, you need the meal and the quantity. The day may help. The cook's notes do not.
Step 2: prepare and standardise
To cleanse, remove or fix errors: repeats, missing values and impossible numbers. To standardise, make equal things look equal: same spelling, case and units.
Keep the raw data safe and work on a copy, so you can check what you changed.
Faults in the till log and the fix
| Fault | Example | Fix |
|---|---|---|
| Different spellings | PASTA BAKE and pasta bake | Trim spaces and use lower case |
| Duplicate row | Tue pasta bake twice | Keep one copy |
| Impossible value | A sale of minus 3 | Remove it and note why |
| Missing value | A sale with no quantity | Leave it out of the totals |
The cells students fill in are left blank in this preview.
Try it: look at the raw log
select meal, sum(qty) as total
from sales_raw
group by meal
order by meal;
-- Notice how one meal is split into several rows.
Try it: clean it, then draw bars
with clean as (
select distinct day, lower(trim(meal)) as meal, qty
from sales_raw
where qty is not null and qty >= 0
)
select meal, sum(qty) as total,
repeat('#', cast(sum(qty) // 3 as bigint)) as bar
from clean
group by meal
order by total desc;
-- One # is 3 meals. Try changing 3 to 6.