Tables - Table of Differences

From Q
Jump to: navigation, search

Compare two tables and by performing t-Tests on each cell with the corresponding cell in the other table. Signficant values can be displayed visually using different shading options.

Example

Options

Table 1 and Table 2 are the input tables to be compared. They are expected to have the same row and column names and the same primary statistic. The primary statistic can be one of "Average", "%", "Total %", "Column %" or "Row %". They must also have an appropriate cell statistics for Sample Size and Standard Error for the primary statistic used. For example when the primary statistic is "Column %", the input tables must also contain "Column n" and "Column Standard Error".

Show This option controls which values are shown in the output table. It does not affect the test statistic or p-values. It can be one of

Differences between Table 2 - Table 1.
Primary statistic of Table 2 with differences not shown but reflected by the shading options.
Primary statistic of Table 2 with differences with separate formatting controls for the primary statistic and the difference values.

Show significant values by shading in controls which element is shaded to reflect the p-values from the t-test performed on the two tables.

None No shading is applied
Cell colors Shading is applied to the cell fill color
Arrows Shading is applied to up/down arrows next to the cell value
Boxes Shading is applied to a box drawn around the cell text. Additional controls are associated with this option. A separate control for the border color is provided for each significance level and the border width, corner roundness, and padding around the box can be adjusted. Note that the padding is specified in terms of pixels, so if the size of the output does not provide have enough space to accommodate the box including its padding, the box will be truncated.

Number of significance levels controls the number of thresholds (and corresponding shades) are applied to the table.

Threshold Controls which cells are shaded. The threshold is compared against the p-values from the independent t-Tests.

Rows/Columns to ignore A comma-separated list of row/column names which should not be shown in the output table.

Font family The font family for all text in the table

Font size The font size of all text in the table.

Font units The units in which the font size is specified. This can be either "px" or "pt".

Number of decimals shown Separate controls are shown for the primary statistic and difference.

Prefix/Suffix Optional text to prepend or append to the primary statistic/difference.

Font family/color/size of primary statistic Font controls for the primary statistic and difference (separately). These will default font controls, but where they differ these controls to allow different fonts to be shown in different setting.

Automatically determine the font color Text will be automatically colored black or white depending on the background color. It takes into account the cell fill color, and conditional shading (both in cell color and box).

Show +/- sign on differences Always prepend differences with ‘+’ or ‘-‘.

Show legend Whether of nor to show a legend explaining the shaded cells/boxes/arrows in the footer of the table.

Show column/row headers Whether to show column/row headers for the table. In some cases, whether the Table of Differences is shown next to other visual elements, information about the row/column names may already be present.

Show borders around row/collumn headers By default, borders are only placed around the table cells, but they can be extended to include the header/row headers.

Row height automatically fills R output This is the default option. Users can adjust the spacing by dragging and resizing the output. Note, however, that if the table has many rows, then it may be better to set it to a fixed row height.

Row height The height of a row in the table (with no word wrap). This option is only shown if row height does not automatically fill R output. This value will be specified in terms of font units. By default it is 5 + font size. If there are too many rows to show, a scrollbar will be shown.

Column widths A column separated list of values (including units, e.g. "px", "pt", "%") specifying the widths of the column. Each value will be a single column starting from the left. The remaining columns with no specified widths will be equally sized to fill the remaining space. Note that the first value will be applied to the row headers (if they are shown).

Column header fill The color of the column header cells.

Row header fill The color of the row header cells containing the names of the profiling variables.

Cell fill The color of the cells (excluding row/column headers) and cells colored by conditional formatting.

Collapse borders Whether the borders of adjacent cells should be collapsed into a single line. This is default, but there is also the option to not collapse borders, in which case the ‘’’gap between rows’’’ or ‘’’gap between columns’’’ can be manually adjusted.

Border color The color of the border.

Border width The width of the border in pixels.

Code

form.setHeading("Table of Differences");

var showAlt = ["Differences", "Primary statistic of Table 2", "Primary statistic of Table 2 with differences"]
form.dropBox({label: "Table 1", name: "formTable1", types: ["Table"]});
form.dropBox({label: "Table 2", name: "formTable2", types: ["Table"]});
var valuesShown = form.comboBox({label: "Show", name: "formShowVal", alternatives: showAlt, default_value: showAlt[0], prompt: "Select which values to show in the cells of the output table."}).getValue();


form.group({label:"Significant Values", expanded: true})
var condShade = form.comboBox({name: "formCondShadeType", label: "Show significant differences by shading in", alternatives: ["None", "Cell colors", "Boxes", "Arrows"], default_value: "Cell colors", prompt: "Select whether cells or elements inside the cell should be shaded to highlight the magnitude of the cell value"});
if (condShade.getValue() != "None")
    var numLevels = form.numericUpDown({label: "Number of significance levels", name: "formNumLevels", default_value: 2}).getValue();

function componentToHex(c) { var hex = Math.floor(c).toString(16); return hex.length == 1 ? "0" + hex : hex; }
function rgbToHex(x) { return "#" + componentToHex(x[0]) + componentToHex(x[1]) + componentToHex(x[2]); }



if (condShade.getValue() != "None")
{
    for (var i = 1; i <= numLevels; i++)
    {
        var alpha = form.numericUpDown({name: "formCondCutoff" + i, label: "Signficance level "+i, default_value: i * 0.025, increment: 0.001, prompt: "A t-Test will be computed to compare the values in Table 2 to the corresponding values in Table 1. Cells will be shaded if the p-value from the t-Test is less than signficance level " + i + "."}).getValue();

        var sc = 255/(numLevels + 1);
        var cU = [i * sc, i * sc, 255];
        var cL = [255, i * sc, i * sc];
        var colU = form.colorPicker({name: "formCondShadeUB" + i, label: "Fill color for increased values in Table 2", prompt: "Cells will be shaded in this color when values in Table 2 is significantly greater than Table 1 at the " + (1-alpha)*100 + "% confidence level", default_value: rgbToHex(cU)}).getValue();
        if (condShade.getValue() == "Boxes")
            form.colorPicker({name: "formCondShadeUBBorder" + i, label: "Border color for increase values in Table 2", prompt: "Box borders will be shaded in this color when values in Table 2 is significantly greater than Table 1 at the " +  (1-alpha)*100 + "% confidence level", default_value: colU});

        var colL = form.colorPicker({name: "formCondShadeLB" + i, label: "Fill color for decreased values in Table 2", prompt: "Cells will be shaded in this color when values in Table 2 is significantly less than Table 1 at the " + (1-alpha)*100 + "% confidence level", default_value: rgbToHex(cL)}).getValue();
        if (condShade.getValue() == "Boxes")
            form.colorPicker({name: "formCondShadeLBBorder" + i, label: "Border color for decreased values in Table 2", prompt: "Box borders will be shaded in this color when values in Table 2 is significantly less than Table 1 at the " +  (1-alpha)*100 + "% confidence level", default_value: colL});

    }
}

if (condShade.getValue() == "Boxes")
{
    form.numericUpDown({name: "formCondBoxWidth", label: "Box border width", default_value: 2});
    form.numericUpDown({name: "formCondBoxRadius", label: "Box corner roundness", default_value: 0, maximum: 50, prompt: "Increase value to get rounder corner; setting to 50 gives ovals"});
    form.numericUpDown({name: "formCondBoxPaddingTop", label: "Box top padding", default_value: 5, prompt: "Space between edge of box and text in pixels"});
    form.numericUpDown({name: "formCondBoxPaddingBottom", label: "Box bottom padding", default_value: 5, prompt: "Space between edge of box and text in pixels"});
    form.numericUpDown({name: "formCondBoxPaddingLeft", label: "Box left padding", default_value: 5, prompt: "Space between edge of box and text in pixels"});
    form.numericUpDown({name: "formCondBoxPaddingRight", label: "Box right padding", default_value: 5, prompt: "Space between edge of box and text in pixels"});
}

form.textBox({label: "Rows to ignore", type: "text", default_value: "NET, Total, SUM", name: "formIgnoreRows", required: false, prompt: "Specify rows to hide as a comma seperated list of row names"});
form.textBox({label: "Columns to ignore", type: "text", default_value: "NET, Total, SUM", name: "formIgnoreColumns", required: false, prompt: "Specify columns to hide as a comma seperated list of column names"});


form.page("Format");
form.group("Font");
var fontFamilies = font_families = ["Arial", "Arial Black", "Century Gothic", "Comic Sans MS",
                 "Courier New", "Georgia", "Impact", "Open Sans", "Tahoma", "Times New Roman", "Trebuchet MS", "Verdana"];
var fontFamily = form.comboBox({name: "formFontFamily", label: "Font family", default_value: "Arial", alternatives: fontFamilies}).getValue();
var fontSize = form.numericUpDown({name: "formFontSize", label: "Font size", default_value: 10}).getValue();
form.comboBox({name: "formFontUnits", label: "Font units", alternatives: ["pt", "px"], default_value: "pt"});
var fontColor = form.colorPicker({name: "formFontColor", label: "Font color", default_value: "#2C2C2C"}).getValue();
v

form.group("Cell text");
if (valuesShown == showAlt[1] || valuesShown == showAlt[2]) 
{
    form.numericUpDown({label: "Number of decimals shown in primary statistic", name: "formPStatDecimals", default_value: 0});
    form.textBox({label: "Prefix", name: "formPStatPrefix", required: false, prompt: "Optional text to prepend to the primary statistic"});
    form.textBox({label: "Suffix", name: "formPStatSuffix", required: false, prompt: "Optional text to append to the primary statistic"});
    form.comboBox({name: "formPStatFontFamily", label: "Font family of primary statistic", alternatives: fontFamilies, default_value: fontFamily});
    form.numericUpDown({name: "formPStatFontSize", label: "Font size of primary statistic", default_value: fontSize});
    var pstatAutoFontColor = form.checkBox({label: "Automatically determine font color", name: "formPStatFontAutocolor", default_value: true, prompt: "Use black or white text depending on the cell fill color or box fill color"}).getValue();
    if (!pstatAutoFontColor)
        form.colorPicker({name: "formPStatFontColor", label: "Font color of primary statistic", default_value: fontColor});
}

if (valuesShown == showAlt[0] || valuesShown == showAlt[2]) 
{
    var prefix_prompt = "";
    var prefix_default = "";
    if (valuesShown == showAlt[2])
    {
        prefix_prompt = " (default is a single space)";
        prefix_default = " ";
    }

    form.numericUpDown({label: "Number of decimals shown in differences", name: "formDiffDecimals", default_value: 0});
    form.textBox({label: "Prefix", name: "formDiffPrefix", required: false, default_value: prefix_default, prompt: "Optional text to prepend to the differences" + prefix_prompt});
    form.textBox({label: "Suffix", name: "formDiffSuffix", required: false, prompt: "Optional text to append to the differences"});
    form.checkBox({label: "Show +/- sign on differences", name: "formDiffSign", default_value: true});
    form.comboBox({name: "formDiffFontFamily", label: "Font family of differences", alternatives: fontFamilies, default_value: fontFamily});
    form.numericUpDown({name: "formDiffFontSize", label: "Font size of differences", default_value: fontSize});
    var diffAutoFontColor = form.checkBox({label: "Automatically determine font color", name: "formDiffFontAutocolor", default_value: true, prompt: "Use black or white text depending on the cell fill color or box fill color"}).getValue();
    if (!diffAutoFontColor)
        form.colorPicker({name: "formDiffFontColor", label: "Font color of differences", default_value: fontColor});
}

form.group("Legend");
var showLegend = form.checkBox({name: "formLegendShow", label: "Show legend", default_value: true}).getValue();
if (showLegend)
{
    form.comboBox({name: "formLegendFontFamily", label: "Font family", alternatives: fontFamilies, default_value: fontFamily});
    form.numericUpDown({name: "formLegendFontSize", label: "Font size", default_value: 8});
    form.colorPicker({name: "formLegendFontColor", label: "Font color", default_value: fontColor});

}

form.group("Column Headers");
var showColumnHeaders = form.checkBox({name: "formColumnHeadersShow", label: "Show column headers", default_value: true}).getValue();
if (showColumnHeaders)
{
    form.checkBox({name: "formColumnHeadersBorder", label: "Show border around column headers", default_value: false});
    form.comboBox({name: "formColumnHeadersFontWeight", label: "Font weight", alternatives: ["Normal", "Bold"], default_value: "Normal"});
    form.comboBox({name: "formColumnHeadersFontFamily", label: "Font family", alternatives: fontFamilies, default_value: fontFamily});
    form.numericUpDown({name: "formColumnHeadersFontSize", label: "Font size", default_value: fontSize});
    form.colorPicker({name: "formColumnHeadersFontColor", label: "Font color", default_value: fontColor});
}

form.group("Row Headers");
var showRowHeaders = form.checkBox({name: "formRowHeadersShow", label: "Show row headers", default_value: true}).getValue();
if (showRowHeaders)
{
    form.checkBox({name: "formRowHeadersBorder", label: "Show border around row headers", default_value: false});
    var rheadAlign = form.comboBox({name: "formRowHeadersAlignHoriz", label: "Alignment", alternatives: ["Left", "Center", "Right"], default_value: "Right"}).getValue();
    if (rheadAlign != "Center")
        form.numericUpDown({name: "formRowHeadersPad", label: "Padding", default_value: 5, prompt: "Space (in pixels) between the row header and the edge to the cell"});
    form.comboBox({name: "formRowHeadersFontWeight", label: "Font weight", alternatives: ["Normal", "Bold"], default_value: "Normal"});
    form.comboBox({name: "formRowHeadersFontFamily", label: "Font family", alternatives: fontFamilies, default_value: fontFamily});
    form.numericUpDown({name: "formRowHeadersFontSize", label: "Font size", default_value: fontSize});
    form.colorPicker({name: "formRowHeadersFontColor", label: "Font color", default_value: fontColor});
}


form.group("Spacing");
var autoRowHeight = form.checkBox({name: "formRowHeightAuto", label: "Row height automatically fills R output", default_value: true}).getValue();
if (!autoRowHeight)
    form.numericUpDown({name: "formRowHeight", label: "Row height", default_value: fontSize + 5});
form.textBox({name: "formColumnWidths", label: "Column widths", required: false, prompt: "Comma separated values, e.g. '40px, 25%' or leave blank for equal widths"});

form.group("Fill color");
if (showColumnHeaders)
    form.colorPicker({name: "formColHeadFill", label: "Column header fill", default_value: "#FFFFFF"});
if (showRowHeaders)
    form.colorPicker({name: "formRowHeadFill", label: "Row header fill", default_value: "#FFFFFF"});
form.colorPicker({name: "formCellFill", label: "Cell fill", default_value: "#FFFFFF"});


form.group("Border");
var borderCollapse = form.checkBox({name: "formBorderCollapse", label: "Collapse borders", default_value: true}).getValue();
if (!borderCollapse)
{
    form.numericUpDown({name: "formBorderGapRows", label: "Gap between rows", default_value: 2});
    form.numericUpDown({name: "formBorderGapColumns", label: "Gap between columns", default_value: 2});
}
form.colorPicker({name: "formBorderColor", label: "Border color", default_value: "#FFFFFF"});
form.numericUpDown({name: "formBorderWidth", label: "Border width", default_value: 1});
library(flipAnalysisOfVariance)
if (!exists("formNumLevels"))
    formNumLevels <- 1

cutoffs <- rep(NA, formNumLevels)
colors.ub <- rep(NA, formNumLevels)
colors.lb <- rep(NA, formNumLevels)
colors.ub.border <- rep(NA, formNumLevels)
colors.lb.border <- rep(NA, formNumLevels)
for (i in 1:formNumLevels)
{
    cutoffs[i] <- get0(paste0("formCondCutoff", i), ifnotfound = NA)
    colors.ub[i] <- get0(paste0("formCondShadeUB", i), ifnotfound = NA)
    colors.lb[i] <- get0(paste0("formCondShadeLB", i), ifnotfound = NA)
    colors.lb.border[i] <- get0(paste0("formCondShadeLBBorder", i), ifnotfound = NA)
    colors.ub.border[i] <- get0(paste0("formCondShadeUBBorder", i), ifnotfound = NA)
}

table.of.diffs <- TableOfDifferences(formTable1, formTable2,       
                  show = formShowVal,
                  cond.shade = formCondShadeType,
                  cond.shade.cutoffs = cutoffs,
                  cond.shade.ub.colors = colors.ub,
                  cond.shade.lb.colors = colors.lb,
                  cond.shade.ub.bordercolors = colors.ub.border,
                  cond.shade.lb.bordercolors = colors.lb.border,
                  cond.box.radius = get0("formCondBoxRadius"),
                  cond.box.borderwidth = get0("formCondBoxWidth"),
                  cond.box.padding.top = get0("formCondBoxPaddingTop"),
                  cond.box.padding.bottom = get0("formCondBoxPaddingBottom"),
                  cond.box.padding.left = get0("formCondBoxPaddingLeft"),
                  cond.box.padding.right = get0("formCondBoxPaddingRight"),
                  cell.fill = formCellFill,
                  font.color = formFontColor,
                  font.size = formFontSize,
                  font.unit = formFontUnits,
                  font.family = formFontFamily,
                  legend.show = formLegendShow,
                  legend.font.family = get0("formLegendFontFamily"),
                  legend.font.color = get0("formLegendFontColor"),
                  legend.font.size = get0("formLegendFontSize"),
                  format.statistic.decimals = get0("formPStatDecimals"),
                  format.statistic.prefix = get0("formPStatPrefix"),
                  format.statistic.suffix = get0("formPStatSuffix"),
                  format.statistic.font.family = get0("formPStatFontFamily"),
                  format.statistic.font.color = get0("formPStatFontColor"),
                  format.statistic.font.autocolor = get0("formPStatFontAutocolor"),
                  format.statistic.font.size = get0("formPStatFontSize"),
                  format.difference.sign = get0("formDiffSign"),
                  format.difference.decimals = get0("formDiffDecimals"),
                  format.difference.prefix = get0("formDiffPrefix"),
                  format.difference.suffix = get0("formDiffSuffix"),
                  format.difference.font.family = get0("formDiffFontFamily"),
                  format.difference.font.color = get0("formDiffFontColor"),
                  format.difference.font.autocolor = get0("formDiffFontAutocolor"),
                  format.difference.font.size = get0("formDiffFontSize"),
                  show.row.header = formRowHeadersShow,
                  row.header.fill = get0("formRowHeadFill"),
                  row.header.align.horizontal = get0("formRowHeadersAlignHoriz"),
                  row.header.pad = get0("formRowHeadersPad", ifnotfound = 0),
                  row.header.font.family = get0("formRowHeadersFontFamily"),
                  row.header.font.color = get0("formRowHeadersFontColor"),
                  row.header.font.size = get0("formRowHeadersFontSize"),
                  row.header.font.weight = get0("formRowHeadersFontWeight"), 
                  show.col.header = formColumnHeadersShow,
                  col.header.fill = get0("formColHeadFill"), 
                  col.header.font.family = get0("formColumnHeadersFontFamily"),
                  col.header.font.color = get0("formColumnHeadersFontColor"),
                  col.header.font.size = get0("formColumnHeadersFontSize"),
                  col.header.font.weight = get0("formColumnHeadersFontWeight"),
                  col.widths = formColumnWidths,
                  row.height = get0("formRowHeight"),
                  border.color = formBorderColor,
                  border.width = formBorderWidth,
                  col.header.border.width = if (isTRUE(get0("formColumnHeadersBorder"))) formBorderWidth else 0,
                  row.header.border.width = if (isTRUE(get0("formRowHeadersBorder"))) formBorderWidth else 0,
                  border.collapse = formBorderCollapse,
                  border.row.gap = get0("formBorderGapRows"),
                  border.column.gap = get0("formBorderGapColumns"),
                  row.names.to.remove = formIgnoreRows,
                  column.names.to.remove = formIgnoreColumns)