Filtering rows with pandas DataFrame.query()

Useful answer

Use DataFrame.query() to filter rows with a readable string expression instead of writing explicit boolean masks. The method returns a new DataFrame that matches the condition; the original DataFrame remains unchanged unless you assign the result back.

How query() works

When you call query(), pandas parses the supplied string with the numexpr engine (falling back to Python parsing for unsupported expressions). The string can reference column names directly; local variables must be prefixed with @, and column names that contain spaces or special characters need backticks.

Example

import pandas as pd

# Sample data
df = pd.DataFrame({
    'age': [25, 35, 45, 28],
    'city': ['Boston', 'New York', 'Boston', 'New York'],
    'salary': [70000, 80000, 120000, 72000]
})

# Filter rows where age > 30 and city is New York
filtered = df.query('age > 30 and city == "New York"')

# Verify equivalence with manual boolean mask
assert filtered.equals(df[(df['age'] > 30) & (df['city'] == 'New York')])

# filtered now contains the two matching rows
print(filtered)

The example shows a clear, single‑line condition that produces the same result as the traditional df[(df['age']>30) & (df['city']=='New York')] approach.

Limitations and common mistakes

• Expression limits: query() relies on numexpr for speed, so arbitrary Python functions (e.g., lambda, np.where) cannot be used directly in the string. For such cases fall back to boolean indexing.
• Local variables: If you need to filter against a variable defined outside the DataFrame, prefix it with @. Forgetting the prefix raises a UndefinedVariableError.
• Column names with spaces or special characters: Enclose them in backticks, e.g., df.query('`annual salary` > 50000'). Using the raw name causes a SyntaxError.
• Assignment: query() does not modify the original DataFrame. Neglecting to assign the result (df.query(...)) leaves df unchanged, a frequent source of confusion.
• Copy vs. view: The returned object is a copy; modifying it does not affect the source unless you explicitly use .copy() or assign back (df = df.query(...)).

To verify your query works as intended, compare the output with the equivalent manual boolean mask in a fresh Python session (pandas ≥1.0). If the two DataFrames are equal, the query expression is correct.