Tables - Combine Tables

From Q
Jump to: navigation, search

Combine two or more tables by merging along either their rows or columns

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

  1. 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).
  2. Run the feature from the menu: Create > Tables > Combine TablesAnything > Table > Combine Tables.
  3. 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.

An example of a merged table with three input tables.

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.

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\")\n" +
    "form.dropBox({\n" +
    "    name: \"tablesinput\",\n" +
    "    label: \"Tables\",\n" +
    "    types: [\"table\", \"RItem:matrix,array,data.frame,table\"],\n" +
    "    multi: true\n" +
    "})\n" +
    "form.comboBox({\n" +
    "    name: \"joindirinput\",\n" +
    "    label: \"Join direction\",\n" +
    "    default_value: \"" + join_direction +"\",\n" +
    "    alternatives: [\"Side-by-side\", \"Up-and-down\"]\n" +
    "})\n" +
    "form.comboBox({\n" +
    "    name: \"nonmatchinginput\",\n" +
    "    label: \"Non-matching rows/columns\",\n" +
    "    alternatives: [\"Keep all\", \"Matching only\"],\n" +
    "    default_value: \"Keep all\",\n" +
    "    required: false\n" +
    "})";
}

function rCodeAsString(out_name,selections, join_dir) {
    var rcode = "library(flipTables)\n\n";
    rcode += out_name + " <- MergeTables(tablesinput, direction = joindirinput, nonmatching = nonmatchinginput)";
    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 {
         var r_attr = 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'];
    var 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")) {
            var 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){
    var rownames1 = getTableNames(table1, 0);
    var rownames2 = getTableNames(table2, 0);
    var colnames1 = getTableNames(table1, 1);
    var colnames2 = getTableNames(table2, 1);
    if (rownames1 !== null && rownames2 !== null)
        var row_matches = rownames1.filter(r => rownames2.indexOf(r) > -1).length;
    else
        var row_matches = 0;
    if (colnames1 !== null && colnames2 !== null)
        var col_matches = colnames1.filter(r => colnames2.indexOf(r) > -1).length;
    else
        var col_matches = 0;
    if (col_matches === 0 && row_matches === 0)
        return -1;
    else if (col_matches > row_matches)
        return "Up-and-down";
    else
        return "Side-by-side";
}

function combineTables() {
    includeWeb("QScript R Output Functions");  // for generateUniqueRObjectName()

    const VALID_ROUTPUT_CLASSES = ["matrix","array","data.frame","table"];
    let selections = project.report.selectedRaw();
    if (project.report.selectedRaw().length === 0) {
        log("Please select Table(s) and/or R Output(s) containing the tables to combine.");
        return false;
    }
    
    let valid_selections = selections.filter(s => (s.type === "Table" && s.question !== null)
          || (s.type === "R Output" && s.error === null
              && s.outputClasses.filter(c => VALID_ROUTPUT_CLASSES.includes(c)).length > 0));
    if (valid_selections.length === 0) {
        log("Please select Table(s) and/or R Output(s) containing the tables to be combined.");    
        return false;
    }else
        var group = valid_selections[0].group;

    let allowed_types = ["R Output", "Table"];
    if (valid_selections.length < 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('merged');
    let join_dir = -1;
    let idx = 0;
    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("tablesinput",guids.join(";"));
    merged_output.update();
    
    project.report.setSelectedRaw([merged_output]);
    return true;
}

if (!combineTables()) {
    log("QScript cancelled.")            
}

See also