Pandas query(): Filter Rows with Expressions

Use query() to select rows with readable expressions. Combine conditions, pass Python values with @ and keep missing records available for review.

Create the student table 🔝

Keep the source table and store each selection separately. Use info() to inspect columns and loc when a Boolean mask is easier to read.

import pandas as pd
import numpy as np

def student_data():
    return pd.DataFrame({
        "ID": [1, 2, 3, 4, 5, 6],
        "NAME": ["Ravi", "Raju", "Alex", "Ron", "King", "Jack"],
        "MATH": [80, 40, 70, 70, 82, 30],
        "ENGLISH": [81, 70, 40, 50, 60, 30]
    })

df = student_data()
print(df)

Expected output

   ID  NAME  MATH  ENGLISH
0   1  Ravi    80       81
1   2  Raju    40       70
2   3  Alex    70       40
3   4   Ron    70       50
4   5  King    82       60
5   6  Jack    30       30

Understand query() syntax 🔝

Call df.query(expression) with a fixed expression string. The result contains matching rows and all columns, preserving their index labels. Column names act as variables; use quotes around text values. These examples specify engine="python" so an optional numexpr installation is unnecessary. This engine choice is not a security sandbox.

Select a name 🔝

Use equality for an exact name. A substring search belongs to str.contains().

df = student_data()
selected = df.query('NAME == "Raju"', engine="python")
print(selected)
assert selected["ID"].tolist() == [2]

Expected output

   ID  NAME  MATH  ENGLISH
1   2  Raju    40       70

Select marks above a threshold 🔝

Greater-than excludes the boundary. Compare this expression with MATH >= 80 to include Ravi as well.

df = student_data()
selected = df.query("MATH > 80", engine="python")
print(selected)
assert selected["ID"].tolist() == [5]

Expected output

   ID  NAME  MATH  ENGLISH
4   5  King    82       60

Use inplace deliberately 🔝

With inplace=True, the table itself is filtered and the return value is None. Make a copy when the full table is needed later. Each following student example starts from a fresh table.

df = student_data()
filtered = df.copy()
returned = filtered.query("MATH > 80", engine="python", inplace=True)
print(filtered)
print("Return value:", returned)
print("Source rows:", len(df))
assert returned is None and len(df) == 6

Expected output

   ID  NAME  MATH  ENGLISH
4   5  King    82       60
Return value: None
Source rows: 6

Require both conditions 🔝

Parentheses make the intended comparisons clear. With the default Pandas parser, query expressions support &, |, and the words and/or for combined conditions.

df = student_data()
selected = df.query("(MATH >= 50) & (ENGLISH >= 50)", engine="python")
print(selected)
assert selected["ID"].tolist() == [1, 4, 5]

Expected output

   ID  NAME  MATH  ENGLISH
0   1  Ravi    80       81
3   4   Ron    70       50
4   5  King    82       60

Accept either condition 🔝

An OR expression keeps a student meeting at least one threshold.

df = student_data()
selected = df.query("(MATH >= 80) | (ENGLISH >= 80)", engine="python")
print(selected)
assert selected["ID"].tolist() == [1, 5]

Expected output

   ID  NAME  MATH  ENGLISH
0   1  Ravi    80       81
4   5  King    82       60

Pass a Python variable with @ 🔝

The @ prefix distinguishes a Python variable from a column name. Pass values with local_dict rather than inserting them into the expression string. Keep the expression developer-written: query can execute arbitrary code and must not accept untrusted expressions.

df = student_data()
my_limit = 80
selected = df.query(
    "(MATH >= @my_limit) | (ENGLISH >= @my_limit)",
    local_dict={"my_limit": my_limit}, engine="python"
)
print(selected)
assert selected["ID"].tolist() == [1, 5]

Expected output

   ID  NAME  MATH  ENGLISH
0   1  Ravi    80       81
4   5  King    82       60

Match a list of names 🔝

Use in to express membership. The list is a set of acceptable names, not a substring pattern.

df = student_data()
selected = df.query('NAME in ["Raju", "Ron"]', engine="python")
print(selected)
assert selected["ID"].tolist() == [2, 4]

Expected output

   ID  NAME  MATH  ENGLISH
1   2  Raju    40       70
3   4   Ron    70       50

Use a Python list and exclude members 🔝

Explicit in and not in communicate membership clearly. The default Pandas parser also supports equality with a list; compare it here with the explicit membership form.

df = student_data()
my_list = ["Raju", "Ron"]
settings = {"my_list": my_list}
selected = df.query("NAME in @my_list", local_dict=settings, engine="python")
equivalent = df.query("NAME == @my_list", local_dict=settings, engine="python")
excluded = df.query("NAME not in @my_list", local_dict=settings, engine="python")
print("Included IDs:", selected["ID"].tolist())
print("Other IDs:", excluded["ID"].tolist())
assert selected.equals(equivalent)
assert excluded["ID"].tolist() == [1, 3, 5, 6]

Expected output

Included IDs: [2, 4]
Other IDs: [1, 3, 5, 6]

Find missing and known values 🔝

Use isnull() and notnull() explicitly. A missing numeric value will not pass a normal greater-than filter. Preserve missing records for review when their eligibility is unknown.

df = student_data()
df.loc[1, "MATH"] = np.nan
df.loc[4, "NAME"] = np.nan
df.loc[3, "ENGLISH"] = np.nan
print("Missing MATH:")
print(df.query("MATH.isnull()", engine="python"))
print("Known NAME:")
print(df.query("NAME.notnull()", engine="python"))
assert df.query("MATH.isnull()", engine="python")["ID"].tolist() == [2]
assert df.query("NAME.notnull()", engine="python")["ID"].tolist() == [1, 2, 3, 4, 6]

Expected output

Missing MATH:
   ID  NAME  MATH  ENGLISH
1   2  Raju   NaN     70.0
Known NAME:
   ID  NAME  MATH  ENGLISH
0   1  Ravi  80.0     81.0
1   2  Raju   NaN     70.0
2   3  Alex  70.0     40.0
3   4   Ron  70.0      NaN
5   6  Jack  30.0     30.0

Quote column names with spaces 🔝

Backticks allow column names such as Math score. Use rename() if consistent simple names better suit your workflow.

df = student_data().rename(columns={"MATH": "Math score"})
selected = df.query("`Math score` >= 80", engine="python")
print(selected)
assert selected["ID"].tolist() == [1, 5]

Expected output

   ID  NAME  Math score  ENGLISH
0   1  Ravi          80       81
4   5  King          82       60

Select a range and particular columns 🔝

Apply column selection with loc after query. Inclusive range boundaries keep marks equal to 70 and 80.

df = student_data()
selected = df.query("70 <= MATH <= 80", engine="python").loc[:, ["ID", "NAME", "MATH"]]
print(selected)
assert selected["ID"].tolist() == [1, 3, 4]

Expected output

   ID  NAME  MATH
0   1  Ravi    80
2   3  Alex    70
3   4   Ron    70

Compare query() with a Boolean mask 🔝

loc is an indexer and uses square brackets. Masks are useful when conditions involve multiple preprocessing steps. Choose the form that makes the rule clearest; performance depends on the expression and data.

df = student_data()
by_query = df.query("(MATH >= 50) & (ENGLISH >= 50)", engine="python")
mask = df["MATH"].ge(50) & df["ENGLISH"].ge(50)
by_mask = df.loc[mask]
print("Same rows:", by_query.equals(by_mask))
print(by_mask)
assert by_query.equals(by_mask)

Expected output

Same rows: True
   ID  NAME  MATH  ENGLISH
0   1  Ravi    80       81
3   4   Ron    70       50
4   5  King    82       60

Practical example: prepare sales for selection 🔝

This synthetic sales example separates invalid amounts from eligible orders. Convert amounts explicitly with data cleaning steps; never replace an unknown amount with zero merely to make filtering easier.

raw = pd.DataFrame({
    "order_id": [101, 102, 103, 104, 105, 106],
    "region": ["North", "South", "North", "North", "South", "North"],
    "status": ["paid", "paid", "test", "paid", "paid", "paid"],
    "amount": ["25", "40", "50", "bad", None, "15"]
})
sales = raw.copy()
sales["amount"] = pd.to_numeric(sales["amount"], errors="coerce")
review = raw.loc[sales["amount"].isna()].copy()
print(sales)
print("Review IDs:", review["order_id"].tolist())
assert review["order_id"].tolist() == [104, 105]

Expected output

   order_id region status  amount
0       101  North   paid    25.0
1       102  South   paid    40.0
2       103  North   test    50.0
3       104  North   paid     NaN
4       105  South   paid     NaN
5       106  North   paid    15.0
Review IDs: [104, 105]

Select paid orders with adjustable criteria 🔝

Use a fixed expression and pass the requested region and minimum amount as values. Missing amounts are kept in the separate review table. Nonmatching valid rows are excluded by the selection rule, not labelled data errors.

chosen_regions = ["North", "South"]
minimum = 20
selected = sales.query(
    '(status == "paid") & (region in @regions) & (amount >= @minimum)',
    local_dict={"regions": chosen_regions, "minimum": minimum},
    engine="python"
).copy()
print(selected)
print("Orders:", len(selected))
print("Revenue:", selected["amount"].sum())
assert selected["order_id"].tolist() == [101, 102]
assert selected["amount"].sum() == 65

Expected output

   order_id region status  amount
0       101  North   paid    25.0
1       102  South   paid    40.0
Orders: 2
Revenue: 65.0

Reconcile the selection and export 🔝

Keep counts for selected rows, invalid amounts and other valid rows. Use groupby() for a regional report and Input and Output for saving files.

other_valid = sales.loc[sales["amount"].notna() & ~sales["order_id"].isin(selected["order_id"])]
print("Selected:", len(selected), "Review:", len(review), "Other valid:", len(other_valid))
print(selected.to_csv(index=False).rstrip())
assert len(selected) + len(review) + len(other_valid) == len(raw)
# Optional Colab download preparation:
# selected.to_csv("selected_sales.csv", index=False)

Expected output

Selected: 2 Review: 2 Other valid: 2
order_id,region,status,amount
101,North,paid,25.0
102,South,paid,40.0

Common questions 🔝

Does query() reset row labels? No; use reset_index() when needed. Why is a variable not found? Use @ and pass it in local_dict. Why did later examples lose rows? An earlier inplace operation may have changed the source. Can visitors enter an expression? Do not pass untrusted expressions to query; offer fixed filters and validate their values. Does engine="python" make this safe? No. Why are missing amounts absent? They do not satisfy the numeric comparison; inspect them separately. Continue with at, iloc, where() and mask().

Exercise: change the region and threshold 🔝

Using the sales table, select paid North orders with amount at least 10. Predict the IDs, count and revenue. Keep invalid amounts in review.

Exercise solution 🔝

The eligible orders are 101 and 106, giving two orders and revenue 40.

answer = sales.query(
    '(status == "paid") & (region == @region) & (amount >= @minimum)',
    local_dict={"region": "North", "minimum": 10}, engine="python"
)
print(answer)
print("Orders:", len(answer), "Revenue:", answer["amount"].sum())
assert answer["order_id"].tolist() == [101, 106]
assert answer["amount"].sum() == 40

Expected output

   order_id region status  amount
0       101  North   paid    25.0
5       106  North   paid    15.0
Orders: 2 Revenue: 40.0

Practice in Google Colab 🔝

Open in Google Colab View on GitHub
Run the examples in order, change the region and minimum amount, then compare selected orders and review rows. Save your own copy to keep edits. All sample tables are included in the notebook.

Continue with the Data Cleaning hub, where(), loc, at, NumPy where(), fillna() and iloc.

Reference: Pandas DataFrame.query documentation.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: