cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Aam_jmp
Level IV

Highlight cells based on matched column names

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  Capture.PNG

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:

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Highlight cells based on matched column names

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

Jim

View solution in original post

12 REPLIES 12
pmroz
Super User

Re: Highlight cells based on matched column names

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 );
	);
);
Aam_jmp
Level IV

Re: Highlight cells based on matched column names

@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

txnelson
Super User

Re: Highlight cells based on matched column names

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

Jim
Aam_jmp
Level IV

Re: Highlight cells based on matched column names

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

txnelson
Super User

Re: Highlight cells based on matched column names

That sounds like a reasonable approach.

Jim
Aam_jmp
Level IV

Re: Highlight cells based on matched column names

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.

txnelson
Super User

Re: Highlight cells based on matched column names

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]] );
 
	);
);
Jim
Aam_jmp
Level IV

Re: Highlight cells based on matched column names

@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. 

Jeff_Perkinson
Community Manager Community Manager

Re: Highlight cells based on matched column names

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";
-Jeff