Filtering rows by a particular index level in a MultiIndex dataframe

Question

Given a multIndex dataframe:

mux = pd.MultiIndex.from_arrays([
    list('aaaabbbbbccdddddd'),
    list('tuvwlmnopxyfghijk')
], names=['one', 'two'])

df = pd.DataFrame({'col': np.arange(len(mux))}, mux)

df

         col
one two     
a   t      0
    u      1
    v      2
    w      3
b   l      4
    m      5
    n      6
    o      7
    p      8
c   x      9
    y     10
d   f     11
    g     12
    h     13
    i     14
    j     15
    k     16

Is it possible to keep rows corresponding to upto the ith value of the 0th level of the dataframe?

For i = 2, my expected output is:

         col
one two     
a   t      0
    u      1
    v      2
    w      3
b   l      4
    m      5
    n      6
    o      7
    p      8

Note that only rows pertaining to a and b are retained, everything else is dropped. I hope the problem is clear, but if it isn't, please feel free to ask for clarifications.

I tried:

idx = pd.IndexSlice
df.iloc[(idx[:2], slice(None))]

But that gives me only the first two rows in the entire df, not all rows of the first two values in the 0th level.

Does df.loc[df.index.levels[0][:2].values] work for you? I realize it's not a very direct way of going about it, but at least gets what you need? — johnchase
– johnchase, Commented Oct 24, 2017 at 0:51
@johnchase Yup, it works for this example (feel free to write an answer). I was kinda hoping there was a solution that could translate to any level. I realise this does not work for the 1st level (only the 0th). — cs95
– cs95, Commented Oct 24, 2017 at 0:53

johnchase · Accepted Answer · 2017-10-24 00:58:47Z

1

One way to go about this is to return the index values for the 0th level and then index into the original data frame with those:

df.loc[df.index.levels[0][:2].values]

         col
one two     
a   t      0
    u      1
    v      2
    w      3
b   l      4
    m      5
    n      6
    o      7
    p      8

As mentioned in the comments this only works for the 0th level and not the 1st. There may be a more a generalizable solution that would work with other levels.

answered Oct 24, 2017 at 0:58

johnchase

13.8k7 gold badges44 silver badges70 bronze badges

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

2 Comments

cs95 Over a year ago

I did ask this question intending to get an answer for just the 0th level, so your answer gives me what I need for now. Thank you!

johnchase Over a year ago

At this point it doesn't seem entirely unreasonable to modify the question to ask for solutions at all levels, but I'm glad it was useful nonetheless!

Collectives™ on Stack Overflow

Filtering rows by a particular index level in a MultiIndex dataframe

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related