Create a Pivot Table with multiple columns – Python Pandas

A pivot table is a data summarization tool that reorganizes and aggregates data. In Pandas, you can create pivot tables with multiple columns using the pandas.pivot_table() function to create a spreadsheet-style pivot table as a DataFrame.

Syntax

pandas.pivot_table(data, index=None, columns=None, values=None, aggfunc='mean')

Creating a DataFrame

Let's start by creating a DataFrame with team records ?

import pandas as pd

# Create DataFrame with Team records
dataFrame = pd.DataFrame({
    'Team ID': {0: 5, 1: 9, 2: 6, 3: 11, 4: 2, 5: 7},
    'Team Name': {0: 'India', 1: 'Australia', 2: 'Bangladesh', 3: 'South Africa', 4: 'Sri Lanka', 5: 'England'},
    'Team Points': {0: 95, 1: 93, 2: 42, 3: 60, 4: 80, 5: 55},
    'Team Rank': {0: 'One', 1: 'Two', 2: 'Six', 3: 'Four', 4: 'Three', 5: 'Five'}
})

print("Original DataFrame:")
print(dataFrame)

Original DataFrame:
   Team ID    Team Name  Team Points Team Rank
0        5        India           95       One
1        9    Australia           93       Two
2        6   Bangladesh           42       Six
3       11 South Africa           60      Four
4        2    Sri Lanka           80     Three
5        7      England           55      Five

Creating Pivot Table with Multiple Index Columns

You can create a pivot table with multiple columns by passing a list to the index parameter ?

import pandas as pd

dataFrame = pd.DataFrame({
    'Team ID': {0: 5, 1: 9, 2: 6, 3: 11, 4: 2, 5: 7},
    'Team Name': {0: 'India', 1: 'Australia', 2: 'Bangladesh', 3: 'South Africa', 4: 'Sri Lanka', 5: 'England'},
    'Team Points': {0: 95, 1: 93, 2: 42, 3: 60, 4: 80, 5: 55},
    'Team Rank': {0: 'One', 1: 'Two', 2: 'Six', 3: 'Four', 4: 'Three', 5: 'Five'}
})

# Create pivot table with multiple index columns
pivot_result = pd.pivot_table(dataFrame, index=["Team ID", "Team Name", "Team Rank"])
print(pivot_result)

                              Team Points
Team ID Team Name    Team Rank           
2       Sri Lanka    Three               80
5       India        One                 95
6       Bangladesh   Six                 42
7       England      Five                55
9       Australia    Two                 93
11      South Africa Four                60

Pivot Table with Values and Aggregation

You can specify which columns to aggregate and what aggregation function to use ?

import pandas as pd

dataFrame = pd.DataFrame({
    'Team ID': {0: 5, 1: 9, 2: 6, 3: 11, 4: 2, 5: 7},
    'Team Name': {0: 'India', 1: 'Australia', 2: 'Bangladesh', 3: 'South Africa', 4: 'Sri Lanka', 5: 'England'},
    'Team Points': {0: 95, 1: 93, 2: 42, 3: 60, 4: 80, 5: 55},
    'Team Rank': {0: 'One', 1: 'Two', 2: 'Six', 3: 'Four', 4: 'Three', 5: 'Five'}
})

# Pivot table with specific values and aggregation
pivot_result = pd.pivot_table(dataFrame, 
                            index=["Team Name", "Team Rank"], 
                            values=["Team Points", "Team ID"],
                            aggfunc='sum')
print(pivot_result)

                   Team ID  Team Points
Team Name    Team Rank                
Australia    Two              9           93
Bangladesh   Six              6           42
England      Five             7           55
India        One              5           95
South Africa Four            11           60
Sri Lanka    Three            2           80

Key Parameters

Conclusion

Use pandas.pivot_table() with multiple columns in the index parameter to create hierarchical pivot tables. This allows you to organize and summarize data across multiple dimensions effectively.