Module 11 · Core

Duplicates and data quality

Find repeated records and validate IDs, ranges, and allowed labels.

Before you start: You can filter rows, convert values, and count missing entries.

Python loads from this site on your first run.

Each run starts with fresh data. Download any exported files before another run. Your written answers and code drafts are saved on this device; export your work to keep a backup.

M11C01 · Explore · predict · practice

Find an exact repeated record

duplicated() marks repeated rows. By default, the first occurrence is kept unmarked and later copies are marked True. With keep=False, every member of a repeated set is marked.

ReadWhy use keep=False when investigating duplicates?

PredictHow many displayed rows and how many extra copies will we see?

ExplainIs a repeated name enough to identify a duplicate person?

Read the example

df = raw_students()
result = df.loc[df.duplicated(keep=False), ["Student_ID", "Name", "Score"]]
print(result)
print("Extra copies:", df.duplicated().sum())

Verified example output · pandas 2.2.3

   Student_ID   Name Score
2        S003  Chloe    91
12       S003  Chloe    91
Extra copies: 1

Watch for this. A duplicate record and a missing value are different problems.

Your turn

Remove exact duplicate rows and reset the index. Store the result in result.

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

df = raw_students()
result = df.loc[df.duplicated(keep=False), ["Student_ID", "Name", "Score"]]
print(result)
print("Extra copies:", df.duplicated().sum())

Show a hint

Use .drop_duplicates().reset_index(drop=True).

Show one worked solution

df = raw_students()
result = df.drop_duplicates().reset_index(drop=True)
print(result[["Student_ID", "Name", "Score"]])
print("Rows:", len(result))

Twelve rows remain. Other data-quality problems are still present and need separate treatment.

Compare your explanations

Read. It shows both the original row and its duplicate so we can compare them.
Predict. Two displayed rows for S003, but only one extra copy.
Explain. No. Different people can share a name; use the intended record key and relevant fields.

One quick check

What does duplicated() count by default here?

What you should be able to explain. Inspect repeated records before removing them.

Your own assessment, separate from code checks

M11C02 · Explore · predict · practice

A repeated ID is not always an exact duplicate

subset lets you check only the columns that define a record key. Two rows can share an ID but disagree on a score. Keeping the last one is defensible only when a documented order says it is the latest record.

ReadWhich field is being treated as the record key?

PredictAre the two S001 rows exact duplicates?

ExplainWhy would dropping one row without a rule be risky?

Read the example

df = pd.DataFrame({"Student_ID": ["S001", "S001", "S002"], "Updated": ["2026-10-01", "2026-10-03", "2026-10-02"], "Score": [80, 88, 72]})
result = df.loc[df.duplicated(subset=["Student_ID"], keep=False)]
print(result)
print("Exact duplicates:", df.duplicated().sum())

Verified example output · pandas 2.2.3

  Student_ID     Updated  Score
0       S001  2026-10-01     80
1       S001  2026-10-03     88
Exact duplicates: 0

Watch for this. In a transaction table, repeated student IDs may be expected; define what one row represents.

Your turn

Assume the later Updated date is the authoritative revision. Parse Updated, sort chronologically, and keep the latest row per Student_ID. Store the retained rows in result, sorted by Student_ID in ascending order.

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

df = pd.DataFrame({"Student_ID": ["S001", "S001", "S002"], "Updated": ["2026-10-01", "2026-10-03", "2026-10-02"], "Score": [80, 88, 72]})
result = df.loc[df.duplicated(subset=["Student_ID"], keep=False)]
print(result)
print("Exact duplicates:", df.duplicated().sum())

Show a hint

Date parsing is introduced in Module 13: use pd.to_datetime with format="%Y-%m-%d". Sort by Updated before drop_duplicates(subset="Student_ID", keep="last"), then sort the retained rows by Student_ID.

Show one worked solution

df = pd.DataFrame({"Student_ID": ["S001", "S001", "S002"], "Updated": ["2026-10-01", "2026-10-03", "2026-10-02"], "Score": [80, 88, 72]})
df["Updated"] = pd.to_datetime(df["Updated"], format="%Y-%m-%d")
result = df.sort_values("Updated").drop_duplicates(subset="Student_ID", keep="last").sort_values("Student_ID")
print(result)

S001 keeps score 88 from October 3; S002 keeps 72. The decision follows the stated update policy.

Compare your explanations

Read. Student_ID.
Predict. No. Their dates and scores differ.
Explain. We could discard the correct observation; the meaning of the records and update order matters.

One quick check

Does keep="last" automatically know the newest date?

What you should be able to explain. Choose a key and a documented conflict rule.

Your own assessment, separate from code checks

M11C03 · Explore · predict · practice

Flag impossible values instead of hiding them

Data can have the right type and still be invalid. If Attendance is a percentage, its allowed range is 0 through 100. A Boolean mask can create a review table before you decide how to repair the source.

ReadWhat does the ~ symbol do to the range check?

PredictWhich record will be flagged?

ExplainWhy should we not automatically change 105 to 100?

Read the example

df = raw_students()
invalid = ~df["Attendance"].between(0, 100)
result = df.loc[invalid, ["Student_ID", "Attendance"]]
print(result)

Verified example output · pandas 2.2.3

   Student_ID  Attendance
10       S011         105

Watch for this. The allowed range comes from the meaning of the variable, not from pandas.

Your turn

Preserve the source Attendance values in Attendance_raw. Convert Attendance to a nullable numeric column and set its out-of-range values to missing. Store the affected rows in result with columns Student_ID, Attendance_raw, and Attendance.

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

df = raw_students()
invalid = ~df["Attendance"].between(0, 100)
result = df.loc[invalid, ["Student_ID", "Attendance"]]
print(result)

Show a hint

Use a copy, astype("Float64"), and one .loc[mask, column] assignment.

Show one worked solution

df = raw_students()
df["Attendance_raw"] = df["Attendance"]
df["Attendance"] = df["Attendance"].astype("Float64")
invalid = ~df["Attendance"].between(0, 100)
df.loc[invalid, "Attendance"] = pd.NA
result = df.loc[invalid, ["Student_ID", "Attendance_raw", "Attendance"]]
print(result)

The original 105 is retained for follow-up. The cleaned measurement is marked unknown, not changed to a guessed value.

Compare your explanations

Read. It reverses True and False, so rows outside the range are selected.
Predict. S011, whose Attendance value is 105.
Explain. We do not know the correct value. Clipping can hide a recording error and overstate attendance.

One quick check

Is a numeric value always a valid measurement?

What you should be able to explain. Validate values against explicit domain rules.

Your own assessment, separate from code checks

M11C04 · Explore · predict · practice

Turn expectations into assertions

An assertion stops execution if an expectation is false. It is useful for classroom checks and analysis pipelines. Validate identity, required values, and ranges separately so a failure has a clear meaning.

ReadWhat happens if an assertion condition is False?

PredictWill these checks pass for students()?

ExplainWhy test missing IDs separately from uniqueness?

Read the example

df = students()
assert df["Student_ID"].notna().all(), "A student ID is missing"
assert df["Student_ID"].is_unique, "Student IDs must be unique here"
assert df["Score"].between(0, 100).all(), "Score outside 0-100"
result = {"rows": len(df), "valid_ids": True, "valid_scores": True}
print(result)

Verified example output · pandas 2.2.3

{'rows': 12, 'valid_ids': True, 'valid_scores': True}

Watch for this. Python can disable assert statements in optimized runs; formal applications may need explicit validation errors.

Your turn

Add checks requiring Campus to be RAK or Dubai and Attendance to be 0-100. Store the two Boolean results in a dictionary named result with keys campus_ok and attendance_ok.

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

df = students()
assert df["Student_ID"].notna().all(), "A student ID is missing"
assert df["Student_ID"].is_unique, "Student IDs must be unique here"
assert df["Score"].between(0, 100).all(), "Score outside 0-100"
result = {"rows": len(df), "valid_ids": True, "valid_scores": True}
print(result)

Show a hint

Use .isin([...]).all() for an allowed-label check.

Show one worked solution

df = students()
campus_ok = df["Campus"].isin(["RAK", "Dubai"]).all()
attendance_ok = df["Attendance"].between(0, 100).all()
assert campus_ok, "Unexpected campus label"
assert attendance_ok, "Attendance outside 0-100"
result = {"campus_ok": bool(campus_ok), "attendance_ok": bool(attendance_ok)}
print(result)

Both checks are True for the clean table. Applying equivalent checks to raw data would expose problems that need review.

Compare your explanations

Read. Python raises an AssertionError and stops the normal execution of the cell.
Predict. Yes. The clean teaching table has 12 unique nonmissing IDs and scores within the allowed range.
Explain. A column with one missing ID can still be unique; uniqueness alone does not require every ID to be present.

One quick check

For a Boolean Series with no missing values, what does .all() require?

What you should be able to explain. Write data expectations as checks you can repeat.

Your own assessment, separate from code checks

Previous: Missing values and honest cleaning Next: Clean and search text

Further detail: official documentation for this topic.