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?
@sacul has the most idiomatic answer, but here are a few alternatives.
MultiIndex.get_level_valuesdf[df.index.get_level_values('name') == 'Ai']
value
year name
1921 Ai 90
1922 Ai 7
DataFrame.querydf.query('name == "Ai"')
value
year name
1921 Ai 90
1922 Ai 7
DataFrame.loc(axis=0) with pd.IndexSliceSimilar 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
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
If you prefer loc, you can use:
In [245]: df.loc[(slice(None), 'Ai'), :]
...:
Out[245]:
value
year name
1921 Ai 90
1922 Ai 7
df.loc[pd.IndexSlice[:, 'Ai'], :].
