How to store subset of a multiindex dataframe in a new dataframe?

user_1534017 0
Viewed 421

I have a multiindex dataframe like this:

import pandas as pd
import numpy as np


df = pd.DataFrame({'ind1': list('aaaaaaaaabbbbbbbbb'),
                   'ind2': list('cccdddeeecccdddeee'),
                   'ind3': list(range(3))*6,
                   'val1': list(range(100, 118)),
                   'val2': list(range(70, 88))})

df_mult = df.set_index(['ind1', 'ind2', 'ind3'])

                val1  val2
ind1 ind2 ind3            
a    c    0      100    70
          1      101    71
          2      102    72
     d    0      103    73
          1      104    74
          2      105    75
     e    0      106    76
          1      107    77
          2      108    78
b    c    0      109    79
          1      110    80
          2      111    81
     d    0      112    82
          1      113    83
          2      114    84
     e    0      115    85
          1      116    86
          2      117    87

I can now select a subset of it using .loc like this

df_subs = df_mult.loc['a', ['c', 'd'], :]

which gives the expected

                val1  val2
ind1 ind2 ind3            
a    c    0      100    70
          1      101    71
          2      102    72
     d    0      103    73
          1      104    74
          2      105    75

If I now want to select again a subset of df_subs, e.g.

df_subs.loc['a', 'c', :]

works and gives

      val1  val2
ind3            
0      100    70
1      101    71
2      102    72

however

df_subs.loc[:, 'c', :]

fails and gives an error

KeyError: 'the label [c] is not in the [columns]'

Why does this fail?

EDIT

Originally, I had two question in this post. I splitted it into two, the second question can be found here.

multi-index pandas dataframe select python
2 Answers

By using IndexSlice:

idx = pd.IndexSlice
df_subs.loc[idx[:, 'c',:],:]
Out[159]: 
                val1  val2
ind1 ind2 ind3            
a    c    0      100    70
          1      101    71
          2      102    72

Or you need to specific slice on row or column

df_subs.loc(axis=0)[:, 'c', :]
Out[196]: 
                val1  val2
ind1 ind2 ind3            
a    c    0      100    70
          1      101    71
          2      102    72

The reason why .loc[:, 'c', :] can not work :

You should specify all axes in the .loc specifier, meaning the indexer for the index and for the columns. There are some ambiguous cases where the passed indexer could be mis-interpreted as indexing both axes, rather than into say the MuliIndex for the rows.

Link1

Link2

user_79645270

Apparently, using .loc may persist the indices in its original form until they are reset. Using .copy() to avoid any views of original dataframe still persists multindex value.

df_subs = df_mult.loc['a', ['c', 'd'], :].copy()

print(df_subs.index)
# MultiIndex(levels=[['a', 'b'], ['c', 'd', 'e'], [0, 1, 2]],
#            labels=[[0, 0, 0, 0, 0, 0], [0, 0, 0, 1, 1, 1], [0, 1, 2, 0, 1, 2]],
#            names=['ind1', 'ind2', 'ind3'])

Additionally, filtering by values still retains multindex values:

df_subs = df_mult[df_mult['val1'] <= 105]

print(df_subs)
#                 val1  val2
# ind1 ind2 ind3            
# a    c    0      100    70
#           1      101    71
#           2      102    72
#      d    0      103    73
#           1      104    74
#           2      105    75

print(df_subs.index)
# MultiIndex(levels=[['a', 'b'], ['c', 'd', 'e'], [0, 1, 2]],
#            labels=[[0, 0, 0, 0, 0, 0], [0, 0, 0, 1, 1, 1], [0, 1, 2, 0, 1, 2]],
#            names=['ind1', 'ind2', 'ind3'])

Therefore, consider manually resetting index, following your original assignment

df_subs = df_mult.loc['a', ['c', 'd'], :].reset_index()

df_subs = df_subs.set_index(['ind1', 'ind2', 'ind3'])

print(df_subs.index)
# MultiIndex(levels=[['a'], ['c', 'd'], [0, 1, 2]],
#            labels=[[0, 0, 0, 0, 0, 0], [0, 0, 0, 1, 1, 1], [0, 1, 2, 0, 1, 2]],
#            names=['ind1', 'ind2', 'ind3'])

Finally, for last .loc assignment (#2), provide at least the first index as may be required:

df_subs2 = df_subs.loc['a', 'c', :]
#       val1  val2
# ind3            
# 0      100    70
# 1      101    71
# 2      102    72
user_14224510
Related