Sorting and Top Lists
sort_values puts a table's rows in order by one column or several, with ascending= saying which way for each; nlargest and nsmallest give the top or bottom rows by a column in one call; and reset_index(drop=True) numbers a top list 0, 1, 2 instead of keeping the old row labels.
Apprentice · Python · Updated
After this lesson you can
- Sort a DataFrame with sort_values by one or two columns, choosing ascending or descending for each
- Make a top list with nlargest, nsmallest or a sort and head, and renumber it with reset_index(drop=True)
Key takeaways
- sort_values("price") puts the rows in order from smallest to largest, and ascending=False puts the largest first.
- With two columns, the second breaks ties in the first, and ascending=[False, True] gives each column its own direction.
- nlargest(3, "reviews") and nsmallest(3, "price") give the top or bottom rows by one column in one call.
- A fair top list filters first, such as products with at least 20 reviews, then sorts.
- reset_index(drop=True) numbers the rows 0, 1, 2 and drops the old labels instead of keeping them as a column.
"Our five best-rated products." "The three cheapest stoves." "Which products get the most reviews?" Top lists are everywhere: a store's home page, a manager's Monday report, a class project that ranks the cities with the cleanest air. Every one of them is a table put in order, then cut off after the first few rows, and the order you choose decides who ends up on top.
The example is Trailhead Outfitters' catalog, products.csv: 300 outdoor gear products with their sku, product name, category, price, stock, rating (1.0 to 5.0, the average of the customers' stars), reviews (how many customers rated it) and more. The catalog is our own made-up data.
Sorting by one column: sort_values
sort_values puts the rows in order by the column you name, smallest first:
import pandas as pd
products = pd.read_csv("products.csv")
columns = ["sku", "product", "price"]
products.sort_values("price")[columns].head(3)
sku product price
261 TO-1262 Cascade Glide Stove 23.99
216 TO-1217 Alpine Pro Stove 25.00
142 TO-1143 Aspen Ultra Stove 25.99
VS Code draws the table as a grid; this lesson shows it as pandas prints it as text. Each row keeps its index label (261, 216, 142), because a whole row moves together. For largest first, add ascending=False:
products.sort_values("price", ascending=False)[columns].head(3)
sku product price
19 TO-1020 Canyon Base Tent 485.00
235 TO-1236 Willow Ultra Tent 482.99
276 TO-1277 Basalt Lite Tent 481.99
Like most pandas methods, sort_values hands back a new table. On its own line, products.sort_values("price") shows the sorted rows and leaves products in its old order; to keep the order, store it: by_price = products.sort_values("price").
Two columns, two directions
Sort by rating, best first, and look at the top:
products.sort_values("rating", ascending=False)[["product", "rating", "reviews"]].head(4)
product rating reviews
266 Cascade Lite Boots 5.0 4
224 Alpine Pro Jacket 5.0 4
80 River Lite Backpack 5.0 4
233 Alpine Glide Sleeping Bag 5.0 2
Lots of products have 5.0, so which comes first among them is anyone's guess. A second column breaks the ties. Give sort_values a list of columns and a list of directions, one for each: here rating from high to low, then price from low to high, so the cheapest of the 5.0s comes first:
products.sort_values(["rating", "price"], ascending=[False, True])[["product", "rating", "price"]].head(4)
product rating price
14 Willow Trek Sleeping Bag 5.0 52.99
224 Alpine Pro Jacket 5.0 110.00
43 Aspen Pro Boots 5.0 124.99
80 River Lite Backpack 5.0 151.99
The second column only matters where the first one ties. It's how a phone book sorts by last name, then by first name.
Top lists: nlargest, nsmallest and a fair top five
For "the top three by one column", nlargest and nsmallest do the sort and the cut in one call: the number of rows, then the column.
stoves = products[products["category"] == "stoves"]
stoves.nsmallest(3, "price")[["product", "price"]]
product price
261 Cascade Glide Stove 23.99
216 Alpine Pro Stove 25.00
142 Aspen Ultra Stove 25.99
nlargest works the other way round: products.nlargest(3, "reviews") gives the three most-reviewed products, led by the Basalt Ultra Jacket with 856 reviews.
Back to the 5.0s: those were rated by two or four customers. A 5.0 from 2 reviews says much less than a 4.9 from 275, so a fair "best rated" list keeps only the products with enough reviews first, then sorts by rating and breaks ties by the number of reviews:
trusted = products[products["reviews"] >= 20]
top5 = trusted.sort_values(["rating", "reviews"], ascending=[False, False]).head(5)
top5[["product", "rating", "reviews"]]
product rating reviews
190 Ridge Glide Tent 5.0 68
50 Alpine Glide Boots 4.9 275
42 Basalt Pro Sleeping Bag 4.9 197
184 Canyon Ultra Backpack 4.9 172
82 River Trek Boots 4.9 108
Filter, then sort, then head: that order is the recipe for most top lists. Filtering Rows with a Condition explains the first step.
A tidy list: reset_index(drop=True)
The labels 190, 50, 42 are left over from the full catalog, and they read oddly in a top five. reset_index numbers the rows 0, 1, 2 again. Without drop=True, it keeps the old labels as a new column called index; with it, they're dropped:
top5 = top5.reset_index(drop=True)
top5[["product", "rating", "reviews"]]
product rating reviews
0 Ridge Glide Tent 5.0 68
1 Alpine Glide Boots 4.9 275
2 Basalt Pro Sleeping Bag 4.9 197
3 Canyon Ultra Backpack 4.9 172
4 River Trek Boots 4.9 108
Now top5.loc[0, "product"] is the winner, the Ridge Glide Tent.
Check yourself
2 questions to try on the lesson page.
So what
A top list is a decision about order: filter to what's fair to compare, sort by the column that answers the question with a second one to break ties, keep the first few, and number them for the people who'll read it.