Opening notes

Loading…

Notes PandasSelecting & Filtering

Selecting & FilteringTopic 29 of 835 min read

The query() Method

Filter rows with a readable string expression — df.query("team == 'MI' and runs > 100") — using and/or/not, in, chained comparisons, @variables and backticks.

Boolean filters are powerful but noisy: players[(players["team"] == "MI") & (players["runs"] > 100)] repeats players three times and needs careful brackets. DataFrame.query() lets you write the same filter as a short, SQL-like string: players.query("team == 'MI' and runs > 100"). Same result, much easier to read.

Definition

DataFrame.query(expr) filters rows using a boolean expression written as a string, in which column names are used directly as variables, and returns a new DataFrame containing the rows where the expression is True.

Expression as a string — the whole condition goes inside quotes. Text values inside it need the other kind of quote: "team == 'MI'".
Column names as variables — write runs, not players["runs"].
Friendlier syntax — inside query you may use the words and, or, not, the operators in / not in, and chained comparisons like 200 <= runs <= 450.
Returns a new DataFrame — the original is not changed.

Syntax

python

df.query("col > 5")
df.query("col1 == 'text' and col2 > 5")
df.query("col in ['a', 'b']")
df.query("low <= col <= high")
df.query("col > @python_variable")
df.query("`column with spaces` > 5")

Feature	Inside `query`	Equivalent boolean filter
AND / OR / NOT	`and`, `or`, `not` (or `&`, `\|`, `~`)	`&`, `\|`, `~`
text value	`team == 'MI'`	`df["team"] == "MI"`
membership	`team in ['MI', 'CSK']`	`df["team"].isin([...])`
range	`200 <= runs <= 450`	`df["runs"].between(200, 450)`
Python variable	`runs >= @min_runs`	`df["runs"] >= min_runs`
odd column name	`strike rate` > 150	`df["strike rate"] > 150`
the index	`index >= 6`	`df.index >= 6`

Example — the basics

python

print(players.query("runs > 300"))
print(players.query("team == 'MI' and runs > 100"))
print(players.query("not (role == 'Batter')")[["player", "role"]])

text

         player team    role  runs  wickets  strike_rate
0   Virat Kohli  RCB  Batter   741        0        154.7
1  Rohit Sharma   MI  Batter   417        0        150.0
6  Shubman Gill   GT  Batter   426        0        147.4
          player team         role  runs  wickets  strike_rate
1   Rohit Sharma   MI       Batter   417        0        150.0
4  Hardik Pandya   MI  All-rounder   216       11        143.0
            player         role
2         MS Dhoni       Keeper
3   Jasprit Bumrah       Bowler
4    Hardik Pandya  All-rounder
5  Ravindra Jadeja  All-rounder
7      Rashid Khan       Bowler

How it works: these are exactly the results from the Boolean Filtering topic — query just builds the same masks for you. Inside the string, and/or/not are allowed (they're translated to &/|/~), and the operator precedence is the natural one, so you don't need brackets around each comparison.

Example — in, ranges and @variables

python

print(players.query("team not in ['MI', 'CSK']")[["player", "team"]])
print(players.query("200 <= runs <= 450")[["player", "runs"]])

min_runs = 400
print(players.query("runs >= @min_runs")[["player", "runs"]])

text

         player team
0   Virat Kohli  RCB
6  Shubman Gill   GT
7   Rashid Khan   GT
            player  runs
1     Rohit Sharma   417
4    Hardik Pandya   216
5  Ravindra Jadeja   267
6     Shubman Gill   426
         player  runs
0   Virat Kohli   741
1  Rohit Sharma   417
6  Shubman Gill   426

How it works: in/not in work like isin. A chained comparison 200 <= runs <= 450 works like between. The @ prefix tells query "this is a Python variable from my code, not a column" — essential when the threshold comes from user input or a loop. A list variable works too: fav = ["RCB", "GT"]; players.query("team in @fav").

Example — column names with spaces, and comparing columns

python

df = players.rename(columns={"strike_rate": "strike rate"})
print(df.query("`strike rate` > 150")[["player", "strike rate"]])
print(players.query("runs > wickets * 30")[["player", "runs", "wickets"]])

text

            player  strike rate
0      Virat Kohli        154.7
2         MS Dhoni        220.5
5  Ravindra Jadeja        157.1
            player  runs  wickets
0      Virat Kohli   741        0
1     Rohit Sharma   417        0
2         MS Dhoni   161        0
5  Ravindra Jadeja   267        8
6     Shubman Gill   426        0

How it works: a name with a space isn't a valid variable name, so wrap it in backticks `. You can also do arithmetic and compare columns with each other: runs > wickets * 30 keeps players whose runs exceed 30 × their wickets (Jadeja: 267 > 240 ✓, Hardik: 216 < 330 ✗).

Comparison — boolean indexing vs query()

	Boolean indexing	`query()`
Looks like	`df[(df["a"] > 1) & (df["b"] == "x")]`	`df.query("a > 1 and b == 'x'")`
Readability	noisy for many conditions	reads like SQL `WHERE`
Parentheses per condition	required	not required
Python variables	used directly	need `@name`
Odd column names	`df["strike rate"]`	`strike rate` in backticks
Typos	caught by your editor	only found when the string runs
Assigning values	`df.loc[mask, "col"] = v` ✅	❌ — `query` only selects

Both are correct; teams usually pick query for long, readable filters and boolean masks when the condition is built dynamically or used to modify values.

Common mistakes

❌ players.query("team == MI") → UndefinedVariableError: name 'MI' is not defined. ✅ Quote text values: "team == 'MI'".
❌ players.query("team = 'MI'") → ValueError: cannot assign without a target object. ✅ Use == for comparison.
❌ players.query("runs >= min_runs") → UndefinedVariableError (no such column). ✅ Prefix Python variables with @: @min_runs.
❌ players.query("strike rate > 150"). ✅ Backticks: "`strike rate` > 150".
❌ Trying df.query(...)["runs"] = 0 to change the original. ✅ query returns a new DataFrame; modify with df.loc[mask, "runs"] = 0.

Interview tip

Q: How do you use a Python variable inside DataFrame.query()? A: Prefix it with @, e.g. threshold = 400; df.query("runs >= @threshold"). Without @, pandas looks for a column with that name and raises UndefinedVariableError if none exists.

Key points to remember

df.query('expression') filters rows with a string condition and returns a new DataFrame.
Inside query, column names are used directly and and/or/not are allowed — no brackets per condition needed.
Text values need inner quotes: "team == 'MI'".
query supports in / not in lists and chained comparisons like '200 <= runs <= 450'.
Use @variable to refer to a Python variable; backticks for column names with spaces.
Columns can be compared or combined with arithmetic: 'runs > wickets * 30'.
query only selects — use df.loc[mask, col] = value to modify data.

Try it yourself

query vs boolean indexing

import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma", "MS Dhoni", "Jasprit Bumrah",
               "Hardik Pandya", "Ravindra Jadeja", "Shubman Gill", "Rashid Khan"],
    "team": ["RCB", "MI", "CSK", "MI", "MI", "CSK", "GT", "GT"],
    "runs": [741, 417, 161, 8, 216, 267, 426, 102],
    "wickets": [0, 0, 0, 20, 11, 8, 0, 10],
})

a = players[(players["team"] == "MI") & (players["runs"] > 100)]
b = players.query("team == 'MI' and runs > 100")
print(b)
print("Same result?", a.equals(b))
print()
print(players.query("team in ['CSK', 'GT'] and wickets >= 8"))

import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma", "MS Dhoni", "Jasprit Bumrah",
               "Hardik Pandya", "Ravindra Jadeja", "Shubman Gill", "Rashid Khan"],
    "team": ["RCB", "MI", "CSK", "MI", "MI", "CSK", "GT", "GT"],
    "runs": [741, 417, 161, 8, 216, 267, 426, 102],
    "wickets": [0, 0, 0, 20, 11, 8, 0, 10],
})

a = players[(players["team"] == "MI") & (players["runs"] > 100)]
b = players.query("team == 'MI' and runs > 100")
print(b)
print("Same result?", a.equals(b))
print()
print(players.query("team in ['CSK', 'GT'] and wickets >= 8"))

@variables, ranges and backticks

import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma", "MS Dhoni", "Jasprit Bumrah",
               "Hardik Pandya", "Ravindra Jadeja", "Shubman Gill", "Rashid Khan"],
    "team": ["RCB", "MI", "CSK", "MI", "MI", "CSK", "GT", "GT"],
    "runs": [741, 417, 161, 8, 216, 267, 426, 102],
    "strike rate": [154.7, 150.0, 220.5, 80.0, 143.0, 157.1, 147.4, 120.0],
})

min_runs = 200
fav_teams = ["MI", "CSK"]
print(players.query("runs >= @min_runs and team in @fav_teams"))
print()
print(players.query("150 <= runs <= 450"))
print()
print(players.query("`strike rate` > 150"))

import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma", "MS Dhoni", "Jasprit Bumrah",
               "Hardik Pandya", "Ravindra Jadeja", "Shubman Gill", "Rashid Khan"],
    "team": ["RCB", "MI", "CSK", "MI", "MI", "CSK", "GT", "GT"],
    "runs": [741, 417, 161, 8, 216, 267, 426, 102],
    "strike rate": [154.7, 150.0, 220.5, 80.0, 143.0, 157.1, 147.4, 120.0],
})

min_runs = 200
fav_teams = ["MI", "CSK"]
print(players.query("runs >= @min_runs and team in @fav_teams"))
print()
print(players.query("150 <= runs <= 450"))
print()
print(players.query("`strike rate` > 150"))

Common query errors

import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma"],
    "team": ["RCB", "MI"],
    "runs": [741, 417],
})

for expr in ["team == MI", "team = 'MI'", "runs > limit", "team == 'MI'"]:
    try:
        result = players.query(expr)
        print(f"{expr!r:16} -> OK, {len(result)} row(s)")
    except Exception as e:
        print(f"{expr!r:16} -> {type(e).__name__}: {e}")

import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma"],
    "team": ["RCB", "MI"],
    "runs": [741, 417],
})

for expr in ["team == MI", "team = 'MI'", "runs > limit", "team == 'MI'"]:
    try:
        result = players.query(expr)
        print(f"{expr!r:16} -> OK, {len(result)} row(s)")
    except Exception as e:
        print(f"{expr!r:16} -> {type(e).__name__}: {e}")

Practice: query the squad

# 🏏 Your turn: with query, find non-MI players with runs above the variable cutoff, or wickets >= 10. Edit and Run.
import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma", "MS Dhoni", "Jasprit Bumrah",
               "Hardik Pandya", "Ravindra Jadeja", "Shubman Gill", "Rashid Khan"],
    "team": ["RCB", "MI", "CSK", "MI", "MI", "CSK", "GT", "GT"],
    "runs": [741, 417, 161, 8, 216, 267, 426, 102],
    "wickets": [0, 0, 0, 20, 11, 8, 0, 10],
})

cutoff = 250
print(players.query("(team != 'MI' and runs > @cutoff) or wickets >= 10"))

# 🏏 Your turn: with query, find non-MI players with runs above the variable cutoff, or wickets >= 10. Edit and Run.
import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma", "MS Dhoni", "Jasprit Bumrah",
               "Hardik Pandya", "Ravindra Jadeja", "Shubman Gill", "Rashid Khan"],
    "team": ["RCB", "MI", "CSK", "MI", "MI", "CSK", "GT", "GT"],
    "runs": [741, 417, 161, 8, 216, 267, 426, 102],
    "wickets": [0, 0, 0, 20, 11, 8, 0, 10],
})

cutoff = 250
print(players.query("(team != 'MI' and runs > @cutoff) or wickets >= 10"))

Revision Flashcards

1 / 6

Tap a card to flip it and see the answer.

Opening notes

Notes PandasSelecting & Filtering

Selecting & FilteringTopic 29 of 835 min read

The query() Method

Filter rows with a readable string expression — df.query("team == 'MI' and runs > 100") — using and/or/not, in, chained comparisons, @variables and backticks.

Definition

DataFrame.query(expr) filters rows using a boolean expression written as a string, in which column names are used directly as variables, and returns a new DataFrame containing the rows where the expression is True.

Expression as a string — the whole condition goes inside quotes. Text values inside it need the other kind of quote: "team == 'MI'".
Column names as variables — write runs, not players["runs"].
Friendlier syntax — inside query you may use the words and, or, not, the operators in / not in, and chained comparisons like 200 <= runs <= 450.
Returns a new DataFrame — the original is not changed.

Syntax

python

df.query("col > 5")
df.query("col1 == 'text' and col2 > 5")
df.query("col in ['a', 'b']")
df.query("low <= col <= high")
df.query("col > @python_variable")
df.query("`column with spaces` > 5")

Feature	Inside `query`	Equivalent boolean filter
AND / OR / NOT	`and`, `or`, `not` (or `&`, `\|`, `~`)	`&`, `\|`, `~`
text value	`team == 'MI'`	`df["team"] == "MI"`
membership	`team in ['MI', 'CSK']`	`df["team"].isin([...])`
range	`200 <= runs <= 450`	`df["runs"].between(200, 450)`
Python variable	`runs >= @min_runs`	`df["runs"] >= min_runs`
odd column name	`strike rate` > 150	`df["strike rate"] > 150`
the index	`index >= 6`	`df.index >= 6`

Example — the basics

python

print(players.query("runs > 300"))
print(players.query("team == 'MI' and runs > 100"))
print(players.query("not (role == 'Batter')")[["player", "role"]])

text

         player team    role  runs  wickets  strike_rate
0   Virat Kohli  RCB  Batter   741        0        154.7
1  Rohit Sharma   MI  Batter   417        0        150.0
6  Shubman Gill   GT  Batter   426        0        147.4
          player team         role  runs  wickets  strike_rate
1   Rohit Sharma   MI       Batter   417        0        150.0
4  Hardik Pandya   MI  All-rounder   216       11        143.0
            player         role
2         MS Dhoni       Keeper
3   Jasprit Bumrah       Bowler
4    Hardik Pandya  All-rounder
5  Ravindra Jadeja  All-rounder
7      Rashid Khan       Bowler

Example — in, ranges and @variables

python

print(players.query("team not in ['MI', 'CSK']")[["player", "team"]])
print(players.query("200 <= runs <= 450")[["player", "runs"]])

min_runs = 400
print(players.query("runs >= @min_runs")[["player", "runs"]])

text

         player team
0   Virat Kohli  RCB
6  Shubman Gill   GT
7   Rashid Khan   GT
            player  runs
1     Rohit Sharma   417
4    Hardik Pandya   216
5  Ravindra Jadeja   267
6     Shubman Gill   426
         player  runs
0   Virat Kohli   741
1  Rohit Sharma   417
6  Shubman Gill   426

Example — column names with spaces, and comparing columns

python

df = players.rename(columns={"strike_rate": "strike rate"})
print(df.query("`strike rate` > 150")[["player", "strike rate"]])
print(players.query("runs > wickets * 30")[["player", "runs", "wickets"]])

text

            player  strike rate
0      Virat Kohli        154.7
2         MS Dhoni        220.5
5  Ravindra Jadeja        157.1
            player  runs  wickets
0      Virat Kohli   741        0
1     Rohit Sharma   417        0
2         MS Dhoni   161        0
5  Ravindra Jadeja   267        8
6     Shubman Gill   426        0

Comparison — boolean indexing vs query()

	Boolean indexing	`query()`
Looks like	`df[(df["a"] > 1) & (df["b"] == "x")]`	`df.query("a > 1 and b == 'x'")`
Readability	noisy for many conditions	reads like SQL `WHERE`
Parentheses per condition	required	not required
Python variables	used directly	need `@name`
Odd column names	`df["strike rate"]`	`strike rate` in backticks
Typos	caught by your editor	only found when the string runs
Assigning values	`df.loc[mask, "col"] = v` ✅	❌ — `query` only selects

Both are correct; teams usually pick query for long, readable filters and boolean masks when the condition is built dynamically or used to modify values.

Common mistakes

❌ players.query("team == MI") → UndefinedVariableError: name 'MI' is not defined. ✅ Quote text values: "team == 'MI'".
❌ players.query("team = 'MI'") → ValueError: cannot assign without a target object. ✅ Use == for comparison.
❌ players.query("runs >= min_runs") → UndefinedVariableError (no such column). ✅ Prefix Python variables with @: @min_runs.
❌ players.query("strike rate > 150"). ✅ Backticks: "`strike rate` > 150".
❌ Trying df.query(...)["runs"] = 0 to change the original. ✅ query returns a new DataFrame; modify with df.loc[mask, "runs"] = 0.

Interview tip

Key points to remember

df.query('expression') filters rows with a string condition and returns a new DataFrame.
Inside query, column names are used directly and and/or/not are allowed — no brackets per condition needed.
Text values need inner quotes: "team == 'MI'".
query supports in / not in lists and chained comparisons like '200 <= runs <= 450'.
Use @variable to refer to a Python variable; backticks for column names with spaces.
Columns can be compared or combined with arithmetic: 'runs > wickets * 30'.
query only selects — use df.loc[mask, col] = value to modify data.

Try it yourself

query vs boolean indexing

import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma", "MS Dhoni", "Jasprit Bumrah",
               "Hardik Pandya", "Ravindra Jadeja", "Shubman Gill", "Rashid Khan"],
    "team": ["RCB", "MI", "CSK", "MI", "MI", "CSK", "GT", "GT"],
    "runs": [741, 417, 161, 8, 216, 267, 426, 102],
    "wickets": [0, 0, 0, 20, 11, 8, 0, 10],
})

a = players[(players["team"] == "MI") & (players["runs"] > 100)]
b = players.query("team == 'MI' and runs > 100")
print(b)
print("Same result?", a.equals(b))
print()
print(players.query("team in ['CSK', 'GT'] and wickets >= 8"))

import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma", "MS Dhoni", "Jasprit Bumrah",
               "Hardik Pandya", "Ravindra Jadeja", "Shubman Gill", "Rashid Khan"],
    "team": ["RCB", "MI", "CSK", "MI", "MI", "CSK", "GT", "GT"],
    "runs": [741, 417, 161, 8, 216, 267, 426, 102],
    "wickets": [0, 0, 0, 20, 11, 8, 0, 10],
})

a = players[(players["team"] == "MI") & (players["runs"] > 100)]
b = players.query("team == 'MI' and runs > 100")
print(b)
print("Same result?", a.equals(b))
print()
print(players.query("team in ['CSK', 'GT'] and wickets >= 8"))

@variables, ranges and backticks

import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma", "MS Dhoni", "Jasprit Bumrah",
               "Hardik Pandya", "Ravindra Jadeja", "Shubman Gill", "Rashid Khan"],
    "team": ["RCB", "MI", "CSK", "MI", "MI", "CSK", "GT", "GT"],
    "runs": [741, 417, 161, 8, 216, 267, 426, 102],
    "strike rate": [154.7, 150.0, 220.5, 80.0, 143.0, 157.1, 147.4, 120.0],
})

min_runs = 200
fav_teams = ["MI", "CSK"]
print(players.query("runs >= @min_runs and team in @fav_teams"))
print()
print(players.query("150 <= runs <= 450"))
print()
print(players.query("`strike rate` > 150"))

import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma", "MS Dhoni", "Jasprit Bumrah",
               "Hardik Pandya", "Ravindra Jadeja", "Shubman Gill", "Rashid Khan"],
    "team": ["RCB", "MI", "CSK", "MI", "MI", "CSK", "GT", "GT"],
    "runs": [741, 417, 161, 8, 216, 267, 426, 102],
    "strike rate": [154.7, 150.0, 220.5, 80.0, 143.0, 157.1, 147.4, 120.0],
})

min_runs = 200
fav_teams = ["MI", "CSK"]
print(players.query("runs >= @min_runs and team in @fav_teams"))
print()
print(players.query("150 <= runs <= 450"))
print()
print(players.query("`strike rate` > 150"))

Common query errors

import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma"],
    "team": ["RCB", "MI"],
    "runs": [741, 417],
})

for expr in ["team == MI", "team = 'MI'", "runs > limit", "team == 'MI'"]:
    try:
        result = players.query(expr)
        print(f"{expr!r:16} -> OK, {len(result)} row(s)")
    except Exception as e:
        print(f"{expr!r:16} -> {type(e).__name__}: {e}")

import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma"],
    "team": ["RCB", "MI"],
    "runs": [741, 417],
})

for expr in ["team == MI", "team = 'MI'", "runs > limit", "team == 'MI'"]:
    try:
        result = players.query(expr)
        print(f"{expr!r:16} -> OK, {len(result)} row(s)")
    except Exception as e:
        print(f"{expr!r:16} -> {type(e).__name__}: {e}")

Practice: query the squad

# 🏏 Your turn: with query, find non-MI players with runs above the variable cutoff, or wickets >= 10. Edit and Run.
import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma", "MS Dhoni", "Jasprit Bumrah",
               "Hardik Pandya", "Ravindra Jadeja", "Shubman Gill", "Rashid Khan"],
    "team": ["RCB", "MI", "CSK", "MI", "MI", "CSK", "GT", "GT"],
    "runs": [741, 417, 161, 8, 216, 267, 426, 102],
    "wickets": [0, 0, 0, 20, 11, 8, 0, 10],
})

cutoff = 250
print(players.query("(team != 'MI' and runs > @cutoff) or wickets >= 10"))

# 🏏 Your turn: with query, find non-MI players with runs above the variable cutoff, or wickets >= 10. Edit and Run.
import pandas as pd

players = pd.DataFrame({
    "player": ["Virat Kohli", "Rohit Sharma", "MS Dhoni", "Jasprit Bumrah",
               "Hardik Pandya", "Ravindra Jadeja", "Shubman Gill", "Rashid Khan"],
    "team": ["RCB", "MI", "CSK", "MI", "MI", "CSK", "GT", "GT"],
    "runs": [741, 417, 161, 8, 216, 267, 426, 102],
    "wickets": [0, 0, 0, 20, 11, 8, 0, 10],
})

cutoff = 250
print(players.query("(team != 'MI' and runs > @cutoff) or wickets >= 10"))

Revision Flashcards

1 / 6

Tap a card to flip it and see the answer.