Selecting, filtering, and sorting in pandas

Most analysis is not modeling. It is: keep these columns, keep these rows, put the important ones on top. In SQL that is SELECT, WHERE, and ORDER BY. In pandas it is column lists, boolean masks, and sort_values. Same job, different spelling. Once you see the map, the fear drops.

This is Part 4 of Python for analytics. Part 3 made DataFrames feel like tables. Now we slice them with clear patterns, not clever one-liners you cannot debug at 5 p.m. If you already think in SQL from the SQL series, you are ahead. Foundations about asking the right question still apply: Analytics foundations. For the series map, see Learn.

What you will learn

How to select columns (the SELECT list)
How to filter rows with boolean masks (the WHERE clause)
How to sort results (ORDER BY)
When to reach for loc vs plain brackets (keep it light)
How to chain steps carefully without losing readability
A SQL to pandas cheatsheet you can keep open

The mental model: mask, then project, then order

SQL engines optimize freely, but humans often think: start from a table, filter rows, pick columns, sort. In pandas, a crystal-clear pattern is:

Filter rows with a condition that returns True/False per row.
Select the columns you need.
Sort for presentation or for a “top N” follow-up.

You can reorder those steps, but teaching them in that order matches how most analysts describe a request: “East region only, show order id and revenue, highest first.”

SQL to pandas cheatsheet

SQL idea	pandas pattern	Notes
`SELECT a, b`	`df[["a", "b"]]`	Double brackets return a DataFrame
`SELECT a`	`df["a"]` or `df[["a"]]`	Single brackets often return a Series
`WHERE x = 1`	`df[df["x"] == 1]`	Boolean mask inside brackets
`WHERE x > 10 AND y = 'East'`	`df[(df["x"] > 10) & (df["y"] == "East")]`	Use `&` `\|` with parentheses
`WHERE x IN (...)`	`df[df["x"].isin([...])]`	Great for region lists
`WHERE x IS NULL`	`df[df["x"].isna()]`	Part 7 deepens null handling
`ORDER BY a DESC`	`df.sort_values("a", ascending=False)`	Stable and readable
`ORDER BY a, b`	`df.sort_values(["a", "b"])`	List of columns
`LIMIT 10`	`df.head(10)` after sort	Or `.iloc[:10]` if needed
`SELECT DISTINCT a`	`df["a"].drop_duplicates()`	Or `df.drop_duplicates(subset=["a"])`

Pin this table. Most weekly work lives inside it.

Selecting columns

Keep a running count as you filter:

Start with the sample orders frame from earlier parts (or any tidy table).

import pandas as pd

orders = pd.read_csv("hello_orders.csv")

# One column as a Series
regions = orders["region"]

# Several columns as a DataFrame (note the double brackets)
slim = orders[["order_id", "region", "revenue"]]

print(slim.head())

Double brackets feel odd until they do not. Single column name: Series. List of names: DataFrame. If you need a one-column DataFrame on purpose (to keep table methods), use [["revenue"]].

Selecting columns is also how you drop noise early. Wide exports with forty unused fields slow down reading and invite wrong joins later. Project to what the question needs.

Filtering rows with boolean masks

A boolean mask is a Series of True/False values aligned to rows. Put the mask inside brackets and pandas keeps the True rows.

# Rows where region is East
east = orders[orders["region"] == "East"]

# Rows with revenue over 5000
big = orders[orders["revenue"] > 5000]

print(east)
print(big)

Example output:

Example output: filter region == East

Combine conditions with & (and), | (or), and ~ (not). Always wrap each comparison in parentheses. That is not optional style. Operator precedence will bite you.

east_big = orders[
    (orders["region"] == "East") & (orders["revenue"] > 5000)
]

west_or_south = orders[orders["region"].isin(["West", "South"])]

print(east_big)
print(west_or_south)

Example:

isin + query select

Why not English and/or? Those try to reduce whole objects to single True/False values. pandas needs element-wise logic. Use the symbols and parentheses. It looks mathy. It is the dialect.

Name intermediate masks when logic gets long:

is_east = orders["region"] == "East"
is_big = orders["revenue"] > 5000
east_big = orders[is_east & is_big]

Readable masks are kinder to reviewers (including future you) than a nested monster.

Sorting

Sorting does not change the meaning of the data. It changes presentation and which rows you see first after a head.

by_revenue = orders.sort_values("revenue", ascending=False)

by_region_then_revenue = orders.sort_values(
    ["region", "revenue"], ascending=[True, False]
)

print(by_revenue)
print(by_region_then_revenue)

Example output:

Example output: sort by revenue

After a descending sort, head(3) is “top three by revenue,” which is a common slide request. Remember: ties exist. If leadership cares about stable ranking rules, define the tie-breakers as extra sort keys.

loc and iloc, lightly

You will see loc and iloc everywhere online. Here is the calm version:

loc selects by label (index labels and column names). Useful for “rows where…, columns A and B” in one step.
iloc selects by integer position (row 0, column 1). Useful for positional slices, not for business logic on region names.

# Same filter + column project with loc
east_ids = orders.loc[orders["region"] == "East", ["order_id", "revenue"]]

# First three rows, first two columns by position
corner = orders.iloc[:3, :2]

print(east_ids)
print(corner)

For most analytics, df[mask][columns] or a careful loc is enough. Avoid building identity around obscure indexing tricks. Clarity ships.

Chaining carefully

Chaining means stacking operations in one expression. It can read like a pipeline. It can also become a debugging nightmare if every step is invisible.

# Clear chain: parentheses let you break lines
result = (
    orders.loc[orders["revenue"] > 3000, ["order_id", "region", "revenue"]]
    .sort_values("revenue", ascending=False)
    .reset_index(drop=True)
)

print(result)

reset_index(drop=True) gives a clean 0…n index after filters. Optional, but nice before export. Prefer named intermediate variables when you are still learning or when a step needs a comment about business rules (“exclude internal test region”).

# Same logic, easier to debug
active = orders[orders["revenue"] > 3000]
slim = active[["order_id", "region", "revenue"]]
result = slim.sort_values("revenue", ascending=False).reset_index(drop=True)

Both are professional. The second is kinder when someone asks “what does step two do?” in a code review.

Worked example: from full table to answer

Business ask: “Show West and East orders over $4,000, only id, region, revenue, highest revenue first.”

order_id	region	revenue
1	East	4200
2	West	8100
3	East	6900
4	South	1500
5	West	3200

import pandas as pd

orders = pd.read_csv("hello_orders.csv")

answer = (
    orders[
        orders["region"].isin(["East", "West"])
        & (orders["revenue"] > 4000)
    ][["order_id", "region", "revenue"]]
    .sort_values("revenue", ascending=False)
    .reset_index(drop=True)
)

print(answer)

Expected rows: order 2 (West, 8100), order 3 (East, 6900), order 1 (East, 4200). South is out by region. West 3200 is out by threshold. That is the whole game: mask, project, sort.

Equivalent SQL for the same ask:

SELECT
  order_id,
  region,
  revenue
FROM orders
WHERE region IN ('East', 'West')
  AND revenue > 4000
ORDER BY revenue DESC;

If your data already lives in a warehouse, prefer this SQL and skip the download. If you already have a file, the pandas version is the right desk (see Part 1).

Empty results are information

When a filter returns zero rows, do not assume the business has no matching activity. Check the boring causes first:

String case or spelling (East vs EAST)
Extra spaces in category labels
Threshold units (dollars vs thousands)
Date filters on columns that are still text
You filtered an already filtered frame by accident

# Quick diagnostics when a filter looks "too empty"
print(orders["region"].unique())
print(orders["revenue"].min(), orders["revenue"].max())
print(orders.shape)
print(orders[orders["region"] == "East"].shape)

Print unique values and min/max before you rewrite the whole notebook. Most “pandas is broken” moments are “my assumption about the data was wrong,” which is normal analytics work, not a personal failure.

The same diagnostic mindset applies when a filter returns “too many” rows. If you expected dozens and got tens of thousands, you may have used | where you meant &, or forgotten parentheses so the mask did not mean what you read in English.

Assignment and the “copy” anxiety

You may see warnings about SettingWithCopy when you filter and then assign into a slice. For analytics, a safe habit is: when you want a standalone table, make the intention obvious.

east = orders.loc[orders["region"] == "East"].copy()
east["revenue_k"] = east["revenue"] / 1000

.copy() says “this is my new working table.” You avoid accidental links to the parent frame. Part 8 pipelines will lean on clear assignments like this.

Common mistakes

Using Python and/or between conditions. Use &/| with parentheses.
Forgetting that df["a"] is a Series. Methods differ slightly from DataFrame methods.
Filtering on the wrong grain. Filtering a customer-level frame with an order-level rule produces nonsense.
Sorting before filtering for “top N” and then forgetting the filter. Order of operations should match the business sentence.
Case-sensitive string matches. "east" is not "East". Normalize categories when sources are messy.
Chaining so hard nobody can insert a row count check. Print shape after big filters.
Using iloc for business rules. Positions change when data changes. Prefer column conditions.

Rule of thumb: Write the request as “which rows, which columns, which order,” then implement those three steps with masks, column lists, and sort_values.

Practice and next step

Using your hello CSV (or a real extract):

Select two columns only and print the head.
Filter to one region and print the shape before and after.
Combine a region filter with a numeric threshold.
Sort descending by the numeric column and show the top three.
Rewrite the same logic as SQL comments above the pandas code to prove the map.

Next: Part 5, Aggregations and groupby, maps GROUP BY to split-apply-combine so you can answer “total by region” without a spreadsheet pivot every time.

Quick recap

Column lists are SELECT.
Boolean masks are WHERE (with &/| and parentheses).
sort_values is ORDER BY.
loc helps label-based row+column selection; iloc is positional.
Chain when it stays readable; otherwise name steps.
Check shape after filters so you know what you actually kept.

Sources

pandas, “Indexing and selecting data”: https://pandas.pydata.org/docs/user_guide/indexing.html
pandas DataFrame.sort_values: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.sort_values.html
pandas Series.isin: https://pandas.pydata.org/docs/reference/api/pandas.Series.isin.html
pandas, “10 minutes to pandas”: https://pandas.pydata.org/docs/user_guide/10min.html
Analytics Made Simple, Learn: https://analyticsmadesimple.com/learn/
Analytics Made Simple, SQL series: https://analyticsmadesimple.com/series/sql/
Analytics Made Simple, Analytics foundations: https://analyticsmadesimple.com/series/analytics-foundations/
Analytics Made Simple, From spreadsheets to real data: https://analyticsmadesimple.com/series/spreadsheets-to-data/