Access last elements of inner multiindex level in pandas dataframe

Question

In a multi index pandas dataframe I want to access the last element of the second index for all values of the first index. The number of levels in the second index vary depending on the value of the first index. I went through the pandas multi index documentation but could not find anything that does that.

For example, for the data frame below:

arrays = [ ['bar', 'bar', 'baz', 'foo', 'foo', 'foo',   'qux'],
           ['one', 'two', 'one', 'one', 'two', 'three', 'one']]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])
df = pd.DataFrame(np.random.randn(7, 3), index=index, columns=['A', 'B', 'C'])
df
                 A         B         C
first second
bar   one     0.289163 -0.464633 -0.060487
      two     0.224442  0.177609  2.156436
baz   one    -0.262329 -0.248384  0.925580
foo   one     0.051350  0.452014  0.206809
      two     2.757255 -0.739196  0.183735
      three  -0.064909 -0.963130  1.364771
qux   one    -1.330857  1.881588 -0.262170

I want to get:

                 A         B         C
first second
bar   two     0.224442  0.177609  2.156436
baz   one    -0.262329 -0.248384  0.925580
foo   three  -0.064909 -0.963130  1.364771
qux   one    -1.330857  1.881588 -0.262170

The dataframes I am working with have over 10M lines so I want to avoid explicit looping.

You could do `df.groupby(level='first').last()` – EdChum Jun 27 '16 at 12:15 — EdChum, Jun 27 '16 at 12:15

jezrael · Accepted Answer · 2016-06-27T12:29:14.760

14

Use groupby with tail:

print (df.groupby(level='first').tail(1))
                     A         B         C
first second                              
bar   two     0.053054 -0.555819  0.589998
baz   one    -0.868676  1.293633  1.339474
foo   three   0.407454  0.738872  1.811894
qux   one    -0.346014 -1.491270  0.446772

because last lost level second:

print (df.groupby(level='first').last())         
              A         B         C
first                              
bar    0.053054 -0.555819  0.589998
baz   -0.868676  1.293633  1.339474
foo    0.407454  0.738872  1.811894
qux   -0.346014 -1.491270  0.446772

edited Jun 27 '16 at 12:29

answered Jun 27 '16 at 12:16

jezrael

822,522
95
1,334
1,252

Using the last command got me a `FutureWarning` stating that I should use a list instead – Eulenfuchswiesel Apr 05 '23 at 12:57
1

@Eulenfuchswiesel - What is your code exactly? Seems have `df.groupby(level='first')['col1','col2'].last()`, need `df.groupby(level='first')[['col1','col2']].last()` – jezrael Apr 05 '23 at 12:58
1

Thanks, that was exactly the issue! I added the parenthesis at the wrong position – Eulenfuchswiesel Apr 05 '23 at 13:02

Access last elements of inner multiindex level in pandas dataframe

1 Answers1

Linked