Querying a pandas dataframe column which has values as list

Question

I have a dataframe as given below.

import pandas as pd


raw_data = {'score': [1,2,3], 
        'tags': [['apple','pear','guava'],['truck','car','plane'],['cat','dog','mouse']]}


df = pd.DataFrame(raw_data, columns = ['score', 'tags'])

df.query("score==1") gives the first row as result.

But df.query("tags='apple'") gives error.

How to write query for the column 'tags'.

You've stored lists in your df, the query method is unable to evaluate your expression to handle this. To filter the df you'd need to do df[df['tags'].apply(lambda x: 'apple' in x)]. Storing non-scalar values in a df is non-performant, you can't expect the usual pandas operations to work like normal — EdChum
– EdChum, Commented Jun 21, 2018 at 10:31
@EdChum, Oops, edited my post at the same time. But I'm going to add another alternative too :) — jpp
– jpp, Commented Jun 21, 2018 at 10:37
@jpp no worries I'm sure there is a dupe for this although not specifically for getting query to handles lists — EdChum
– EdChum, Commented Jun 21, 2018 at 10:54

jpp · Accepted Answer · 2018-06-21 10:36:45Z

1

You cannot use pd.DataFrame.query to test membership of a string in lists within a series of lists. Holding lists in Pandas dataframes is not recommended as you lose vectorised functionality.

With your existing dataframe, you can instead calculate a mask using pd.Series.apply:

res = df[df['tags'].apply(lambda x: 'apple' in x)]

print(res)

   score                  tags
0      1  [apple, pear, guava]

Or you can use a list comprehension:

res = df[['apple' in x for x in df['tags']]]

A third option is to use set:

res = df[df['tags'].apply(set) >= {'apple'}]

The last option, although expensive, may suit when you are testing for existence of multiple tags. In each case, we are constructing a Boolean series, which we then use to mask the dataframe.

answered Jun 21, 2018 at 10:36

jpp

166k37 gold badges301 silver badges363 bronze badges

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

Collectives™ on Stack Overflow

Querying a pandas dataframe column which has values as list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related