Browse cheat sheets · Python
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
- Problem
- Quick answer
- Code / commands
- 1. Sort by one column
- 2. Sort by multiple columns
- 3. Make tie-breaking explicit
- 4. Preserve original order when sorting ties matters
- 5. Put nulls first or last
- 6. Sort with a transformation using key=
- 7. Find duplicate rows
- 8. Find duplicate business keys
- 9. Count duplicate-key frequency
- 10. Keep the first duplicate by current row order
- 11. Keep the last duplicate by current row order
- 12. Remove every duplicated key
- 13. Keep the latest row per business key
- 14. Keep the highest-priority row per key
- 15. Keep the row with the maximum value per group
- 16. Keep top-N rows per group
- 17. Rank globally
- 18. Rank within a group
- 19. Understand rank methods
- 20. Rank with deterministic tie-breaking
- 21. Reproduce SQL ROW_NUMBER() in pandas
- 22. Reproduce SQL RANK() behavior
- 23. Reproduce SQL DENSE_RANK() behavior
- 24. Keep all rows tied for first place
- 25. Deduplicate after a merge only if the relationship is understood
- 26. Compare row counts before and after deduplication
- 27. Preserve duplicate evidence before removing rows
- 28. Validate uniqueness after deduplication
- 29. Check whether deduplication changes an important total
- 30. Use nlargest() when you only need the top rows
- What the result means
- What to look for
- Different rows survive across runs or source files
- drop_duplicates() removes more rows than expected
- Duplicate count increases after a join
- Rank results contain gaps
- Too many rows have rank 1
- “Latest” row is wrong
- Production notes
- Deduplication is a business rule
- Source row order should not be trusted
- Tie-breaking must be deterministic
- Sorting large DataFrames can be expensive
- Deduplication can hide data-quality defects
- Nulls in keys require explicit semantics
- Ordered categories are useful for business priority
- Common mistakes
- 1. Calling drop_duplicates() without a subset
- 2. Keeping first without sorting
- 3. Using timestamp alone as a tie-breaker
- 4. Deduplicating after a bad join
- 5. Using rank() when unique row numbers are required
- 6. Assuming max() returns the full latest/highest row
- 7. Ignoring null sort behavior
- 8. Removing duplicates without measuring impact
- Next step
- 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 rowscumcount()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.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.