Tables - Crosstabs

From Q
Jump to navigation Jump to search

Related Videos
Part 4 of Creating Tables in Q (Video)
 

Generate multiple crosstabs at the same time This QScript generates multiple crosstabs at the same time (also, see Tables - Banner Tables). This is an alternative to using the Basic Tables feature, which automatically flattens any two-dimensional (grid) questions.

Technical detail

You will be asked to select:

  1. The questions use in the rows of the tables (these will be placed in the Blue Drop-down Menu).
  2. The questions use in the columns of the tables (these will be placed in the Brown Drop-down Menu).
  3. Whether you would like to sort the tables based on their significance (p-values) and whether you would like to only show tables with p-values below the selected threshold.
  4. The statistic(s) to be shown on the generated tables. Statistics will only be shown where they make sense for the data selected in the rows and columns of the tables.
  5. (In Displayr only) An (optional) variable to use as a filter and an (optional) variable to use as a weight.

Any Pick One - Multi, Pick Any - Grid and Number - Grid questions will be copied and flattened before using them in a crosstab. The flattened copy will contain all of the cells from the original question in a single column or row.

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

includeWeb("QScript Functions to Generate Outputs");
includeWeb('QScript Selection Functions');
includeWeb('QScript Table Functions');
includeWeb('QScript Utility Functions');
 
if (!main())
    log("QScript cancelled.");
else 
    conditionallyEmptyLog("QScript finished.");
 
function main() {
    // first condition makes sure Q.isOnTheWeb is defined
    var web_mode = (!!Q.isOnTheWeb && Q.isOnTheWeb());
    var ALLOWED_STATS_Q = ["%", "Column %", "Row %", "Total %", "Average",
			   "Median", "Index", "n", "Column n", "Row n", "Base n"];
    var allowed_stats;
    if (web_mode)
        allowed_stats = ALLOWED_STATS_Q.map(function(s){ return project.translations.get(s); });
    else
        allowed_stats = ALLOWED_STATS_Q;
    
    if (!requireDataFile()) {
        return false;
    }

    var column_q_choices = [];
    var row_q_choices = [];
 
    if (project.dataFiles.length > 1) 
        var data_file = selectOneDataFile('There is more than one data file in your project. ' + 
                'Please select the data file to use:', project.dataFiles)
    else
        var data_file = project.dataFiles[0];   
 
    var row_q_choices = data_file.questions.filter(function (q) { return q.isValid && !q.isHidden && q.questionType.indexOf("Text") == -1 && !questionHasOnlyMissingValues(q)});
    var column_q_choices = row_q_choices.filter(function (q) { return q.questionType != 'Ranking' && q.questionType != 'Experiment'});
 
    if (row_q_choices.length == 0 || column_q_choices.length == 0) {
        log("Not enough questions available to make crosstabs.")
        return false;
    }
 
    var row_questions, column_questions;
    while (!row_questions || !oneOrMoreQuestions(row_questions))
        row_questions = selectManyQuestions('Please select data to place in the rows:', row_q_choices).questions;
 
    while (!column_questions || !oneOrMoreQuestions(column_questions))
        column_questions = selectManyQuestions('Please select data to place in the columns:', column_q_choices).questions;
 
    // Sorting happens within each row by column variables, so if only one column variable, nothing to sort
    if (column_questions.length === 1)
        var show_opts = ["All tables", "Delete tables not significant at the 0.1 level",
                       "Delete tables not significant at the 0.05 level", "Delete tables not significant at the 0.01 level",
                       "Delete tables not significant at the 0.001 level", "Delete tables not significant at the 0.0001 level"];
    else
        var show_opts = ["All tables", "Sort tables by statistical significance of column data", "Delete tables not significant at the 0.1 level",
                       "Delete tables not significant at the 0.05 level", "Delete tables not significant at the 0.01 level",
                       "Delete tables not significant at the 0.001 level", "Delete tables not significant at the 0.0001 level",
                       "Sort and delete tables not significant at 0.1", "Sort and delete tables not significant at 0.05",
                       "Sort and delete tables not significant at 0.01", "Sort and delete tables not significant at 0.001",
                       "Sort and delete tables not significant at 0.0001"];
    var show_opt_idx = selectOne("Show", show_opts, null, 0);
    var selected_opt = show_opts[show_opt_idx];
    var sort_tables = selected_opt.match("^Sort|^All tables sorted") !== null;
    var delete_tables = selected_opt.match("[Dd]elete") !== null;
    if (delete_tables)
        var p_value_cutoff = selected_opt.match("0[.][0]{0,3}[15]")[0];    

    var stat_selection_text = "Which statistic would you like to show on the table? " + 
                              "(Statistics will only be shown where they make sense " +
                              "for the data selected in the rows and columns of the tables.)";
    var selected_stats_idx = selectMany(stat_selection_text, allowed_stats);
    allowed_stats = ALLOWED_STATS_Q.filter(function(s,i){ return selected_stats_idx.includes(i);});
    
    flattenSelectedQuestions(column_questions);
    flattenSelectedQuestions(row_questions);
    
    // Ask user for filter and weight variables
    if (web_mode) {
        var filter_questions = data_file.questions.filter(function isFilter(v){ return v.isFilter;});
        if (filter_questions.length > 0) {
            var filter_variables = filter_questions[0].variables
            for (i = 1; i < filter_questions.length; i++)
                filter_variables = filter_variables.concat(filter_questions[i].variables);
            var filter_opts = ["Total Sample"];
            filter_opts = filter_opts.concat(filter_variables.map(function getNames(v){ return v.label}));
            
            var selected_filters = selectMany("Please select filter(s) to apply to each crosstab.", filter_opts, null, [0]);
            if (selected_filters == 0)
                selected_filters = "Total Sample";
            else {
                selected_filters = selected_filters.filter(function gt0(x){ return x > 0; });
                selected_filters = selected_filters.map(function idx(x){return filter_variables[x-1];});
            }
        }else
            var selected_filters = "Total Sample";

        var weight_questions = data_file.questions.filter(function isWeight(v){ return v.isWeight;});    
        if (weight_questions.length > 0) {        
            var weight_variables = weight_questions[0].variables
            for (i = 1; i < weight_questions.length; i++)
                weight_variables = weight_variables.concat(weight_questions[i].variables);

            var weight_opts = ["None"];
            weight_opts = weight_opts.concat(weight_variables.map(function getNames(v){ return v.label}));
          
            var selected_weight = selectOne("Please select a weight to apply to each crosstab.", weight_opts, null, 0);
            if (selected_weight == 0)
                selected_weight = "None"
            else
                selected_weight = weight_variables[selected_weight-1];
        }else
            var selected_weight = "None";
    }  
    
    // check for large crosstabs
    var q_pairs_large = [];
    for (var i = 0; i < row_questions.length; i++) {
        for (var j = 0; j < column_questions.length; j++) {
            var row_q = row_questions[i];
            var column_q = column_questions[j];
            if (row_q.isValid && column_q.isValid && questionsYieldLargeCrosstab(row_q, column_q)) {
                q_pairs_large.push('"' + row_q.name + ' and ' + column_q.name + '"');
            }
        }
    }
 
    // warn about large crosstabs
    if (q_pairs_large.length == 1) {
        var cancelled = !confirm('The question pair ' + q_pairs_large[0] +
                       
                                 
                                 ' will result in a very large table.\n\n' +
                                 'Very large tables are not likely to be interesting, will be slow to calculate ' +
                                 'and will generate a warning message when viewed.\n\n' +
                                 'Do you wish to continue?');
        if (cancelled)
            return false;
    } else if (q_pairs_large.length > 1) {
        var q_pairs_large_examples = '';
        for (var i = 0; i < Math.min(3, q_pairs_large.length); i++) {
            q_pairs_large_examples = q_pairs_large_examples + q_pairs_large[i] + '\n';
        }
        var cancelled = !confirm('There are ' + q_pairs_large.length + ' very large tables that will be created.\n\n' +
                                 'Very large tables are not likely to be interesting, will be slow to calculate ' +
                                 'and will generate a warning message when viewed.\n\n ' +
                                 'Question pairs that will result in very large tables include:\n' + q_pairs_large_examples + '\n' +
                                 'Do you wish to continue?');
        if (cancelled)
            return false;
    }
 
    var crosstabs = project.report.appendGroup();
    crosstabs.name = 'Crosstabs';
 
    var nr = row_questions.length;
    var nc = column_questions.length;
    var min_p_values = new Array(nr);
    for (var i = 0; i < nr; i++) {
        min_p_values[i] = new Array(nc);
    }
    for (var i = 0; i < nr; i++) {
        // First compute corrected p-values to get sort order then add to do
        for (var j = 0; j < nc; j++) {
            if (web_mode) {
                var page = crosstabs.appendPage("Item");
                var table = page.appendTable();
            }else
                var table = crosstabs.appendTable();
            
            table.primary = row_questions[i];
            table.secondary = column_questions[j];
            var default_stat = table.cellStatistics;
            if (!addStat(table, 'Row %') * !addStat(table, 'n')) // non short-circuiting &&
                if (!addStat(table, 'Column n'))
                    addStat(table, 'Row n');
            
            var cell_stats = table.cellStatistics;
            if (allowed_stats.length > 0)
                cell_stats = allowed_stats;
            else
                cell_stats = default_stat;
            
            addStat(table, 'Corrected p');
            var p_values = getStatisticsFromTable(table, ['Corrected p']);
            if (p_values === null) {
                min_p_values[i][j] = 1;
            }else {
                p_values = p_values['Corrected p'];
                min_p_values[i][j] = minWithReplacedNaN(p_values, Number.POSITIVE_INFINITY);
            }
            table.cellStatistics = cell_stats; // reset stats back to user selected values or the default statistic

            if (web_mode) {
                page.name = table.primary.name + ' by ' + table.secondary.name;  
                var title_text = page.subItems[0];
                title_text.text = page.name;  

                if (selected_filters !== "Total Sample")
                    table.filters = selected_filters;
                if (selected_weight !== "None")
                    table.weight = selected_weight;     
            }
        }
    }
    
    // delete all the tables and recreate them in the sorted order only adding those with p-value below p_value_cutoff
    if (sort_tables || delete_tables) {    
        crosstabs.deleteItem();
        var crosstabs = project.report.appendGroup();
        crosstabs.name = 'Crosstabs';
        
        for (var i = 0; i < nr; i++) {            
            if (sort_tables) {
                var table_order = order(min_p_values[i]);
            }else {
                var table_order = new Array(nc);
                for (var j = 0; j < nc; j++)
                    table_order[j] = j;
            }

            for (var j = 0; j < nc; j++) {
                var idx = table_order[j];
                if (!delete_tables || min_p_values[i][idx] < p_value_cutoff) {
                    if (web_mode) {
                        var page = crosstabs.appendPage("Item");
                        var table = page.appendTable();
                    }else
                        var table = crosstabs.appendTable();
            
                    table.primary = row_questions[i];
                    table.secondary = column_questions[idx];
                    if (allowed_stats.length > 0)
                        table.cellStatistics = allowed_stats;
                
                    if (web_mode) {
                        page.name = table.primary.name + ' by ' + table.secondary.name;    
                        var title_text = page.subItems[0];
                        title_text.text = page.name;  

                        if (selected_filters !== "Total Sample")
                            table.filters = selected_filters;
                        if (selected_weight !== "None")
                            table.weight = selected_weight;        
                    }
                }
            }
        }
        if (crosstabs.subItems.length == 0) {
            crosstabs.deleteItem();
            log("No tables are significant at p-value " + p_value_cutoff + ".");
            return true;
        }
    }

    if (fileFormatVersion() > 8.65)
        project.report.setSelectedRaw([crosstabs.subItems[0]]);
    conditionallyEmptyLog('Crosstabs have been added to the "' + crosstabs.name + '" group.');
    return true;
} 

// replicate R's order() function
// e.g. sortWithIndices(['b','c','a']) = [2,1,0]
function order(arr) {
    var arr_with_index = [];
    for (var i in arr) {
        arr_with_index.push([arr[i], i]);
    }
    arr_with_index.sort(function(left, right) {
      return left[0] - right[0];
    });

    var idxs = [];
    for (var j in arr_with_index) {
        // arr.push(arr_with_index[j][0]);
        idxs.push(arr_with_index[j][1]);
    }
    return idxs;
}
 
function addStat(table, stat) {
    cell_stats = table.cellStatistics;
    cell_stats.push(stat);
    table.cellStatistics = cell_stats;
    return table.cellStatistics.indexOf(stat) > -1;
}
 
function oneOrMoreQuestions(questions) {
    if (questions.length == 0) {
        alert('Select one or more questions.');
        return false;
    } else
        return true;
}

function questionHasOnlyMissingValues(q) {
    var v = q.variables[0];
    try {
        var raw = v.rawValues;
    }
    catch(e) {
        return true;
    }
    var all_missing = true;
    for (var i = 0; i < raw.length; i++) { 
        if (!v.valueAttributes.getIsMissingData(raw[i])) {
            all_missing = false;
            break;
        }
    }
    return all_missing;  
}

See also

Retrieved from ‘https://wiki2.q-researchsoftware.com/index.php?title=Tables_-_Crosstabs&oldid=53824’