[00:00] In this lesson, we'll examine how to manipulate datasets through scripting. There will be many times when manipulating datasets becomes convenient or even necessary, so we'll show examples of how to do that. A dataset is a tabular data structure, much like this one, consisting of rows and columns. It mimics exactly what a SQL select query would return with a fixed number of columns with names and data types or database fields, and any number of rows or database records. Datasets are used heavily by ignition, especially in the vision module. They're commonly used for the information that tables and charts display, and by components to store their configuration information. Two things of note regarding datasets. First, datasets are not native to Python. They are built into ignition for their usefulness in handling database data. You can think of them as lists of lists. Second, datasets are immutable. They can't be changed once created, and aren't editable, but we'll see how to get around this later in this lesson.

[01:04] In the example shown here in our designer, this is just a simple table component from the pallet filled with randomized data. Then there are six buttons here, each one backed by a small event script, demonstrating some typical usage of datasets. We can access an event script by right-clicking and selecting its scripting option, but to make this lesson a bit more efficient, we already have those six event scripts open but minimized here in the task bar. We'll refer to each one as we demonstrate each of the button actions. It's worth noting that all of the techniques to be shown would be identical whether we were using data in a table such as this one, data from a SQL database via select query, or getting at the configuration parameters of some component. So for starters, let's select the table component and examine its data parameters. We see it is indeed a dataset with a starting default size of 150 rows and three columns. But suppose we didn't know that, how would we get it size data names and data types programmatically? So now we'll select the preview mode and remain there for the rest of this lesson, and we'll press the show params button, and we see a bunch of data appear.

[02:13] In this button script editor, these first few lines will be common to all our examples. So let's go over them just this one time. Event.source is the triggering source for this event or this button, .parent is its root container, and .get component gives us our table of interest. Knowing all this, we can then extract the table data into a standalone data set variable. And with that dataset, we'll just use dot notation along with some available data set functions. So get column count and get row count are how we got the three and the 150. Get column names gave us all of these column names, and get column name one gave us this one particular one, remembering that it's zero-based indexing, and get column types and get column type gives us these data types here.

[03:06] And that shows the general pattern we'll be using here. So let's click okay to dismiss and clear the console window. In this next example, let's extract one specific value from the table, using the spinner inputs for the row and column indices. We'll treat those indices as user-facing one based inputs. So these are columns one, two and three as noted, and these rows begin at row one on downward. We'll handle the zero-based indexing needed internally in a moment. Let's start by extracting and printing the one, one element by pressing the show specific button. And there is the expected value. So we will extract the two, two element like so, and there it is. And then the three, three element like so. And you'll notice that the raw element isn't formatted like the table element is. To see how all this is done, let's open the next scripting window.

[04:02] And we see that getting the table data is unchanged, that this time we get the column and the row indices from the spinner components, and then we need to offset them by one into zero-based indices. Once we've done all that, we can extract a single value of interest from the dataset by using its get value at function with these zero-based indices, of course. And each time we print the selected dataset cell with a script. Again, we'll click okay to dismiss and clear the console window. In our next example, we'll use the same get value add, except now we wanna see all the data in our table, row by row, then by each column. So we'll need to use looping. If we press the show all button, we see that every cell in the dataset has been printed out, one per line. At the top, we see the three elements of the first row individually, and then so on and so on. To see how this is done, we will just open the next scripting window. And at the bottom, we have two nested for loops.

[05:03] First by row, and then by column. Each for loop is implemented using the Python range function, which sets up values from zero up to, but excluding the provided value. That limit value is obtained using the row and column count functions we've already seen. Then on line 11, we simply print one value each time using the get value add function. The only difference now is we do this for all values, not just one. This gives us the long repeating series of int, label, and double values we see output. Again, we'll click okay to dismiss and clear the console window. For the next example, let's look at the numerical averages for these first and third columns. This will again require for looping through all the rows, but we've already seen how that's done. So we'll click on the average button and we see these two average values printed. To see how this is done, let's open the next scripting window. We'll initialize two totals to zero, one int and one double one.

[06:04] Then we'll loop over all the rows using get row count as before, and notice how as we increment each total using get value add, we can specify the desired column using either its numerical zero-based index value or its column named string. Finally, we'll compute the averages by dividing by the row counts once more, and we'll format the double column average to have two decimal places to match its column values. Again, we'll click okay to dismiss and clear the console window. For the next example, let's actually modify the dataset of the table by deleting a selected row. In the property editor, we see our starting dataset size is still 150 rows by three columns. So now let's select some random row and click the delete selected button. Each time we do this, we see that the dataset size is in fact going down. And we are in fact altering the size of our dataset by deleting rows.

[07:05] To see how this is done, let's open the next scripting window. On line eight, we grabbed the table selected row. Of course, in real life, it'd be good to check that this wasn't minus one and something was actually selected. But for example purposes, we'll keep this really simple. Earlier, we said that datasets are immutable. They can't be changed once created, which is still true. So here on line 11, delete row takes the original dataset in a row number two delete, and then efficiently creates an entirely new dataset with the alteration applied, which we then use on line 14 to update the data for our table component. It's important to note that the original dataset was never changed, we just updated the component itself with a new smaller dataset size. That's what made it appear the original dataset in the table was slowly losing rows. And again, let's click okay to dismiss the scripting window. For the last example, let's consider the opposite of what we just did.

[08:06] Let's add rows back to our table. Before we do that, we see that our property editor shows the current dataset size is now 148 rows. So now let's select some other random row, and we'll click the copy selected button twice. Each time we do this, we are now increasing the size of our dataset by copying the selected row into a new row. So we're back at 150 rows, and we've added two more copies of that row. To see how this is done, we'll open the final scripting window. Down to line eight where we got the selected row is identical to before. But now on row 11, we're creating a Python list with three elements in it, once again using get value at to retrieve individual values from the specified row. Once we have this added row copy, we use add row with the existing dataset, the specified row, and the list we just created to create a new dataset, which we then use to update the components data.

[09:11] Once again, the original dataset did not change. We've once again updated the tables dataset on line 15 with a new dataset, this time a larger one. So it appears now that the tables dataset is adding rows. So in this lesson, we've shown various aspects of dataset usage and manipulation, retrieving row and column size info, column names and data types, looping through datasets, and updating a table's original dataset by adding or deleting rows.