Missing values, duplicates, and bad rows · Working with Data

Almost every real dataset has missing values, duplicates, and a small number of rows that should not exist. These are not exotic problems. They are the background noise of data work. The skill is recognising them quickly, deciding what to do with them, and not silently passing the mess downstream into a model. This section is the first cleaning pass on any dataset you pick up.

Setting up a small messy dataset

For this section we will work with a small, deliberately-messy version of the transactions dataset from Course 01. Put this in data/messy_transactions.csv:

vendor,amount,date
Khalti,5000,2026-06-25
eSewa,1200,2026-06-25
Khalti,,2026-06-26
Khalti,5000,2026-06-25
FonePay,900,2026-06-26
,3500,2026-06-26
Khalti,-200,2026-06-27

That has all three problems in seven rows: a missing amount, an exact duplicate, a missing vendor, and a negative amount (probably a refund or a data error — we will decide which).

Load it:

import pandas as pd
df = pd.read_csv("data/messy_transactions.csv")
print(df)

Detecting missing values

The first move on any new dataset is asking what is missing:

df.isna().sum()

Output:

vendor    1
amount    1
date      0
dtype: int64

One missing vendor, one missing amount. That is information; do not skip it. A column with 80% missing is a different problem from a column with 0.5% missing. The percentage tells you whether to delete the column, impute the values, or drop the affected rows.

A more visual look:

df[df.isna().any(axis=1)]

This shows you which rows have any missing value — so you can eyeball them and form a hypothesis about why they are missing.

Three honest options for missing values

Once you know what is missing, you have three legitimate moves:

1. Drop the rows. Simple, brutal, and the right answer when the missingness is rare and probably random:

df_clean = df.dropna()

The cost: you lose those rows. If 80% of rows have a missing value, you lose 80% of your data.

2. Drop the column. When a single column is mostly empty and you can live without it:

df_clean = df.drop(columns=["unreliable_column"])

3. Fill (impute) the values. Replace missing values with something reasonable. The right choice depends on the column:

df["amount"] = df["amount"].fillna(0)            # zero — careful, see warning
df["vendor"] = df["vendor"].fillna("Unknown")    # explicit unknown label
df["amount"] = df["amount"].fillna(df["amount"].median())  # median — robust to outliers

The decision is not technical; it is judgmental. Document what you chose and why, ideally in a comment near the line where you made the choice. Future-you will need to remember.

Detecting duplicates

A duplicate is a row whose values are identical (or near-identical) to another row. They sneak in through bad merges, double-entries during data capture, and exporters that include the same record twice.

df.duplicated().sum()           # how many duplicates
df[df.duplicated(keep=False)]   # show all rows that are duplicates of each other

The keep=False is important: by default .duplicated() marks only the second and later occurrences as duplicates; with keep=False it shows you all the matching rows side by side so you can see what was duplicated.

To remove duplicates:

df = df.drop_duplicates()

That keeps the first occurrence of each unique row and drops the rest. If you want to keep the last instead (sometimes more recent is more accurate):

df = df.drop_duplicates(keep="last")

Near-duplicates — the harder case

Often the trickier problem is near-duplicates: rows that should be the same but differ in a tiny way. Two rows for the same transaction where one has the vendor name as "Khalti" and another as "khalti " (lowercase, trailing space). Both are the same transaction; neither matches df.duplicated().

You catch these in stages — and we’ll do most of the work in Section 3 of this chapter (standardising names). For now, the pattern: normalise the columns you suspect of near-duplicates before checking for duplicates.

df["vendor"] = df["vendor"].str.strip().str.lower()
df = df.drop_duplicates(subset=["vendor", "amount", "date"])

The subset= argument tells pandas to consider only certain columns when judging duplication — useful when one column might legitimately vary but the rest should agree.

”Bad rows” — and what counts

A “bad row” is one that breaks an assumption you have about what valid data looks like. In our example, the amount of -200 is suspicious. It could be:

A refund (legitimate).
A data-entry error (someone typed minus instead of plus).
A debit row from a different transaction format that should not be in this file at all.

You cannot know which without context. The way to handle it depends on what kind of bad it is.

A useful pattern: range checks.

print(df["amount"].describe())

The .describe() summary will show you min, max, mean, and the quartiles. If the min is -200 and you expected only positive transactions, you have something to investigate. If the max is 999999999, same — investigate.

To flag suspicious rows:

suspicious = df[df["amount"] < 0]
print(suspicious)

Then decide: keep them (with a flag column), drop them, or fix them.

df["is_suspicious"] = df["amount"] < 0
# or
df_clean = df[df["amount"] >= 0]

A complete cleaning pass

Putting it all together. A first-pass cleaner for our small dataset:

import pandas as pd

df = pd.read_csv("data/messy_transactions.csv")

# 1. Normalise text columns
df["vendor"] = df["vendor"].astype(str).str.strip()

# 2. Handle missing values
df["vendor"] = df["vendor"].replace("", pd.NA).fillna("Unknown")
df["amount"] = df["amount"].fillna(df["amount"].median())

# 3. Remove exact duplicates
df = df.drop_duplicates()

# 4. Flag suspicious rows but don't auto-delete
df["is_suspicious"] = df["amount"] < 0

# 5. Save
df.to_csv("data/clean/transactions.csv", index=False)
print(df)

Five small steps, in roughly the right order. Most cleaning code you ever write will look like some elaboration of this. The order matters — normalising text before checking duplicates catches near-duplicates that would otherwise survive.

Check your understanding

Quick check

—

A loan-amount column has 0.5% missing values. The amounts otherwise range from Rs 5,000 to Rs 500,000 with a few extreme outliers (one entry of Rs 10,000,000 that's clearly real). Which imputation strategy is the safest default for the 0.5% missing?

Fill with 0 — it is the simplest default.
Fill with the column median, because median is robust to the extreme outlier and the missingness is rare. Document the choice with a comment.
Fill with the column mean, because the mean is more standard.
Drop the column entirely, because 0.5% missing is too high.

Quick check

—

You suspect your transactions dataset has near-duplicates because vendor names like 'Khalti', 'khalti ', and ' Khalti' might refer to the same transaction. What is the right order of operations?

Run `df.drop_duplicates()` first; pandas will figure it out.
Normalise the vendor column first — `df['vendor'] = df['vendor'].str.strip().str.lower()` — then run `df.drop_duplicates()`. Otherwise the duplicates do not match exactly and survive.
Delete every row where the vendor name has any whitespace.
Ask the user to retype the data.

What comes next

Missing values, duplicates, and bad rows are the universal three. The next section tackles a Nepal-specific problem that catches everyone: encoding. CSVs that show ?????? instead of Devanagari, dates that confuse Bikram Sambat and AD, and the half-dozen ways the same date can be formatted across a single ministry export. We fix all of it with the same kind of patient, pattern-based work you just did.