Deduplication

Duplicates appear whenever the same record enters a dataset by more than one route.

Files loaded twice, forms submitted twice and systems merged together are the usual sources.

Removing them matters because almost every count, sum and average assumes one row per real thing.

The first step is deciding what makes 2 rows the same, which is called the key.

For orders the key might be the order number, while for people it might be name, date of birth and postcode together.

Checking only the key columns catches rows that repeat the same order with a slightly different load time.

Once duplicates are found, one copy must be kept, often the first, the latest or the most complete.

In pandas the keep option of drop_duplicates chooses the first copy, the last copy, or none at all.

Near duplicates need fuzzy matching, which scores how similar 2 strings are rather than testing for an exact match.

Fuzzy matching makes 2 kinds of mistake: merging different people who look alike, and missing the same person written oddly.

Comparing every row with every other grows slow very fast, so candidates are usually grouped first, for example by postcode.

Deleting the wrong copy loses information, such as a newer phone number held only in the second record.

Merging the copies field by field, keeping the best value of each, avoids that loss at the cost of more work.

In practice the number of duplicates removed is logged on every run, so a sudden jump gets noticed.

The usual mistake is deduplicating on every column, which misses copies that differ only in a load time.