Analytics Made Simple

,

Selecting, filtering, and sorting in pandas

Jose S
8 min read
Featured image: Select filter sort, Python for analytics series

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:

  1. Filter rows with a condition that returns True/False per row.
  2. Select the columns you need.
  3. 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.”

Diagram mapping SQL select filter sort to pandas

SQL to pandas cheatsheet

SQL ideapandas patternNotes
SELECT a, bdf[["a", "b"]]Double brackets return a DataFrame
SELECT adf["a"] or df[["a"]]Single brackets often return a Series
WHERE x = 1df[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 NULLdf[df["x"].isna()]Part 7 deepens null handling
ORDER BY a DESCdf.sort_values("a", ascending=False)Stable and readable
ORDER BY a, bdf.sort_values(["a", "b"])List of columns
LIMIT 10df.head(10) after sortOr .iloc[:10] if needed
SELECT DISTINCT adf["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:

c4 filter flow

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:

c4 filter east
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:

c4 isin query
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:

c4 sort revenue
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_idregionrevenue
1East4200
2West8100
3East6900
4South1500
5West3200
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):

  1. Select two columns only and print the head.
  2. Filter to one region and print the shape before and after.
  3. Combine a region filter with a numeric threshold.
  4. Sort descending by the numeric column and show the top three.
  5. 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

Merch on Etsy

Open store →
Y-Hat Coffee Mug View on Etsy → Data Science T-Shirt View on Etsy → Dashboard to Excel Ceramic Coffee Mug View on Etsy → ETL Data Science Shirt View on Etsy →