Data Cleaning Exercises

Module 9 · Duplicates · 6 min

Duplicate reports

You'll be able to: Report duplicates with the survivor's key so reviewers can check the rule.

Before this: Find the row grain before removing duplicates

The idea

For every row that loses the survivor rule, report the key, the survivor's id and the reason.

Counts of duplicates per key help find sources that resend data.

Worked example

Input: 3 rows for key (O1, L1)

Visual
Remove duplicates → losers to exceptions as duplicate
SQL (DuckDB)
SELECT *, 'duplicate' AS reason FROM ranked WHERE rn > 1
pandas
ranked[ranked.rn > 1].assign(reason="duplicate")

Result: 2 rows reported as duplicate of the survivor

Common mistakes

  • Discarding losers.
  • No survivor reference.

Check yourself

What should a duplicate report include?

The key, the survivor and the reason.

Practice it

Deduplicate at the right grain.

Deduplicate order lines at the right grain · VisualDeduplicate order lines at the right grain · SQLDeduplicate order lines at the right grain · Python

Next: Deduplicate order lines at the right grain