One date, two spellings
A made-up web shop sends an export. One row says 2026-09-01. Another says 02/09/2026. Both are real dates.
Sort the file by date and the second row lands in the wrong place. Today we turn a messy export into data that sorts, adds up and joins.
Key words
| Word | What it means | Example |
|---|---|---|
| Requirement | What the finished data must be able to do or show. | One row per sale in September |
| Gather | Collect the data you need from each source. | Export the web orders and the till sales |
| Remove | Take out rows that should not be there. | Test orders and exact repeats |
| Edit | Change a value so it is tidy and correct. | Take spaces off a name |
| Parse | Split or read a piece of text so its parts can be used. | Read 02/09/2026 as a date |
| Standardise | Write every value in one agreed style. | Every date as year, month, day |
| Test row | A made-up row used to check a system, not a real sale. | TEST ORDER |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can pick the sources a requirement needs, gather the data, and cleanse and standardise it by removing, editing and parsing.
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.
Start from the requirement
The shop owner writes the requirement: "One row for every sale in September, with the date, who bought, what and the price in pounds."
Read it for the fields it needs. Then ask which source holds each field. Gather only what the requirement needs.
Worked example: requirement to sources
| Field the requirement needs | Source | Gathered as |
|---|---|---|
| Date of the sale | Web export and till export | Text, in two styles |
| Who bought | Web export only | Text with extra spaces |
| What was bought | Till export | Text in mixed case |
| Price in pounds | Both exports | Text such as GBP 4.50 |
The cells students fill in are left blank in this preview.
Try it: look at the raw export
select * from web_raw;
-- Spot four problems: spaces, capitals, two date styles, and a test row.Three cleansing jobs
Remove. Take out rows that do not belong: test rows and exact repeats.
Edit. Fix values in place: take off extra spaces, make letters one case.
Parse. Read text so its parts can be used: turn 02/09/2026 into a real date, and GBP 12.00 into the number 12.00.
Then standardise: every value in a column ends up in one style.