Dataset manipulation to a new Dataset

Ben_Blaschuk · March 14, 2023, 8:53pm

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.

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.

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

ryan.white · March 14, 2023, 10:40pm

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.

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)

Transistor · March 14, 2023, 10:59pm

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.

Ben_Blaschuk · March 15, 2023, 2:41pm

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.

lrose · March 15, 2023, 2:53pm

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

Ben_Blaschuk · March 15, 2023, 3:18pm

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

lrose · March 15, 2023, 3:32pm

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.

Ben_Blaschuk · March 15, 2023, 3:42pm

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.

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

lrose · March 15, 2023, 4:07pm

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

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).

Ben_Blaschuk · March 16, 2023, 8:42pm

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:

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)

ryan.white · March 16, 2023, 9:13pm

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:

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)

Ben_Blaschuk · March 17, 2023, 3:27pm

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