Introduction
Ordering and counting are how a table starts to answer questions: who scored highest, which product sells most, which city appears most often. pandas sorts whole rows, so every column moves together, and it can rank and count values directly. The details that matter are the ones this lesson spends time on: what happens to ties and to missing values.
How it works
sort_values orders the rows by a column, ascending by default:
df = pd.DataFrame({
"name": ["Ada", "Rahim", "Mei", "Omar", "Lena"],
"city": ["London", "Dhaka", "Dhaka", "London", "Dhaka"],
"score": [88, 92, 88, None, 75],
})
df.sort_values("score", ascending=False, kind="stable")["name"].tolist()
# ['Rahim', 'Ada', 'Mei', 'Lena', 'Omar']
df.sort_values(["city", "score"], ascending=[True, False])
Missing values go last whatever the direction, unless na_position="first". Rows with equal values may come out in any order unless you ask for a stable sort with kind="stable", which keeps them in their original order; when sorting by several columns, pandas always uses a stable sort. With a list of columns, the second column only orders rows that tie on the first, and ascending can take one flag per column. sort_index() sorts by the row labels instead.
df.nlargest(3, "score") returns the three rows with the highest scores (nsmallest the lowest), which is faster than sorting everything when you want a top few.
rank gives each value its place in sorted order, starting at 1. The question is what tied values get, and the method argument answers it: "average" (the default) gives each the average of their places, so two values tied for 2nd and 3rd both get 2.5; "min" gives both 2, as sports tables do; "max" gives both 3; "dense" gives 2 and lets the next value be 3, with no gap; "first" breaks ties by order of appearance. ascending=False ranks the largest value first. Missing values get a rank of NaN.
value_counts counts each distinct value of a column, most common first, and returns a Series indexed by the values. normalize=True gives fractions instead of counts, and missing values are left out unless dropna=False.
df["city"].value_counts().tolist() # [3, 2]: Dhaka, then London
df["score"].rank(ascending=False).tolist() # [2.5, 1.0, 2.5, nan, 4.0]
df["score"].rank(ascending=False, method="min").tolist() # [2.0, 1.0, 2.0, nan, 4.0]
Example
# Sorting rows, ranking values with ties, and counting values.
names = ["Ada", "Rahim", "Mei", "Omar", "Lena"]
cities = ["London", "Dhaka", "Dhaka", "London", "Dhaka"]
scores = [88, 92, 88, None, 75]
rows = list(zip(names, cities, scores))
present = [r for r in rows if r[2] is not None]
missing = [r for r in rows if r[2] is None]
# sort_values("score", ascending=False, kind="stable"): missing values go last
by_score = sorted(present, key=lambda r: -r[2]) + missing
print("by score:", [r[0] for r in by_score])
# sort_values(["city", "score"], ascending=[True, False])
by_city = sorted(present, key=lambda r: (r[1], -r[2]))
print("by city, then score:", [(r[1], r[0]) for r in by_city])
# rank(ascending=False): tied values share the average of their places
ordered = sorted([r[2] for r in present], reverse=True)
for value in sorted(set(ordered), reverse=True):
places = [i + 1 for i, x in enumerate(ordered) if x == value]
print(value, "has rank", sum(places) / len(places))
# value_counts(): how often each value appears, most common first
counts = {}
for c in cities:
counts[c] = counts.get(c, 0) + 1
print("value_counts:", sorted(counts.items(), key=lambda kv: -kv[1]))Line by line
- The rows with a score are sorted from highest to lowest; Python's sort is stable, so Ada stays before Mei, and Omar, with no score, goes last.
- Sorting by city and then by score descending uses a tuple key with a negated score.
- The two 88s fill places 2 and 3, so each gets the average, 2.5.
- Counting the cities gives Dhaka 3 and London 2, most common first.
Expected result
by score: ['Rahim', 'Ada', 'Mei', 'Lena', 'Omar']
by city, then score: [('Dhaka', 'Rahim'), ('Dhaka', 'Mei'), ('Dhaka', 'Lena'), ('London', 'Ada')]
92 has rank 1.0
88 has rank 2.5
75 has rank 4.0
value_counts: [('Dhaka', 3), ('London', 2)]
In the real world
Leaderboards, top-ten product lists and "most common complaints" reports are sorting, ranking and value_counts. Percentile ranks of exam scores use rank(pct=True). Analysts run value_counts on every text column of a new table, because it shows misspellings at once: "Dhaka", "dhaka" and "Dhaka " appear as three different values.
Common mistakes
- Assuming tied rows keep their order without
kind="stable". - Forgetting that missing values sort last in both directions, which can hide them at the bottom.
- Using the default average ranks where a competition expects the min method.
- Missing the NaN count in value_counts, which is hidden unless dropna=False.
Best practices
- Sort with explicit directions for each column.
- Choose a rank method on purpose, and say which in reports.
- Run value_counts(dropna=False) to see the whole picture of a column, gaps included.
Summary
sort_values orders rows by one or more columns, with missing values last and a stable sort on request; sort_index orders by labels and nlargest takes the top rows. rank turns values into places, with average, min, max, dense or first deciding ties. value_counts counts distinct values, most common first, without missing values unless asked.
Quick check
Key takeaways
- sort_values takes one column or a list, with an ascending flag for each; missing values go last.
- rank turns values into places, and its method decides what ties get.
- value_counts counts distinct values and leaves out missing ones unless dropna=False.
Practice problems for this lesson
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.