# Loop through Dataset and subtract columns

**URL:** <https://forum.inductiveautomation.com/t/loop-through-dataset-and-subtract-columns/49776>\
**Category:** Ignition\
**Tags:** ignition79, scripting, vision\
**Created:** [August 10, 2021, 7:14pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-subtract-columns/49776 "2021-08-10T19:14:04Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![nickboratyn](https://avatars.discourse-cdn.com/v4/letter/n/9d8465/32.png) [@nickboratyn](https://forum.inductiveautomation.com/u/nickboratyn)\
**Post date:** [August 10, 2021, 7:14pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-subtract-columns/49776/1 "2021-08-10T19:14:04Z")

</div>

Hey guys,

So I have a dataset as such:  
Bin1Mat Bin1Act Bin1Targ  
Sand 1715 1700  
Sand 1705 1700  
Sand 1698 1700

Is there any good was to swift through this dataset and subtract the actual weights from the target weight and add each result to a new dataset?

Thanks a lot  
NB

---

<div class="post-metadata">

**Author:** ![grietveld](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/grietveld/32/6984_2.png) [@grietveld](https://forum.inductiveautomation.com/u/grietveld)\
**Post date:** [August 10, 2021, 7:38pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-subtract-columns/49776/2 "2021-08-10T19:38:07Z")

</div>

There is a pretty good example of this in the documentation for system.dataset.addColumn. Should be able to mostly copy paste and then return the data to whatever is using it.

[https://docs.inductiveautomation.com/display/DOC81/system.dataset.addColumn](https://docs.inductiveautomation.com/display/DOC81/system.dataset.addColumn)

---

<div class="post-metadata">

**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\
**Post date:** [August 10, 2021, 8:11pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-subtract-columns/49776/3 "2021-08-10T20:11:06Z")

</div>

Something like this? This will only work with a pydataset, you would need to convert it back and forth to use in a table or tag.

```auto
d = [['Sand',1715,1700],['Sand',1705,1700],['Sand',1698,1700]]
r = [[x[0],x[1],x[2],x[1]-x[2]] for x in d]

```

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

---

<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:** [August 10, 2021, 8:40pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-subtract-columns/49776/4 "2021-08-10T20:40:08Z")

</div>

I’m a bit biased towards my [view()](https://www.automation-pros.com/simaids/doc/expression.view.html) expression function, from [Simulation Aids](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-simulation-aids). The binding would look like this:

```auto
view("Select *, Bin1Act-Bin1Targ As Bin1Delta", {Root Container.path.to.source.data})

```

---

<div class="post-metadata">

**Author:** ![nickboratyn](https://avatars.discourse-cdn.com/v4/letter/n/9d8465/32.png) [@nickboratyn](https://forum.inductiveautomation.com/u/nickboratyn)\
**Post date:** [August 11, 2021, 1:58pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-subtract-columns/49776/5 "2021-08-11T13:58:51Z")

</div>

I used that addcolumn function and I think I am close.  
Here is my code so far, the issue I am having now is that I want 24 new columns at the end of the existing dataset and right now it is only creating one new column at the end of the existing dataset.  
Its like it runs through the loop and puts the last iteration in the existing dataset.

sTime = system.tag.read("[Furnace 51]Dashboard/StartTime")  
eTime = system.tag.read("[Furnace 51]Dashboard/EndTime")  
batch = system.db.runNamedQuery(“BatchQuery”,{“StartTime”:sTime.value,“EndTime”:eTime.value})  
y = 1  
actCol = 2  
targCol = 3  
newbatch =

while y != 25:

```
colCount = batch.getColumnCount()
columnName = "Bin"+str(y)+"Err"
columnData = []

for i in range(batch.getRowCount()):
	val1 = batch.getValueAt(i,int(actCol))
	val2 = batch.getValueAt(i,int(targCol))
	columnData.append(float(val1) - float(val2))
newbatch = system.dataset.addColumn(batch, colCount, columnData, columnName, float)

y = y + 1
actCol = actCol + 3
targCol = targCol + 3

```

newbatch.getColumnNames()