---
jupytext:
  formats: content_md///md:myst,content///ipynb
  text_representation:
    extension: .md
    format_name: myst
    format_version: 0.13
    jupytext_version: 1.16.4
kernelspec:
  display_name: Python 3 (ipykernel)
  language: python
  name: python3
---

(managing-data:pandas:advanced-indexing)=
# Advanced Indexing

One of Pandas' most useful features is its powerful indexing mechanism. Here we'll discuss several types of index objects.

```{code-cell} ipython3
import pandas as pd
```

Series and data frames have one or two index objects, respectively. They are accessible via `Series.index` or `DataFrame.index` and `DataFrame.columns`. An index object is a list-like object holding all row or column labels.

To create a new index, call the constructor of the `Index` class:

```{code-cell} ipython3
new_index = pd.Index(['a', 'b', 'c', 'd', 'e'])
new_index
```

## Reindexing

An index object may be replaced by another one. We have to take care whether data alignment between old and new index shall be applied or not.

### With Data Alignment

`Series` and `DataFrame` objects provide the [`reindex`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.reindex.html) method. This method takes an index object (or a list of labels) and replaces the existing index by the new one. Data alignment is applied, that is, rows/columns with label in the intersection of old and new index remain unchanged, but rows/columns with old label not in the new index are dropped. If there are labels in the new index which aren't in the old one, then rows/columns are filled with `nan` or some specified value or by some more complex filling logic.

```{code-cell} ipython3
s = pd.Series({'a': 123, 'b': 456, 'e': 789})
print(s, '\n')

new_index = pd.Index(['a', 'b', 'c', 'd', 'e'])
s = s.reindex(new_index)
s
```

With fill value:

```{code-cell} ipython3
s = pd.Series({'a': 123, 'b': 456, 'e': 789})
print(s, '\n')

new_index = pd.Index(['a', 'b', 'c', 'd', 'e'])
s = s.reindex(new_index, fill_value=0)
s
```

With filling logic:

```{code-cell} ipython3
s = pd.Series({'a': 123, 'b': 456, 'e': 789})
print(s, '\n')

new_index = pd.Index(['a', 'b', 'c', 'd', 'e'])
s = s.reindex(new_index, method='bfill')
s
```

The [`align`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.align.html) method reindexes two series/data frames such that both have the same index.

```{code-cell} ipython3
s1 = pd.Series({'a': 123, 'b': 456, 'e': 789})
s2 = pd.Series({'a': 98, 'c': 76, 'e': 54})
print(s1, '\n')
print(s2, '\n')

s1, s2 = s1.align(s2, axis=0)
print(s1, '\n')
print(s2)
```

### Without Data Alignment

To simply replace an index without data alignment, that is to rename all the labels, there are two variants:
* replace the index object by a new one of same length via usual assignment,
* use an existing column as index.

```{code-cell} ipython3
s = pd.Series({'a': 123, 'b': 456, 'e': 789})
print(s, '\n')

new_index = pd.Index(['aa', 'bb', 'cc'])
s.index = new_index
s
```

To use a column of a data frame as index call the [`set_index`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.set_index.html) method and provide the column label.

```{code-cell} ipython3
df = pd.DataFrame([[1, 2, 3], [4, 5, 6], [7, 8, 9]],
                  index=['a', 'b','c'], columns=['A', 'B', 'C'])
display(df)

df = df.set_index('A')
df
```

To convert the index to a usual column call [`reset_index`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.reset_index.html). The index will be replaced by the standard index (integers starting at 0).

```{code-cell} ipython3
df = df.reset_index()
df
```

## Index Sharing

Index objects may be shared between several series or data frames. Simply pass the index of an existing series or data frame to the constructor of a new series or data frame or assign it directly or use `reindex`.

```{code-cell} ipython3
s1 = pd.Series({'a': 123, 'b': 456, 'e': 789})
s2 = pd.Series([4, 6, 8], index=s1.index)

s1.index is s2.index
```

```{note}
Reindexing two series/data frames with `align` (see above) results in a shared index.
```

## Enlargement by Assignment

To append data to a series or data frame we may use label based indexing:

```{code-cell} ipython3
s = pd.Series({'a': 123, 'b': 456})
print(s, '\n')

s.loc['e'] = 789
s
```

```{code-cell} ipython3
df = pd.DataFrame([[1, 2], [3, 4]], index=['a', 'b'], columns=['A', 'B'])
display(df)

df.loc['c', 'D'] = 5
df
```

## Range Indices

Pandas' standard index is of type [`RangeIndex`](https://pandas.pydata.org/docs/reference/api/pandas.RangeIndex.html). It's used whenever a series or a data frame is created without specifying an index.

```{code-cell} ipython3
index = pd.RangeIndex(5, 21, 2)
print(index, '\n')

for k in index:
    print(k)
```

## Interval Indices

The [`IntervalIndex`](https://pandas.pydata.org/docs/reference/api/pandas.IntervalIndex.html) class allows for imprecise indexing. Each item in a series or data frame can be accessed by any number in a specified interval.

```{code-cell} ipython3
interval_list = [pd.Interval(2, 3), pd.Interval(6, 7), pd.Interval(6.5, 9)]
print(interval_list, '\n')

s = pd.Series([23, 45, 67], index=pd.IntervalIndex(interval_list, closed='left'))
s
```

Indexing by concrete numbers:

```{code-cell} ipython3
print(s.loc[2], '\n')
# print(s.loc[3], '\n')    # KeyError
print(s.loc[2.5], '\n')
print(s.loc[6.7])
```

Indexing by intervals:

```{code-cell} ipython3
# print(s.loc[pd.Interval(2, 3)])    # KeyError
print(s.loc[pd.Interval(2, 3, 'left')])    # only exact matches!
```

`IntervalIndex` objects provide [`overlaps`](https://pandas.pydata.org/docs/reference/api/pandas.IntervalIndex.overlaps.html) and [`contains`](https://pandas.pydata.org/docs/reference/api/pandas.IntervalIndex.contains.html) methods for more flexible indexing:

```{code-cell} ipython3
mask = s.index.overlaps(pd.Interval(2.5, 6.4))
print(mask)
s.loc[mask]
```

```{code-cell} ipython3
mask = s.index.contains(6.7)
print(mask)
s.loc[mask]
```

```{note}
In principle `contains` should work with intervals instead of concrete numbers, too. But in Pandas 2.2.3 `NotImplementedError` is raised.
```

## Multi-Level Indexing

Up to some details, multi-level indexing is indexing using tuples as labels. Corresponding indexing objects are of type [`MultiIndex`](https://pandas.pydata.org/docs/reference/api/pandas.MultiIndex.html). Major application of multi-level indices are indices representing several dimensions. Thus, high dimensional data can be stored in a two-dimensional data frame.

### Creating a Multi-Level Index

Let's start with a two-level index. First level contains courses of studies provided by a university. Second level contains some lecture series. Data is the number of students from each course attending a lecture and the average rating for each lecture.

Using the `MultiIndex` constructor is the most general, but not very straight forward way to create a multi-level index. We have to provide lists of labels for each level. In addition, we need lists of codes for each level indicating which label to use at each position. Each level may have a name.

```{code-cell} ipython3
courses = ['Mathematics', 'Physics', 'Philosophie']
lectures = ['Computer Science', 'Mathematics', 'Epistemology']

courses_codes =  [0, 0, 0, 1, 1, 1, 2, 2, 2]
lectures_codes = [0, 1, 2, 0, 1, 2, 0, 1, 2]

index = pd.MultiIndex(levels=[courses, lectures],
                      codes=[courses_codes, lectures_codes],
                      names=['course', 'lecture'])

data = zip([10, 15, 8, 20, 17, 3, 2, 1, 89],
           [2.1, 1.3, 3.6, 3.0, 1.6, 4.7, 3.9, 4.9, 1.1])

df = pd.DataFrame(data, index=index, columns=['students', 'rating'])

df
```

Alternative creation methods are [`MultiIndex.from_arrays`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.MultiIndex.from_arrays.html), [`MultiIndex.from_tuples`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.MultiIndex.from_tuples.html), [`MultiIndex.from_product`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.MultiIndex.from_product.html), [`MultiIndex.from_frame`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.MultiIndex.from_frame.html).

```{hint}
The mentioned creation methods are `static` methods. See [](python:variables:types) for some explanation of the concept.
```

The above index contains each combination of items from two lists. Thus `from_product` is applicable:

```{code-cell} ipython3
courses = ['Mathematics', 'Physics', 'Philosophie']
lectures = ['Computer Science', 'Mathematics', 'Epistemology']

index = pd.MultiIndex.from_product([courses, lectures], names=['course', 'lecture'])

data = zip([10, 15, 8, 20, 17, 3, 2, 1, 89],
           [2.1, 1.3, 3.6, 3.0, 1.6, 4.7, 3.9, 4.9, 1.1])

df = pd.DataFrame(data, index=index, columns=['students', 'rating'])

df
```

Level information is stored in the `levels` member variable:

```{code-cell} ipython3
df.index.levels
```

Note, that multi-level indexing is not restricted to row indexing. Multi-level column indexing works in exactly the same manner.

### Accessing Data

Accessing data works as for other types of indices. Labels now are tuples containing one item per level. But there exist additional techniques specific to multi-level indices.

#### Single Tuples

```{code-cell} ipython3
df.loc[('Physics', 'Computer Science'), :]
```

```{code-cell} ipython3
df.iloc[1, 1]
```

```{code-cell} ipython3
df.loc[[('Physics', 'Computer Science'), ('Mathematics', 'Epistemology')], :]
```

Slicing works as usual.

#### Slicing Inside Tuples

A new feature specific to multi-level indexing is slicing inside tuples. We would expect notation like
```python
('Physics', :)
```
to get all rows with `Physics` at first level. But usual slicing syntax is not available here. Instead we have to use the built-in `slice` function. It takes start, stop and step values (start and step default to `None`) and returns a `slice` object. More precisely, the `slice` function is the constructor for `slice` objects. A slice object simply holds three values (start, stop, step).

```{code-cell} ipython3
df.loc[('Physics', slice(None)), :]
```

With `slice(None)` we create a slice object interpreted as *all* (analogously to `:`).

Slicing in the first level works, too.

```{code-cell} ipython3
df = df.sort_index()
df.loc[(slice('Mathematics', 'Physics'), 'Epistemology'), :]
```

Note that label based slicing above requires a sorted index. Thus, we have to call [`sort_index`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.sort_index.html) first.

An alternative to `slice` is creating a `pd.IndexSlice` object, which allows for natural slicing syntax:

```{code-cell} ipython3
df.loc[pd.IndexSlice['Mathematics':'Physics', 'Epistemology'], :]
```

#### Building a Mask Based on a Level

The [`get_level_values`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Index.get_level_values.html) method of an index object takes a level name or level index as argument and returns a simple `Index` object containing only the labels at the specified level. This object can then be used to create a boolean array for row indexing.

```{code-cell} ipython3
import numpy as np

no_physics_mask = df.index.get_level_values('course') != 'Physics'
no_epistemology_mask = df.index.get_level_values(1) != 'Epistemology'

mask = np.logical_and(no_physics_mask, no_epistemology_mask)

df.loc[mask, :]
```

Comparing an index object with a single value results in a one-dimensional boolean NumPy array with same length as the index object. NumPy's `logical_and` method implements elementwise *logical and*.

#### Cross-Sections

There's a shorthand for selecting all rows with a given label at a given level: [`xs`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.xs.html). This method takes a label and a level and returns corresponding rows as data frame.

```{code-cell} ipython3
df.xs('Mathematics', level='lecture')
```

### The `level` Keyword Argument

Many Pandas functions accept a `level` keyword argument (like `xs` above or [`drop`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.drop.html)) to provide functionality adapted to multi-level indices.

### More on Multi-Level Indexing

There are many different styles for using multi-level indexing. Some of them are very confusing for beginners, because same syntax may have different semantics depending on the objects (row/column label, tuple, list) passed as arguments. Here we only considered save and syntactically clear variants. To get an idea of other indexing styles have a look at [MultiIndex / advanced indexing](https://pandas.pydata.org/pandas-docs/stable/user_guide/advanced.html) in the Pandas user guide.
