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:

0
level_0level_1level_2
2ax87
y41
3bz84
x50
cz5
x54

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
Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.
Accept
Reject