Sort/Reorder Rows or Columns - Sort Categories from Highest to Lowest (Does Not Update When Data Changes)

From Q
Jump to: navigation, search

Sorts the categoies of the selected data from highest to lowest based on summary data This QScript sorts the questions in the project from highest to lowest (i.e., ranks the data, so that the order of the rows reveals a ranking). This is computed using the entire sample. The order does not update if the data changes (e.g., when filtered or updated). Also, see Sorting and Reordering - Sort Rows (Automatically Updates when Data Changes).

Example

SortingRule.png

Technical details

  • Categories which look like 'Other/Specify', 'Don't Know', 'None of these', or 'All of these' style responses, as well as the NET and SUM rows, will be left at the bottom of the table.
  • This QScript will not try to sort questions that look like scale questions, according to the rules of the function looksLikeScale.
  • Tables are sorted in the descending order of the relevant statistic, as determined by their Question Type:

Technical details

includeWeb("QScript Functions to Generate Outputs");
includeWeb('QScript Selection Functions');
includeWeb('QScript Table Functions');
includeWeb('QScript Utility Functions');
includeWeb('QScript Functions to Generate Outputs');
includeWeb('QScript Questionnaire Functions');
includeWeb('QScript Value Attributes Functions');
 
 
function getAllQuestionsFromDataFiles(data_file_array) {
    let selected_questions = [];
    data_file_array.forEach(function (file) { selected_questions = selected_questions.concat(file.questions)});
    return selected_questions.filter(function(q) { return !q.isHidden && !q.isBanner; }) ;
}
 
 
// This function will sort a Pick One - Multi according to the Row % statistics
// in the right-most column that is not the NET columm
 
// Accounts for the possibility that the data reduction has been transposed
function sortPickOneMultiByRightColumn(question) {
    checkQuestionType(question, ["Pick One - Multi"]);
    let temp_table = project.report.appendTable();
    temp_table.primary = question;
 
    let output;
    try {
        output = temp_table.calculateOutput();
    } catch(e) {
        temp_table.deleteItem();
        return null;// Table is empty
    }
 
    // Handle the case where the table has had its rows and columns swapped
    let is_row = output.availableStatistics.indexOf('Row %') > -1;
    let values = is_row ? getRightMostColumnFromTableExcludingNet(temp_table, 'Row %') : getBottomRowFromTableExcludingNet(temp_table, 'Column %');
    if (values.filter(function (x) { return !isNaN(x); }).length > 0)
        anchoredDescendingSortTable(temp_table, values, !is_row);

    temp_table.deleteItem();
}
 
function sortGridByLeftColumn(question) {
    checkQuestionType(question, ["Pick Any - Grid", "Number - Grid"]);
    let stat;
    if (question.questionType == "Pick Any - Grid")
        stat = '%';
    else
        stat = 'Average';
    let temp_table = project.report.appendTable();
    temp_table.primary = question;
    let values = getLeftMostColumnFromTableExcludingNet(temp_table, stat);
    if (values.filter(function (x) { return !isNaN(x); }).length > 0)
        anchoredDescendingSortTable(temp_table, values, false);
    temp_table.deleteItem();
}
 
 
 
function anchoredDescendingSortTable(table, values, by_columns) {
    let labels;
    let nets;
    if (!by_columns) {
        labels = table.primary.dataReduction.rowLabels;
        nets = table.primary.dataReduction.netRows;
    } else {
        labels = table.primary.dataReduction.columnLabels;
        nets = table.primary.dataReduction.netColumns;
    }
    if (labels == null)
        alert(table.name + ' has no labels');
    let num_vals = values.length;
 
    for (let i = 0; i < num_vals; i++)  {
        let label = labels[i];
        if (isNonSortable(label) || (nets && nets.indexOf(i) != -1) || (!nets && (label == "NET" || label == "SUM")))
            values[i] = -999999999999;
    }
 
    //sorting
    let sorted_values = values.slice(0);
    sorted_values.sort(function (a,b) { return b-a; } );
    let dataReduction = table.primary.dataReduction;
    let n_values = values.length;
    let positions = new Array(values.length);
    let prev_label = null;
    for (let i = 0; i < values.length; i++)  {
        var this_label = labels[values.indexOf(sorted_values[i])];
        dataReduction.moveAfter(this_label,prev_label);
        prev_label = this_label;
    }
 
}
 
 
function getRightMostColumnFromTableExcludingNet(table, stat) {
    let output;
    let values;
    try {
        output = table.calculateOutput();
        values = output.get(stat);
    } catch (e) {
        return null; //This table is empty or does not have the stat
    }
    let num_cols = output.numberColumns;
    let num_rows = output.numberRows;
    let cols_without_net = output.columnIndices(false);
    let result = new Array(num_rows);//as the array could be huge, specifying its size up-front
    if (cols_without_net.length > 0) {
        let col = cols_without_net[cols_without_net.length - 1];
        for (let row = 0; row < num_rows; row ++)
            result[row] = values[row][col];
    }
    return result;
}
 
function getBottomRowFromTableExcludingNet(table, stat) {
    let output;
    let values;
    try {
        output = table.calculateOutput();
        values = output.get(stat);
    } catch (e) {
        return null; //This table is empty or does not have the stat
    }
    let num_cols = output.numberColumns;
    let num_rows = output.numberRows;
    let rows_without_net = output.rowIndices(false);
    let result = new Array(num_cols);//as the array could be huge, specifying its size up-front
    if (rows_without_net.length > 0) {
        let row = rows_without_net[rows_without_net.length - 1];
        for (let col = 0; col < num_cols; col ++)
            result[col] = values[row][col];
    }
    return result;
}
 
function getLeftMostColumnFromTableExcludingNet(table, stat) {
    let output;
    let values;
    try {
        output = table.calculateOutput();
        values = output.get(stat);
    } catch (e) {
        return null; //This table is empty or does not have the stat
    }
    let num_cols = output.numberColumns;
    let num_rows = output.numberRows;
    let cols_without_net = output.columnIndices(false);
    let result = new Array(num_rows);//as the array could be huge, specifying its size up-front
    if (cols_without_net.length > 0) {
        let col = cols_without_net[0];
        for (let row = 0; row < num_rows; row ++)
            result[row] = values[row][col];
    }
    return result;
}
 
function consistentQuestionSort(question) {
    let q_type = question.questionType;
    if (q_type == "Text" || q_type == "Text - Multi")
        return null;
    if (["Pick One", "Pick Any", "Pick Any - Compact", "Number - Multi", "Ranking"].indexOf(q_type) > -1) {
        let temp_table = project.report.appendTable();
        temp_table.primary = question;
        let empty = false;
        try {
            temp_table.calculateOutput()
        } catch (e) {
            empty = true;
        }
        if (!empty)
            sortTableDescending(temp_table);
        temp_table.deleteItem();
    }
    else if (q_type == "Pick One - Multi")
        sortPickOneMultiByRightColumn(question);
    else if (q_type == "Pick Any - Grid" || q_type == "Number - Grid")
        sortGridByLeftColumn(question);  
}

let selected_questions;

if (!inDisplayr()) {
    // Ask the user to choose which data files to use
    let selected_datafiles = dataFileSelection();
 
    let candidate_questions = getAllQuestionsFromDataFiles(selected_datafiles).filter(function (q) { return q.questionType != "Text" && q.questionType != "Text - Multi"; } );
    selected_questions = selectManyQuestions("Select questions to sort: ", candidate_questions).questions;
} else {
    let user_selections = getAllUserSelections();
    selected_questions = user_selections.selected_questions;
    if (selected_questions.length == 0) {
        log("No appropriate data is selected. Select one or more variable sets containing multiple categories in the Data Sets section and run this option again.");
    }
}
 
selected_questions.forEach(function (question) {
    consistentQuestionSort(question);
});

Prior to the 15th of December, 2015, this page was known as Sort - Sort from Highest to Lowest (Does Not Update When Data Changes)

See also