Find values in one column that have association with certain values in another column

user_5425172 0
Viewed 141

I have the following example pandas dataframe:

df = pd.DataFrame({'A': ['a1', 'a2', 'a3', 'a1', 'a2', 'a4'],
                   'B': ['b1', 'b1', 'b2', 'b3', 'b6', 'b6']})

I want to find the values in A that have association with all of the values of an input list in B.

For example, for an input lst = ['b1', 'b6'], the desired output is ['a2']. I have tried df[df['B'].isin(lst)] from here but it is indeed not yet sufficient, or may not be even necessary.

pandas tabular dataframe python
3 Answers

We can do it groupby + filter with isin + all 

df.groupby('A').filter(lambda x : pd.Series(lst).isin(x['B']).all())
    A   B
1  a2  b1
4  a2  b6

Or 

s=pd.crosstab(df.A,df.B).ge(1)[lst].all(1) # you have the series 
s # s[s].index
A
a1    False
a2     True
a3    False
a4    False
dtype: bool
user_79645270

First filter df on each element of lst using a list comprehension

a_list = [ df.loc[df['B']==el,'A'].tolist() for el in lst]
# [['a1', 'a2'], ['a2', 'a4']]

The values you are looking for are the intersection of all lists in a_list

list(set.intersection(*[set(x) for x in a_list]))
# ['a2']
user_33011130

Using set.issubset to check if one list is in the other:

m = df.groupby('A')['B'].apply(lambda x: set(lst).issubset(set(x)))
df[df['A'].map(m)]

    A   B
0  a1  b1
1  a2  b1
3  a1  b3
4  a2  b6
7  a2  b7
8  a1  b6
user_90812670
Related