1

I have this DataFrame that has a MultiIndex with the fiels Hi, Md, Lo

import pandas as pd

df = pd.DataFrame(
    [
        ["A", "X", "C", "7", "3"],
        ["A", "B", "C", "7", "2"],
        ["A", "B", "C", "7", "1"],
        ["A", "X", "C", "7", "3"],
        ["1", "2", "3", "7", "3"],
        ["4", "5", "6", "7", "3"],
    ],
    columns=["Hi", "Md", "Lo", "O", "I"],
).set_index(["Hi", "Md", "Lo"])

It looks like this

          O  I
Hi Md Lo      
A  X  C   7  3
   B  C   7  2
      C   7  1
   X  C   7  3
1  2  3   7  3
4  5  6   7  3

How can I set a filter to get only rows where the Md has the value B

I tried via field name

df[df['Md'] == 'B']

This raises a KeyError, because the "field" is not column, but part of the MultiIndex

df[df.index.map(lambda x: x[1] == 'B')]

works, but is very hacky.

Is there a shorter way to write this filter, maybe using the field name Md or the index position 1?

576i
  • 7,579
  • 12
  • 55
  • 92
  • 1
    `df.xs('B', level='Md', axis='index')` (xs = "cross section") – Paul H Mar 02 '21 at 18:27
  • This question (mine) from _years_ ago, is a bit outdated, but the answer still applies https://stackoverflow.com/questions/12047418/how-do-i-really-use-the-ix-method-of-a-pandas-dataframe – Paul H Mar 02 '21 at 18:28

2 Answers2

2

You can use DataFrame.query

df.query("Md == 'B'")

Output

          O  I
Hi Md Lo      
A  B  C   7  2
      C   7  1

Alternative (Might not work with different data)

df.filter(like='B', axis=0)
Vishnudev Krishnadas
  • 10,679
  • 2
  • 23
  • 55
1

Here is a way by passing a tuple in to df.loc

df.loc[(slice(None),'B',slice(None))]
rhug123
  • 7,893
  • 1
  • 9
  • 24