[00:00] In this lesson, we're going to look at how to manipulate data sets through scripting. Data sets are a tabular type of data that Ignition uses very heavily, especially in the vision module both for information that tables and charts display as well as a lot of components, store configuration, information and data sets. And a data set is a structure that mimics exactly the return value of what a sequel select query would return in that there's a number of columns and the columns each have a name and a data type and then you can have any number of rows. So, you'll find in your journey of using Ignition that there's a number of times where it becomes convenient to manipulate data sets through scripting, so that's what we're going to look at here today.

[01:03] This first script is going to calculate the average value of the first column of this table and in these examples I'm just using a tabled data set as my source of data but the techniques would be absolutely equivalent if the data came straight out of a sequel database via a select query. So what this button does is it first it grabs a reference to the table and then it grabs the table's data but it wraps it up in this system.dataset.toPyDataSet function which takes a dataset and turns it into a Py dataset, so there's these two type of objects that we have and you can convert at will between the two via to dataset and toPy dataset. They represent the exact same information. The only difference is that a Py dataset acts more like a well-made Python data structure in that you can use the len function on it and you can iterate over it in a for loop, so the rule of thumb is that if you're doing a bunch of iteration over something, a Py dataset's probably going to be more convenient.

[02:13] Otherwise you can just stick with the plain dataset. So, in this case because we're calculating the average, we're going to switch it to a Py dataset and you can see how clean this makes our code. We initialize a total variable and we just say for row in data, and that's going to loop through all the rows in the dataset and then we're just going to totalize the first column here and we can access the values inside the row either via if you use an integer here it's going to obviously reference them by position but you could use a string here and reference it by column name also, and then it's going to print out our average. So let's go see what our average is. All right, average is 47, so you can see this thing works. Now our next example is actually going to modify the dataset of the table by deleting the selected row. So let's see this in action real quick. Delete, delete, delete. Pretty simple.

[03:10] Let's see how it works. So again we grab the table and we grab the table's dataset. This time we're not wrapping it up in a Py dataset because we're not doing any looping. So we grab the table's data set. We grab the table's selected row. Of course in real life we would want to check and make sure that this wasn't negative one to make sure that something is actually selected, but for the purpose of this demo we wanted to keep it real simple. And here we can just use the system.dataset.deleteRow function which takes a dataset and a row number to delete and the key that you need to understand for all of these different functions that are inside of system.dataset that manipulate datasets is that they don't actually alter the dataset you give it, so this dataset is not changing. What this function does is it efficiently creates a new dataset with the alteration applied because datasets can't actually be changed. Once they're created, they're not editable.

[04:10] So you can see I'm assigning the return value of this function to a variable called new data and then I put that right inside of table.data and that's how it appears that the rows are being deleted. Similarly, we've got a copy selected which is going to copy the selected row and inject it into the dataset, and that works in a really similar manner. The only difference here is that we get the selected row and then we create a Python list with three entries in it and this example shows how you can get at a cell's value of a dataset if you didn't convert it to a Py dataset. You can use this getValueAt function that's on datasets and you give it a row index and a column index. And again, this can either be an integer or a column name string, and then we just call system.dataset.addRow which takes a dataset, an index, and the row to add which creates a new dataset which we then assign onto the table.