Browse cheat sheets · Python

Technology: PythonCategory: Data CleaningDifficulty: IntermediateUpdated

Sort, Rank and Deduplicate Data

Sort, rank, and deduplicate pandas data safely by defining deterministic order, business keys, tie behavior, and the exact row that should survive.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Sort by one column
  5. 2. Sort by multiple columns
  6. 3. Make tie-breaking explicit
  7. 4. Preserve original order when sorting ties matters
  8. 5. Put nulls first or last
  9. 6. Sort with a transformation using key=
  10. 7. Find duplicate rows
  11. 8. Find duplicate business keys
  12. 9. Count duplicate-key frequency
  13. 10. Keep the first duplicate by current row order
  14. 11. Keep the last duplicate by current row order
  15. 12. Remove every duplicated key
  16. 13. Keep the latest row per business key
  17. 14. Keep the highest-priority row per key
  18. 15. Keep the row with the maximum value per group
  19. 16. Keep top-N rows per group
  20. 17. Rank globally
  21. 18. Rank within a group
  22. 19. Understand rank methods
  23. 20. Rank with deterministic tie-breaking
  24. 21. Reproduce SQL ROW_NUMBER() in pandas
  25. 22. Reproduce SQL RANK() behavior
  26. 23. Reproduce SQL DENSE_RANK() behavior
  27. 24. Keep all rows tied for first place
  28. 25. Deduplicate after a merge only if the relationship is understood
  29. 26. Compare row counts before and after deduplication
  30. 27. Preserve duplicate evidence before removing rows
  31. 28. Validate uniqueness after deduplication
  32. 29. Check whether deduplication changes an important total
  33. 30. Use nlargest() when you only need the top rows
  34. What the result means
  35. What to look for
  36. Different rows survive across runs or source files
  37. drop_duplicates() removes more rows than expected
  38. Duplicate count increases after a join
  39. Rank results contain gaps
  40. Too many rows have rank 1
  41. “Latest” row is wrong
  42. Production notes
  43. Deduplication is a business rule
  44. Source row order should not be trusted
  45. Tie-breaking must be deterministic
  46. Sorting large DataFrames can be expensive
  47. Deduplication can hide data-quality defects
  48. Nulls in keys require explicit semantics
  49. Ordered categories are useful for business priority
  50. Common mistakes
  51. 1. Calling drop_duplicates() without a subset
  52. 2. Keeping first without sorting
  53. 3. Using timestamp alone as a tie-breaker
  54. 4. Deduplicating after a bad join
  55. 5. Using rank() when unique row numbers are required
  56. 6. Assuming max() returns the full latest/highest row
  57. 7. Ignoring null sort behavior
  58. 8. Removing duplicates without measuring impact
  59. Next step
  60. Related deep dives

Problem

You need to order records, rank values within groups, or keep one row from a set of duplicates.

Typical examples:

  • keep the latest record per customer
  • rank products by revenue within category
  • keep the highest-priority event per business key
  • remove duplicate source rows
  • identify duplicate keys before loading a target
  • create deterministic “top 1 per group” logic

The risk is not the syntax.

The risk is silently keeping the wrong row because:

  • sort order is incomplete
  • ties are not handled deliberately
  • duplicate keys are misunderstood
  • row order is treated as meaningful when it is not
  • deduplication happens before the business grain is defined

Quick answer

For the latest row per customer:

latest = (
    df.sort_values(
        ["customer_id", "event_time"],
        ascending=[True, False],
    )
    .drop_duplicates(
        subset=["customer_id"],
        keep="first",
    )
)

For top-N ranking within a group:

df["revenue_rank"] = (
    df.groupby("category")["revenue"]
    .rank(
        method="dense",
        ascending=False,
    )
)

For duplicate investigation:

duplicates = df.loc[
    df.duplicated(
        subset=["customer_id", "event_time"],
        keep=False,
    )
]

The key rule is:

Define the business key and tie-break order before calling drop_duplicates().

Code / commands

1. Sort by one column

sorted_df = df.sort_values(
    "revenue",
    ascending=False,
)

Use this when one field fully determines the desired order.


2. Sort by multiple columns

sorted_df = df.sort_values(
    ["customer_id", "event_time"],
    ascending=[True, False],
)

This means:

  • customer ascending
  • newest event first within each customer

Use multiple columns when the business order depends on more than one attribute.


3. Make tie-breaking explicit

Suppose two events can have the same timestamp.

Add another deterministic key:

sorted_df = df.sort_values(
    [
        "customer_id",
        "event_time",
        "event_id",
    ],
    ascending=[
        True,
        False,
        False,
    ],
)

Then deduplicate:

latest = sorted_df.drop_duplicates(
    subset=["customer_id"],
    keep="first",
)

Without a tie-breaker, “latest” may still be ambiguous.


4. Preserve original order when sorting ties matters

pandas supports a stable sorting algorithm.

sorted_df = df.sort_values(
    "priority",
    kind="stable",
)

Use stable sorting when equal sort keys should retain their previous order.

For multi-column sorts, rely on the full sort key rather than using stability as a substitute for explicit tie-break rules.


5. Put nulls first or last

Default behavior places nulls last.

sorted_df = df.sort_values(
    "event_time",
    na_position="last",
)

To place them first:

sorted_df = df.sort_values(
    "event_time",
    na_position="first",
)

Choose based on the business rule.

Do not let default null positioning decide which duplicate row survives.


6. Sort with a transformation using key=

Case-insensitive sort:

sorted_df = df.sort_values(
    "customer_name",
    key=lambda s: s.str.casefold(),
)

This changes sort behavior without permanently mutating the original column.


7. Find duplicate rows

duplicate_rows = df.loc[
    df.duplicated(
        keep=False,
    )
]

This finds rows that are fully duplicated across all columns.

Use this only when full-row duplication is meaningful.


8. Find duplicate business keys

key_cols = [
    "customer_id",
    "order_id",
]

duplicates = df.loc[
    df.duplicated(
        subset=key_cols,
        keep=False,
    )
].sort_values(key_cols)

This is usually more useful than full-row duplicate detection.

The key should match the intended business grain.


9. Count duplicate-key frequency

duplicate_counts = (
    df.groupby(
        ["customer_id", "order_id"],
        dropna=False,
    )
    .size()
    .reset_index(name="row_count")
    .loc[lambda x: x["row_count"] > 1]
    .sort_values(
        "row_count",
        ascending=False,
    )
)

Use this to see whether duplication is isolated or systematic.


10. Keep the first duplicate by current row order

deduped = df.drop_duplicates(
    subset=["customer_id"],
    keep="first",
)

This is safe only if the current row order has already been made meaningful.

Do not use this on arbitrary ingestion order.


11. Keep the last duplicate by current row order

deduped = df.drop_duplicates(
    subset=["customer_id"],
    keep="last",
)

Again, this depends entirely on existing row order.

Usually sort first.


12. Remove every duplicated key

unique_only = df.loc[
    ~df.duplicated(
        subset=["customer_id"],
        keep=False,
    )
]

This keeps only keys that occur exactly once.

Use this for exception isolation or strict quality checks.

It is not the same as “keep one row per key.”


13. Keep the latest row per business key

latest = (
    df.sort_values(
        ["customer_id", "event_time"],
        ascending=[True, False],
    )
    .drop_duplicates(
        subset=["customer_id"],
        keep="first",
    )
)

This is one of the most common production deduplication patterns.

Add a deterministic tie-breaker if timestamps can repeat.


14. Keep the highest-priority row per key

Suppose priority is:

critical > high > medium > low

Define an ordered category:

from pandas.api.types import CategoricalDtype

priority_type = CategoricalDtype(
    categories=[
        "low",
        "medium",
        "high",
        "critical",
    ],
    ordered=True,
)

df["priority"] = (
    df["priority"]
    .astype(priority_type)
)

Then sort and deduplicate:

best = (
    df.sort_values(
        ["customer_id", "priority"],
        ascending=[True, False],
    )
    .drop_duplicates(
        subset=["customer_id"],
        keep="first",
    )
)

This is safer than alphabetical sorting.


15. Keep the row with the maximum value per group

idx = (
    df.groupby("customer_id")["revenue"]
    .idxmax()
)

top_rows = df.loc[idx]

This preserves the actual row containing the maximum value.

Use this when one numeric field decides the winning row.

Be careful when a group has only null values in the target column.


16. Keep top-N rows per group

Sort first:

top_3 = (
    df.sort_values(
        ["category", "revenue"],
        ascending=[True, False],
    )
    .groupby(
        "category",
        group_keys=False,
    )
    .head(3)
)

This returns up to three rows per category.

If ties must all be retained, ranking is usually better.


17. Rank globally

df["revenue_rank"] = (
    df["revenue"]
    .rank(
        method="dense",
        ascending=False,
    )
)

Higher revenue gets the smaller rank number.


18. Rank within a group

df["category_rank"] = (
    df.groupby("category")["revenue"]
    .rank(
        method="dense",
        ascending=False,
    )
)

Use this for:

  • product rank by category
  • merchant rank by region
  • customer rank by segment

19. Understand rank methods

Example values:

100
100
90
80

method="min":

1
1
3
4

method="dense":

1
1
2
3

method="first":

1
2
3
4

method="average":

1.5
1.5
3
4

Choose based on the business meaning of ties.


20. Rank with deterministic tie-breaking

If ties should not share a rank, sort explicitly first.

ranked = df.sort_values(
    [
        "category",
        "revenue",
        "product_id",
    ],
    ascending=[
        True,
        False,
        True,
    ],
).copy()

ranked["row_number"] = (
    ranked.groupby("category")
    .cumcount()
    .add(1)
)

This behaves more like SQL ROW_NUMBER() than rank().

Use it when every row needs a unique sequence number.


21. Reproduce SQL ROW_NUMBER() in pandas

ranked = df.sort_values(
    [
        "customer_id",
        "event_time",
        "event_id",
    ],
    ascending=[
        True,
        False,
        False,
    ],
).copy()

ranked["row_number"] = (
    ranked.groupby("customer_id")
    .cumcount()
    .add(1)
)

Then:

latest = ranked.loc[
    ranked["row_number"] == 1
]

This makes the winning-row logic explicit and debuggable.


22. Reproduce SQL RANK() behavior

df["rank"] = (
    df.groupby("category")["revenue"]
    .rank(
        method="min",
        ascending=False,
    )
)

Ties receive the same rank and create gaps afterward.


23. Reproduce SQL DENSE_RANK() behavior

df["dense_rank"] = (
    df.groupby("category")["revenue"]
    .rank(
        method="dense",
        ascending=False,
    )
)

Ties share a rank, but there are no rank gaps.


24. Keep all rows tied for first place

df["rank"] = (
    df.groupby("category")["revenue"]
    .rank(
        method="min",
        ascending=False,
    )
)

leaders = df.loc[
    df["rank"] == 1
]

Use this when ties are legitimate and all winners should be retained.


25. Deduplicate after a merge only if the relationship is understood

Bad pattern:

result = (
    left.merge(
        right,
        on="customer_id",
        how="left",
    )
    .drop_duplicates()
)

This can hide an incorrect one-to-many join.

Instead, validate the relationship before merging:

result = left.merge(
    right,
    on="customer_id",
    how="left",
    validate="many_to_one",
)

Fix the join problem rather than deduplicating the symptom.


26. Compare row counts before and after deduplication

before = len(df)

deduped = df.drop_duplicates(
    subset=["customer_id", "order_id"],
)

after = len(deduped)

print("Rows before:", before)
print("Rows after:", after)
print("Rows removed:", before - after)

Always know how many rows your rule removed.


27. Preserve duplicate evidence before removing rows

key_cols = [
    "customer_id",
    "order_id",
]

duplicates = df.loc[
    df.duplicated(
        subset=key_cols,
        keep=False,
    )
].copy()

Then deduplicate separately:

deduped = (
    df.sort_values(
        key_cols + ["event_time"],
        ascending=[True, True, False],
    )
    .drop_duplicates(
        subset=key_cols,
        keep="first",
    )
)

This keeps the evidence available for troubleshooting.


28. Validate uniqueness after deduplication

key_cols = [
    "customer_id",
    "order_id",
]

assert not deduped.duplicated(
    subset=key_cols
).any()

For production code, structured validation may be preferable to assert, especially if Python optimization settings or pipeline error handling matter.


29. Check whether deduplication changes an important total

before_total = df["amount"].sum()

deduped = df.drop_duplicates(
    subset=["order_id"],
    keep="first",
)

after_total = deduped["amount"].sum()

print("Before:", before_total)
print("After:", after_total)

A changed total may be expected.

The important part is understanding why.


30. Use nlargest() when you only need the top rows

top_10 = df.nlargest(
    10,
    "revenue",
)

For smallest:

bottom_10 = df.nsmallest(
    10,
    "revenue",
)

These are often clearer than sorting the entire DataFrame when only a small top/bottom subset is required.

What the result means

Operation Meaning
sort_values() Orders rows by one or more columns.
drop_duplicates() Removes repeated rows or repeated key combinations according to current order.
duplicated() Marks repeated rows/keys without removing them.
rank() Produces numerical rank values with configurable tie behavior.
cumcount() Produces a zero-based sequence within each group.
idxmax() Returns the index label of a maximum value per group/Series.
head(n) after grouped sort Keeps the first N sorted rows per group.
nlargest() Selects top values without requiring a full explicit sort pattern.

The key distinction is:

rank != row number

With ties:

  • rank() may assign the same rank to multiple rows
  • cumcount() after a deterministic sort gives each row a unique position

What to look for

Different rows survive across runs or source files

Your deduplication order is probably incomplete.

Add deterministic tie-break columns.

drop_duplicates() removes more rows than expected

Check whether the subset represents the real business key.

You may be deduplicating at too broad a grain.

Duplicate count increases after a join

The join may have introduced one-to-many or many-to-many expansion.

Do not fix this with drop_duplicates() until the relationship is understood.

Rank results contain gaps

That is expected with rank methods such as:

method="min"

Use dense if gapless ranks are required.

Too many rows have rank 1

There are ties.

Decide whether to:

  • keep all ties
  • use a tie-breaker
  • use row-number semantics

“Latest” row is wrong

Check:

  • timestamp dtype
  • timezone handling
  • null position
  • timestamp ties
  • sort direction

Production notes

Deduplication is a business rule

There is no universal definition of duplicate.

Possible grains include:

one row per order
one row per customer per day
one row per event ID
one row per merchant + site + date

Define the grain first.

Source row order should not be trusted

CSV order, API response order, database result order, or ingestion order may change.

If the surviving row matters, sort explicitly.

Tie-breaking must be deterministic

If two rows have the same primary timestamp or score, use another stable business field such as:

  • sequence number
  • event ID
  • version
  • ingestion timestamp
  • source priority

Avoid arbitrary tie resolution.

Sorting large DataFrames can be expensive

Full sorting is typically more expensive than simple filtering.

On large data:

  • filter first
  • select required columns
  • avoid repeated sorts
  • use nlargest() / nsmallest() where appropriate
  • push ranking/deduplication to SQL/Spark when data exceeds comfortable memory limits

Deduplication can hide data-quality defects

Do not treat deduplication as cleanup by default.

First determine whether duplicates are:

  • valid repeated events
  • source retries
  • replayed files
  • bad joins
  • source-system defects
  • intentionally versioned rows

Nulls in keys require explicit semantics

Two rows with null key components may be considered duplicates by pandas depending on the subset values.

Decide whether missing-key rows are even eligible for deduplication.

Ordered categories are useful for business priority

Alphabetical sorting rarely represents real priority.

Define category order explicitly when the business has ranked labels.

Common mistakes

1. Calling drop_duplicates() without a subset

This deduplicates full rows, which may not match the business key.

2. Keeping first without sorting

first means first in current row order, not earliest or latest by time.

3. Using timestamp alone as a tie-breaker

Timestamps can repeat.

Add another deterministic column.

4. Deduplicating after a bad join

This can hide the real cardinality problem and discard valid rows.

Fix the merge relationship first.

5. Using rank() when unique row numbers are required

Ties may share the same rank.

Use sorted groupby().cumcount() + 1 for row-number behavior.

6. Assuming max() returns the full latest/highest row

Aggregating each column independently can combine values from different rows.

Select the actual winning row.

7. Ignoring null sort behavior

Nulls can unexpectedly become the kept/dropped row if sort order is not deliberate.

8. Removing duplicates without measuring impact

Always report rows removed and validate important totals or counts.

Next step

Use the pattern to decide what comes next.

  • Need latest row per key → sort by key + timestamp + tie-breaker, then keep the first row.
  • Need top-N per group → sort and use grouped head(), or rank if ties matter.
  • Need SQL ROW_NUMBER() behavior → sort deterministically and use cumcount() + 1.
  • Need SQL RANK() / DENSE_RANK() behavior → use rank(method="min") or rank(method="dense").
  • Unexpected duplicates appeared after merge → inspect join cardinality instead of deduplicating.
  • Duplicate removal changes totals → reconcile the removed rows before continuing.
  • Tie behavior is ambiguous → define a deterministic business tie-breaker.
  • Sorting is too expensive in memory → reduce the dataset earlier or move the operation to a larger execution engine.

← All cheat sheets