Script: Pivot Data

Pivot data that is coming in before I use it further downstream.

Requirements

Snaps used

For this scenario, the following Snaps are used:

JSON Generator or a File Reader
Script

Other requirements

A file or the content for the JSON Generator.

Configuration

This example will bring in data using the JSON Generator.

Within Designer, create a new pipeline.

Add and configure a JSON Generator Snap.

Click Edit JSON and add the following sample data:


{
    "Key": "001",
    "Month01": 151,
    "Month02": 152,
    "Month03": 153,
    "Month04": 154,
    "Month05": 155,
    "Month06": 156,
    "Month07": 157,
    "Month08": 158,
    "Month09": 159,
    "Month10": 160,
    "Month11": 161,
    "Month12": 162
}

Add and configure a Script Snap.

Set Scripting language to Python.
Select Execute during preview.
Click Edit Script and add the following sample script:

from com.snaplogic.scripting.language import ScriptHook
from com.snaplogic.scripting.language.ScriptHook import *
  
class TransformScript(ScriptHook):
    def __init__(self, input, output, error, log):
        self.input = input
        self.output = output
        self.error = error
        self.log = log
  
    def execute(self):
        self.log.info("Executing Transform script")
        i = 1
        while self.input.hasNext():
            data = self.input.next()
            dataoutkey = data['Key']
            for x in range(1, 13):
                dataout = {}
                monthkey = 'Month' + str(x).zfill(2)
                dataout['key'] = dataoutkey
                dataout['monthkey'] = monthkey
                dataout['value'] = data[monthkey]
                self.output.write(dataout)
                x = x + 1
        self.log.info("Finished executing the Transform script")
 
hook = TransformScript(input, output, error, log)

This script will process the data from the JSON Generator Snap and deliver it as:


monthkey	key	value
Month01	001	151
Month02	001	152
Month03	001	153
Month04	001	154
Month05	001	155
Month06	001	156
Month07	001	157
Month08	001	158
Month09	001	159
Month10	001	160
Month11	001	161
Month12	001	162