Cleaning Data, Part One: Duplicates, Gaps and Wrong Types
Find exact duplicates, count missing values, and track down the one word that turns a number column into text.
In this lesson you will
- Find and remove exact duplicates
- Count missing values and choose how to handle them
- Find values of the wrong type
- Record each cleaning decision
About 26 minutes
Garbage in, garbage out
Leila asks a simple question: “How much money did we take in?” The quickest answer is to add up the cost column. But the file has gone through many hands: volunteers typed some rows, a helper pasted others in, and nobody checked. If she adds it up now, the answer will be wrong, and she will not know by how much. Cleaning means finding and fixing such problems before you analyse. This lesson covers three of the most common: duplicates, missing values and wrong types.
Duplicates
A duplicate is the same record entered twice. Counting it twice inflates every total. Two visits in the file appear twice, and you can see them here with the same visit number, the same date and the same everything else:
6,2026-09-15, ben cole,hybrid,gears,44,21.0,5,poster
6,2026-09-15, ben cole,hybrid,gears,44,21.0,5,poster
23,2026-10-03,Eli Moss,hybrid,wheel,50,free,3,online
23,2026-10-03,Eli Moss,hybrid,wheel,50,free,3,online
Not every repeated value is a duplicate. Two different visits can have the same customer, repair and cost. What makes these two rows duplicates is that the whole row matches, including the unique visit number. Always decide what identifies a record before you remove anything.
Missing values
A blank in a table means one of several things: the person was not asked, they did not answer, the value does not apply, or someone forgot. You have three honest choices. You can remove the rows, which loses their other facts. You can fill the gap with a sensible value, which invents data and needs a reason. Or you can leave it missing and make every calculation ignore it, so that nothing is invented. For ratings, the third is usually right: a made-up rating is worse than none.
Wrong types
Remember the cost column that was listed as text. The program below finds out why. It also counts the duplicates, drops them, and measures how many ratings are missing.
import pandas as pd
df = pd.read_csv("visits.csv")
print("rows before:", len(df))
print("exact duplicate rows:", df.duplicated().sum())
df = df.drop_duplicates()
print("rows after:", len(df))
print("missing ratings:", df["rating"].isna().sum())
print("share missing:", round(df["rating"].isna().mean() * 100, 1), "percent")
print("cost column type:", df["cost"].dtype)
numeric = pd.to_numeric(df["cost"], errors="coerce")
print("costs that are not numbers:", list(df["cost"][numeric.isna()]))
rows before: 40
exact duplicate rows: 2
rows after: 38
missing ratings: 5
share missing: 13.2 percent
cost column type: str
costs that are not numbers: ['free']
The file had 40 rows, of which 2 were exact duplicates, so 38 real visits remain. There are 5 missing ratings, which is 13.2 percent of the visits. And the stubborn cost column contains the word free, typed by a volunteer for a repair that cost nothing. One word was enough to turn the whole column into text, which would make every sum fail.
Now the decisions. Duplicates are removed, because they are the same visit counted twice. The word free becomes zero, because that is what it means, and the decision is written down. The missing ratings stay missing: the program will not guess them. Writing the decision down matters as much as making it, and the next lesson collects them into a cleaning log.
Three questions for any problem
- What does it mean? Is a blank a true zero, a refusal, or a forgotten entry? The answer changes what you do.
- How many are affected? Two odd rows out of forty is a note. Twenty is a different dataset.
- What changes if I fix it? Compare a total before and after. If the answer to your question moves a lot, the cleaning decision matters, and you should say so in your report.
Asking these questions turns cleaning from a chore into part of the thinking. Each answer is a small piece of knowledge about how the data came to exist.
Dropping every row that has any gap. Here that would remove more than one visit in eight just to avoid a blank rating, and the loss may not be random. Remove only when you have a reason.
The visits are made up, and so is the idea that a free repair is typed as a word. In your own data, look for different markers of a missing value, such as a dash, n/a, zero or a blank. Percentages are rounded.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What makes two rows duplicates in this file?
- The same customer appears twice
- The whole row matches, including the visit number
- The same repair appears twice
- The same cost appears twice
Two different visits can share a customer or repair.
Why does the word free in the cost column cause trouble?
- It makes the file larger
- It is a duplicate
- It deletes the date
- It turns the whole column into text, so sums fail
One word stopped the column from being numbers.
What is a safe way to handle a missing rating?
- Fill it with 5
- Leave it missing and let calculations ignore it
- Delete the whole file
- Replace it with the visit number
A made-up rating is worse than none.
Which of these is a disguised missing value?
- A date
- A bike type
- A visit number
- A dash or n/a typed where a number should be
Gaps can appear in many forms, not just blanks.
Why write each cleaning decision down?
- So others, and you later, can see what changed and why
- Because Git requires it
- To make the file smaller
- To hide the changes
The cleaning log in the next lesson collects them.
Summary
- A duplicate is a whole matching record, not just a repeated value.
- Remove, fill or leave a gap missing, and give a reason.
- One stray word can turn a number column into text.
- Write every cleaning decision down.
Questions
Should I always remove duplicates?
Only exact duplicates of the same record. Two different events that look alike are not duplicates. Decide what identifies a record first.
Is it ever right to fill in a missing value?
Sometimes, when there is a good reason and you say so, for example filling a missing count with zero when blank truly means none. Never fill without a documented reason.
How much missing data is too much?
There is no single limit. It depends on the question and on whether the gaps are random. Report the amount, and think about whether the people with gaps differ from the rest.
