cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar

JSL script create multiple columns

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
pmroz
Super User

Re: JSL script create multiple columns

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

 

View solution in original post

6 REPLIES 6
pmroz
Super User

Re: JSL script create multiple columns

 

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

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?

pmroz
Super User

Re: JSL script create multiple columns

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

Great, that works, thanks

Re: JSL script create multiple columns

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

Re: JSL script create multiple columns

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