# TIP - Converting a dataset column into a list

**URL:** <https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388>\
**Category:** Ignition\
**Tags:** ignition80\
**Created:** [September 4, 2020, 4:07am UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388 "2020-09-04T04:07:03Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![kgamble](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kgamble/32/8019_2.png) [@kgamble](https://forum.inductiveautomation.com/u/kgamble)\
**Post date:** [September 4, 2020, 4:07am UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/1 "2020-09-04T04:07:03Z")

</div>

Something that I have done a ton, is query a list of values from a database, and then convert them into a list for some form of manipulation. Maybe I need to generate a list of users, equipment, or really anything.

I have always converted my datasets into lists the following way:

```auto
myList = []
for row in range(dataset.getRowCount()):
	myList.append(dataset.getValueAt(row, "myColumn"))

```

Well, today I feel dumb because I learned that you can do

```auto
myList = dataset.getColumnAsList("myColumn")

```

I figured I would share incase anyone has somehow missed this being a method on the dataset object like I have!

Enjoy

---

<div class="post-metadata">

**Author:** ![Matthew.gaitan](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@Matthew.gaitan](https://forum.inductiveautomation.com/u/Matthew.gaitan)\
**Post date:** [September 4, 2020, 6:31am UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/2 "2020-09-04T06:31:14Z")

</div>

> [@kgamble](#):
>
> se anyone has somehow missed this being a method on the dataset object like I have!
> 
> Enjoy

huh. I too did not know this existed! Thank you!

---

<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, 2020, 12:51pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/3 "2020-09-04T12:51:46Z")

</div>

Beware! That is not defined on the `Dataset` Interface, but on the `BasicDataset` implementing class. There are implementations of `Dataset`, particularly in Gateway scope, that don’t have it.

---

<div class="post-metadata">

**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\
**Post date:** [September 4, 2020, 2:36pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/4 "2020-09-04T14:36:22Z")

</div>

@pturmel

Can you elaborate further? Will this proposed method always work on datasets returned from a query?

---

<div class="post-metadata">

**Author:** ![kgamble](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kgamble/32/8019_2.png) [@kgamble](https://forum.inductiveautomation.com/u/kgamble)\
**Post date:** [September 4, 2020, 2:39pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/5 "2020-09-04T14:39:52Z")

</div>

The method is present on the `BasicStreamingDataset` class that is returned by a query binding in perspective.

I have noticed in query bindings it will not take a string parameter though, only a column index.

> [@pturmel](#):
>
> That is not defined on the `Dataset` Interface, but on the `BasicDataset` implementing class. There are implementations of `Dataset` , particularly in Gateway scope, that don’t have it.

To agree with this statement, I know that it works on query bindings and datasets returned from `system.db.runNamedQuery()`, however there are surely other instances that cant use it (before 8.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, 2020, 2:53pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/6 "2020-09-04T14:53:21Z")

</div>

`ds.getColumnAsList(ds.getColumnIndex("column"))` should be safe on any dataset in 8.0+ - `getColumnAsList` accepting an integer is part of the Dataset interface. Note that if the column isn’t found you’ll get an index out of bounds exception.

---

<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, 2020, 2:57pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/7 "2020-09-04T14:57:52Z")

</div>

> [@paul-griffith](#):
>
> 8.0+ - `getColumnAsList` accepting an integer is part of the Dataset interface

Ah! Hadn't noticed that change.

---

<div class="post-metadata">

**Author:** ![kgamble](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kgamble/32/8019_2.png) [@kgamble](https://forum.inductiveautomation.com/u/kgamble)\
**Post date:** [September 4, 2020, 3:11pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/8 "2020-09-04T15:11:15Z")

</div>

> [@paul-griffith](#):
>
> ds.getColumnAsList(ds.columnNames.indexOf("column"))

Any reason why sometimes I have found this will take a column name and sometimes requires an index?

---

<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, 2020, 3:18pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/9 "2020-09-04T15:18:30Z")

</div>

> [@paul-griffith](#):
>
> ds.columnNames.indexOf("column")

Blegh! Please use `ds.getColumnIndex("column")` if you care about speed.

---

<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, 2020, 3:24pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/10 "2020-09-04T15:24:23Z")

</div>

> [@kgamble](#):
>
> Any reason why sometimes I have found this will take a column name and sometimes requires an index?

As @pturmel pointed out, it's not part of the Dataset _interface_, so implementing classes are not guaranteed to have it. Retrieving by column index is the only 'safe' way.

> [@pturmel](#):
>
> Blegh! Please use `ds.getColumnIndex("column")` if you care about speed.

Sure, but we're using `indexOf` internally in `AbstractDataset` 🤷‍♂️

---

<div class="post-metadata">

**Author:** ![kgamble](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kgamble/32/8019_2.png) [@kgamble](https://forum.inductiveautomation.com/u/kgamble)\
**Post date:** [September 4, 2020, 3:26pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/11 "2020-09-04T15:26:24Z")

</div>

> [@pturmel](#):
>
> Blegh! Please use `ds.getColumnIndex("column")` if you care about speed.

For prosperities sake, the following _quick_ test shows that the bottom three methods are all equally as fast, and MUCH faster than the iterative way that I used to use

```auto

def convertMethod1(ds):
	newRows = []
	for row in range(ds.getRowCount()):
		newRows.append(ds.getValueAt(row, "ID"))
	return newRows

def convertMethod2(ds):
	return ds.getColumnAsList(0)

def convertMethod3(ds):
	return ds.getColumnAsList(ds.columnNames.indexOf("ID"))

def convertMethod4(ds):
	return ds.getColumnAsList(ds.getColumnIndex("ID"))

def timeFunction(func, ds):
	time = system.date.toMillis(system.date.now())
	myRows = func(ds)
	end = system.date.toMillis(system.date.now())
	print "%s took %s milliseconds" % (str(func), end - time)
	

dsRows = []
for i in range(1000000):
	dsRows.append([i,"String %s" % i])

ds = system.dataset.toDataSet(['ID', 'StringVal'], dsRows)

timeFunction(convertMethod1, ds)
timeFunction(convertMethod2, ds)
timeFunction(convertMethod3, ds)
timeFunction(convertMethod4, ds)

```

With the result set:

```auto
<function convertMethod1 at 0x15> took 428 milliseconds
<function convertMethod2 at 0x16> took 9 milliseconds
<function convertMethod3 at 0x17> took 10 milliseconds
<function convertMethod4 at 0x18> took 10 milliseconds
	

```

---

<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, 2020, 3:39pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/12 "2020-09-04T15:39:32Z")

</div>

> [@paul-griffith](#):
>
> > [@pturmel](#):
> >
> > Blegh! Please use `ds.getColumnIndex("column")` if you care about speed.
> 
> Sure, but we’re using `indexOf` internally in `AbstractDataset`

But by referencing `.columnNames` you are calling `.getColumnNames()` to make a new list--not free--then searching it in a case-sensitive way. `.getColumnIndex()` is both faster and case insensitive. Of course, in any row-looping algorithm, the column index should be looked up just once, ahead of the loop, but that sort of optimization is lost on most people.

---

<div class="post-metadata">

**Author:** ![kgamble](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kgamble/32/8019_2.png) [@kgamble](https://forum.inductiveautomation.com/u/kgamble)\
**Post date:** [September 4, 2020, 4:18pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/13 "2020-09-04T16:18:37Z")

</div>

Question while you’re here, is it possible to call the automatic dataset to json conversion used in a query binding?

I know that there is `system.dataset.toJSONObject()` that returns it in pure json, but that isnt able to be de-serialized with `system.util.jsonDecode()` due to the way `null`'s are stored

---

<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, 2020, 4:22pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/14 "2020-09-04T16:22:36Z")

</div>

There’s a public static method, but it would only be accessible inside Perspective scripts - it delegates because of special handling we have to use to send quality codes back and forth between Perspective’s frontend and backend. There’s `com.inductiveautomation.ignition.common.TypeUtilities#datasetToJSON`, but it’s got its own problems, and a very limited representation format.

---

<div class="post-metadata">

**Author:** ![kgamble](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kgamble/32/8019_2.png) [@kgamble](https://forum.inductiveautomation.com/u/kgamble)\
**Post date:** [September 4, 2020, 4:24pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/15 "2020-09-04T16:24:44Z")

</div>

> [@paul-griffith](#):
>
> There’s a public static method, but it would only be accessible inside Perspective scripts

Would it be reliable enough within Perspective to call it at the end of a script transform?

---

<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, 2020, 4:27pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/16 "2020-09-04T16:27:59Z")

</div>

Should be, it’s exactly the same as what we call at the end of queries.  
`com.inductiveautomation.perspective.gateway.binding.BindingUtils#datasetToJson`

\*With a caveat that this is an implementation detail, and a future refactoring of Ignition’s code could theoretically move this method, rename it, change the call signature, etc.

---

<div class="post-metadata">

**Author:** ![kgamble](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kgamble/32/8019_2.png) [@kgamble](https://forum.inductiveautomation.com/u/kgamble)\
**Post date:** [September 4, 2020, 4:32pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/17 "2020-09-04T16:32:28Z")

</div>

> [@paul-griffith](#):
>
> With a caveat that this is an implementation detail, and a future refactoring of Ignition’s code could theoretically move this method, rename it, change the call signature, etc.

`system.dataset.toJSON()` sounds like a great idea for a supported function to me 🤷‍♂️ hahaha

---

<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 4, 2020, 4:38pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/18 "2020-09-04T16:38:09Z")

</div>

There is system.util.jsonEncode()  
[https://docs.inductiveautomation.com/display/DOC79/system.util.jsonEncode](https://docs.inductiveautomation.com/display/DOC79/system.util.jsonEncode)

We use it to convert datasets to json with the webDev module.

---

<div class="post-metadata">

**Author:** ![kgamble](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kgamble/32/8019_2.png) [@kgamble](https://forum.inductiveautomation.com/u/kgamble)\
**Post date:** [September 4, 2020, 4:46pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/19 "2020-09-04T16:46:10Z")

</div>

> [@MMaynard](#):
>
> There is system.util.jsonEncode()

This works if I convert it to a pyDataset first, however the function aforementioned converts it into more of an object

So where `jsonEncode` will convert it into this format:

```auto
{
  "columns": [
    {
      "name": "Column1",
      "type": "java.lang.Double"
    },
    {
      "name": "Column2",
      "type": "java.lang.String"
    }
  ],
  "rows": [
    [
      1.0,
      5842.081
    ],
    [
      3.0,
      8305.222
    ],
    [
      5.0,
      11238.889
    ]
  ]
}

```

The aforementioned function built into a perspective transform would return it as an object formatted like this:

```auto
[
  {
    "Column1": 1.0,
    "Column2": 5842.081
  },
  {
    "Column1": 3.0,
    "Column2": 8305.222
  },
  {
    "Column1": 5.0,
    "Column2": 11238.889
  }
]

```

---

<div class="post-metadata">

**Author:** ![tmahaffey](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tmahaffey/32/30949_2.png) [@tmahaffey](https://forum.inductiveautomation.com/u/tmahaffey)\
**Post date:** [February 25, 2021, 7:26pm UTC](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388/20 "2021-02-25T19:26:48Z")

</div>

I’m wondering why this isn’t in the scripting functions if what I’m reading says that in version 8 there are not scope limitations for this fucntion. It’s a neat function and I have to comb through the forum to find it.

[Next page](https://forum.inductiveautomation.com/t/tip-converting-a-dataset-column-into-a-list/38388.md?page=2)