How to groupby dataframe rows and filter against all occurrences from a list of strings?

user_14460824 0
Viewed 257

Situation

There is an df that is holding elements, each element could have different types:

df = pd.DataFrame({
    'id':[1,1,2,2,2],
    'type':['a','b','a','b','c']
})

This df should be filtert against a list and show only the ids that types contains all the list:

['a','b','c']

Expected id in this case is: 2 cause all of its types a,b,c are in the list.

The approaches that I tried

Grouping by id and get a list of its types, but no idea how to compare against the list or to apply something like set(list).issubset(types):

df = df.groupby('id')['type'].apply(list)
df.reset_index(name = 'types')

Query against list, but with result that are all types in:

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

Would be great to get a hint - Thanks

pandas python
4 Answers

You can try set.issubset with groupby.apply

l = ['a','b','c']
s = df.groupby("id")['type'].apply(lambda x: set(l).issubset(x))
s[s].index

Int64Index([2], dtype='int64', name='id')

s[s].index[0]
#2
user_98406370

You can check isin then groupby:

your_list = ['a', 'b', 'c']

df['type'].isin(your_list).groupby(df['id']).all()

Output:

id
1    True
2    True
Name: type, dtype: bool

Now, if you want to filter your original data, it's groupby.transform:

df[df['type'].isin(your_list).transform('all')]

Update if you are looking for id that contains all types in list:

valids = df['type'].isin(your_list)
mask = df[valids].groupby('id')['type'].nunique() == len(set(your_list))
mask[mask].index

Output:

Int64Index([2], dtype='int64', name='id')
user_42384080

We can use np.logical_and.reduce in a list comprehension to check that all values exist at least somewhere in the group (accomplished via groupby + transform('any')). This might scale better if your list of values is small while your number of unique IDs is large.

import numpy as np
my_list =  ['a', 'b', 'c']

mask = np.logical_and.reduce([df['type'].eq(val).groupby(df['id']).transform('any') 
                              for val in my_list])
#array([False, False,  True,  True,  True])

df[mask]
#   id type
#2   2    a
#3   2    b
#4   2    c
user_43333590

You can compare sets:

s = set(['a','b','c'])
df.groupby('id')['type'].apply(set).eq(s)

Output:

id
1    False
2     True
Name: type, dtype: bool
user_89736200
Related