Sorted MultiIndex DataFrame indexig by index number

user_4359229 0
Viewed 35

I've a MultiIndex DataFrame as follows:

header = pd.MultiIndex.from_product([['#'],
                                     ['TE', 'SS', 'M', 'MR']])
dat = ([[100, 20, 21, 35], [100, 12, 5, 15]])
df = pd.DataFrame(dat, index=['JC', 'TTo'], columns=header)
df = df.stack()
df = df.sort_values('#', ascending=False).sort_index(level=0, sort_remaining=False)

enter image description here

And I want to get the next rows indexig by index number not by name, that is the third row of every level 0 index:

JC   M   21
TTo  SS  12

Of all that I have tried, what is closest to what I am looking for is:

df.loc[pd.IndexSlice[:, df.index[2]], '#']

But this doesn't work also as intended.

multi-index pandas
3 Answers

You can do the following:

df["idx"] = df[df.groupby(level=0).cumcount() == 2]
df.loc[df.idx == 2]

One line solution from Quang Hoang:

df[df.groupby(level=0).cumcount() == 2]
user_75716730

Another way using df.xs:

df.set_index(df.groupby(level=0).cumcount()+1,append=True).xs(3,level=2)

         #
JC  M   21
TTo SS  12
user_98406370

Try with groupby then

out = df.groupby(level=0).apply(lambda x: x.iloc[[2]])
Out[141]: 
             #
JC  JC  SS  20
TTo TTo SS  12
user_79645270
Related