# Dataset manipulation to a new Dataset

**URL:** <https://forum.inductiveautomation.com/t/dataset-manipulation-to-a-new-dataset/71055>\
**Category:** Ignition\
**Created:** [March 14, 2023, 8:53pm UTC](https://forum.inductiveautomation.com/t/dataset-manipulation-to-a-new-dataset/71055 "2023-03-14T20:53:24Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ben\_Blaschuk](https://avatars.discourse-cdn.com/v4/letter/b/5daacb/32.png) [@Ben\_Blaschuk](https://forum.inductiveautomation.com/u/Ben_Blaschuk)\
**Post date:** [March 14, 2023, 8:53pm UTC](https://forum.inductiveautomation.com/t/dataset-manipulation-to-a-new-dataset/71055/1 "2023-03-14T20:53:24Z")

</div>

ok... I have a dataset that is 8 tags(columns) by 8hr increments(rows) for a set number of days(dropdown value). I have the "Duration On" for each tag during each 8hr increment. I want the cumulative percentage based on a specific weight of each tag's "Duration On". Tags(column) 1,3,6,8 are all weighted at 8.33% and tags(column) 2,4,5,7 are all weighted at 16.67%. How do I take my "Duration On" number for each column and either make it one number in a new table for the designated 8hr increment(a dataset that is x-rows by 1 column) OR create a new dataset(x rows by 8 columns)that is based on the weighted value. There is a For Loop in there somewhere but the syntax doesn't make sense(yet).  
Here is the dataset described above.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/b/bb7eb3d1f0671090d0a3e2e5a7b4cc2d3c32096d.png)

I used an expression to get it on an daily bases which be below... I need to somehow mesh the 2 to get the 8hr increments as a percentage.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/1/21ee876896b8a5e510ead9b105b7cd99188b356f.png)

Sincerely: the Newbie to this but Mid-Life-Crisis to VBA

---

<div class="post-metadata">

**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\
**Post date:** [March 14, 2023, 10:40pm UTC](https://forum.inductiveautomation.com/t/dataset-manipulation-to-a-new-dataset/71055/2 "2023-03-14T22:40:09Z")

</div>

Something like this maybe? This will make a 1 column with x rows dataset. I'm not seeing where you are using the `numOfTableSamples` variable so I'm not sure how it is supposed to be utilized. If anything this hopefully gets you on track.

```auto
LDnPaths = ["[default]Hi Speed Nordson/Material in Booth 1 Zone 1",
			"[default]Hi Speed Nordson/Material in Booth 1 Zone 2",
			"[default]Hi Speed Nordson/Material in Booth 1 Zone 3",
			"[default]Hi Speed Nordson/Material in Booth 1 Zone 4",
			"[default]Hi Speed Nordson/Material in Booth 1 Zone 5",
			"[default]Hi Speed Nordson/Material in Booth 1 Zone 6",
			"[default]Hi Speed Nordson/Material in Booth 1 Zone 7",
			"[default]Hi Speed Nordson/Material in Booth 1 Zone 8"
			]

numOfTableSamples = event.source.parent.getComponent("Dropdown").selectedValue * 3
rackDensity = system.tag.queryTagHistory(LDnPaths, startDate=event.source.parent.startTime, intervalHours=8, aggregationMode="Duration")

weightedValueList = []
for row in xrange(rackDensity.rowCount):
	weightedValue = 0
	for column in xrange(rackDensity.columnCount):
		if (column + 1) in [1, 3, 6, 8]:
			weightedValue = weightedValue + (0.0833 * rackDensity.getValueAt(row, column))
		elif (column + 1) in [2, 4, 5, 7]:
			weightedValue = weightedValue + (0.1667 * rackDensity.getValueAt(row, column))

	weightedValueList.append(weightedValue)

weightedValueDataset = system.dataset.toDataSet(["WeightedValue"], weightedValueList)

```

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [March 14, 2023, 10:59pm UTC](https://forum.inductiveautomation.com/t/dataset-manipulation-to-a-new-dataset/71055/3 "2023-03-14T22:59:13Z")

</div>

Tip: post code - not pictures of code. That saves us having to type it all out in our answers. Use the \</\> button to format the code as code.

---

<div class="post-metadata">

**Author:** ![Ben\_Blaschuk](https://avatars.discourse-cdn.com/v4/letter/b/5daacb/32.png) [@Ben\_Blaschuk](https://forum.inductiveautomation.com/u/Ben_Blaschuk)\
**Post date:** [March 15, 2023, 2:41pm UTC](https://forum.inductiveautomation.com/t/dataset-manipulation-to-a-new-dataset/71055/4 "2023-03-15T14:41:56Z")

</div>

I don’t need the numOfTableSamples any more… was in there from a previous set up of the table.. Once I figured out how to use the start time and interval hours combination I removed it from the TagHistory syntax.

![image001.png](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/a/9ae8b17522974b96f5df2795c3c1b7c7d74db46b.png)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [March 15, 2023, 2:53pm UTC](https://forum.inductiveautomation.com/t/dataset-manipulation-to-a-new-dataset/71055/5 "2023-03-15T14:53:49Z")

</div>

You have a date column in the dataset, you will need to modify the code to exclude that column.

---

<div class="post-metadata">

**Author:** ![Ben\_Blaschuk](https://avatars.discourse-cdn.com/v4/letter/b/5daacb/32.png) [@Ben\_Blaschuk](https://forum.inductiveautomation.com/u/Ben_Blaschuk)\
**Post date:** [March 15, 2023, 3:18pm UTC](https://forum.inductiveautomation.com/t/dataset-manipulation-to-a-new-dataset/71055/6 "2023-03-15T15:18:21Z")

</div>

umm.... how? I cant find any tutorials on that. 😕

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [March 15, 2023, 3:32pm UTC](https://forum.inductiveautomation.com/t/dataset-manipulation-to-a-new-dataset/71055/7 "2023-03-15T15:32:34Z")

</div>

Did you use @ryan.white's code? If not can you post your code in pre-formatted text?

I don't know what your data looks like, what column is being returned as a date, so I can't be of much help.

---

<div class="post-metadata">

**Author:** ![Ben\_Blaschuk](https://avatars.discourse-cdn.com/v4/letter/b/5daacb/32.png) [@Ben\_Blaschuk](https://forum.inductiveautomation.com/u/Ben_Blaschuk)\
**Post date:** [March 15, 2023, 3:42pm UTC](https://forum.inductiveautomation.com/t/dataset-manipulation-to-a-new-dataset/71055/8 "2023-03-15T15:42:49Z")

</div>

I did. The dataset is a custom property so i had to modify it to be like so. but i am still getting the error. it doesn't like the "event" at the beginning for some reason.

```auto
for row in xrange(event.source.parent.rackDensity.rowCount):
	weightedValue = 0
	for column in xrange(event.source.parent.rackDensity[1,8]):
		if (column + 1) in [1, 3, 6, 8]:
			weightedValue = weightedValue + (0.0833 * event.source.parent.rackDensity.getValueAt(row, column))
		elif (column + 1) in [2, 4, 5, 7]:
			weightedValue = weightedValue + (0.1667 * event.source.parent.rackDensity.getValueAt(row, column))

	weightedValueList.append(weightedValue)

event.source.parent.getComponent('Paint Rack Density').calcRackDensity = weightedValueDataset

```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [March 15, 2023, 4:07pm UTC](https://forum.inductiveautomation.com/t/dataset-manipulation-to-a-new-dataset/71055/9 "2023-03-15T16:07:56Z")

</div>

Try this, I am assuming that the first column is the date.

```auto
for row in xrange(event.source.parent.rackDensity.rowCount):
	weightedValue = 0
	for column in xrange(1,event.source.parent.rackDensity.columnCount + 1):
		if (column) in [1, 3, 6, 8]:
			weightedValue = weightedValue + (0.0833 * event.source.parent.rackDensity.getValueAt(row, column))
		elif (column) in [2, 4, 5, 7]:
			weightedValue = weightedValue + (0.1667 * event.source.parent.rackDensity.getValueAt(row, column))

	weightedValueList.append(weightedValue)

event.source.parent.getComponent('Paint Rack Density').calcRackDensity = weightedValueDataset

```

I'm not sure what `event.source.parent.rackDensity[1,8]` is trying to accomplish. I've replaced it with `event.source.parent.rackDensity.columnCount`

`xrange(1,columnCount)` will tell the function to start with 1 instead of 0 so you will not get the "assumed" date column. I've added 1 to the column count so xrange() should produce [1,2,3,4,5,6,7,8] (it's really a generator but that's neither here nor there).

---

<div class="post-metadata">

**Author:** ![Ben\_Blaschuk](https://avatars.discourse-cdn.com/v4/letter/b/5daacb/32.png) [@Ben\_Blaschuk](https://forum.inductiveautomation.com/u/Ben_Blaschuk)\
**Post date:** [March 16, 2023, 8:42pm UTC](https://forum.inductiveautomation.com/t/dataset-manipulation-to-a-new-dataset/71055/10 "2023-03-16T20:42:22Z")

</div>

ok... I have a new problem. I think it has to do with the custom property dataset I want to send it to. But I even tried to send it to a regular table dataset and i get the below error. In reality, I want to keep the date column that this conversion loop is bypassing to get the data.

Original Dataset is set up like this.... Date, tag1, tag2, tag3, tag4 etc...  
New dataset i am looking for is: Data, WeightedValue

The error is :  
Traceback (most recent call last):  
File "event:propertyChange", line 42, in   
java.lang.ClassCastException: java.lang.ClassCastException: class org.python.core.PyFloat cannot be cast to class org.python.core.PySequence (org.python.core.PyFloat and org.python.core.PySequence are in unnamed module of loader java.net.URLClassLoader @7b51103f)

```
caused by ClassCastException: class org.python.core.PyFloat cannot be cast to class org.python.core.PySequence (org.python.core.PyFloat and org.python.core.PySequence are in unnamed module of loader java.net.URLClassLoader @7b51103f)

```

Ignition v8.1.14 (b2022012711)  
Java: Azul Systems, Inc. 11.0.13

the code i am using is:

```auto
weightedValueList = []
for row in range(event.source.parent.rackDensity.rowCount):
	weightedValue = 0
	for column in range(event.source.parent.rackDensity.columnCount + 1):
		if (column) in [1, 3, 6, 8]:
			weightedValue = weightedValue + (0.0833 * event.source.parent.rackDensity.getValueAt(row, column))
		elif (column) in [2, 4, 5, 7]:
			weightedValue = weightedValue + (0.1667 * event.source.parent.rackDensity.getValueAt(row, column))

	weightedValueList.append(weightedValue)

event.source.parent.getComponent('Paint Rack Density').calcRackDensity = system.dataset.toDataSet(["WeightedValue"], weightedValueList)

```

---

<div class="post-metadata">

**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\
**Post date:** [March 16, 2023, 9:13pm UTC](https://forum.inductiveautomation.com/t/dataset-manipulation-to-a-new-dataset/71055/11 "2023-03-16T21:13:03Z")

</div>

You'll need to post the entirety of the code for the `propertyChange` event, since the code you are showing doesn't have 42 lines and we cannot see what line numbers your snippet is from.

If you want to include the date you can try:

```auto
weightedValueList = []
for row in xrange(event.source.parent.rackDensity.rowCount):
	weightedValue = 0
	weightedValueDate = None
	for column in xrange(event.source.parent.rackDensity.columnCount + 1):
		if column == 0:
			weightedValueDate = event.source.parent.rackDensity.getValueAt(row, column)
		elif column in [1, 3, 6, 8]:
			weightedValue = weightedValue + (0.0833 * float(event.source.parent.rackDensity.getValueAt(row, column)))
		elif column in [2, 4, 5, 7]:
			weightedValue = weightedValue + (0.1667 * float(event.source.parent.rackDensity.getValueAt(row, column)))

	weightedValueList.append([weightedValueDate, weightedValue])

event.source.parent.getComponent('Paint Rack Density').calcRackDensity = system.dataset.toDataSet(["Date", "WeightedValue"], weightedValueList)

```

---

<div class="post-metadata">

**Author:** ![Ben\_Blaschuk](https://avatars.discourse-cdn.com/v4/letter/b/5daacb/32.png) [@Ben\_Blaschuk](https://forum.inductiveautomation.com/u/Ben_Blaschuk)\
**Post date:** [March 17, 2023, 3:27pm UTC](https://forum.inductiveautomation.com/t/dataset-manipulation-to-a-new-dataset/71055/12 "2023-03-17T15:27:41Z")

</div>

This worked. I had a binding to my dataset I wanted to change. Thank you 😊