pandas drop_duplicates: keep='first', and the Row It Chose For You

1. Two different questions

Before the explanation: eight rows, three repeated order numbers. Say how many of the rows are duplicates.

The question has two answers, and which one you want decides everything else.

df.duplicated().sum()                      1     whole rows repeated
df.duplicated(subset=["order_id"]).sum()   3     keys repeated

One row is an exact copy of another. Three rows share an order number with a row above them. The gap between one and three is the whole subject: two of those rows repeat a key while carrying different information, and they are not copies of anything.

"Duplicate" is not a property of the data, it is a property of the columns you compare. Comparing every column asks "is this row a copy". Comparing the key asks "should this thing appear only once". Those are different questions and they have different right answers, and drop_duplicates will answer whichever one your subset argument asked for without commenting on the choice.

2. duplicated, and the three keeps

duplicated() returns a boolean per row, and the keep argument decides which member of each group is not flagged.

df.duplicated()                 flags 1 row     keep='first', the default
df.duplicated(keep='last')      flags 1 row
df.duplicated(keep=False)       flags 2 rows

keep='first' flags everything except the first of each group, which is what you want when you are about to drop. keep=False flags every member of any group that has more than one, which is what you want when you are about to look, because it gives you all the rows involved rather than only the extras.

That second one is the more useful of the two while investigating:

df[df.duplicated(subset=["order_id"], keep=False)]

That is the query to run first, every time. It returns the six rows that share a key with something, side by side, so you can see what they disagree about before deciding anything.

3. drop_duplicates, and the rows it returns

Same table, four calls, and four different answers.

drop_duplicates()                                rows 7   units 46
drop_duplicates(subset=["order_id"])             rows 5   units 35
drop_duplicates(subset=["order_id"], keep="last") rows 5   units 37
drop_duplicates(subset=["order_id"], keep=False)  rows 2   units 16

From 8 rows and 56 units to anywhere between 7 and 2. The last one is the surprise for most people: keep=False here does not mean "keep none of the duplicates", it means drop every row involved in any group of more than one, so only the two order numbers that appear exactly once survive.

The middle two differ only in which member of each group lived, and that changed the units total from 35 to 37. Two units, on eight rows, from one keyword. On a real table it is not two units.

Say out loud what a 35 or a 37 would look like in a report. Both are plausible, neither carries any indication that a choice was made, and the person reading it cannot tell which keyword was typed.

4. The survivor is chosen by the sort

Since keep="first" means "whichever is higher up", the way to choose deliberately is to put the row you want on top before dropping.

df.sort_values(["order_id", "units"]).drop_duplicates(subset=["order_id"])
     1003 keeps 3        total units 35

df.sort_values(["order_id", "units"], ascending=[True, False]) \
  .drop_duplicates(subset=["order_id"])
     1003 keeps 5        total units 37

Identical data, identical drop, two different results, decided entirely by the sort in front of it. That is not a flaw; it is the only place where the choice can be expressed, which is why it should be written down rather than left to whatever order the file arrived in.

Three sorts that come up constantly:

Write the sort and the drop as one chained expression, so nobody can move one without the other, and put a comment on it saying which rule you chose.

5. Mark instead of delete

Two extra columns turn a destructive operation into a reversible one, and they answer both questions from section one at once.

df["seen"]     = df.groupby("order_id").cumcount() + 1
df["row_seen"] = df.groupby(list(df.columns)).cumcount() + 1

cumcount numbers the rows within each group, starting at zero, so adding one gives a human-readable "this is the nth time I have seen this". Run on the table:

row  order_id  region  units  seen  row_seen
  0      1001   North      4     1         1
  1      1002   South     10     1         1
  2      1002   South     10     2         2
  3      1003   East       3     1         1
  4      1003   East       5     2         1
  5      1004   North      6     1         1
  6      1004   north      6     2         1
  7      1005   West      12     1         1

rows where the key repeats:  3
rows where the row repeats:  1
the rows worth looking at:   1003 and 1004

Rows 4 and 6 are the point. Both have seen = 2 and row_seen = 1: the order number has been seen before, and this exact row has not. Those are the two rows where the data disagrees with itself, and a one-column de-duplication destroys both without comment.

Keep the marked frame and filter from it, rather than dropping and hoping. It costs two columns and it means every later question, how many, which ones, what did they disagree about, can be answered without reloading anything.

6. pandas is case sensitive, and the spreadsheet is not

Before the explanation: rows 5 and 6 are identical except that one region is North and the other is north. Say whether drop_duplicates() treats them as the same row.

"North" == "north"                False
df["region"].nunique()            5    East, North, South, West, north
df["region"].str.lower().nunique() 4

It does not. pandas compares strings exactly, so those are two different rows and both survive, which is why drop_duplicates() on all columns returned seven rows here.

The same eight rows in Excel come back as six, because its Remove Duplicates comparison ignores capitals. Same data, same intent, two tools, two answers, and neither of them tells you it made a choice about case.

That is worth knowing beyond the trivia, because analyses migrate between tools and the row counts stop matching for a reason nobody suspects. The defence is to normalise before comparing rather than to rely on either default:

key = df["region"].str.strip().str.casefold()

casefold rather than lower, because it handles more of the world's alphabets, and strip first, because whitespace is the other invisible difference. Do the same to any text column you are comparing on, and the two tools agree again.

7. The index afterwards

Dropping rows does not renumber the ones that are left.

df.drop_duplicates(subset=["order_id"]).index      [0, 1, 3, 5, 7]

The index has gaps where the dropped rows were, which is correct and occasionally awkward. It means .iloc[2] and .loc[2] now refer to different rows, and anything that assumes a contiguous index, including some plotting and concatenation, will behave oddly.

df.drop_duplicates(subset=["order_id"]).reset_index(drop=True).index   [0, 1, 2, 3, 4]

drop=True matters: without it the old index is kept as a new column called index, which is occasionally useful for tracing a row back to the original file and usually just clutter.

8. Counting instead of dropping

Often the honest deliverable is not a de-duplicated table, it is a count.

df["order_id"].value_counts().sort_index()

1001  1
1002  2
1003  2
1004  2
1005  1

Three order numbers appear twice. That is a fact about the data, and if the order number is supposed to be unique then it is a fact about a system somewhere that needs fixing, which is a more useful thing to report than a table you tidied on your own initiative.

Two related counts worth having beside it. df["order_id"].nunique() against len(df) tells you the size of the problem in one comparison, 5 against 8 here. And value_counts()[lambda s: s > 1] filters straight to the offenders.

Picture your own most-used extract. If its identifier column stopped being unique next month, is there anything in your code that would notice, or would the totals simply go up?

Edge cases worth knowing

Six that get through.

Whitespace. "North " and "North" are different, so duplicates hide behind a trailing space and drop_duplicates reports finding nothing. Strip before comparing.

Missing values in the key. pandas treats NaN as equal to NaN for this purpose, so every row with a missing key is a duplicate of every other one and all but one are dropped. On a partly populated key that can be most of the table.

Floats. 10.0 and 10.000000001 are different rows and display identically. Round explicitly before comparing on a numeric column.

Dates with a time. Two records on the same day are different values if one carries 09:14. Normalise with .dt.normalize() if the day is what you mean.

Column order in subset. It does not matter for correctness, and it does matter for reading: list the columns in the order somebody would say them, because that list is your definition of identity.

Duplicates created by a merge. If the row count went up before you got here, the duplicates are not in the data, they were manufactured by a join to a table whose key was not unique. De-duplicating afterwards hides the cause; the merge guide has the check that catches it at the source.