What Dirty Data Looks Like
What You Will Learn in This Lesson
By the end of this lesson, you will be able to:
- Name the four problems that turn up in almost every real data file.
- Explain why each one produces a wrong answer rather than an error.
- Inspect a column with
setand a count before summarising it. - Write a small report of what is wrong with a table.
Why This Matters
A crash tells you where to look. Dirty data does not crash: a blank score, a number stored as text, a student entered twice all flow through to an average that looks exactly as trustworthy as a correct one. Learning to look before you summarise is the habit that separates an analysis you can defend from one that is merely confident — and it costs a few lines.
Step 1: Four recurring problems
Almost every file you will ever clean has some mix of the same four faults.
Missing values and wrong types
A missing value is a blank, or a marker like "n/a", where a value should be. A wrong type is a number stored as text, which is every number read from a file until you convert it. Both break arithmetic, and some of those breaks are silent.
rows = [
{"name": "Ada", "score": "92"},
{"name": "Grace", "score": ""},
{"name": "Alan", "score": "n/a"},
]
for r in rows:
print(repr(r["score"]))Duplicates and inconsistent spellings
A duplicate is the same record twice, often from a file being appended to more than once. An inconsistent spelling is the same value written several ways — "Maths", "maths", " maths" — which a computer counts as three different things.
subjects = ["Maths", "maths", " maths", "Computing"]
print(len(set(subjects)), "distinct values for what is really 2 subjects")Mini Practice #1: Spot the four
Try It YourselfPrint the distinct values of subject and of score. Which of the four problems can you see?
Step 2: Why they are dangerous
The problem is not that dirty data is hard to work with. It is that it is easy to work with and wrong.
A wrong answer that looks right
A duplicated row is counted twice. Nothing about the resulting average tells you so.
scores = [92, 88, 79, 92] # Ada entered twice
print(round(sum(scores) / len(scores), 1))
clean = [92, 88, 79]
print(round(sum(clean) / len(clean), 1))Text that adds
Adding two strings does not raise — it joins them. A total computed over a text column is a long string of digits, and if it is then printed next to a label it can look like a number.
scores = ["92", "88"]
total = scores[0] + scores[1]
print("total:", total)Step 3: Look before you summarise
Three cheap looks catch most of the four problems.
The distinct values in a column
set(column) collapses a column to the values that actually occur. Blanks, markers and spelling variants all show up at once.
subjects = ["maths", "Maths", "art", "", "art", "n/a"]
print(sorted(set(subjects)))How many of each problem
Counting blanks and repeats turns “the data seems a bit messy” into “4 of 300 scores are blank and 2 students appear twice”, which is something you can act on and report.
scores = ["92", "", "88", "", "79"]
print("blank:", scores.count(""), "of", len(scores))
names = ["Ada", "Grace", "Ada"]
print("repeated:", len(names) - len(set(names)))Key idea: Looking is not cleaning. The next four lessons fix each problem in turn; this one is about knowing they are there before any number leaves your program.
Mini Practice #2: Count the blanks
Try It YourselfPrint how many scores are blank and how many names are repeated.
Step 4: When to Use It
Every new file
Look at the distinct values and count blanks and repeats before any summary leaves your program.
print(sorted(set(column)))When a result surprises you
An unexpected average is more often dirty data than a discovery. Inspect before you explain.
When Not to Use It
Do not start fixing as soon as you see a problem. Count all four kinds first, so you can report what was wrong and decide the order of the fixes.
End-of-Lesson Exercises
Exercise 1: Count the missing scores
Write count_missing(rows) that returns how many rows have an empty string for score.
An empty string is falsy, which is convenient and also a trap: 0 is falsy too. Compare against "" when that is what you mean.
Exercise 2: A dirt report
Write dirt_report(rows) for rows with name and score keys (both strings). Return a dictionary with three counts: "blank", scores that are empty or only spaces; "not_number", non-blank scores that are not made of digits; and "repeated", how many rows repeat a name already seen.
Keep a set of names seen so far. For each row: if score.strip() == "" it is blank; elif not score.strip().isdigit() it is not a number; and if the name is already in the set it is a repeat.