Using .loc on just second index in multiindex

Question

I have multiindex dataframe that looks like this:

                value
year    name                
1921    Ah      40     
1921    Ai      90      
1922    Ah      100     
1922    Ai      7

in which year and name are the indices. I want to select every row where the name Ai appears. I have tried df.loc[(:,'Ai')] and df.loc['Ai'] but both give errors. How do I index only using the name column?

Answer 1

@sacul has the most idiomatic answer, but here are a few alternatives.

MultiIndex.get_level_values

df[df.index.get_level_values('name') == 'Ai']

           value
year name       
1921 Ai       90
1922 Ai        7

---

DataFrame.query

df.query('name == "Ai"')

           value
year name       
1921 Ai       90
1922 Ai        7

---

DataFrame.loc(axis=0) with pd.IndexSlice

Similar to @liliscent's answer, but does not need the trailing : if you specify axis=0.

df.loc(axis=0)[pd.IndexSlice[:, 'Ai']]

           value
year name       
1921 Ai       90
1922 Ai        7

Answer 2

I would use .xs on the first level of your multiindex (note: level=1 refers to the "second" index (name) because of python's zero indexing: level 0 is year in your case):

df.xs('Ai', level=1, drop_level=False)
# or
df.xs('Ai', level='name', drop_level=False)

           value
year name       
1921 Ai       90
1922 Ai        7