Calculation - Multiply - Table(s)
Jump to navigation
Jump to search
This page is currently under construction, or it refers to features which are under development and not yet available for use.
This page is under construction. Its contents are only visible to developers!
This tool multiplies the numbers in one table or output by those in another, or to multiply by a single number.
Examples
The first table shows the number of Google and Microsoft shares someone owns. The second table shows the stock price for both companies over the last four days.
Using Multiply produces a new table which contains the value of the shares over the last four days.
Options
Multiply the Choose whether you wish the first term of the multiplication to be an output containing one or more numbers, or a single value. When Output is selected, you can choose an output in your document. When Single numeric value is selected you can then type in the desired value.
by the Choose whether you wish the second term of the multiplication to be an output containing one or more numbers, or a single value. When Output is selected, you can choose an output in your document. When Single numeric value is selected you can then type in the desired value.
Automatically match elements Only shown when there are multiple inputs to Input. This controls how and whether matching is done between the labels of the inputs. The default, "Yes - hide unmatched", will look for matching labels in the rows and columns of the inputs before proceeding with the calculation, and any rows/columns that are not contained in all the inputs will not be included in the output. See the Example. For a full description of the matching algorithm, see the Technical Details. "Yes - show unmatched" will also perform matching, but any unmatched rows (columns) will appear in the output as rows (columns) of all missing values. Selecting "No" for this option will cause any labels in the data to be ignored and not perform any matching. Selecting "Custom" will bring up two additional controls that allow for specifying the matching behavior for rows and columns separately.
Match rows Only shown if Automatically match elements is set to "Custom". Specifies the matching behavior when comparing row labels of the inputs. "Yes - show unmatched" and "Yes - hide unmatched" look for exact matches in the row labels in the inputs. "Fuzzy - show unmatched" and "Fuzzy - hide unmatched" perform fuzzy matching so that labels that differ only by a single character are considered to be a match.
Match columns Only shown if Automatically match elements is set to "Custom". The options are the same as Match rows, but control the matching between columns.
Rows to exclude Here you can type in row labels that should be excluded from the calculation.
Columns to exclude As above, but for columns.
Code
const UNCHECK_NAMES = ["SUM", "NET", "TOTAL"];
const MULTI_QUESTIONTYPES = ["Text - Multi",
"Pick One - Multi", "Pick Any - Compact",
"Pick Any - Grid", "Number - Grid"];
const ALLOWED_R_CLASSES = ["numeric", "integer", "logical", "factor", "matrix", "array", "data.frame", "table"];
function getInputNames(input, dim = 0)
{
var input_names;
var listbox_names = {};
let input_type = input.type;
if (input_type === "R Output")
{
try
{
var output_class = input.outputClasses;
if (output_class.includes("array") || output_class.includes("matrix"))
{
var dimnames = input.data.getAttribute([], "dimnames");
if (dim < dimnames.length && dimnames[dim] != null)
input_names = dimnames[dim];
else
input_names = [];
} else if (output_class.includes("data.frame"))
{
if (dim === 1)
input_names = input.data.getAttribute([], "names");
else
{
let row_names = input.data.getAttribute([], "row.names");
input_names = typeof(row_names[0]) === "string" ? row_names : [];
}
} else
{
input_names = dim === 0 ? input.data.getAttribute([], "names") : [];
}
}
catch(e)
{
input_names = [];
}
listbox_names["names"] = input_names;
listbox_names["initial"] = filterSingleNames(input_names);
} else {
let primary_type = input.primary.variableSetStructure;
let has_multi_or_grid = primary_type.endsWith("Multi") || primary_type.endsWith("Grid");
let has_columns = !!input.secondary || has_multi_or_grid || input.cellStatistics.length > 1;
listbox_names = {names: ["foo"], initial: has_columns ? ["bar"] : []};//getTableDimNames(input, dim);
}
// DS-3147: replace newline chars/any whitespace with single space
if (listbox_names["names"].length > 0)
{
Object.keys(listbox_names).map(key => {
listbox_names[key] = listbox_names[key].map(str => typeof(str) === "string" ? str.replace(/\s+/g, " ") : str);
});
}
return listbox_names;
}
function getTableDimNames(table, dim)
{
let has_primary = table.primary != null;
let table_output_names = {"names": [], "initial": []};
if (has_primary)
{
let table_output = table.calculateOutput();
let is_crosstab_or_multi_or_raw = table.secondary.type === "Question"
|| MULTI_QUESTIONTYPES.includes(table.primary.questionType)
|| table.secondary === "RAW DATA";
if (table.primary.isBanner && table.secondary === "SUMMARY")
is_crosstab_or_multi_or_raw = false;
if (dim === 0)
{
let row_names = table_output.rowLabels;
let row_spans = table_output.rowSpans;
let row_indices = table_output.rowIndices(include_nets_sums = false);
if (row_spans.length > 1)
{
table_output_names = flattenSpanNames(row_names, row_spans);
} else
{
let initial = !!row_indices ? row_names.filter((name, i) => row_indices.includes(i)) : filterSingleNames(row_names);
table_output_names = {"names": row_names, "initial": initial};
}
}
if (dim === 1)
{
let n_columns = table_output.numberColumns;
let col_spans = n_columns < 2 ? [] : table_output.columnSpans;
let col_indices = table_output.columnIndices(include_nets_sums = false);
let col_names = [];
if (col_spans.length > 1)
{
col_names = table_output.columnLabels;
table_output_names = flattenSpanNames(col_names, col_spans);
} else
{
col_names = is_crosstab_or_multi_or_raw ? table_output.columnLabels : table_output.statistics;
let initial = !!col_indices ? col_names.filter((name, i) => col_indices.includes(i)) : filterSingleNames(col_names);
table_output_names = {"names": col_names, "initial": initial};
}
}
}
return table_output_names;
}
function filterSingleNames(names)
{
return names.filter(n => !UNCHECK_NAMES.includes(n));
}
function flattenSpanNames(labels, span_names)
{
let span_length = span_names.length;
let span_labels = labels;
let unselect_labels = span_names.filter(span => UNCHECK_NAMES.includes(span["label"]));
let unselect_span_indices = [];
if(unselect_labels.length > 0)
{
unselect_span_indices = unselect_labels.map(unselect => unselect["indices"]);
unselect_span_indices = [].concat.apply([], unselect_span_indices);
unselect_span_indices = uniq(unselect_span_indices);
}
let unselected_base_indices = labels.map((l, i) => UNCHECK_NAMES.includes(l) ? i : "").filter(Number);
let unselected_indices = [].concat.apply([], [unselect_span_indices, unselected_base_indices]);
unselected_indices = uniq(unselected_indices)
labels.forEach((item, i) => {
for (j = 0; j < span_length; j++)
{
let curr_span = span_names[j];
if (curr_span["indices"].includes(i))
{
span_labels[i] = span_names[j]["label"] + " - " + span_labels[i];
}
}
});
let initial_values = span_labels.filter((label, i) => !unselected_indices.includes(i));
return {"names": span_labels, "initial": initial_values};
}
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 uniq(a)
{
var seen = {};
return a.filter(function(item) {
return seen.hasOwnProperty(item) ? false : (seen[item] = true);
});
}
function addListBoxAfterProcessingNames(all_listbox_names, dim, guid)
{
if (all_listbox_names.length === 1)
{
return addListBox(all_listbox_names[0], dim, guid);
} else
{
let keys = Object.keys(all_listbox_names[0]);
let final_listbox_names = {};
keys.forEach(key => {
let names = all_listbox_names.map(names => names[key]);
names = [].concat.apply([], names);
final_listbox_names[key] = uniq(names);
})
return addListBox(final_listbox_names, dim,guid);
}
}
let input_structure = {"names": ["Multiplicand", "Multiplier"],
"labels": ["Multiply the", "by the"]};
let input_guids = [];
for (let i = 0; i < 2; i++)
{
let name = input_structure["names"][i];
let label = input_structure["labels"][i];
let combo_control = form.comboBox({name: "formCombo" + name, label: label,
alternatives: ["Output", "Single numeric value"],
default_value: "Output",
prompt: "Choose an output on the page or specify a single value"});
if (combo_control.getValue() === "Single numeric value")
{
form.textBox({name: "formSingle" + name, label: "", type: "number",
default_value: 1,
error: "The " + name + " here cannot be empty and must be a single numeric value",
prompt: "The single value to be used in the calculation"});
} else
{
let data_input_control = form.dropBox({name: "form" + name, label: "",
types: ["table", "RItem: " + ALLOWED_R_CLASSES.join(", ")],
multi: false,
error: "Please input data such as a table, vector or matrix",
prompt: "Input data such as a table or R vector or matrix"}).getValue();
if (data_input_control != null)
input_guids.push(data_input_control.guid);
}
}
let n_inputs = input_guids.length;
if (n_inputs > 0)
{
let row_names = [];
let col_names = [];
let inputs = input_guids.map(guid => recursiveGetItemByGuid(project.report, guid));
row_names = inputs.map(input => getInputNames(input, 0));
col_names = inputs.map(input => getInputNames(input, 1));
row_names = row_names.filter(item => item["names"].length > 0);
col_names = col_names.filter(item => item["names"].length > 0);
let add_row_listbox = row_names.length > 0;
let add_col_listbox = col_names.length > 0;
let add_matching_control = row_names.length > 1 || col_names.length > 1;
if (add_matching_control)
{
var automatic_choice = form.comboBox({label: "Automatically match elements",
name: "formMatchElements",
alternatives : ["Yes - hide unmatched",
"Yes - show unmatched",
"No",
"Custom"],
default_value: "Yes - hide unmatched",
prompt: "Automatically determine elements to match via row and column names"});
automatic_choice = automatic_choice.getValue();
if (automatic_choice === "Custom")
{
let has_both_row_names = row_names.every(item => item["names"].length > 0);
let has_both_col_names = col_names.every(item => item["names"].length > 0);
form.comboBox({name: "formMatchRows",
label: "Match rows",
alternatives: ["Yes - hide unmatched", "Yes - show unmatched", "Fuzzy - hide unmatched", "Fuzzy - show unmatched", "No"],
default_value: has_both_row_names ? "Yes - hide unmatched" : "No",
prompt: "Specify the matching behavior across the rows"});
form.comboBox({name: "formMatchColumns",
label: "Match columns",
alternatives: ["Yes - hide unmatched", "Yes - show unmatched", "Fuzzy - hide unmatched", "Fuzzy - show unmatched", "No"],
default_value: has_both_col_names ? "Yes - hide unmatched" : "No",
prompt: "Specify the matching behavior across the columns"});
}
}
form.textBox({name: "formIncludeRows", label: "Rows to exclude", prompt: "Select the row labels to be excluded in the output table.", default_value: "NET; SUM", required: false});
form.textBox({name: "formIncludeColumns", label: "Columns to exclude", prompt: "Select the columns labels to be excluded in the output table.", default_value: "NET; SUM", required: false});
}
form.setHeading("Multiply");
library(verbs)
multiplicand <- if (formComboMultiplicand == "Single numeric value") as.numeric(formSingleMultiplicand) else formMultiplicand
multiplier <- if (formComboMultiplier == "Single numeric value") as.numeric(formSingleMultiplier) else formMultiplier
formInputs <- list(multiplicand, multiplier)
match.elements <- get0("formMatchElements", ifnotfound = c(rows = "No", columns = "No"))
if (length(match.elements) == 1L && match.elements == "Custom")
match.elements <- c(rows = formMatchRows, columns = formMatchColumns)
row.ctrl.name <- ls(pattern = "^formIncludeRows")
col.ctrl.name <- ls(pattern = "^formIncludeColumns")
include.rows <- if (length(row.ctrl.name))
get(row.ctrl.name)
include.cols <- if (length(col.ctrl.name))
get(col.ctrl.name)
sep <- ifelse(grepl(";", include.rows), ";", ",")
remove.rows <- trimws(strsplit(include.rows, sep)[[1]])
sep <- ifelse(grepl(";", include.cols), ";", ",")
remove.columns <- trimws(strsplit(include.cols, sep)[[1]])
inputs <- formInputs
inputs <- verbs:::checkInputsAtMost2DOrQTable(inputs)
all.rownames <- unlist(lapply(inputs, function(input)
{
if (!is.null(dimnames(input)))
dimnames(input)[[1]]
else
names(input)
}))
## Handle edge where user wants to remove one label and it contains a comma
if (trimws(include.rows) %in% all.rownames)
remove.rows <- trimws(include.rows)
all.colnames <- unlist(lapply(inputs, function(x) if (length(dim(x)) > 1) dimnames(x)[[2]]))
if (trimws(include.cols) %in% all.colnames)
remove.columns <- trimws(include.cols)
unmatched.rows <- which(!remove.rows %in% all.rownames)
unmatched.cols <- which(!remove.columns %in% all.colnames)
any.row.spans <- any(vapply(formInputs, function(x) NCOL(attr(x, "span")$rows) > 1, logical(1)))
## Don't warn if user hasn't changed from defaults, unless table has been flattened
if (length(unmatched.rows) > 0 && (any.row.spans || !"NET; SUM" == include.rows))
{
bad.labels <- paste0("'", paste(remove.rows[unmatched.rows], collapse = "', '"), "'")
msg <- ngettext(length(unmatched.rows),
paste0("The following row label requested to be excluded was ",
"not found in the input data: ", bad.labels, "."),
paste0("The following row labels specified to be excluded were ",
"not found in the input data: ", bad.labels, "."))
good.labels <- all.rownames[1:min(3, length(all.rownames))]
msg <- paste0(msg, " Please supply labels such as '", paste(good.labels, collapse = "', '"), "'.")
warning(msg)
}
any.col.spans <- any(vapply(formInputs, function(x) NCOL(attr(x, "span")$columns) > 1, logical(1)))
## Don't warn if user hasn't changed from defaults, unless table has been flattened
if (!is.null(all.colnames) && length(unmatched.cols) > 0 && (any.col.spans || !"NET; SUM" == include.cols))
{
bad.labels <- paste0("'", paste(remove.columns[unmatched.cols], collapse = "', '"), "'")
msg <- ngettext(length(unmatched.rows),
paste0("The following column label requested to be excluded was ",
"not found in the input data: ", bad.labels, "."),
paste0("The following column labels specified to be excluded were ",
"not found in the input data: ", bad.labels, "."))
good.labels <- all.colnames[1:min(3, length(all.colnames))]
msg <- paste0(msg, " Please supply labels such as '", paste(good.labels, collapse = "', '"), "'.")
warning(msg)
}
multiplied.output <- Multiply(multiplicand, multiplier,
remove.rows = remove.rows,
remove.columns = remove.columns,
match.elements = match.elements,
warn = TRUE)