skip to content

Duplicates and Counting

duplicated marks repeats, drop_duplicates keeps the first, last or none on a subset, and value_counts and nunique count values. Interviewers ask which duplicate survives.

on this pageshow

questions

6

In pandas, how do DataFrame.duplicated and drop_duplicates differ, and which copy of a repeated row counts as the original by default?

level: juniorimportance: must knowfreq 72%

answer

  1. one marks, one removes
  2. same subset and keep arguments
  3. first occurrence is the original
  4. row labels never compared
  5. missing values match each other

basics

~20 s

DataFrame.duplicated returns a boolean Series flagging repeated rows; drop_duplicates returns a new DataFrame without them. Both compare all columns unless subset is given, ignore the index, and by default keep='first' treats the first occurrence as the original.

solid answer

~40 s

`df.duplicated()` returns a boolean Series with one value per row, `True` where the row repeats an earlier one, so `df.duplicated().sum()` is how many rows a dedupe would remove. `df.drop_duplicates()` is effectively `df[~df.duplicated()]`: a new DataFrame that keeps the surviving rows with their original index labels. Both take `subset=` (one column label or a list; all columns by default) and `keep=`: `'first'` (the default) keeps the earliest copy, `'last'` the latest by row position, and `False` marks or drops every copy. Neither compares the index, and missing values count as equal, so two contacts with no email are duplicates on `subset='email'`. `ignore_index=True` renumbers the result, and `inplace=True` returns `None`.

code

python · 17 lines
python
import pandas as pd

crm = pd.DataFrame({
    "contact_id": [101, 102, 101, 103, 101],
    "email": ["[email protected]", "[email protected]", "[email protected]", None, None],
    "plan": ["free", "pro", "pro", "free", "trial"],
})

mask = crm.duplicated(subset="email")
print(mask.tolist())          # [False, False, True, False, True]
print(mask.sum())             # 2 rows would be removed

deduped = crm.drop_duplicates(subset="email")
print(deduped.index.tolist()) # [0, 1, 3] - original labels kept

print(crm.drop_duplicates(subset="email", ignore_index=True).index.tolist())  # [0, 1, 2]
print(crm.drop_duplicates(subset="email", keep=False).index.tolist())  # [1]

go deeper

for a junior

Recall that duplicated marks and drop_duplicates removes, that keep='first' is the default, and that subset picks the identity columns.

for a middle

Explain that drop_duplicates is the inverse of the duplicated mask, that the index is ignored, and that missing values match each other on subset.

for a senior

Show the habit of counting with duplicated before dropping, choosing subset to match the business key, and excluding missing keys before a dedupe.

for a principal

Argue for one documented identity key per dataset and a logged duplicate count in every pipeline, so silent row loss shows up in review.

## Two methods, one rule pandas gives a **DataFrame** (a table of labelled rows and columns) two methods for repeated rows, and they share one definition of "repeat": - `DataFrame.duplicated(subset=None, keep='first')` **marks** repeats. It returns a **boolean Series**, one value per row, aligned to the frame's index. - `DataFrame.drop_duplicates(subset=None, *, keep='first', inplace=False, ignore_index=False)` **removes** them. In the source it is implemented as `self[-self.duplicated(subset, keep=keep)]`, so it returns the rows the mask leaves unflagged. Because one is built on the other, any argument you learn for `duplicated` behaves the same in `drop_duplicates`. That makes `duplicated` the natural first step: look at what would be removed, then remove it. ## What counts as a duplicate Two rows are duplicates when their values are equal in every column of `subset`: - With no `subset`, **all columns** are compared, so only exact copies match. - With `subset='email'` or `subset=['first_name', 'last_name']`, only those columns are compared and the rest may differ. A label that is not a column raises `KeyError`. - The **index is never part of the comparison**. The docstring says it plainly: indexes, including time indexes, are ignored. Two rows labelled 10 and 11 with identical values are duplicates. - **Missing values compare equal to each other** here. Two rows whose email is missing are duplicates on `subset='email'`, which surprises anyone who expects NaN never to equal NaN. To find repeated row **labels** instead, use the index's own method: `df[~df.index.duplicated(keep='first')]`. `Index.duplicated` returns a NumPy boolean array rather than a Series. ## Which copy survives `keep` decides which member of each group of duplicates is treated as the original: | `keep=` | `duplicated` marks as `True` | `drop_duplicates` keeps | |---|---|---| | `'first'` (default) | every copy after the first | the first copy | | `'last'` | every copy before the last | the last copy | | `False` | every copy, the first included | no copy at all | "First" and "last" mean **row position in the frame as it stands**, not any date or value. If the frame is in file order, `keep='first'` keeps whatever the file listed first. To keep the latest record per key you sort before deduplicating; the sort's own options are covered with sorting. ## What comes back 1. `duplicated` returns a `bool` Series of the same length as the frame, so you can sum it, filter with it or invert it. 2. `drop_duplicates` returns a **new DataFrame**. The surviving rows keep their original labels, so the index has gaps (0, 1, 3, ...). 3. `ignore_index=True` relabels the result 0 to n-1, the same as following it with `reset_index(drop=True)`. 4. `inplace=True` changes the frame and returns `None`, so `df = df.drop_duplicates(inplace=True)` leaves `df` bound to `None`. Plain reassignment, `df = df.drop_duplicates()`, is the safer habit. ## The same pair on a Series and an Index The two methods exist on the other pandas containers too, with small differences worth knowing: - `Series.duplicated(keep='first')` and `Series.drop_duplicates(*, keep='first', inplace=False, ignore_index=False)` have no `subset`, because a Series has only one column of values. They return a boolean Series and a Series. - `Index.duplicated(keep='first')` returns a **NumPy boolean array**, not a Series, and `Index.drop_duplicates(*, keep='first')` returns a new Index. - `keep` means the same thing everywhere: `'first'`, `'last'` or `False`. So `crm['email'].drop_duplicates()` gives the distinct emails with the label of each one's first row, while `crm.drop_duplicates(subset='email')` gives whole rows. ## A short CRM example ```python import pandas as pd crm = pd.DataFrame({ "contact_id": [101, 102, 101, 103, 101], "email": ["[email protected]", "[email protected]", "[email protected]", None, None], "plan": ["free", "pro", "pro", "free", "trial"], }) crm.duplicated() # all columns: no row repeats exactly crm.duplicated(subset="email") # rows 2 and 4 repeat an earlier email crm.drop_duplicates(subset="email") # keeps rows 0, 1 and 3 ``` Row 4 is dropped on `subset='email'` because its missing email matches row 3's missing email, even though they are different contacts. ## Habits worth keeping - Check `duplicated(...).sum()` before dropping, and log it. - Say which columns define identity with `subset`; the all-columns default rarely matches the business key. - Exclude rows with a missing key before deduplicating on it, or they collapse into one. - Normalise near-duplicates such as letter case or stray spaces first; pandas compares values exactly.

  • How do you remove rows whose index label repeats, rather than rows whose values repeat?
    Use the index's own method: `df[~df.index.duplicated(keep='first')]`. `drop_duplicates` ignores the index entirely, so it cannot do this. `Index.duplicated` takes the same `keep` values and returns a NumPy boolean array, which works directly as a row mask.
  • Why might drop_duplicates leave two rows that look identical on screen?
    pandas compares values exactly. `'[email protected]'` and `'[email protected] '` differ in case and a trailing space, and two floats can differ in the last digit while printing the same. Build a normalised key column first, for example with `str.strip()` and `str.lower()`, and deduplicate on that.
  • What is the difference between duplicated().sum() and duplicated(keep=False).sum()?
    With the default `keep='first'` the sum is the number of rows a dedupe would remove. With `keep=False` every member of every duplicate group is flagged, so the sum is the number of rows involved in duplication, originals included.

  • drop_duplicates also compares the index labels, so rows with different labels never match
  • duplicated returns only the repeated rows instead of a boolean mask
  • Rows with a missing value in the subset column are never treated as duplicates
  • keep='first' keeps the oldest record by date
  • df = df.drop_duplicates(inplace=True) leaves df holding the deduplicated frame
open as a page

In pandas, how do you keep only each contact's most recent sign-up when a CRM export repeats contacts across sign-ups?

level: middleimportance: must knowfreq 66%

basics

~10 s

drop_duplicates keeps a copy by row position, not by date, so sort first: crm.sort_values('signup', ascending=False).drop_duplicates('contact_id') keeps each contact's latest dated row; ignore_index=True renumbers the result.

open as a page

In pandas, how do Series.value_counts(normalize=True) shares of churn reasons change when some reasons are missing and you pass dropna=False?

level: middleimportance: must knowfreq 64%

basics

~20 s

By default value_counts drops missing values from both the counts and the denominator, so the shares of known reasons sum to 1. dropna=False adds a NaN row and divides by every row, so each known share shrinks.

open as a page

In pandas, why can len(s.unique()) differ from s.nunique() on the same Series, and what does each one return?

level: juniorimportance: should knowfreq 55%

basics

~10 s

Series.unique returns an array of the distinct values in order of first appearance, missing value included; Series.nunique returns an int that skips missing values by default. With a NaN present, len(s.unique()) is one higher.

open as a page

In pandas 3.0, in what order does DataFrame.value_counts list plan-and-reason combinations with sort=True, and with sort=False?

level: seniorimportance: should knowfreq 38%

basics

~10 s

In pandas 3.0, sort=True orders combinations by count with a stable sort, so ties keep first-appearance order; sort=False keeps the order each combination first appears. In 2.x, sort=False sorted by the column values instead.

open as a page

Before deleting repeated contacts in pandas, how does duplicated(keep=False) let you inspect every copy, and why is the default keep misleading for review?

level: seniorimportance: should knowfreq 44%

basics

~10 s

duplicated(keep=False) flags every row in a duplicate group, originals included, so df[mask].sort_values(key) shows each group side by side. The default keep='first' flags only later copies, leaving nothing to compare them with.

open as a page