Pivoting Data with Pandas in Python – Data Analytics in Python 

by | Nov 17, 2022 | Uncategorized | 0 comments

In this section of the series Data Analytics in Python, we will go through Pivot Tables in Pandas which is a handy technique for exploring data from different dimensions and extracting insights from Data. Pivoting is one of the main techniques used in Business Intelligence Solutions and Data Science for slicing and Dicing the data.

Lets consider initial sales data of the 5 Regions for the years 2016, 2017 and 2018.

YearCentralEastNorthSouthWest
2016300150500325200
2017200300450300200
2018250225150375150

Table 1: Initial Sales Data Table

Now consider that a report is needed to be generated where the Data Values of Years is to be shown as columns and generate a summary of each year accordingly something as follows:

Year201620172018
Central300200250
East150300225
North500450150
South325300375
West200200150

Table 2: Preview of the Desired Result 

For generating the required output illustrated above, we will need to Pivot our data. Let’s dive in Python and first make a Pandas’ DataFrame out of the initial data presented in Table 1:

import pandas as pd
 
df = pd.DataFrame({'Year': ['2016','2017','2018'],
'North' : [500, 450, 150],
'East' : [150, 300, 225],
'South': [325, 300, 375],
'West': [200, 200, 150],
'Central':[300, 200, 250],})
 
>>> df
   Central  East  North  South  West  Year
0      300   150    500    325   200  2016
1      200   300    450    300   200  2017
2      250   225    150    375   150  2018

To generate the required output illustrated in Table 2, we will use Pandas DataFrame’s pivot_table:

>>> df.pivot_table(columns='Year')
Year     2016  2017  2018
Central   300   200   250
East      150   300   225
North     500   450   150
South     325   300   375
West      200   200   150

A more readable output of the above function is as follows:

Year201620172018
Central300200250
East150300225
North500450150
South325300375
West200200150

Table 3: Desired Result Generated with Pandas Pivot Table 

In this way, we can change the dimensions of our data and generate different insights out of it.