To remove a multiIndex from a pandas pivot table, you can use the values argument along with the reset_index() function:
pd.pivot_table(df, index='col1', columns='col2', values='col3').reset_index()
The following example shows how to use this syntax in practice.
Example: Remove MultiIndex in Pandas Pivot Table
Suppose we have the following pandas DataFrame that contains information about various basketball players:
import pandas as pd #create DataFrame df = pd.DataFrame({'team': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'], 'position': ['G', 'G', 'F', 'F', 'G', 'F', 'F', 'F'], 'points': [4, 4, 6, 8, 9, 5, 5, 12]}) #view DataFrame print(df) team position points 0 A G 4 1 A G 4 2 A F 6 3 A F 8 4 B G 9 5 B F 5 6 B F 5 7 B F 12
Now suppose we create the following pivot table to summarize the mean value of points by team and position:
#create pivot table to summarize mean points by team and position
pd.pivot_table(df, index='team', columns='position')
points
position F G
team
A 7.000000 4.0
B 7.333333 9.0
The resulting pivot table summarizes the mean value of points by team and position, but contains a multiIndex.
To remove the multiIndex, we can use the values argument within the pivot_table() function and add reset_index() to the end:
#create pivot table to summarize mean points by team and position
pd.pivot_table(df, index='team', columns='position', values='points').reset_index()
position team F G
0 A 7.000000 4.0
1 B 7.333333 9.0
The resulting pivot table summarizes the mean value of points by team and position and no longer has a multiIndex.
Note that the pivot_table() function calculates the mean value by default.
To calculate a different metric, such as the sum, use the aggfunc argument as follows:
#create pivot table to summarize sum of points by team and position
pd.pivot_table(df, index='team', columns='position', values='points',
aggfunc='sum').reset_index()
position team F G
0 A 14 8
1 B 22 9
The resulting pivot table summarizes the sum of values of points by team and position and also has no multiIndex.
Note: You can find the complete documentation for the pandas pivot_table() function here.
Additional Resources
The following tutorials explain how to perform other common operations in pandas:
Pandas: How to Add Filter to Pivot Table
Pandas: How to Sort Pivot Table by Values in Column
Pandas: How to Add Subtotals to Pivot Table