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: * Get a datatable after using tabulate * Stack all the columns (except for 'type'). Then do a summary of the whole data set to find min, median and max. * 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. sample_datatable_heatmap.jmp

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Creating heatmap on a datatable and updating color gradient with script

tarkan_bih

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:

Get a datatable after using tabulate
Stack all the columns (except for 'type'). Then do a summary of the whole data set to find min, median and max.
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.

sample_datatable_heatmap.jmp

Accepted answers

txnelson

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
		)
	);
);

All comments

txnelson

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;<BR />
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
	)
);

tarkan_bih

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
	)
);

txnelson

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
		)
	)
);

tarkan_bih

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

txnelson

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
		)
	);
);