Clean for what?
Two analysts get the same messy order table. One must report total sales. The other must email every customer.
They clean it differently. The first can ignore a missing phone number. The second cannot. Cleaning is never done for its own sake. It is done to meet a need.
Key words
| Word | What it means | Example |
|---|---|---|
| Cleansing | Fixing or removing poor data so it can be used. | Delete a repeat |
| Parameter | A limit that shapes the task: what, when, how exact. | Only March, sales only |
| Assess | To look at the data and list its faults. | One repeat, one test row |
| Remove | To take out rows that should not be there. | The repeat |
| Edit | To correct a value. | Trim spaces, fix case |
| Parse | To read text and turn it into a form that is easy to use. | £2.00 into 2.00 |
| Requirement | What the cleaned data must be able to do. | Give total sales by item |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can clean a table for a stated need, using remove, edit and parse, and check the result.
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.
Worked example: the order table
| Step | What we find or do |
|---|---|
| Parameters | Need: total sales by item, real orders only |
| Assess | Id 2 appears twice. Id 3 is a test. Names have spaces. Items have mixed case. Prices hold a pound sign |
| Remove | The repeat of id 2 and the test row |
| Edit | Trim the name and make items small letters |
| Parse | Turn the price text into a number |
| Review | Four orders left. Totals now add up |
The cells students fill in are left blank in this preview.
Try it: clean in DuckDB
select distinct id, trim(customer) as customer, lower(item) as item, qty,
cast(replace(price, '£', '') as decimal(8,2)) as price
from orders
where status <> 'test'
order by id;
-- Try it: remove the word distinct and see the repeat come back.Try it: review the totals
select lower(item) as item,
sum(qty * cast(replace(price, '£', '') as decimal(8,2))) as sales
from (select distinct * from orders where status <> 'test')
group by lower(item)
order by item;
-- Review: do the totals make sense for the need?A common mistake
Warning
Many people clean everything they can see and never check the result against the need. Cleaning too much can throw away good rows. Always compare the cleaned data with the requirement, and keep the original.