Pandas Query with Variable as Column Name

Question

Passing on new application of information I learned that was part of another question: Unable to query a local variable in pandas 0.14.0

Credit and thanks to user @choldgraf. I'm applying his answer from the above link differently.

Objective: To use a variable as the column name in a query

Failed examples:

import pandas as pd
fooframe = pd.DataFrame({'Size':['Large', 'Medium', 'Small', 'Tiny'], 'Color':[1, 2, 3, 4]})
myvar = 'Size'
subframe = fooframe.query("myvar == 'Large'")

The code above returns a key error for 'myvar'.

import pandas as pd
fooframe = pd.DataFrame({'Size':['Large', 'Medium', 'Small', 'Tiny'], 'Color':[1, 2, 3, 4]})
myvar = 'Size'
subframe = fooframe.query("@myvar == 'Large'")

The code above adds "@" before myvar in the query to reference myvar as a local variable. However, the code still returns an error.

Is this a bug with pandas, or intended to not work? As of March 2019, I also cannot reference to columns by variables... — Thomas
– Thomas, Commented Mar 29, 2019 at 13:04
Hi @Thomas, did you see my answer below? Is that still not working for you? The question demonstrates two techniques that I couldn't get to work. — TempleGuard527
– TempleGuard527, Commented Apr 4, 2019 at 18:27
Hi @TempleGuard527, I saw the answer and upvoted it because it works, but was wondering whether any future visitors to this question find a way to make it work with "@variable" instead since this is the intended (and less cluttered) method — Thomas
– Thomas, Commented Apr 5, 2019 at 11:56

ramslök · Accepted Answer · 2022-05-16 04:52:18Z

25

Credit and thanks to user @choldgraf. I used the technique he mentioned in another post (Unable to query a local variable in pandas 0.14.0) not for the value in the column but for the column name.

A variable can be used as the column name in a pandas query by inserting it into the query string like so:

import pandas as pd
fooframe = pd.DataFrame({'Size':['Large', 'Medium', 'Small', 'Tiny'], 'Color':[1, 2, 3, 4]})
myvar = 'Size'
subframe = fooframe.query("`{0}` == 'Large'".format(myvar))

(Where backticks are used to bracket the column name, dealing with special characters and spaces in column names.)

edited May 16, 2022 at 4:52

ramslök

2,9042 gold badges17 silver badges23 bronze badges

answered Apr 11, 2018 at 17:56

TempleGuard527

7031 gold badge7 silver badges18 bronze badges

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

2 Comments

Harsh Gupta Over a year ago

The answer is perfect. It will only create an issue if the column name has space in it, you can use backtick to overcome subframe = fooframe.query("{0} == 'Large'".format(myvar))

karel van dongen Over a year ago

fooframe.query(f"{myvar} == 'Large' ") is also possible.

paradocslover · Accepted Answer · 2023-04-14 10:23:08Z

4

@TempleGuard527's answer works perfectly. This answer is just an addition with the introduction of f-strings from python3.6. Now, you can do something like this:

import pandas as pd
fooframe = pd.DataFrame({'Size':['Large', 'Medium', 'Small', 'Tiny'], 'Color':[1, 2, 3, 4]})
myvar = 'Size'
subframe = fooframe.query(f"`{myvar}` == 'Large'")

answered Apr 14, 2023 at 10:23

paradocslover

3,3543 gold badges25 silver badges49 bronze badges

Collectives™ on Stack Overflow

Pandas Query with Variable as Column Name

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related