Tables - Select Rows and Columns from Table

From Q
Jump to: navigation, search

Select a subset of rows and columns to show from tables. Select from rows or columns of one or more tables via Object Inspector (GUI) controls. This Standard R feature can be used to easily select (remove) rows and columns from tables. Selection can be performed using check box controls in the Object Inspector or by supplying an R Output.

Example

Consider the following table, a crosstab involving three Nominal variables showing work status by age and gender. Notice that the columns of the table contain a Banner.

Table.Q31.Work.status.by.BANNER.png

After supplying the table to Tables in page(s) and unselecting the checkboxes for "Retired", "Student", "Less than 18 year's old", and "30 to 39 year's old" in the Object Inspector, we obtain the following output table.

Table - Select- output table example.png

Options

The options in the Object Inspector are organized into two tabs: Inputs and Properties.

Inputs

Each selected table is given its own group TABLE 1, TABLE 2, etc. which contain the GUI controls for selecting from the table. When a table is added to the last group, a new group will automatically be created so additional tables can be supplied. Each group contains the following controls.

Tables in page(s): The table to select from.

Select row(s) by: The method to use to select the rows of the table to include in the output, with the following options.

  • Selecting options below - When this option is chosen, a checkbox control will appear for each label in the rows of the table. Uncheck the box beside a particular label to remove it from the output.
  • Control or output from Page(s) - If this method is chosen, a drop-down Control to select row(s) appears, which can be used to supply an R Output or control containing the row labels to include.
  • First rows - If this method is chosen, a numeric control Number of rows to select appears, which specifies the number of rows from the first to include; e.g., specify 3 to show only the first three rows of the table.
  • Last rows - Similar to "First rows", except for selecting rows from the bottom of the table.
  • Range - Use this option to specify a numeric range of indices to include. When selected, a text box Range appears for specifying the row numbers to include. Ranges are specified with a hyphen and multiple ranges can be separated by a comma; e.g., "2-5,8,12-15".
  • First date-time periods - This method can we used when the row labels of the table contain dates to select for example the first 3 years of dates in the table or the first two quarters from the first date in the table. Three additional controls appear in this case: 1) Period specifies the date-time unit/period to consider, e.g. "Year", "Month", "Quarter", etc., 2) First date-time periods to select specifies the number of periods from the first date in the table to include, and 3) Calendar - If this is checked then the date-time periods selected will be relative to the calendar; e.g., the first quarter is always January - March. If unchecked, then date-time periods are counted relative to the first date in the table.
  • Last date-time periods - Similar to "First date-time periods", except for selecting dates in relation to the last date in the table.
  • Date range - If this option is selected, two controls appear Start date and End date, and only rows with dates falling between the chosen start and end dates will appear in the output table.

If there are columns in the table, then equivalent controls to above also appear for columns; e.g., Specify columns to select using, Number of columns to select, etc.

Additionally, when multiple tables have been added, the following GUI controls appear in a group TABLE SELECTION.

Table names: The names to use for the tables.

Toggle between tables using: A drop-down for supplying an output to use to toggle between the tables. The item names in the control should match Table names.

Properties

This tab contains options for formatting the size of the object, as well as the underlying R code used to create the output table, and the JavaScript code use to customize the Object Inspector itself (see Object Inspector for more details about these options). Additional options are available by editing the code.

Code

var allow_control_groups = Q.fileFormatVersion() > 10.9;
var displayr = Q.isOnTheWeb();
// any table names appearing in UNCHECK_NAMES default to not being selected
const UNCHECK_NAMES = ["SUM", "NET", "TOTAL"];
const MULTI_QUESTIONTYPES = ['Text - Multi', 'Number - Multi', 'Pick Any',
			     'Pick One - Multi', 'Pick Any - Compact',
			     'Pick Any - Grid', 'Number - Grid'];
// restict max number of checkboxes so GUI doesn't become slow for large tables
const MAX_CHECKBOXES_PER_DIM = 200;

function isEmpty(x) { return (x == undefined || x.getValue() == null &&
			      (x.getValues() == null ||
			       x.getValues().length == 0)) }

function recursiveGetItemByGuid(group_item, guid) {
    var cur_sub_items = group_item.subItems;
    for (var j = 0; j < cur_sub_items.length; j++)
    {
        if (cur_sub_items[j].type == 'ReportGroup') {
            var res = recursiveGetItemByGuid(cur_sub_items[j], guid);
            if (res != null)
                return(res)
        }
        else if (cur_sub_items[j].guid == guid)
            return(cur_sub_items[j]);
    }
    return null;
}

function routputHasAttr(s, attr) {
   if (s.type === "R Output"){
     try {
         var r_attr = s.data.getAttribute([], attr);
         return true;
     }catch(e){
         return false;
     }
   }
   return false;
}

function getTableNames(tbl, dim = 0){
    var tnames;
    if (tbl.type === "R Output") {
        try {
            if (tbl.outputClasses.includes("data.frame") && dim === 1)
                tnames = tbl.data.getAttribute([], "names");
            else {
                var dimnames = tbl.data.getAttribute([], "dimnames");
                if (dim < dimnames.length)
                    tnames = dimnames[dim];
                else
                    tnames = [];
            }
        }catch(e) {
            tnames = [];
        }
    } else {
        let tout = tbl.calculateOutput();
        let is_crosstab_or_multi = tbl.secondary.type === "Question" || MULTI_QUESTIONTYPES.includes(tbl.primary.questionType);
        if (tbl.primary.isBanner && tbl.secondary === "SUMMARY")
            is_crosstab_or_multi = false;
        if (dim === 0)
            tnames = tout.rowLabels;
        else if (dim === 1 && is_crosstab_or_multi)  // get column labels from a crosstab
            tnames = tout.columnLabels;
        else
            tnames = tout.statistics;
    }
    // DS-3147: replace newline chars/any whitespace with single space
    if (tnames.length > 0) {
        tnames = tnames.map(str => str.replace(/\s+/g, " "));
    }
    return tnames;
}

function addSelectorControls(controls,table_data,table_num,dim){
    let names = getTableNames(table_data,dim);
    let dim_str = dim === 0 ? "rows" : "columns";
    let ctrl_dim = dim === 0 ? "Row" : "Col";
    let allow_checkbox_selection = names !== null &&
	names.length <= MAX_CHECKBOXES_PER_DIM;
    let selection_alternatives = ["Control or output from Page(s)",
			       "First " + dim_str,
			       "Last " + dim_str,
			       "Range",
			       "First date-time periods",
			       "Last date-time periods",
			       "Date range"];
    if (allow_checkbox_selection)
	selection_alternatives.unshift("Selecting options below");

    let ctrl_name = "form" + ctrl_dim + "SelectorTbl" + table_num;
    let ctrl_label = "Select " + dim_str + " by";
    let ctrl_prompt = "Choose how " + dim_str + " are to be selected";
    var selection = form.comboBox({label: ctrl_label,
			 alternatives: selection_alternatives,
			 name: ctrl_name,
			 default_value: selection_alternatives[0],
			 prompt: ctrl_prompt});
    controls.push(selection);
    switch (selection.getValue()) {
	case "Selecting options below":
	    controls = addCheckBoxes(controls,table_data,dim,table_num);
	    break;
	case "Control or output from Page(s)":
	    ctrl_name = "form" + ctrl_dim + "SelectionsTbl" + table_num;
	    ctrl_prompt = ("Provide a control or R Output specifying " +
		       dim_str + " to select.");
	    let selector = form.dropBox({label: "Control to select " + dim_str,
					name: ctrl_name,
		     prompt: ctrl_prompt, 
		     types: ["Control", "RItem"],
		     multi: false, required: true});  
	    controls.push(selector);
	    break;
	case "First " + dim_str:
	    // ctrl_name = "formFirst" + ctrl_dim + "sTbl" + table_num;
        ctrl_name = "form" + ctrl_dim + "SelectionsTbl" + table_num;    
	    ctrl_label = "Number of " + dim_str + " to select";
	    ctrl_prompt = ctrl_label + ".";
	    let first_ctrl = form.numericUpDown({name: ctrl_name,
						 label: ctrl_label,
						 default_value: 1, minimum: 1,
						 maximum: 1000000,
			      prompt: ctrl_prompt});
	    controls.push(first_ctrl);
	    break;
	case "Last " + dim_str:
	    // ctrl_name = "formLast" + ctrl_dim + "sTbl" + table_num;
        ctrl_name = "form" + ctrl_dim + "SelectionsTbl" + table_num;    
	    ctrl_label = "Number of " + dim_str + " to select";
	    ctrl_prompt = ctrl_label + ".";
	    let last_ctrl = form.numericUpDown({name: ctrl_name,
					    label: ctrl_label,
					    default_value: 1, minimum: 1,
					    maximum: 1000000,
			      prompt: ctrl_prompt});
	    controls.push(last_ctrl);
	    break;
	case "Range":
	    // ctrl_name = "form" + ctrl_dim + "RangeTbl" + table_num;
        ctrl_name = "form" + ctrl_dim + "SelectionsTbl" + table_num;    
	    ctrl_prompt = "Enter numeric range for " + dim_str +
		"; e.g., 2-7, 9-10, 12";
	    var range = form.textBox({label: "Range", name: ctrl_name, 
			    default_value: "", type: "text",
			    prompt: ctrl_prompt});
	    controls.push(range);
	    break;
	case "First date-time periods":
	    addFirstLastDateControls(controls,true,table_num,dim);
	    break;
	case "Last date-time periods":
	    addFirstLastDateControls(controls,false,table_num,dim);
	    break;
	case "Date range":
            ctrl_name = "formStartDate" + ctrl_dim + "Tbl" + table_num;
	    var dp1 = form.datePicker({label: "Start date",
				       name: ctrl_name});
	    controls.push(dp1);
            ctrl_name = "formEndDate" + ctrl_dim + "Tbl" + table_num;
	    var dp2 = form.datePicker({label: "End date", 
				       name: ctrl_name});
	    controls.push(dp2);
	    break;
    }
    return;
}

function addCheckBoxes(controls,tbl,dim,table_num) {
    let cb;
    let checked;
    let names = getTableNames(tbl,dim);
    let dimname = (dim === 0 ? "Row" : "Col");
    let msg_dimname = (dim === 0 ? "row" : "column");
    let dim_has_span = false;
    if (tbl.type === "Table") {
	var tout = tbl.calculateOutput();
	dim_has_span = tableDimensionContainsSpan(tbl, tout, dim);
    }
    
    if (dim_has_span) {
	let dim_length = dim === 0 ? tout.numberRows : tout.numberColumns;

	let span_indices = removeVariableSetNamesFromSpan(tbl,dim);
	let remaining_spans = span_indices;
	let idx_not_in_span = seqIntArray(dim_length).filter(i => remaining_spans.map(l => l.indices).filter(idx => idx.includes(i)).length === 0);
	let filtered_names = names.filter((n,i) => idx_not_in_span.includes(i));
	let span_checkbox_counter = 0;
	let not_in_span_checkbox_counter = 0;
	let prev_checkbox_checked = true;
	for (let curr_idx = 0; curr_idx < dim_length; curr_idx++){
          if (idx_not_in_span.includes(curr_idx)){
              let name_idx = idx_not_in_span.shift();
              let checked = !UNCHECK_NAMES.includes(filtered_names[not_in_span_checkbox_counter]);
              cb = form.checkBox({name: "formInclude" + dimname + (name_idx + 1) + "Tbl" + table_num,
                            label: filtered_names[not_in_span_checkbox_counter],
                            default_value: checked});
              controls.push(cb);
              not_in_span_checkbox_counter++;
          }else {
              let current_spans = remaining_spans.filter(span => span.indices.includes(curr_idx));
              let all_current_spans = span_indices.filter(span => span.indices.includes(curr_idx));	      
              let n_span_levels = span_indices.filter(span => span.indices.includes(curr_idx)).length;
              let current_level = n_span_levels - current_spans.length;
              let prev_checkbox;
              // Unicode box drawing character to show span nesting; ┌, └, ├, ─, \u200b\xA0, │
              let box_drawing_char = current_level === 0 ? "" : "├─";  
              let space_chars = ("\u200b\xA0".repeat(4)).repeat(Math.max(0, current_level-1));
              let label_prefix = space_chars + box_drawing_char + " ";
              for (let s = current_spans.length - 1; s > -1; s--){
                  if (s === current_spans.length - 1 ||
                      prev_checkbox_checked){
                      prev_checkbox = form.checkBox({name: "formInclude" + dimname + "Span" + ++span_checkbox_counter + "Tbl" + table_num,
                              label: label_prefix + current_spans[s].label,
                              prompt: "Unselecting this will unselect all " + msg_dimname + "s in span.",
                              default_value: !UNCHECK_NAMES.includes(current_spans[s].label)});
                      controls.push(prev_checkbox);
                      prev_checkbox_checked = prev_checkbox.getValue();
                  }
                  box_drawing_char = "├─";
                  if (s < current_spans.length - 1 || current_level !== 0)
                      space_chars += "\u200b\xA0".repeat(4);
                  
                  label_prefix = space_chars + box_drawing_char + " ";
              }
              // add final 
              let cs = all_current_spans.shift();
              let cs_label = cs.label;
              let span_idx = cs.indices;
              if (prev_checkbox_checked) {
		  checked = !UNCHECK_NAMES.includes(names[curr_idx]);
		  let last_in_span = curr_idx === span_idx.pop() ;
		  box_drawing_char = last_in_span ? "└─" : "├─";
		  label_prefix = space_chars + box_drawing_char + " ";
		  prev_checkbox = form.checkBox({name: "formInclude" + dimname + (curr_idx+1) + "Tbl" + table_num,
				 label: label_prefix + names[curr_idx],
				 prompt: ["Unselect to remove ", msg_dimname, "'" + names[curr_idx] + "'"].join(" "),
				 default_value: checked});
		  controls.push(prev_checkbox);
	      }

              remaining_spans = remaining_spans.filter(span => !span.indices.includes(curr_idx));
          }
      }
    }else if (names.length > 0)
        controls = addListBox(controls, names,"formInclude" + dimname,
			      "Tbl" + table_num, dim);

   return controls;
}

function addFirstLastDateControls(controls,first,table_num,dim){
  let ctrl_dim = dim === 0 ? "Row" : "Col";
  let type_str = first ? "First" : "Last";
  let ctrl_name = "formDateTimePeriod" + ctrl_dim + "Tbl" + table_num;
    let dt_period = form.comboBox({name: ctrl_name,
	prompt: "Date-time period",
	label: "Date-time period",
	alternatives: ["Year", "Quarter", "Month", "Week",
		     "Day", "Hour", "Minute", "Second"],
		     default_value: "Year"});
    controls.push(dt_period);
    // ctrl_name = "formDateTime" + ctrl_dim + "Tbl" + table_num;
    ctrl_name = "form" + ctrl_dim + "SelectionsTbl" + table_num;
    let ctrl_label = type_str + " date-time periods to select";
    let ctrl_prompt = "Number of " + type_str.toLowerCase() +
		      " date-time periods to select.";
    let dt = form.numericUpDown({name: ctrl_name,
		      label: ctrl_label,
		      default_value: 1, minimum: 1, maximum: 1000000,
		      prompt: ctrl_prompt});
    controls.push(dt);
    ctrl_name = "formCalendar" + ctrl_dim + "Tbl" + table_num;
    let calendar = form.checkBox({label: "Calendar periods",
	 name: ctrl_name,
	 default_value: true,
	 prompt: "Whether to consider calendar periods"});
    controls.push(calendar);
    return;
}

function tableDimensionContainsSpan(tbl, tout, dim) {
    let dim_has_span = false;
    if ((dim === 0 && tout.rowSpans.length > 0) ||
	(dim === 1 && tbl.secondary !== null && tout.columnSpans.length > 0))
	dim_has_span = true;
    return dim_has_span;
}

function addListBox(controls, names, name_prefix, tbl_name, dim) {
    let dim_str = dim === 0 ? "Row" : "Column";
    let initial_values = names.filter(n => !UNCHECK_NAMES.includes(n));
    let lb = form.listBox({name: name_prefix + tbl_name,
                           label: dim_str + "s to include",
                           alternatives: names, names: names,
                           required: false,
                           prompt: "Select the " + dim_str.toLowerCase() + " labels to be included in the output table.",
                           initialValues: initial_values, multiSelection: true,
                          nItemsVisible: 5});
    controls.push(lb);
    return controls;
}

function seqIntArray(n) {
    let seq_dim_length = [];
    for (let i = 0; i < n; i++)
        seq_dim_length.push(i);
    return seq_dim_length;
}

// If the user selects "Add column spans of variable set names"
// when creating their BANNER, these labels need to be removed
// from the span as they don't provide useful info for selecting
// rows and columns
function removeVariableSetNamesFromSpan(tbl,dim) {
    let tout = tbl.calculateOutput();
    let span;
    if (dim === 0)
        span = tout.rowSpans;
    else if (dim === 1)
        span = tout.columnSpans;
    else
        throw new UserError("'dim' must be either 0 for row spans, or 1 for column spans");

    if (span.length === 0)
        return [];

    let span_varset_labels = getLabelsForVariablesInSpan(tbl);
    return span.filter(s => !span_varset_labels.includes(s.label));
}

function getLabelsForVariablesInSpan(tbl) {
    let questions_in_table = tbl.dependants(true).filter(d => d.type === "Question" && d.variables.length === 1);
    return questions_in_table.map(q => q.variables[0].label);
}


var controls = [];
var i = 1;
var table_i;
while (i === 1 || !isEmpty(table_i)) {
    if (allow_control_groups)
        form.group({label: "Table " + i, expanded: i === 1});
    table_i = form.dropBox({label: "Tables in Page(s)", name: "formTable" + i,
                     prompt: "Select tables to subset",
                     types: ["table"], // "RItem:matrix,array,data.frame,table"],
                     multi: false, required: i === 1});
    controls = controls.concat(table_i);
    if (!isEmpty(table_i)) {
        let table_data = recursiveGetItemByGuid(project.report, table_i.getValue().guid);
	addSelectorControls(controls,table_data,i,0);

        let colnames = getTableNames(table_data,1);
	if (colnames !== null && colnames.length > 1)
	    addSelectorControls(controls,table_data,i,1);
    }
    i++;
}

// If two or more table provided add controls to select between the tables
// i > 3 because if there are two tables a third (empty) table dropbox will
// be created and i goes to 4
if (i > 3) {
    if (allow_control_groups)
        form.group({label:"Table Selection", expanded:true});
    var table_names = form.textBox({name: "formTableNames", label: "Table names (default 1, 2, ...): ",
                                   prompt: "Enter the names to use to refer to each table, separated by commas.",
                                   required: false});
    controls.push(table_names);
    var table_selection = form.dropBox({label: "Toggle between tables using", name: "formTableSelection",
                     prompt: "Specify a Control or R Output to toggle between tables",
                     types: ["Control", "RItem"],
                     multi: false, required: false});
    controls.push(table_selection);
}

form.setInputControls(controls);
library(verbs)

## This function processes the GUI control inputs into the format
## required by verbs::SelectFromTable() (called at the bottom of this code)
TABLE.NAME.PREFIX = "formTable"
CHECK.BOX.PREFIX <- "formInclude"
TABLE.NUM.SEP <- "Tbl"
.geti <- function(ctrl.name, table.num, table.num.sep = TABLE.NUM.SEP, ...)
    get0(paste0(ctrl.name, table.num.sep, table.num), envir = .GlobalEnv, ...)


createTableList <- function(table.name.prefix = "formTable", table.names = NULL)
{

    .nTables <- function(table.name.prefix = "formTable")
        length(ls(pattern = paste0("^", table.name.prefix, "[0-9]+$"),
                  envir = .GlobalEnv)) - 1

    n.tables <- .nTables(table.name.prefix)
    out <- vector("list", n.tables)

    for (i in seq_len(n.tables)){
        vname <- paste0(table.name.prefix, i)
        if (exists(vname, envir = .GlobalEnv))
            out[[i]] <- get0(vname, envir = .GlobalEnv)
    }

    if (is.null(table.names) || !nzchar(table.names)){
        out.names <- seq_len(n.tables)
    }else
    {
        out.names <- flipU::ConvertCommaSeparatedStringToVector(table.names)

        if (length(out.names) != length(out))
            stop("The number of entries in ",
                 sQuote("Table names"), " (", length(out.names),
                 "), does not match ",
                 " the number of input tables (",
                 length(out), ").")
    }
    names(out) <- out.names
    return(out)
}

.checkboxSelectionsToVector <- function(table, table.num, table.name.sep = "Tbl",
                                       check.box.name.prefix = "formInclude",
                                       dim = 1)
{
    ## All checkboxes have names of the form:
    ##   formIncludeCol[Column Index]Tbl[Table Number]
    ##   OR formIncludeRow[Row Index]Tbl[Table Number]
    dimname <- ifelse(dim == 1, "Row", "Col")
    .calcNCheckBoxesInSpan <- function(table, dim)
    {
        if (dim == 1)
            return(NROW(table))
        else {
            if (NCOL(table) == 1L)
                return(0)
            else
                return(NCOL(table))
        }
    }

    n_checkboxes <- .calcNCheckBoxesInSpan(table, dim)
    out <- logical(n_checkboxes)
    for (j in seq_len(n_checkboxes))
        out[j] <- get0(paste0(check.box.name.prefix, dimname, j,
                              table.name.sep, table.num),
                       ifnotfound = FALSE, envir = .GlobalEnv)
    return(out)
}

tableToShow <- function(tables)
{
    n.tables <- length(tables)
    table.to.show <- 1
    table.selection <- get0("formTableSelection", ifnotfound = FALSE,
                            envir = .GlobalEnv)
    if (!isFALSE(table.selection) &&
        length(table.selection) == 1L){
        table.to.show <- which(names(tables) %in% table.selection)
        if (!length(table.to.show))
            stop("Table named specified by toggle/control not found.")
    }else if (n.tables > 1)
        warning("No valid Control or R Output provided in TABLE SELECTION ",
                "to select a table. Only the first table will be shown.")
    return(table.to.show)
}

selectionMode <- function(table.to.show, dim)
{
    ctrl.name <- paste0("form", ifelse(dim == 1, "Row", "Col"), "Selector")
    selector <- .geti(ctrl.name, table.to.show)
    if (is.null(selector))
        return("vector")
    return(switch(selector,
                  "Selecting options below" = "vector",
                  "Control or output from Page(s)" = "vector",
                  tolower(selector)))

}

selections <- function(table, table.to.show, dim)
{
    if (dim == 2 && NCOL(table) == 1L)
        return(NULL)
    dim.str <- switch(dim, "1" = "Row", "Col")
    ctrl.name <- paste0("form", dim.str, "Selector")
    selector <- .geti(ctrl.name, table.to.show)
    ctrl.name <- paste0("form", dim.str, "Selections")
    .formatDateRange <- function(table.to.show, dim)
        paste(.geti(paste0("formStartDate", dim), table.to.show),
              .geti(paste0("formEndDate", dim), table.to.show),
              sep = "--")

    cb.patt <- paste0("^", CHECK.BOX.PREFIX, dim.str, "[0-9]+", TABLE.NUM.SEP, "[0-9]+$")
    has.banner <- length(ls(pattern = cb.patt, envir = .GlobalEnv)) > 0

    selections <- switch(selector,
                  "Selecting options below" =
                         {
                             if (has.banner)
                               .checkboxSelectionsToVector(table,
                                table.to.show,
                                TABLE.NUM.SEP,
                                CHECK.BOX.PREFIX, dim = dim)
                             else
                                 .geti(paste0("formInclude", dim.str), table.to.show)
                         },
                  "Date range" = .formatDateRange(table.to.show, dim.str),
                  .geti(ctrl.name, table.to.show))
    return(selections)
}

unitInput <- function(table.to.show)
    c(.geti("formDateTimePeriodRow", table.to.show),
      .geti("formDateTimePeriodCol", table.to.show))

calendarInput <- function(table.to.show)
    c(.geti("formCalendarRow", table.to.show, ifnotfound = TRUE),
      .geti("formCalendarCol", table.to.show))


tables <- createTableList(TABLE.NAME.PREFIX, get0("formTableNames", .GlobalEnv))
n.tables <- length(tables)
table.to.show <- tableToShow(tables)

table <- tables[[table.to.show]]
table <- suppressWarnings(verbs::FlattenTableAndDropStatisticsIfNecessary(table))

selected.table <- SelectFromTable(table,
                        row.selection.mode = selectionMode(table.to.show, 1),
                        row.selections = selections(table, table.to.show, 1),
                        column.selection.mode = selectionMode(table.to.show, 2),
                        column.selections = selections(table, table.to.show, 2),
                        unit = unitInput(table.to.show),
                        calendar = calendarInput(table.to.show))