Creating heatmap on a datatable and updating color gradient with script

[#1]

Hi,

I have a lot data that I like to plot as a heatmap from a data table. I have attached an image below of a sample data table image and the steps I do to create the heatmap on the data table. It follows the following steps:

1. Get a datatable after using tabulate
2. Stack all the columns (except for 'type'). Then do a summary of the whole data set to find min, median and max.
3. Take the min, median and max values from step2 and put them into the color gradient values.

The result of the data table after running these steps is shown below. Currently I do step 3 manually where whatever value I get post Summary I input into the color gradient values. Is there a way to take the values (min, median, max) from Summary datatable with a script and input it into the color gradient? 

I have attached the sample dataset as well.

[#2]

Here is the JSL that will set the color gradient

names default to here(1);
dt=current data table();

dt:"Group:0, test:A"n << set property("color gradient",{"Green Yellow Red", Range(
	{-142.439058836207, -42.6065023706896, -97.9221899694684}
)});

I find the easiest way to insert the values into the required lists is to use code similar to below:

Max = -142.439058836207;
Min = -42.6065023706896;
Mid = -97.9221899694684;

Eval(
	Substitute(
			Expr(
				dt:"Group:0, test:A"n << set property(
					"color gradient",
					{"Green Yellow Red", Range( {__Max__, __Min__, __Mid__} )}
				);
			),
		Expr( __Max__ ), Max,
		Expr( __Min__ ), Min,
		Expr( __Mid__ ), Mid
	)
);

[#3]

Thanks for the input! I am using your script as a good starting point but I was hoping to make it more dynamic.

• So in the script you manually set the max, min and mid to values is there any way for them to be set by the output summary table in step2 in the image. Basically this max, min and mid are in the scope for Group:0 test:A I would like to get the scope max, min and mid for the whole table.
• I had to add Color Cell by Value(1) into my script and I have been trying to set reverse scale as well and have been getting error messages any idea how to do this?
• Also is it safe to assume that to run the color gradient on all columns I would need to but a for loop outside of the code you sent? Or is there another way to do this?

names default to here(1);
dt=current data table();

dt:"Group:0, test:A" << set property("color gradient",{"Green Yellow Red", Range(
	{-142.439058836207, -42.6065023706896, -97.9221899694684}
)});

Max = -142.439058836207;
Min = -42.6065023706896;
Mid = -97.9221899694684;
Eval(
	Substitute(
			Expr(
				dt:"Group:0, test:A"n << set property(
					"color gradient",
					{"Green Yellow Red", Range( {__Max__, __Min__, __Mid__} )},	
				)
				:dt <<  Color Cell by Value( 1 );
			),
		Expr( __Max__ ), Max,
		Expr( __Min__ ), Min,
		Expr( __Mid__ ), Mid
	)
);

[#4]

Here is an example that automates the column coloring of the Big Class sample data table.  You should be able to branch off from this script to get it working with your data

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );

colList = dt << get column names( continuous, string );
For( i = 1, i <= N Items( colList ), i++, 


	Eval(
		Substitute(
				Expr(
					Summarize(
						dt,
						myMax = Max( As Column( __col__ ) ),
						myMin = Min( As Column( __col__ ) ),
						myMid = Mean( As Column( __col__ ) )
					);
					Column( __col__ ) <<
					set property(
						"color gradient",
						{"Green Yellow Red",
						Range( {__Max__, __Min__, __Mid__} ), "Reverse Scale"}
					) << Color Cell by Value( 1 );
				),
			Expr( __col__ ), colList[i],
			Expr( __Max__ ), myMax,
			Expr( __Min__ ), myMin,
			Expr( __Mid__ ), myMid
		)
	)
);

[#5]

When I run your script as is (using big class dataset) it crashes for me and I get the message below:

Name Unresolved: myMax in access or evaluation of 'myMax' , myMax/*###*/

at line 25 in Script 4.jsl

[#6 · Accepted solution]

My error......the initial values of myMax etc. were carried forward from previous executions as I was writing the script.  Below is a fix

Names Default To Here( 1 );
Clear Symbols();
dt = Open( "$SAMPLE_DATA/big class.jmp" );

colList = dt << get column names( continuous, string );
For( i = 1, i <= N Items( colList ), i++, 

	Eval(
		Substitute(
				Expr(
					Summarize(
						dt,
						myMax = Max( __col__ ),
						myMin = Min( __col__ ),
						myMid = Mean( __col__ )
					)
				),
			Expr( __col__ ), colList[i]
		)
	);


	Eval(
		Substitute(
				Expr(
					Column( __col__ ) <<
					set property(
						"color gradient",
						{"Green Yellow Red",
						Range( {__Max__, __Min__, __Mid__} ), "Reverse Scale"}
					) << Color Cell by Value( 1 );
				),
			Expr( __col__ ), colList[i],
			Expr( __Max__ ), myMax,
			Expr( __Min__ ), myMin,
			Expr( __Mid__ ), myMid
		)
	);
);

[#7]

Thanks this is very close to what I need! The only thing off is that the heat map gives the gradient based on the max, min and mid for that column instead of from the whole datatable. Basically in the code below I want to find max, min and mid before I enter the for loop to color the cells. When I do this the functions Maximum, Minimum and Median do not recognize the colList being passed to extract max value. Is there a way for jsl to find the max, min and median based on the list when running get column names? Or does this need to go into a for loop?

Names Default To Here( 1 );
Clear Symbols();
//dt = Open( "$SAMPLE_DATA/big class.jmp" );
dt = Current Data Table();

colList = dt << get column names( continuous, string );
myMax = Maximum(colList);
myMin = Minimum(colList);
myMid = Median(colList);
print(myMax);
print(myMin);
print(myMid);
print(colList);
For( i = 1, i <= N Items( colList ), i++, 
/*
	Eval(
		Substitute(
				Expr(
					Summarize(
						dt,
						myMax = Max( __col__ ),
						myMin = Min( __col__ ),
						myMid = Mean( __col__ )
					)
				),
			Expr( __col__ ), colList[i]
		)
	);
*/

	Eval(
		Substitute(
				Expr(
					Column( __col__ ) <<
					set property(
						"color gradient",
						{"Green Yellow Red",
						//Range( {__Max__, __Min__, __Mid__} ),"Reverse Scale"}
						Range( {__Max__, __Min__, __Mid__} )}
					) << Color Cell by Value( 1 );
				),
			Expr( __col__ ), colList[i],
			Expr( __Max__ ), myMax,
			Expr( __Min__ ), myMin,
			Expr( __Mid__ ), myMid
		)
	);
);

[#8]

The Maximum, Minimum and Median functions will not perform a cumulative calculation across a list of columns.  The easiest way i have found to do this, is to convert the referenced columns into a matrix, and then calculate the statistics, since the Maximum, Minimum and Median functions will work across a matrix.

Here is a simple script that does that calculation

Names Default To Here( 1 );
Clear Symbols();
dt = Open( "$SAMPLE_DATA/big class.jmp" );

colList = dt << get column names( continuous, string );

// Create a temporary subset to calculate the required overall statistics
dtsub = dt << subset(columns(colList), selected rows(0),invisible);

// Move the data into amatrix
allData = dtsub << get as matrix;

// Close the no longer needed temporary daa table
close(dtsub, nosave);

// Calculate the statistics across the full data matrix
myMax = Maximum(allData);
myMin = Minimum(allData);
myMid = Median(allData);

I strongly recommend that you take the time to read the Scripting Guide, which can be found under the 

     Help=>JMP Documentation Library

I also suggest that you familiarize yourself with the Scripting Index, which is also available under the Help pull down menu.

[#9]

Thanks for all the input and help! I was not aware of scripting index it was a big help. I posted the code I am using below as my final version

Names Default To Here( 1 );
Clear Symbols();
dt = Current Data Table();


///////////////////////////////////
// Setting Variables for script ///
rev_scale =1;
mean_median = 0; //mean=0, median=1
print(mean_median);
///////////////////////////////////


//gets all columns that are continous and stores that in a list as strings.
colList = dt << get column names( continuous, string );

// Create a temporary subset where the columns are the column list, selects all rows, 
// invisible makes it so you don't see the datatable on screen since it is temporary
dtsub = dt << subset(columns(colList), selected rows(0),invisible);

// Move the data into a matrix this will be used later to get min, max, median|mean 
allData = dtsub << get as matrix;

// Close the no longer needed temporary data table
close(dtsub, nosave);

// Calculate the statistics across the full data matrix
myMax = Maximum(allData);
myMin = Minimum(allData);

if(mean_median == 0,
	myMid = Mean(allData),
	myMid = Median(allData)
);

print(myMax);
print(myMin);
print(myMid);

// Run for loop over data table with the min, max and median|mean calculated from above
// gradient color scheme needs to be set.
if(rev_scale == 1,
	For( i = 1, i <= N Items( colList ), i++, 
		Eval(
			Substitute(
					Expr(
						Column( __col__ ) <<
						set property(
							"color gradient",
							{"Green Yellow Red",
							Range( {__Max__, __Min__, __Mid__} ),"Reverse Scale"}
						) << Color Cell by Value( 1 );
					),
				Expr( __col__ ), colList[i],
				Expr( __Max__ ), myMax,
				Expr( __Min__ ), myMin,
				Expr( __Mid__ ), myMid
			)
		);
	);
,
	For( i = 1, i <= N Items( colList ), i++, 
		Eval(
			Substitute(
					Expr(
						Column( __col__ ) <<
						set property(
							"color gradient",
							{"Green Yellow Red",
							Range( {__Max__, __Min__, __Mid__} )}
						) << Color Cell by Value( 1 );
					),
				Expr( __col__ ), colList[i],
				Expr( __Max__ ), myMax,
				Expr( __Min__ ), myMin,
				Expr( __Mid__ ), myMid
			)
		);
	);
);