topic Re: Highlight cells based on matched column names in Discussions

Highlight cells based on matched column names

Aam_jmp — Mon, 04 Jun 2018 17:24:28 GMT

I have a script that colors the minimum values yellow for every Studentized column in a table. I want to also highlight values adjacent to these values in raw columns. For example, for Studentized Resid Rest Pulse, the cell adjacent to -2.321144119 in Rest Pulse column i.e. 44. How can I do this in jsl? Can regex or match be helpful? Thank you

dt = current data table();
col = dt << get column names( string );
nc = N Items( col );
For( i = 1, i <= nc, i++,
 Cols = Column(dt,i);
 If( Contains( col[i], "Studentized Resid" ),
 Min = dt << get rows where(as column(Cols)==(Col Min( Cols )));
 Cols << color cells(Yellow, Min);
 )
 );

I am looking for something like this:

Re: Highlight cells based on matched column names

pmroz — Fri, 01 Jun 2018 20:08:41 GMT

Here's one way with hard-coded column names:

dt = Current Data Table();
col = dt << get column names( string );
nc = N Items( col );
For( i = 1, i <= nc, i++,
	Cols = Column( dt, i );
	If( Contains( col[i], "Studentized Resid" ),
		Min = dt << get rows where( As Column( Cols ) == (Col Min( Cols )) );
		Cols << color cells( Yellow, Min );
		:Rest Pulse << color cells( Yellow, Min );
		:Run Pulse << color cells( Yellow, Min );
		:Max Pulse << color cells( Yellow, Min );
	);
);

Re: Highlight cells based on matched column names

Aam_jmp — Mon, 04 Jun 2018 13:07:58 GMT

@pmroz thank you for the respone, however, I am looking for a more dynamic solution to such a problem since I might have

a) A lot of columns

b) Different column names in the future.

Are there any JMP functions that can do the task? Thank you

Re: Highlight cells based on matched column names

txnelson — Tue, 05 Jun 2018 15:01:38 GMT

How do you envision the inputting of what columns need to be processed?

Have you read the Scripting Guide? There are many examples in the book that will give you ideas.

Help==>Books==>Scripting Guide

Re: Highlight cells based on matched column names

Aam_jmp — Tue, 05 Jun 2018 15:09:41 GMT

All the raw columns that have/match Studentized columns are to be highlighted. I was looking at the scriting guide and have been trying to work with Regex Match as well as substring to extract out column names without the "Studentized Resid" in it. I am keeping a list of row numbers of highlighted columns here as well, hoping it helps achieve what I want. Any better approach will be much appreciated. @txnelson

Re: Highlight cells based on matched column names

txnelson — Tue, 05 Jun 2018 15:22:33 GMT

That sounds like a reasonable approach.

Re: Highlight cells based on matched column names

Aam_jmp — Fri, 08 Jun 2018 13:04:44 GMT

I still haven't been able to figure out how I can solve this problem. I have looked aroound other questions and the scripting guide to get to my most recent script.

Here is my most recent script:

dt = current data table();
col = dt << get column names( string );
nc = N Items( col );

For( i = 1, i <= nc, i++,
 Cols = Column(dt,i);
 If( Contains( col[i], "Studentized Resid" ),
 Min = dt << get rows where(as column(Cols)==(Col Min( Cols )));
 Cols << color cells(Yellow, Min);
 //show(Loc())
 MinValue = Col Min(Cols);
 ColValue = As List(Cols << Get Values);
 show(Loc(ColValue,MinValue));
 show(substr(col[i], 19,100));
 
 )
 
 );

I would really appreciate if someone can show me how I can pursue using the Substring method to match columns. Thank you in advance.

Re: Highlight cells based on matched column names

txnelson — Fri, 08 Jun 2018 14:59:04 GMT

Here is a simple rework of your script. It works with your sample data, and should work with as many columns as you have in the data table

dt = Current Data Table();

// I changed the name from "col" to colNameList to keep from
// it being confused with the variable "Cols".  It isn't
// confusing to JMP, just to me
colNameList = dt << get column names( string );
nc = N Items( colNameLIst );

// Loop across all columns
For( i = 1, i <= nc, i++, 

	// Create a variable that contains the "column" representation
	// of the column name string value
	Cols = Column( dt, i );
	
	// If the column name starts with the string "Studentized Resid"
	// and if so, then process
	// I changed this because your Substr() function below indicates
	// that the column with "Studentized Resid" will be at the beginning
	If( Left( colNameList[i], 17 ) == "Studentized Resid", 
		
		// Find the minimum value for the column
		MinValue = Col Min( Cols );
		
		// Find all of the rows that have the minimum value.  There
		// could be more than one row with the minimum value, but this
		// code only uses the first row
		MinRows = dt << get rows where( As Column( Cols ) == MinValue );
		
		// Theoretically, there should always be a matching value for this,
		// but it is a good way to make sure the code will not fail.  Also,
		// if you ever want to deal with the posibility of more than one
		// row matching the minimum, you would do it using this type of
		// code structure
		If( N Rows( MinRows ) > 0,
			Cols << color cells( Yellow, MinRows )
		);
		
		// Output the requested values
		Show( Substr( colNameList[i], 19 ), Cols[MinRows[1]] );
 
	);
);

Re: Highlight cells based on matched column names

Aam_jmp — Fri, 08 Jun 2018 17:10:45 GMT

@txnelson Hi Jim, I'm sorry if I am missing anything here. I wanted to also highlight the adjacent raw columns for every studentized column. I am not able to make this work.

Re: Highlight cells based on matched column names

Jeff_Perkinson — Fri, 08 Jun 2018 20:17:35 GMT

Is the Substr() function what you're missing?

resid_colname="Studentized Resid Rest Pulse";

//There are 19 characters in "Studentized Resid "
 
orig_colname = substr(resid_colname, 19);

show(orig_colname);
/*:

orig_colname = "Rest Pulse";

Re: Highlight cells based on matched column names

txnelson — Fri, 08 Jun 2018 22:05:53 GMT

I may not be understanding your issue. If you want to color the cells adjecent to the min values found for the columns that contain "Studentized ....." then all you have to do is to do it. You have the list of column names......colNamesList, and you are looking through the columns. When you find a column that has "Studentized......" in it, you then find the name of the other column you want to color and the row that is the minimum value. So that means, you also know the cells that are adjecent to the cells you are going to color.....

column( Substr( colNameList[i], 19 ) ) <<  << color cells( Yellow, MinRows )

and if you add this right after the coloring of the "Studentized......" column, it will color the appropriate cell

Re: Highlight cells based on matched column names

Aam_jmp — Tue, 12 Jun 2018 13:29:15 GMT

@txnelson Do you mean something like this?

dt = Current Data Table();

// I changed the name from "col" to colNameList to keep from
// it being confused with the variable "Cols".  It isn't
// confusing to JMP, just to me
colNameList = dt << get column names( string );
nc = N Items( colNameLIst );

// Loop across all columns
For( i = 1, i <= nc, i++, 

	// Create a variable that contains the "column" representation
	// of the column name string value
	Cols = Column( dt, i );
	
	// If the column name starts with the string "Studentized Resid"
	// and if so, then process
	// I changed this because your Substr() function below indicates
	// that the column with "Studentized Resid" will be at the beginning
	If( Left( colNameList[i], 17 ) == "Studentized Resid", 
		
		// Find the minimum value for the column
		MinValue = Col Min( Cols );
		
		// Find all of the rows that have the minimum value.  There
		// could be more than one row with the minimum value, but this
		// code only uses the first row
		MinRows = dt << get rows where( As Column( Cols ) == MinValue );
		
		// Theoretically, there should always be a matching value for this,
		// but it is a good way to make sure the code will not fail.  Also,
		// if you ever want to deal with the posibility of more than one
		// row matching the minimum, you would do it using this type of
		// code structure
		If( N Rows( MinRows ) > 0,
			Cols << color cells( Yellow, MinRows ),
			//column( Substr( colNameList[i], 19 ) ) << color cells( Yellow, MinRows )
		);
                column( Substr( colNameList[i], 19 ) ) << color cells( Yellow, MinRows )
	)	
		
);

I made it to work. Thanks a lot for the guidance..

Re: Highlight cells based on matched column names

Aam_jmp — Tue, 12 Jun 2018 13:12:17 GMT

@Jeff_Perkinson Hi Jeff, I have already worked the substring part out. Thanks though!!