Slicing a Pandas dataframe with a multiindex by combinations of level values

Question

I 'm looking to slice a Pandas dataframe with a multi index by a list of values for some of the index levels.

import pandas as pd
index = pd.MultiIndex.from_product((('A','B'),
                                    range(2),
                                    ('c','d')))
df = pd.DataFrame(index=index,columns=range(2))

slice_list = [('A',0),('B',1)]

This doesn't work:

df.loc[slice_list,:]

I can get my expected result by doing boolean masking(?):

idx = (((df.index.get_level_values(0) == 'A')
   & (df.index.get_level_values(1) == 0)) |
   ((df.index.get_level_values(0) == 'B')
    & (df.index.get_level_values(1) == 1)))
df.loc[idx]



         0    1
A 0 c  NaN  NaN
    d  NaN  NaN
B 1 c  NaN  NaN
    d  NaN  NaN

But I would much prefer a slicing option.

jezrael · Accepted Answer · 2021-03-30 10:56:10Z

1

You can use Index.isin with remove last level for 2 levels MultiIndex:

df1 = df[df.index.droplevel(-1).isin(slice_list)]
print (df1)
         0    1
A 0 c  NaN  NaN
    d  NaN  NaN
B 1 c  NaN  NaN
    d  NaN  NaN

Details:

print (df.index.droplevel(-1))
MultiIndex([('A', 0),
            ('A', 0),
            ('A', 1),
            ('A', 1),
            ('B', 0),
            ('B', 0),
            ('B', 1),
            ('B', 1)],
           )

answered Mar 30, 2021 at 10:56

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lucas Ford · Accepted Answer · 2021-03-30 11:07:48Z

0

You can use pandas.IndexSlice (https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.IndexSlice.html).

idx = pd.IndexSlice
slice = idx[['A','B'],[0,1],:]
df.loc[slice,:]

Or something along those lines, I am currently replying from a cell phone so I'm not 100% sure.

answered Mar 30, 2021 at 11:07

Lucas Ford

1813 bronze badges

1 Comment

Artturi Björk Over a year ago

thanks for the suggestion, but I'm not looking to get all cells that have either 'A' or 'B' as their first index and either 0 or 1 in the second index, but specifically cells with 'A',0 or 'B',1 in the first two indices.

Collectives™ on Stack Overflow

Slicing a Pandas dataframe with a multiindex by combinations of level values

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related