Sum multiple datasets

Ignition
1

Hi,

i want to sum the datasets value and put in first dataset

note: First column will be time stamp that i don't want to sum it

example code

image
image1074×419 44.4 KB 

endTime = system.date.now()
startTime = system.date.addMinutes(endTime, -30)
diff = system.date.minutesBetween(endTime,startTime)

d1 = system.tag.queryTagHistory(paths=['test'], startDate=startTime, endDate=endTime, returnSize= abs(diff), aggregationMode="Maximum", returnFormat='Wide')
list  = ["Elec","Gas"]
for i in list:
	d2 = system.tag.queryTagHistory(paths=[i], startDate=startTime, endDate=endTime, returnSize= abs(diff), aggregationMode="Maximum", returnFormat='Wide')

Code i am using d1 will be my first datatset

and for loop will run mutiple times depends upon the list data

so i want to get the for loop datatset and sum with the d1 dataset

basically i want to sum all the dataset with corresponding rows. Return size of all dataset will be same

is there any better way to do it?

@JordanCClark any idea to implement this feature

2

The first thing you probably want to do is look at this page.

Your code will look something like this.

d1 =system.tag.queryTagHistory() # dataset 1
d2 =system.tag.queryTagHistory() # dataset 2

pyds1 = system.dataset.toPyDataSet(d1)
pyds2 = system.dataset.toPyDataSet(d2)

# Make sure we have the same number of rows in each dataset.
assert(len(pyds1)==len(pyds2))

for index, row in enumerate(pyds1):
        row[1] = row[1] + pyds2[index][1] 

return system.dataset.toDataSet(pyds1)
3

getting this error
image
if my dataset have multiple columns how to sum it?

4

I'm not sure if I understood but maybe this is what you want:

endTime = system.date.now()
startTime = system.date.addMinutes(endTime, -30)
diff = system.date.minutesBetween(endTime,startTime)

paths = ["test","Elec","Gas"] #add more if you want (at least one)
history = system.tag.queryTagHistory(paths=paths, startDate=startTime, endDate=endTime, returnSize= abs(diff), aggregationMode="Maximum", returnFormat='Wide')
data = []
for r in range(history.rowCount):
	row = [0]
	for c in range(history.columnCount):
		if c != history.getColumnIndex('t_stamp'):
			row.append(history.getValueAt(r,c))
	data.append([history.getValueAt(r,'t_stamp'), sum(row)])
result = system.dataset.toDataSet(['t_stamp',history.getColumnName(1)], data)
5

Hi correct
after getting the result

i want to add the result dataset with d1 query dataset
d1 = system.tag.queryTagHistory(paths=['test'], startDate=startTime, endDate=endTime, returnSize= abs(diff), aggregationMode="Maximum", returnFormat='Wide')

how to do that

note - d1 also having same number of rows and first row will be t_stamp

6

above code already sum

7

yaa but i have corrected the code for your understanding

list  = ["Elec","Gas"]
for i in list:
	d2 = system.tag.queryTagHistory(paths=['compressor'+i, "water"+i], startDate=startTime, endDate=endTime, returnSize= abs(diff), aggregationMode="Maximum", returnFormat='Wide')

for loop will run for first index and i have one dataset

for loop wil run for scond index and that time only i want to sum second index dataset with the first index dataset

this is what i am trying to do

8

Please check now. i have corrected the code for your understanding. How i want to sum the datasets

9

ohh

endTime = system.date.now()
startTime = system.date.addMinutes(endTime, -30)
diff = system.date.minutesBetween(endTime,startTime)

base = system.tag.queryTagHistory(paths=["[SLC]f0"], startDate=startTime, endDate=endTime, returnSize= abs(diff), aggregationMode="Maximum", returnFormat='Wide')
list = ["[SLC]f","[SLC]f","[SLC]f"]
for i in list:
	history = system.tag.queryTagHistory(paths=[i+"1", i+"2",i+"3"], startDate=startTime, endDate=endTime, returnSize= abs(diff), aggregationMode="Maximum", returnFormat='Wide')
	for t in range(history.rowCount):
		for r in range(base.rowCount):
			if base.getValueAt(r,'t_stamp') == history.getValueAt(t,'t_stamp'):
				currentValue = base.getValueAt(r,base.getColumnName(1))
				newValue = sum([history.getValueAt(t,c) for c in range(history.columnCount) if c != history.getColumnIndex('t_stamp')])
				base = system.dataset.setValue(base, r, base.getColumnName(1), currentValue + newValue)
				break
result = base
10

getting error in this line
newValue = sum([history.getValueAt(t,c) for c in range(history.columnCount) if c != history.getColumnIndex('t_stamp')])

10:38:56.764 [AWT-EventQueue-0] ERROR com.inductiveautomation.ignition.client.util.gui.ErrorUtil - <HTML>Error executing script for event:&nbsp;<code><b>mouseClicked</b></code><BR>on component:&nbsp;<code><b>Button</b></code>.
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last):
  File "<event:mouseClicked>", line 22, in <module>
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'

	at org.python.core.Py.TypeError(Py.java:236)
	at org.python.core.PyObject._basic_add(PyObject.java:2091)
	at org.python.core.PyObject._add(PyObject.java:2068)
	at org.python.pycode._pyx888.f$0(<event:mouseClicked>:26)
	at org.python.pycode._pyx888.call_function(<event:mouseClicked>)
	at org.python.core.PyTableCode.call(PyTableCode.java:173)
	at org.python.core.PyCode.call(PyCode.java:18)
	at org.python.core.Py.runCode(Py.java:1687)
	at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:788)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:206)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:299)
	at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)
	at com.sun.proxy.$Proxy57.mouseClicked(Unknown Source)
	at java.desktop/java.awt.AWTEventMulticaster.mouseClicked(Unknown Source)
	at java.desktop/java.awt.Component.processMouseEvent(Unknown Source)
	at java.desktop/javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.Component.processEvent(Unknown Source)
	at java.desktop/java.awt.Container.processEvent(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.EventQueue.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
	at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
	at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.desktop/java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)

checked dataset it doesn't have none values

image
image1041×583 65.9 KB

11 
endTime = system.date.now()
startTime = system.date.addMinutes(endTime, -30)
diff = system.date.minutesBetween(endTime,startTime)

base = system.tag.queryTagHistory(paths=["[SLC]f0"], startDate=startTime, endDate=endTime, returnSize= abs(diff), aggregationMode="Maximum", returnFormat='Wide')
list = ["[SLC]f","[SLC]f","[SLC]f"]
for i in list:
	history = system.tag.queryTagHistory(paths=[i+"1", i+"2",i+"3"], startDate=startTime, endDate=endTime, returnSize= abs(diff), aggregationMode="Maximum", returnFormat='Wide')
	for t in range(history.rowCount):
		for r in range(base.rowCount):
			if base.getValueAt(r,'t_stamp') == history.getValueAt(t,'t_stamp'):
				currentValue = base.getValueAt(r,base.getColumnName(1))
				newValue = sum([history.getValueAt(t,c) for c in range(history.columnCount) if c != history.getColumnIndex('t_stamp') and history.getValueAt(t,c) is not None])
				base = system.dataset.setValue(base, r, base.getColumnName(1), currentValue + newValue)
				break
result = base
12

its suming all in first column - not like that

you can remove base path- only use this code

ist  = ["Elec","Gas"]
for i in list:
	d2 = system.tag.queryTagHistory(paths=['compressor'+i, "water"+i], startDate=startTime, endDate=endTime, returnSize= abs(diff), aggregationMode="Maximum", returnFormat='Wide')

for loop will run for first index and i have one dataset

for loop wil run for scond index and that time only i want to sum second index dataset with the first index dataset

path has 2 tags - so it will return 30R * 3C
so i want to sum as it is with second index dataset with first index dataset

13

ugh.. Is just hard to understnad you. Maybe this?

endTime = system.date.now()
startTime = system.date.addMinutes(endTime, -30)
diff = system.date.minutesBetween(endTime,startTime)

base = system.tag.queryTagHistory(paths=["[SLC]f0"], startDate=startTime, endDate=endTime, returnSize= abs(diff), aggregationMode="Maximum", returnFormat='Wide')
list = ["[SLC]f","[SLC]f","[SLC]f"]
for e, i in enumerate(list):
	paths=[i+"1", i+"2",i+"3"]
	history = system.tag.queryTagHistory(paths=paths, startDate=startTime, endDate=endTime, returnSize= abs(diff), aggregationMode="Maximum", returnFormat='Wide')
	if e == 0:
		r_history = history
	else:
		for r in range(history.rowCount):
			for j in range(r_history.rowCount):
				if history.getValueAt(r,'t_stamp') == r_history.getValueAt(j,'t_stamp'):
					headers = []
					currentRow = []
					for c in range(r_history.columnCount):
						if c != r_history.getColumnIndex('t_stamp'):
							currentValue = r_history.getValueAt(j,c)
							currentRow.append(0 if currentValue is None else currentValue)
							headers.append(r_history.getColumnName(c))
						
					newRow = [0 if history.getValueAt(r,c) is None else history.getValueAt(r,c) for c in range(history.columnCount) if c != history.getColumnIndex('t_stamp')]
					changes = {h:x+y for h,x,y in zip(headers, currentRow, newRow)}
					r_history = system.dataset.updateRow(r_history, j, changes)
					break

data = []
for r in range(base.rowCount):
	date = base.getValueAt(r,'t_stamp')
	row = [date]
	for c in range(base.columnCount):
		if c != base.getColumnIndex('t_stamp'):
			row.append(base.getValueAt(r,c))
	for n in range(r_history.rowCount):
		if r_history.getValueAt(n,'t_stamp') == date:
			for c in range(r_history.columnCount):
				if c != r_history.getColumnIndex('t_stamp'):
					row.append(r_history.getValueAt(n,c))
			break
	data.append(row)
result = system.dataset.toDataSet(base.getColumnNames()+[h for h in r_history.getColumnNames() if h != 't_stamp'], data)
1 Like
14

Thanks alot . Its my mistake i didn't explained you clearly at first

list = ["[SLC]f","[SLC]f","[SLC]f"]
for e, i in enumerate(list):
	paths=[i+"1", i+"2",i+"3"]
	history = system.tag.queryTagHistory(paths=paths, startDate=startTime, endDate=endTime, returnSize= abs(diff), aggregationMode="Maximum", returnFormat='Wide')
	if e == 0:
		r_history = history
	else:
		for r in range(history.rowCount):
			for j in range(r_history.rowCount):
				if history.getValueAt(r,'t_stamp') == r_history.getValueAt(j,'t_stamp'):
					headers = []
					currentRow = []
					for c in range(r_history.columnCount):
						if c != r_history.getColumnIndex('t_stamp'):
							currentValue = r_history.getValueAt(j,c)
							currentRow.append(0 if currentValue is None else currentValue)
							headers.append(r_history.getColumnName(c))
						
					newRow = [0 if history.getValueAt(r,c) is None else history.getValueAt(r,c) for c in range(history.columnCount) if c != history.getColumnIndex('t_stamp')]
					changes = {h:x+y for h,x,y in zip(headers, currentRow, newRow)}
					r_history = system.dataset.updateRow(r_history, j, changes)
					break

This is the script i have excepted thanks again

1 Like