Pivoting a Dataset in Ignition Script

Ignition
1

image
image956×530 26.7 KB

I need to pivot my final_dataset to a form which i have shown under Line15. Is there a way to do this in Ignition Scripting

2

This script will accomplish what you are asking if you add it to the script you have above:

headers = ["RunID", "Counter"]
data = []
for row in range(final_dataset.getRowCount()):
	for column in range (final_dataset.getColumnCount()):
		if column > 0 and final_dataset.getValueAt(row, column)>0:
			data.append([final_dataset.getValueAt(row, 0), final_dataset.getValueAt(row, column)])
pivoted_dataset = system.dataset.toDataSet(headers, data)

Output:
image

3

A (slightly) more efficient method:

headers = ['RunID', 'Counter']
data = []

for row in xrange(final_dataset.rowCount):
	runID = final_dataset.getValueAt(row, 0)
	for col in xrange(1, final_dataset.columnCount):
		counter = final_dataset.getValueAt(row, col)
		if counter > 0: 
			dataOut.append([runID, counter])

pivoted_dataset = system.dataset.toDataSet(headers, dataOut)
  • rowCount and columnCount are already attributes. It's not really necessary to call getRowCount() and getColumnCount(). Not that I don't catch myself doing it, though. :laughing:
  • we know we need the runID value on every row, so we only need to get the value once.
  • we need each counter value to see if it's >0, so we only need to get it's value once as well.
1 Like
4

All right, one more, then I'm done. Probably.

If the incoming data is from a query within a script (something I do frequently), the data type will be a PyDataSet. Even easier to work with, IMO.

sampleHeaders = ['RunID', 'Counter1', 'Counter2', 'Counter3']
sampleData    = [
                 ['1234', 1000, 1500, 0],
                 ['5678', 2300, 2000, 500],
                 ['9012', 0, 0, 5000]
                ]
# Standard Dataset
final_dataset = system.dataset.toDataSet(sampleHeaders, sampleData)

# PyDataSet -- This is what would come back from a db query.
py_final_dataset = system.dataset.toPyDataSet(final_dataset)

###########################################################

headers = ['RunID', 'Counter']
data = []

for row in py_final_dataset:
	runID = row['RunID']
	data.extend([[runID, col] for col in row[1:] if col > 0])

pivoted_dataset = system.dataset.toDataSet(headers, data)
1 Like
5

If you'd like to play with more complicated cases, you can install my Simulation Aids module (free) and use its view() expression function to do the pivot for you. If you like, you can put it into DEBUG mode and look at the jython it produces for your case That can be cut and pasted into a script library function.

2 Likes