CODEWITHSUNDEEP
pythonpandasdataframeindexingpandas-groupby
Iván Sánchez
Iván Sánchez

Reputation: 305

How to get all last rows at second level in MultiIndex DataFrame whose second level has variable length

I have this dataframe:

df = pd.DataFrame({
    'thread_id': [0,0,1,1,1,2,2],
    'message_id_in_thread': [0,1,0,1,2,0,1],
    'text': ['txt0', 'txt1', 'txt2', 'txt3', 'txt4', 'txt5', 'txt6']
}).set_index(['thread_id', 'message_id_in_thread'])

enter image description here

And I want to keep all the last second level rows, meaning that:

This can easily be achieved by doing df.iterrows(), but I would like to know if there is any direct indexing method.

I look for something like df.loc[(:, -1)], which selects from all (:) level 1 groups, the last (-1) row of that block/group, but obviously this does not work.

Upvotes: 2

Views: 670

Answers (1)

jezrael
jezrael

Reputation: 862741

If need both levels use GroupBy.tail:

df = df.groupby(level=0).tail(1)
print (df)
                                text
thread_id message_id_in_thread      
0         1                     txt1
1         2                     txt4
2         1                     txt6

If need only first level use GroupBy.last or GroupBy.nth:

df = df.groupby(level=0).last()
#df = df.groupby(level=0).nth(-1)
print (df)
           text
thread_id      
0          txt1
1          txt4
2          txt6

Upvotes: 2

Related Questions