Sort/Reorder Rows or Columns - Sort Columns

From Q
Jump to navigation Jump to search


This rule sorts the columns of table from the highest to lowest (or lowest to highest) according to the values shown (i.e., ranks the data, so that the order of the rows reveals a ranking) similar to [Rows or Columns - Sort Rows].


How to apply this rule

For the first time in a project

  • Select the table(s)/chart(s) that you wish to apply the rule to.
  • Start typing the name of the Rule into the Search features and data box in the top right of the Q window.
  • Click on the Rule when it appears in the QScripts and Rules section of the search results.

OR

  • Select Automate > Browse Online Library.
  • Choose this rule from the list.

Additional applications of the rule

  • Select a table or chart that has the rule and any table(s)/chart(s) that you wish to apply the rule to.
  • Click on the Rules tab (bottom-left of the table/chart).
  • Select the rule that you wish to apply.
  • Click on the Apply drop-down and choose your desired option.
  • Check New items to have it automatically applied to new items that you create. Use Edit > Project Options > Save as Template to create a new project template that automatically uses this rule.

Removing the rule

  • Select the table(s)/chart(s) that you wish to remove the rule from.
  • Press the Rules tab (bottom-right corner).
  • Press Apply next to the rule you wish to remove and choose the appropriate option.

How to modify the rule

  • Click on the Rules tab (bottom-left of the table/chart).
  • Select the rule that you wish to modify.
  • Click Edit Rule and make the desired changes. Alternatively, you can use the JavaScript below to make your own rule (see Customizing Rules).

JavaScript

includeWeb('Table JavaScript Utility Functions');
includeWeb('JavaScript Text Analysis Functions');
includeWeb('JavaScript Array Functions');

excludeRTablesWithoutColumns();

// Prevent operating on empty tables
if (table.numberRows < 1)
    form.ruleNotApplicable('too few rows');

// Prevent operating on tables containing text
let primary_statistic = table.availableStatistics[0];
if (primary_statistic == 'Text')
    form.ruleNotApplicable('this rule does not work on text tables');

let sort_within_spans = table.columnSpans.length > 0;

// Check for duplicate labels within spans (if any) or within the table as a whole.
if (sort_within_spans) {
    let column_labels = table.columnLabels;
    let spans = getLowestLevelSpans(table.columnSpans);
    let found_indices = getAllIndicesInSpans(spans);
    let leftovers = getLeftoverIndices(found_indices, table.numberColumns);
    spans.forEach(function (span) {
        let labels = span.indices.map(function (x) { return column_labels[x]; });
        if (arrayHasDuplicateElements(labels))
            form.ruleNotApplicable('there are duplicate column labels in span ' + span.label);
    });
    let leftover_labels = leftovers.map(function (x) { return column_labels[x]; });
    if (arrayHasDuplicateElements(leftover_labels))
        form.ruleNotApplicable('there are duplicate column labels in the table');
}
else {
    if (arrayHasDuplicateElements(table.columnLabels))
        form.ruleNotApplicable('there are duplicate column labels in the table');
}

// User controls
let ascending_label = form.newLabel('Sort:');
let ascending_menu = form.newComboBox('am', ['Descending', 'Ascending']);
ascending_menu.setDefault('Descending');
ascending_menu.lineBreakAfter = true;
let row_selection_label = form.newLabel('Row selection:');
let row_selection_menu = form.newComboBox('rsm', ['Largest row', 'Last row', 'Choose a row']);
row_selection_menu.setDefault('Largest row');
row_selection_menu.lineBreakAfter = true;
let description = form.newLabel('Sorts the columns of table from the highest to lowest (or lowest to highest) according to the values shown');
description.lineBreakAfter = true;
let control_array = [description, ascending_label, ascending_menu, row_selection_label, row_selection_menu];

let row_list = [];
if (row_selection_menu.getValue() == 'Choose a row' && table.rowLabels != null) {
    row_list = enumerateDuplicatesInStringArray(table.rowLabels, '(', ')');
    let row_choice_label = form.newLabel('Row:');
    var row_choice_menu = form.newComboBox('rcm', row_list);
    row_choice_menu.lineBreakAfter = true;
    control_array.push(row_choice_label);
    control_array.push(row_choice_menu);
}
form.setInputControls(control_array);

let ascending = ascending_menu.getValue() == 'Ascending';
let low_high_label = ascending ? 'Lowest to Highest' : 'Highest to Lowest';
const rule_name = 'Sort Columns';
form.setHeading(rule_name);
form.setSummary('Sort columns from ' + low_high_label);

// User specified labels to always exclude from the sort
// or never exclude from the sort.
let exclude_labels = addExpandingTextBoxes(control_array, 'Exclude from sort (in addition to NET/SUM):', 'el').map(function (label) { return label.toLowerCase(); });
let do_not_exclude_labels = addExpandingTextBoxes(control_array, 'Never exclude from sort:', 'dnel').map(function (label) { return label.toLowerCase(); });

// Sort according to the first stat selected in the table
let stat = table.statistics[0];
let target_row_index;

if (table.numberRows == 1) {
    target_row_index = 0;
}
else if (row_selection_menu.getValue() == 'Largest row') {
    // Obtain Row n from the Statistics - Right and make sure that the resulting array is the correct shape
    if (!rightTableExists() || right_table.availableStatistics.indexOf('Row n') == -1) {
        const stat_name = table.getTranslation('Row n');
        form.ruleNotApplicable(stat_name + " statistic does not exist for this table. Change Row Selection to 'Choose a row'");
    }
    let row_ns = right_table.get('Row n');
    if (row_ns.length == 1)
        row_ns = row_ns[0];
    else
        row_ns = row_ns.map(function (a) { return a[0]; });
    let num_rows = table.numberRows;

    // Find largest row n and the index of the row
    if (num_rows != 1) {
        let max_row_n = Number.NEGATIVE_INFINITY;
        for (let j = 0; j < num_rows; j++)
            if (!isNaN(row_ns[j]) && row_ns[j] >= max_row_n) {
                max_row_n = row_ns[j];
                target_row_index = j;
            }
        let rows_with_max = row_ns.filter(function (x) { return !isNaN(x) && x >= max_row_n; });
        // When there is more than one row with the same max value of row n then
        // default to the NET or SUM if found.
        if (rows_with_max.length > 1) {
            let net_rows = table.netRows;
            if (net_rows.length > 0)
                target_row_index = net_rows[0];
        }
    }
}
else if (row_selection_menu.getValue() == 'Last row') {
    target_row_index = (table.numberRows - 1);
}
else {
    const row_choice = row_choice_menu.getValue();
    if (row_choice == null || row_choice == '') {
        form.ruleNotApplicable('Please select a row to sort by');
    }
    target_row_index = row_list.indexOf(row_choice);
}

// Work out what to do with spans.
var spans;
if (table.columnSpans.length == 0) {
    let ind = [];
    for (let j = 0; j < table.numberColumns; j++)
        ind.push(j);
    spans = [{ indices: ind, label: 'all columns' }];
}
else {
    spans = getLowestLevelSpans(table.columnSpans);
    found_indices = getAllIndicesInSpans(spans);
    leftovers = getLeftoverIndices(found_indices, table.numberColumns);
    // Any indices not in spans get moved to the right of the table
    if (leftovers.length > 0) {
        let last = Math.max(...found_indices);
        let modifier = 0;
        leftovers.forEach(function (x) {
            if ((x - modifier) < last) {
                table.moveColumnAfter(x - modifier, last);
                modifier++;
            }
        });
        spans = getLowestLevelSpans(table.columnSpans);
        found_indices = getAllIndicesInSpans(spans);
        leftovers = getLeftoverIndices(found_indices, table.numberColumns);
        spans.push({ indices: leftovers, label: 'Columns outside of spans' });
    }
}

// Get values to be used by the sorting and determine if each column should be sorted (based on its label)
var values = table.get(stat);

spans.forEach(function (span) {
    let column_labels = table.columnLabels;
    let labels = span.indices.map(function (x) { return column_labels[x]; });
    let column_objects = [];
    let net_columns = table.netColumns;
    let sgn = (ascending ? 1 : -1);
    for (let j = 0; j < labels.length; j++) {
        let current_column_index = span.indices[j];
        let is_net = net_columns.indexOf(current_column_index) != -1;
        // Obtain a value for the current column to reflect it's sort order.
        // Replace NaN with -Infinity so that NaNs don't get mixed through the table,
        // as NaN does not play nicely with sort();
        // Add or subtract a small value based on the column's current position
        // so that in case of a tie, the current relative ordering of tied
        // columns will be maintained.
        if (values[target_row_index][current_column_index]) {
            let this_value = isNaN(values[target_row_index][current_column_index]) ? Number.NEGATIVE_INFINITY : values[target_row_index][current_column_index] + sgn * (j / 10000);
            column_objects.push({
                label: labels[j],
                value: this_value,
                sort: shouldSort(labels[j], exclude_labels, do_not_exclude_labels, is_net)
            });
        }
    }

    // Sort the column objects based on their values
    column_objects.sort(function (col_x, col_y) {
        // Get the values for the two columns we are comparing.
        let value_x = col_x.value;
        let value_y = col_y.value;
        if (value_x == Number.NEGATIVE_INFINITY && value_y == Number.NEGATIVE_INFINITY)
            return col_y.label.charCodeAt(0) - col_x.label.charCodeAt(0);
        return value_x - value_y;
    });

    // Perform the sorting of the table columns, ignoring any columns that have been marked with sort = false
    let top_index_in_span = Math.min(...span.indices);
    let top_column = top_index_in_span == 0 ? null : top_index_in_span - 1;
    if (ascending) {
        for (let j = column_objects.length - 1; j > -1; j--)
            if (column_objects[j].sort)
                moveColumnAfterComplete(findColumnInSpan(column_objects[j].label, span.indices), top_column);
    }
    else {
        for (let j = 0; j < column_objects.length; j++)
            if (column_objects[j].sort)
                moveColumnAfterComplete(findColumnInSpan(column_objects[j].label, span.indices), top_column);
    }
});

function findColumnInSpan(column_label, span_indices) {
    let column_labels = table.columnLabels;
    let labels = span_indices.map(function (x) { return column_labels[x]; });
    return span_indices[labels.indexOf(column_label)];
}

See also

Retrieved from "https://wiki.q-researchsoftware.com/index.php?title=Sort/Reorder_Rows_or_Columns_-_Sort_Columns&oldid=69315"