How to save a subset of a multi-data index in a new data frame?

Question

I have a multi data index:

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

Now I can 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 now I want to select a subset again df_subs, for example

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', :]

crashes and gives an error

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

Why is this failing?

EDIT

Initially, I had two questions in this post. I divided it into two, the second question can be found here .

Answer 1

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 do you need a specific fragment in a 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

Reason why .loc[:, 'c', :]it cannot work:

.loc, . , , MuliIndex .

Link1

Link2

Answer 2

-, .loc , reset. .copy() - .

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'])

, :

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'])

,

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'])

, .loc (# 2) , :

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