Filtering multilevel dataframe by the values of the lower level

user_6401403 0
Viewed 41

I have a dataframe with three levels, let's call them Class -> Group -> Value:

Class   Group   Value   Fail
A       1       0       False
A       1       1       True
A       2       0       False
A       2       1       True
A       2       2       True
A       3       2       True
A       3       5       True
B       4       2       False
B       4       7       True
B       4       8       True
B       4       12      True
B       4       15      True
B       5       1       False
B       5       2       False
B       5       3       True
B       6       7       False
B       6       8       False

I need to get the groups where the minimal value in a group is equal to the minimal value in a class where Fail==True:

Class   Group   Value   Fail
A       1       0       False
A       1       1       True
A       2       0       False
A       2       1       True
A       2       2       True
B       5       1       False
B       5       2       False
B       5       3       True

So in the class A the minimal value with Fail==True is 1, and it is in the groups 1 and 2. in the class B the minimal value with Fail==True is 3 in the group 5.

How to do this?

pandas python
2 Answers

you need groupby.transform, once on Class and once on Group, get the min of Value. You want where both are equal eq

df[df['Value'].where(df['Fail']).groupby(df['Class']).transform('min')
     .eq(df['Value'].where(df['Fail']).groupby(df['Group']).transform('min'))]
   Class  Group  Value   Fail
0      A      1      0  False
1      A      1      1   True
2      A      2      0  False
3      A      2      1  False
4      A      2      2   True
12     B      5      1  False
13     B      5      2  False
14     B      5      3   True

if you have the same Group name in several Class, you should consider using the second groupby on both Class and Group.

df[df['Value'].where(df['Fail']).groupby(df['Class']).transform('min')
     .eq(df['Value'].where(df['Fail']).groupby([df['Class'], df['Group']]).transform('min'))]
user_92747320

We do it by two steps, find the min , find the id with the min 

df1 =df[df.Fail].copy()
s=df1.groupby('Class').Value.min()
df=df[df.Group.isin(df1.loc[df1.Value.isin(s),'Group'])]
   Class  Group  Value   Fail
0      A      1      0  False
1      A      1      1   True
2      A      2      0  False
3      A      2      1   True
4      A      2      2   True
12     B      5      1  False
13     B      5      2  False
14     B      5      3   True
user_79645270
Related