Data Science · Data Science Core
Data cleaning
In Data Science because a claim is only as honest as the rows — missing values, wrong types, duplicates and obvious bad rows have to be faced before you summarise.
Rao's week did not arrive clean. Bala's visits cell is blank, Chitra's spent cell says the letters N/A, Anu appears twice with the same four facts, and a junk row has the name ???, visits -2 and spent 9999. Cleaning is deciding what each of those is — a fact you did not observe, a word sitting in a money column, a copy, or a row that cannot be a person — before you trust a total.
- Data Science
- Medium level
- 5 concepts
1Missing is a fact until you decide
Look at Bala's row as it arrived. The visits cell is blank. spent is 80, plan is drop-in, came_back is no. We know three facts about Bala and we do not know the visit count. A blank is not the number 0. Zero would mean 'we watched, and Bala did not visit'. A blank means 'we do not have that observation'.
Filling the blank with 0 pretends Bala stayed home. Dropping the row pretends Bala was never a customer. Either choice changes a later mean. On the eight-person week, the other seven visits add to 21. Fill Bala with 0 and mean visits are 21 / 8 = 2.625. Drop Bala and they are 21 / 7 = 3. If you later learn Bala visited once, the honest mean is 22 / 8 = 2.75. A later 'mean visits' is only honest if you remember which choice you made.
Figure. Bala's visits cell arrived blank. That is not the number 0. Deepak's visits cell is 0 — we watched, and he did not come. A blank and a zero are different facts.
Before you fill
- See itA blank visits cell is missing — not zero, and not a reason to delete Bala on sight.
- Name the choiceFill, drop, or keep the row and count the hole separately.
- Say itWrite the choice down. The mean visits you quote later depends on it.
| name | visits | spent | plan | came_back |
|---|---|---|---|---|
| Bala | 80 | drop-in | no |
Three treatments of Bala's blank
The other seven customers visited 4, 6, 0, 3, 2, 5 and 1 times (sum 21). Bala's visits cell is blank. What mean visits do you get if you fill 0, if you drop Bala, and if Bala really visited once?
- 4 + 6 + 0 + 3 + 2 + 5 + 121
- fill 0: (21 + 0) / 82.625
- drop Bala: 21 / 73
- Bala visited 1: (21 + 1) / 82.75
Pro tip. The three means are different stories. Pick one in writing before you quote 'mean visits'.
Coding lab. Find Bala's blank runs in the app, with checks on your output.
Bala's visits cell is blank and spent is 80. What is the honest first move?
- Treat visits as missing — not as zero — and decide whether to fill, drop, or keep
- Replace the blank with 0 so describe() runs
- Delete every drop-in row
A blank is a missing observation. Zero is a different story.
2Types that look like numbers
Chitra's spent cell arrived as the three letters N/A — a word meaning 'not available', not a rupee amount. The shop asked for rupees; it received text. A computer that stores that column as text (pandas calls this an object column) cannot add 320 + 80 + N/A + 0 and get rupees. A sum of words is not a money total.
Cleaning the type means making spent a number so 510 can sit beside 320. The usual move is: read each cell as a number where you can, and turn unreadable tokens into missing. After that, N/A is a hole — the same kind of hole as Bala's blank visits — and you face the same fill-or-drop choice. Do not leave a money column as text and then quote a total.
Figure. Four spent tokens as they arrived: 320, 80, N/A, 0. N/A is letters, not rupees. After coercion that slot is missing; 320 + 80 + 0 = 400 is all you can add.
N/A becomes missing
import pandas as pd
spent = pd.Series(['320', '80', 'N/A', '0'])
as_number = pd.to_numeric(spent, errors='coerce')
print(list(as_number))Coerce four spent cells
spent holds the text 320, 80, N/A, 0. After reading what you can as numbers, what is each slot, and what can you add?
- readable cells320, 80, missing, 0
- 320 + 80 + 0400
- Chitra's slotmissing — not 510 yet
Pro tip. errors='coerce' turns N/A into missing, not into a fake number like 0 or 9999.
Coding lab. Coerce spent including N/A runs in the app, with checks on your output.
spent holds 320, 80, N/A, 0. After pd.to_numeric(..., errors='coerce'), what is the N/A slot?
- Missing (NaN) — coerce turns unreadable text into missing
- The string N/A still
- 9999
errors='coerce' makes unreadable tokens missing, not a fake number.
3A copy is not a second customer
The export listed Anu twice with the same four facts: 4 visits, Rs 320, monthly, came back. That is one person written down twice, not two Anus. If you count both lines you report nine customers and six comebacks. The shop had eight people and five comebacks; the export stuttered.
Keeping one Anu restores the honest sheet. If two Anu rows disagreed — same name, different spend — that is a conflict, not a duplicate, and you stop rather than pick silently. A copy copies the yes. The shop did not gain a customer.
Figure. Two identical Anu rows: 4 visits, Rs 320, monthly, yes. The second line is a copy, not a second customer. Keep one and the week is eight people again.
| name | visits | spent | plan | came_back |
|---|---|---|---|---|
| Anu | 4 | 320 | monthly | yes |
| Anu | 4 | 320 | monthly | yes |
What the extra Anu does to the counts
The honest week is 8 customers and 5 comebacks. The export adds a second identical Anu row. What do the totals become if you keep both?
- customers if both Anu rows stay8 + 1 = 9
- came_back yes if both stay5 + 1 = 6
- after keeping one Anu8 customers, 5 yes
Pro tip. A duplicate copies the yes. The shop did not gain a customer.
Coding lab. Drop the extra Anu runs in the app, with checks on your output.
Two identical Anu rows land in the export. What does keeping both do to 'how many came back'?
- It counts Anu twice, so the comeback total is one too high
- It cancels Anu out
- It converts Anu into a drop-in
A duplicate copies the yes. The shop did not gain a customer.
4Impossible rows are not people
One export row is name ???, visits -2, spent 9999. Negative visits cannot happen in this shop — you cannot visit minus two times. Rs 9999 is an order of magnitude above Chitra's 510, the busiest real ticket. This is not Deepak's honest zero. Deepak is a quiet monthly member. The junk row is not a person.
Drop the row or quarantine it before you compute a mean. The honest eight-row mean spent is 1760 / 8 = 220. Add the junk 9999 and the mean becomes 11759 / 9 ≈ 1307. Cleaning is not moralising about customers — it is refusing rows that cannot be cases.
| name | visits | spent | keep? |
|---|---|---|---|
| Deepak | 0 | 0 | yes — quiet member, real case |
| ??? | -2 | 9999 | no — visits cannot be negative |
What the junk row does to mean spent
Honest week spend is Rs 1760 across 8 people. The junk row adds spent 9999. What is mean spent with and without that row?
- 1760 / 8220
- 1760 + 999911759
- 11759 / 91306.56...
Pro tip. Zero can be a fact. Negative visits cannot. One junk row moved the mean from 220 to about 1307.
Coding lab. Refuse the junk row runs in the app, with checks on your output.
Which row should you refuse before you compute mean spent?
- visits -2 and spent 9999
- Deepak's visits 0 and spent 0
- Hari's visits 1 and spent 60
Negative visits are not a customer-week. Zero can be.
5Lab: clean the dirty export
The dirty export has ten rows: the eight real people, a second identical Anu, and the ??? row with visits -2. This lab makes spent readable as numbers, drops the exact extra Anu, drops the row whose visits are negative, and prints how many rows remain. The honest sheet has 8 rows. Bala and Chitra stay — their holes are missing facts, not impossible rows.
pd.to_numeric(..., errors='coerce') is the type-clean from the previous concept: N/A becomes missing, 320 stays 320. drop_duplicates keeps one of two identical Anu rows. The filter visits >= 0, with a blank treated as 0 only for that comparison, drops -2 and keeps Bala. You are not filling Bala's visits for a mean yet — you are refusing a row that cannot be a person.
The check looks for 8. If you see 9, the extra Anu or the junk row is still in. If you see 7, you dropped Bala or Chitra with the junk. Missing is a hole. Negative visits are not a fact.
No diagram — the cleaned row count is printed by the coding lab.
| Step | Why |
|---|---|
| Coerce spent | N/A is text; a later sum needs numbers |
| Drop exact duplicate rows | The second Anu is a copy, not a customer |
| Drop visits < 0 | Negative visits cannot be a person-week |
| Print len(df) | The check looks for 8 — Bala and Chitra stay |
Coding lab. Clean the shop export runs in the app, with checks on your output.
After you drop the extra Anu and the visits=-2 row, why do Bala and Chitra still belong?
- They are real customers with a hole in one cell, not impossible rows
- Missing cells mean the person is fake
- N/A means spent was 9999
Holes are missing facts. Negative visits are not a fact.
Notes
- In Data Science because a claim is only as honest as the rows — missing values, wrong types, duplicates and obvious bad rows have to be faced before you summarise.
- A blank visits cell on Bala is not zero and not 'delete the person'. It is a missing observation: we know Bala spent Rs 80 and did not come back, and we do not know the visit count.
- Chitra's spent cell is the text N/A. The shop asked for rupees; it received a word. A sum of words is not a money total.
Exam traps & shortcuts
- Name the question before you open the sheet — a table without a question is just a dump.
- A number is a claim only when you can point at the rows that produced it.
Recap
Missing, types, duplicates, and impossible rows. Clean before you add.
- Missing is a fact until you decide
- Bala's blank visits cell is a missing observation, not a zero. Filling it with 0 or dropping Bala changes the mean visits — say the choice out loud.
- Types that look like numbers
- N/A in spent is a word. After turning readable cells into numbers, that slot is missing, and 320 + 80 + 0 = 400 is the sum of what you can read.
- A copy is not a second customer
- Two identical Anu rows would report 9 customers and 6 comebacks. The shop had eight people; keep one Anu.
- Impossible rows are not people
- visits -2 and spent 9999 cannot be a customer-week. Leaving that row in pulls mean spent from Rs 220 to about Rs 1307.
Practise Data cleaning
Reading is free and needs no account. Practice, mocks and progress live in the app.
- A 3-question practice set that ends the chapter
- 5 quick checks with worked explanations
- Timed mocks scored with the real marking scheme
- Readiness tracked per topic, kept on your device