Pandas Selecting and Filtering: loc, iloc, Masks and query

Analysis is mostly asking narrow questions of a big table: "only the delivered orders", "Mumbai stores in the evening slot", "the 50 slowest deliveries", "customers whose name starts with A". In SQL you would write a WHERE clause and a SELECT list. In pandas the same job is done by selecting (choosing rows and columns by label or position) and filtering (choosing rows by a condition). These two skills are the ones you will use hundreds of times a day, so we are going to learn them thoroughly.

The most important rule to take away is this: a condition produces a boolean Series, and passing that Series inside brackets keeps the rows where it is True. You met this idea in NumPy as the boolean mask. Everything in this lesson (comparisons, isin, between, string tests, combining conditions) is a way of producing a good mask. We will also cover the safe way to modify filtered data in pandas 3 and a few pitfalls that confuse almost every beginner.

Worked examples

Example 1: Columns, rows and slices

Question: Pick specific columns, and the first rows, using brackets.

import pandas as pd
import numpy as np

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

print(orders["city"] if "city" in orders else "no city column here")
print(orders[["order_id", "store_id", "order_status"]].head(3))
print(orders[:3].shape)
print(orders[10:13][["order_id"]])

no city column here
  order_id store_id order_status
0   O02299      S03    Delivered
1   O04533      S07    Delivered
2   O00778      S09    Delivered
(3, 9)
   order_id
10   O00619
11   O00918
12   O03406

How it works: df["col"] selects a column, df[["a", "b"]] selects several, and df[10:13] with a slice selects rows by position. Using plain brackets for both columns and rows is convenient but ambiguous, which is why loc and iloc exist. The orders table has no city column (cities live in stores.csv), so the first line returns a message instead of failing.

Reading the result: The slice [:3] returns 3 rows and the column list returns three columns. Avoid mixing meanings in one bracket.

Example 2: loc and iloc

Question: Select by label with loc and by position with iloc.

small = orders.set_index("order_id").head(6)
print(small.iloc[0])
print(small.iloc[1:3, 0:2])
first_id, third_id = small.index[1], small.index[3]
print(small.loc[first_id, "delivery_minutes"])
print(small.loc[first_id:third_id, ["store_id", "order_status"]])
print(small.iloc[-1]["order_status"])

customer_id                 C0078
store_id                      S03
order_date             2025-01-01
delivery_slot       Evening (4-8)
payment_method               Card
order_status            Delivered
delivery_minutes             34.0
discount_pct                   15
Name: O02299, dtype: object
         customer_id store_id
order_id                     
O04533         C0230      S07
O00778         C0328      S09
24.0
         store_id order_status
order_id                      
O04533        S07    Delivered
O00778        S09    Delivered
O03607        S02    Delivered
Delivered

How it works: iloc[row_selection, column_selection] uses integer positions, with end-exclusive slices like lists. loc[row_selection, column_selection] uses labels and slices are inclusive of the end label. After set_index("order_id") the row labels are order ids, so we can ask for an order by its id, such as small.loc[first_id, ...]. Remember: i = integer position, loc = label.

Reading the result: The order O04533 took 24 minutes. Label slices include both ends, which surprises people used to Python slices.

Example 3: Boolean filters

Question: Find delivered orders that were slow.

delivered = orders[orders["order_status"] == "Delivered"]
print(len(delivered))

slow = orders[orders["delivery_minutes"] > 40]
print(len(slow))

print((orders["delivery_minutes"] > 40).head(3).tolist())
print(orders["delivery_minutes"].gt(40).sum())

cancelled = orders[orders["order_status"] != "Delivered"]
print(len(cancelled), len(orders) - len(delivered))

5391
51
[False, False, False]
51
609 609

How it works: orders["delivery_minutes"] > 40 produces a boolean Series with one True/False per row. Putting that inside orders[...] keeps only True rows. The method forms (gt, lt, ge, le, eq, ne) are equivalent to the operators and can be chained. Rows with NaN compare as False, so cancelled orders never appear in slow.

Reading the result: 5,391 orders were delivered and 51 took longer than 40 minutes. The remaining 609 were cancelled or returned.

Example 4: Combining conditions

Question: Evening deliveries in Delhi and Mumbai stores that took over 35 minutes, or any order paid by wallet with a deep discount.

stores = pd.read_csv("stores.csv")
metro_stores = stores.loc[stores["city"].isin(["Delhi", "Mumbai"]), "store_id"]
print(list(metro_stores))

evening_late = orders[
    orders["store_id"].isin(metro_stores)
    & orders["delivery_slot"].str.startswith("Evening")
    & (orders["delivery_minutes"] > 35)
]
print(len(evening_late))

wallet_or_deep = orders[(orders["payment_method"] == "Wallet") | (orders["discount_pct"] >= 20)]
print(len(wallet_or_deep))

not_upi = orders[~(orders["payment_method"] == "UPI")]
print(len(not_upi))

['S03', 'S04', 'S05', 'S06']
29
938
2670

How it works: Combine masks with & (and), | (or) and ~ (not). Each condition must be wrapped in brackets, because & binds more tightly than comparison operators. Splitting a long condition over several lines inside the outer brackets is the clearest style. Do not use the Python words and/or: they fail on Series.

Reading the result: 29 evening orders from the Delhi and Mumbai stores took over 35 minutes, and 938 orders either used a wallet or had a discount of 20 percent or more.

Example 5: isin, between, and missing values

Question: Use shortcuts for membership, ranges and blanks.

print(orders["payment_method"].isin(["UPI", "Card"]).sum())
print(orders["delivery_minutes"].between(20, 30).sum())
print(orders["delivery_minutes"].between(20, 30, inclusive="left").sum())

print(orders["delivery_minutes"].isna().sum(), orders["delivery_minutes"].notna().sum())
print(orders[orders["delivery_minutes"].isna()]["order_status"].value_counts())

print(orders["discount_pct"].value_counts().sort_index().to_dict())

4555
3209
2967
370 5630
order_status
Cancelled    370
Name: count, dtype: int64
{0: 2998, 5: 716, 10: 1056, 15: 724, 20: 506}

How it works: isin replaces a chain of == and |. between(a, b) is inclusive on both ends by default (inclusive="left", "right", "neither" change that). isna() and notna() find blanks; never test with == np.nan. The last check confirms that every row with a blank delivery time is a cancelled order.

Reading the result: 3,209 orders took 20 to 30 minutes inclusive, and the blank delivery times all belong to Cancelled orders. Discount percentages take only the values shown, so they are effectively categories.

Example 6: String filters

Question: Search the customers table by name and email.

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

starts_a = customers[customers["name"].str.startswith("A")]
print(len(starts_a))

contains = customers[customers["name"].str.contains("sharma", case=False)]
print(len(contains), contains["name"].head(3).tolist())

gmail_like = customers["email"].str.endswith("@example.com").sum()
print(gmail_like)

pattern = customers[customers["name"].str.contains(r"^(Isha|Diya)\b", regex=True)]
print(len(pattern))
print(customers["city"].str.lower().str.contains("pune|delhi").sum())

135
34 ['Priya Sharma', 'Nikhil Sharma', 'Suresh Sharma']
600
43
169

How it works: The .str accessor applies string methods to a whole column: startswith, endswith, contains (with optional case=False and regular expressions), lower, strip, split and more. Rows with missing text return NaN, which can break a filter; pass na=False to treat missing as False.

Reading the result: 135 customers have a name beginning with A, and 34 contain "sharma" in any case. A regular expression like ^(Isha|Diya)\b matched 43 names; the regex lesson explains the syntax.

Example 7: query

Question: Write a filter in a SQL-like readable form.

q1 = orders.query("order_status == 'Delivered' and delivery_minutes > 40")
print(len(q1))

limit = 35
q2 = orders.query("delivery_minutes > @limit and store_id in ['S01', 'S02']")
print(len(q2))

q3 = orders.query("payment_method == 'UPI' and discount_pct >= 15").shape
print(q3)

cols = ["order_id", "delivery_minutes"]
print(orders.query("delivery_minutes < 8")[cols].head(3))

50
62
(682, 9)
Empty DataFrame
Columns: [order_id, delivery_minutes]
Index: []

How it works: query takes the condition as a string; column names act like variables, and/or/not work, and @name refers to a Python variable. It reads well and chains neatly, though it is slightly harder to debug than masks, and column names with spaces need backticks.

Reading the result: The delivered-and-slow query gave 50 rows, the same as the equivalent mask, and the query using @limit returned 62. Choose whichever style your team finds clearer.

Example 8: Conditional columns with loc, where and select

Question: Create a delivery performance label.

orders["speed"] = "unknown"
orders.loc[orders["delivery_minutes"] <= 20, "speed"] = "fast"
orders.loc[orders["delivery_minutes"].between(20.01, 35), "speed"] = "normal"
orders.loc[orders["delivery_minutes"] > 35, "speed"] = "slow"
print(orders["speed"].value_counts())

orders["is_late"] = np.where(orders["delivery_minutes"] > 30, "late", "on time")

conditions = [orders["delivery_minutes"].isna(), orders["delivery_minutes"] <= 20, orders["delivery_minutes"] <= 35]
choices = ["no delivery", "fast", "normal"]
orders["speed2"] = np.select(conditions, choices, default="slow")
print(orders["speed2"].value_counts())
print((orders["speed"].replace("unknown", "no delivery") == orders["speed2"]).all())

speed
normal     3514
fast       1836
unknown     370
slow        280
Name: count, dtype: int64
speed2
normal         3514
fast           1836
no delivery     370
slow            280
Name: count, dtype: int64
True

How it works: df.loc[mask, "col"] = value sets values only where the mask is true, creating the column if needed. np.where(cond, a, b) is the vectorised if/else. np.select(conditions, choices, default=...) handles several cases in order; the first matching condition wins. This is how you translate a SQL CASE WHEN expression.

Reading the result: The label counts show 1,836 fast, 3,514 normal and 280 slow deliveries, and both methods agree (True).

Example 9: Index tricks

Question: Use the index for lookups and reset it again.

products = pd.read_csv("products.csv").set_index("product_id")
print(products.loc["P001", "product_name"])
print(products.loc[["P001", "P002"], ["product_name", "unit_price"]])
print("P999" in products.index, products.index.is_unique)

by_cat = products.reset_index().set_index(["category", "product_id"]).sort_index()
print(by_cat.loc["Dairy"].head(3)[["product_name", "unit_price"]])

flat = products.reset_index(drop=False)
print(flat.columns[0], flat.shape)

Banana Robusta
              product_name  unit_price
product_id                            
P001        Banana Robusta          48
P002         Tomato Hybrid          36
False True
                  product_name  unit_price
product_id                                
P009        Full Cream Milk 1L          68
P010                 Curd 400g          45
P011               Paneer 200g          95
product_id (50, 7)

How it works: set_index turns a column into the row labels, which makes loc lookups by id convenient and also aligns data when you combine tables. A multi-index uses several columns as labels, and loc["Dairy"] then selects the whole group. reset_index() moves the labels back into an ordinary column. Index values should usually be unique.

Reading the result: Product P001 is Banana Robusta. The id is unique (True), so label lookups return a single row.

Example 10: Chained assignment and copies

Question: Why do you sometimes see a "SettingWithCopy" warning, and what does pandas 3 do?

subset = orders[orders["store_id"] == "S01"]
subset["flag"] = 1
print("flag" in subset.columns, "flag" in orders.columns)

# modify the original safely, in one step
orders.loc[orders["store_id"] == "S01", "is_s01"] = True
print(orders["is_s01"].sum(), orders["is_s01"].isna().sum())

# chained assignment no longer modifies the original in pandas 3
orders[orders["store_id"] == "S02"]["marker"] = "x"
print("marker" in orders.columns)

safe_copy = orders[orders["store_id"] == "S03"].copy()
safe_copy["flag"] = 1
print(safe_copy["flag"].sum() == len(safe_copy))

True False
682 5318
False
True

How it works: In pandas 3 (with copy-on-write), a filtered result behaves as an independent object: changing subset never changes orders, and orders[mask]["col"] = value is chained assignment, which has no effect on the original. To modify the original rows, use a single loc call: df.loc[mask, "col"] = value. When you want to work on a subset separately, call .copy() to make your intention explicit.

Reading the result: The flag went onto the subset only (False for the original). The loc assignment marked 682 rows of store S01 in the original table, and the chained assignment created no marker column at all (False).

Common mistakes

1. Using and/or instead of &/|.

orders[(orders["delivery_minutes"] > 40) and (orders["store_id"] == "S01")]

Traceback (most recent call last):
  File "example.py", line 1, in <module>
    orders[(orders["delivery_minutes"] > 40) and (orders["store_id"] == "S01")]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "example.py", line 1501, in __bool__
    raise ValueError(
    ...<2 lines>...
    )
ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

2. Forgetting brackets around each condition.

3. Chained assignment: df[mask]["col"] = x (use df.loc[mask, "col"] = x).

4. Testing equality with NaN: use isna().

5. Confusing loc (inclusive label slices) with iloc (exclusive position slices).

Practice questions

Practice 1 (Easy): Prime customers

From customers, select the Prime customers living in Mumbai and show how many there are.

prime_mumbai = customers[customers["is_prime"] & (customers["city"] == "Mumbai")]
print(len(prime_mumbai))

33

Practice 2 (Medium): Big discounts on fast deliveries

How many orders were delivered in under 15 minutes and had a discount of at least 10 percent? What share of all delivered orders is that?

mask = (orders["delivery_minutes"] < 15) & (orders["discount_pct"] >= 10)
n = mask.sum()
share = n / (orders["order_status"] == "Delivered").sum()
print(n, f"{share:.1%}")

211 3.9%

Practice 3 (Hard): Slowest 5% per query

Using quantile, keep only the deliveries slower than the 95th percentile, then report how many stores they come from and which store appears most often.

cutoff = orders["delivery_minutes"].quantile(0.95)
tail = orders[orders["delivery_minutes"] > cutoff]
print(cutoff, len(tail))
print(tail["store_id"].nunique())
print(tail["store_id"].value_counts().head(3))

35.0 280
11
store_id
S07    45
S06    33
S01    32
Name: count, dtype: int64

Key takeaways

• A condition produces a boolean Series; df[mask] keeps the True rows.
• Combine masks with &, |, ~ and wrap each condition in brackets.
• loc is label-based (inclusive), iloc is position-based (exclusive).
• isin, between, isna and the .str methods build masks quickly; query is the readable alternative.
• Create conditional columns with loc, np.where and np.select.
• Modify the original in one step with df.loc[mask, "col"] = value; avoid chained assignment.