Pandas Data Cleaning: From Messy to Beautiful

The Scoresheet That Lied

Imagine your school's coding club just finished a quiz contest with ten entries typed in by different volunteers on different laptops. Someone hands you the results and asks: "Who won, and what was the average score?" You open the sheet and something feels off. "Rohan" appears once with no score at all, and again as "ROHAN" with a 65. "Priya" is listed twice with identical scores of 92 — did she really submit twice, or did someone paste her row in by accident? One score reads "71 " with a trailing space that you can't even see. If you naively add up every row and divide by the count, you will get a number that is confidently wrong — and nobody looking at the final report will know it, because a wrong average doesn't come with a warning label.

This is the real problem that data cleaning solves. It is not about making a spreadsheet "look nice." It is about the fact that a computer treats "Rohan" and "ROHAN" as two completely different pieces of text, treats a blank cell and a zero as two completely different facts, and will silently compute an average using duplicate rows unless you tell it not to. In this chapter, you will learn to use pandas, the Python library that professional data analysts, government statisticians, and companies from Zomato to ISRO's mission-data teams use to turn exactly this kind of mess into something trustworthy — and you will trace every single line of code by hand so you know precisely why it works.

What Pandas Actually Is

You already know Python lists and variables. A library is simply a collection of ready-made tools that someone else wrote, which you can bring into your own program with an import statement instead of writing everything from scratch. Pandas is a library built specifically for working with tables — rows and columns, exactly like a spreadsheet, except now every row and column is something you can search, filter, and transform using code instead of your mouse.

In pandas, a table is called a DataFrame. Think of it as a spreadsheet that has been loaded into Python's memory: it has rows (numbered starting at 0, called the index), columns (with names, like "Name" or "Score"), and every cell sits at the intersection of one row and one column — just like cell B3 in Excel. A single column pulled out of a DataFrame is called a Series. That's really all the new vocabulary you need before we start: DataFrame = the whole table, Series = one column of it.

Building Our Messy Dataset

Let's recreate the contest scoresheet exactly as it might have been typed in, with all its real-world flaws intact: inconsistent capitalization, stray whitespace, a missing value, a value written as the text "N/A" instead of being left blank, and duplicate entries.

import pandas as pd
import numpy as np

data = {
    "Name":  ["Aarav ", "aarav", "Priya", "Priya", "Rohan",
              "ROHAN", "Sneha", "Vikram", "Vikram", "Ishaan"],
    "City":  ["Mumbai", "Mumbai", "Delhi", "Delhi", "Bengaluru",
              "bengaluru", "Chennai", "Pune", "Pune", "Kolkata"],
    "Score": ["78", " 78", "92", "92", np.nan,
              "65", "N/A", "88", "88", " 71"]
}
df = pd.DataFrame(data)
print(df.shape)

Run this and df.shape prints (10, 3) — pandas is telling you, in the order (rows, columns), that this table has 10 rows and 3 columns. Notice something important about how the Score column was typed in: every value is written inside quotes, as text, even though it represents a number. This happens constantly in real data — someone exported the sheet from a form, and every field came out as text by default. Pandas does not know yet that "78" is a number; to pandas right now, it is just three keyboard characters.

Step 1: Inspect Before You Clean

The single most important habit in data cleaning is to look before you touch. Never assume you know what's wrong with a dataset — ask pandas to show you.

print(df.head(3))
print(df.dtypes)
print(df.isnull().sum())

df.head(3) prints the first three rows so you get a feel for the data without dumping all ten rows on screen — useful when a real dataset has thousands of rows instead of ten. df.dtypes prints the data type pandas has assigned each column. Here it will show object for all three columns — pandas' label for "text, or a mix of text and other things." This confirms our suspicion: the Score column, which should behave like numbers, is currently stored as text and cannot yet be averaged, sorted numerically, or compared with > or < in a way that makes sense. df.isnull().sum() counts, per column, how many cells pandas already recognizes as missing (written as NaN, short for "Not a Number," pandas' universal marker for a missing value). At this point it reports exactly one missing value in Score — Rohan's blank entry — because the "N/A" text that Sneha's row contains looks like an ordinary word to pandas right now, not yet like a missing value. That distinction matters, and we'll fix it in Step 3.

Step 2: Fixing Text — Whitespace and Case

To a human, "Aarav " and "aarav" are obviously the same eleven-year-old. To a computer, string comparison is done character by character, and a trailing space or a capital letter is a real difference. "Aarav " == "aarav" evaluates to False in Python — not almost equal, not close enough, simply not the same value. This is why two "different" students named Aarav can end up in your dataset even though only one Aarav exists.

Pandas gives every text column a .str accessor — a gateway to string operations applied to every cell in the column at once, without writing a loop.

df["Name"] = df["Name"].str.strip().str.title()
df["City"] = df["City"].str.strip().str.title()

.str.strip() removes leading and trailing whitespace from every cell in the column. .str.title() converts each cell to Title Case — capitalizing the first letter of each word. Trace it: "Aarav " becomes "Aarav" after .strip(), then stays "Aarav" after .title() since it's already title-cased. Meanwhile "aarav" becomes "aarav" after .strip() (nothing to trim), then becomes "Aarav" after .title(). Both rows now hold the identical string "Aarav". The same logic turns "bengaluru" into "Bengaluru", matching the other row that already said "Bengaluru". We have just made two students who were computationally "different people" into the same person — a prerequisite for finding duplicates correctly in Step 4.

Step 3: Fixing Types — Turning Text Into Numbers

Now the Score column. It still contains text like " 78" and the word "N/A". We fix this in two moves.

df["Score"] = df["Score"].str.strip()
df["Score"] = pd.to_numeric(df["Score"], errors="coerce")

First, .str.strip() removes the stray space from " 71", turning it into "71". Pandas' string methods automatically skip over any cell that is already NaN, leaving Rohan's missing entry untouched. Second, pd.to_numeric() attempts to convert every cell to an actual number. The keyword errors="coerce" is the crucial part: it tells pandas that if a cell cannot be converted — like the text "N/A", which is not a valid number — don't crash the program, just replace it with NaN. Trace the whole column: "78" → 78.0, "78" → 78.0, "92" → 92.0, "92" → 92.0, the existing NaN stays NaN, "65" → 65.0, "N/A" → NaN (newly coerced), "88" → 88.0, "88" → 88.0, "71" → 71.0. Run df.isnull().sum() again and Score now correctly reports two missing values, because the fake-text missing value has been unmasked as a real one. This is exactly why you inspect data both before and after each cleaning step — the "before" count was silently hiding a problem.

Step 4: Finding Duplicates the Naive Way — and Why It Fails

Now that names and cities are standardized, we can look for duplicate students. Pandas offers .duplicated(), which returns True for every row that repeats an earlier one, and .drop_duplicates(), which removes those rows. A first attempt might look like this:

naive = df.drop_duplicates(subset=["Name", "City"], keep="first")

The subset argument tells pandas to consider two rows duplicates if they match on Name and City, ignoring Score (since the whole point is that the same student might have different-looking score entries). keep="first" means: when duplicates are found, keep whichever copy appears earliest in the table and discard the rest.

Trace this against our table in its current row order. Rohan/Bengaluru appears at row index 4 (with score NaN) and again at row index 5 (with score 65.0). Row 4 comes first, so keep="first" keeps row 4 — the one with the missing score — and throws away row 5, which was the one row that actually recorded Rohan's real result. The naive approach just deleted the only correct data point we had for Rohan, purely because of the accident of which row happened to be typed in first. This is a genuine bug, and it is the kind of mistake that slips into real reports unnoticed.

Step 4, Corrected: Sort First, Then Deduplicate

The fix is simple once you see the problem: before removing duplicates, sort the table so that rows with real data come before rows with missing data. Then keep="first" will preserve the informative row automatically.

df = df.sort_values("Score", ascending=False,
                     na_position="last", kind="mergesort")
df_clean = df.drop_duplicates(subset=["Name", "City"], keep="first")
df_clean = df_clean.reset_index(drop=True)

na_position="last" pushes every NaN score to the bottom of the sort instead of letting it land arbitrarily. kind="mergesort" asks for a stable sort, meaning rows with equal scores keep their original relative order rather than being shuffled — this keeps our trace predictable. After sorting, Rohan's row with 65.0 comes before Rohan's row with NaN, because 65 outranks a missing value under na_position="last". Now drop_duplicates(keep="first") keeps the 65 and correctly discards the empty duplicate. reset_index(drop=True) just renumbers the remaining rows 0, 1, 2, … so the index is tidy again — the old index numbers are dropped rather than kept as a column.

Tracing the full table through this pipeline gives exactly six rows, one per real student, sorted from highest score to lowest:

     Name       City  Score
0   Priya      Delhi   92.0
1  Vikram       Pune   88.0
2   Aarav     Mumbai   78.0
3  Ishaan    Kolkata   71.0
4   Rohan  Bengaluru   65.0
5   Sneha    Chennai    NaN

Every duplicate is gone, every name and city is standardized, every score is a real number except Sneha's — which is genuinely, correctly, still missing, because she never actually got a recorded score. That last point leads to the most important misconception in this entire chapter.

Step 5: The Misconception — "Missing" Is Not "Zero"

A very common mistake students make is to "fix" every missing value by filling it with 0. It feels tidy — no more blank cells, no more NaN — but it silently changes the meaning of your data. NaN means "we don't know" or "this didn't happen." A score of 0 means "this happened, and the result was zero." Sneha's missing score most likely means she was absent or her sheet went missing — it does not mean she took the quiz and scored nothing. Treating those as the same fact produces a wrong answer that looks perfectly confident.

Watch what happens to the class average under each choice:

correct_avg = df_clean["Score"].mean()
# correct_avg = (92 + 88 + 78 + 71 + 65) / 5 = 394 / 5 = 78.8

wrong = df_clean["Score"].fillna(0)
wrong_avg = wrong.mean()
# wrong_avg = (92 + 88 + 78 + 71 + 65 + 0) / 6 = 394 / 6 ≈ 65.67

Pandas' .mean() already ignores NaN values by default — it correctly divides by 5, the number of students who actually have a score, giving 78.8. But if you first overwrite Sneha's missing score with 0 using .fillna(0), the same sum is now divided by 6, and the average collapses to about 65.67 — over 13 points lower, dragging down every real student's standing purely because of one manufactured zero. Neither the students who scored 65 to 92 did anything different in the second calculation; the entire drop is an artifact of a bad cleaning decision. The correct instinct is usually one of: leave the value as NaN and let aggregate functions like .mean() skip it automatically (as we did here), use .dropna() to remove that row entirely if the row is unusable for a specific analysis, or fill it with a value that has genuine meaning in context — never a bare 0 chosen only because it "looks complete."

There is a related, smaller distinction worth naming precisely: len(df_clean) gives 6 — the total number of student rows — while df_clean["Score"].count() gives 5 — the number of non-missing scores. If someone asks "how many students attempted the quiz," the answer is 5, not 6; if someone asks "how many students are enrolled in the contest," the answer is 6. Cleaning data doesn't just mean removing junk — it means keeping enough information to answer the actual question correctly.

The Cleaning Pipeline, Visualized

The diagram below follows one duplicated entry — Rohan's two rows — through every stage of the pipeline we just built, showing exactly why the order of operations (text first, then types, then sort-and-deduplicate) matters.

A Note on Real Files: read_csv and na_values

We built our DataFrame directly in code so every value could be traced precisely, but in real CBSE Informatics Practices work you will almost always load data from an actual .csv file using pd.read_csv("results.csv"). Real files are even messier than our example: missing values might be written as "N/A", "NA", "-", "none", or simply left blank, all in the same column. Rather than fixing these one by one after loading, you can tell pandas to recognize them as missing right at import time:

df = pd.read_csv("results.csv",
                  na_values=["N/A", "NA", "-", "none", ""])

Every value in that list is converted straight to NaN the moment the file is read, so df.isnull().sum() gives you an honest count of missing data immediately, instead of hiding some of it behind disguised text the way our Sneha example did until Step 3. It is good practice to always specify na_values explicitly rather than trust pandas' small default list, because different organizations invent different ways of writing "unknown."

Common Misconceptions, Named and Corrected

• "Filling every blank with 0 makes the data clean." Wrong — as shown above, it can silently distort every average and ranking computed afterward. Missing data should stay missing (NaN) unless you have a specific, justified value to put there.
• "Two rows that look the same to me will automatically be treated as duplicates by pandas." Wrong — pandas compares exact string values. "Rohan" and "ROHAN " are different strings until you standardize case and whitespace first. Clean text before you deduplicate, never after.
• "drop_duplicates(keep='first') is always safe." Wrong — "first" only means "the row that happens to appear earliest in the table's current order," which is not necessarily the most complete or accurate row. Sort deliberately by a column that reflects data quality (like a score, or a "last updated" date) before deduplicating, so "first" means "best," not "earliest by accident."

Check Your Understanding

1. A column named "Marks" shows dtype object even though every cell looks like a number. What is the most likely cause, and which pandas function fixes it?
2. You run pd.to_numeric(col, errors="coerce") on a column containing the value "absent". What does that cell become afterward?
3. Why must you clean whitespace and capitalization in text columns before calling drop_duplicates(), not after?
4. A dataset has 40 rows; a "Marks" column has 5 missing values. What does len(df) return, and what does df["Marks"].count() return?
5. Explain, using a concrete number, why fillna(0) on a scores column can produce a misleadingly low average.
6. You want to deduplicate a list of students by (Name, City) but keep the row with the most recent "Last Updated" date for each student. Describe the two-step approach (which method runs first, and why).

Answers: (1) The column contains at least one non-numeric entry (like a stray space, "N/A", or blank), forcing pandas to store the whole column as text; use pd.to_numeric(df["Marks"], errors="coerce"). (2) It becomes NaN, because "absent" cannot be converted to a number and errors="coerce" replaces unconvertible values with a missing marker instead of crashing. (3) Because pandas compares exact text — "Rohan" and "ROHAN " are different strings to a duplicate check even though they represent the same person; cleaning first makes true duplicates actually match. (4) len(df) returns 40 (total rows); df["Marks"].count() returns 35 (only non-missing values). (5) If five students scored a combined 400 marks and a sixth was missing, the true average is 400/5 = 80; filling the missing student with 0 changes it to 400/6 ≈ 66.7 — over 13 points lower, even though no real student's score changed. (6) Sort by "Last Updated" (most recent first, or however "best" is defined) using sort_values() first, then call drop_duplicates(subset=["Name","City"], keep="first") — sorting must happen before deduplicating so "first" corresponds to "most recent," not to accidental row order.

Summary

Cleaning data with pandas is not one operation — it is a disciplined sequence. First, inspect with .shape, .head(), .dtypes, and .isnull().sum() before changing anything, so you know what you're actually dealing with. Second, fix text with .str.strip() and .str.title() (or .str.lower()), because a computer compares strings character by character and whitespace or casing differences hide true matches. Third, fix types with pd.to_numeric(errors="coerce") to convert numbers stored as text into real numbers, turning any unconvertible entry into an honest NaN rather than a disguised piece of text. Fourth, deduplicate deliberately — sort by a column that reflects which copy of a duplicate is more trustworthy, then use drop_duplicates(subset=..., keep="first"), since text and type cleaning must happen first or true duplicates will slip past unnoticed. Fifth, treat missing values as missing — resist filling every gap with a lazy 0, since NaN carries the specific meaning "unknown" that a fabricated number destroys, and pandas' own aggregate functions like .mean() already skip NaN correctly by default. Follow this order — inspect, fix text, fix types, deduplicate, handle missingness honestly — and a spreadsheet that used to lie to everyone becomes a table you can actually trust an answer from.