Completing missing combinations of categories in our data with pandas.MultiIndex!
Tags: python, datascience, tutorial, pandas
Published: 2023-02-04 16:00:29

The [`pandas.MultiIndex`](https://pandas.pydata.org/docs/user_guide/advanced.html) is a powerful tool for handling **multi-level indexing** in [`pandas.DataFrames`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.html) giving us increased flexibility for **manipulating, preparing, and analyzing** complex datasets

```python
from pandas import MultiIndex
```

Let's investigate how we can leverage `MultiIndex` to **complete missing combinations** of categories in our datasets with an incredibly elegant solution!

## Table of Contents

- [Understanding missing combinations of categories](#understanding-missing-combinations-of-categories)
- [What is MultiIndex?](#what-is-multiindex)
- [Creating a MultiIndex with all possible combinations of categories](#creating-a-multiindex)
- [Reindexing our DataFrame to align with the MultiIndex](#reindexing-our-dataframe)
- [Conclusion](#conclusion)
- [Additional resources](#additional-resources)

{% embed https://www.christophergreening.com %}

---

## Understanding missing combinations of categories <a src="#understanding-missing-combinations-of-categories"></a>

When it comes to data preparation, a common scenario we encounter is **categorical data** that spans across **multiple levels** of our dataset. While working with this data it's important to understand and identify where our categories might contain **missing combinations**

A good example of this could be **sales data** that spans several geographic **regions** and **product** categories. 

A missing combination might indicate that:

- a particular product is not sold in a certain region 
- OR that the data itself for that region-product combination is **missing**!

Let's take this a step further and look at a simple dataset of `spam` and `eggs` sales in `New York`, `Texas`, and `California` - can you tell which combinations of region and product are missing sales data?:

```python
       region product  sales
0    New York    spam      9
1    New York    eggs      2
2       Texas    eggs      5  <- ?
3  California    spam      7  <- ?
```

In this case, `Texas` is missing a row for `spam` sales and `California` is missing a row for `eggs`! 

Some **important questions** to consider:

- Were there zero sales so those rows were ommitted? 
- Are those products respectively not offered in Texas or California?
- How will these missing combinations affect our analyses?

And instead of just leaving these combinations out it might be imperative to **complete the missing categories** and fill the associated values with zero (or `NA`) to give us a more complete picture of our dataset

```python
       region product  sales
0    New York    spam      9
1    New York    eggs      2
2       Texas    spam      0  <- :D
3       Texas    eggs      5
4  California    spam      7
5  California    eggs      0  <- :D
```

---

## What is MultiIndex? <a src="#what-is-multiindex"></a>

As mentioned in the introduction, `MultiIndex` is a powerful tool for managing `DataFrame`'s that contain **nested** layers, categories, and/or segmentations

In our sales data example from the prior section, we have multiple indices that categorize our sales data by: 
- `region`
- `product`

By leveraging `MultiIndex`, we're able to **encode this hierarchy** into a `DataFrame` and gain access to an elegant toolkit for manipulating, preparing, and analyzing the different levels of our data

---

## Creating a MultiIndex with all possible combinations of categories <a src="#creating-a-multiindex"></a>

Let's take our `spam` and `eggs` raw sales data from earlier now and store it in a `DataFrame` (*let's call it* `sales_df`) 

```python
import pandas as pd

sales_df = pd.DataFrame({
    "region": ["New York", "New York", "Texas", "California"],
    "product": ["spam", "eggs", "eggs", "spam"],
    "sales": [9, 2, 5, 7]
})
```

To create a `MultiIndex` that contains **every possible combination** of the **unique** values in `region` and `product`, we can leverage the `pd.MultiIndex.from_product` method by passing as arguments:

1. a **list of lists** containing *every* unique `region` and *every* unique `product` in our dataset 
2. and a **list of strings** containing the `names` of our columns

```python
unique_categories = [
    sales_df['region'].unique(), 
    sales_df['product'].unique()
]
names = ["region", "product"]
multiindex = pd.MultiIndex.from_product(
    unique_categories,
    names=names
)

>>> print(multiindex)
MultiIndex([(  'New York', 'spam'),
            (  'New York', 'eggs'),
            (     'Texas', 'spam'),  <- wow!
            (     'Texas', 'eggs'),
            ('California', 'spam'),
            ('California', 'eggs')], <- great!
           names=['region', 'product'])
```

Check it out! By taking the **cross-product** of our unique categories, `MultiIndex` went ahead and created every possible combination of `region` and `product` for us 

Thanks MultiIndex, you're the best!

---

## Reindexing our DataFrame to align with the MultiIndex <a src="#reindexing-our-dataframe"></a>

And for the grand finale! We will `reindex` our `DataFrame` to align with our `MultiIndex` **completing missing combinations** and **filling them with zero** in the process!

To do this, we will:
1. [set our `DataFrame`'s index](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.set_index.html) on columns `region` and `product` using our `names` list from the previous section
2. [reindex](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.reindex.html) our `DataFrame` using our `multiindex` and filling missing values with zero
3. and [**reset the index**](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.reset_index.html) to remove the encoded hierarchy

```python
sales_df = (
    sales_df
    .set_index(names)
    .reindex(multiindex, fill_value = 0)
    .reset_index()
)

>>> print(sales_df)
       region product  sales
0    New York    spam      9
1    New York    eggs      2
2       Texas    spam      0  <- yay!
3       Texas    eggs      5
4  California    spam      7
5  California    eggs      0  <- fantastic!
```
---

## Conclusion <a src="#conclusion"></a>

And just like that, we've learned how we can use `pandas.MultiIndex` to complete missing combinations with an incredibly elegant solution

While our product-region example was trivial, this will **scale** to an **arbitrary** amount of categorizations (including **time series** i.e. dates, weeks, and months!)  

So get out there and go complete those missing combinations, *I believe in you!*

![GIF showing spam and eggs sales in New York, Texas, and California with missing combinations of categories getting completed and filled with zero](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w0hq44kwrqq72igy59o6.gif)

If you want to take this a step further and **practice** with sample code and data, I've pulled together a **full working example** for you to explore on [GitHub](https://github.com/chris-greening/chris-greening-blog/blob/main/posts/Completing%20missing%20combinations%20of%20categories%20in%20our%20data%20using%20the%20pandas.MultiIndex/code/completing_categories_with_multiindex.py)!

**Thanks so much for reading** and if you liked my content, be sure to check out some of my other work or **connect** with me on social media or my [**personal website**](https://www.christophergreening.com/) :smile: 

{% embed https://www.christophergreening.com %}

Cheers!

{% embed https://dev.to/chrisgreening/leveraging-the-pipe-method-to-write-beautiful-and-concise-data-transformations-in-pandas-2nc7 %}
{% embed https://dev.to/chrisgreening/connecting-to-a-relational-database-using-sqlalchemy-and-python-1619 %} 

---

## Additional resources <a src="#additional-resources"></a>

- [10 minutes to pandas](https://pandas.pydata.org/docs/user_guide/10min.html)
- [How to Use MultiIndex in Pandas to Level Up Your Analysis by Byron Dolon](https://towardsdatascience.com/how-to-use-multiindex-in-pandas-to-level-up-your-analysis-aeac7f451fce)
- [YouTube: How do I use the MultiIndex in pandas?](https://www.youtube.com/watch?v=tcRGa2soc-c&t=358s)