filter by two conditions after a group by

Question

I am willing to filter the ids which have SMS and Phone in the type column and whenever login_method is equal to resend

df

id    type   login_method
 1     SMS         resend
 1     SMS       complete
 2   phone         resend
 2     SMS         resend
 2     SMS          start
 3   phone         resend
 3   phone          start
 3   phone       complete
 3     SMS           nice

expected result

df
    id    type   login_method
 1     SMS         resend
 1     SMS       complete
 3   phone         resend
 3   phone          start
 3   phone       complete
 3     SMS           nice

In this case only id 2 have phone and SMS in the login method equal to resend

What is your question? What have you tried? What is your output? Do you get any error(s)? — harvpan, Jul 05 '18 at 14:35
that would give my expected value. But the problem is an illustrative example, my dataframe could have another value for login_method which is not equal to resend — Lucas Dresl, Jul 05 '18 at 14:40
I am confused because group 2 have `resend` values in sample data. It is wrong sample? — jezrael, Jul 05 '18 at 14:42
there i edit my sample to show what i was meaning. I want to drop ID 2 which has in their resend code phone and SMS — Lucas Dresl, Jul 05 '18 at 14:43

score 1 · Accepted Answer · answered Jul 05 '18 at 15:02

Use:

v = ['SMS','phone']
#first filter only valuse by list
df = df[df['type'].isin(v)]

#get id where are all values per groups with resend
m1 = df['login_method'] == 'resend'
s = df[m1].drop_duplicates(['id','type']).groupby('id')['type'].nunique() != len(v)

#filtering by ids
df1 = df[df['id'].isin(s.index[s])]
print (df1)
   id   type login_method
0   1    SMS       resend
1   1    SMS     complete
4   3  phone       resend
5   3  phone        start
6   3  phone     complete
7   3    SMS         nice

filter by two conditions after a group by

1 Answers1