Select Columns with Query and Eval

You’ve been filtering DataFrames with boolean masks and loc[], but your code is starting to look like a tangled mess of brackets and ampersands. Compare, df[(df['age'] > 30) & (df['salary'] > 70000)] — it works, but it’s hard to read, easy to get wrong, and a pain to maintain. In this lesson, you’ll learn how query() and eval() let you select rows and columns with plain, expressive expressions — faster to write, faster to run, and far easier to debug.

The problem this lesson solves

When you need to filter a DataFrame based on column values, the obvious approach is boolean indexing: df[df['age'] > 30]. But as soon as you combine two or more conditions, the syntax gets ugly fast:

import pandas as pd

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie', 'Diana'],
    'age': [25, 32, 37, 29],
    'salary': [50000, 75000, 80000, 67000]
})

# Traditional boolean masking – works but hard to read
selected = df[(df['age'] > 30) & (df['salary'] > 70000)]
print(selected)

Output:

      name  age  salary
1      Bob   32   75000
2  Charlie   37   80000

The problem compounds with more conditions, | for OR, and ~ for NOT. Plus, every time you reference a column, you repeat df['name'] — verbose and error-prone. The pandas query() method fixes this by letting you write the condition as a string expression that reads like English: df.query('age > 30 and salary > 70000').

But query() isn’t just about readability. It also does not require you to copy the DataFrame for each filter — it can work on the original object in place (with inplace=True) and, crucially, eval() can compute new columns or filter using expressions without creating intermediate Series. In large datasets, this saves memory and time.

Core concept / mental model

Think of query() as a SQL WHERE clause for pandas — you write a string that describes the rows you want, and pandas evaluates it against the DataFrame. eval() is its sibling: it evaluates expressions that can create new columns or perform arithmetic, without pulling data out into Python first.

Mental model: - query() — filter rows using a string condition. - eval() — compute new columns or values using a string expression.

Both rely on the pandas expression engine, which parses the string and executes it directly on the underlying data structures — usually with significantly better performance than pure Python loops or repeated column references.

Column selection nuance: While query() and eval() are primarily about row filtering and column creation, they indirectly let you select columns of interest. For example, df.query('age > 30')[['name', 'salary']] literally selects columns after filtering. This lesson focuses on using query() and eval() for this dual purpose — fast, readable selection and transformation.

Pro tip: If you come from SQL, df.query() feels like WHERE; df.eval() feels like SELECT with arithmetic on columns. That mental shift makes the syntax intuitive.

How it works step by step

Follow these steps to harness query() and eval() effectively:

1. Start with a clean DataFrame. Ensure column names are valid Python identifiers (no spaces, no special characters) — otherwise you’ll need backticks around them in the expression.
2. Use query() for row filtering. Pass the condition as a string. Use and, or, not (or &, |, ~) for chaining. Refer to column names directly — no df['col'] needed.
3. Combine query() with column selection. After filtering, add a list of columns to select: df.query('age > 30')[['name', 'salary']].
4. Use eval() for new columns. Assign computed values to a new column: df.eval('bonus = salary * 0.1').
5. Chain multiple operations. You can chain query() and eval() together: df.query('age > 25').eval('tax = salary * 0.2').
6. Prefer local variable support. In query() and eval() strings, you can reference external Python variables by prefixing them with @ — e.g., df.query('age > @min_age'). This keeps expressions dynamic.

Syntax cheat sheet

Pro tip: query() and eval() do not modify the original DataFrame by default. Use inplace=True if you truly want to mutate — but chaining a new variable is often clearer.

Hands-on walkthrough

Let’s build a practical example. We’ll work with a sample employee dataset and use query() and eval() to select rows, create new columns, and filter dynamically.

Example 1: Basic filtering with query()

import pandas as pd

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie', 'Diana', 'Eve'],
    'age': [25, 32, 37, 29, 41],
    'salary': [50000, 75000, 80000, 67000, 90000]
})

# Select rows where age > 30 and salary > 70000
result = df.query('age > 30 and salary > 70000')
print(result)

Output:

      name  age  salary
1      Bob   32   75000
2  Charlie   37   80000
4      Eve   41   90000

Example 2: Select specific columns after filtering

# Filter rows and select only name and salary
selected_cols = df.query('age > 30')[['name', 'salary']]
print(selected_cols)

Output:

      name  salary
1      Bob   75000
2  Charlie   80000
4      Eve   90000

Example 3: Create new column with eval()

# Add a bonus column using eval
with_bonus = df.eval('bonus = salary * 0.1')
print(with_bonus)

Output:

      name  age  salary   bonus
0    Alice   25   50000  5000.0
1      Bob   32   75000  7500.0
2  Charlie   37   80000  8000.0
3    Diana   29   67000  6700.0
4      Eve   41   90000  9000.0

Example 4: Dynamic filtering with local variables

min_age = 30
min_salary = 70000

# Use @ to reference Python variables inside query string
filtered = df.query('age >= @min_age and salary >= @min_salary')
print(filtered)

Output:

      name  age  salary
1      Bob   32   75000
2  Charlie   37   80000
4      Eve   41   90000

Example 5: Chaining query and eval

# Filter then create a calculated column
chained = df.query('age > 25').eval('tax = salary * 0.2')
print(chained)

Output:

      name  age  salary    tax
1      Bob   32   75000  15000.0
2  Charlie   37   80000  16000.0
3    Diana   29   67000  13400.0
4      Eve   41   90000  18000.0

Compare options / when to choose what

You have multiple ways to select columns and filter rows — which should you use? Here’s a comparison:

Recommendations: - Use query() when your filter has multiple conditions or you want to reference local variables dynamically. - Use eval() when you need to add a computed column and want to avoid verbose assignment syntax. - Use traditional boolean indexing when you need complex Python logic (e.g., lambda functions or custom functions) that can’t be expressed in a string. - Use loc[] when you also need to select specific columns and want to keep everything in one place.

Pro tip: For maximum performance on large DataFrames, prefer query() over boolean indexing — it avoids creating intermediate boolean Series and can use numexpr acceleration if installed.

Troubleshooting & edge cases

Column names with spaces or special characters

query() and eval() expect valid Python identifiers. If your column has spaces, use backticks around it:

df = pd.DataFrame({'first name': ['Alice', 'Bob'], 'age': [25, 30]})
# Works with backticks
result = df.query('`first name` == "Alice"')
print(result)

Output:

  first name  age
0      Alice   25

Using & instead of and

Inside query(), you must use the word and (not &) for logical AND. Using & will raise a NotImplementedError or give unexpected results.

df.query('age > 30 & salary > 70000')  # WRONG – raises error
# Use:
df.query('age > 30 and salary > 70000')  # Correct

Referencing Python variables without @

If you forget the @ prefix, pandas will treat the variable name as a column name and raise a KeyError or produce wrong results.

min_age = 30
df.query('age > min_age')  # WRONG – column 'min_age' not found
# Correct: df.query('age > @min_age')

eval() not updating the original DataFrame

By default, eval() returns a new DataFrame and does not modify the original. Beginners often expect in-place changes:

df.eval('bonus = salary * 0.1')  # Returns a new DataFrame, df unchanged
# To modify in place:
df.eval('bonus = salary * 0.1', inplace=True)

Performance issues with large DataFrames

If query() runs slowly, check that numexpr is installed — it accelerates expression evaluation. If not, install it via pip install numexpr.

What you learned & what's next

In this lesson, you mastered select columns with query and eval:

• You understand that query() provides a readable, SQL-like syntax for row filtering.
• You can use eval() to create new columns with arithmetic expressions.
• You know how to chain both for combined filtering and transformation.
• You can reference Python variables using @ for dynamic filters.
• You’re aware of common pitfalls like column name quoting and operator misuse.

These skills are foundational for efficient data analysis — they make your code cleaner, faster, and easier to debug. As you move forward in the Data Analysis with Python track, you’ll combine these techniques with grouping, aggregation, and visualization to build powerful analytical pipelines.

Next up: In the next lesson, you’ll dive into groupby() operations — learning how to split your data into groups, apply functions, and combine results for summary statistics. query() and eval() will help you pre-filter and clean your data before aggregation, making your analysis both elegant and high-performing.

Ready to take the next step? Practice what you learned in this lesson with the exercise below, then continue to the next topic.