topic Re: JSL script create multiple columns in Discussions

JSL script create multiple columns

alex_chufarovsk — Mon, 20 Jul 2015 11:15:28 GMT

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

Re: JSL script create multiple columns

pmroz — Mon, 25 Jun 2018 17:54:33 GMT

// 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));
     );
);

Re: JSL script create multiple columns

alex_chufarovsk — Mon, 20 Jul 2015 17:41:45 GMT

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:

UNIT	Data 1	Data 2	Data 3
A	12.35336	8.876483	3.207919
B	9.838289	1.483974	2.5232
C	3.136643	6.092754	0.980194
D	0.3593	4.639877	6.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:

UNIT	Data 1	Data 2	Data 3	Data_Floor 1	Data_Floor 2	Data_Floor 3
A	12.35336452	8.876483446	3.207918972	12	8	3
B	9.838288756	1.483974287	2.523200042	9	1	2
C	3.136642653	6.092754288	0.980193636	3	6	0
D	0.359299803	4.639877062	6.672548555	0	4	6

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

Re: JSL script create multiple columns

pmroz — Mon, 25 Jun 2018 17:53:48 GMT

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();

Re: JSL script create multiple columns

alex_chufarovsk — Mon, 20 Jul 2015 19:21:11 GMT

Great, that works, thanks

Re: JSL script create multiple columns

alex_chufarovsk — Mon, 20 Jul 2015 19:25:00 GMT

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?

Re: JSL script create multiple columns

pmroz — Mon, 25 Jun 2018 17:55:04 GMT

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));
);