Filter rows from DataFrame by multi-index column slice predicate

Question

I have a dataframe with a multi-index for the columns defined as follows:

import numpy as np
import pandas as pd
index = range(4)
columns = pd.MultiIndex.from_product([
    ['A0', 'B0'],
    ['A1', 'B1'],
    ['A2', 'B2']
])

data = np.random.rand(len(index), len(columns))
df = pd.DataFrame(data, index=index, columns=columns)

This gives me something like:

         A0                                      B0                              
         A1                  B1                  A1                  B1          
         A2        B2        A2        B2        A2        B2        A2        B2
0  0.523564  0.270243  0.881117  0.760946  0.687436  0.318483  0.963247  0.161210
1  0.141363  0.563427  0.242174  0.966277  0.382161  0.486944  0.417305  0.513510
2  0.832275  0.036995  0.510963  0.112446  0.069597  0.490321  0.022453  0.643659
3  0.601649  0.705902  0.735125  0.506853  0.666612  0.533352  0.484133  0.069325

I now want to filter all the rows where the value of any of the B2 columns is below a threshold value, e.g. 0.05. I did the following:

df_filtered = df[df.loc[:, (slice(None), slice(None), 'B2')] < 0.05]

But this gives me the following:

   A0                    B0            
   A1            B1      A1      B1    
   A2        B2  A2  B2  A2  B2  A2  B2
0 NaN NaN       NaN NaN NaN NaN NaN NaN
1 NaN NaN       NaN NaN NaN NaN NaN NaN
2 NaN  0.036995 NaN NaN NaN NaN NaN NaN
3 NaN NaN       NaN NaN NaN NaN NaN NaN

This is not what I want because:

the row's values are somehow mapped to NaN. I want to preserve the original row contents.
all rows are returned. I only want the rows where the any of the B2 values are below 0.05, in this cas only row with index=2.

How can I achieve this?

score 2 · Accepted Answer · answered Oct 12 '18 at 08:38

Use DataFrame.any for check at least one True per column and add reindex for append missing levels of MultiIndex:

np.random.seed(456)

import numpy as np
import pandas as pd
index = range(4)
columns = pd.MultiIndex.from_product([
    ['A0', 'B0'],
    ['A1', 'B1'],
    ['A2', 'B2']
])

data = np.random.rand(len(index), len(columns))
df = pd.DataFrame(data, index=index, columns=columns)
print (df)
         A0                                      B0                      \
         A1                  B1                  A1                  B1   
         A2        B2        A2        B2        A2        B2        A2   
0  0.248756  0.163067  0.783643  0.808523  0.625628  0.604114  0.885702   
1  0.181105  0.150169  0.435679  0.385273  0.575710  0.146091  0.686593   
2  0.569999  0.645701  0.723341  0.680671  0.180917  0.118158  0.242734   
3  0.360068  0.146042  0.542723  0.857103  0.200212  0.134633  0.213594   



         B2  
0  0.759117  
1  0.468804  
2  0.008183  
3  0.973156

mask = ((df.loc[:, (slice(None), slice(None), 'B2')] < 0.05)
           .any()
           .reindex(df.columns, fill_value=False))
print (mask)
A0  A1  A2    False
        B2    False
    B1  A2    False
        B2    False
B0  A1  A2    False
        B2    False
    B1  A2    False
        B2     True
dtype: bool

df = df.loc[:, mask]
print (df)
         B0
         B1
         B2
0  0.759117
1  0.468804
2  0.008183
3  0.973156

For rows solution is simplier - use DataFrame.any with axis=1 for check at least one True per rows:

mask = (df.loc[:, (slice(None), slice(None), 'B2')] < 0.05).any(axis=1)
print (mask)
0    False
1    False
2     True
3    False
dtype: bool

df = df[mask]
print (df)
         A0                                      B0                      \
         A1                  B1                  A1                  B1   
         A2        B2        A2        B2        A2        B2        A2   
2  0.569999  0.645701  0.723341  0.680671  0.180917  0.118158  0.242734   



         B2  
2  0.008183

Filter rows from DataFrame by multi-index column slice predicate

1 Answers1