Creating Pivot Table with Multiple Columns using Pandas
Pivot Table for Students Report
Let’s create a DataFrame (df
) with columns Name, Subject, Score, and Grade. Subsequently, a pivot table is generated using the pivot_table
method, and the ‘Name’ column is designated as the index. The aggregation functions for score and grade columns are defined using the aggfunc
parameter. For ‘Score’, the mean value is computed and ‘Grade’, the ‘first’ value encountered is chosen as the representative grade.
Python3
import pandas as pd data = { 'Name' : [ 'Shravan' , 'Jeetu' , 'Ram' , 'Jeetu' , 'Shravan' , 'Ram' ], 'Subject' : [ 'DSA' , 'DSA' , 'DSA' , 'DBMS' , 'DBMS' , 'DBMS' ], 'Score' : [ 85 , 90 , 75 , 80 , 88 , 82 ], 'Grade' : [ 'A' , 'A' , 'B' , 'B' , 'A' , 'B' ] } df = pd.DataFrame(data) # Creating a pivot table with multiple columns pivot_table = df.pivot_table(index = 'Name' , columns = 'Subject' , values = [ 'Score' , 'Grade' ], aggfunc = { 'Score' : 'mean' , 'Grade' : 'first' }) print (pivot_table) |
Output:
Grade Score
Subject DBMS DSA DBMS DSA
Name
Jeetu B A 80 90
Ram B B 82 75
Shravan A A 88 85
Pivot Table for Shop Items Report
A structured DataFrame is created to capture information related to dates Date, different categories Category, and corresponding numerical values Value1 and Value2. Setting margins=True
adds subtotals along both the rows and columns, creating a more detailed summary of the data.
Python3
import pandas as pd data = { 'Date' : [ '2022-01-01' , '2022-01-01' , '2022-01-02' , '2022-01-02' ], 'Category' : [ 'A' , 'B' , 'A' , 'B' ], 'Value1' : [ 10 , 15 , 20 , 25 ], 'Value2' : [ 30 , 35 , 40 , 45 ] } df = pd.DataFrame(data) # Create a pivot table with multiple columns with additional parameters pivot_table = df.pivot_table( index = 'Date' , columns = 'Category' , values = [ 'Value1' , 'Value2' ], aggfunc = 'sum' , margins = True , margins_name = 'Total' , sort = True ) print (pivot_table) |
Output:
Value1 Value2
Category A B Total A B Total
Date
2022-01-01 10 15 25 30 35 65
2022-01-02 20 25 45 40 45 85
Total 30 40 70 70 80 150
Creating Pivot Table with Multiple Columns using Python Pandas
PythonPandas make data manipulation, representation and analysis easier. Pandas Pivot Tables are used to create spreadsheet-style pivot tables as a DataFrame. The levels in the pivot table will be stored in MultiIndex objects (hierarchical indexes) on the index and columns of the result DataFrame.
Contact Us