Tables - Combine Tables

This QScript combines two or more tables by matching either row or column labels. The tables should be selected before you begin, and whether to splice/merge along columns or rows will be determined based on which dimension has the most common labels shared between the tables. The join direction and the tables used can additionally be changed via GUI controls in the Object Inspector after the QScript is run.

Note that the output table is an R Output and will not perform the significance testing included with other tables.

Usage

Select any tables on your page that you would like to combine (hold the Ctrl key when clicking on each table with the mouse to select more than one).
Run the feature from the menu: Create > Tables > Combine TablesAnything > Table > Combine Tables.
If necessary, adjust any of the Inputs listed below via the Object Inspector of the created R Output.

Example

The following example involves combining three tables, each containing a single categorical variable and a single statistic, %. The tables are named table.Q1b.Awareness, table.Q4.BrandAttitude, and table.Q3.Preferred.cola. The output after using this feature is shown below.

Options

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

Inputs

Tables: The tables to merge.

Join direction: Whether to join the tables by side-by-side (must have some rows in common) or up-and-down (must have some columns in common).

Handle non-matching rows/columns: The tables that you are merging might have row (or column) names that do not match. This option allows you to specify how to handle the non-matches.

Keep all: Keep all rows from the tables. Rows that do not match the other tables will be blank.

Matching only: Keep only the rows that are common between the tables.

Row names: If you wish to override the names of the rows in the new table you can type new names here, separating them with commas. These names will be applied to the rows, beginning at the top.

Column names: If you wish to override the names of the columns in the new table you can type new names here, separating them with commas. These names will be applied to the columns, beginning with the leftmost column.

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.

How to apply this QScript

Start typing the name of the QScript into the Search features and data box in the top right of the Q window.
Click on the QScript when it appears in the QScripts and Rules section of the search results.

OR

Select Automate > Browse Online Library.
Select this QScript from the list.

Customizing the QScript

This QScript is written in JavaScript and can be customized by copying and modifying the JavaScript.

Customizing QScripts in Q4.11 and more recent versions

Start typing the name of the QScript into the Search features and data box in the top right of the Q window.
Hover your mouse over the QScript when it appears in the QScripts and Rules section of the search results.
Press Edit a Copy (bottom-left corner of the preview).
Modify the JavaScript (see QScripts for more detail on this).
Either:
- Run the QScript, by pressing the blue triangle button.
- Save the QScript and run it at a later time, using Automate > Run QScript (Macro) from File.

Customizing QScripts in older versions

Copy the JavaScript shown on this page.
Create a new text file, giving it a file extension of .QScript. See here for more information about how to do this.
Modify the JavaScript (see QScripts for more detail on this).
Run the file using Automate > Run QScript (Macro) from File.

JavaScript

function guiControlsAsString(join_direction){
   return `
form.setHeading('Combine Tables');
form.dropBox({name: 'formTables',
              label: 'Tables',
              types: ['table', 'RItem:matrix,array,data.frame,table,integer,factor,logical,numeric,character'],
              multi: true});
form.comboBox({name: 'formJoinDir', label: 'Join direction',
               default_value: '${join_direction}',
               alternatives: ['Side-by-side', 'Up-and-down']});
form.comboBox({name: 'formNonMatching', label: 'Non-matching rows/columns',
               alternatives: ['Keep all', 'Matching only'],
               default_value: 'Keep all', required: false});
form.textBox({name: 'formRowNames',
              label: 'Row names',
              prompt: 'Enter the row names to use for each table, separated by commas.',
              required: false});
form.textBox({name: 'formColNames',
              label: 'Column names',
              prompt: 'Enter the column names to use for each table, separated by commas.',
              required: false});
`;
}

function rCodeAsString(out_name) {
    let rcode = 'library(flipTables)\n\n';
    let out_leng = ' '.repeat(out_name.length);
    rcode = `
library(flipTables)


${out_name} <- MergeTables(formTables, direction = formJoinDir,
${out_leng}                nonmatching = formNonMatching,
${out_leng}                override.row.names = formRowNames,
${out_leng}                override.column.names = formColNames)
`;
    return rcode;
}

// Filter out R Outputs that don't have dim attribute, i.e.
// aren't matrix, table, array, data.frame.
// The R code flipTables::Merge2Tables() relies on
// the inputs having a dim attribute
// It is possible to replace this with outputClasses property
// of QScript R Outputs
function routputHasAttr(s, attr) {
   if (s.type === 'R Output'){
     try {
         s.data.getAttribute([], attr);
         return true;
     }catch(e){
         return false;
     }
   }
   return false;
}

// dim = 0 for rownames
//     = 1 for colnames
function getTableNames(table, dim = 0){
    const MULTI_QUESTIONTYPES = ['Text - Multi', 'Number - Multi', 'Pick Any',
                                 'Pick One - Multi', 'Pick Any - Compact',
                                 'Pick Any - Grid', 'Number - Grid'];
    let tnames;
    if (table.type === 'R Output'){
        if (table.error === null && table.outputClasses.includes('data.frame') && dim === 1)
            tnames = table.data.getAttribute([], 'names');
        else if  (routputHasAttr(table, 'dimnames')) {
            let dimnames = table.data.getAttribute([], 'dimnames');
            if (dim < dimnames.length) {
                tnames =  dimnames[dim];
            }else
                tnames = null;
        }else
            tnames = null;
    }else {
        let tout = table.calculateOutput();
        let is_crosstab_or_multi = table.secondary.type === 'Question' || MULTI_QUESTIONTYPES.includes(table.primary.questionType);
        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 !== null) {
        tnames = tnames.map(str => str.replace(/\s/g, ' '));
    }
    return tnames;
}

// Determine whether tables should be merged by
// rows (Side-by-side) or columns (Up-and-down)
//
// If both tables have row names and column names,
// join direction is determined based on which dimension has
// the most agreement in the names on that direction
// returns: "Side-by-side" if more row names match,
// "Up-and-down" if more column names match,
// and -1 if there are no matching names in either the rows or columns (or
// there are no names to compare; e.g. 1D tables)
function determineJoinDirection(table1, table2){
    let rownames1 = getTableNames(table1, 0);
    let rownames2 = getTableNames(table2, 0);
    let colnames1 = getTableNames(table1, 1);
    let colnames2 = getTableNames(table2, 1);
    let row_matches = 0;
    if (rownames1 !== null && rownames2 !== null)
        row_matches = rownames1.filter(r => rownames2.indexOf(r) > -1).length;
    let col_matches = 0;
    if (colnames1 !== null && colnames2 !== null)
        col_matches = colnames1.filter(r => colnames2.indexOf(r) > -1).length;
    if (col_matches === 0 && row_matches === 0)
        return -1;
    if (col_matches > row_matches)
        return 'Up-and-down';
    return 'Side-by-side';
}

includeWeb('QScript Selection Functions'); // for getAllUserSelections()
includeWeb('QScript R Output Functions');  // for generateUniqueRObjectName()
includeWeb('JavaScript Array Functions');  // for splitArrayIntoApplicableAndNotApplicable()

let raw_selections = project.report.selectedRaw();
let selections = getAllUserSelections();
selections = [].concat(selections.selected_tables, selections.selected_r_outputs);
let sorted_selections = splitArrayIntoApplicableAndNotApplicable(selections, validTableOrTableLikeROutput);
let valid_selections = sorted_selections.applicable;
if  (project.report.selectedRaw().length === 0 || valid_selections.length === 0)
    log('Please select valid Table(s) and/or R Output(s) containing the tables to combine.');
else {
    let group = valid_selections[0].group;

    if (valid_selections.length < raw_selections.length)
        log('Some selections were not a Q Table or an R Output (containing a table) and they will be ignored.');

    let output_name = generateUniqueRObjectName('combined.tables');
    let join_dir = -1;
    try {
        if (valid_selections.length === 1)
            join_dir = 'Side-by-side';
        else {
            for (let i = 0; i < valid_selections.length - 1 && join_dir === -1; i++)
                for (let j = i + 1; j < valid_selections.length && join_dir === -1; j++)
                    join_dir = determineJoinDirection(valid_selections[i],valid_selections[j]);

            if (join_dir === -1)
                join_dir = 'Side-by-side';
        }

        let r_expression = rCodeAsString(output_name,valid_selections);

        let guids = valid_selections.map(s => s.guid);

        let merged_output = group.appendR(r_expression);
        merged_output.setCodeForGuiControls(guiControlsAsString(join_dir));
        merged_output.setGuiControlInputRaw('formTables',guids.join(';'));
        merged_output.update();
        project.report.setSelectedRaw([merged_output]);
    } catch(e)
    {
        log("Could not combine tables: " + e);
    }
}