Pandas filter MultiIndex on part of MultiIndex using .loc

Pandas filter MultiIndex on part of MultiIndex using .loc

Problem Description:

I want to filter a DataFrame using only 2 levels of a 3-level MultiIndex. Is there a way cant find a way to do that with .loc?

The only way I managed to do that is the following:

df=pd.DataFrame(index=pd.MultiIndex.from_tuples([(1,'a','x')
,(1,'a','y')
,(1,'b','z')
,(1,'b','x')
,(2,'c','y')
,(2,'c','z')
,(2,'a','x')
,(2,'a','y')
,(3,'b','z')
,(3,'b','x')
,(3,'c','y')
,(3,'c','z')]), 
data=[20,26,43,20,65,40,87,41,84,50,5,54])

f=[(2, 'a'), (3, 'b'), (3, 'c')]

df=df.reset_index(level=2).loc[f].reset_index().set_index(['level_0','level_1','level_2'])

resulting df is:

What I want is to be able to do something like df.loc[(f,slice(None))] to make the code a bit less complicated

Solution – 1

IIUC you still can achieve that, you just have to structure your tuple correctly inside loc.

df.loc[([2, 3], ["a", "b"], ), :]

Output:

        0
2 a x  87
    y  41
3 b z  84
    x  50

Solution – 2

i think f is not appropriate example becuz a and b do not overlap in 2 and 3

Let’s take a from 1 and only b from 3 (becuz 1 also has b)

idx = [(1, 'a'), (3, 'b')]
df[df.index.droplevel(2).isin(idx)]

result:

            0
1   a   x   20
        y   26
3   b   z   84
        x   50

Solution – 3

Another way of doing it is to use get_locs the output of which can be used to locate using iloc. Basically get_locs returns a sequence of integers based on the index passed.

df.iloc[np.ravel([df.index.get_locs(t) for t in f])]

Output:

        0
2 a x  87
    y  41
3 b z  84
    x  50

Solution – 4

One option is with select_rows from pyjanitor – your data might need to be sorted to avoid a Performance warning:

# pip install pyjanitor
import pandas as pd
import janitor

df.sort_index().select_rows(f)
        0
2 a x  87
    y  41
3 b x  50
    z  84
  c y   5
    z  54