Use variable in Pandas query

Question

I'm trying to query a Pandas dataframe like this:

inv = pd.read_csv(infile)
inv.columns = ['County', 'Site', 'Role', 'Hostname'] 
clist = inv.County.unique()  # Get list of counties
for county in clist:  # for each county
    csub = inv.query('County == county')  # create a county subset
    # ... do stuff on subset

But I get an error:

pandas.core.computation.ops.UndefinedVariableError: name 'county' is not defined

I'm sure it's a trivial error, but I can't figure it out. How do I pass a variable to the query method?

how about 'County == ' + county

seanv507
– seanv507

2019-07-31 18:43:06 +00:00
Commented Jul 31, 2019 at 18:43 — seanv507
– seanv507, Commented Jul 31, 2019 at 18:43
inv[inv['County'] == county]

pythonic833
– pythonic833

2019-07-31 18:43:24 +00:00
Commented Jul 31, 2019 at 18:43 — pythonic833
– pythonic833, Commented Jul 31, 2019 at 18:43
inv.query('County ==@ county')

BENY
– BENY

2019-07-31 18:44:11 +00:00
Commented Jul 31, 2019 at 18:44 — BENY
– BENY, Commented Jul 31, 2019 at 18:44

Philip Ciunkiewicz · Accepted Answer · 2019-07-31 18:44:01Z

118

According to the documentation, you can reference variables using @:

csub = inv.query('County == @county')

answered Jul 31, 2019 at 18:44

Philip Ciunkiewicz

2,8183 gold badges15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dr. Manuel Kuehner · Accepted Answer · 2022-05-13 21:53:22Z

15

Format String Function

I found another (more generic) solution that might be interesting: The format string function (for examples, see 6.1.3.2. Format examples).

xyz = df.query('ColumnName >= {}'.format(VariableName))

The {} is replaced by VariableName.

f-Strings

In addition, user pciunkiewicz mentioned in a comment another solution using so-called f-strings which were introduced in Python 3.6 (August 2015):

xyz = df.query(f'ColumnName >= {VariableName}')

A more general f-strings example, taken from here:

>>> name = "Eric"
>>> age = 74
>>> f"Hello, {name}. You are {age}."
'Hello, Eric. You are 74.'

PS: I am new to Python.

edited May 13, 2022 at 21:53

answered Mar 28, 2021 at 20:27

Dr. Manuel Kuehner

4311 gold badge7 silver badges17 bronze badges

2 Comments

Philip Ciunkiewicz Over a year ago

You can clean this up using f-strings which were introduced in Python 3.6: xyz = df.query(f'ColumnName >= {VariableName}').

do-me Over a year ago

ATTENTION: Simple string substitution might be unsafe in production, see here github.com/pandas-dev/pandas/issues/…

Collectives™ on Stack Overflow

Use variable in Pandas query

2 Answers 2

Comments

Format String Function

f-Strings

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Format String Function

f-Strings

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related