Filtering a mutli-index

user_17490862 0
Viewed 83
C1 C2 C3 C4
A 12 True 89
9 False 77
5 True 23
B 9 True 45
5 True 45
2 False 78
C 11 True 10
8 False 08
12 False 09

C1 & C2 are the multi index. I'm hoping to get a result which gives me only values in C1 which have values both lower than 10 and greater than or equal to 10 in C2.

So in the table above C1 - B should go, with the final result should look like this:

C1 C2 C3 C4
A 12 True 89
9 False 77
5 True 23
C 11 True 10
8 False 08
12 False 09

I tried df.loc[(df.C2 < 10 ) & (df.C2 >= 10)] but this didn't work.

I also tried:

filter1 = df.index.get_level_values('C2') < 10 filter2 = df.index.get_level_values('C2') >= 10

df.iloc[filter1 & filter2]

Which I saw suggested on another post that also didn't work. Any one know how to solve this? Thanks

multi-index pandas python
1 Answers

Use GroupBy.transform with GroupBy.any for test at least one condition match per groups, so possible last filter by m DataFrame:

filter1 = df.index.get_level_values('C2') < 10 
filter2 = df.index.get_level_values('C2') >= 10

m = (df.assign(filter1= filter1, filter2=filter2)
       .groupby(level=0)[['filter1','filter2']]
       .transform('any'))

print (m)
       filter1  filter2
C1 C2                  
A  12     True     True
   9      True     True
   5      True     True
B  9      True    False
   5      True    False
   2      True    False
C  11     True     True
   8      True     True
   12     True     True

df = df[m.filter1 & m.filter2]
print (df)
          C3  C4
C1 C2           
A  12   True  89
   9   False  77
   5    True  23
C  11   True  10
   8   False   8
   12  False   9

Alternative solution:

filter1 = df.index.get_level_values('C2') < 10 
filter2 = df.index.get_level_values('C2') >= 10

lvl1 = df.index[filter1].remove_unused_levels().levels[0]
lvl2 = df.index[filter2].remove_unused_levels().levels[0]

df1 = df.loc[set(lvl1).intersection(lvl2)]
print (df1)
          C3  C4
C1 C2           
A  12   True  89
   9   False  77
   5    True  23
C  11   True  10
   8   False   8
   12  False   9
user_29010020
Related