Filtering and Selecting Rows

Boolean Indexing

Boolean indexing is the most common filtering technique in pandas. You create a boolean Series (True/False values) and pass it to the DataFrame to select only the rows where the condition is True.

How It Works

When you compare a DataFrame column to a value, pandas returns a boolean Series — True where the condition holds, False elsewhere:

import pandas as pd

df = pd.DataFrame({
    "product": ["A", "B", "C", "D", "E"],
    "category": ["Food", "Tech", "Food", "Tech", "Food"],
    "price": [10.99, 299.99, 5.49, 149.99, 7.99],
    "quantity": [50, 10, 100, 25, 80]
})

# Step 1: Create the boolean mask
mask = df["category"] == "Food"
print(mask)
# 0     True
# 1    False
# 2     True
# 3    False
# 4     True
# Name: category, dtype: bool

# Step 2: Apply the mask to filter rows
food_products = df[mask]
print(food_products)
#   product category  price  quantity
# 0       A     Food  10.99        50
# 2       C     Food   5.49       100
# 4       E     Food   7.99        80

Comparison Operators

df["price"] > 100       # greater than
df["price"] <= 50       # less than or equal
df["category"] == "Tech"  # equal to
df["category"] != "Food"  # not equal
df["price"].between(10, 100)  # range (inclusive)

Each comparison produces a boolean Series. The mask selects rows where the value is True.

Combining Conditions

Use & (and), | (or), and ~ (not) to combine multiple conditions. Each condition must be wrapped in parentheses:

# AND: Food products with price below 10
mask = (df["category"] == "Food") & (df["price"] < 10)
result = df[mask]
#   product category  price  quantity
# 2       C     Food   5.49       100
# 4       E     Food   7.99        80

# OR: Tech products OR items with quantity above 90
mask = (df["category"] == "Tech") | (df["quantity"] > 90)
result = df[mask]
#   product category   price  quantity
# 1       B     Tech  299.99        10
# 2       C     Food    5.49       100

# NOT: Everything except Tech
mask = ~(df["category"] == "Tech")
result = df[mask]

The parentheses are mandatory. Without them, Python's operator precedence causes & to bind before ==, producing incorrect results or errors.

Why Boolean Indexing

Boolean indexing is explicit and flexible. You can build complex conditions step by step, inspect intermediate masks, and reuse masks across multiple operations. It is the foundation that other selection methods build on.

Common Pitfalls

1. Forgetting parentheses: df["a"] > 1 & df["b"] < 2 fails. Write (df["a"] > 1) & (df["b"] < 2).
2. Using and/or instead of &/|: Python's and and or work on single boolean values, not pandas Series. Use & and | for element-wise operations.
3. Chaining filters: df[df["a"] > 1][df["b"] < 2] works but is inefficient — it creates an intermediate DataFrame. Combine into one mask instead.

Reusing Masks

Store masks in variables when you need to apply the same filter multiple times:

high_value = df["price"] > 100
in_stock = df["quantity"] > 0

# Apply separately
expensive = df[high_value]
available = df[in_stock]

# Combine
available_expensive = df[high_value & in_stock]

This makes your code self-documenting. The mask name describes the intent, and you can reuse it without rewriting the condition.

Selecting with .loc

.loc selects rows and columns by their labels. It is the primary method for label-based selection in pandas.

Selecting Rows by Label

import pandas as pd

df = pd.DataFrame({
    "product": ["A", "B", "C"],
    "price": [10.99, 299.99, 5.49],
    "quantity": [50, 10, 100]
}, index=["r1", "r2", "r3"])

# Select a single row by label
row = df.loc["r1"]
# product       A
# price      10.99
# quantity      50
# Name: r1, dtype: object

# Select multiple rows by label
subset = df.loc[["r1", "r3"]]
#    product  price  quantity
# r1       A  10.99        50
# r3       C   5.49       100

# Slice by label (inclusive on both ends)
subset = df.loc["r1":"r2"]
#    product  price  quantity
# r1       A  10.99        50
# r2       B  299.99       10

Selecting Rows and Columns Together

.loc accepts two arguments: row selector and column selector, separated by a comma.

# Specific rows, specific columns
result = df.loc[["r1", "r2"], ["product", "price"]]
#    product  price
# r1       A  10.99
# r2       B  299.99

# All rows, specific columns
result = df.loc[:, ["product", "quantity"]]
#    product  quantity
# r1       A        50
# r2       B        10
# r3       C       100

# Slice rows, slice columns
result = df.loc["r1":"r2", "product":"price"]
#    product  price
# r1       A  10.99
# r2       B  299.99

Boolean Mask with .loc

You can combine .loc with boolean masks for powerful filtering:

# Rows where price > 100, only product and quantity columns
result = df.loc[df["price"] > 100, ["product", "quantity"]]
#    product  quantity
# r2       B        10

# Multiple conditions
mask = (df["price"] > 5) & (df["quantity"] >= 50)
result = df.loc[mask, ["product", "price"]]
#    product  price
# r1       A  10.99
# r3       C   5.49

Why .loc Over Boolean Indexing Alone

Boolean indexing (df[mask]) selects rows but returns all columns. .loc lets you select rows and columns in a single operation, making your intent clear and your code concise.

Setting Values with .loc

.loc is also for assignment:

# Update a single value
df.loc["r1", "price"] = 12.99

# Update multiple values
df.loc[["r1", "r2"], "quantity"] = [60, 15]

# Update based on condition
mask = df["price"] < 10
df.loc[mask, "quantity"] = 0

Key Rule

.loc is label-based. If your DataFrame has a default integer index (0, 1, 2, ...), .loc[0] selects the row labeled 0, not the first row by position. If you need position-based selection, use .iloc.

When to Use .loc

Use .loc when your DataFrame has meaningful row labels — dates, product IDs, customer names. Label-based selection makes code self-documenting: df.loc["2024-01-15"] is clearer than df.iloc[0] when the first row might change order.

Selecting with Callable

Both .loc and .iloc accept callable (function) selectors. The function receives the index (for .loc) or the DataFrame (for .iloc) and returns a boolean mask:

# Select rows where the index label starts with '2024'
df.loc[lambda x: x.index.str.startswith('2024')]

# Select columns where the mean is above 100
df.loc[:, lambda x: x.mean() > 100]

Callables are useful for dynamic selection logic that would be verbose to write inline. They are evaluated once at selection time, so the function runs on the current state of the DataFrame.

Selecting with .iloc

.iloc selects rows and columns by their integer position (0-based). It works like Python list indexing but for DataFrames.

Selecting by Position

import pandas as pd

df = pd.DataFrame({
    "product": ["A", "B", "C", "D"],
    "price": [10.99, 299.99, 5.49, 149.99],
    "quantity": [50, 10, 100, 25]
})

# Select the first row (position 0)
row = df.iloc[0]
# product        A
# price       10.99
# quantity       50
# Name: 0, dtype: object

# Select rows 0 and 2
subset = df.iloc[[0, 2]]
#   product  price  quantity
# 0       A  10.99        50
# 2       C   5.49       100

# Slice rows (start inclusive, end exclusive — like Python lists)
subset = df.iloc[1:3]
#   product  price  quantity
# 1       B  299.99       10
# 2       C   5.49       100

Selecting Rows and Columns by Position

# Rows 0-1, columns 0-2
result = df.iloc[0:2, 0:2]
#   product  price
# 0       A  10.99
# 1       B  299.99

# First two rows, last column
result = df.iloc[:2, -1]
# 0    50
# 1    10
# Name: quantity, dtype: int64

# All rows, columns at positions 0 and 2
result = df.iloc[:, [0, 2]]
#   product  quantity
# 0       A        50
# 1       B        10
# 2       C       100
# 3       D        25

.loc vs .iloc

# With default integer index, these are equivalent:
df.loc[0]    # row labeled 0
df.iloc[0]   # first row by position

# But they diverge if the index is not 0, 1, 2, ...
df2 = df.set_index("product")
df2.loc["A"]    # works — label "A" exists
df2.iloc[0]     # works — first row regardless of label
df2.loc[0]      # KeyError — no label 0

When to Use .iloc

Use .iloc when position matters more than labels:

1. Extracting the first or last N rows: df.iloc[:10] or df.iloc[-5:].
2. Selecting columns by position when names are unknown: df.iloc[:, 0].
3. Working with default integer indices: .iloc is unambiguous.
4. Programmatic selection: When positions come from a variable or calculation.

Negative Indexing

.iloc supports negative indices, just like Python lists:

df.iloc[-1]      # last row
df.iloc[-3:]     # last 3 rows
df.iloc[:, -1]   # last column

Common Mistake

Using .loc with positions when the index is not 0-based integers. If you set a string index and then call .loc[0], you get a KeyError. Use .iloc[0] for position-based access regardless of the index type.

Practical Example: Train/Test Split

When splitting data for machine learning, you often select by position:

# First 80% for training, last 20% for testing
split = int(len(df) * 0.8)
train = df.iloc[:split]
test = df.iloc[split:]

Position-based selection is the natural choice here because the split point is a number, not a label.

Filtering with .query()

.query() filters DataFrame rows using a string expression. It produces cleaner syntax for complex conditions and is faster for large DataFrames.

Basic Syntax

import pandas as pd

df = pd.DataFrame({
    "product": ["A", "B", "C", "D", "E"],
    "category": ["Food", "Tech", "Food", "Tech", "Food"],
    "price": [10.99, 299.99, 5.49, 149.99, 7.99],
    "quantity": [50, 10, 100, 25, 80]
})

# Simple condition
result = df.query("price > 100")
#   product category   price  quantity
# 1       B     Tech  299.99        10
# 3       D     Tech  149.99        25

# String comparison
result = df.query('category == "Food"')
#   product category  price  quantity
# 0       A     Food  10.99        50
# 2       C     Food   5.49       100
# 4       E     Food   7.99        80

Combining Conditions

# AND
result = df.query('category == "Food" and price < 10')
#   product category  price  quantity
# 2       C     Food   5.49       100
# 4       E     Food   7.99        80

# OR
result = df.query('category == "Tech" or quantity > 90')
#   product category   price  quantity
# 1       B     Tech  299.99        10
# 2       C     Food    5.49       100

# NOT
result = df.query('not category == "Tech"')
#   product category  price  quantity
# 0       A     Food  10.99        50
# 2       C     Food   5.49       100
# 4       E     Food   7.99        80

Using Variables in Queries

Reference Python variables with @:

threshold = 100
result = df.query("price > @threshold")

cat = "Food"
result = df.query("category == @cat and price < @threshold")

The @ symbol tells pandas to look up the variable in the surrounding Python scope, not as a DataFrame column name.

Why .query() Over Boolean Indexing

1. Readability: df.query('category == "Food" and price < 10') is more readable than df[(df["category"] == "Food") & (df["price"] < 10)].
2. No parentheses: The expression is a string, so you do not need parentheses around each condition.
3. Performance: For large DataFrames, .query() can be faster because it evaluates the expression in a single pass using pandas' internal engine.
4. Familiar syntax: The expression syntax resembles SQL, which lowers the barrier for analysts coming from SQL.

When .query() Falls Short

1. Complex logic: If you need function calls or complex transformations in the condition, boolean indexing is more flexible.
2. Column names with spaces: Use backticks: df.query("column name > 5").
3. Dynamic column names: If the column name is stored in a variable, boolean indexing is easier: df[df[col_name] > 5].

Combining .query() with Column Selection

.query() filters rows. Chain it with column selection:

result = (df
    .query("category == 'Food' and price < 10")
    .loc[:, ["product", "price"]]
)
#   product  price
# 2       C   5.49
# 4       E   7.99

This pattern — filter with .query(), select with .loc — is clean and readable. Each method does one thing well.

Performance Consideration

For small DataFrames (under 100,000 rows), the performance difference between .query() and boolean indexing is negligible. For large DataFrames, .query() can be 10-20% faster because it avoids creating intermediate boolean arrays. However, readability should be your primary concern — choose the method that makes your code clearest.