Lesson 2 of 8 · 7 min · Working with tables in pandas
Selecting and filtering rows with loc, iloc and conditions
What you will learn
- Filter rows with conditions,
&,|and~ - Use
isin,between,str.containsandquery - Select rows and columns with
locandiloc - Sort and take top rows
- Change values safely, and why chained assignment no longer works
In simple words
Filtering is asking "show me only the rows where...". In Excel you click the filter arrow; in SQL you write WHERE. In pandas you write a condition that gives a yes or no for every row, and use it as a stencil: only the rows with "yes" get through. Selecting is choosing which rows and columns you want to look at.
Words to know
- Boolean mask: a column of True and False values, one per row, used to pick rows.
- loc: select by labels (index values and column names).
- iloc: select by position (the first row, the third column).
- Chained assignment: changing data through two indexing steps in a row, such as
df[a][b] = x, which is unreliable.
Filter with a condition
Using the orders DataFrame from the first lesson:
orders["amount"] > 800
gives a Series of booleans, False, False, True, .... Use it inside brackets:
orders[orders["amount"] > 800]
returns orders 101 (1200), 103 (1500) and 105 (900). Combine conditions with & (and), | (or) and ~ (not), and put each condition in brackets, because these operators bind tighter than comparisons:
orders[(orders["status"] == "paid") & (orders["amount"] >= 800)] # orders 101, 102
orders[(orders["status"] == "refunded") | (orders["status"] == "pending")]
orders[~(orders["status"] == "paid")]
Do not use Python's and, or, not here: they raise "truth value of a Series is ambiguous".
Handy filters
orders[orders["status"].isin(["paid", "pending"])] # in a list
orders[orders["amount"].between(600, 900)] # inclusive: 600, 800, 900
orders[orders["order_date"] >= "2025-10-01"] # dates compare with strings
orders[orders["status"].str.contains("pa")] # text contains
orders[orders["status"].isna()] # missing values
.query() writes the same condition as text, which many find easier to read:
orders.query("status == 'paid' and amount >= 800")
min_amount = 800
orders.query("amount >= @min_amount") # @ uses a Python variable
loc: select by label
loc[rows, columns] selects with labels or a boolean mask. It is also the correct way to filter and pick columns together:
orders.loc[orders["status"] == "paid", ["order_id", "amount"]]
returns order id and amount for the four paid orders. A slice with loc includes both ends: orders.loc[0:2] gives rows labelled 0, 1 and 2.
iloc: select by position
iloc[rows, columns] uses integer positions, and excludes the end like normal Python slices:
orders.iloc[0] # the first row
orders.iloc[:3] # the first three rows
orders.iloc[:, 0:2] # all rows, the first two columns
orders.iloc[-1] # the last row
After sorting or filtering, labels and positions differ, so know which one you mean.
Sort and top rows
orders.sort_values("amount", ascending=False)
orders.sort_values(["status", "amount"], ascending=[True, False])
orders.nlargest(3, "amount") # top 3 by amount
orders.nsmallest(2, "amount")
orders.drop_duplicates(subset="customer_id") # first row per customer (next lessons)
nlargest is faster and shorter than sorting and slicing.
Changing values safely
To set values for rows that meet a condition, use loc in one step:
orders.loc[orders["status"] == "pending", "status"] = "open"
orders.loc[orders["amount"] > 1000, "tier"] = "high"
Another tidy way is np.where or .where/.mask, and .map with a dictionary:
orders["status_label"] = orders["status"].map({"paid": "Paid", "refunded": "Refund", "pending": "Open"})
Chained assignment does not work in pandas 3:
orders[orders["amount"] > 1000]["status"] = "big" # no effect
The first brackets create a new (copied) object, and you assign into that, leaving orders unchanged. Under copy-on-write there is no warning to rely on; write orders.loc[orders["amount"] > 1000, "status"] = "big" instead. In pandas 3 you also no longer need defensive .copy() calls after filtering, because the result already behaves as a copy.
Counting and checking
(orders["amount"] > 800).sum() # how many rows pass: 3 (True counts as 1)
(orders["amount"] > 800).mean() # proportion: 0.5
orders["status"].eq("paid").any() # any? True
len(orders[orders["status"] == "paid"]) # 4
Common mistakes
- Using
and/orinstead of&/|, or forgetting brackets around each condition. - Chained assignment (
df[mask]["col"] = value). - Mixing up
locandilocafter sorting. - Comparing text dates in a different format than
YYYY-MM-DD. - Forgetting that missing values are neither equal nor unequal; use
isna().
Try it yourself
- Find paid orders above 700.
- Find orders in October 2025 using
betweenor a condition on dates. - Show only
order_idandamountfor refunded or pending orders usingloc. - Top three orders by amount with
nlargest. - Set a
sizecolumn to "large" for amounts of 1000 or more and "small" otherwise in one step (hint:np.whereormap).
Check yourself
Why must each condition be in brackets with & and |?
Those operators have higher precedence than comparison operators, so without brackets Python evaluates them in the wrong order and raises an error.
What is the difference between loc and iloc?
loc selects by label (index values and column names, slices inclusive); iloc selects by integer position (end excluded).
Why does df[df["a"] > 1]["b"] = 0 not change df?
It is chained assignment: the first selection creates a separate object and the assignment goes into that. Use df.loc[df["a"] > 1, "b"] = 0.
What does df.query("a > @x") do?
It filters rows with a text condition, where @x is the Python variable x.
Interview questions
How do you filter a DataFrame on multiple conditions?
Build boolean masks, put each in brackets and combine with & for and, | for or, ~ for not, then index the DataFrame with the combined mask or use query.
What is the SettingWithCopyWarning and what replaced it?
It warned about chained assignment that might change a copy rather than the original. In pandas 3, copy-on-write makes derived objects always behave as copies, the warning is gone, and chained assignment simply has no effect, so use loc for assignment.
How do you get the top 5 rows by a column?
df.nlargest(5, "col"), or sort_values("col", ascending=False).head(5).
Summary
- A boolean mask picks rows; combine masks with
&,|,~in brackets. isin,between,str.contains,isnaandquerycover most filters.locselects by label and mask;ilocby position.- Assign with
df.loc[mask, "col"] = value, never with chained brackets.
Going deeper
- Indexing and selecting data:
loc,ilocand boolean indexing. - Copy-on-Write: why chained assignment stopped working.
- DataFrame.query: syntax and the
@variable.
See how much of this you can explain
A free mock interview asks you new questions on Python for data analysis with pandas for Data Analyst and scores every answer, with what to fix.