cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
BijuP
Level II

Transform multiple columns with JSL

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ih
Super User (Alumni) ih
Super User (Alumni)

Re: Transform multiple columns with JSL

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

View solution in original post

4 REPLIES 4
ih
Super User (Alumni) ih
Super User (Alumni)

Re: Transform multiple columns with JSL

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.

Custom Transformation.png

 

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
BijuP
Level II

Re: Transform multiple columns with JSL

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.

ih
Super User (Alumni) ih
Super User (Alumni)

Re: Transform multiple columns with JSL

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 ) );
);
BijuP
Level II

Re: Transform multiple columns with JSL

Great! Thank you for your help and prompt replies.