How to transform spearman correlation table into a matrix format like pearson?

[#1]

In multivariate analysis report the spearman correlation results are in a table; can I get this in a matrix format like the pearson correlation matrix?

[#2 · Accepted solution]

Hi @frankderuyck,

Once the results of the Spearman correlation obtained in the table of the Multivariate platform, you can right-click on the table, select "Make into Datatable", and then use the Graph Builder on this new data table to reproduce the kind of graph you're used to with Pearson correlation matrix:

You can also generate a table with Tabulate platform on this datatable:

As Pearson correlation is symmetric, you might expect some missing values for pairs of variables (if you have correlation value between A and B, the value between B and A is not displayed but is the same). To have nice looking graph or Tabulate tables, you might have to add column property "Value order" to order the different variables.

JSL code to generate graph from the table:

Graph Builder(
	Size( 534, 464 ),
	Show Control Panel( 0 ),
	Variables( X( :by Variable ), Y( :Variable ), Color( :Spearman ρ ) ),
	Elements( Heatmap( X, Y, Legend( 7 ), Label( "Label by Value" ) ) ),
	SendToReport(
		Dispatch( {}, "400", ScaleBox,
			{Legend Model(
				7,
				Properties(
					0,
					{gradient( {Scale Values( [-1 0 1] )} )},
					Item ID( "Spearman ρ", 1 )
				)
			)}
		),
		Dispatch( {}, "graph title", TextEditBox,
			{Set Text( "Variable by Variable Spearman correlation matrix" )}
		)
	)
);

And for table from tabulate (that you can transform into a JMP datatable with red triangle option from Tabulate "Make Into Data Table"):

Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table(
			Grouping Columns( :by Variable ),
			Analysis Columns( :Spearman ρ ),
			Statistics( Mean )
		),
		Row Table( Grouping Columns( :Variable ) )
	)
);

Please find attached the datatable and analysis used for this example.

Hope this answer will help you,

[#3]

Thanks Victor, this graph builder option looks interesting!

[#4]

Guess there is no spearman matrix option under a red trangle?

[#5]

Whe I run the scripts on the pearman table I get very confusing picture..? Aso the tabulation script does not generate a pearson-like correlation matrix? Value ordening is not easy for I have many variables. The pearson correlation matrix is perfect, why not possible for spearman?

[#6]

In attachment the spearman rank table

[#7 · Accepted solution]

Hi Franck,

Yes, the ordering of the variables is very important to get a nice triangle confusion matrix.
I found a solution with your dataset:

1. Right-click on column "Variable", New Formula Column>Group By.
2. Then, right-click on column "Variable", New Formula Column>Character>Rank (reverse order). Then redo the action 1 so that "Variable" is no longer the GroupBy variable.
3. Do the same steps for the column "By Variable".
4. Change the modeling type of the two new columns to Ordinal.
5. Then you can use Graph Builder, place the Variable, By Variables and Spearman columns, and add the "Reverse Rank[Variable][Variable]" column on top of "Variable" axis, and "Reverse Rank[by Variable][by Variable]" column on top of "By variable" axis.

You should then get the nice looking Spearman correlation matrix:

Or once the rank columns are created, you can directly use this Graph Builder script:

Graph Builder(
	Size( 733, 664 ),
	Show Control Panel( 0 ),
	Variables(
		X( :"Reverse Rank[by Variable][by Variable]"n ),
		Y( :"Reverse Rank[Variable][Variable]"n ),
		Color( :Spearman ρ )
	),
	Elements( Heatmap( X, Y, Legend( 22 ), Label( "Label by Value" ) ) ),
	SendToReport(
		Dispatch( {}, "400", ScaleBox,
			{Legend Model(
				22,
				Properties(
					0,
					{gradient( {Scale Values( [-1 0 1] )} )},
					Item ID( "Spearman ρ", 1 )
				)
			)}
		),
		Dispatch( {}, "graph title", TextEditBox,
			{Set Text( "Correlation matrix Spearman" )}
		)
	)
);

For the Tabulate option, this is more straightforward, you just need to check the option "Order by count of grouping columns" to get the nice matrix:

Please find attached your dataset with the two columns and the visualization added.

Hope this answer will help you,

[#8]

Excellent, thanks Victor!