How to Turn a Table into a Heatmap

Introduction

This article describes how to go from a standard table, in this case using a Pick Any - Grid question in the blue drop-down, and SUMMARY in the brown drop-down...

...to a table that is shaded proportionally to the cell values:

Method

1. Select your table.

2. Go to Automate > Custom Rule.

3. Paste the below into the dialog:

// Get the name of the first statistic.
var first_statistic = table.statistics[0];

// Get the first statistic's values.
var values = table.get(first_statistic);

// Some cells should be ignored.
var ignore_rows = [];
var ignore_columns = [];
if (first_statistic == 'Column %')
  ignore_rows.push(table.rowIndex('NET'));
else if (first_statistic == 'Row %')
  ignore_columns.push(table.columnIndex('NET'));

// Calculate the minimum and maximum values.
var min, max;
for (var row = 0; row < table.numberRows; row++)
 for (var column = 0; column < table.numberColumns; column++)
  if (ignore_rows.indexOf(row) == -1 && ignore_columns.indexOf(column) == -1) {
  var value = values[row][column];
  if (min == null && max == null) {
   min = value;
   max = value;
  } else {
   min = Math.min(min, value);
   max = Math.max(max, value);
  }
}

// Get the array of cell colors (currently blank, or white).
var cell_colors = table.cellColors;

for (var row = 0; row < table.numberRows; row++)
  for (var column = 0; column < table.numberColumns; column++)
   if (ignore_rows.indexOf(row) == -1 && ignore_columns.indexOf(column) == -1) {
   var value = values[row][column];

// Get the current value as a scale proportion (0-1)
var scale = (value - min) / (max - min);

// Make sure the current value is not missing data or infinity,
// which could arise if there is insufficient data on the table...
if (!isNaN(scale) && isFinite(scale)) {
// Create a color to represent where this value fits on the scale.

// covertHSVtoRGB() is a built-in function that Q provides.

// This varies the scale by saturation instead of hue:
var color = convertHSVtoRGB(32, scale, 1);

// Try and experiment using some different color schemes.

// In this scheme,
// the lowest color has a hue of 240 (blue).
// the highest color has hue of 0 (red).
// The beauty of the Hue, Saturation, Value (or Brightness) color space
// means that changing the value of Hue proportionally makes the color
// look 'hotter'.
//var color = convertHSVtoRGB(240 * (1 - scale), 1, 1);

// Set the color for this cell.
  cell_colors[row][column] = color;
 }
}

// Set the array of cell colors we just modified.
table.cellColors = cell_colors;

Note that:

• By default, it uses shades of orange but it can be modified so that it shows low values as blue (cold) and high values are red (hot). This is done by commenting out (i.e., putting // in front of) the line var color = convertHSVtoRGB(32, scale, 1); and un-commenting-out (i.e. removing // from) the line // var color = convertHSVtoRGB(240 * (1 - scale), 1, 1);
• If you change it from a table to a Grid of Bars Chart it will shade that chart.

4. Press the blue Play button > Close > OK > OK.

Next

How to Use Rules in Q

How to Customize a Rule

JavaScript Color Table