query() for fast filtering in Pandas - Step-by-Step Execution

Concept Flow - query() for fast filtering

Start with DataFrame

↓

Write query string

↓

Apply df.query(query_string)

↓

Filter rows matching condition

↓

Return filtered DataFrame

The flow shows how a DataFrame is filtered by writing a query string and applying df.query() to get matching rows.

Execution Sample

Pandas

import pandas as pd

df = pd.DataFrame({
    'age': [25, 30, 35, 40],
    'city': ['NY', 'NY', 'NY', 'SF']
})

filtered = df.query('age > 30 and city == "NY"')

This code filters rows where age is greater than 30 and city is NY using df.query().

Execution Table

Step	Action	Query Condition	Rows Checked	Rows Matching	Resulting DataFrame
1	Start with full DataFrame	age > 30 and city == 'NY'	All rows (4)	N/A	[All 4 rows]
2	Check row 0	age=25, city=NY	Row 0	False (25 > 30? No)	Exclude row 0
3	Check row 1	age=30, city=NY	Row 1	False (30 > 30? No)	Exclude row 1
4	Check row 2	age=35, city=NY	Row 2	True (35 > 30 and city NY)	Include row 2
5	Check row 3	age=40, city=SF	Row 3	False (city SF != NY)	Exclude row 3
6	Return filtered DataFrame	N/A	N/A	Rows matching: 1	DataFrame with row 2 only

💡 All rows checked; only row 2 matches the query condition.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
filtered	Empty	Empty	Empty	Contains row 2	Contains row 2	Contains row 2

Key Moments - 2 Insights

Why does df.query('age > 30 and city == "NY"') exclude rows where age is exactly 30?

Because the condition uses '>' which means strictly greater than 30. Rows with age 30 do not satisfy 'age > 30' as shown in execution_table row 3.

How does df.query handle string comparisons like city == 'NY'?

It compares the string values exactly. Only rows where city equals 'NY' are included, as seen in execution_table row 4 where city is 'NY' and row 5 where city is 'SF' is excluded.

Visual Quiz - 3 Questions

Look at the execution table, which row is included in the filtered DataFrame?

A Row 0

B Row 1

C Row 2

D Row 3

💡 Hint

Check the 'Rows Matching' column in execution_table rows 2-5.

At which step does the query condition become false for row 1?

A Step 3

B Step 4

C Step 2

D Step 5

💡 Hint

Look at the 'Rows Checked' and 'Rows Matching' columns for row 1 in execution_table.

If the query was changed to 'age >= 30 and city == "NY"', which additional row would be included?

A Row 0

B Row 1

C Row 3

D Row 1 and Row 3

💡 Hint

Check which rows have age exactly 30 and city 'NY' in variable_tracker and execution_table.

Concept Snapshot

df.query('condition') filters DataFrame rows fast.
Use a string condition like 'age > 30 and city == "NY"'.
It returns rows where condition is True.
Supports logical operators: and, or, not.
Works with column names directly.
Faster and cleaner than boolean indexing.

Full Transcript

This visual execution shows how pandas df.query() filters rows by a condition string. Starting with a DataFrame of ages and cities, the query 'age > 30 and city == "NY"' is applied. Each row is checked: rows 0 and 1 fail because age is not greater than 30, row 3 fails because city is not NY. Only row 2 matches both conditions and is included in the filtered DataFrame. Variables track the filtered result building up. Key moments clarify why exact comparisons matter and how strings are matched. The quiz tests understanding of which rows pass or fail and how changing the condition affects results. The snapshot summarizes usage: df.query() is a clean, fast way to filter DataFrames using readable string conditions.

Practice

1. What is the main purpose of the query() method in pandas?

easy

A. To filter rows of a DataFrame using a readable string expression

B. To sort the DataFrame by column values

C. To merge two DataFrames based on a key column

D. To create a new DataFrame from scratch

Solution

1. Step 1: Understand the purpose of query()

   The query() method is designed to filter rows in a DataFrame by using a string expression that specifies conditions on columns.

2. Step 2: Compare with other DataFrame methods

   Sorting is done by sort_values(), merging by merge(), and creating new DataFrames is done by constructors, not query().

3. Final Answer:

   To filter rows of a DataFrame using a readable string expression -> Option A

4. Quick Check:

   query() filters rows [OK]

Hint: Remember: query() filters rows with string conditions [OK]

Common Mistakes:

• Confusing query() with sorting or merging methods
• Thinking query() creates new DataFrames
• Assuming query() modifies columns

2. Which of the following is the correct syntax to filter rows where column age is greater than 30 using query()?

easy

A. df.query('age > "30"')

B. df.query(age > 30)

C. df.query('age > 30')

D. df.query('age => 30')

Solution

1. Step 1: Check the correct string format for query()

   The condition must be passed as a string, so it should be inside quotes: 'age > 30'.

2. Step 2: Validate the comparison operator and quotes

   Operator must be >, not =>. Also, 30 should be a number, not a string with quotes.

3. Final Answer:

   df.query('age > 30') -> Option C

4. Quick Check:

   Use string with correct operator [OK]

Hint: Put condition inside quotes with correct operators [OK]

Common Mistakes:

• Omitting quotes around the condition string
• Using wrong comparison operator => instead of >
• Putting numbers inside quotes making them strings

3. Given the DataFrame:

import pandas as pd
df = pd.DataFrame({'name': ['Anna', 'Bob', 'Cara'], 'score': [85, 92, 78]})

What is the output of df.query('score >= 80')?

medium

A. Rows with names Anna and Bob only

B. Rows with names Bob and Cara only

C. Rows with names Anna and Cara only

D. All rows

Solution

1. Step 1: Identify rows where score >= 80

   Scores are Anna:85, Bob:92, Cara:78. Only Anna and Bob have scores 80 or above.

2. Step 2: Understand query() filters these rows

   The query returns rows where the condition is true, so rows for Anna and Bob are returned.

3. Final Answer:

   Rows with names Anna and Bob only -> Option A

4. Quick Check:

   score >= 80 filters Anna and Bob [OK]

Hint: Check numeric condition matches rows carefully [OK]

Common Mistakes:

• Including rows that don't meet the condition
• Confusing >= with > or <= operators
• Assuming query returns all rows

4. What is wrong with this code snippet?

df.query('age > 25 and city == New York')

medium

A. The column names must be in quotes

B. The operator 'and' is not allowed in query()

C. The query() method cannot filter by multiple conditions

D. The string New York should be quoted inside the query string

Solution

1. Step 1: Check string literals inside query()

   String values like city names must be quoted inside the query string, e.g., city == "New York".

2. Step 2: Verify use of 'and' and column names

   The 'and' operator is allowed, and column names do not need quotes inside the query string.

3. Final Answer:

   The string New York should be quoted inside the query string -> Option D

4. Quick Check:

   Quote string values inside query() [OK]

Hint: Quote string values inside query() conditions [OK]

Common Mistakes:

• Not quoting string literals in conditions
• Thinking 'and' is invalid in query()
• Quoting column names unnecessarily

5. You have a DataFrame df with columns temperature and humidity. You want to filter rows where temperature is above 20 and humidity is below 50. Which query() call correctly does this?

hard

A. df.query('temperature > 20 or humidity < 50')

B. df.query('temperature > 20 and humidity < 50')

C. df.query('temperature > 20 & humidity < 50')

D. df.query('temperature > 20 && humidity < 50')

Solution

1. Step 1: Understand logical operators in query()

   Within query strings, use and and or for combining conditions, not & or &&.

2. Step 2: Match the condition requirements

   The problem wants temperature > 20 and humidity < 50, so the correct operator is and.

3. Final Answer:

   df.query('temperature > 20 and humidity < 50') -> Option B

4. Quick Check:

   Use 'and' for multiple conditions in query() [OK]

Hint: Use 'and' not '&' to combine conditions in query() [OK]

Common Mistakes:

• Using bitwise operators (&, &&) instead of 'and'
• Using 'or' instead of 'and' when both conditions needed
• Mixing Python operators outside query() syntax