Articles in this section

Sorting Tables By the Largest Column

  • Updated
Follow

This example code can be used to create a custom Rule that sorts the table according to the column that has the largest Column Sample Size as found in the Statistics - Below of a table.

Technical details

There are a number of points to note about how the table will be sorted:

  • Tables will be sorted in descending order.
  • If the table only has a single column, then it will be sorted.
  • This script will have no effect when Column Sample Size is not available from the Statistics - Below.
  • If two or more columns have the same value for Column Sample Size then the table will be sorted according to the left-most column that is not the NET or SUM column.
  • Rows that look like they are Don't Know, Other/Specify, All of these, or None of these responses will be left at the bottom of the table, as will NET and SUM rows.
  • Tables will be sorted to the %, Column %, Average, or Probability % statistic depending on the Question Type or Structure of the questions that are shown in the table.

Method

This example uses the Colas.sav file.

1. First create a summary table of Q5. Brands associations.

2. Apply the below as a custom rule. See here for Q and here for Displayr.

var applicable_table; // Prevent an error when no below_table exists
try {
applicable_table = below_table.availableStatistics.indexOf('Column n') != -1;
} catch (e) {
applicable_table = false;
}
if (applicable_table) {// If this table has the 'Column n' statistic
// Choose which statistic to use to sort
var stat;
if (table.availableStatistics.indexOf('%') > -1)
stat = '%';
else if (table.availableStatistics.indexOf('Column %') > -1)
stat = 'Column %';
else if (table.availableStatistics.indexOf('Average') > -1)
stat = 'Average';
else if (table.availableStatistics.indexOf('Probability %') > -1)
stat = 'Probability %';
else stat = table.statistics[0];
var max_index = 0;
if (table.numberColumns > 1) {
// Obtain Column n from the Statistics - Below and make sure that the resulting array is the correct shape
var column_ns = below_table.get('Column n');
if (column_ns.length == 1)
column_ns = column_ns[0];
var num_cols = table.numberColumns;
var column_labels = table.columnLabels;
// Find largest column n and the index of the column
var max_col_n = Number.NEGATIVE_INFINITY;
for (var j = 0; j < num_cols; j++)
if (!isNaN(column_ns[j]) && column_ns[j] > max_col_n && column_labels[j] != 'NET' && column_labels[j] != 'SUM') {
max_col_n = column_ns[j];
max_index = j;
} 
}
// Obtain the values from the column with the largest column n
// If the row is a 'Other', 'Dont Know', 'None of these', 'All of these'
// or 'NET' or 'SUM' then assign it a very large value to keep it at the 
// bottom of the table.
var values = table.get(stat);
var labels = table.rowLabels;
var row_indices = [];// Create an array of the original row numbers, which we will sort.
var values_to_sort = []; // Array of values from the target column
for (var row = 0; row < table.numberRows; row++) {
row_indices.push(row);
values_to_sort.push(values[row][max_index]);
var label = labels[row];
var replacement_value = getBottomAnchoredReplacementValueForLabel(label);
if (replacement_value != null)
values_to_sort[row] = replacement_value + row / 100000.0;//adding a small number to break ties
if (isNaN(values_to_sort[row]))
values_to_sort[row] = -999999999900; // Treat NaN as a large negative number
}
// Now sort the array of row numbers by the statistical values.
// This 'function' is run many times - to compare each row against another.
row_indices.sort(function (row_x, row_y) {
// Get the values for the two rows we are comparing.
var value_x = values_to_sort[row_x];
var value_y = values_to_sort[row_y];
return value_y - value_x;
});
table.sortRows(row_indices);// Instruct the table to sort the rows according to the new order.
var footers = table.extraFooters;
if (table.numberColumns > 1)
footers.push('Sorted according to: ' + column_labels[max_index] + ' by JavaScript');
else
footers.push('Sorted by JavaScript');
table.extraFooters = footers;
}
// checks to see if a label contains one of the possible substrings (array)
function containsSubstring(label, possible_substrings) {
for (var i in possible_substrings)
if((label).toLowerCase().indexOf(possible_substrings[i]) != -1) // checks if label contains any of the above
return true;
return false;
}
// checks to see if a label represents a don't know
function isDontKnow(label) {
if (containsSubstring(label,['dk','d.k.','don\'t know','dont know','unsure','un-sure','not sure','do not know','no idea','N/A','applicable','not use', 'never use']))
return true;
if (label == 'NA' || label == 'na') //removes labels that are NA
return true;
return false;
}
// checks to see if a label is an other label
function isOther(label) {
var label_lower = label.toLowerCase();
return label_lower.indexOf('other') != -1;
}
// checks to see if a label represents a 'none of these' type option
function isNoneOfThese(label) {
return containsSubstring(label, ['none','nothing']);
}
// checks to see if a label represents a 'none of these' type option
function isAllOfThese(label) {
if (containsSubstring(label, ['all of these','any of these','all of them','any of them']))
return true;
var lower_label = label.toLowerCase();
if (lower_label == 'any' || lower_label == 'all')
return true;
return false;
}
// This function returns a very small value for labels that are
// 'Other', 'Dont Know', 'None of These', 'All of these' 
function getBottomAnchoredReplacementValueForLabel(label) {
if (isOther(label))
return -999999999995;
else if (isNoneOfThese(label))
return -999999999996;
else if (isAllOfThese(label))
return -999999999997;
else if (isDontKnow(label))
return -999999999998;
else if (label == 'NET' || label == 'SUM')
return -999999999999;
else
return null;
}

Note, there are some more advanced techniques used in this script. These include:

  • A try/catch call is made at the start of the script to prevent an error from being generated if the table does not have any Statistics - Below. This happens on tables showing Pick One - Multi or Nominal - Multi questions.
  • The functions isDontKnow, isOther, isNoneOfThese, isAllOfThese, and getBottomAnchoredReplacementValueForLabel have been used to figure out when a row should be kept at the bottom of the table. This is done by replacing values for such rows with large negative numbers, ensuring that such rows will remain at the bottom of the table when the rows are sorted. These functions are discussed more in JavaScript Text Analysis Functions
  • A comparison function is being created to sort the row numbers based on the statistics. See Sorting Arrays. This sorts in descending order (smallest value at the top).

See also

Related articles

Powered by Zendesk