Pandas: Filter out the rows (unique ID) that contains two desirable values

user_9816518 0
Viewed 37

I have a dataframe like below;

ID          code

333_c_132   x
333_c_132   n06
333_c_132   n36
333_c_132   n60
999_c_133   x
999_c_133   n06
999_c_133   n12
999_c_133   n24
998_c_134   x
998_c_134   n06
998_c_134   n12
998_c_134   n18
998_c_134   n36
997_c_135   x
997_c_135   n06
997_c_135   n12
997_c_135   n24
997_c_135   n36

I want to filter out the rows (unique ID) that contains both x and n36. ie

ID          code
333_c_132   x
333_c_132   n36
998_c_134   x
998_c_134   n36
997_c_135   x
997_c_135   n36

I tried like this bellow;

df = df[(df.code == "x") | (df.code == "n36")]

but it returns 999_c_133 x as well.. which I want to avoid.

pandas-groupby pandas python-3.x
1 Answers

Use, Series.eq to create a boolean mask m1 & m2 then use groupby to group the mask m1 & m2 on df[ID], now use transform(any) to create a boolean mask which satisfy the codition where the ID contains both the values m1 and m2, thereafter filter the dataframe using this mask:

m1 = df['code'].eq('x')
m2 = df['code'].eq('n36')
m = (
    m1.groupby(df['ID']).transform('any') & 
    m2.groupby(df['ID']).transform('any') & 
    df['code'].isin(['x', 'n36'])
)
df1 = df[m]

# print(df1)
           ID code
0   333_c_132    x
2   333_c_132  n36
8   998_c_134    x
12  998_c_134  n36
13  997_c_135    x
17  997_c_135  n36
user_128331660
Related