JIYIK CN >

This article will discuss how to flatten a hierarchical index in a Pandas Dataframe column.

Groupby aggregation functions are often used to create hierarchical indexes. The used aggregation function will be visible in the hierarchical index of the resulting DataFrame.

We will use different functions to explain how to flatten a hierarchical index across columns.

rest_index()Flattening a Hierarchical Index in a Pandas Column Using

The function in Pandas reset_index()flattens the hierarchical index created by the groupby aggregation function.

grammar:

pandas.DataFrame.reset_index(level, drop, inplace)

in,

• level: Remove only the indicated level from the index.
• drop: Reset the index to the default integer index.
• inplace: Do not copy, permanently modify the DataFrame object.

We use the Pandas groupby()function to group the bus sales data by quarter and reset_index()the pandas function to flatten the hierarchical index column of the grouped DataFrame.

First, we import the Python Pandas library and then create a simple DataFrame. The DataFrame is stored in data_busa variable.

import pandas as pd

data_bus = pd.DataFrame(
    {
        "bus": ["2x", "3Tr", "4x", "5x"],
        "bus_sale_q1": [21, 23, 25, 27],
        "bus_sale_q2": [12, 14, 16, 18],
    },
    columns=["bus", "bus_sale_q1", "bus_sale_q2"],
)
print(data_bus)

Output:

     bus    bus_sale_q1    bus_sale_q2
0    2x     21             12
1    3Tr    23             14
2    4x     25             16
3    5x     27             18

The above output shows the simple DataFrame created. After that, groupby()the total column is grouped based on the sum of sales q1 and q2 using the function.

grouped_data = data_bus.groupby(by="bus").agg("sum")
grouped_data

Output:

bus    bus_sale_q1    bus_sale_q2
2x     21             12
3Tr    23             14
4x     25             16
5x     27             18

We will use reset_index()the function to flatten the hierarchical index columns.

flat_data = grouped_data.reset_index()
flat_data

Output:

     bus    bus_sale_q1    bus_sale_q2
0    2x     21             12
1    3Tr    23             14
2    4x     25             16
3    5x     27             18

as_indexFlattening a Hierarchical Index in a Pandas Column Using

The pandas groupby()function will be used to group the bus sales data by quarter, while as_indexthe hierarchical index column of the grouped DataFrame will be flattened.

grammar:

pandas.DataFrame.groupby(by, level, axis, as_index)

in,

• level: The column on which the groupby operation must be performed.
• by: The column on which the groupby operation must be performed.
• axis: Whether to split along rows (0) or columns (1).
• as_index: For aggregate output, returns an object with indexed group labels.

We will groupby()group the bus sales data by quarter using the Pandas function with as_indexthe parameter set to False. This ensures that the hierarchical index of the grouped DataFrame is flattened.

We will use the same DataFrame as in the previous example.

example:

import pandas as pd

data_bus = pd.DataFrame(
    {
        "bus": ["2x", "3Tr", "4x", "5x"],
        "bus_sale_q1": [21, 23, 25, 27],
        "bus_sale_q2": [12, 14, 16, 18],
    },
    columns=["bus", "bus_sale_q1", "bus_sale_q2"],
)
data_bus
grouped_data = data_bus.groupby(by="bus", as_index=False).agg("sum")
print(grouped_data)

Output:

     bus    bus_sale_q1    bus_sale_q2
0    2x     21             12
1    3Tr    23             14
2    4x     25             16
3    5x     27             18