Generate Pivot Table Using Python

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 the residence. For example, say you want to know how many students are falling under Uncertain 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:

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. 

You can also have a quick look at the recording on my YouTube channel: