Unit 2 · Cleaning and transforming data¶
You have the data. Now look closely: dates written three different ways, prices missing, the same game listed twice, numbers stored as text. This unit is the "smelt and sort" stage: you audit a deliberately dirty table of Steam games and turn it, step by step, into something you can trust — then describe it in a report.
| Learning outcome | Hours | Term |
|---|---|---|
| RA2 — Cleans and transforms the data for its classification. | 12 | 1st |
What you will learn
- Audit a table and name its problems: missing values, duplicates, errors and outliers
- Normalise types, formats and units so every column says one thing in one way
- Categorise values into bands and tiers
- Impute missing values — and justify the choice instead of deleting rows
- Add synthetic variables: calculated columns and metrics
- Do the same cleaning in Power Query and compare it with SQL
- Summarise a dataset with descriptive statistics and write a descriptive report
Lessons¶
| # | Lesson | What it covers |
|---|---|---|
| 1 | Data quality | Auditing a table before you touch it |
| 2 | Cleaning: types and formats | Text, numbers, dates and the casts between them |
| 3 | Cleaning: units and duplicates | Units, hidden characters and repeated rows |
| 4 | Categorisation | Grouping values into bands with CASE |
| 5 | Imputation | What to put where a value is missing |
| 6 | Synthetic variables | Calculated columns and aggregate metrics |
| 7 | Power Query | The same cleaning, with applied steps instead of SQL |
| 8 | Exploratory statistics | Mean, median, spread and distribution |
| 9 | The descriptive report | Describing a dataset in numbers and words |
What you are graded on¶
The three assessment criteria of the unit's learning outcome, and the lessons that train each one.
| Criterion | Official wording, in English | Lessons |
|---|---|---|
| a) | The dataset is cleaned, normalising, categorising or imputing missing values. | Data quality · Types and formats · Units and duplicates · Categorisation · Imputation · Power Query |
| b) | Synthetic variables are added from aggregate calculations. | Synthetic variables |
| c) | A report is written highlighting the main characteristics of the data. | Exploratory statistics · The descriptive report |
The datasets for every lesson are on the Resources page.