# Set entire column to value with button

**URL:** <https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530>\
**Category:** Ignition\
**Created:** [September 4, 2018, 7:50pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530 "2018-09-04T19:50:04Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [September 4, 2018, 7:50pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/1 "2018-09-04T19:50:04Z")

</div>

I’m trying to set an entire column of values equal to 0. I’ve tried about 50 different ways, and keep failing miserably. I’ve found several snippets that are close to what I want, but I can’t get any to work.

```auto
table = event.source.parent.getComponent('poGroup').getComponent('Table3').data
pydata = system.dataset.toPyDataSet(table)

for row in pydata:
	row['QUANTITY'] = 0

table.data = pydata
```

---

<div class="post-metadata">

**Author:** ![mrogers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mrogers/32/4110_2.png) [@mrogers](https://forum.inductiveautomation.com/u/mrogers)\
**Post date:** [September 4, 2018, 7:58pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/2 "2018-09-04T19:58:04Z")

</div>

Almost there - try this

```auto
table = event.source.parent.getComponent('poGroup').getComponent('Table3').data
pydata = system.dataset.toPyDataSet(table)
#this gets a list of column headers from the dataset
headers = system.dataset.getColumnHeaders(table)
for row in pydata:
	row['QUANTITY'] = 0
#You changed it to a pydataset, but you need to get back to a regular dataset
table.data = system.dataset.toDataSet(headers, pydata)

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [September 4, 2018, 8:04pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/3 "2018-09-04T20:04:28Z")

</div>

This looks better than what I had, but it says that object does not support item assignment, referring to row[‘QUANTITY’] = 0.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [September 4, 2018, 8:05pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/4 "2018-09-04T20:05:28Z")

</div>

Try a different approach, using standard datasets throughout:

```auto
colName = 'QUANTITY'
table = event.source.parent.getComponent('poGroup').getComponent('Table3')
ds = table.data
colIndex = ds.getColumnIndex(colName)
colType = ds.getColumnType(colIndex)
ds = system.dataset.filterColumns(ds, [i for i in range(ds.columnCount) if i != colIndex])
ds = system.dataset.addColumn(ds, colIndex, [0] * ds.rowCount, colName, colType)

table.data = ds

```

---

<div class="post-metadata">

**Author:** ![mrogers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mrogers/32/4110_2.png) [@mrogers](https://forum.inductiveautomation.com/u/mrogers)\
**Post date:** [September 4, 2018, 8:08pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/5 "2018-09-04T20:08:13Z")

</div>

I was going to suggest using system.dataset.setValue iterating through rows, but this looks like a more straightforward way.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [September 4, 2018, 8:08pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/6 "2018-09-04T20:08:50Z")

</div>

I got this with your code

```auto
TypeError: can't put element of type class java.lang.Integerin column of type class java.lang.Double

```

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [September 4, 2018, 8:46pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/7 "2018-09-04T20:46:06Z")

</div>

> [@dkhayes117](#):
>
> I got this with your code

I don't see how - filterColumn will remove the type completely, and the newly generated row should work just fine. I did some testing and wasn't able to reproduce that error.

> [@mrogers](#):
>
> I was going to suggest using system.dataset.setValue iterating through rows, but this looks like a more straightforward way.

I was curious about this, so I did some benchmarking, and (not really surprisingly) setting the values-per-row is _incredibly_ slower - many orders of magnitude as the row count increases:

```python
Running 'zeroColumnAtOnce' on a dataset with 100 rows 10000 times took 204ms; average: 0.0204ms
Running 'zeroColumnIteratively' on a dataset with 100 rows 10000 times took 5133ms; average: 0.5133ms
Running 'zeroColumnAtOnce' on a dataset with 1000 rows 1000 times took 98ms; average: 0.098ms
Running 'zeroColumnIteratively' on a dataset with 1000 rows 1000 times took 39854ms; average: 39.854ms

```

And the code I used, including a slightly cleaned up version of what I originally posted:

```python
from java.lang import Double, Float, Long

def zeroColumnAtOnce(ds, colName):
	colIndex = ds.getColumnIndex(colName)
	colType = ds.getColumnType(colIndex)
	ds = system.dataset.filterColumns(ds, [i for i in range(ds.columnCount) if i != colIndex])
	ds = system.dataset.addColumn(ds, colIndex, [0] * ds.rowCount, colName, colType)
	
	return ds
	
def zeroColumnIteratively(ds, colName):
	copy = ds
	for row in range(ds.rowCount):
		copy = system.dataset.setValue(copy, row, colName, 0)
	return copy
	
def generateSample(rows=1000, cols=5):
	sampleDataHeaders = ["Column %s" % (col + 1) for col in range(cols)]
	sampleData = [[i, Float(i), Double(i), Long(i), str(i)] for i in range(rows)]
	return system.dataset.toDataSet(sampleDataHeaders, sampleData)

def time(function, runs, **kwargs):
	start = system.date.now()
	data = generateSample(**kwargs)
	for run in range(runs):
		function(data, "Column 3")
	end = system.date.now()
	elapsed = system.date.millisBetween(start, end)
	avg = elapsed / float(runs)
	rows = data.rowCount
	print "Running '%s' on a dataset with %s rows %s times took %sms; average: %sms" % (function. __name__ , rows, runs, elapsed, avg)
	
time(zeroColumnAtOnce, 10000, rows=100)
time(zeroColumnIteratively, 10000, rows=100)

```

---

<div class="post-metadata">

**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\
**Post date:** [September 4, 2018, 9:13pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/8 "2018-09-04T21:13:15Z")

</div>

To expand on _why_ setting one value at a time is slower, remember that datasets are immutable. That means each time you call system.dataset.setValue(), all the data is copied to a whole new dataset. So changing a single value in each of 1000 rows means each value in the entire dataset is read and written to a new dataset 1000 times. If you do filterColumns and then addColumn, each value is read twice and written twice (once for each operation).

Always prefer the bulk operations on datasets when you can.

---

<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:** [September 4, 2018, 9:50pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/9 "2018-09-04T21:50:26Z")

</div>

To keep the overhead to the barest minimum, use [DatasetBuilder](http://files.inductiveautomation.com/sdk/javadoc/ignition79/795/com/inductiveautomation/ignition/common/util/DatasetBuilder.html) and do everything in one pass.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [September 5, 2018, 12:49pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/10 "2018-09-05T12:49:17Z")

</div>

I would never call myself an actual programmer, I get by with what I know and that’s it. I still don’t have any working code. I don’t really care about efficiency, I’m looking for simple code. I will not be using this over a 50 row count.  
753rd try doesn’t error out, but nothing happens either:

```auto
table = event.source.parent.getComponent('poGroup').getComponent('Table3').data

for i in range(table.rowCount):
	newData = system.dataset.setValue(table, i, 'QUANTITY', 0)
table = newData

```

---

<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:** [September 5, 2018, 1:06pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/11 "2018-09-05T13:06:02Z")

</div>

> [@dkhayes117](#):
>
> but nothing happens either:

That's because your final assignment is to a local variable, instead of the data property of `Table3`. You are also overwriting newData in every iteration, so the final assignment will only reflect a change to the last row.

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [September 5, 2018, 1:20pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/12 "2018-09-05T13:20:26Z")

</div>

You need to wrap the 0 in quotes to use this.

> [@paul-griffith](#):
>
> ds = system.dataset.addColumn(ds, colIndex, ['0'] \* ds.rowCount, colName, colType)

I tested it with a standard power table with random data on the string column.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [September 5, 2018, 1:28pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/13 "2018-09-05T13:28:39Z")

</div>

I appreciate the replies, I got it working finally with a little nudge from @pturmel. I’m sure that @paul-griffith’s solution is more efficient then what I came up with, but it works and I’m using it on small datasets so if the shoe fits!

`table = event.source.parent.getComponent('poGroup').getComponent('Table3')
idx = table.data.rowCount
for i in range(idx):
	newData = system.dataset.setValue(table.data, i, 'QUANTITY', 0)
	table.data = newData
`

---

<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:** [September 5, 2018, 1:54pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/14 "2018-09-05T13:54:35Z")

</div>

Ugh! No. Don’t assign to table.data within the loop. That’ll generate a propertyChange event for every row you change. Use a local dataset variable instead of table.data within the loop (preloading with table.data before the loop). Then assign back to table.data after the loop, yielding a single change event.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [September 5, 2018, 2:10pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/15 "2018-09-05T14:10:27Z")

</div>

How’s this

`table = event.source.parent.getComponent('poGroup').getComponent('Table3')
copy = table.data
idx = table.data.rowCount
for i in range(idx):
	newData = system.dataset.setValue(copy, i, 'QUANTITY', 0)
	copy = newData
table.data = copy
`

---

<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:** [September 5, 2018, 2:14pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/16 "2018-09-05T14:14:10Z")

</div>

You’re an actual programmer now, @dkhayes117 !  
😉

---

<div class="post-metadata">

**Author:** ![ctonge](https://avatars.discourse-cdn.com/v4/letter/c/ec9cab/32.png) [@ctonge](https://forum.inductiveautomation.com/u/ctonge)\
**Post date:** [September 7, 2018, 1:16pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/17 "2018-09-07T13:16:07Z")

</div>

Just going to put a plug in for the Perfect Abstractions mutable pydataset here…check it out in their Power Scripting module. For operations like this it’s much more intuitive. Not sure how it stacks up in performance tests vs system.dataset operations, but I’d have to guess it’s more efficient.

> **[PA Power Scripting Module](https://www.perfectabstractions.com/pa-power-scripting-module.html)**
>
> Price: $200 ​ The Perfect Abstractions (PA) Power Scripting Module adds powerful scripting capabilities and features to Ignition.  The module works fully without a license in an Ignition designer....

---

<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:** [September 7, 2018, 1:24pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/18 "2018-09-07T13:24:36Z")

</div>

> [@ctonge](#):
>
> Just going to put a plug in for the Perfect Abstractions mutable pydataset here

Dangerous. All other datasets are immutable (after finalization for a few specialized ones in gateway context), and deliberately so. Immutability is crucial to generating propertyChange events within the Java Swing ecosystem. The presence of this datatype in the Power Scripting module prevents me from ever recommending it, despite its other useful features.

---

<div class="post-metadata">

**Author:** ![ctonge](https://avatars.discourse-cdn.com/v4/letter/c/ec9cab/32.png) [@ctonge](https://forum.inductiveautomation.com/u/ctonge)\
**Post date:** [September 20, 2018, 7:59pm UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/19 "2018-09-20T19:59:27Z")

</div>

If you can expand on what you mean by dangerous I’m all ears. Converting a table component’s dataset to a pa mutable dataset, and manipulating it’s data, then converting back to an ignition dataset for your table component is a bad thing? Keep in mind that you are levels above me in your knowledge here, I’m a converted SCADA guy, not a software developer.

---

<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:** [September 21, 2018, 2:45am UTC](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530/20 "2018-09-21T02:45:51Z")

</div>

> [@ctonge](#):
>
> then converting back to an ignition dataset for your table component is a bad thing?

No, that part is fine. It's the assigning it to the table component and mutating in place (because it's mutable) that is bad.

[Next page](https://forum.inductiveautomation.com/t/set-entire-column-to-value-with-button/19530.md?page=2)