Convert dataset column values from string to integer

framebuilder · December 8, 2015, 2:08am

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.

pturmel · December 8, 2015, 2:57am

[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.

framebuilder · December 8, 2015, 3:49am

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.

JordanCClark · December 8, 2015, 11:50am

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

pturmel · December 8, 2015, 1:05pm

[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.

pturmel · December 8, 2015, 1:32pm

[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.

framebuilder · December 8, 2015, 2:47pm

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.

JordanCClark · December 8, 2015, 3:22pm

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]

JordanCClark · December 8, 2015, 3:27pm

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…

pturmel · December 8, 2015, 3:59pm

[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… [/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.

framebuilder · December 8, 2015, 4:14pm

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.

nmudge · December 8, 2015, 4:39pm

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.

data.columns[“OP100 Cycle Time2”] gets the entire column as a list.
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.
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,

pturmel · December 8, 2015, 7:04pm

[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.
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.

ahardin · May 2, 2018, 5:04pm

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

pturmel · May 2, 2018, 5:23pm

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

ahardin · May 2, 2018, 6:39pm

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

ahardin · May 2, 2018, 7:02pm

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

pturmel · May 2, 2018, 8:06pm

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.

ahardin · May 3, 2018, 11:46am

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.

tamerplc · March 2, 2019, 12:48pm

@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")]