Search code examples
pythonpandasdataframemulti-index

Selecting all rows based on last secondary index for each primary index

Given a multi-index dataframe with varying number of secondary indices, how can I select the last secondary index for all primary indices? Example df:

              THill
Elm    Ply         
100000 1    0.22865
       2    0.22847
       3    0.33411
       4    0.33370
100001 1    0.22919
       2    0.22907
       3    0.33480
       4    0.33436
       5    0.22828
       6    0.22801

The desired result would be:

Elm    THill
100000 0.33370 (from Ply=4)
100001 0.22801 (from Ply=6)

`

I can select a given Ply such as df.xs(4,level='Ply') but how do I select all last secondary indices?

Solution

  • One possible solution to this problem is grouping along the second level of the index and calling groupby.last:

    df.groupby(level=0).last()

    Alternatively, you can use tail in the same manner (thanks, Wen!):

    df.groupby(level=0).tail(1)