Script: Pivot Data

Scenario

I want to 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.

1. Within Designer, create a new pipeline.
2. 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
     }

3. 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: