Duplicate records · B02 · Sunflower Intelligence

Before you start

You will be able to

Distinguish exact duplicates, repeated keys with conflicting values and near duplicates, and explain how each kind arises.
Identify the business key that defines “the same record” for a dataset, and the evidence that separates a duplicate from a genuine repeat.
Choose a survivorship rule (first, latest update, most complete or merge) for a group of duplicates and justify it.
Calculate how duplicates distort totals, counts and averages.
Compute the edit distance between two short strings and explain how fuzzy matching uses it.
Find duplicates with a spreadsheet, with SQL and with pandas.

Review first

Datasets: rows, columns, records and variables — You need the unit of observation and the idea of a primary or composite key.
Profiling a dataset and writing validation rules — You need uniqueness as a data quality dimension and the habit of profiling a column before changing it.
Sums, counts and averages.

Fictional case A fictional coffee business founded by Ana Lim. It starts as a catering and barista-training service, opens its own cafés and an online shop for beans, and plans a canned cold brew.

The idea

Fictional case

Ana Lim’s Lantern Coffee Co. now sells beans and coffee subscriptions through an online shop. At the end of each month the shop platform produces an order export, lantern-orders, with one row per order and columns such as order_id, order_date, customer_id, email, product, quantity, unit_price and order_total. When Lantern’s bookkeeper adds up order_total for March, the result is higher than the money the payment provider actually paid out. Nobody has stolen anything. Some orders are in the file more than once.

A duplicate record is a row that describes the same real-world thing as another row in the same dataset: the same order, the same customer, the same hotel booking. Almost every analysis assumes that each thing is recorded once. If the unit of observation is one order, each order should occupy exactly one row; a second row for the same order makes the data say that something happened twice when it happened once.

Duplicates are dangerous because nothing about them looks wrong. A copied row has a valid date, a sensible price and a real customer, so it passes every range check and format check. It only shows up when you ask a question about the table as a whole: are the keys unique, does the total agree with another source, why does this customer have two accounts?

Three kinds of duplicate

Here are six rows from an order export.

Six rows from an online-shop order export (fictional)
Row	order_id	order_date	email	product	order_total
1	LC-10412	2026-03-02	maya.okafor@example.com	House Blend 250g	$14.00
2	LC-10412	2026-03-02	maya.okafor@example.com	House Blend 250g	$14.00
3	LC-10418	2026-03-03	tom.reyes@example.com	Colombia Huila 250g	$17.00
4	LC-10418	2026-03-03	tom.reyes@example.com	Colombia Huila 250g	$15.30
5	LC-10425	2026-03-04	j.chen@example.net	Ethiopia Yirgacheffe 250g	$19.50
6	LC-10426	2026-03-04	j.chen@example.net	Ethiopia Yirgacheffe 250g	$19.50

Exact copies. Rows 1 and 2 agree in every column. An exact duplicate is the easiest kind to find and, once you are sure it is a copy, the easiest to fix: keep one row and drop the others.
Repeated keys with conflicting values. Rows 3 and 4 share the order number LC-10418 but disagree on the total. They can’t both be right. Usually one is an earlier version of the order (before the 10% subscriber discount was applied, say: $17.00 \times 0.90 = 15.30$ ) and the other a later one. Removing exact copies won’t touch them, because they aren’t identical; you have to decide which version to keep.
Possible repeats that need evidence. Rows 5 and 6 have different order numbers but the same customer, product, price and day. That could be a customer who clicked “Pay” twice, or a customer who really did buy two bags, one for home and one as a present. The six columns can’t tell you which.

A further kind hides in lists of people and organisations. Here are four rows from Lantern’s customer list.

Four rows from a customer list (fictional; ␣ marks a trailing space, which a table would otherwise hide)
Row	customer_id	name	email	postcode
A	C0907	Maya Okafor	maya.okafor@example.com	HC4 7QT
B	C0988	maya okafor␣	maya.okafor@example.com	HC4 7QT
C	C0912	Maya Okafro	m.okafor@example.org	HC4 7QT
D	C0936	Mia Okafor	mia.okafor@example.com	HC2 1AB

Rows A and B are the same person: the names differ only in capital letters and a trailing space, and the email and postcode match. Row C is probably her too, with a misspelt surname and a second email address. Row D shares a surname but is someone else. Records like A, B and C, which describe the same entity but whose values differ slightly, in case, spacing, spelling or abbreviation or because the person used two addresses, are near duplicates. Exact comparison misses them; finding them takes standardisation and a measure of similarity, both introduced below.

Where duplicates come from

Exports and loads. A report is run twice and both files are appended; an export that pages through results repeats the last page; a failed upload is retried after part of it had already arrived.
Double submissions. A customer clicks “Pay” twice or refreshes a slow checkout page, or an app retries a request after a timeout.
System syncs. Two systems exchange records and one creates a new record instead of updating the existing one. The channel manager that passes bookings from travel websites to Harborview Hotel re-sends the whole booking each time it changes, and when a sync goes wrong it can create the same stay twice under two different booking references. The second kind is harder to catch, because the key itself differs: you have to compare the dates, room, guests and rate instead.
Joins. Combining two tables on a key that is not unique on one side repeats rows. If one customer appears twice in the customer table, joining orders to customers gives each of that customer’s orders two rows.
People. Two staff members register the same walk-in customer; an applicant applies to Northfield University twice with different email addresses.

The mechanism matters because it tells you which copy is right. A double click produces copies of one original. A sync produces a sequence of versions, of which the latest is usually the truth. A join produces copies that should never have existed, and the fix belongs in the join, not in the result.

What counts as “the same”?

Before you remove anything, decide what “the same record” means for this dataset. The business key is the column, or set of columns, that identifies one real-world thing in the business’s own terms: the order number for an order, the booking reference for a hotel stay, the student number for a student. In a well-designed database the business key is enforced as the table’s primary key, and the system refuses a second row with the same value. Exports, spreadsheets and merged files rarely enforce anything, which is how duplicates get in.

Sometimes no single column will do: a daily sales summary is identified by store and date together, a composite key. And sometimes there is no reliable key at all. A café’s till export that lists only date, product and price will contain two identical rows whenever two people buy a flat white on the same day. Those rows are identical, but they are not duplicates. Identical values are evidence that two rows describe one thing; they are proof only when the columns include something that should be unique.

The hard cases are repeats that could be genuine. A customer really can order the same bag of beans twice on the same day. To decide, look for evidence outside the columns you were comparing.

Evidence that separates a double submission from a genuine repeat order
Evidence	Points to a double submission	Points to a genuine repeat
Time placed	Seconds apart	Hours apart, or in different sessions
Payment	One charge, or a second charge that was refunded or voided	Two separate, settled payments
Delivery	Same address, one parcel shipped	Different addresses (a gift), or two parcels shipped
Status	One of the orders cancelled or voided	Both orders fulfilled
Order number	Not decisive: a shop may issue a new number for each click	Not decisive on its own either

The whole process of finding records that describe the same thing and reducing each group to one record is deduplication. It is a sequence of decisions, not a button: define the key, find the groups that share it, decide for each kind of group which record survives, leave the raw data untouched, and write down what you removed and why.

In depth

Exact duplicates, duplicate keys and how to count them

Call the number of rows $n$ . Two rows are exact duplicates if they agree on every column, and key duplicates if they agree on the business key. Every exact duplicate is also a key duplicate, provided the key columns are part of the row, but not the other way round: rows 3 and 4 in the first table share a key but differ in the total. A key is unique when the number of distinct key values equals the number of rows. That is the uniqueness check you met when profiling data.

The number of surplus rows, the rows you would remove if you kept one per group, is the number of rows minus the number of distinct values (formula below). Count it twice: once on whole rows and once on the business key. If the second count is larger, the difference is the number of conflicting versions: rows that share a key with another row without being a copy of it. Those need a decision rather than a deletion.

Profile the key before you delete anything, because the pattern of repeats tells you how they arose. Count the rows per key value. Mostly ones with a scattering of twos suggests double clicks. A contiguous block of keys that each appear exactly twice suggests a file, or part of one, loaded twice. Keys that appear four or five times, with a different timestamp each time, suggest a sync that sends every change.

Choosing the survivor

When several rows share a key, a survivorship rule decides which one stays, or how they are combined. The rule should follow from how the duplicates arose, not from habit.

Four survivorship rules
Rule	Keeps	Use when	Watch out for
First	The earliest record	Later rows are re-sends of an original, as with double clicks	File order is not time order; sort by a timestamp first
Latest update	The most recently updated record	Later rows are corrections or status changes: a modified or cancelled booking	Needs a trustworthy updated-at or received-at column
Most complete	The record with the fewest missing fields	Copies hold different amounts of detail	A full record can be out of date: completeness is not correctness
Merge	One record built field by field	Each source is best for different fields	Needs a rule for every field, and more review

Merging is common for people and organisations, where each system knows something different. Lantern’s shop holds a verified email address, while the booking list for its catering and workshops holds a phone number and a more recent postal address. A merged record (data teams often call it a golden record) takes each field from the source that is best for it and keeps a note of where each value came from.

How duplicates distort the numbers

A sum is inflated by exactly the values in the surplus rows, and a count by the number of surplus rows. Averages are subtler. Suppose there are $n$ genuine orders with mean $\bar{x}$ , plus $k$ surplus rows whose mean is $\bar{d}$ . The export reports

\bar{x}_{\text{reported}} = \frac{n\,\bar{x} + k\,\bar{d}}{n + k}

which equals the true mean only when the duplicated rows are typical, $\bar{d} = \bar{x}$ . Duplicates are rarely a random sample. If a checkout page tends to time out on large baskets, the orders that get submitted twice are the large ones, and the average order value is pushed up as well as the total. Near duplicates distort different numbers: one customer under two email addresses counts as two customers, which inflates the customer count and the number of “new” customers, and deflates orders per customer.

Near duplicates and fuzzy matching

Many near duplicates disappear once values are standardised: trim spaces, put text in one case, remove punctuation, compare email addresses in lower case. The next two lessons cover these steps. After standardising, compare the values exactly again. What remains are differences of spelling, which need fuzzy matching: comparing values by how similar they are rather than whether they are equal, to find pairs that are probably the same.

The most common similarity measure for short text is edit distance, also called Levenshtein distance after Vladimir Levenshtein, whose 1965 paper “Binary codes capable of correcting deletions, insertions, and reversals” introduced it. It is the smallest number of single-character insertions, deletions and substitutions that turn one string into the other. “Nrth” is one insertion away from “North”. “Okafor” and “Okafro” are two edits apart, because swapping two neighbouring letters takes two single-character edits; some variants of the measure count a swap of adjacent letters as one edit.

Edit distance is computed with a table. Write one string down the side and the other across the top, each preceded by an empty position. The cell in row $i$ and column $j$ holds the distance between the first $i$ letters of the first string and the first $j$ letters of the second. The first row and column count up from zero, because turning $j$ letters into nothing takes $j$ deletions. Every other cell is the smallest of three moves: the cell above plus one (a deletion), the cell to the left plus one (an insertion), or the cell diagonally above-left plus nothing if the two letters match and one if they don’t (a substitution). The answer is in the bottom-right cell. Filling the table this way is usually credited to Wagner and Fischer (1974); the challenging example below works through one.

Raw distances favour long strings: one edit in a three-letter name is a big change, one edit in a twenty-letter product name a small one. A common fix is to turn the distance into a similarity between 0 and 1 by dividing by the length of the longer string (formula below). Even then, a cut-off on a single field makes errors in both directions. Set it loose and “Jon Lee” merges with “Jan Lee”, two different people; set it tight and “Jhon Reyes” stays separate from “John Reyes”. Practical matching therefore compares several fields at once (name, email, postcode, phone), and sends the uncertain pairs to a person.

Comparing every record with every other is also expensive: a list of 10,000 customers contains $10{,}000 \times 9{,}999 \div 2 = 49{,}995{,}000$ pairs. Matching software therefore compares only pairs that share something cheap to check, such as the same postcode or the same first letter of the surname. This is called blocking, and its cost is that a pair split across two blocks is never compared.

These ideas come from record linkage, the statistical field that studies matching records within and across files. Fellegi and Sunter (1969) set out its classic framework: compare each pair of records field by field, use the pattern of agreements to judge how likely the pair is to be a match, and classify it as a link, a non-link or a possible link for clerical review, with the cut-offs chosen to control both kinds of error.

Finding duplicates in a spreadsheet, SQL and pandas

Spreadsheet. Sort by the business key, then add a helper column such as =COUNTIF(A:A, A2), which counts how many rows share this row’s key, and filter for values above 1 to review every repeated key. Excel’s Remove Duplicates command (Data tab, Data Tools group) deletes the duplicates permanently, and Microsoft notes that it compares the value displayed in each cell, not the value stored: the same date shown in two formats counts as two different values. Copy the data to another sheet first.

SQL. SELECT DISTINCT * FROM orders returns each distinct row once, which removes exact copies from the result. To list repeated keys, group by the key and keep the groups with more than one row: SELECT order_id, COUNT(*) FROM orders GROUP BY order_id HAVING COUNT(*) > 1. In PostgreSQL, SELECT DISTINCT ON (booking_id) * FROM bookings ORDER BY booking_id, received_at DESC keeps the latest version of each booking.

pandas. df.duplicated() marks each row that repeats an earlier one; df.duplicated(subset=['order_id'], keep=False) marks every row of a repeated key, including the first, which is what you want for review. df.sort_values('received_at').drop_duplicates(subset=['booking_id'], keep='last') keeps the latest version of each booking. The keep argument accepts 'first', 'last' or False (drop or mark them all).

Formulas

Surplus (duplicate) rows

\text{Surplus rows} = n - n_{\text{distinct}}

$n$: Number of rows in the tablerows
$n_{\text{distinct}}$: Number of distinct values of the business key (or of whole rows, when counting exact copies)rows

Assumes. One row per distinct key should survive.

Count on whole rows and on the key: the difference is the number of conflicting versions.

Duplicate rate

\text{Duplicate rate} = \dfrac{n - n_{\text{distinct}}}{n}

$n,\ n_{\text{distinct}}$: As aboverows

A convention, not a standard: some teams divide by the number of distinct keys instead. Say which you use.

Mean reported when duplicates are left in

\bar{x}_{\text{reported}} = \dfrac{n\,\bar{x} + k\,\bar{d}}{n + k}

$n$: Number of genuine recordsrecords
$\bar{x}$: True mean of the genuine recordsunits of the variable
$k$: Number of surplus duplicate rowsrows
$\bar{d}$: Mean of the variable in the surplus rowsunits of the variable

Unbiased only when the duplicated records are typical; totals and counts are inflated either way.

Edit (Levenshtein) distance, filled in as a table

d_{i,j} = \min\begin{cases} d_{i-1,j} + 1 \\ d_{i,j-1} + 1 \\ d_{i-1,j-1} + c_{ij} \end{cases} \qquad d_{i,0} = i,\quad d_{0,j} = j

$d_{i,j}$: Distance between the first i characters of string a and the first j characters of string bedits
$c_{ij}$: Substitution cost: 0 if the i-th character of a equals the j-th character of b, otherwise 1edits
$d_{i,0},\ d_{0,j}$: Turning i (or j) characters into an empty string takes one edit per characteredits
$i,\ j$: Number of characters taken from the start of a and of bcharacters

Assumes. Each insertion, deletion and substitution costs one edit.

The distance between the whole strings is the bottom-right cell.

Normalised edit similarity

\text{Similarity} = 1 - \dfrac{d(a,b)}{\max(|a|,\,|b|)}

$d(a,b)$: Edit distance between strings a and bedits
$|a|,\ |b|$: Lengths of the two stringscharacters

One common normalisation among several. 1 means identical; a similarity cut-off still needs checking on real pairs.

Worked examples

A first example: Counting the copies in an order export

Fictional figures

These eight rows come from Lantern’s order export for early March. Which rows are duplicates, what is the true revenue, and how much did the duplicates overstate revenue and the average order value?

Eight rows from Lantern’s order export (fictional)
Row	order_id	order_date	email	product	order_total
1	LC-20301	2026-03-02	maya.okafor@example.com	House Blend 250g	$14.00
2	LC-20302	2026-03-02	tom.reyes@example.com	Cold Brew Pack	$22.00
3	LC-20303	2026-03-03	j.chen@example.net	Espresso Roast 1kg	$49.00
4	LC-20302	2026-03-02	tom.reyes@example.com	Cold Brew Pack	$22.00
5	LC-20304	2026-03-03	ines.alves@example.org	Decaf Colombia 250g	$16.50
6	LC-20305	2026-03-04	maya.okafor@example.com	House Blend 250g	$14.00
7	LC-20303	2026-03-03	j.chen@example.net	Espresso Roast 1kg	$49.00
8	LC-20306	2026-03-04	sam.patel@example.com	Colombia Huila 250g	$17.00

Choose the business key. The shop issues one order_id per order, so one row per order_id should survive. There are $8$ rows and $6$ distinct order ids (LC-20301 to LC-20306), so $8 - 6 = 2$ rows are surplus.
Compare whole rows within each repeated key. Row 4 matches row 2 in every column, and row 7 matches row 3. Both are exact copies, so it doesn’t matter which of each pair you keep; keep the first occurrence.
Check the near misses. Rows 1 and 6 have the same customer and product, but different order ids and dates two days apart: Maya simply ordered again. They are a genuine repeat and both stay.
Recompute. The export’s total is $14 + 22 + 49 + 22 + 16.50 + 14 + 49 + 17 = 203.50$ . The surplus rows contribute $22 + 49 = 71$ , so true revenue is $203.50 - 71 = 132.50$ .
Averages. Reported average order value: $203.50 \div 8 = 25.4375 \approx 25.44$ . True average: $132.50 \div 6 \approx 22.08$ .

Answer. Rows 4 and 7 are exact duplicates. True revenue is $132.50; the copies overstated it by $71.00 (54% of the true figure) and pushed the average order value from $22.08 to $25.44.

What it means. The total was overstated by exactly the value of the copies. The average rose too, because the two copies averaged $(22 + 49) \div 2 = 35.50$ dollars, well above the true average of $22.08; had the copies been typical orders, the average would have survived and only the total and the count been wrong. In a full month’s export a few copies among thousands of rows are a much smaller percentage, but the dollar error is still exactly the value of the copies.

A harder example: Is “Jhon Reyes” the same customer as “John Reyes”?

Fictional figures

Lantern’s customer list contains “John Reyes” (john.reyes@example.com, postcode HC3 9PL, phone ending 4417) and “Jhon Reyes” (jreyes@example.net, postcode HC3 9PL, phone ending 4417). It also contains “Jon Lee” and “Jan Lee” at different postcodes. Compute the edit distance between “Jhon” and “John”, turn it into a similarity, compare it with “Jon” and “Jan”, and decide which pairs should be merged.

Edit distance table for “Jhon” (down the side) and “John” (across the top); ∅ is the empty start
	∅	J	o	h	n
∅	0	1	2	3	4
J	1	0	1	2	3
h	2	1	1	1	2
o	3	2	1	2	2
n	4	3	2	2	2

Set up the table: “Jhon” down the side, “John” across the top, and the first row and column counting up from 0, because turning the first $j$ letters into nothing takes $j$ edits.
Fill row J. Against J, the letters match, so the cell is the diagonal 0 plus 0, giving 0. Moving right, each extra letter of “John” costs one insertion: 1, 2, 3.
Fill row h. Against J, the cell above (0) plus one deletion gives 1. Against “o”, the smallest move is the diagonal 0 plus 1 for a substitution, giving 1. Against “h”, the letters match, so take the diagonal (row J, column o) of 1 plus 0, giving 1. Against “n”, the smallest is the cell to the left plus one, giving 2.
Fill rows o and n the same way. Against “o” in row o, the letters match and the diagonal is 1, so the cell is 1. The bottom-right cell is 2: “Jhon” and “John” are two edits apart, the cost of swapping two neighbouring letters.
Normalise: similarity $= 1 - 2 \div 4 = 0.50$ . For “Jon” and “Jan”, one substitution gives a distance of 1 and a similarity of $1 - 1 \div 3 \approx 0.67$ .
Judge with all the evidence. On names alone the different people (Jon and Jan Lee) look more alike than the same person (John Reyes). The other fields reverse the picture: the Reyes records share a postcode and a phone number, the Lee records share neither.

Answer. The edit distance between “Jhon” and “John” is 2 (similarity 0.50); between “Jon” and “Jan” it is 1 (similarity 0.67). Merge the two Reyes records; keep Jon Lee and Jan Lee separate.

What it means. A similarity score on one short field is weak evidence in either direction. Matching works when several fields agree, and when uncertain pairs are reviewed by someone who can check the source, for example by looking at past deliveries to that address.

Applied: a business decision: Keeping the right version of a hotel booking

Fictional figures

Harborview Hotel’s channel manager sends the whole booking to the hotel’s system every time a booking is made or changed. A revenue manager preparing next month’s forecast exports the rows below. What booked revenue and how many room-nights (one room occupied for one night) should the forecast use?

Seven rows from Harborview’s booking feed (fictional)
booking_id	received_at	check_in	nights	status	total
HB-26301	2026-10-01 09:12	2026-11-12	3	Confirmed	$540
HB-26302	2026-10-01 10:40	2026-11-12	2	Confirmed	$380
HB-26301	2026-10-03 16:05	2026-11-12	2	Confirmed	$360
HB-26303	2026-10-04 11:30	2026-11-13	1	Confirmed	$190
HB-26302	2026-10-05 08:15	2026-11-12	2	Cancelled	$0
HB-26304	2026-10-05 14:50	2026-11-12	4	Confirmed	$720
HB-26303	2026-10-04 11:30	2026-11-13	1	Confirmed	$190

Profile the key. There are $7$ rows but only $4$ distinct booking ids, so $3$ rows are surplus. The two HB-26303 rows are identical: an exact copy. HB-26301 and HB-26302 each have two versions that disagree.
Choose the survivorship rule from the mechanism. The channel manager sends a new message whenever a booking changes, so a later message supersedes an earlier one: keep the row with the latest received_at for each booking_id. HB-26301 was shortened from 3 nights to 2; HB-26302 was cancelled.
Survivors: HB-26301 (2 nights, $360), HB-26302 (cancelled), HB-26303 (1 night, $190), HB-26304 (4 nights, $720). Cancelled bookings contribute no revenue and no room-nights. (This feed sets a cancelled booking’s total to $0. The course’s harborview-bookings dataset does not: its total is the booked room charge, cancellations included, so there you exclude cancelled bookings by status rather than relying on a zero total.)
Totals with the right rule: revenue $360 + 0 + 190 + 720 = 1{,}270$ ; room-nights $2 + 1 + 4 = 7$ .
Compare the alternatives. Summing every row gives $540 + 380 + 360 + 190 + 0 + 720 + 190 = 2{,}380$ and $15$ room-nights. Keeping the first version of each booking gives $540 + 380 + 190 + 720 = 1{,}830$ and $3 + 2 + 1 + 4 = 10$ room-nights.

Answer. Use $1,270 of booked revenue and 7 room-nights. Summing all rows would overstate revenue by $1,110 and room-nights by 8; keeping the first version would still overstate them by $560 and 3.

What it means. “Keep first” looks like a safe default, but here it keeps a cancelled booking and a stay that was later shortened. The right rule came from asking how the duplicates were produced. A forecast built on the raw feed would have the hotel staffing and pricing for demand that no longer exists.

Explore

Common mistakes

“Identical rows are always duplicates.”
Only if the columns include something that should be unique. A till export without transaction numbers produces identical rows every time the same item sells twice on a day, and deleting them deletes real sales. Before removing identical rows, ask whether two different real events could have produced them.
“If the rows aren’t identical, they aren’t duplicates.”
Two versions of one order, or one customer under two spellings, are duplicates however different they look. Removing exact copies is only the first step: also count rows per business key, and look for near duplicates after standardising text.
“Same customer, same product, same day means a double order.”
People reorder, buy gifts and split purchases. Look for evidence outside the compared columns: how far apart the orders were placed, whether there were two payments, whether two parcels shipped, and whether one order was voided.
“Keep the first row” is a safe default.
The first row in a file is first only in the order the file happens to have. When later rows are corrections or cancellations, keeping the first keeps the outdated version, as the Harborview example showed. Choose the rule from the mechanism, and sort by a meaningful timestamp before keeping first or last.
“A small edit distance means the same person.”
Short strings make distances misleading: “Jon” and “Jan” are one edit apart and are different people, while a transposed “Jhon” is two edits from “John”. Use several fields together, and review uncertain pairs.
“Remove the duplicates from the raw file and move on.”
Deduplication involves judgement, and judgements get revisited. Keep the raw data unchanged, write the cleaned table separately, and record which rows each rule removed so that someone else can rerun or challenge the step.

Guided practice

Work through these with hints when you need them. Every hint and every worked solution is there to be used.

Loading four questions…

On your own

No hints this time. Answer, check, and read the feedback; the solution appears once you have answered or if you ask for it. Your first answers count toward mastery.

Loading six questions…

In practice

Reconciling revenue. At month end Lantern’s bookkeeper compares the total of the order export with the payouts from the payment provider. A gap between the two is a prompt to profile order_id before anything else. She removes exact copies, resolves conflicting versions by keeping the latest update, and only then investigates what remains: refunds, fees and timing differences. If she skipped the deduplication, she would spend the afternoon chasing a “missing” payment that was never owed.

Counting customers. Ana wants to know how many new customers the shop gained in March and how many of them ordered again. Near duplicates bias both answers in the same direction: a returning customer who checks out with a new email address is counted as new, and the repeat-purchase rate falls because her history is split across two records. Matching customers on standardised email, name and postcode before counting changes the story Ana tells her bank about customer loyalty.

Running a hotel. Harborview’s revenue manager uses booked room-nights to set prices and staffing for the weeks ahead. Duplicate bookings from the channel-manager sync make the hotel look fuller than it is, which pushes prices up and hides the need for promotion. Her team writes the survivorship rule into the data pipeline (latest message per booking reference wins, cancellations included) and checks every week that booking ids are unique after the rule runs.

Student records. At Northfield University a student who changes programme can appear twice in a term’s enrolment extract, once under each programme. Whether that is a duplicate depends on the question. For a headcount of students it is: the unit is a student, so the business key is the student number. For a count of programme enrolments it may not be, because the unit is a student in a programme, a composite key. The same rows can be duplicates for one analysis and legitimate for another, which is why the key must be chosen before any rows are removed.

Across all four settings the professional habit is the same: say what one record represents, profile the key, decide the survivorship rule from how the duplicates arose, and measure the effect on the totals that matter. A deduplication that changes revenue by 0.1% needs a sentence in the report; one that changes it by 10% needs an explanation to whoever relied on the old figure.

Knowledge check

Six questions, one attempt each, no hints. Score 80% or more to complete the lesson; you can take it again at any time.

Loading six questions…

Summary

A duplicate record describes the same real-world thing as another row. Exact copies, repeated keys with conflicting values and near duplicates need different treatment.
The business key defines “the same”. Identical rows are not duplicates when nothing in them should be unique, and different-looking rows can be versions of one record.
Separate double submissions from genuine repeats with outside evidence: timing, payments, deliveries and status.
Choose the survivorship rule (first, latest update, most complete or merge) from how the duplicates arose, and sort by a meaningful timestamp before keeping first or last.
Duplicates inflate sums by the value of the surplus rows and counts by their number; averages are biased when the duplicated records are not typical.
Edit distance counts the single-character insertions, deletions and substitutions between two strings; fuzzy matching should combine several fields and send uncertain pairs for review.
Profile keys with COUNTIF, GROUP BY … HAVING COUNT(*) > 1 or duplicated(keep=False); keep the raw data and record what you removed.

Key formulas

Surplus (duplicate) rows
Duplicate rate
Mean reported when duplicates are left in
Edit (Levenshtein) distance, filled in as a table
Normalised edit similarity

Terms introduced

Duplicate record: A row that describes the same real-world entity or event as another row in the same dataset.
Exact duplicate: A row whose values are identical to another row’s in every column.
Near duplicate: A record that describes the same entity as another but whose values differ slightly, for example in case, spacing, spelling or a second email address.
Business key: The column or columns that identify one real-world thing in the business’s own terms, such as an order number or booking reference.
Deduplication: Finding records that describe the same thing and reducing each group to a single record, or merging them, according to stated rules.
Survivorship rule: The rule that decides which record in a group of duplicates is kept, or how their fields are combined: for example first, latest update, most complete or merge.
Fuzzy matching: Comparing values by how similar they are rather than whether they are exactly equal, to find records that probably refer to the same thing.
Edit distance: The smallest number of single-character insertions, deletions and substitutions needed to turn one string into another; also called Levenshtein distance.

Sources

Levenshtein, V. I. (1966). Binary codes capable of correcting deletions, insertions, and reversals. Soviet Physics Doklady, 10(8), 707–710. Russian original: Doklady Akademii Nauk SSSR, 163(4), 845–848, 1965. mathnet.ru/eng/dan31411
Wagner, R. A. & Fischer, M. J. (1974). The string-to-string correction problem. Journal of the ACM, 21(1), 168–173. projects.csail.mit.edu/jacm/References/wagnerf1974:168.html
Fellegi, I. P. & Sunter, A. B. (1969). A theory for record linkage. Journal of the American Statistical Association, 64(328), 1183–1210.
pandas documentation. pandas.DataFrame.duplicated and pandas.DataFrame.drop_duplicates (the subset and keep arguments). pandas.pydata.org/docs/reference/api/pandas.DataFrame.drop_d…
PostgreSQL Global Development Group. PostgreSQL Documentation: SELECT (the DISTINCT and HAVING clauses). postgresql.org/docs/current/sql-select.html
Microsoft Support. Filter for or remove duplicate values (Excel). support.microsoft.com/en-us/office/filter-for-or-remove-dupl…

Before you start

You will be able to

Review first

The idea

Three kinds of duplicate

Where duplicates come from

What counts as “the same”?

In depth

Exact duplicates, duplicate keys and how to count them

Choosing the survivor

How duplicates distort the numbers

Near duplicates and fuzzy matching

Finding duplicates in a spreadsheet, SQL and pandas

Formulas

Surplus (duplicate) rows

Duplicate rate

Mean reported when duplicates are left in

Edit (Levenshtein) distance, filled in as a table

Normalised edit similarity

Worked examples

Explore

Common mistakes

“Identical rows are always duplicates.”

“If the rows aren’t identical, they aren’t duplicates.”

“Same customer, same product, same day means a double order.”

“Keep the first row” is a safe default.

“A small edit distance means the same person.”

“Remove the duplicates from the raw file and move on.”

Guided practice

On your own

In practice

Knowledge check

Summary

Key formulas

Terms introduced

Sources