Tables - Splice Tables

From Q
Jump to: navigation, search
Related Videos
Part 4 of Creating Tables in Q (Video)
 


This QScript combine two tables into a new table by splicing either the rows or columns from the second table into the first. The two tables should be selected before you begin, and you will be given the option to splice in the rows or the columns.

Examples

By Columns (tables placed side-by-side)

In this example a SUMMARY of a Pick One question is spliced with a SUMMARY of a Pick Any question by columns. Note that some of the row labels are not common to both tables:

Splice.png

By Rows (tables placed on top of another)

In this example two Pick One - Multi SUMMARY tables have been spliced together by rows:

First table:

SpliceByRows1a.PNG

Second table:

SpliceByRows1b.PNG

Spliced table:

SpliceByRows1c.PNG

Technical details

When splicing by rows, the column labels from the two tables will be compared, and those columns of the second table that do not exist in the first table will be added as well. When splicing by columns, those rows from the second table that do not exist in the first table will also be added. Where a row or column label does not exist in one of the original tables the values for the statistics will be shown as NaN.

Filters and weights can be applied to the spliced table by making selections from the Filter and Weight drop-down menus below the table. The initial filter and weight will be taken from the first of the tables selected. If the two selected tables use questions from different data files then in order to apply filters or weights to the spliced table then there must be an appropriate Data File Relationship set up, and there must be filter or weight variables in both data files with matching Names and Labels. For more information on working with data from different files, see Multiple Data Files.

The new table is constructed by a Rule that is generated when the tables are spliced. The questions that are selected on the second table can be changed by selecting Rules > Splice Tables > Edit Rule. Information about the second table is added to the Footer.

When one or both of the tables shows a SUMMARY table with a single column then an extra question called All Respondents is added to the appropriate data file to facilitate the splicing.

The two selected tables must be compatible with one another in the sense that they must have the same available statistics. For example, you cannot splice a SUMMARY table of a Pick Any question with a SUMMARY table of a Number - Multi question, and you cannot splice a crosstab with a SUMMARY table of a Pick One - Multi question.

If splicing by columns then the two original tables must each have unique row labels. Duplicated labels prevent the matching of the rows between the two tables. Similarly, when splicing by rows, each of the input tables must each have unique column labels.

Significance results are not shown on the spliced table due to the nature of the changes, and the following statistics are not available on the spliced table:

Additionally, Statistics - Right and Statistics - Below are not available for spliced tables.

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

// Statistics which are not compatible with the spliced tables.
var _FORBIDDEN_STATS = ["% Column Share", "% Row Share", "% Share", "% Total Responses", "% Column Responses", "% Row Responses", "Index"];
 
// Function to generate a dummy Pick Any question that takes the value of 1 for each respondent.
// Using this question in place of "SUMMARY" allows for columns to be added to tables that
// would otherwise be one-dimensional (and hence could not have columns added).
var createDummyQuestionFor1DTable = function(data_file) {
    includeWeb('QScript Utility Functions')
    var existing_dummy = data_file.getVariablesByName("dummy_for_spliced_table")[0];
    if (existing_dummy != null)
        return existing_dummy.question;
    var new_dummy_question = data_file.newJavaScriptVariable("1", false, preventDuplicateVariableName(data_file, "dummy_for_spliced_table"), 
                                                            preventDuplicateQuestionName(data_file, "All Respondents"), null).question;
    new_dummy_question.questionType = "Pick Any";
    new_dummy_question.valueAttributes.setCountThisValue(1, true);
    return new_dummy_question;
};
 
 
var spliceIntoTable = function(second_blue, second_brown, by_rows) {
    // This function splices the table generated by the input parameters
    // with the current table.
 
    table.requireNumericTable();
    includeWeb('JavaScript Utilities');
    includeWeb('JavaScript Array Functions');
    includeWeb('Table JavaScript Utility Functions');
 
    // Suppress table when statistics - right or below shown
    var marginal_stats_message = "Statistics - Right and Statistics - Below are not available for this spliced table";
    suppressOutputForRightTable(marginal_stats_message);
    suppressOutputForBelowTable(marginal_stats_message);
 
    // Get a list of questions to display in the rule controls
    var blue_questions = [];
    project.dataFiles.forEach(function (data_file) {
        blue_questions = blue_questions.concat(data_file.questions.filter(function (q) {return !q.isHidden && q.questionType.indexOf("Text") == -1; }));
    });
    var blue_names = project.dataFiles.length == 1 ?
                         blue_questions.map(function (q) { return q.name; }) :
                         blue_questions.map(function (q) { return q.name + " [" + q.dataFile.name + "]"; });
 
    var brown_questions = [null].concat(blue_questions);
    var brown_names = ["SUMMARY"].concat(blue_names);
 
    var rule_name = "Splice tables";
    form.setSummary(rule_name);
    form.setHeading(rule_name);
 
    // Create controls
    var rows_columns_label = form.newLabel("Splice by:");
    var rows_columns_combobox = form.newComboBox("rowsorcolumns", ["Rows", "Columns"]);
    rows_columns_combobox.setDefault(by_rows ? "Rows" : "Columns");
    rows_columns_combobox.lineBreakAfter = true;
 
    var blue_combo_label = form.newLabel("Blue:");
    var blue_question_combobox = form.newComboBox("spliceblue", blue_names);
    blue_question_combobox.setDefault(blue_names.indexOf(second_blue) > -1 ? second_blue : blue_names[0]);
    blue_question_combobox.lineBreakAfter = true;
 
    var brown_combo_label = form.newLabel("Brown:");
    var brown_question_combobox = form.newComboBox("splicebrown", brown_names);
    brown_question_combobox.setDefault(brown_names.indexOf(second_brown) > -1 ? second_brown : "SUMMARY");
    brown_question_combobox.lineBreakAfter = true;
 
    var checkbox_footer = form.newCheckBox('footer', 'Show significance testing message in footer');
    checkbox_footer.setDefault(true);
 
    // Set controls
    var controls = [rows_columns_label, rows_columns_combobox, blue_combo_label, blue_question_combobox, brown_combo_label, brown_question_combobox, checkbox_footer];
    form.setInputControls(controls);
 
    // Get values from form
    var add_footer = checkbox_footer.getValue();
    second_blue = blue_question_combobox.getValue();
    var second_blue_question = blue_questions[blue_names.indexOf(second_blue)];
    second_brown = brown_question_combobox.getValue();
    var second_brown_question = brown_questions[brown_names.indexOf(second_brown)];
    by_rows = rows_columns_combobox.getValue() == "Rows";
 
    // Generate the second table to splice
    var second_table = calculateTable(second_blue, second_brown, ['!UseQFilters'], '!UseQWeight');
 
    // Check if dummy questions are required to splice these two tables
    // and regenerate the tables with the dummy question in the brown
    // drop-down if necessary.  
    var first_table_is_1D = table.columnLabels == null;
    var second_table_is_1D = second_table.columnLabels == null;
 
    if (first_table_is_1D) {
        var dummy_question1 = createDummyQuestionFor1DTable(table.blueQuestion.dataFile);
        table.suppressOutput("This table has a single column and cannot be spliced. Please choose the question called " 
                             + dummy_question1.name 
                             + (project.dataFiles.length == 1 ? "" : " [" + dummy_question1.dataFile.name + "]")
                             + " in the Brown drop-down.");
    }
    if (second_table_is_1D) {
        var dummy_question2 = createDummyQuestionFor1DTable(second_blue_question.dataFile);
        var temp_brown_string = dummy_question2.name + (project.dataFiles.length == 1 ? "" : " [" + dummy_question2.dataFile.name + "]");
        second_table = calculateTable(second_blue, temp_brown_string, ['!UseQFilters'], '!UseQWeight');
    }
 
    var table_1_stats = difference(table.availableStatistics, _FORBIDDEN_STATS);
    var table_2_stats = difference(second_table.availableStatistics, _FORBIDDEN_STATS);
    if (intersection(table_1_stats, table_2_stats).length != table_1_stats.length) {
        var primary_stat_1 = table.availableStatistics[0];
        var primary_stat_2 = second_table.availableStatistics[0];
        form.ruleNotApplicable("the first table has the \'" + primary_stat_1 + "\' and the second table has the \'" + primary_stat_2 + "\'. Tables with different sets of statistics cannot be combined");
    }
 
    // Calculate the base n range for the second table
    var base_n = second_table.get("Base n");
    var min_base = Number.POSITIVE_INFINITY;
    var max_base = 0;
    for (var row = 0; row < second_table.numberRows; row++)
        for (var col = 0; col < second_table.numberColumns; col++) {
            if (!isNaN(base_n[row][col])) {
                if (base_n[row][col] > max_base)
                    max_base = base_n[row][col];
                if (base_n[row][col] < min_base)
                    min_base = base_n[row][col];
            }
        }
    var base_n_string = "Second table base n = ";
    if (max_base.toString() == min_base.toString())
        base_n_string += max_base.toString();
    else
        base_n_string += "from " + min_base + " to " + max_base;
 
 
    // Enumerate the rows and columns of the original table
    var original_num_rows = table.numberRows;
    var original_num_cols = table.numberColumns;
    var original_row_indices = [];
    var original_col_indices = [];
    for (var j = 0; j < original_num_rows; j++)
        original_row_indices.push(j);
    for (var j = 0; j < original_num_cols; j++)
        original_col_indices.push(j);
 
    // Obtain the row or column labels for matching
    var labels1;
    var labels2;
    if (by_rows) {
        labels1 = table.columnLabels;
        labels2 = second_table.columnLabels;
    } else {
        labels1 = table.rowLabels;
        labels2 = second_table.rowLabels;
    }
 
    if (unique(labels1).length != labels1.length)
        form.ruleNotApplicable("the current table contains duplicate " + (by_rows ? "column" : "row") + " labels. Please adjust the labels so that they are unique");
    if (unique(labels2).length != labels2.length)
        form.ruleNotApplicable("the second table contains duplicate " + (by_rows ? "column" : "row") + " labels. Please adjust the labels so that they are unique");
 
 
    var common_labels = intersection(labels1, labels2);
    var missing_labels1 = labels1.filter(function (s) { return common_labels.indexOf(s) == -1; });
    var missing_labels2 = labels2.filter(function (s) { return common_labels.indexOf(s) == -1; });
 
    var selected_statistics = table.statistics;
 
    if (by_rows) {
        var num_new_cols = missing_labels2.length;
        var num_new_rows = second_table.numberRows;
 
        // Add new rows
        var last_row_index = table.numberRows - 1;
        var new_row_span_indices = [];
        for (var j = 0; j < num_new_rows; j++) {
            table.insertRowAfter(last_row_index, second_table.rowLabels[j]);
            last_row_index++;
            new_row_span_indices.push(j + original_num_rows);
        }
 
        // Copy row spans from the second table
        var second_spans = second_table.rowSpans;
        if (second_spans.length > 0) {
            second_spans.forEach(function (s) {
                var span_label = s.label;
                var new_indices = s.indices.map(function (x) {
                    return x + original_num_rows;
                });
                table.spanRows(new_indices, span_label);
            });
        }
        table.spanRows(original_row_indices, table.blue);
        table.spanRows(new_row_span_indices, second_blue);
 
        // Add new columns (one for each unmatched label from the second table)
        var last_col_index = table.numberColumns - 1;
        for (var j = 0; j < num_new_cols; j++) {
            table.insertColumnAfter(last_col_index, missing_labels2[j]);
            last_col_index++;
        }
 
        // Copy relevant values, adding NaN where the relevant cell does not exist in the second table
        var stat_tables = selected_statistics.map(function (stat) { return {stat: stat, destination: table.get(stat), source: second_table.get(stat)}; });
        for (var destination_col = 0; destination_col < table.numberColumns; destination_col++) {
            for (var source_row = 0; source_row < second_table.numberRows; source_row++) {
                var source_col = second_table.columnLabels.indexOf(table.columnLabels[destination_col]);
                var destination_row = source_row + original_num_rows;
                stat_tables.forEach(function(stat_ob) {
                    if (source_col > -1)
                        stat_ob.destination[destination_row][destination_col] = stat_ob.source[source_row][source_col];
                    else
                        stat_ob.destination[destination_row][destination_col] = _COLUMN_COMPARISON_STATS.indexOf(stat_ob.stat) > -1 ? "" : NaN;
                });
            }
        }
 
 
    } else {
        var num_new_rows = missing_labels2.length;
        var num_new_cols = second_table.numberColumns;
 
        // Add new columns
        var last_col_index = table.numberColumns - 1;
        var new_col_span_indices = [];
        for (var j = 0; j < num_new_cols; j++) {
            table.insertColumnAfter(last_col_index, second_table.columnLabels[j]);
            last_col_index++;
            new_col_span_indices.push(j + original_num_cols);
        }
 
        // Copy column spans from the second table
        var second_spans = second_table.columnSpans;
        if (second_spans.length > 0) {
            second_spans.forEach(function (s) {
                var span_label = s.label;
                var new_indices = s.indices.map(function (x) {
                    return x + original_num_cols;
                });
                table.spanColumns(new_indices, span_label);
            });
        }
 
        // Create New spans to indicate which table the columns are drawn from
        // Where there is only a single column, rename the column label instead
        // of creating a span. 
        if (original_col_indices.length == 1) {
            var labs = table.columnLabels;
            labs[0] = table.blue;
            table.columnLabels = labs;
        } else
            table.spanColumns(original_col_indices, table.blue + " by " + table.brown);
 
        if (new_col_span_indices.length == 1) {
            var labs = table.columnLabels;
            labs[original_num_cols] = second_blue;
            table.columnLabels = labs;
        } else
            table.spanColumns(new_col_span_indices, second_blue + " by " + second_brown);
 
        // Add new rows (one for each unmatched label from the second table)
        var last_row_index = table.numberRows - 1;
        for (var j = 0; j < num_new_rows; j++) {
            table.insertRowAfter(last_row_index, missing_labels2[j]);
            last_row_index++;
        }
 
        // Copy relevant values
        var stat_tables = selected_statistics.map(function (stat) { return {stat: stat, destination: table.get(stat), source: second_table.get(stat)}; });
        for (var source_col = 0; source_col < second_table.numberColumns; source_col++) {
            for (var destination_row = 0; destination_row < table.numberRows; destination_row++) {
                var destination_col = source_col + original_num_cols;   
                var source_row = second_table.rowLabels.indexOf(table.rowLabels[destination_row]);
                stat_tables.forEach(function(stat_ob) {
                    // If the row is matched in the second table then copy the value from the second table,
                    // otherwise set the value to NaN
                    if (source_row > -1)
                        stat_ob.destination[destination_row][destination_col] = stat_ob.source[source_row][source_col];
                    else
                        stat_ob.destination[destination_row][destination_col] = NaN;
                });
            }
        }
    }
 
    // Set the new statistics
    stat_tables.forEach(function (stat_ob) {
        table.set(stat_ob.stat, stat_ob.destination);
    });
 
    // Generate a message for the footer
    var footer = table.extraFooters;
    var second_table_string = "Additional cells spliced from the table: " + second_blue + " by " + second_brown;
 
    footer.push(base_n_string);
    footer.push(second_table_string);
    table.extraFooters = footer;
 
    // Prevent Stat Tesing
    preventAllSignificanceTesting(table, rule_name, add_footer);
 
    // Clear results that don't make sense or which are wrong
 
    _FORBIDDEN_STATS.forEach(function(stat) { 
        if (table.availableStatistics.indexOf(stat) > 0)
            setStatisticToNaN(stat);
    });
 
    // Move the NET/SUM row or column to the bottom or right
    if (by_rows) {
        table.netColumns.forEach(function (col, index) {
            table.moveColumnAfter(col - index, table.numberColumns - 1);
        });
    } else {
        table.netRows.forEach(function (row, index) {
            table.moveRowAfter(row - index, table.numberRows - 1);
        });
    }
};
 
 
 
 
 
try {
    var selected_tables = project.report.selectedItems();
    if (selected_tables.length != 2)
        throw new SetupError("Please select two tables before running the script.");
    if (selected_tables.filter(function (t) {return t.type != "Table"; }).length > 0)
        throw new SetupError("Please select table items only.")
    var by_rows = selectOne("Would you like to stack the tables on top of each other, or, have them side-by-side?", ["Stack on top", "Stack side-by-side"]) == 0;
    tableSpliceSetup(selected_tables[0], selected_tables[1], by_rows);
} catch (e) {
    if (e instanceof SetupError)
        log(e.message);
    else
        throw e;
}
 
 
 
// Generate a new spliced table from the input tables
function tableSpliceSetup(table1, table2, by_rows) {
 
    includeWeb('JavaScript Array Functions');
    includeWeb('QScript Utility Functions');
    includeWeb('JavaScript Utilities');
 
    var output1 = table1.calculateOutput();
    var output2 = table2.calculateOutput();
 
    // If splicing 1D tables create new dummy questions to enable
    // the addition of columns. (Columns can't be added to 1D tables).
 
    var table1_is_1D = output1.columnLabels == null;
    var table2_is_1D = output2.columnLabels == null;
    if (table1_is_1D && table2_is_1D) {
        var dummy1 = createDummyQuestionFor1DTable(table1.primary.dataFile);
        table1.secondary = dummy1;
        if (table1.primary.dataFile == table2.primary.dataFile)
            table2.secondary = dummy1;
        else {
            var dummy2 = createDummyQuestionFor1DTable(table2.primary.dataFile);
            table2.secondary = dummy2;  
        }
        output1 = table1.calculateOutput();
        output2 = table2.calculateOutput();
    } else if (table1_is_1D) {
        var dummy1 = createDummyQuestionFor1DTable(table1.primary.dataFile);
        table1.secondary = dummy1;
        output1 = table1.calculateOutput();
    } else if (table2_is_1D) {
        var dummy2 = createDummyQuestionFor1DTable(table2.primary.dataFile);
        table2.secondary = dummy2;
        output2 = table2.calculateOutput();
    }
 
    // Check that the two tables are compatible.
    var table_1_stats = difference(output1.availableStatistics, _FORBIDDEN_STATS);
    var table_2_stats = difference(output2.availableStatistics, _FORBIDDEN_STATS);
    if (intersection(table_1_stats, table_2_stats).length != table_1_stats.length) {
        log(output1.availableStatistics)
        log(output2.availableStatistics)
        if (table1_is_1D)
            table1.secondary = "SUMMARY";
        if (table2_is_1D)
            table2.secondary = "SUMMARY";
        var primary_stat_1 = output1.availableStatistics[0];
        var primary_stat_2 = output2.availableStatistics[0];
        throw new SetupError("The first table has the \'" + primary_stat_1 + "\' and the second table has the \'" + primary_stat_2 + "\'. Tables with different sets of statistics cannot be combined.");
    }
 
    // Determine the uniqueness of the labels in the table.
    // Unique labels are required to match the rows/columns between the two tables.
    var labels1;
    var labels2;
    if (by_rows) {
        labels1 = output1.columnLabels;
        labels2 = output2.columnLabels;
    } else {
        labels1 = output1.rowLabels;
        labels2 = output2.rowLabels;
    }
    if (unique(labels1).length != labels1.length) {
        if (table1_is_1D)
            table1.secondary = "SUMMARY";
        if (table2_is_1D)
            table2.secondary = "SUMMARY";
        throw new SetupError("The table " + table1.name + " contains duplicate " + (by_rows ? "column" : "row") + " labels.\r\n\r\nPlease select tables with no duplicated labels.");
    }
    if (unique(labels2).length != labels2.length) {
        if (table1_is_1D)
            table1.secondary = "SUMMARY";
        if (table2_is_1D)
            table2.secondary = "SUMMARY";
        throw new SetupError("The table " + table2.name + " contains duplicate " + (by_rows ? "column" : "row") + " labels.\r\n\r\nPlease select tables with no duplicated labels.");
    }
 
 
    // Create a new table
    var new_group = project.report.appendGroup();
    new_group.name = "Spliced Table: " + table1.name + " and " + table2.name;
    var new_table = new_group.appendTable();
    var target_table = new_group.copyAfter(table1, new_table);
    new_table.deleteItem();
 
    // Develop the Table JavaScript expression
    var expression = "includeWeb('Table JavaScript Utility Functions');\r\n"
                   + "var _FORBIDDEN_STATS = [" + _FORBIDDEN_STATS.map(function (str) { return "'" + str + "'"; }) + "];\r\n"
                   + "var createDummyQuestionFor1DTable = " + createDummyQuestionFor1DTable + ";\r\n"
                   + "var spliceIntoTable = " + spliceIntoTable + ";\r\n";
 
    var second_blue_string = table2.primary.name;
    var second_brown_string = table2.secondary == "SUMMARY" ? "SUMMARY" : table2.secondary.name;
    if (project.dataFiles.length > 1) {
        second_blue_string += " [" + table2.primary.dataFile.name + "]";
        if (second_brown_string != "SUMMARY")
            second_brown_string += " [" + table2.secondary.dataFile.name + "]";
    }
    second_blue_string = second_blue_string.replace(/'/g, "\\'").replace(/"/g, '\\"');
    second_brown_string = second_brown_string.replace(/'/g, "\\'").replace(/"/g, '\\"');
    expression += "spliceIntoTable(" 
                + "\'" + second_blue_string + "\', " 
                + "\'" + second_brown_string + "\', "
                + by_rows + ");"
 
    // Create the new rule and add to the table
    var new_rule = project.rules.newCustomRule(expression, {});
    target_table.rules.add(new_rule);
 
    // Tidy up original tables if they were 1D
    if (table1_is_1D)
        table1.secondary = "SUMMARY";
    if (table2_is_1D)
        table2.secondary = "SUMMARY";
    log("A new group containing the spliced table has been added to your report.")
}
 
 
// A custom error object so we can abort the setup of the choice model,
// and catch this error, presenting the message to the user without
// causing the QScript to crash and show an error report.
function SetupError(message) {
    this.message = message;
}


See also