The hottest day was 99.9 degrees
A bike hire shop exports a year of its records. The new manager asks for the hottest day of 2025. The answer comes back as 99.9 degrees.
Nobody rode a bike in that heat. Somebody typed a value wrongly, and every chart built on it is wrong too.
By the end of this lesson
Note
I can find and fix bad data.
Key words
| Word | What it means | Example |
|---|---|---|
| Data cleaning | finding and fixing faults before you use data | |
| Missing value | a cell with nothing in it | NaN |
| Duplicate | a row that appears more than once | |
| Outlier | a value far away from all the others | 99.9 |
| Consistent | written the same way every time | |
| Median | the middle value when they are put in order |
The cells students fill in are left blank in this preview.
Before you start
Note
Press Start the lab. The lab is one container called jupyter. The terminal has no Python of its own, so every Run starts with docker exec jupyter, which runs one command inside that container.
Look at the data before you fix it
The file hires.csv has one row for every day of 2025. A script in the lab makes messy.csv, a copy with the faults a real export has.
The Run below makes that copy and reports on it. It counts the rows, says the type of each column, counts the empty cells and the repeated rows, and lists the weather words.
Make the messy copy and report on it
docker exec jupyter python3 steps/messy.pyShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Worked example: reading the report
| The report says | What it means | Kind of fault |
|---|---|---|
| rows: 367 | A year has 365 days | Duplicates |
| repeated rows: 2 | Two days were written twice | Duplicates |
| hires is object, not a number | Some cells hold words, such as "unknown" | Wrong type |
| temp_c 5 | Five days have no temperature | Missing values |
| highest temperature: 99.9 | Impossible for a British year | Outlier |
| sunny, Sunny and SUNNY | One weather word written three ways | Not consistent |
The cells students fill in are left blank in this preview.
Fix each fault with one rule
Each kind of fault gets one rule. Remove the repeated rows. Turn hires into numbers and remove any that is not a count.
Take the spaces off the weather words and make them small letters. Mark the impossible temperature as missing. Then fill every missing temperature with the median, so no day is lost.
Apply the rules and count each step
docker exec jupyter python3 steps/clean.pyShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Find the faults yourself in a notebook
Open JupyterLab from the bar. Choose File, New, Notebook. Type the first cell below, then press Shift and Enter to run it. It shows only the rows that are repeats. Then type the second cell in a new cell and run it.
Cell 1: show the repeated rows
import pandas as pd
# messy.csv was made by the Run above, in the same folder.
messy = pd.read_csv("messy.csv")
messy[messy.duplicated()]Cell 2: tidy the weather words
# Take off the spaces, make small letters, then count each word.
messy["weather"].str.strip().str.lower().value_counts()A common mistake
Warning
Many people think cleaning means deleting every row with a problem. A day with no temperature still has a true count of hires. Delete a row only when its main value is wrong. When only one cell is wrong, mark it or fill it, and keep the row.
Write down every change you make
Cleaning changes the data, so somebody must be able to check what you did. Keep a cleaning log: each rule, and how many rows there were before and after it.
The cleaning Run printed its own log as it went. Here it is as a table.
Worked example: a cleaning log
| Step | Rule | Rows after |
|---|---|---|
| Start | Read messy.csv | 367 |
| 1 | Remove repeated rows | 365 |
| 2 | Remove hires that are not a count | 363 |
| 3 | Tidy the weather words | 363 |
| 4 | Mark 99.9 as missing, then fill 6 temperatures with the median | 363 |
The cells students fill in are left blank in this preview.
Going further: which days were lost?
Cleaning removed two days, so the year now has gaps. A good log names them.
Add a cell that shows the rows of messy.csv whose hires are not digits: messy[~messy["hires"].str.isdigit()]. The ~ means "not". Which two dates are they, and what would you tell the manager about them?