Pandas df.query() Function

Pandas 常用函数 Common Pandas Functions

query()is a very practical data filtering function in Pandas. It allows using SQL-like string expressions to filter data. Compared with traditional boolean indexing methods,query()its syntax is more concise and intuitive, especially suitable for handling complex filtering conditions.

In data analysis work, it is often necessary to filter data based on various conditions.query()The function writes filtering conditions in string form, just like writing SQL queries, which is very friendly to users familiar with SQL. At the same time, it also supports using Python variables and functions, making dynamic filtering possible.

Basic Syntax and Parameters

query()is a method of DataFrame, invoked via the dot operator.It accepts a string parameter containing the filtering expression.

Syntax Format

DataFrame.query(expr, inplace=False, **kwargs)

Parameter Description

Parameter	Type	Required	Description	Default Value
expr	str	Required	Filtering expression, similar to the WHERE clause in SQL.	-
inplace	bool	Optional	Whether to directly modify the original DataFrame.	False

Return Value Description

Return Value Type: Returns a new DataFrame containing the rows that satisfy the filtering conditions.
Original data is not modified: By default, the original DataFrame remains unchanged.

Examples

Through rich examples, let us fully masterquery()the usage of

Example 1: Basic Usage - Single-Condition Filtering

The simplestquery()usage is to filter using a single condition.

Example

import pandas as pd

# Create a sample DataFrame
data = {
'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace'],
'age': [18, 19, 17, 18, 20, 19, 18],
'score': [85, 92, 78, 90, 88, 95, 82],
'grade': ['A', 'A', 'B', 'A', 'B', 'A', 'B']
}
df = pd.DataFrame(data)

print("Original DataFrame:")
print(df)
print()

# Filter students with score greater than 85
print("Students with score greater than 85:")
print(df.query('score > 85'))
print()

# Filter students whose age equals 18
print("Students whose age equals 18:")
print(df.query('age == 18'))
print()

# Filter students with grade A
print("Students with grade A:")
print(df.query('grade == "A"'))

Running result:

原始 DataFrame:
      name  age  score grade
0    Alice   18     85     A
1      Bob   19     92     A
2  Charlie   17     78     B
3    David   18     90     A
4      Eve   20     88     B
5    Frank   19     95     A
6    Grace   18     82     B

分数大于 85 的学生:
      name  age  score grade
1      Bob   19     92     A
3    David   18     90     A
4      Eve   20     88     B
5    Frank   19     95     A

年龄等于 18 的学生:
      name  age  score grade
0    Alice   18     85     A
3    David   18     90     A
6    Grace   18     82     B

等级为 A 的学生:
      name  age  score grade
0    Alice   18     85     A
1      Bob   19     92     A
3    David   18     90     A
5    Frank   19     95     A

Code explanation:

Filtering conditions are placed in quotes, using SQL-like syntax.
Numerical comparisons use>, ==, <and other operators.
String comparisons use double quotes or single quotes.

Example 2: Compound Condition Filtering

query()Supports using logical operators to combine multiple conditions.

Example

import pandas as pd

data = {
'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace'],
'age': [18, 19, 17, 18, 20, 19, 18],
'score': [85, 92, 78, 90, 88, 95, 82],
'grade': ['A', 'A', 'B', 'A', 'B', 'A', 'B']
}
df = pd.DataFrame(data)

# Use AND condition: score greater than 85 and age less than 19
print("Students with score greater than 85 and age less than 19:")
print(df.query('score > 85 and age 90'))
print()

# Use NOT condition: grade is not A
print("Students whose grade is not A:")
print(df.query('not grade == "A"'))
print()

# Complex compound condition
print("Students aged between 18 and 20 with score not equal to 88:")
print(df.query('age >= 18 and age <= 20 and score != 88'))

Running result:

分数大于 85 且年龄小于 19 的学生:
      name  age  score grade
3    David   18     90     A
5    Frank   19     95     A

等级为 A 或分数大于 90 的学生:
      name  age  score grade
1      Bob   19     92     A
3    David   18     90     A
5    Frank   19     95     A

等级不为 A 的学生:
      name  age  score grade
2  Charlie   17     78     B
4      Eve   20     88     B
6    Grace   18     82     B

年龄在 18 到 20 之间且分数不等于 88 的学生:
      name  age  score grade
0    Alice   18     85     A
1      Bob   19     92     A
3    David   18     90     A
5    Frank   19     95     A

Code explanation:

Useandto represent the logical AND operation.
Useorto represent the logical OR operation.
Usenotto represent the logical NOT operation.
Compound conditions can be combined arbitrarily to form complex filtering logic.

Example 3: Using Python Variables

query()Supports using Python variables in expressions, which is very useful in dynamic queries.

Example

import pandas as pd

data = {
'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace'],
'age': [18, 19, 17, 18, 20, 19, 18],
'score': [85, 92, 78, 90, 88, 95, 82],
'grade': ['A', 'A', 'B', 'A', 'B', 'A', 'B']
}
df = pd.DataFrame(data)

# Use @ symbol to reference Python variables
min_score = 85
target_grade = 'A'

print("Filtering with variable - score greater than {}:".format(min_score))
print(df.query('score > @min_score'))
print()

print("Filtering with variable - grade is '{}':".format(target_grade))
print(df.query('grade == @target_grade'))
print()

# Use a list as a variable
target_grades = ['A', 'B']
print("Students with grades in {}:".format(target_grades))
print(df.query('grade in @target_grades'))
print()

# Use a Python function
def get_average_score(df):
return df['score'].mean()

print("Students with scores higher than the average:")
print(df.query('score > @get_average_score(df)'))

Running result:

使用变量筛选 - 分数大于 85:
      name  age  score grade
1      Bob   19     92     A
3    David   18     90     A
4      Eve   20     88     B
5    Frank   19     95     A

使用变量筛选 - 等级为 'A':
      name  age  score grade
0    Alice   18     85     A
1      Bob   19     92     A
3    David   18     90     A
5    Frank   19     95     A

等级在 ['A', 'B'] 中的学生:
      name  age  score grade
0    Alice   18     85     A
1      Bob   19     92     A
2  Charlie   17     78     B
3    David   18     90     A
4      Eve   20     88     B
5    Frank   19     95     A
6    Grace   18     82     B

分数高于平均分的学生:
      name  age  score grade
1      Bob   19     92     A
3    David   18     90     A
5    Frank   19     95     A

Code explanation:

Use the@symbol to reference Python variables in query expressions.
You can directly use Python lists forinin checks.
You can call Python functions to calculate dynamic thresholds.

Example 4: String Operations

query()It also supports some common string operations.

Example

import pandas as pd

# Create a DataFrame with string columns
data = {
'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace'],
'city': ['Beijing', 'Shanghai', 'Beijing', 'Guangzhou', 'Shanghai', 'Beijing', 'Shenzhen'],
'score': [85, 92, 78, 90, 88, 95, 82]
}
df = pd.DataFrame(data)

# Filter records for a specific city
print("Students in Beijing:")
print(df.query('city == "Beijing"'))
print()

# String containment check (using in)
print("Students in Shanghai or Beijing:")
print(df.query('city in ["Shanghai", "Beijing"]'))
print()

# Use @ to reference a variable containing a string
target_city = 'Guangzhou'
print("Students in {}:".format(target_city))
print(df.query('city == @target_city'))

Running result:

在北京的学生:
      name      city  score
0    Alice   Beijing     85
2  Charlie   Beijing     78
5    Frank   Beijing     95

在上海或北京的学生:
      name      city  score
0    Alice   Beijing     85
1      Bob   Shanghai     92
2  Charlie   Beijing     78
4      Eve   Shanghai     88
5    Frank   Beijing     95

在 Guangzhou 的学生:
      name       city  score
3    David  Guangzhou     90

Code explanation:

query()Supports equality comparison of strings.
You can use theinkeyword to check whether an element is in a list.
You can use@to reference string variables.

Example 5: Handling Column Names with Spaces or Special Characters

If column names contain spaces or special characters, backticks `` ` `` can be used to wrap the column names.

Example

import pandas as pd

# Create a DataFrame with column names containing spaces
data = {
'student name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
'score': [85, 92, 78, 90, 88],
'pass status': [True, True, False, True, True]
}
df = pd.DataFrame(data)

print("Original DataFrame (column names contain spaces):")
print(df)
print()

# Use backticks to wrap column names
print("Students with score greater than 85:")
print(df.query('`score` > 85'))
print()

print("Students with pass status True:")
print(df.query('`pass status` == True'))
print()

# This method can also handle reserved words as column names
df2 = pd.DataFrame({
'class': ['A', 'B', 'A', 'B', 'A'],
'def': [1, 2, 3, 4, 5] # def is a Python reserved word
})
print("Using a reserved word as a column name:")
print(df2.query('`def` > 3'))

Running result:

原始 DataFrame（列名包含空格）:
  student name  score  pass status
0       Alice     85        True
1         Bob     92        True
2    Charlie     78       False
3     David   90        True
4        Eve     88        True

分数大于 85 的学生:
  student name  score  pass status
1         Bob     92        True
3       David     90        True
4        Eve     88        True

通过状态为 True 的学生:
  student name  score  pass status
0       Alice     85        True
1         Bob     92        True
3       David     90        True
4        Eve     88        True

使用保留字作为列名:
  class  def
3     B    4
4     A    5

Code explanation:

By using backticks, you can handle cases where column names contain spaces or conflict with Python reserved words.
`score`and`pass status`Backticks are used to reference column names.
`def`It demonstrates how to handle Python reserved words as column names.

Notes

query()Column names in expressions must be valid Python identifiers, or wrapped in backticks.
String comparisons can use single quotes or double quotes.
Use the@symbol to reference Python variables.
For large DataFrames,query()performance is usually superior to traditional boolean indexing.
If the filtering expression contains reserved words or special characters, backticks must be used.

Tip:query()Especially suitable for scenarios where filtering conditions need to be dynamically constructed, such as filtering data based on user input or configuration files. Combined with Python's string formatting capabilities, very flexible data queries can be achieved.

Summary

query()is a powerful data filtering function in Pandas. It provides SQL-like query syntax, making complex conditional filtering more concise and readable.

Its main advantages include: concise and intuitive syntax, support for compound logical operations, support for Python variables and functions, and the ability to handle special column names. In actual data analysis, especially when filtering conditions need to be dynamically constructed,query()it is a very practical tool.

Pandas 常用函数 Common Pandas Functions

Other Extensions

Pandas df.filter() Function

Pandas df.iloc[] Function

Data Reading and Writing

Data Processing Core

Reference Manual

Statistics and Cases

Pandas df.query() Function

Basic Syntax and Parameters

Syntax Format

Parameter Description

Return Value Description

Examples

Example 1: Basic Usage - Single-Condition Filtering

Example

Example 2: Compound Condition Filtering

Example

Example 3: Using Python Variables

Example

Example 4: String Operations

Example

Example 5: Handling Column Names with Spaces or Special Characters

Example

Notes

Summary