pandas query

Have you ever written a filter like this, and wondered whether there was a shorter way to say it?

df.loc[(df['Status'] == 'operating') & (df['Start year'] >= 2015)]

Nothing is wrong with that line. But you had to name df twice, wrap each comparison in parentheses so & would not grab the wrong operands, and remember that & is not and. Do it with five conditions and the line stops being readable.

The query method is Pandas saying: describe the rows you want in a string, and I will work out the rest.

df.query("Status == 'operating' and `Start year` >= 2015")

Same rows, one mention of df, no parentheses, and the word and means what you think it means.

Official documentation: DataFrame.query

The shape of a query

Inside that string you are in a small language that is part Python and part SQL. Six rules cover nearly everything:

# Column names are bare -- no quotes, no df[...]
df.query("Status == 'operating'")

# Values that are strings do need quotes
df.query('Status == "operating"')      # either quoting style works

# and / or / not, spelled as words
df.query("Status == 'operating' and Region == 'Asia'")

# Python's `in`, against a list
df.query("Country in ['China', 'India']")

# Column names containing spaces go in backticks
df.query("`Start year` >= 2015")

# Python variables from outside the string need @
cutoff = 2015
df.query("`Start year` >= @cutoff")

That last rule is the one that makes query worth learning. Without @, the string is sealed off from your program; with it, a query is parameterized like any other function call. And because query returns a data frame, it drops into a chain wherever a filter belongs.

A worked example, on real data

Here is the Global Coal Plant Tracker again — the same workbook used in Bamboo Weekly #64, a real spreadsheet with one row for every coal-fired generating unit on earth.

import pandas as pd

url = ('https://www.bambooweekly.com/content/files/wp-content/uploads/2024/02/'
       'global-coal-plant-tracker-january-2024.xlsx')

df = pd.read_excel(url, sheet_name='Units',
                   usecols=['Country', 'Capacity (MW)', 'Status',
                            'Start year', 'Region'])

That gives us 13,906 rows. The question: which countries have added the most coal capacity that is still running today?

Two conditions, then. The unit has to be operating, and it has to have started recently. Status is one word, so it needs nothing special. Start year has a space, so it goes in backticks:

df.query("Status == 'operating' and `Start year` >= 2015")

That takes us from 13,906 rows to 1,479. Now pull the year into a variable, so the same query answers the question for any cutoff:

cutoff = 2015

df.query("Status == 'operating' and `Start year` >= @cutoff")

And since a filter is rarely the last thing you want, chain the summary onto it:

(
    df
    .query("Status == 'operating' and `Start year` >= @cutoff")
    .groupby('Country')['Capacity (MW)']
    .sum()
    .sort_values(ascending=False)
    .head(10)
)

Which gives:

Country
China           379819.0
India            80278.0
Indonesia        28496.0
Vietnam          18735.0
South Korea      16241.0
Japan            11662.3
South Africa      7968.8
Pakistan          7638.0
Türkiye           6710.0
Philippines       6382.8
Name: Capacity (MW), dtype: float64

China has built more coal capacity since 2015 than every other country combined, several times over.

Where it shows up in Bamboo Weekly

I use query sparingly, and the archive shows it: across every public Bamboo Weekly solution it appears three times, in two issues. That is worth knowing on its own, because it tells you this is a tool with a narrow sweet spot rather than a replacement for .loc.

Bamboo Weekly #20: World inflation is the issue where I asked for it by name. The data is the World Bank inflation database, read from a multi-sheet Excel file, and the task was to plot two inflation measures for the United States against each other. The two measures were not adjacent, so slicing was awkward:

df.query("""Country == 'United States' and `Series Name` in ['Headline Consumer Price Inflation', 'Producer Price Inflation']""")

Notice what that buys. Series Name has a space, so backticks. Country is an index level, and query lets you name index levels as though they were ordinary columns — something .loc will not do for you.

Bamboo Weekly #14: JOLTS is the better argument. Finding the most recent nationwide quits rate in the Bureau of Labor Statistics data means matching five codes at once:

(
    df
    .query("dataelement_code == 'QU' and industry_code == 0 "
           "and state_code == '00' and seasonal == 'S' "
           "and ratelevel_code == 'R'")
    [['year', 'period', 'value']]
    .sort_values(['year', 'period'])
    .tail(1)
)

The .loc version of that same filter needs df[...] five times and six pairs of parentheses. This is the case where query earns its keep.

One disambiguation, because the search results will mislead you: Bamboo Weekly #69 is full of calls to query, but they are duckdb.query — real SQL against a data frame, not the Pandas mini-language. Different tool, same word.

Five mistakes people make

Quoting the column name instead of the value. This is the most common one, and the error message is no help at all:

df.query("'Status' == 'operating'")
# KeyError: 'False: boolean label can not be used without a boolean index'

Inside the string, 'Status' is a literal, not a column. Pandas compared two literals, got False, then tried to use False as a row label. Column names are bare; only values get quotes.

Forgetting the @ on a Python variable. The string does not see your namespace unless you say so:

cutoff = 2015
df.query("`Start year` >= cutoff")
# UndefinedVariableError: name 'cutoff' is not defined

The fix is one character: @cutoff. This applies to lists too — Country in @wanted, never Country in wanted.

Leaving the backticks off a column name with a space. The parser is reading Python, and a space where it expects an operator is a syntax error:

df.query('Start year > 2015')
# SyntaxError: invalid syntax

Backticks are also the answer for column names with punctuation. `Capacity (MW)` > 1000 works fine, parentheses and all.

Breaking the string across lines. A long query begs to be reformatted, and this is the one place you cannot do it:

df.query("""
    Status == 'operating'
    and `Start year` >= 2015
""")
# ValueError: multi-line expressions are only valid in the context of data,
#             use DataFrame.eval

Still true in Pandas 3.0. Use Python's implicit string concatenation instead, as in the JOLTS example above — adjacent string literals join without a +.

Reaching for query when a boolean mask reads better. With one condition, query is overhead. The moment you need a method call, a computed column, or anything that is not a plain comparison, the string starts fighting you.

query and pd.col

Pandas 3.0 added pd.col, which solves the problem query was solving — referring to a column before you have the data frame in hand — without the string:

df.loc[(pd.col('Status') == 'operating') & (pd.col('Start year') >= 2015)]

That is the same 1,479 rows. It is longer, and for a two-condition filter I would still write the query. But pd.col composes with the rest of Pandas in a way a string never will: it works in assign, it carries into case_when, and your editor can see the column names.

What you cannot do is mix them:

df.query(pd.col('Status') == 'operating')
# ValueError: expr must be a string to be evaluated,
#             <class 'pandas.api.typing.Expression'> given

The official documentation lists DataFrame.query under See Also for pandas.col, which makes them look like partners. They are two separate mechanisms for the same idea, and you pick one per line.

Practice it

Work through a .query() exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/query/

Go deeper

More on the alternative: pd.col, including every error it raises and what each one means. For the filtering method you will reach for far more often, see loc.

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

Related methods

• .filter() — when you are selecting by column name rather than by the values in the rows
• .isin() — which is what a long chain of equality tests usually wants to be
• .loc() — when you want to select columns in the same breath as filtering rows

See it on real data

Below are the 4 Bamboo Weekly exercises that use query on real-world data — try each one, then study the worked solution.

• Bamboo Weekly #151: PyPI in 2025
• Bamboo Weekly #69: Election participation
• Bamboo Weekly #20: World inflation
• Bamboo Weekly #14: JOLTS

Part of the Pandas Methods Index. See also practice by skill.