# Pivoting a Dataset in Ignition Script

**URL:** <https://forum.inductiveautomation.com/t/pivoting-a-dataset-in-ignition-script/67319>\
**Category:** Ignition\
**Created:** [December 7, 2022, 6:11am UTC](https://forum.inductiveautomation.com/t/pivoting-a-dataset-in-ignition-script/67319 "2022-12-07T06:11:33Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Arjun\_Vaishak\_C](https://avatars.discourse-cdn.com/v4/letter/a/d2c977/32.png) [@Arjun\_Vaishak\_C](https://forum.inductiveautomation.com/u/Arjun_Vaishak_C)\
**Post date:** [December 7, 2022, 6:11am UTC](https://forum.inductiveautomation.com/t/pivoting-a-dataset-in-ignition-script/67319/1 "2022-12-07T06:11:33Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/b/0bee364d633df539c8fc426fe0a3b82440d73250.png)  
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

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [December 7, 2022, 6:52am UTC](https://forum.inductiveautomation.com/t/pivoting-a-dataset-in-ignition-script/67319/2 "2022-12-07T06:52:32Z")

</div>

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

```auto
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/2/1298b337e8ceb93d1d970b03fba629b127e8b742.png)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [December 7, 2022, 11:50am UTC](https://forum.inductiveautomation.com/t/pivoting-a-dataset-in-ignition-script/67319/3 "2022-12-07T11:50:48Z")

</div>

A (slightly) more efficient method:

```python
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. 😆
- 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.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [December 7, 2022, 12:04pm UTC](https://forum.inductiveautomation.com/t/pivoting-a-dataset-in-ignition-script/67319/4 "2022-12-07T12:04:05Z")

</div>

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.

```python
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)

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 7, 2022, 1:39pm UTC](https://forum.inductiveautomation.com/t/pivoting-a-dataset-in-ignition-script/67319/5 "2022-12-07T13:39:44Z")

</div>

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.