Removing Multilevel Indexes in Pandas DataFrames: Practical Examples and Techniques

Removing Multilevel Index Using Pivot Table

Let’s look at some practical examples to illustrate how to remove the multilevel index in different scenarios.

Example 1: Sales Data

Consider a sales dataset with multiple levels of indexing.

Python

sales_data = {
    'Date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02'],
    'Store': ['A', 'B', 'A', 'B'],
    'Product': ['X', 'Y', 'X', 'Y'],
    'Sales': [100, 200, 150, 250]
}

df = pd.DataFrame(sales_data)
pivot_df = df.pivot_table(values='Sales', index=['Date', 'Store'], columns='Product', aggfunc='sum')
print(pivot_df)

Output:

Product         X    Y
Date       Store      
2023-01-01 A     100  NaN
           B     NaN  200
2023-01-02 A     150  NaN
           B     NaN  250

To remove the multilevel index:

Python

flat_df = pivot_df.reset_index()
print(flat_df)

Output:

Product        Date Store      X      Y
0        2023-01-01     A  100.0    NaN
1        2023-01-01     B    NaN  200.0
2        2023-01-02     A  150.0    NaN
3        2023-01-02     B    NaN  250.0

Example 2: Financial Data

Consider a financial dataset with multiple levels of indexing.

Python

financial_data = {
    'Year': [2021, 2021, 2022, 2022],
    'Quarter': ['Q1', 'Q2', 'Q1', 'Q2'],
    'Revenue': [1000, 1500, 2000, 2500],
    'Profit': [200, 300, 400, 500]
}

df = pd.DataFrame(financial_data)
pivot_df = df.pivot_table(values=['Revenue', 'Profit'], index=['Year', 'Quarter'], aggfunc='sum')
print(pivot_df)

Output:

           Profit  Revenue
Year Quarter                
2021 Q1       200     1000
     Q2       300     1500
2022 Q1       400     2000
     Q2       500     2500

To remove the multilevel index:

Python

flat_df = pivot_df.reset_index()
print(flat_df)

Output:

   Year Quarter  Profit  Revenue
0  2021      Q1     200     1000
1  2021      Q2     300     1500
2  2022      Q1     400     2000
3  2022      Q2     500     2500

How to Get Rid of Multilevel Index After Using Pivot Table in Pandas

Pandas is a powerful and versatile library in Python for data manipulation and analysis. One of its most useful features is the pivot table, which allows you to reshape and summarize data. However, using pivot tables often results in a multilevel (hierarchical) index, which can be cumbersome to work with. In this article, we will explore how to get rid of the multilevel index after using a pivot table in Pandas, making your data easier to handle and analyze.

Table of Content

Understanding Pivot Tables in Pandas
Understanding Multilevel Index
Removing Multilevel Index Using Pivot Table

1. Using reset_index()
2. Using droplevel()
3. Using rename_axis()