Pandas 1.0.1 - how to index a DataFrame with MultiIndex using a list containing a slicer

user_2309803 0
Viewed 71

As a example dataset -

In [12]: import numpy as np; import pandas as pd                                                     

In [24]: data_raw = pd.DataFrame([  
    ...:      ...: {'frame': 1, 'face': np.NaN, 'lmark': np.NaN, 'x': np.NaN, 'y': np.NaN},  
    ...:      ...: {'frame': 197, 'face': 0, 'lmark': 1, 'x': 969, 'y': 737},  
    ...:      ...: {'frame': 197, 'face': 0, 'lmark': 2, 'x': 969, 'y': 740},  
    ...:      ...: {'frame': 197, 'face': 0, 'lmark': 3, 'x': 970, 'y': 744},  
    ...:      ...: {'frame': 197, 'face': 0, 'lmark': 4, 'x': 972, 'y': 748},  
    ...:      ...: {'frame': 197, 'face': 0, 'lmark': 5, 'x': 973, 'y': 752},  
    ...:      ...: {'frame': 300, 'face': 0, 'lmark': 1, 'x': 745, 'y': 367},   
    ...:      ...: {'frame': 300, 'face': 0, 'lmark': 2, 'x': 753, 'y': 411},   
    ...:      ...: {'frame': 300, 'face': 0, 'lmark': 3, 'x': 759, 'y': 455},  
    ...:      ...: {'frame': 301, 'face': 0, 'lmark': 1, 'x': 741, 'y': 364},    
    ...:      ...: {'frame': 301, 'face': 0, 'lmark': 2, 'x': 746, 'y': 408},    
    ...:      ...: {'frame': 301, 'face': 0, 'lmark': 3, 'x': 750, 'y': 452}]).set_index(['frame', 'face', 'lmark'])

In Pandas 1.0.3, I could filter out rows of the DataFrame above where lmark > 3 with the following -

data_filtered = data_raw.loc[(slice(None), slice(None), [np.NaN, slice(3)]), :]

but in Pandas 1.1.0, the same statement fails with 

TypeError: unhashable type: 'slice'

apparently this change is by design.

In that case, how could I filter out rows of the DataFrame below where lmark > 3?

pandas indexing
2 Answers

Note: I am not so sure about versions before Pandas 1.0. If you have to deal with versions less than Pandas 1.0, @jezrael's solution is much more stable.

I don't know if this meets your use case, where u want only rows less than 3 on lmark: Multi-Index Slicing

#allows for easier slicing than Slice(None)
idx = pd.IndexSlice

#list the data u want to keep ... including np.nan
data_raw.loc[idx[:,:,[np.nan,1,2,3]],:]

                           x    y
frame   face    lmark                   
1        NaN    NaN      NaN    NaN
197     0.0     1.0     969.0   737.0
                2.0     969.0   740.0
                3.0     970.0   744.0
300     0.0     1.0     745.0   367.0
                2.0     753.0   411.0
                3.0     759.0   455.0
301     0.0     1.0     741.0   364.0
                2.0     746.0   408.0
                3.0     750.0   452.0
user_71757130

Selecting with IndexSlice and slice working only in some versions correctly, so I suggest use another approach, selecting by condition(s):

Filtering by DataFrame.query:

vals = [np.nan,1,2,3]
df = data_raw.query('lmark in @vals')

Or by Index.isin:

vals = [np.nan,1,2,3]
df = data_raw[data_raw.index.get_level_values('lmark').isin(vals)]

If want select all values without >3:

df = data_raw[~(data_raw.index.get_level_values('lmark') > 3)]

Or all values < 3 with missing values:

i = data_raw.index.get_level_values('lmark')
df = data_raw[(i <= 3) | i.isna()]

Or first 3 rows per first level by GroupBy.head:

df = data_raw.groupby(level=0).head(3)

print (df)
                      x      y
frame face lmark              
1     NaN  NaN      NaN    NaN
197   0.0  1.0    969.0  737.0
           2.0    969.0  740.0
           3.0    970.0  744.0
300   0.0  1.0    745.0  367.0
           2.0    753.0  411.0
           3.0    759.0  455.0
301   0.0  1.0    741.0  364.0
           2.0    746.0  408.0
           3.0    750.0  452.0
user_29010020
Related