Combine pandas DataFrame query () method with isin ()

Question

So I want to use the isin() method with df.query() to select the rows with id in the list: id_list . Similar questions were asked before, but they used the typical df[df['id'].isin(id_list)] . I am wondering if there is a way to use df.query() .

 df = pd.DataFrame({'a': list('aabbccddeeff'), 'b': list('aaaabbbbcccc'), 'c': np.random.randint(5, size=12), 'd': np.random.randint(9, size=12)}) id_list = ["a", "b", "c"] 

And it gives an error

 df.query('a == id_list') 

Answer 1

From docs to query

You can refer to variables in the environment by prefixing them with the "@" symbol, like @a + b .

In your case:

 In [38]: df.query('a == @id_list') Out[38]: abcd 0 aa 3 4 1 aa 4 5 2 ba 2 3 3 ba 1 5 4 cb 2 4 5 cb 1 2 

Answer 2

You can also include a list in the query string:

 >>> df.query('a in ["a", "b", "c"]') 

This is the same as:

 >>> df.query('a in @id_list') 

Answer 3

It works:

 >>> df.query('a == {0}'.format(id_list)) abcd 0 aa 4 1 1 aa 0 7 2 ba 2 1 3 ba 0 1 4 cb 4 0 5 cb 4 2 

Whether this is clearer is a matter of personal taste.