Three spellings of one city
A delivery firm asks you for the number of deliveries to each city.
Your count shows "Leeds" 120 times, "leeds " 14 times and " LEEDS" 6 times. Twelve rows have no city at all.
The report is wrong before you start. Real data is like this. Cleaning it is a large part of an analyst's week.
By the end of this lesson
Note
I can find and fix missing and wrong values in a table of data.
Key words
| Word | What it means | Example |
|---|---|---|
| Missing value | a value that was never recorded | None |
| Wrong value | a value that cannot be true | -8 minutes |
| Inconsistent | one thing written in different ways | |
| Clean | to find and fix bad values | |
| Rule | a test a good value always passes | minutes >= 0 |
The cells students fill in are left blank in this preview.
Rules find the bad values
You cannot fix what you have not found. So first you write rules.
A city must not be empty. A delivery time must be there, and it must be zero or more.
A loop tests every row against every rule. Each failure is printed with its row id, so you can look at it.
Report the bad rows
deliveries = [
{"id": 1, "city": "Leeds", "minutes": 42},
{"id": 2, "city": "leeds ", "minutes": 35},
{"id": 3, "city": "", "minutes": 51},
{"id": 4, "city": "York", "minutes": None},
{"id": 5, "city": "York", "minutes": -8},
]
# Look at every row and report what looks wrong.
for row in deliveries:
if row["city"] == "":
print("Row", row["id"], "has no city")
if row["minutes"] is None:
print("Row", row["id"], "has no time")
elif row["minutes"] < 0:
print("Row", row["id"], "has a time below zero")
# Add a check for a time over 300 minutes, then press Run.
What the checks found
| Row | Problem | Kind |
|---|---|---|
| 2 | city is "leeds " with a space | inconsistent |
| 3 | city is empty | missing |
| 4 | minutes is None | missing |
| 5 | minutes is -8 | wrong |
The cells students fill in are left blank in this preview.
Each problem has its own fix
An inconsistent value is fixed by changing it into one standard form. strip() removes spaces at each end and title() sets the capitals.
A missing or wrong value is harder. You may find the real value in another system. If you cannot, you leave the row out of that sum and say so in your report.
Put every city in one form
cities = ["Leeds", "leeds ", " LEEDS", "York", "york"]
# strip() removes spaces at each end. title() makes "leeds" into "Leeds".
clean = []
for city in cities:
clean.append(city.strip().title())
print("Before:", cities)
print("After: ", clean)
# Add " bristol" to the list, then press Run again.
A common mistake
Warning
Many people think a missing value means zero. It does not: a blank delivery time is unknown, not instant. If you fill blanks with 0, the average time drops and the report looks better than the truth.