python pandas filter rows with multiple conditions and query

python pandas filter rows with multiple conditions and query requires a clear understanding of the core syntax, runtime behavior, and practical implementation patterns demonstrated in the examples below.

Filtering rows in a pandas DataFrame is a daily task for data engineers and analysts. When you need to apply multiple conditions at once, the choice between boolean indexing and the query method affects readability, maintainability, and sometimes performance. This article covers both approaches for filtering rows with multiple conditions, explains how they behave, and gives concrete guidance on when each is the the better fit.

n## Boolean Indexing with Multiple Conditions

The most direct way to filter rows is to build a boolean mask using comparison operators and combine conditions with & (and) and | (or). Each condition must be wrapped in parentheses because pandas overrides the Python bitwise operators to work element-wise on Series.

import pandas as pd

df = pd.DataFrame({
    'product': ['A', 'B', 'C', 'D'],
    'price': [10, 25, 15, 30],
    'stock': [100, 50, 200, 10]
})

# Rows where price > 15 and stock < 100
filtered = df[(df['price'] >  15) & (df['stock'] < 100)]

The parentheses are mandatory. Without them, Python evaluates the expression incorrectly because & has a higher precedence than > in the language grammar. The resulting mask is a boolean Series aligned with the DataFrame index, and df[mask] returns only rows where the mask is True.

For more than two conditions, chain them with & or | as needed. For example, to select rows where price is between 10 and 30 and stock is positive:

filtered = df[(df['price'] >= 10) & (df['price'] <= 30) & (df['stock'] > 0)]

This approach is explicit and uses standard pandas syntax. It works in every pandas version and does not rely on optional dependencies.

Using the query Method for Multiple Conditions

The query method accepts a string expression and evaluates it against the DataFrame's columns. It often produces more readable code when conditions are numerous.

n```python filtered = df.query('price > 15 and stock < 100')


You can use `and`/`or` or `&`/`|` inside the query string. The method also supports referencing Python variables by prefixing them with `@`:

```python
min_price = 15
max_price = 30
filtered = df.query('@min_price <= price <= @max_price')

Query expressions support column names with spaces if you wrap them in backticks, and they can include arithmetic, function calls, in operators. The string is parsed and evaluated without needing to create intermediate boolean Series, which can make the the code cleaner for complex filters.

Combining Conditions with Logical Operators

Both boolean indexing and query respect logical precedence, but the rules differ slightly. In In boolean indexing, you explicitly group conditions with parentheses. In query, the string follows Python's precedence rules: and binds tighter than or, and comparisons have lower precedence than logical operators. For clarity, use parentheses in query strings when mixing and and or.

# Correct: parentheses make the intent clear
filtered = df.query('(price > 15 or stock < 50) and (price < 30)')

Without parentheses, or and and can produce unexpected results because and is evaluated first. This is a common source of bugs when moving from boolean indexing to query.

Performance and Memory Considerations

Boolean indexing creates a temporary boolean Series for each condition and then combines them with &/|. This involves allocating memory for each mask, which can be significant for large DataFrames. The query method, when the numexpr library is installed, evaluates the expression in a vectorized manner without materializing intermediate masks. This can reduce memory usage and sometimes speed up filtering on very large datasets.

However, the performance difference is not guaranteed. If numexpr is not installed, query falls back to Python's eval, which may be slower than boolean indexing. The actual impact depends on DataFrame size, the complexity of the expression, and the hardware. In practice, for DataFrames up to a few hundred thousand rows, both approaches are fast enough; for multi-million-row datasets, query with numexpr can be measurably faster, but you should verify with your own data.

Memory usage is more predictable with boolean indexing because you can see exactly what masks are created. query hides that detail, which can be an advantage or a disadvantage depending on your need for transparency.

Common Pitfalls and Edge Cases

Several issues arise when filtering with multiple conditions.

Missing values (NaN) are treated as False in comparisons. A condition like df['price'] > 15 will exclude rows where the price is NaN, which is usually desired but can surprise you if you expect NaN to pass. Use isna() or notna() explicitly if you need to handle missing values.

Chained indexing can occur when you filter and then assign or modify the result. For example, df[df['price'] > 15]['stock'] = 0 may trigger a SettingWithCopyWarning. Instead, use .loc with the mask:

df.loc[df['price'] > 15, 'stock'] = 0

String comparisons are case-sensitive by default. If you need case-insensitive matching, use str.lower() or str.contains with case=False.

Using isin with multiple conditions is often clearer than chaining == with |:

filtered = df[df['product'].isin(['A', 'C'])]

This avoids the parentheses-heavy syntax and is more readable.

Choosing Between Boolean Indexing and query

Decide based on the specific situation.

Use boolean indexing when you need to integrate with other pandas operations, such as .loc or .groupby, and when you want full control over each condition. It is also the default choice when you need to apply conditions that are not easily expressed as strings, such using a function call on a column.
Use query when you have many conditions and want the code to read like a natural language sentence. It is also convenient when building dynamic queries from user input, because you can construct the string programmatically. However, be careful with injection-like risks if the query string includes user-supplied values; always use @ variables instead of concatenating values directly.

For a one-off script, either approach works. For code that will be maintained over time, query often reduces visual noise, while boolean indexing is more explicit and easier to debug if a condition fails.

Working with Dynamic Conditions

When the number or type of conditions changes at runtime, you can build a query string dynamically. For example, if you have a list of product names to filter:

products = ['A', 'C']
query_str = 'product in @products'
filtered = df.query(query_str)

Using @ passes the list as a variable, and pandas expands it correctly. You can also combine dynamic conditions with and and or by joining strings, but you must ensure parentheses are placed correctly. A safer approach is to use boolean indexing with a list comprehension:

mask = df['product'].isin(products)
if min_price is not None:
    mask &= df['price'] >= min_price
filtered = df[mask]

This keeps the logic in Python, making it easier to test and debug. Dynamic query strings can become brittle if the column names or values contain special characters; using boolean indexing avoids parsing issues.

Ultimately, the choice between boolean indexing and query is about clarity and control. Both methods are valid for filtering rows with multiple conditions, and understanding their behavior lets you pick the right one for each task.