Convert dataset column values from string to integer

Hi everyone,
I have a dataset with two columns. Column1 has string values that I need to convert into integers in order to use both columns on a chart. Is this possible?

I was thinking about creating a Custom Property (dataset) on the Root Container that is bound to the dataset and use a propertyChange event to fire a script to loop through the dataset, cast all the values in Column1 to an integer and then push the new dataset to another Custom Property. I am having a hard time with the script for this and seem to be overcomplicating it.

It would be nice if there was an Expression language to cast an entire columnā€™s value of a dataset to integer, etc. Looks like Expression can only handle one.

[quote=ā€œframebuilderā€]It would be nice if there was an Expression language to cast an entire columnā€™s value of a dataset to integer, etc. Looks like Expression can only handle one.[/quote]Youā€™re in luck!
The view() expression function can do this with one line of pseudo-SQL:view("SELECT columnname1, int(columnname2) as newcolumnname", {Root Container.mySourceDataset})ā€¦ with appropriate substitutions for your case. :slight_smile:

Phil,
I tried installing the module but the Designer hangs at ā€œInitializing Scriptingā€. If I uninstall, the Designer launches fine. This is on 7.7.6 with version 1.6.1 (b1510110139) Simulation Aids. A Gateway restart has not helped.

I may be oversimplifying it, but does Column1 on the original dataset have to be a string?

[quote=ā€œframebuilderā€]Phil,
I tried installing the module but the Designer hangs at ā€œInitializing Scriptingā€. If I uninstall, the Designer launches fine. This is on 7.7.6 with version 1.6.1 (b1510110139) Simulation Aids. A Gateway restart has not helped.[/quote]Thanks for the heads-up. I hadnā€™t had a chance to play with 7.7.6 yet. Will fix.

[quote=ā€œpturmelā€][quote=ā€œframebuilderā€]Phil,
I tried installing the module but the Designer hangs at ā€œInitializing Scriptingā€. If I uninstall, the Designer launches fine. This is on 7.7.6 with version 1.6.1 (b1510110139) Simulation Aids. A Gateway restart has not helped.[/quote]Thanks for the heads-up. I hadnā€™t had a chance to play with 7.7.6 yet. Will fix.[/quote]
Ah, just noticed your build number. You should upgrade to the latest, b1511032223. Iā€™ll go fix the link in the post.

Jordan,
I was wondering if someone would catch that. Unfortunately, it has to be a string because it comes from another datasource that we canā€™t modify.

Hereā€™s what I came up with so far by playing with a Table as the output (Iā€™ll move to a chart later). It seems to be working for the one string column and converting to int, but I canā€™t figure out how to build the next column which is already an int.

[code]data = event.source.parent.CycleTimes
table = event.source.parent.getComponent(ā€œTableā€)

rows = []
headers = [ā€œOP100 CTā€]
for row in range(data.rowCount):
oneRow = data.getValueAt(row, ā€œOP100 Cycle Time2ā€)
rows.append([int(oneRow)])

data = system.dataset.toDataSet(headers,rows)
table.data = data
[/code]

Iā€™m still getting used to playing with datasets and going through the docs so Iā€™m sure itā€™s something simple that Iā€™m missing.

Actually, you have a great start! Building the columns is done at the same time. It could look something like this:

[code]data = event.source.parent.CycleTimes
table = event.source.parent.getComponent(ā€œTableā€)

rows = []
headers = [ā€œOP100 CTā€, ā€œcolumn2Nameā€]
for row in range(data.rowCount):
oneRow = [int(data.getValueAt(row, ā€œOP100 Cycle Time2ā€), data.getValueAt(row, ā€œtheOtherColumnā€)]
rows.append(oneRow)

data = system.dataset.toDataSet(headers,rows)
table.data = data[/code]

Also, I like Philā€™s solution a lot! Just wanna be clear on that one, as itā€™s a good all-in-one tool.

On the other hand, doing it the ā€œlong wayā€ is a good way to build your coding skills.

Just shows there are a lot of skinned catsā€¦ :laughing:

[quote=ā€œJordanCClarkā€]Also, I like Philā€™s solution a lot! Just wanna be clear on that one, as itā€™s a good all-in-one tool.

On the other hand, doing it the ā€œlong wayā€ is a good way to build your coding skills.

Just shows there are a lot of skinned catsā€¦ :laughing:[/quote]The simulation aids module can help you learn these algorithms, too ā€“ if you can express the problem in pseudo-SQL to get a result from view(), you can turn on its ā€˜debugā€™ mode to see the python it created to solve your problem. Which you can then cut, paste, study, and customize further.

Nice. Hereā€™s the code that got it working.

data = event.source.parent.CycleTimes
table = event.source.parent.getComponent("Table")

rows = []
headers = ["OP100 CT", "Line PC"]
for row in range(data.rowCount):
   oneRow = [int(data.getValueAt(row, "OP100 Cycle Time2")), data.getValueAt(row, "Line Production Count")]
   rows.append(oneRow)

data = system.dataset.toDataSet(headers,rows)
table.data = data

Phil,
Iā€™m going to try out the Simulation Aids module as well, but I have a question. You mention it works with Expressionsā€“does that mean when I go to bind a chartā€™s data, I can use the Expression button and I will see a new option on the right side that has view() and some others? Iā€™m getting excited.

Hereā€™s how to implement the solution using a MutablePyDataSet:

table = event.source.parent.getComponent('Table') data = pa.dataset.toData(table.data) for row in data: row["Col 1"] = int(row["Col 1"]) table.data = data.toDataSet() MutablePyDataSet is a swiss army knife for Datasets.

To show off the various things you can do with a MutablePyDataSet, hereā€™s another way it could be done:table = event.source.parent.getComponent('Table') data = pa.dataset.toData(table.data) data.setColumn("OP100 Cycle Time2",map(int,data.columns["OP100 Cycle Time2"])) table.data = data.toDataSet()1. The ā€œpa.dataset.toDataā€ function converts the dataset to a MutablePyDataSet.

  1. data.columns[ā€œOP100 Cycle Time2ā€] gets the entire column as a list.

  2. map(int,data.columns[ā€œOP100 Cycle Time2ā€]) creates a new list from the ā€œOP100 Cycle Time2ā€ column list but with each value converted to an int.

  3. data.setColumn(ā€œOP100 Cycle Time2ā€,map(str,data.columns[ā€œOP100 Cycle Time2ā€])) sets a new column at position ā€œOP100 Cycle Time2ā€, using the result of map(int,data.columns[ā€œOP100 Cycle Time2ā€]) as the new column.

MutablePyDataSet is part of the PA Power Scripting Module.
nickmudge.info/post/pa-power-scripting-module
marketplace.inductiveautomation. ā€¦ oduleId=87
nickmudge.info/post/datasets-in-Ignition

Best,

[quote=ā€œframebuilderā€]Iā€™m going to try out the Simulation Aids module as well, but I have a question. You mention it works with Expressionsā€“does that mean when I go to bind a chartā€™s data, I can use the Expression button and I will see a new option on the right side that has view() and some others? Iā€™m getting excited.[/quote]Yes. In the f(x) menu. :smiley:
The view() function and the objectScript() function are the most versatile for general use. The rest of the module really is tailored to simulation of not-yet-available processes.
Expression functions have the big advantage that they run in the designer without having to ask for preview mode, making some design tasks much quicker and/or more intuitive. The demo project has several live examples.

Hi Phil,
I have the view() function working with a PowerTable (thank you btw), but when I try grab a piece of information in a Text Field Component all I get is Dataset [1R x 1C]. Is there a way return the value rather than the dataset info?

Thanks,
Aaron

Like a scalar query? Try adding [0,0] at the end of the expression.

I have a dataset tag that pulls data from our ERP system. Working with PowerTables getting specific information seems pretty easy, however, grabbing specific information into other components doesnā€™t work the way that I would like. Iā€™m new to Python so my going is a little slow.

I tried the following:
view({Root Container.txtStatusPath.text}, {Performance/Performance_Detail})[0,0]
view({Root Container.txtStatusPath.text}, {Performance/Performance_Detail}[0,0])
view({Root Container.txtStatusPath.text}, {Performance/Performance_Detail[0,0]})
None of these worked.

Iā€™m trying to learn about working with datasets as much as I can now.

Thanks,
Aaron

I see what you mean by the [0,0] now. Still not answered, but it helps. Thanks!

Careful! Ignition's expression language isn't python. view() is part of the expression language--it just uses python for its internals for for the expressions nested within the pseudo-SQL.

Anyways, this one should have worked, since view() returns a dataset:

view({Root Container.txtStatusPath.text}, {Performance/Performance_Detail})[0,0]

assuming {Root Container.txtStatusPath.text} contains your pseudo-SQL.

1 Like

Hi Phil,
Sorry I misspoke, I should have said new to Ignition (but also Python and the Expression language).

Thanks for the info! Trying to learn quickly and not ask the same question twice.

@framebuilder
for below line in your script , i need to convert string to Datetime whatā€™s the code should be used .
Thanks

oneRow = [int(data.getValueAt(row, "OP100 Cycle Time2")), data.getValueAt(row, "Line Production Count")]