Filtering and sorting

Why it matters

Most questions are about a subset: orders from one region, samples above a threshold, patients in a date range, the ten largest accounts. Selecting the right rows and columns, and ordering them, is the most common thing you will do in pandas. It is also where many silent bugs hide, so it is worth learning the clear, explicit way.

We will use this table:

orders = pd.DataFrame({
    "order_id": [101, 102, 103, 104, 105, 106],
    "region":   ["East", "West", "East", "North", "East", "West"],
    "product":  ["Kettle", "Toaster", "Blender", "Kettle", "Toaster", "Blender"],
    "units":    [12, 3, 15, 7, 4, 10],
    "price":    [25.0, 40.0, 60.0, 25.0, 40.0, 60.0],
})
orders["revenue"] = orders["units"] * orders["price"]

Selecting columns

orders["units"]                    # one column -> Series
orders[["order_id", "units"]]      # a list of columns -> DataFrame

Note the double brackets: the inner brackets are a Python list of names.

Boolean masks

A comparison on a column gives a mask: a Series of True/False, one per row.

mask = orders["units"] >= 10
# [True, False, True, False, False, True]
orders[mask]          # only the True rows: 101, 103, 106

Combine conditions with & (and), | (or) and ~ (not), and wrap each condition in parentheses:

east_big = orders[(orders["region"] == "East") & (orders["units"] >= 10)]
# orders 101 and 103

not_east = orders[~(orders["region"] == "East")]

Python's and/or do not work here: they try to treat a whole Series as one True or False and raise ValueError: The truth value of a Series is ambiguous.

For "one of several values" use isin; for ranges use between (inclusive at both ends):

orders[orders["product"].isin(["Kettle", "Toaster"])]
orders[orders["price"].between(30, 60)]

.loc and .iloc

.loc selects by label (index labels and column names), and accepts masks. It is the clearest way to pick rows and columns at once:

orders.loc[orders["units"] < 5, ["order_id", "product"]]
#    order_id  product
# 1       102  Toaster
# 4       105  Toaster

.iloc selects by position, like list slicing:

orders.iloc[0]        # first row
orders.iloc[:3, :2]   # first 3 rows, first 2 columns

Rule of thumb: use .loc with conditions and names in analysis code; use .iloc when position really is what you mean ("the first row after sorting"). Note that .loc slices include the end label, while .iloc slices exclude the end position.

query is a readable alternative for simple filters: orders.query("region == 'East' and units >= 10").

Sorting

orders.sort_values("revenue", ascending=False)              # largest first
orders.sort_values(["region", "units"], ascending=[True, False])  # region A-Z, then units high-low
orders.nlargest(3, "revenue")                                # the top 3 rows by revenue

After filtering and sorting the index keeps the old labels (for example 2, 0, 5). Use .reset_index(drop=True) when you want 0, 1, 2 again, and remember that .iloc[0] means "first row now" while .loc[0] means "the row whose label is 0".

A worked example

"List the East orders worth at least 300, largest first, showing only id and revenue":

result = (
    orders.loc[(orders["region"] == "East") & (orders["revenue"] >= 300), ["order_id", "revenue"]]
          .sort_values("revenue", ascending=False)
          .reset_index(drop=True)
)
#    order_id  revenue
# 0       103    900.0
# 1       101    300.0

Wrapping a chain in parentheses lets you put each step on its own line.

Common mistakes

• Using and/or instead of &/|. Raises the "truth value is ambiguous" error.
• Missing parentheses. df["a"] == 1 & df["b"] == 2 is parsed wrongly because & binds tighter than ==.
• Forgetting that sorting returns a new DataFrame. orders.sort_values("units") on its own line changes nothing; assign the result.
• Mixing up .loc[0] and .iloc[0] after filtering or sorting.
• Filtering, then assigning into the result and expecting the original to change. Use .loc[mask, "col"] = value on the original.

Quick recap

• df[mask] keeps rows where the mask is True; combine with &, |, ~ and parentheses.
• isin for sets of values, between for ranges.
• .loc[rows, cols] by label or mask; .iloc by position.
• sort_values (assign the result), nlargest for top N, reset_index(drop=True) to renumber.

Try it

In the Do tab you will filter, select and sort an orders table: big East orders, a two-column view of small orders, and the top three orders by revenue.