CODEWITHSUNDEEP
pandasmulti-index
Elle518
Elle518

Reputation: 59

Sorted MultiIndex DataFrame indexig by index number

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.

Upvotes: 2

Views: 42

Answers (3)

qmeeus
qmeeus

Reputation: 2412

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]

Upvotes: 3

anky
anky

Reputation: 75120

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

Upvotes: 2

BENY
BENY

Reputation: 323356

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

Upvotes: 1

Related Questions