Transform multiple columns with JSL

[#1]

Hello JSL Gurus,

Is there an easy way to transform a column list in JSL with the same transform formula?

I am trying to get similar functionality like in a table, for example, where I can select multiple columns & from the right click menu:

New Formula Column -> Transform -> Absolute Value

If this can be done without a For loop it will be helpful.

Appreciate any pointers.

Thanks.

[#2]

You might get a lot of answers of ways to do this with existing tools in JMP but I still use the script below which is ran from a menu item inside a table.  Select columns in a table, run the script, write a formula referencing those first columns, and then every selected column is transformed.

Script:

View more...

Names default to here( 1 );

//Reference current table
dt1 = Current Data Table();

//flag to output a bunch of status info to the log file
debug = 0;

//window box, all code is inside this box
nw = New Window( "Custom Column Transformation",

	//box to hold spacers on left and right side of data
	H List Box ( 
		
		//spacer on left side of window
		Spacer Box( Size( 12, 12) ),
	
		//box to hold all other parts of window
		V List Box(
			
			//heading and instructions
			Text Box( "Choose Transformation Settings " ), 
			Spacer Box( Size( 12, 12 ) ),
			V List Box( Text Box( "Directions:" ||
								  "\!N1. Select columns to transform in the data table." ||
								  "\!N2. Type a formula below, or select an example." ||
								  "\!N3. Select a phrase to be appended to each new column name." ||
								  "\!N4. Press OK." ||
								  "\!N5. Repeat 2-4 as necessary." ||
								  "\!N6. Press Close/Cancel to exit."), 
			Spacer Box( Size( 12, 12 ) ) ),
			
			//radio buttons with examples, when selected the text in the formula edit box is modified
			panelbox("Example Equations",
				rb1 = radio box({"Lag", "Change", "Shuffle", "Date Dep", "Column Only", "Cos Oscillation", "Sin Oscillation", "10 day t score"},
					one_rb = rb1 << get selected;
					if (one_rb == "Lag",
						FormulaIn << set text("Lag(<column>,1)");,
						one_rb == "Change",
						FormulaIn << set text("<column> - Lag(<column>,1)");,
						one_rb == "Shuffle",
						FormulaIn << set text("Col Stored Value(<column>, Col Shuffle())");,
						one_rb == "Date Dep",
						FormulaIn << set text("If( As Column( \!"Date\!" ) < Date DMY( 1, 1, 2016 )," ||
											  "\!N\!t<column> / 10,\!N\!t<column>\!N)");,
						one_rb == "Cos Oscillation",
						FormulaIn << set text("cos(<column> * (2 * pi()) / Col Max( <column> ) )");,
						one_rb == "Sin Oscillation",
						FormulaIn << set text("sin(<column> * (2 * pi()) / Col Max( <column> ) )");,
						one_rb == "10 day t score",
						FormulaIn << set text("Lag( 
						  (<column> - Mean(
							Lag( <column>, 1 ),
							Lag( <column>, 2 ),
							Lag( <column>, 3 ),
							Lag( <column>, 4 ),
							Lag( <column>, 5 ),
							Lag( <column>, 6 ),
							Lag( <column>, 7 ),
							Lag( <column>, 8 ),
							Lag( <column>, 9 ),
							Lag( <column>, 10 )
						) ) / Std Dev(
							Lag( <column>, 1 ),
							Lag( <column>, 2 ),
							Lag( <column>, 3 ),
							Lag( <column>, 4 ),
							Lag( <column>, 5 ),
							Lag( <column>, 6 ),
							Lag( <column>, 7 ),
							Lag( <column>, 8 ),
							Lag( <column>, 9 ),
							Lag( <column>, 10 )
						) ) )");,
						FormulaIn << set text("<column>");
						
						
						
					);
				),
			),
			
			//Formula edit box with instructions
			Spacer Box( Size( 6, 6 ) ),
			V List Box( Text Box( "FORMULA\!NCreate a new column in the data table and then copy/paste the formula from " ||
								  "the formula editor.  Then use <column> up to 50 times to indicate where column names " ||
								  "should be substituted. Reference other columns using As Column(\!"Column Name\!")." ),
			Spacer Box( Size( 12, 12 ) ), 
			V List Box( 
				FormulaIn = Text Edit Box(
					"Lag(<column>,1)",
					<<Justify Text( Left ),
					<<Set Width( 400 ),
					<<setNlines( 5 )
				)
			),
					   
			//Description edit box with instructions
			Spacer Box( Size( 6, 6 ) ),
			V List Box ( Text Box( "DESCRIPTION\!NText appended to each new column name:" ) ),
			Spacer Box( Size( 12, 12 ) ) ),
			V List Box ( DescIn = Text Edit Box( " TRANSFORMED",
							<<Justify Text( Left ),
							<<Set Width( 400 ),
							<<setNlines( 1 )
						 )
					   ),
			Spacer Box( Size( 25, 25 ) ),
			
			//Box to hold command buttons
			H List Box(
			
				//Button to create new columns
				Button Box( "OK",
				
					//get list of colums selected in the data table
					ColumnList = dt1 << Get Selected Columns();
					
					//TODO: loop through this section for multiple input columns, list of <column1>, <column2>, etc.
					
					//variable to hold text string to be replaced with column names
					ReplaceTarget = "<column>";
					
					//Get formula from text box
					FormulaInput = FormulaIn << Get Text;
					
					//get column description from text box
					DescInput = DescIn << Get Text;
					
					//output debug info
					if (debug == 1,Show( FormulaInput );Show( DescInput );Show( ColumnList ););
					
					//get number of selected columns
					n = N Items( ColumnList );
					
					//for each column
					For( i = 1, i <= n, i++,
					
						//get data type of column
						ColumnDataType = Column( ColumnList[i] ) << Get Data Type;
						
						//output debug info
						if (debug == 1,Show( columnList[i] );Show( ColumnDataType ););
						
						//create new variable for modified formula
						FormulaComplete = FormulaInput;
						
						//TODO: stop itterating when string no longer found or after a huge number of cycles
						
						//replace column reference for up to 50 occurances (change this for large formulas)
						For( j = 1, j <= 50, j++,
						
							//output debug info
							if (debug == 1,Show( FormulaComplete );Show( Contains( FormulaComplete, ReplaceTarget ) );
								Show( Length( ReplaceTarget ) ););
								
							//if the formula contains the target string
							If( Contains( FormulaComplete, ReplaceTarget ) > 0,
								
								//replace the first occurance of the target string
								FormulaComplete = Eval(
									Munger(
										FormulaComplete,
										Contains( FormulaComplete, ReplaceTarget ),
										Length( ReplaceTarget ),
										"As Column(\!"" || Char( ColumnList[i] ) || "\!")"
									)
								)
							); //end if formula contains reference string
						); //loop for each occurance of the reference string
						
						//output debug info
						if (debug == 1,Show( FormulaComplete ););
						
						//create string containing expression that will create the new column reference
						newColExpr = Expr(
							dt1 << New Column( Char( ColumnList[i] ) || Char( DescInput ),
								Expr( ColumnDataType ),
								Formula( Eval( Parse( FormulaComplete ) ) )
							)
						);
						
						//output debug info
						if (debug == 1,Show( Eval Expr( newColExpr ) ););
						
						//Try creating the new column reference
						Try( Eval( Eval Expr( newColExpr ) ) );
						
						//TODO: Check if new column was created and it has a formula, inform user
						
						//output debug info
						if( debug == 1, Show( "done" ););
						
					); //loop each selected column
				), //end of 'OK' command button
				
				//button to close the window
				Button Box( "Close/Cancel",
					Show( "Closed" );
					nw << Close Window;
				)
				
			), //end of box to hold command buttons
			
			//spacer on bottom of window
			Spacer Box( Size( 12, 12 ) )
		), //end of vertical box with all window content except left and right spacers
		
		//spacer in right side of window
		Spacer Box( Size( 12, 12 ) )
		
	) //end of horizontal box for spacers on left and right side
	
); //end of window box

[#3]

Thanks. This is a great utility.

If you have something simpler for what I described above that I can embed in other scripts, that will be useful for list of column names that I have generated in other parts of my script. If not, I can parse and use parts/concepts in your code to do that.

[#4 · Accepted solution]

Here is an example that might get you started:

names default to here(1);

dt = open("$Sample_data/iris.jmp");
ColumnList = dt << Get Column Names(Numeric);

ReplaceTarget = "<column>";
FormulaInput = "<column> + 1";

//for each column
For( i = 1, i <= N Items( ColumnList ), i++,
	FormulaComplete = Substitute( FormulaInput, "<column>", ":Name(\!"" || Char( ColumnList[i] ) || "\!")");
	
	//create an expression that will create the new column reference
	newColExpr = Expr(
		dt << New Column( Expr( Char( ColumnList[i] ) || " Transformed" ),
			Numeric,
			Formula( Expr( Parse( FormulaComplete ) ) )
		)
	);
	
	//Create the new column
	Eval( Eval expr( newColExpr ) );
);

[#5]

Great! Thank you for your help and prompt replies.