Subscribe Bookmark RSS Feed

Selecting Multiple Columns in a Script

dkraeuter_sunne

Community Trekker

Joined:

Jun 2, 2016

I have been working on a script to evaluate some possible transformations for fit for both first order and second order polynomials. The script seems to work well. However, I can only select one column at a time. Is there a way to change this script to allow for more than one column to be evaluated at a time?

 

 

Clear Log();
dt = Current Data Table(); // Use Current Data Table;
xvar = .;
yvar = .;
win = New Window( "Transformation Variable Selection",
<<Modal,
<<On Validate(
// require the user to select two variables before clicking OK
Show( xvar, yvar );
If( Is Missing( xvar ) | Is Missing( yvar ),
// if xvar or yvar are missing do nothing when OK is clicked
0
,
1
);
),
Text Box( " Select Numeric Columns. " ),
H List Box(
Text Box( " X, Factor " ),
x = Col List Box(
dt, // data table reference
all, // display all columns from the data table
xvar = (x << Get Selected)[1];
// get the name of the selected column before the window closes
Show( xvar );
),
Text Box( "Y, Response" ),
y = Col List Box(
dt,
all,
yvar = (y << Get Selected)[1];
Show( yvar );
)
)
);
If( win["Button"] == 1, // if the user clicks OK...
xcol = Column( dt, xvar ); // get the columns
ycol = Column( dt, yvar );
);
d = 1;
m = Matrix( 72, 5 );
l = List( 72 );
l1 = List( 72 );
l2 = List( 72 );
For( c = 1, c < 3, c++,
For( b = 1, b < 7, b++,
transb = Match( b, 1, "Log", 2, "Sqrt", 3, "Square", 4, "Reciprocal", 5, "Exp", 6, "None", );
For( a = 1, a < 7, a++,
transa = Match( a,
1, "Log",
2, "Sqrt",
3, "Square",
4, "Reciprocal",
5, "Exp",
6, "None",
);
biv = Bivariate(
Y( "ycol" ),
X( "xcol" ),
Fit Special(
xTran( transb ),
yTran( transa ),
Degree( c ),
Centered Polynomial( 0 ),
{Line Color( {213, 72, 87} )}
),
//SendToReport(
// Dispatch(
// {"Transformed Fit Log to Log"},
// "Summary of Fit",
// OutlineBox,
// {Set Title( "Summary of Fit" )}
// )
//)

);
rbiv = Report( biv );
bivmatrix = Try( rbiv[Outline Box( 3 )][1][2] << Get As Matrix );
If( Is Missing( bivmatrix = Try( rbiv[Outline Box( 3 )][1][2] << Get As Matrix ) ),
0
);
//bivmatrix2 = rbiv[Number Col Box( 7 )] << Get As Matrix;
//bivMatrix3 = rbiv[Number Col Box( 8 )] << Get As Matrix;
bivR2 = bivmatrix[1];
bivRMSE = bivmatrix[3];
//bivIntercept = bivMatrix2[1];
//bivX = bivMatrix2[2];
//bivForm = rbiv[Text Edit Box( 4 )];
//l[d] = transb || "(" || xvar || ") vs " || transa || "(" || yvar || ")";
l[d]=xvar;
l1[d] = transb;
l2[d] = transa;
m[d, 1] = c;
m[d, 2] = bivR2;
m[d, 3] = bivRMSE;
Close All( Reports, No Save );
//m[d, 3] = bivIntercept;
//m[d, 4] = bivx;
d++;

);
)
);
ds = New Table( "Tranformation Evalutation" || " " || xvar || " vs " || yvar );
ds << New Column("Independent Variable", Character, Text, Width(5), Values(l));
ds << New Column( "X Transformation", Character, Text, Width( 5 ), Values( l1 ) );
ds << New Column( "Y Transformation", Character, Text, Width( 5 ), Values( l2 ) );
ds << New Column( "Polynomial Degree", Numeric, Continuous, Width( 5 ), Values( m[0, 1] ) );
ds << New Column( "R2", Numeric, Continuous, Width( 5 ), Values( m[0, 2] ) );
ds << New Column( "RMSE", Numeric, Continuous, Width( 5 ), Values( m[0, 3] ) );

 

1 ACCEPTED SOLUTION

Accepted Solutions
monica_beals

Staff

Joined:

Dec 2, 2014

Solution

Here's another approach to the dialog that lets the user select one or more columns for X and Y. This returns a list of columns for each role. With multiple columns selected, though, the remainder of your script will need to do additional processing of the list to retrieve the columns before looping through the transformations - you may need to add another level of iteration.

 

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Body Fat.jmp" );
win = New Window( "Transformation Variable Selection",
	<<Modal,
	Text Box( "Available Columns" ),
	H List Box(
		// list box to present cols
		allcols = Col List Box( dt, all ), // or specify numeric instead of all
		Lineup Box(
			2,
			bbx = Button Box( "X, Factor", xcols << append( allcols << get selected ) ), 
			// list box to receive cols
			xcols = Col List Box( "Numeric", <<Modeling Type( {"Continuous"} ), min items( 1 ) ),
			bby = Button Box( "Y, Response", ycols << append( allcols << get selected ) ), 
			// list box to receive cols
			ycols = Col List Box( "Numeric", <<Modeling Type( {"Continuous"} ), min items( 1 ) )
		),
		V List Box(
			bbOK = Button Box( "OK",
				xvar = (xcols << Get Items);
				yvar = (ycols << Get Items);
				// Show( xvar, yvar );
			),
			bbCancel = Button Box( "Cancel" )
		)
	)
);
// stop the script if user chooses Cancel
If( win["Button"] == -1,
	Throw( "User Cancelled" )
);

 

 

4 REPLIES
ih

Community Trekker

Joined:

Sep 30, 2016

When getting the column names from the Col List Box in your script, you subset the list that is returned with [1].  This only returns the first selected column.  If you remove the brackets and then iterate over the lists you can apply your script to multiple x and y column pairs.  You likely want to summarize or concatenate your results but the basic idea would look like the script below. Note that to speed things up I only evaluate a=1 (see the commented /* 7 */)

 

Names Default To Here( 1 );

//wrap your script to process both variables in a function
//this is not necessary but it make the new for loops more clear
processpair = function({dt, xvar, yvar},
	d = 1;
	m = Matrix( 72, 5 );
	l = List( 72 );
	l1 = List( 72 );
	l2 = List( 72 );
	For( c = 1, c < 3, c++,
		For( b = 1, b < 7, b++,
			transb = Match( b, 1, "Log", 2, "Sqrt", 3, "Square", 4, "Reciprocal", 5, "Exp", 6, "None", );
			For( a = 1, a < 7 /* 7 */, a++,
				transa = Match( a, 1, "Log", 2, "Sqrt", 3, "Square", 4, "Reciprocal", 5, "Exp", 6, "None", );
				biv = Bivariate(
					Y( Column( yvar ) ),
					X( Column( xvar ) ),
					Fit Special(
						xTran( transb ),
						yTran( transa ),
						Degree( c ),
						Centered Polynomial( 0 ),
						{Line Color( {213, 72, 87} )}
					), 
	//SendToReport(
					// Dispatch(
					// {"Transformed Fit Log to Log"},
					// "Summary of Fit",
					// OutlineBox,
					// {Set Title( "Summary of Fit" )}
					// )
					//)

				);
				rbiv = Report( biv );
				bivmatrix = Try( rbiv[Outline Box( 3 )][1][2] << Get As Matrix );
				If( Is Missing( bivmatrix = Try( rbiv[Outline Box( 3 )][1][2] << Get As Matrix ) ),
					0
				);
	//bivmatrix2 = rbiv[Number Col Box( 7 )] << Get As Matrix;
				//bivMatrix3 = rbiv[Number Col Box( 8 )] << Get As Matrix;
				bivR2 = bivmatrix[1];
				bivRMSE = bivmatrix[3];
	//bivIntercept = bivMatrix2[1];
				//bivX = bivMatrix2[2];
				//bivForm = rbiv[Text Edit Box( 4 )];
				//l[d] = transb || "(" || xvar || ") vs " || transa || "(" || yvar || ")";
				l[d] = xvar;
				l1[d] = transb;
				l2[d] = transa;
				m[d, 1] = c;
				m[d, 2] = bivR2;
				m[d, 3] = bivRMSE;
				Close All( Reports, No Save );
	//m[d, 3] = bivIntercept;
				//m[d, 4] = bivx;
				d++;

			);
		)
	);
	ds = New Table( "Tranformation Evalutation" || " " || xvar || " vs " || yvar );
	ds << New Column( "Independent Variable", Character, Text, Width( 5 ), Values( l ) );
	ds << New Column( "X Transformation", Character, Text, Width( 5 ), Values( l1 ) );
	ds << New Column( "Y Transformation", Character, Text, Width( 5 ), Values( l2 ) );
	ds << New Column( "Polynomial Degree", Numeric, Continuous, Width( 5 ), Values( m[0, 1] ) );
	ds << New Column( "R2", Numeric, Continuous, Width( 5 ), Values( m[0, 2] ) );
	ds << New Column( "RMSE", Numeric, Continuous, Width( 5 ), Values( m[0, 3] ) );
);

//use sample data
dt = Open( "$SAMPLE_DATA/Body Fat.jmp" );

xvar = .;
yvar = .;



win = New Window( "Transformation Variable Selection",
	Text Box( " Select Numeric Columns. " ),
	H List Box(
		Text Box( " X, Factor " ),
		x = Col List Box(
			dt, // data table reference
			all, // display all columns from the data table
			// get the name of the selected column before the window closes
			xvars = (x << Get Selected);
			Show( xvar );
		),
		Text Box( "Y, Response" ),
		y = Col List Box(
			dt,
			all,
			yvars = (y << Get Selected);
			Show( yvar );
		)
	),
	tbStatus = Text Box("Ready."),
	Button Box("Analyze Variables",

		//Get variables
		xvars = x << Get selected;
		yvars = y << Get selected;
		
		If( n items(xvars) == 0  | n items(yvars) == 0, 
			tbStatus << Set Text("Select at least one ind and one dep variable to continue."),
			
			//for each combination of x and y variable
			for( xvar_n = 1, xvar_n <= N items(xvars), xvar_n ++,
				xvar = xvars[xvar_n];
				show(xvar);
				for(yvar_n = 1, yvar_n <= N items(yvars), yvar_n ++,
					yvar = yvars[yvar_n];
					show(yvar);
					
					//Run your script
					processpair( dt, xvar, yvar );
				)
			);
		);
	)
);

edited: fixed some bugs, see re-write later in thread 

 

dkraeuter_sunne

Community Trekker

Joined:

Jun 2, 2016

Whenever I remove the [1] (see underligned and italicized in the script below) from the portion of the script below, it doesn't work.  The script you provided does not allow me to click okay on the text box and therefore doesn't actual run the bivariate portion of the script.

 

I don't mean to be a pain, I am still trying to get a handle on JSL. Any help you can provide would be greatly appreciated.

 

       H List Box(

              Text Box( " X, Factor " ),

              x = Col List Box(

                     dt, // data table reference

                     all, // display all columns from the data table

                     xvar = (x << Get Selected)[1];

// get the name of the selected column before the window closes

                     Show( xvar );

              ),

              Text Box( "Y, Response" ),

              y = Col List Box(

                     dt,

                     all,

                     yvar = (y << Get Selected)[1];

                     Show( yvar );

ih

Community Trekker

Joined:

Sep 30, 2016

Sorry about that, there were a few bugs in that script.  Here is something closer to how I would implement this:

Names Default To Here( 1 );
//Fit all requested combinations of transformations and fit types for a single pair of variables
processPair = function({dt, xvar, yvar},
	transformations = {"Log", "Sqrt", "Square", "Reciprocal", "Exp", "None"};
	
	For( transx = 1, transx < 7, transx++,		
		For( transy = 1, transy < 7 /* 7 */, transy++,			
			For (FitDegree = 1, FitDegree <= 3, FitDegree++,
				
				biv = dt << Bivariate(
					Y( Column( yvar ) ),
					X( Column( xvar ) ),
					Fit Special(
						xTran( transformations[transx] ),
						yTran( transformations[transy] ),
						Degree( FitDegree ),
						//Centered Polynomial( 1 ),
						{Line Color( {212, 73, 88} )}
					)
				);
				
				rbiv = Report( biv );
				bivmatrix = Try( rbiv[Outline Box( 3 )][1][2] << Get As Matrix );
				If( Is Missing( bivmatrix = Try( rbiv[Outline Box( 3 )][1][2] << Get As Matrix ) ),	0 );
				
				dtOutput << Add Rows( 1, At End );
				rowout = n rows( dtOutput );
				
				Column( dtOutput, "Independent Variable" )[rowout] = xvar;
				Column( dtOutput, "Dependent Variable" )[rowout] = yvar;
				Column( dtOutput, "X Transformation" )[rowout] = transformations[transx];
				Column( dtOutput, "Y Transformation" )[rowout] = transformations[transy];
				Column( dtOutput, "Polynomial Degree" )[rowout] = FitDegree;
				Column( dtOutput, "R2" )[rowout] = bivmatrix[1];
				Column( dtOutput, "RMSE" )[rowout] = bivmatrix[3];
				
				biv << Close Window;
			);
		);
	);	
);

//Function to analyze all variables, runs when button is pressed and at least one variable is selected
processAll = function({dt, xvars, yvars},
	tbStatus << Set Text("Starting analysis...");
	
	//Make a data table to hold output
	dtOutput = New Table( "Tranformation Evalutation Results",
		New Column( "Independent Variable", Character, Text, Width( 5 ) ),
		New Column( "Dependent Variable", Character, Text, Width( 5 ) ),
		New Column( "X Transformation", Character, Text, Width( 5 ) ),
		New Column( "Y Transformation", Character, Text, Width( 5 ) ),
		New Column( "Polynomial Degree", Numeric, Ordinal, Width( 5 ) ),
		New Column( "R2", Numeric, Continuous, Width( 5 ) ),
		New Column( "RMSE", Numeric, Continuous, Width( 5 ) )
	);
	
	//for each combination of x and y variable update status and fit all types of transformations
	for( xvar_n = 1, xvar_n <= N items(xvars), xvar_n++,
		for(yvar_n = 1, yvar_n <= N items(yvars), yvar_n++,
			tbStatus << Set Text("Processing x = '" || xvars[xvar_n] || "', y = '" || yvars[yvar_n] || "'");
			processPair( dt, xvars[xvar_n], yvars[yvar_n] );
		);
	);	
	tbStatus << Set Text("Done with analysis.");	
);

//open sample data
dt = Open( "$SAMPLE_DATA/Body Fat.jmp" );

//window to select columns and button to run functions above
win = New Window( "Transformation Variable Selection",
	
	Text Box( " Select Numeric Columns. " ),
	H List Box(
		Text Box( " X, Factor " ),
		x = Col List Box(
			dt, // data table reference
			all // display all columns from the data table;
		),
		Text Box( "Y, Response" ),
		y = Col List Box(
			dt,
			all
		)
	),
	tbStatus = Text Box("Ready."),
	Button Box("Analyze Variables",

		//Get variables
		xvars = x << Get selected;
		yvars = y << Get selected;
		
		If( n items(xvars) == 0  | n items(yvars) == 0, 
			tbStatus << Set Text("Select at least one ind and one dep variable to continue."),
			processAll(dt, xvars, yvars);
		)
	)
);
monica_beals

Staff

Joined:

Dec 2, 2014

Solution

Here's another approach to the dialog that lets the user select one or more columns for X and Y. This returns a list of columns for each role. With multiple columns selected, though, the remainder of your script will need to do additional processing of the list to retrieve the columns before looping through the transformations - you may need to add another level of iteration.

 

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Body Fat.jmp" );
win = New Window( "Transformation Variable Selection",
	<<Modal,
	Text Box( "Available Columns" ),
	H List Box(
		// list box to present cols
		allcols = Col List Box( dt, all ), // or specify numeric instead of all
		Lineup Box(
			2,
			bbx = Button Box( "X, Factor", xcols << append( allcols << get selected ) ), 
			// list box to receive cols
			xcols = Col List Box( "Numeric", <<Modeling Type( {"Continuous"} ), min items( 1 ) ),
			bby = Button Box( "Y, Response", ycols << append( allcols << get selected ) ), 
			// list box to receive cols
			ycols = Col List Box( "Numeric", <<Modeling Type( {"Continuous"} ), min items( 1 ) )
		),
		V List Box(
			bbOK = Button Box( "OK",
				xvar = (xcols << Get Items);
				yvar = (ycols << Get Items);
				// Show( xvar, yvar );
			),
			bbCancel = Button Box( "Cancel" )
		)
	)
);
// stop the script if user chooses Cancel
If( win["Button"] == -1,
	Throw( "User Cancelled" )
);