Python is nowadays a very common language to use, especially when you want to automate something. Hence, we will go with Python to automate our today’s flow wherein we will generate a pivot table using Python and then we will save it back to Microsoft Excel.
Input Data
I’m considering CSV file as an input, which holds multiple columns as shown in the below sample:
![]()
Scenario
Let’s consider a scenario, wherein we want to generate a pivot table which depicts the interest of students based on their residence. For example, say you want to know how many students are falling under a certain category from Rural, then pivot table should be able to provide us with this data.
Generating Pivot Table
The very first thing we need to do is to grab the CSV data and bring it into the memory, so that we can perform operations on it.
Yes, you guessed it right. We can go for a pandas data frame. Once data is available in a data frame, we can filter out the required columns and start our pivoting process.
Here is the complete code for this:
import pandas as pd
df = pd.read_csv(‘data.csv’)
df = df[[‘interest’,’residence’,’gender’]]
pTable = df.pivot_table(index=’residence’, columns=’interest’, aggfunc=’count’)
pTable.to_excel(‘MyPivotData.xlsx’,’Pivot_Data’,startrow=2)
In the above code, pivot_table(…) function is the heart of all this. It takes index, columns and then the aggregator function as a parameter.
Executing the above lines of code will return below output:
![]()
I hope you find this content useful.
If there is any point that is left unclear, then do check out the recording of this whole process on my YouTube channel named Shweta Lodha.