Flatten Pandas dataframe MultiIndex
Tags: pandas, python, sql, datascience
Published: 2021-10-21 13:31:17

---
title: Flatten Pandas dataframe MultiIndex
published: true
description: How to properly flatten Pandas dataframe MultiIndex
tags: pandas, python, SQL, DataScience
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sntl33jxn6snhradomts.png
---

## Context
A few days ago, I had to extract a data from Oracle Database using SQL, and then `PIVOT` a long set of values in a column, to multiple columns. The sintax to do this, requires to write every single value you want to be as a column, not one but twice to avoid the default quotes.

See reference
https://www.oracle.com/technical-resources/articles/database/sql-11g-pivot.html

There are a few options like pivoting using `PIVOT XML`, or even more recent to build a JSON column using `JSON_ARRAYAGG` and `JSON_OBJECT` to get dynamic columns or any value as an attribute, but still not so straightforward.

##First try:
Lets try with a python notebook (I use VSCode notebook).
First import pandas:
```python
import pandas as pd
```
We will use a random free dataset:
```python
# source file from https://www.kaggle.com/gregorut/videogamesales
df = pd.read_csv('./vgsales.csv')
df
```
![vgsales.csv](https://res.cloudinary.com/rolangom/image/upload/v1634820884/flaten_pandas_df/Screenshot_2021-10-21_084950_ajwvh9.png)

Using the function `pivot_table` we will transponse the values of the column Genre to be a column for every value in the dataset.

```python
pivot_df = df.pivot_table(index=['Year', 'Publisher'], columns=['Genre'], values=['Global_Sales'], aggfunc=np.sum, fill_value=0)
pivot_df
```
![simple df.pivot_table](https://res.cloudinary.com/rolangom/image/upload/v1634820884/flaten_pandas_df/Screenshot_2021-10-21_085018_edj4yv.png)

Now let's `reset_index()` to "try" flattening the indexes.
```python
mi_pivot_df = pivot_df.reset_index()
mi_pivot_df
```
![pivot_df.reset_index](https://res.cloudinary.com/rolangom/image/upload/v1634820884/flaten_pandas_df/Screenshot_2021-10-21_085042_cdqgmk.png)
Ook, not sure, but, lets try to export to an Excel, with `index=False` to avoid showing the index column to the left.
```python
pivot_df.to_excel('./global_sales_by_publishers_genres.xlsx', index=False)
```
![pivot_df.to_excel 1](https://res.cloudinary.com/rolangom/image/upload/v1634820884/flaten_pandas_df/Screenshot_2021-10-21_085123_txirwh.png)
So, it's not implemented. Although, it does work when you remove the `index=False` part. However it will show the index column to the left and the two column indexes, which I don't want to be there.

### Second try
I searched the solution to flat the most, but no answer was full, complete enough, and free of errors. I collected all, and by try and error, got here, a working solution. I also works after using `pivot` function:
```python
flat_index_pivot_df = pivot_df['Global_Sales'].reset_index()
flat_index_pivot_df
```
![df.reset_index 2](https://res.cloudinary.com/rolangom/image/upload/v1634820884/flaten_pandas_df/Screenshot_2021-10-21_085158_id5fzc.png)

Now you can get a clean Excel Sheet, free of MultiIndex.
```python
flat_index_pivot_df.to_excel('./global_sales_by_publishers_genres.xlsx', index=False)
```
Do you know another solution?