Nick Mudge Ignition Software Consulting & Development

A dataset in Ignition is a set of rows and columns holding values, like a spreadsheet.

Many components in Ignition use datasets, including table components, dropdown list, list,template repeater and canvas and chart components. In addition database queries return results as a dataset.

It is very true that if somebody is going to do any Python scripting in Ignition then he/she is going to be dealing with datasets.

Being able to access data in datasets, create new datasets and create modified versions of datasets are importand skills, and being able to do so smoothly and easily makes programming in Ignition that much smoother and easier.

This article is going to describe three different kinds of datasets.

Dataset

The Dataset dataset is a Java data type that is used by component properties in Ignition. For example the "data" property of an Ignition table is a Dataset.

Dataset are treated as immutable. That means Datasets are not changed. When you want to "change" a Dataset you actually create a new, different Dataset that is different in someway than the one before. Some built-in system.dataset.* functions in Ignition make it easier to do. Here is an example that makes all the values in a column upper case:

table = event.source.parent.getComponent('Table')
for rowIndex in range(table.data.rowCount):
    value = table.data.getValueAt(rowIndex,"Col 2")	
    table.data = system.dataset.setValue(table.data, rowIndex, "Col 2",value.upper())

Notice that the system.dataset.setValue function assigns its return value to the "data" property of the table in each iteration of the loop. This is because the system.dataset.setValue function actually creates a new Dataset each time it is called, copying the Dataset that was passed in as an argument except making the specified value different.

Also notice that we cannot directly loop through the rows of the Dataset. Instead we need to create a Python list of integers by using the range function and then loop through the integer list, using each integer as a rowIndex. The Dataset data type does not provide any of Pythons nice and easy syntax for looping through data, accessing data or modifying data.

PyDataSet

A PyDataSet is similar to a Dataset but it provides some of Python's nice syntax for looping, row access and slicing. PyDataSet isn't useful for anything else. Like Dataset, PyDataSet is immutable. But there are no functions for creating new different PyDataSets. Database queries in Ignition return PyDataSets. Here's some examples:

table = event.source.parent.getComponent('Table')
#Convert a Dataset into a PyDataSet
data = system.dataset.toPyDataSet(table.data)
#loop directly through a PyDataSet
for row in data:
    #Nice python syntax for accessing values in rows
    print row["Col 2"]

#Nice python syntax for row access
print data[2]

#Nice python syntax for row and column access to get values
print data[2]["Col 2"]

#Nice python slice syntax for getting the first three rows
firstThreeRows = data[:3]
print firstThreeRows

#Python syntax to get the last row? No cigar, doesn't work.
print data[-1]

#No, this does not work because PyDataSet is immutable, cannot be changed
data[2]["Col 2"] = "something"

A PyDataSet can help you manually create a different dataset. Here's a way to make all the values in a column uppercase:

table = event.source.parent.getComponent('Table')
newData = []
columnNames = list(table.data.getColumnNames())
pyDataSet = system.dataset.toPyDataSet(table.data)
for row in pyDataSet:
    rowList = []
    for column in columnNames:
        if column == "Col 2":
            rowList.append(row[column].upper())
        else:
            rowList.append(row[column])
    newData.append(rowList)
table.data = system.dataset.toDataSet(columnNames,newData)

This code copies the PyDataSet into a Python list of lists, except it uppercases the "Col 2" column. Once this is done the list of lists is converted into a Dataset and assigned to the table.

MutablePyDataSet

The MutablePyDataSet takes things further. It is like a PyDataSet except it is mutable and it has many many methods for accessing data in it, sorting, searching and modifying it. Here's the code for uppercasing a column:

table = event.source.parent.getComponent('Table')
#convert the Dataset into a MutablePyDataSet
data = pa.dataset.toData(table.data)
for row in data:
    row["Col 2"] = row["Col 2"].upper()
table.data = data.toDataSet()

Very simple, we convert a Dataset into a MutablePyDataSet, loop though it and directly change the "Col 2" column in each row. Then it is converted back into a Dataset.

Here's another example that achieves the same thing but in a completely different way. It shows off some interesting and useful ways to access and change data:

table = event.source.parent.getComponent('Table')
data = pa.dataset.toData(table.data)
data.setColumn("Col 2",map(unicode.upper,data.columns["Col 2"]))
table.data = data.toDataSet()

To summerize briefly, the third line of code accesses the "Col 2" column of the MutablePyDataSet, creates a new "Col 2" list with uppercase values and sets the values in the "Col 2" column of the MutablePyDataSet.

Here's a more detailed description: The 'data.columns["Col 2"]' part returns all the values in the "Col 2" column as a Python list. 'unicode.upper' is a function that uppercases unicode strings. The 'map' function applies the 'unicode.upper' function to each item in the 'data.columns["Col 2"]' list and returns the results as a new list.

The 'data.setColumn' method sets the 'Col 2' column position in all rows in the MutablePyDataSet to the values from the list created by 'map(unicode.upper,data.columns["Col 2"])'.

One of the nice things MutablePyDataSet does is provide a very nice print out of its contents. This is very useful for development and debugging. Here is an example:

table = event.source.parent.getComponent('Table')
print pa.dataset.toData(table.data)
Output Console:
row | Col 1 Col 2       Col 3        
-------------------------------------
 0  | 84    TEST ROW 11 43.4355031232
 1  | 12    TEST ROW 16 87.0748299491
 2  | 21    TEST ROW 1  46.9309520282
 3  | 76    TEST ROW 0  79.041838165 
 4  | 94    TEST ROW 1  17.8630985677
 5  | 97    TEST ROW 2  94.3354725437
 6  | 20    TEST ROW 17 27.9129276711

The output shows the columns and rows of the data.

There are many examples of various capabilities and methods of MutablePyDataSet I could show you but an example already exists for each capability and method in the MutablePyDataSet documentation.

You can find real-world examples of using MutablePyDataSet on the Inductive Automation forum by searching for MutablePyDataSet.

The MutablePyDataSet is part of the PA Power Scripting Module.

Comments

Name: (required)
Email: (required)
Website:
What has four legs, rhymes with bat and says, "Meow?" (One word answer.)
Spam Filter: