How to pivot and unpivot using Python?

Rajnilari2015
Posted by Rajnilari2015 under Python category on | Points: 40 | Views : 544
Let's say we have the below RecordSet

Product Y1 Y2 Y3 Y4 Y5 Y6 Y7 Y8 Y9 Y10
Product1 1 2 3 4 5 6 7 8 9 10
Product2 2 3 4 5 6 7 8 9 10 11
Product3 3 4 5 6 7 8 9 10 11 12
Product4 4 5 6 7 8 9 10 11 12 13


The processed output should look as under

YKey = Y1  Year = 2016 Product1 = 1 Product2 = 2 Product3 = 3 Product4 = 4
YKey = Y2 Year = 2017 Product1 = 2 Product2 = 3 Product3 = 4 Product4 = 5
YKey = Y3 Year = 2018 Product1 = 3 Product2 = 4 Product3 = 5 Product4 = 6
YKey = Y4 Year = 2019 Product1 = 4 Product2 = 5 Product3 = 6 Product4 = 7
YKey = Y5 Year = 2020 Product1 = 5 Product2 = 6 Product3 = 7 Product4 = 8
YKey = Y6 Year = 2021 Product1 = 6 Product2 = 7 Product3 = 8 Product4 = 9
YKey = Y7 Year = 2022 Product1 = 7 Product2 = 8 Product3 = 9 Product4 = 10
YKey = Y8 Year = 2023 Product1 = 8 Product2 = 9 Product3 = 10 Product4 = 11
YKey = Y9 Year = 2024 Product1 = 9 Product2 = 10 Product3 = 11 Product4 = 12
YKey = Y10 Year = 2025 Product1 = 10 Product2 = 11 Product3 = 12 Product4 = 13


The below program will help us to do so

from System import *
from System.Collections.Generic import *

class Program(object):
def Main(args):
#unpivot records
unpivotRecords = (Product1 = Program.Unpivot(0), Product2 = Program.Unpivot(1), Product3 = Program.Unpivot(2), Product4 = Program.Unpivot(3))
#pivot records
pivotRecords = Program.Pivot(unpivotRecords)
#display the records
pivotRecords.ForEach()
Console.ReadKey()

Main = staticmethod(Main)

#Source Records
def GetSourceRecords():
source = List[InputData]()
source.Add(InputData(ProductName = "Product1", Year1 = 1, Year2 = 2, Year3 = 3, Year4 = 4, Year5 = 5, Year6 = 6, Year7 = 7, Year8 = 8, Year9 = 9, Year10 = 10))
source.Add(InputData(ProductName = "Product2", Year1 = 2, Year2 = 3, Year3 = 4, Year4 = 5, Year5 = 6, Year6 = 7, Year7 = 8, Year8 = 9, Year9 = 10, Year10 = 11))
source.Add(InputData(ProductName = "Product3", Year1 = 3, Year2 = 4, Year3 = 5, Year4 = 6, Year5 = 7, Year6 = 8, Year7 = 9, Year8 = 10, Year9 = 11, Year10 = 12))
source.Add(InputData(ProductName = "Product4", Year1 = 4, Year2 = 5, Year3 = 6, Year4 = 7, Year5 = 8, Year6 = 9, Year7 = 10, Year8 = 11, Year9 = 12, Year10 = 13))
return source

GetSourceRecords = staticmethod(GetSourceRecords)

#Unpivot records
def Unpivot(row):
source = Program.GetSourceRecords()
yearwiseProductValues = List[int]()
multiKeyDictionary = Dictionary[str, List]()
yearwiseProductValues.Add(source[row].Year1)
yearwiseProductValues.Add(source[row].Year2)
yearwiseProductValues.Add(source[row].Year3)
yearwiseProductValues.Add(source[row].Year4)
yearwiseProductValues.Add(source[row].Year5)
yearwiseProductValues.Add(source[row].Year6)
yearwiseProductValues.Add(source[row].Year7)
yearwiseProductValues.Add(source[row].Year8)
yearwiseProductValues.Add(source[row].Year9)
yearwiseProductValues.Add(source[row].Year10)
multiKeyDictionary.Add(source[row].ProductName, yearwiseProductValues)
return multiKeyDictionary

Unpivot = staticmethod(Unpivot)

#Pivot records
def Pivot(unpivotRecords):
count = 10
startYear = 2016
pivotRecords = List[OutputData]()
i = 0
while i < count:
pivotRecords.Add(OutputData(YKey = "Y" + (i + 1), Year = startYear + i, Product1 = unpivotRecords.Product1["Product1"][i], Product2 = unpivotRecords.Product2["Product2"][i], Product3 = unpivotRecords.Product3["Product3"][i], Product4 = unpivotRecords.Product4["Product4"][i]))
i += 1
return pivotRecords

Pivot = staticmethod(Pivot)

class OutputData(object):
def get_YKey(self):

def set_YKey(self, value):

YKey = property(fget=get_YKey, fset=set_YKey)

def get_Year(self):

def set_Year(self, value):

Year = property(fget=get_Year, fset=set_Year)

def get_Product1(self):

def set_Product1(self, value):

Product1 = property(fget=get_Product1, fset=set_Product1)

def get_Product2(self):

def set_Product2(self, value):

Product2 = property(fget=get_Product2, fset=set_Product2)

def get_Product3(self):

def set_Product3(self, value):

Product3 = property(fget=get_Product3, fset=set_Product3)

def get_Product4(self):

def set_Product4(self, value):

Product4 = property(fget=get_Product4, fset=set_Product4)

class InputData(object):
def get_ProductName(self):

def set_ProductName(self, value):

ProductName = property(fget=get_ProductName, fset=set_ProductName)

def get_Year1(self):

def set_Year1(self, value):

Year1 = property(fget=get_Year1, fset=set_Year1)

def get_Year2(self):

def set_Year2(self, value):

Year2 = property(fget=get_Year2, fset=set_Year2)

def get_Year3(self):

def set_Year3(self, value):

Year3 = property(fget=get_Year3, fset=set_Year3)

def get_Year4(self):

def set_Year4(self, value):

Year4 = property(fget=get_Year4, fset=set_Year4)

def get_Year5(self):

def set_Year5(self, value):

Year5 = property(fget=get_Year5, fset=set_Year5)

def get_Year6(self):

def set_Year6(self, value):

Year6 = property(fget=get_Year6, fset=set_Year6)

def get_Year7(self):

def set_Year7(self, value):

Year7 = property(fget=get_Year7, fset=set_Year7)

def get_Year8(self):

def set_Year8(self, value):

Year8 = property(fget=get_Year8, fset=set_Year8)

def get_Year9(self):

def set_Year9(self, value):

Year9 = property(fget=get_Year9, fset=set_Year9)

def get_Year10(self):

def set_Year10(self, value):

Year10 = property(fget=get_Year10, fset=set_Year10)



At first we are flattening the records by using a Multikey dictionary (Dictionary<string, List<int>>) inside the Unpivot function. That record we are passing into the Pivot function to get the desired output.

Comments or Responses

Login to post response