Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
FN
FN
Level V

String to data table (code optimization)

An .exe file within a loop returns a string a need to convert (efficiently) to a data table.

 

The string has the following characteristics:

 

  • Rows are limited by "\n" characters, although this can be changed if needed
  • Columns are limited by ","
  • The begging and the end of the string has rows one needs to eliminate
  • The datetime rows can vary. In the example one can see 7 but these can go up to 100k rows.
  • This operations will be repeated within the loop in the order of hundreds (different strings but same number of rows)

 

 

str_query = 
"*> mycolumnname
1,mycolumnname,50.,mycolumdescription,mycolumnunits
* End exe...
*> mycolumnname, 31-Jan-2020 14:30:00, 31-Jan-2020 15:30:00, blabla, 7
31-Jan-20 14:30:00,mycolumnname,10.60466
31-Jan-20 14:40:00,mycolumnname,10.60466
31-Jan-20 14:50:00,mycolumnname,10.60466
31-Jan-20 15:00:00,mycolumnname,10.60466
31-Jan-20 15:10:00,mycolumnname,10.60466
31-Jan-20 15:20:00,mycolumnname,10.60466
31-Jan-20 15:30:00,mycolumnname,10.60466
* End exe...
...
...
...

...
";

 

 

How can I transform this string into a data table with the following (stacked) format:

 

31-Jan-20 14:30:00,mycolumnname,10.60466,mycolumdescription,mycolumnunits
31-Jan-20 14:40:00,mycolumnname,10.60466,mycolumdescription,mycolumnunits
31-Jan-20 14:50:00,mycolumnname,10.60466,mycolumdescription,mycolumnunits
31-Jan-20 15:00:00,mycolumnname,10.60466,mycolumdescription,mycolumnunits
31-Jan-20 15:10:00,mycolumnname,10.60466,mycolumdescription,mycolumnunits
31-Jan-20 15:20:00,mycolumnname,10.60466,mycolumdescription,mycolumnunits
31-Jan-20 15:30:00,mycolumnname,10.60466,mycolumdescription,mycolumnunits

Which JSL functions can manage these string to data table operations efficiently (memory and/or CPU wise?).

 

Multi-threading or multiprocessing cannot be used this is querying a database.

 

 

 

 

6 REPLIES 6
Highlighted
Byron_JMP
Staff

Re: String to data table (code optimization)

Maybe putting the two parts into lists is a way to get started?

clear globals();

query = 
"*> mycolumnname 1,mycolumnname,50.,mycolumdescription,mycolumnunits
* End exe...
*> mycolumnname, 31-Jan-2020 14:30:00, 31-Jan-2020 15:30:00, blabla, 7
31-Jan-20 14:30:00,mycolumnname,10.60466
31-Jan-20 14:40:00,mycolumnname,10.60466
31-Jan-20 14:50:00,mycolumnname,10.60466
31-Jan-20 15:00:00,mycolumnname,10.60466
31-Jan-20 15:10:00,mycolumnname,10.60466
31-Jan-20 15:20:00,mycolumnname,10.60466
31-Jan-20 15:30:00,mycolumnname,10.60466
* End exe...
...
...
...

...
";

query=Substitute( query, "*> mycolumnname ", "colnames=list(" );
query=Substitute( query, "* End exe...", ");" );
query=Substitute( query, "*> mycolumnname, ", "coldata=list(" );
query=Substitute( query, "* End exe...", ");" );
for(i=1, i<=5, i++, query=Substitute( query, "...", "" ));

show(query);

What have you tried?

JMP Systems Engineer, Pharm and BioPharm Sciences
Highlighted
Craige_Hales
Staff (Retired)

Re: String to data table (code optimization)

your format looks a lot like a CSV file. the open() function understands CSV data, and if you give it enough information about the columns and data types, it can be quite efficient (both your development time and your waiting time.)

How are you getting the data from the .exe program to JMP? (flat file? runProgram()? open()? something else?)

 

Craige
Highlighted
FN
FN
Level V

Re: String to data table (code optimization)

Yes, it is almost a string with a CSV formatting, and the output can be tuned to certain extent.

 

The current code is using RunProgram() to get the output string, then Words() to obtain a list and then TextToColumns().

 

Can we Open() a string directly? It seems open is for files only, which ideally should be avoided.

 

Highlighted
Craige_Hales
Staff (Retired)

Re: String to data table (code optimization)

Cool! RunProgram can return a blob. The open() function can accept a blob in place of a file name. If you can get the external program to create the CSV as its printed output (on stdout) it should work.

example here: https://community.jmp.com/t5/Discussions/Load-CSV-from-variable/m-p/208453

 

Craige
Highlighted
Craige_Hales
Staff (Retired)

Re: String to data table (code optimization)

...and you can still pass the CSV parameters to that open statement. If the output has extra lines at the beginning that you can't fix, you can tell the open statement about the first data line, and the presence of column names, and what data types to use. If the data is more than a few lines, it may be noticeably faster to tell JMP everything that is in the source script so JMP doesn't make an extra pass to guess the format. The source script can be found in a table JMP already imported.

Craige
Highlighted
Byron_JMP
Staff

Re: String to data table (code optimization)

FN,

Curious to know if you tried Craig's approach?  Did it work for your example?

-B

JMP Systems Engineer, Pharm and BioPharm Sciences
Article Labels

    There are no labels assigned to this post.