Sort and rank data in pandas

You have a DataFrame of sales records, but the rows are in the order they were entered—chaotic and unhelpful. You need to find the top-performing products or see who got the highest test scores, but scanning the raw data is slow and error-prone. That's where sorting and ranking come in: they transform a jumble of rows into a clear, ordered view of your data, revealing patterns and insights at a glance.

The problem this lesson solves

Data rarely arrives in the order you need it. An e-commerce export lists transactions chronologically, but you want to see the most expensive order first. A gradebook spreadsheet is organized by student ID, but you need to know who scored in the top 10%. Without sorting, every analysis begins with tedious manual scanning. Without ranking, you can't easily compare values relative to each other—like 'this product is 3rd best' or 'this score is in the 95th percentile.' This lesson gives you the two pandas tools that make order and relative position a one-liner: sort_values() and rank(). By the end, you'll be able to reorder entire DataFrames and assign rankings with precision, avoiding the frustration of guesswork and manual loops.

Core concept / mental model

Think of sorting as rearranging rows in a DataFrame based on one or more columns. It's like alphabetizing a bookshelf: the books don't change, only their position on the shelf. In pandas, sort_values() returns a new DataFrame (or Series) with rows in ascending or descending order.

Ranking, on the other hand, assigns a number to each row based on its position in the sorted order. It's like giving medals in a race: the fastest runner gets a 1, the second-fastest a 2, and so on. Ranking doesn't reorder the data—it adds a new column that tells you where each row stands relative to the others.

Here's the key distinction:

• Sorting → changes row order
• Ranking → changes/adds values (the rank itself)

A quick analogy: a class roster sorted by name (sorting) vs. a roster that shows each student's class rank (ranking). Both use the same underlying value—say, test scores—but they serve different purposes.

How it works step by step

1. Load your data into a DataFrame — start with pandas and your dataset.
2. Identify the column(s) to sort or rank by — this is your 'key' column, like 'price' or 'score'.
3. Sort using sort_values() — pass the column name to by and choose ascending=True or False.
4. Handle multi-column sorting — sort by primary column, then secondary (like sorting by price, then by product name).
5. Rank using rank() — choose a method (average, min, max, dense, etc.) to control ties.
6. Assign the rank to a new column — so you keep the original data and add the ranking.

nuances of Sorting

When you call df.sort_values('price'), pandas returns a new DataFrame by default. If you want to change the original, you need inplace=True. Also, by default, missing values (NaN) are placed at the end, but you can control this with na_position.

nuances of Ranking

rank() offers several tie-breaking methods. The default method='average' gives tied values the average of their ranks. method='min' assigns the minimum rank in a tie, which is common in competitions. method='dense' ensures no gaps, so if two values tie for 1st, the next distinct value gets 2—useful for dense rankings like 'top 3'.

Hands-on walkthrough

Let's put this into practice with a small sales dataset. We'll sort and rank in a few different ways.

Example 1: Basic sorting

import pandas as pd

# Sample data: sales by product
df = pd.DataFrame({
    'product': ['Laptop', 'Mouse', 'Keyboard', 'Monitor', 'USB Cable'],
    'price': [1200, 25, 80, 300, 15],
    'units_sold': [150, 800, 500, 200, 1200]
})

# Sort by price ascending (cheapest first)
sorted_by_price = df.sort_values('price')
print(sorted_by_price)

Expected output:

      product  price  units_sold
4   USB Cable     15        1200
1       Mouse     25         800
2    Keyboard     80         500
3     Monitor    300         200
0      Laptop   1200         150

Example 2: Sorting by multiple columns

# Sort by units_sold descending, and if tied, by price ascending
sorted_multi = df.sort_values(['units_sold', 'price'], ascending=[False, True])
print(sorted_multi)

Expected output:

      product  price  units_sold
4   USB Cable     15        1200
1       Mouse     25         800
2    Keyboard     80         500
3     Monitor    300         200
0      Laptop   1200         150

Example 3: Ranking with different methods

# Add a rank column based on units_sold (descending: highest gets 1)
df['rank_sold'] = df['units_sold'].rank(ascending=False, method='min')
print(df)

Expected output:

      product  price  units_sold  rank_sold
0      Laptop   1200         150          5
1       Mouse     25         800          2
2    Keyboard     80         500          3
3     Monitor    300         200          4
4   USB Cable     15        1200          1

Example 4: Ranking with dense method

# Use dense method to avoid gaps in ranks
df['dense_rank'] = df['units_sold'].rank(ascending=False, method='dense')
print(df)

Expected output (if any ties, they'd share rank; here no ties, so same as min):

      product  price  units_sold  rank_sold  dense_rank
0      Laptop   1200         150          5           5
1       Mouse     25         800          2           2
2    Keyboard     80         500          3           3
3     Monitor    300         200          4           4
4   USB Cable     15        1200          1           1

Pro tip: Use ascending=False with rank() when higher values should get lower rank numbers (like top sales). If you're ranking test scores where higher is better, this is the default mental model.

Compare options / when to choose what

Here's a quick comparison of sorting vs ranking and common methods:

When to use sorting vs ranking:

• Use sorting to physically reorder rows for display or further processing (like top 5 rows).
• Use ranking to add a new column that shows relative position, especially when you want to keep original order and just annotate.

Alternatives:

• For top-N rows, you can combine sort_values() and head().
• For quantile-based ranks (percentiles), use rank(pct=True).

Troubleshooting & edge cases

• Problem: You get a KeyError when sorting by a column name that doesn't exist.

• Fix: Double-check column names with df.columns. Ensure the column is spelled exactly, including case.

• Problem: NaNs appear at the top when sorting ascending, and you want them at the end.

• Fix: Use na_position='last' for ascending sort, or na_position='first' for descending.

• Problem: rank() produces .5 values when ties occur.

• Fix: Use method='min' to assign whole numbers, or method='dense' for gap-free integer ranks.

• Problem: Sorting doesn't change the original DataFrame.

• Fix: Remember sort_values() returns a new object. Use inplace=True or assign the result back to a variable.

• Problem: Ties in ranking with method='average' give non-integer ranks.

• Fix: If you need integer ranks, choose method='min' or method='dense'.

Blockquote: Always inspect your data for missing values before ranking. rank() handles NaNs by giving them NaN ranks, which may not be what you want—you might want to fill or drop them first.

What you learned & what's next

You've mastered two essential pandas operations: sorting and ranking. You can now reorder rows with sort_values() — either by one column or multiple — and assign relative positions with rank(), choosing from different tie-handling methods. You practiced with a real sales dataset and saw how to produce top-N lists and dense rankings. These skills are crucial for any data exploration task, from finding the highest revenue product to understanding score distributions.

You also learned to handle edge cases like missing values, column name typos, and tie-breaking. This foundation will help you in the next lesson, where you'll dive into grouping and aggregating data — a natural next step after you've ordered and ranked your data, because you'll often want to rank within groups (like top product per category).

Now that you can sort and rank, you're ready to explore more advanced data transformations. Keep practicing!