Significance Testing in Tables - Color Comparisons with Specific Column

This rule uses the Column Comparisons to add color to cells which have a significant relationship to a particular column. This can be used, for example, to color any cells whose Column Comparisons letters indicate that the cell is significantly higher or lower than the NET. The rule will replace the column letters with colors when comparing against the target column, but will maintain the letters for other comparisons. This rule works within the spans of the table, so that if you wish to highlight comparisons with the NET or other column within each of the spans on your table, then this is done automatically.

Example

In the following table, we have used this rule to highlight results which are significant compared with the results in the NET column. In the options we have specified orange for cells which are significantly low, and yellow for cells that are significantly high. Note that the Column Comparisons letters remain for comparisons that do not include the NET column.

Technical details

The Column Comparisons do not test with the NET column(s) by default, and in order to include the NET in the testing you must change the settings according to How to Include the Main NET Column in Column Comparisons.

This rule will not show the appropriate high and low results if Show Redundant Tests is enabled in Statistical Assumptions at the table or project options level.

How to apply this rule

For the first time in a project

Select the table(s)/chart(s) that you wish to apply the rule to.
Start typing the name of the Rule into the Search features and data box in the top right of the Q window.
Click on the Rule when it appears in the QScripts and Rules section of the search results.

OR

Select Automate > Browse Online Library.
Choose this rule from the list.

Additional applications of the rule

Select a table or chart that has the rule and any table(s)/chart(s) that you wish to apply the rule to.
Click on the Rules tab (bottom-left of the table/chart).
Select the rule that you wish to apply.
Click on the Apply drop-down and choose your desired option.
Check New items to have it automatically applied to new items that you create. Use Edit > Project Options > Save as Template to create a new project template that automatically uses this rule.

Removing the rule

Select the table(s)/chart(s) that you wish to remove the rule from.
Press the Rules tab (bottom-right corner).
Press Apply next to the rule you wish to remove and choose the appropriate option.

How to modify the rule

Click on the Rules tab (bottom-left of the table/chart).
Select the rule that you wish to modify.
Click Edit Rule and make the desired changes. Alternatively, you can use the JavaScript below to make your own rule (see Customizing Rules).

JavaScript

includeWeb("Table JavaScript Utility Functions");
includeWeb("JavaScript Array Functions");
includeWeb("JavaScript Utilities");
 
excludeRTables();

if (table.availableStatistics.indexOf("Column Names") == -1)
    form.ruleNotApplicable("column comparisons not available on this table");

// table.hypothesisTestingEnabled = false; // alpha button disabled

// Setting up the form
form.setHeading("Color Comparisons with a Particular Column");
let description = form.newLabel("Highlight cells using the Column Comparisons results instead of displaying that comaprison with letters.");
description.lineBreakAfter = true;
let column_choice_label = form.newLabel("Highlight cells significantly higher/lower than:");
let column_name_box = form.newTextBox("cn");
column_name_box.setDefault("NET");
column_name_box.lineBreakAfter = true;
let high_label = form.newLabel("Higher results:");
let high_color_picker = form.newColorPicker("hcolor");
high_color_picker.setDefault("Yellow");
high_color_picker.lineBreakAfter = true;
let low_label = form.newLabel("Lower results:");
let low_color_picker = form.newColorPicker("lcolor");
low_color_picker.setDefault("Orange");
low_color_picker.lineBreakAfter = true;
form.setInputControls([description, column_choice_label, column_name_box, high_label, high_color_picker, low_label, low_color_picker]);


let target_label = column_name_box.getValue();
form.setSummary("Color cells which are significant compared with: " + target_label);
let high_color = high_color_picker.getValue();
let low_color = low_color_picker.getValue();


// Get the column spans at the lowest level

let lowest_col_spans = getLowestLevelSpans(table.columnSpans)

function getLowestLevelSpans(spans) {
    let lowest_spans = [];
    let found_indices = [];
    spans.forEach(function (span) {
        if (span.indices.every(function (x) { return found_indices.indexOf(x) == -1; })) {
            found_indices = found_indices.concat(span.indices);
            lowest_spans.push(span);
        }
    });
    return lowest_spans;
}

if (lowest_col_spans.length == 0) {
    let span_indices = [];
    for (let j = 0; j < table.numberColumns; j++)
        span_indices.push(j);
    lowest_col_spans = [{label: "the columns", indices: span_indices}];
}
    
// _HIGHLIGHTED_BELOW_STATS 

let do_below = false;
let below_colors, below_transposed, below_column_comparisons;
if (belowTableExists()) {
    do_below = below_table.availableStatistics.indexOf("Column Comparisons") > -1 && intersection(below_table.statistics, _HIGHLIGHTED_BELOW_STATS).length > 0;    
    if (do_below) {
        below_column_comparisons = below_table.get("Column Comparisons");
        below_colors = below_table.cellColors;
        below_transposed = below_column_comparisons.length > 1;
    }
                                                       
}

// Loop over each span and make the necessary modifications
let cell_colors = table.cellColors;
let column_letters = table.get("Column Names");
let column_comparisons = table.get("Column Comparisons");
let column_labels = table.columnLabels;
lowest_col_spans.forEach(function (span) {
    let span_labels = span.indices.map(function (x) { return column_labels[x]; });
    let matching_labels = span_labels.filter(function (label) { return label == target_label; });
    if (matching_labels.length > 1)
        form.ruleNotApplicable("column " + target_label + " appears more than once in " + span.label);
    
    // Unique match, do the modifications
    if (matching_labels.length == 1) {
        let target_index = span.indices[span_labels.indexOf(target_label)];
        let target_letter = column_letters[0][target_index].toLowerCase();
        span.indices.forEach(function (col) {
            if (col != target_index) {
                for (let row = -1; row < table.numberRows; row++) {
                    // Current result is high
                    
                    if (row == -1) {
                        if (do_below) {
                            let current_r = below_transposed ? col : 0;
                            let current_c = below_transposed ? 0 : col;
                            let target_r = below_transposed ? target_index : 0;
                            let target_c = below_transposed ? 0 : target_index;
                            if (below_column_comparisons[current_r][current_c].toLowerCase().indexOf(target_letter) > -1) {
                                below_colors[current_r][current_c] = high_color;
                                let replace_regexp = new RegExp(target_letter, 'i')
                                below_column_comparisons[current_r][current_c] = below_column_comparisons[current_r][current_c].replace(replace_regexp, "");
                            } else if (below_column_comparisons[target_r][target_c].toLowerCase().indexOf(column_letters[0][col].toLowerCase()) > -1) {
                                below_colors[current_r][current_c] = low_color;    
                            }
                        }      
                    } else {
                        if (column_comparisons[row][col].toLowerCase().indexOf(target_letter) > -1) {
                            cell_colors[row][col] = high_color;
                            let replace_regexp = new RegExp(target_letter, 'i')
                            column_comparisons[row][col] = column_comparisons[row][col].replace(replace_regexp, "");
                        } else if (column_comparisons[row][target_index].toLowerCase().indexOf(column_letters[row][col].toLowerCase()) > -1) {
                            cell_colors[row][col] = low_color;
                        }                        
                    }
                    

                }
            }
        })
        // Blank out comparisons in target column
        for (let row = 0; row < table.numberRows; row++) {
            column_comparisons[row][target_index] = "";
        }
        if (do_below){
             below_column_comparisons[below_transposed ? target_index : 0][below_transposed ? 0 : target_index] = "";
        }
    }
});


table.cellColors = cell_colors;
table.set("Column Comparisons", column_comparisons);
if (do_below) {
    below_table.cellColors = below_colors;
    below_table.set("Column Comparisons", below_column_comparisons);
}