Subscribe Bookmark RSS Feed

JSL script create multiple columns

alex_chufarovsk

Community Trekker

Joined:

Jul 2, 2015

Hi

I would like to ask some help with JMP Scripting.

I need to automatically retrieve several column names from the current table, and for each column create a new column, using a formula.

For example, this is my table

Unit ID Data1 Data2 Data3

A          1.1     1.2      1.1

B          1.2     1.2      1.0

C          1.3     1.2      1.4

D          1.1     1.2      1.1

1 - I need to select the columns from Data1 to Data3

2 - need to create 3 new columns, as following Data1_Floor, Data2_Floor, Data3_Floor

3 - each new column will be calculated as a  "Floor" function using the data of the original column: Data1_Floor = Floor (Data1), ...

I would appreciate an example of JSL script with some comments

thanks

Alex

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

You're close.  You need to assign the variable dt to point to your data table.  You can do that either with:

// Replace <your table name> with the actual name of your table

dt = data table("<your table name>");

or with:

dt = current data table();

6 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

// Create data table

dt = New Table( "Floor Example",

     Add Rows( 4 ),

     New Column( "Unit ID", Character, Nominal, Set Values( {"A", "B", "C", "D"} ) ),

     New Column( "Data1", Numeric, Continuous, Format( "Best", 12 ),

           Set Values( [1.1, 1.2, 1.3, 1.1] ) ),

     New Column( "Data2", Numeric, Continuous, Format( "Best", 12 ),

           Set Values( [1.2, 1.2, 1.2, 1.2] ) ),

     New Column( "Data3", Numeric, Continuous, Format( "Best", 12 ),

           Set Values( [1.1, 1, 1.4, 1.1] ) ),

);

// Get the list of column names

col_list = dt << get column names(string);

// Loop over column names, searching for data columns

for (i = 1, i <= nitems(col_list), i++,

// If it's a Data column then add the floor formula

     if (contains(col_list[i], "Data"),

           data_col = ":" || col_list[i];

           floor_col = col_list[i] || "_Floor";

// Build new column string dynamically and then execute it.

           str = evalinsert(

"\[dt << New Column( "^floor_col^", Numeric, Continuous, Format( "Best", 12 ),

                Formula( Floor( ^data_col^))

           );]\");

           eval(parse(str));

     );

);

alex_chufarovsk

Community Trekker

Joined:

Jul 2, 2015

Hi PMroz,

thanks for a quick reply, I appreciate it.  Unfortunatelly I am new to JSL, so I could not reproduce it, I tryed to run your script from the line "col_list =..." but I did not get the output that I expected.  I must be doing something wrong.

Here is what I would like to get.

This is my original data table:   

UNITData 1Data 2Data 3
A12.353368.8764833.207919
B9.8382891.4839742.5232
C3.1366436.0927540.980194
D0.35934.6398776.672549

I'd like to run the script which will get all columns with "Data" string and for each one of them will generate the new column, so the final table would look like this:

   

UNITData 1Data 2Data 3Data_Floor 1Data_Floor 2Data_Floor 3
A12.353364528.8764834463.2079189721283
B9.8382887561.4839742872.523200042912
C3.1366426536.0927542880.980193636360
D0.3592998034.6398770626.672548555046

could you please try to make a script to do this action?

Solution

You're close.  You need to assign the variable dt to point to your data table.  You can do that either with:

// Replace <your table name> with the actual name of your table

dt = data table("<your table name>");

or with:

dt = current data table();

alex_chufarovsk

Community Trekker

Joined:

Jul 2, 2015

Great, that works, thanks

alex_chufarovsk

Community Trekker

Joined:

Jul 2, 2015

Let me also ask you: what if my columns did not have a common string in their names?  How can I make it work so instead of "ifcontains" I would have to tell the script to take the columns from column B to column D, or in numerical order, take columns from #2 to #4?

pmroz

Super User

Joined:

Jun 23, 2011

This should do it:

dt = current data table();

startcol = 2;

endcol   = 4;

for (i = startcol, i <= endcol, i++,

     data_col = column(i) << get name;

     floor_col = data_col || "_Floor";

     str = evalinsert(

"\[dt << new column( "^floor_col^", Numeric, Continuous, Format( "Best", 12 ),

           formula(floor(:^data_col^)));]\");

     eval(parse(str));

);