Introduction

When filtering a pandas DataFrame with multiple conditions, unexpected behavior almost always comes from using Python's and/or keywords instead of bitwise operators &/|, or from forgetting parentheses around individual conditions. Python's and and or evaluate the truth value of entire arrays, which is ambiguous for arrays with multiple elements, triggering the infamous ValueError: The truth value of a Series is ambiguous. The correct approach uses & (and), | (or), and ~ (not) with parentheses around each condition.

The Problem: and/or vs &/|

1import pandas as pd
2
3df = pd.DataFrame({
4    'name': ['Alice', 'Bob', 'Charlie', 'Diana'],
5    'age': [25, 30, 35, 28],
6    'salary': [50000, 60000, 70000, 55000],
7})
8
9# WRONG: Python's "and" tries to evaluate the entire Series as a single bool
10# df[df['age'] > 25 and df['salary'] > 55000]
11# ValueError: The truth value of a Series is ambiguous.
12
13# CORRECT: Use & with parentheses
14result = df[(df['age'] > 25) & (df['salary'] > 55000)]
15print(result)
16#       name  age  salary
17# 1      Bob   30   60000
18# 2  Charlie   35   70000

Python's and calls bool() on each operand. A pandas Series with multiple elements cannot be reduced to a single True/False, so Python raises the error. The & operator performs element-wise AND on the boolean Series.

Operator Precedence: Why Parentheses Matter

1# WITHOUT parentheses — wrong result or error
2# df[df['age'] > 25 & df['salary'] > 55000]
3# This is parsed as: df['age'] > (25 & df['salary']) > 55000
4# Because & has higher precedence than >
5
6# WITH parentheses — correct
7result = df[(df['age'] > 25) & (df['salary'] > 55000)]
8
9# More examples
10# OR condition
11young_or_rich = df[(df['age'] < 30) | (df['salary'] > 60000)]
12
13# NOT condition
14not_alice = df[~(df['name'] == 'Alice')]
15
16# Combined
17complex_filter = df[
18    ((df['age'] > 25) & (df['salary'] > 50000)) |
19    (df['name'] == 'Alice')
20]

& has higher operator precedence than >, <, == in Python. Without parentheses, df['age'] > 25 & df['salary'] is parsed as df['age'] > (25 & df['salary']), producing wrong results.

Using .query() for Cleaner Syntax

1# .query() uses a string expression — more readable
2result = df.query('age > 25 and salary > 55000')
3
4# Variables can be referenced with @
5min_age = 25
6min_salary = 55000
7result = df.query('age > @min_age and salary > @min_salary')
8
9# Complex conditions
10result = df.query('(age > 25 and salary > 55000) or name == "Alice"')
11
12# String methods require backticks for column names with spaces
13# df.query('`first name` == "Alice"')

df.query() uses and/or/not (the readable Python keywords) instead of &/|/~. It is often cleaner for complex conditions and avoids the parentheses issue entirely.

Using .loc with Multiple Conditions

1# .loc with boolean mask
2mask = (df['age'] > 25) & (df['salary'] > 55000)
3result = df.loc[mask]
4
5# .loc with conditions and column selection
6result = df.loc[mask, ['name', 'salary']]
7print(result)
8#       name  salary
9# 1      Bob   60000
10# 2  Charlie   70000
11
12# Modify values matching the condition
13df.loc[mask, 'salary'] = df.loc[mask, 'salary'] * 1.1  # 10% raise

.loc with a boolean mask is the most explicit approach. Separating the mask creation from the indexing makes complex logic easier to read and debug.

Multiple Conditions with .isin() and .between()

1# isin: check membership in a list
2target_names = ['Alice', 'Charlie']
3result = df[df['name'].isin(target_names)]
4
5# between: range check (inclusive by default)
6result = df[df['age'].between(25, 30)]
7# Equivalent to: (df['age'] >= 25) & (df['age'] <= 30)
8
9# Combine isin with other conditions
10result = df[df['name'].isin(target_names) & (df['salary'] > 50000)]
11
12# Negation with ~
13result = df[~df['name'].isin(['Alice', 'Bob'])]

Filtering with np.where and np.select

1import numpy as np
2
3# np.where for conditional column creation
4df['category'] = np.where(
5    (df['age'] > 30) & (df['salary'] > 60000),
6    'Senior High Earner',
7    'Other'
8)
9
10# np.select for multiple conditions
11conditions = [
12    (df['salary'] > 65000),
13    (df['salary'] > 55000),
14    (df['salary'] <= 55000),
15]
16choices = ['High', 'Medium', 'Low']
17df['salary_tier'] = np.select(conditions, choices, default='Unknown')
18print(df)

Common Pitfalls

• Using and/or instead of &/|: Python's and and or evaluate the truth value of the entire Series, which is ambiguous for multi-element arrays. Always use & for AND, | for OR, and ~ for NOT when filtering DataFrames with boolean indexing.
• Forgetting parentheses around conditions: df[df['a'] > 1 & df['b'] < 5] is parsed as df[df['a'] > (1 & df['b']) < 5] due to & having higher precedence than comparison operators. Always wrap each condition in parentheses: df[(df['a'] > 1) & (df['b'] < 5)].
• Chaining comparisons like pure Python: df[25 < df['age'] < 35] does not work as expected with pandas Series because Python's chained comparison uses and internally. Write it as df[(df['age'] > 25) & (df['age'] < 35)] or use df[df['age'].between(26, 34)].
• Modifying a filtered DataFrame without .loc: df[df['age'] > 30]['salary'] = 0 triggers a SettingWithCopyWarning and may not modify the original DataFrame. Use df.loc[df['age'] > 30, 'salary'] = 0 for safe in-place modification.
• Using == with None instead of .isna(): df[df['column'] == None] does not reliably detect NaN values. Use df[df['column'].isna()] for null checks and df[df['column'].notna()] for non-null filtering.

Summary

• Use &, |, ~ (not and, or, not) for combining conditions in pandas boolean indexing
• Always wrap each condition in parentheses due to operator precedence
• Use df.query() for readable multi-condition filtering with Python keywords
• Use .loc[mask] for explicit boolean mask indexing and safe column assignment
• Use .isin() for membership checks and .between() for range filtering
• Use .isna() instead of == None for null value detection