Selection with pandas multiIndexed dataframe

Question

I have a multiIndexed dataframe that looks like this:

df.head():

How can I select all of the rows where the first index == "particular school name" and all of second indices, where the Month column == "Jan"?

I haven't worked with multiIndexed dataframes before, I can select all rows where Month == "Jan" like this:

df[df['Month'] == 'Jan']

but this gives me all of the schools. I've been playing with it, but haven't been able to add indexing for just one school. So how does this work?

Edit: Ok so this works: df[df['Month'] == 'Mar'].loc["School Name"], but is there a more idiomatic way this is done or would this be the standard way?

Answer 1

You can use query:

print (df.query('ilevel_0 == "School Name" and Month == "Jan"'))

Sample:

df = pd.DataFrame({'A':['School Name','Agona','another'],
                   'B':[0,1,2],
                   'Month':['Jan', 'Jan', 'Feb']}).set_index(['A','B'])
df.index.names = [None, None]
print (df)
              Month
School Name 0   Jan
Agona       1   Jan
another     2   Feb

print (df.query('ilevel_0 == "School Name" and Month == "Jan"'))
              Month
School Name 0   Jan

print (df.query('ilevel_0 == "School Name" & Month == "Jan"'))
              Month
School Name 0   Jan

Solution with boolean indexing:

mask = (df.index.get_level_values(0) == 'School Name') & (df['Month'] == 'Jan')
print (df[mask])
              Month
School Name 0   Jan

EDIT:

For working with variable use @:

var = 'School Name'
print (df.query('ilevel_0 == @var & Month == "Jan"'))
              Month
School Name 0   Jan