BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
texas_stats
Occasional Contributor

JSL Script to Transform Selected Columns

Hi, in JMP 14 I would like to allow a user to select non-specific columns, say X,Y,Z, and then run a JSL script that generates new columns with the difference of each column, i.e. X-Y, X-Z, Y-Z.  The script would not need to calculate Y-X, or Z-X since those differences have already been calculated.  Any help is appreciated, thanks!

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
ian_jmp
Staff

Re: JSL Script to Transform Selected Columns

Here's one way (using formulas):

NamesDefaultToHere(1);

// Make a table to use
nr = 100;	// Number of rows
nc = 10;	// Number of columns
dt = AsTable(J(nr, nc, RandomNormal()));
dt << setName("Example Data");

// Make a UI to allow selection of columns from the current data table
dt = CurrentDataTable();
nw = NewWindow("Difference of Columns",
		TextBox("Select columns:", << fontColor("Blue")),
		clb = ColListBox(all, numeric),
		LineUpBox(NCol(2),
			ButtonBox("OK", OKscript),
			ButtonBox("Cancel", nw << closeWindow)
			);
		);
nw << setWindowSize(300, 300);

// This script runs if the user hits 'OK'
OKScript =
Expr(
	nw << closeWindow;
	// Get which columns were selected
	colList = clb << getSelected;
	// Define a formula column for each pairwise difference
	for(c1=2, c1<=NItems(colList), c1++,
		for(c2=1, c2<c1, c2++,
			addCol = Expr(
						dt << NewColumn(colList[c1]||" minus "||colList[c2],
										Numeric, Continuous,
										Formula(AsColumn(c1TBD) - AsColumn(c2TBD))
										)
						);
			SubstituteInto(addCol, Expr(c1TBD), NameExpr(colList[c1]), Expr(c2TBD), NameExpr(colList[c2]));
			addCol;
			)
		)
	);

Use 'Help > Books > Scripting Guide' and 'Help > Scripting Index' to understand how it works.

 

0 Kudos
4 REPLIES 4
ian_jmp
Staff

Re: JSL Script to Transform Selected Columns

Here's one way (using formulas):

NamesDefaultToHere(1);

// Make a table to use
nr = 100;	// Number of rows
nc = 10;	// Number of columns
dt = AsTable(J(nr, nc, RandomNormal()));
dt << setName("Example Data");

// Make a UI to allow selection of columns from the current data table
dt = CurrentDataTable();
nw = NewWindow("Difference of Columns",
		TextBox("Select columns:", << fontColor("Blue")),
		clb = ColListBox(all, numeric),
		LineUpBox(NCol(2),
			ButtonBox("OK", OKscript),
			ButtonBox("Cancel", nw << closeWindow)
			);
		);
nw << setWindowSize(300, 300);

// This script runs if the user hits 'OK'
OKScript =
Expr(
	nw << closeWindow;
	// Get which columns were selected
	colList = clb << getSelected;
	// Define a formula column for each pairwise difference
	for(c1=2, c1<=NItems(colList), c1++,
		for(c2=1, c2<c1, c2++,
			addCol = Expr(
						dt << NewColumn(colList[c1]||" minus "||colList[c2],
										Numeric, Continuous,
										Formula(AsColumn(c1TBD) - AsColumn(c2TBD))
										)
						);
			SubstituteInto(addCol, Expr(c1TBD), NameExpr(colList[c1]), Expr(c2TBD), NameExpr(colList[c2]));
			addCol;
			)
		)
	);

Use 'Help > Books > Scripting Guide' and 'Help > Scripting Index' to understand how it works.

 

0 Kudos
texas_stats
Occasional Contributor

Re: JSL Script to Transform Selected Columns

Thank you very much Ian!  I appreciate the quick response.  That's what i was looking for. 

 

What if I wanted to make things a little more fancy?  Let's say I wanted to allow the user to select columns (as before) and then also to be able to choose 1 or more of the following transformations: add, subtract, multiple, divide.  The script would then create new columns based on the transformations selected.  Thank you !    

0 Kudos
texas_stats
Occasional Contributor

Re: JSL Script to Transform Selected Columns

I was able to come up with this script.  Wasn't sure how to shorten the transform into an "if" statement.

@ian_jmp 

NamesDefaultToHere(1);

// Make a table to use
nr = 100;	// Number of rows
nc = 10;	// Number of columns
dt = AsTable(J(nr, nc, RandomNormal()));
dt << setName("Example Data");

// Make a UI to allow selection of columns from the current data table
dt = CurrentDataTable();
nw = NewWindow("New Column Transforms",
		TextBox("Select all transforms to perform", << fontColor("Blue")),
		VListBox(
			cb=CheckBox({"Plus","Minus","Times","DivideBy"},
				Print(
					"Selected: " || Concat Items(cb <<GetSelected(), ", ")
				)
			)
		),
		TextBox("Select columns:", << fontColor("Blue")),
		clb = ColListBox(all, numeric),
		LineUpBox(NCol(2),
			ButtonBox("OK", OKscript),
			ButtonBox("Cancel", nw << closeWindow)
			);
		);
nw << setWindowSize(500, 900);

// This script runs if the user hits 'OK'
OKScript =
Expr(
	nw << closeWindow;
	// Get which transforms were selected
	tList = cb <<getSelected;	
	// Get which columns were selected
	colList = clb << getSelected;
	// Define a formula column for each pairwise difference
	for(t=1, t<=NItems(tList), t++,
		for(c1=2, c1<=NItems(colList), c1++,
			for(c2=1, c2<c1, c2++,
						if(tList[t]=="Plus",
							addCol = Expr(
								dt << NewColumn(colList[c1]||" plus "||colList[c2],
									Numeric, Continuous,
									Formula(AsColumn(c1TBD) + AsColumn(c2TBD))
									)
							);
							SubstituteInto(addCol, Expr(c1TBD), NameExpr(colList[c1]), Expr(c2TBD), NameExpr(colList[c2]));
							addCol;,
							
							tList[t]=="Minus",
							addCol = Expr(
								dt << NewColumn(colList[c1]||" minus "||colList[c2],
									Numeric, Continuous,
									Formula(AsColumn(c1TBD) - AsColumn(c2TBD))
									)
							);
							SubstituteInto(addCol, Expr(c1TBD), NameExpr(colList[c1]), Expr(c2TBD), NameExpr(colList[c2]));
							addCol;,
							
							tList[t]=="Times",
							addCol = Expr(
								dt << NewColumn(colList[c1]||" times "||colList[c2],
									Numeric, Continuous,
									Formula(AsColumn(c1TBD) * AsColumn(c2TBD))
									)
							);
							SubstituteInto(addCol, Expr(c1TBD), NameExpr(colList[c1]), Expr(c2TBD), NameExpr(colList[c2]));
							addCol;,
							
							tList[t]=="DivideBy",
							addCol = Expr(
								dt << NewColumn(colList[c1]||" divideBy "||colList[c2],
									Numeric, Continuous,
									Formula(AsColumn(c1TBD) / AsColumn(c2TBD))
									)
							);
							SubstituteInto(addCol, Expr(c1TBD), NameExpr(colList[c1]), Expr(c2TBD), NameExpr(colList[c2]));
							addCol;		
						)
				)
			)
		)
	);

Re: JSL Script to Transform Selected Columns

You could use the Match() function instead of the If() function. Better yet, you could use the Choose() function if you replaced the check boxes with radio buttons. (Seems like the choice of the arithmetic is exclusive.)

Learn it once, use it forever!
0 Kudos