Using lists in a pandas query

Question

I am performing a query on a DataFrame:

Index Category
1     Foo
2     Bar
3     Cho
4     Foo

I would like to return the rows where the category is "Foo" or "Bar". When I use the code:

df.query("Catergory==['Foo','Bar']")

This works fine and returns:

Index Category
1     Foo
2     Bar
4     Foo

However in future I will want the filter to be changed dynamically so I wrote:

filter_list=['Foo','Bar']
df.query("Catergory==filter_list")

Which threw out the error:

UndefinedVariableError: name 'filter_list' is not defined

Other variations I tried with no success were:

df.query("Catergory"==filter_list)
df.query("Catergory=="filter_list)

Respectively producing:

ValueError: expr must be a string to be evaluated, <class 'bool'> given
SyntaxError: invalid syntax

Scott Boston · Accepted Answer · 2019-08-28 15:45:40Z

38

Use @ to reference variables in query:

filter_list=['Foo','Bar']

df.query("Category == @filter_list")

Output:

   Index Category
0      1      Foo
1      2      Bar
3      4      Foo

answered Aug 28, 2019 at 15:45

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

Comments

Erfan · Accepted Answer · 2019-08-28 16:36:57Z

23

Use isin method.

df.query('Category.isin(@filter_list)')

edited Aug 28, 2019 at 16:36

answered Aug 28, 2019 at 15:46

Erfan

43.3k10 gold badges75 silver badges86 bronze badges

1 Comment

abeboparebop Over a year ago

I find this syntax much more clear than the accepted answer (for which I would expect exact list equality). Works for me in Pandas version 1.2.4.

MarcoS · Accepted Answer · 2022-03-08 12:49:44Z

10

Try this:

df = pd.DataFrame({'Index':[1,2,3,4],'Category':['Foo','Bar','Cho','Foo']})
filter_list = ['Foo','Bar']

df.query(f'Category=={filter_list}')

edited Mar 8, 2022 at 12:49

MarcoS

13.6k7 gold badges45 silver badges64 bronze badges

answered Aug 28, 2019 at 15:46

samredai

7223 silver badges8 bronze badges

2 Comments

MarcoS Over a year ago

You can also write df.query(f'Category in {filter_list}'), which may be more readable

gregV Over a year ago

df.query("Category in @filter_list") works as well

Collectives™ on Stack Overflow

Using lists in a pandas query

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related