An export with holes in it
The shop's booking system exports a year of days for you to study. Some temperatures are blank. One day says 99.9 degrees. One hire count says "unknown".
A model fed this file learns from the faults as well as the facts. Cleaning comes first, and it is most of the work.
By the end of this lesson
Note
I can find and fix missing and wrong values in a data set.
Key words
| Word | What it means | Example |
|---|---|---|
| Missing value | a cell with nothing in it | NaN |
| Data type | the kind of value a column holds | float64 |
| Duplicate | a row that repeats another exactly | |
| Outlier | a value far from the rest | |
| Impute | fill a missing value with a sensible guess |
The cells students fill in are left blank in this preview.
Four faults to look for
Most bad data has one of four faults. A value is missing. A column has the wrong data type, so numbers are stored as text. A row is a duplicate. Or a value cannot be true.
Each fault has a quick test in pandas. Look before you change anything.
Worked example: each fault and its test
| Fault | How it shows | The test |
|---|---|---|
| Missing value | NaN in a cell | messy.isna().sum() |
| Wrong data type | object where you expect numbers | messy.dtypes |
| Duplicate | more rows than days | messy.duplicated().sum() |
| Impossible value | a maximum that cannot happen | messy["temp_c"].max() |
| Words that differ | Sunny and sunny counted apart | messy["weather"].value_counts() |
The cells students fill in are left blank in this preview.
Make the messy copy and inspect 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.
Type this in your notebook: missing rows
import pandas as pd
messy = pd.read_csv("messy.csv")
# Only the rows whose temperature is missing.
messy[messy["temp_c"].isna()]
A text column hides a number problem
One word in a column of numbers makes pandas store the whole column as text. Its type becomes object, and a mean no longer works.
pd.to_numeric(column, errors="coerce") turns every value it can into a number. What it cannot read becomes NaN, so you can find it.
Words have the same trap. "Sunny " with a space and "SUNNY" are different values to a computer.
Type this in your notebook: bad counts
# Turn the counts into numbers; what cannot be read becomes NaN.
counts = pd.to_numeric(messy["hires"], errors="coerce")
messy[counts.isna() | (counts < 0)]
Tidying words and checking counts
# Three weather words from a messy export.
words = ["sunny", "Sunny ", "SUNNY"]
for word in words:
print(repr(word), "becomes", repr(word.strip().lower()))
# Is a hire count a whole number?
for text in ["305", "-5", "unknown"]:
print(text, text.isdigit())
# Add "3.5" to the second list. What does isdigit say?
A common mistake
Warning
Many people think every outlier is an error to delete. An unusual value can be true: the busiest day in the data, 487 hires, is a real day. Remove or replace a value only when it cannot be true, such as 99.9 degrees or a count below zero.
Drop, fill or fix, and write it down
For each fault, choose one of three things. Drop the row when it cannot be saved, such as a hire count of "unknown". Fix it when the right value is clear, such as "Sunny " to "sunny". Impute it when a guess will do, such as the median temperature.
Keep the raw file unchanged and clean a copy, so every step can be checked and run again.
Worked example: the cleaning steps in order
| Step | Rows left | Why |
|---|---|---|
| Read messy.csv | 367 | The export as it came |
| drop_duplicates | 365 | Two rows were repeats |
| Keep counts of 0 or more | 363 | "unknown" and -5 are not counts |
| Set temperatures over 45 to missing | 363 | The 99.9 joins the missing values |
| Fill missing temperatures with the median | 363 | A guess is better than a gap here |
The cells students fill in are left blank in this preview.
Run the cleaning steps
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.
Going further
In your notebook, clean messy.csv a different way. Instead of the median of the whole year, fill each missing temperature with the median of its own month.
Hint: groupby("month")["temp_c"].transform("median") gives each row its month's median. Compare the filled values for 21 January and 1 June with the whole-year median. Which looks more believable, and why?