Filtering, sorting and ranking

Filtering keeps the rows that meet a condition, sorting puts rows in order and ranking numbers them by a value. pandas has a ready-made tool for each common filter: isin for “one of these values”, between for a range (both ends included unless you say otherwise), str.contains for text, and query for a whole condition written as one readable string. sort_values orders rows by one or more columns and puts missing values last unless you ask otherwise. rank turns values into places, with five different rules for ties, and nlargest picks the top N. Ties are where reports go wrong. When three rows share the value at the cut-off, “the top 3” has more than one defensible answer, and two tools, or the same tool on a reshuffled file, can pick differently. This lesson shows each operation on a small table of pass rates and ends with a rule that makes a top N come out the same every time.

The results table

The examples use invented pass rates for eight colleges in four cities. Three colleges share a rate of 88.0, one result is missing, and two names start with a lower-case letter, so every tie and ordering rule below has something to show:

college,city,pass_rate,students
Alpha Institute,Pune,91.5,420
beta college,Delhi,88.0,310
Gamma Academy,Pune,88.0,275
Delta Polytechnic,Chennai,95.2,510
Epsilon College,Delhi,88.0,190
Zeta School,Pune,,230
eta institute,Kochi,79.4,150
Theta College,Chennai,84.1,380

Filtering with isin, between, str.contains and query

import pandas as pd

results = pd.read_csv("results.csv")

print(results[results["city"].isin(["Pune", "Kochi"])]["college"].tolist())
print(results[results["pass_rate"].between(84.1, 91.5)]["college"].tolist())  # both ends included
print(results[results["pass_rate"].between(84.1, 91.5, inclusive="neither")]["college"].tolist())
print(results[results["college"].str.contains("college", case=False)]["college"].tolist())

limit, cities = 85, ["Pune", "Delhi"]
print(results.query("pass_rate > @limit and city in @cities")["college"].tolist())

['Alpha Institute', 'Gamma Academy', 'Zeta School', 'eta institute']
['Alpha Institute', 'beta college', 'Gamma Academy', 'Epsilon College', 'Theta College']
['beta college', 'Gamma Academy', 'Epsilon College']
['beta college', 'Epsilon College', 'Theta College']
['Alpha Institute', 'beta college', 'Gamma Academy', 'Epsilon College']

• isin answers “is the value one of these?”. city.isin(["Pune", "Kochi"]) keeps four colleges. It replaces a chain of == comparisons joined with |, and a missing city does not match a list of city names.
• between(low, high) keeps a range and includes both ends by default, so 84.1 and 91.5 are kept in the first call and dropped with inclusive="neither". The other choices are "left" and "right". Writing the argument out even when you want the default tells the next reader that you thought about the ends.
• str.contains matches part of a text value; case=False ignores letter case. The pattern is a regular expression by default, so characters such as . or ( have special meanings: pass regex=False to look for the text literally. On a str column, a missing value gives False, so it is simply not kept.
• query takes the condition as one string, where and and or are allowed and @name refers to a Python variable. It reads well in a notebook, but the string is checked only when it runs, column names with spaces need backticks, and your editor cannot help with it. In code that others reuse, boolean masks are the safer habit.

Sorting by several keys

import pandas as pd

results = pd.read_csv("results.csv")
cols = ["college", "pass_rate"]

print(results.sort_values("pass_rate", ascending=False)[cols])  # the missing rate goes last
print(results.sort_values("pass_rate", ascending=False, na_position="first")[cols].head(2))

# Two keys: the rate, highest first; then the name, A to Z, to break ties.
ranked = results.sort_values(["pass_rate", "college"], ascending=[False, True])
print(ranked["college"].tolist())

# Names compare by code point, so capitals come before every lower-case letter.
print(results.sort_values("college")["college"].tolist())
print(results.sort_values("college", key=lambda s: s.str.casefold())["college"].tolist())

             college  pass_rate
3  Delta Polytechnic       95.2
0    Alpha Institute       91.5
1       beta college       88.0
2      Gamma Academy       88.0
4    Epsilon College       88.0
7      Theta College       84.1
6      eta institute       79.4
5        Zeta School        NaN
             college  pass_rate
5        Zeta School        NaN
3  Delta Polytechnic       95.2
['Delta Polytechnic', 'Alpha Institute', 'Epsilon College', 'Gamma Academy', 'beta college', 'Theta College', 'eta institute', 'Zeta School']
['Alpha Institute', 'Delta Polytechnic', 'Epsilon College', 'Gamma Academy', 'Theta College', 'Zeta School', 'beta college', 'eta institute']
['Alpha Institute', 'beta college', 'Delta Polytechnic', 'Epsilon College', 'eta institute', 'Gamma Academy', 'Theta College', 'Zeta School']

• sort_values("pass_rate", ascending=False) puts the highest rate first, and the missing one last, as it would in an ascending sort: na_position="last" is the default in both directions. na_position="first" puts Zeta School on top instead.
• The three colleges at 88.0 are a tie. pandas’ default sorting algorithm is not stable, so the order of tied rows is not something to rely on. Give the sort a second key that decides ties: ["pass_rate", "college"] with ascending=[False, True] sorts by rate, highest first, and then by name.
• That second key reveals the last trap: text compares by code point, and every capital letter from A to Z comes before every lower-case letter, so “beta college” sorts after “Zeta School”. key=lambda s: s.str.casefold() sorts without regard to case. The key function is applied to each sort column in turn, so in a sort with several keys it has to leave the number columns alone, as the next example’s rule does.

Ranking: five ways to number ties

rank gives every row its place by a value. Ties are the whole question, and the method argument decides them:

import pandas as pd

results = pd.read_csv("results.csv")
rate = results["pass_rate"]
table = results[["college", "pass_rate"]].copy()
for method in ["average", "min", "max", "dense", "first"]:
    table[method] = rate.rank(method=method, ascending=False)
print(table.sort_values("first", na_position="last").to_string(index=False))

          college  pass_rate  average  min  max  dense  first
Delta Polytechnic       95.2      1.0  1.0  1.0    1.0    1.0
  Alpha Institute       91.5      2.0  2.0  2.0    2.0    2.0
     beta college       88.0      4.0  3.0  5.0    3.0    3.0
    Gamma Academy       88.0      4.0  3.0  5.0    3.0    4.0
  Epsilon College       88.0      4.0  3.0  5.0    3.0    5.0
    Theta College       84.1      6.0  6.0  6.0    4.0    6.0
    eta institute       79.4      7.0  7.0  7.0    5.0    7.0
      Zeta School        NaN      NaN  NaN  NaN    NaN    NaN

• average, the default, gives tied rows the mean of the places they occupy: places 3, 4 and 5 make 4.0 each.
• min gives each tied row the lowest of those places, 3, and the next row jumps to 6, like SQL’s RANK().
• max gives the highest place, 5.
• dense gives 3 and lets the next row continue at 4, like SQL’s DENSE_RANK().
• first numbers the tied rows in the order they appear in the data, 3, 4 and 5, like SQL’s ROW_NUMBER() would if its window were ordered by the original row order.

Ranks are floats because the average method can produce halves. A missing value gets no rank (na_option="keep"); na_option="bottom" ranks it after everything else.

Top N, and why ties make it ambiguous

nlargest(3, "pass_rate") looks like the obvious “top 3”, but the third place belongs to three colleges at once. Its keep argument decides what to do about it:

import pandas as pd

results = pd.read_csv("results.csv")

for keep in ["first", "last", "all"]:
    top = results.nlargest(3, "pass_rate", keep=keep)
    print(f"keep={keep!r:8}", top["college"].tolist())

# A rule anyone can rerun: highest rate first, ties broken by name (A to Z, ignoring case).
by_rule = results.sort_values(
    ["pass_rate", "college"],
    ascending=[False, True],
    key=lambda s: s.str.casefold() if s.name == "college" else s,
)
print("rule:     ", by_rule.head(3)["college"].tolist())

# Everyone whose dense rank is 3 or better: the podium, ties included.
dense = results["pass_rate"].rank(method="dense", ascending=False)
print("dense<=3: ", results[dense <= 3]["college"].tolist())

keep='first'  ['Delta Polytechnic', 'Alpha Institute', 'beta college']
keep='last'   ['Delta Polytechnic', 'Alpha Institute', 'Epsilon College']
keep='all'    ['Delta Polytechnic', 'Alpha Institute', 'beta college', 'Gamma Academy', 'Epsilon College']
rule:      ['Delta Polytechnic', 'Alpha Institute', 'beta college']
dense<=3:  ['Alpha Institute', 'beta college', 'Gamma Academy', 'Delta Polytechnic', 'Epsilon College']

• keep="first" takes the tied row that comes first in the data, beta college. Shuffle the file and the answer changes, although no number did.
• keep="last" takes the last one, Epsilon College.
• keep="all" returns every tied row, five colleges for a “top 3”.
• The rule sorts by rate and then by name, ignoring letter case, and takes the first three. It gives the same answer whatever the file order, and anyone can check it.
• A dense rank of 3 or better keeps the colleges with the three highest distinct rates, which is five colleges here, listed in file order.

Decide which question the report answers before choosing: exactly three rows needs a tie-breaker rule, written down in the report; “the three best rates” is the dense rank; “everyone at least as good as the third” is keep="all".

Missing values sort differently in polars and DuckDB

The same table, sorted by the same column in three tools, does not put the missing value in the same place:

import duckdb
import pandas as pd
import polars as pl

scores = pd.DataFrame({
    "college": ["Alpha", "Zeta", "Gamma", "Delta"],
    "pass_rate": [91.5, None, 88.0, 95.2],  # Zeta's result is missing
})
as_polars = pl.from_pandas(scores)  # the missing value becomes a polars null


def duck(order):
    return [row[0] for row in duckdb.sql(f"SELECT college FROM scores ORDER BY pass_rate {order}").fetchall()]


print("pandas  ascending: ", scores.sort_values("pass_rate")["college"].tolist())
print("pandas  descending:", scores.sort_values("pass_rate", ascending=False)["college"].tolist())
print("polars  ascending: ", as_polars.sort("pass_rate")["college"].to_list())
print("polars  descending:", as_polars.sort("pass_rate", descending=True)["college"].to_list())
print("DuckDB  ascending: ", duck("ASC"))
print("DuckDB  descending:", duck("DESC"))

pandas  ascending:  ['Gamma', 'Alpha', 'Delta', 'Zeta']
pandas  descending: ['Delta', 'Alpha', 'Gamma', 'Zeta']
polars  ascending:  ['Zeta', 'Gamma', 'Alpha', 'Delta']
polars  descending: ['Zeta', 'Delta', 'Alpha', 'Gamma']
DuckDB  ascending:  ['Gamma', 'Alpha', 'Delta', 'Zeta']
DuckDB  descending: ['Delta', 'Alpha', 'Gamma', 'Zeta']

pandas puts the missing value last in both directions. polars puts nulls first in both directions, because its nulls_last option is False by default. DuckDB puts NULLs last in both directions, its documented default of NULLS LAST, while PostgreSQL treats NULL as the largest value and puts it first in a descending sort. A “highest first” query can therefore start with a missing value in one tool and not in another. Whenever order matters, say it explicitly: na_position in pandas, nulls_last=True in polars, NULLS LAST or NULLS FIRST in SQL.

Practise: the top 2 products per category

A sales table lists products in three categories, with two ties and one product that has no revenue yet. Return the two best-selling products of each category, highest revenue first, breaking ties by product name, and leave out the product without revenue.

Sort by category, then by revenue from high to low, then by name; then take the first two rows of each category. groupby("category").head(2) does that last step: it keeps the first two rows of every category, in the order the rows are in. The group-by module explains group-by in full.

import pandas as pd

sales = pd.DataFrame({
    "category": ["books", "books", "books", "games", "games", "games", "games", "music"],
    "product": ["Atlas", "Novel", "Cookbook", "Chess", "Puzzle", "Cards", "Dice", "Vinyl"],
    "revenue": [1200, 950, 1200, 640, 880, 640, None, 300],
})

top2 = (
    sales.dropna(subset=["revenue"])
    .sort_values(["category", "revenue", "product"], ascending=[True, False, True])
    .groupby("category")
    .head(2)
)
print(top2.to_string(index=False))

category  product  revenue
   books    Atlas   1200.0
   books Cookbook   1200.0
   games   Puzzle    880.0
   games    Cards    640.0
   music    Vinyl    300.0

Atlas and Cookbook tie at 1,200 in books, and the name breaks the tie in Atlas’s favour for first place. In games, Cards and Chess tie at 640 for second place, and Cards wins on its name. Dice had no revenue and was dropped first, and music has only one product, so it gets only one row.

Interview questions

Fresher · How do the rank methods min, dense and first differ?

They differ only in how they number ties. Take the values 95, 91, 88, 88, 88, 84 ranked from the highest. With min, the three tied rows all get the lowest place they share, 3, and the next value gets 6, because places 4 and 5 were used up; this is SQL’s RANK(). With dense, the tied rows also get 3, but the next value gets 4: places count distinct values, as in SQL’s DENSE_RANK(). With first, the tied rows get 3, 4 and 5 in the order they appear in the data, so every row has its own place, like ROW_NUMBER(). The default, average, would give each tied row 4.0. Because first depends on row order, add a tie-breaker sort when the result must be reproducible.

Key takeaways

• isin, between, str.contains and query cover most filters. between includes both ends unless you change inclusive, and str.contains treats its pattern as a regular expression unless regex=False.
• sort_values puts missing values last in both directions by default. polars puts nulls first and DuckDB last, so state the rule whenever order matters.
• Text sorts by code point, so capitals A to Z come before lower-case letters. Sort with key=lambda s: s.str.casefold() for a case-insensitive order.
• The rank methods differ only on ties: min is SQL’s RANK(), dense is DENSE_RANK() and first numbers ties in data order.
• A top N with a tie at the cut-off is ambiguous. Choose keep="all", a dense rank or a written tie-breaker rule instead of relying on row order.