In this tutorial, we'll see how to select values with .loc()
on multi-index in Pandas DataFrame.
Here are quick solutions for selection on multi-index:
(1) Select first level of MultiIndex
(2) Select columns - MultiIndex
(3) Conditional selection on level of MultiIndex
Setup
For this article we're going to use two DataFrames:
- first one with multi index on columns
- second one with multi index on rows
data:
company A | company B | |||
---|---|---|---|---|
rank | points | rank | points | |
0 | 1 | 2 | 3 | 4 |
1 | 2 | 3 | 3 | 4 |
The second one:
data
Grade | |||
---|---|---|---|
11 | 21 | 31 | A |
22 | 32 | B | |
12 | 21 | 33 | A |
22 | 34 | C |
Step 1: .loc() and MultiIndex
Pandas method .loc()
can select on multi-index.
To find out what are the index values we can use method: df2.index
which will give us:
MultiIndex([('11', '21', '31'),
('11', '22', '32'),
('12', '21', '33'),
('12', '22', '34')],
)
To select values from the multi index above we can use following syntax:
which give us:
Grade A
Name: (11, 21, 31), dtype: object
Step 2: Select first level of multi-index
To select first level of multiindex we can use method loc()
and provide list of values:
result:
Grade | |||
---|---|---|---|
11 | 21 | 31 | A |
22 | 32 | B |
To select multiple values from the first level we can use:
Step 3: Select second level of multi-index
To select second or N-th level from multi index in Pandas DataFrame we can use slice(None)
or method get_level_values()
:
or
result in selection of the second level of this multi-index:
Grade | |||
---|---|---|---|
11 | 21 | 31 | A |
Alternatively we can create IndexSlice object:
to get the same result.
Step 4: Conditional selection on multi-index
For conditional selection on multi index in pandas we can use method get_level_values()
and mask:
In this way we can combine multiple conditions:
- select first level - value '11'
- or second level - value '22'
Grade | |||
---|---|---|---|
11 | 21 | 31 | A |
22 | 32 | B | |
12 | 22 | 34 | C |
Step 5: Query selection on multi-index
If our multi-index has named levels we can use queries to select data:
Step 6: Select multi-index column
All from above apply to columns. For columns we need to pass criteria as second value:
result:
company A rank 1
Name: 0, dtype: int64
Conclusion
In this article, we looked at different solutions for selection and quering data from Pandas Multi-Index. We focused on row/index selection, column selection is exactly the same.
We covered conditional selection, selection from first or second level and queries on Multi-Index DataFrame.