# Sum multiple datasets

**URL:** <https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146>\
**Category:** Ignition\
**Tags:** vision\
**Created:** [January 30, 2023, 1:01pm UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146 "2023-01-30T13:01:45Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [January 30, 2023, 1:01pm UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146/1 "2023-01-30T13:01:45Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/2/e2eeeb8e004bfdfb98bd28936dc142a567a043ac.png)

```auto
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

---

<div class="post-metadata">

**Author:** ![mmunin](https://avatars.discourse-cdn.com/v4/letter/m/c0e974/32.png) [@mmunin](https://forum.inductiveautomation.com/u/mmunin)\
**Post date:** [January 30, 2023, 6:00pm UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146/2 "2023-01-30T18:00:58Z")

</div>

The first thing you probably want to do is look at [this](https://docs.inductiveautomation.com/display/DOC81/Datasets) page.

Your code will look something like this.

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

```

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [January 31, 2023, 3:33am UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146/3 "2023-01-31T03:33:21Z")

</div>

> [@mmunin](#):
>
> ```auto
> for index, row in enumerate(pyds1):
> row[1] = row[1] + pyds2[index][1] 
> 
> return system.dataset.toDataSet(pyds1)
> 
> ```

getting this error  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/9/b92f7444ca0a83745e9ebffaf2cfa2eb9c5d506e.png)  
if my dataset have multiple columns how to sum it?

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [January 31, 2023, 4:10am UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146/4 "2023-01-31T04:10:57Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [January 31, 2023, 4:28am UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146/5 "2023-01-31T04:28:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [January 31, 2023, 4:33am UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146/6 "2023-01-31T04:33:55Z")

</div>

above code already sum

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [January 31, 2023, 4:37am UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146/7 "2023-01-31T04:37:03Z")

</div>

yaa but i have corrected the code for your understanding

```auto
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

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [January 31, 2023, 4:40am UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146/8 "2023-01-31T04:40:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [January 31, 2023, 4:59am UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146/9 "2023-01-31T04:59:05Z")

</div>

ohh

```auto
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

```

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [January 31, 2023, 5:09am UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146/10 "2023-01-31T05:09:52Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [January 31, 2023, 5:16am UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146/11 "2023-01-31T05:16:24Z")

</div>

```auto
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

```

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [January 31, 2023, 5:29am UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146/12 "2023-01-31T05:29:20Z")

</div>

> [@jespinmartin1](#):
>
> ```auto
> 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
> 
> ```

its suming all in first column - not like that

you can remove base path- only use this code

```auto
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

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [January 31, 2023, 6:42am UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146/13 "2023-01-31T06:42:50Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [January 31, 2023, 6:51am UTC](https://forum.inductiveautomation.com/t/sum-multiple-datasets/69146/14 "2023-01-31T06:51:45Z")

</div>

> [@jespinmartin1](#):
>
> ```auto
> 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
> 
> ```

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

```auto
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**