Modify Whole Table or Plot - Replace Value with Rolling Average

From Q
Jump to navigation Jump to search


This rule computes a rolling average on a table. There are options for controlling the number of periods in the moving average and whether to roll by the rows or columns.

Technical details

  • A lagged rolling average is computed. For example, if January has a score of 3, February 4 and March 6, the 3-period rolling average for March is (3 + 4 + 6) / 3 = 4.33333333. More sophisticated rolling averages can be computed using Time Series Analysis (see How to Compute a Moving Average).
  • Where values cannot be computed due to insufficient data, the value is shown as NaN. For example, if January has a score of 3, February 4 and March 6, the 3-period rolling average for the three months, respectively, are NaN, NaN and 4.33333333.
  • This rule is only applied to the first Statistic selected in each of Statistics - Cells and whichever is appropriate of Statistics - Below and Statistics - Right.
  • You may not have a NET in the variable containing the dates.
  • The statistical testing results on the table are not changed by this rule. That is, they reflect the results for the original, un-rolled statistics.

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

rollingAverage = function (x, n_periods) {
    Array.prototype.sum = function () {
        let total = 0;
        let i = this.length;

        while (i--) {
            total += this[i];
        }

        return total;
    };

    let n_columns = x[0].length;
    let n_rows = x.length;
    let result = new Array(n_rows);
    for (let c = 0; c < n_columns; c++) {
        let sum = 0;
        for (let r = 0; r < n_rows; r++) {
            if (c == 0)
                result[r] = new Array(n_columns);

            if (r >= n_periods - 1) {
                let array_subset = x.map(function (arr) { return arr[c]; }).slice(r - n_periods + 1, r + 1);
                result[r][c] = array_subset.sum() / n_periods;
            }
            else {
                result[r][c] = NaN;
            }
        }

    }
    return (result);
};





includeWeb("Table JavaScript Utility Functions");

// For R tables where columns are not recognised, this rule only works when there is 1 statistic (column)
if (table.statistics.length > 1) {
    excludeRTablesWithoutColumns();
}

// Create the form
form.setHeading('Replace Value with Rolling Average');
let description = form.newLabel("Replace the values of the first statistic with rolling averages.");
description.lineBreakAfter = true;
let number_label = form.newLabel('Number of periods to roll:');
let numeric_up_down = form.newNumericUpDown('periods');
numeric_up_down.lineBreakAfter = true;
numeric_up_down.setDefault(3);
numeric_up_down.setIncrement(1);
numeric_up_down.setMinimum(1);
let within_rows_cb = form.newCheckBox('check', "Dates are in the rows");
within_rows_cb.setDefault(false);
form.setInputControls([description, number_label, numeric_up_down, within_rows_cb]);
let within_rows = within_rows_cb.getValue();
let n_periods = numeric_up_down.getValue();
form.setSummary(n_periods + " period rolling average (within " + (within_rows ? "columns" : "rows") + ")");



let labs = within_rows ? table.rowLabels : table.columnLabels;

if (labs == null) {
    form.ruleNotApplicable("there are no " + (within_rows ? "rows" : "columns") + " to average over. Toggle the 'Dates are in the rows' setting above");
}

if (labs.indexOf("Total") != -1 || labs.indexOf("NET") != -1)
    form.ruleNotApplicable("this rule can only be applied when the date labels don't contain a NET or Total");

if (!within_rows && n_periods > table.numberColumns)
    form.ruleNotApplicable("there are fewer columns than periods to average over");
if (within_rows && n_periods > table.numberRows)
    form.ruleNotApplicable("there are fewer rows than periods to average over");
if (table.statistics[0] == "Text")
    form.ruleNotApplicable("this rule can only be applied to a numeric table. Try using Text Analysis - Automatic Categorization to create categorical variables to tabulate");

// Peforming rolling average on main table.
let values = table.get(table.statistics[0]);
if (typeof values[0][0] != "number")
    form.ruleNotApplicable("the first statistic in the table is not numeric");
if (!within_rows)
    values = Q.transpose(values);
values = rollingAverage(values, n_periods);
if (!within_rows)
    values = Q.transpose(values);
table.set(table.statistics[0], values);

// Peforming rolling average on marginal table table.
if (within_rows ? (rightTableExists() && right_table.statistics.length > 0) : (belowTableExists() && below_table.statistics.length > 0)) {
    let marginal_values = within_rows ? right_table.get(right_table.statistics[0]) : below_table.get(below_table.statistics[0]);
    if (typeof marginal_values[0][0] == "number") { // Do not try to average over column comparisons strings
        let needs_transpose = marginal_values.length == 1;

        if (needs_transpose) {
            marginal_values = Q.transpose(rollingAverage(Q.transpose(marginal_values), n_periods));
        }
        else {
            marginal_values = rollingAverage(marginal_values, n_periods);
        }
        if (within_rows)
            right_table.set(right_table.statistics[0], marginal_values);
        else
            below_table.set(below_table.statistics[0], marginal_values);
    }
}

See also

Retrieved from "https://wiki.q-researchsoftware.com/index.php?title=Modify_Whole_Table_or_Plot_-_Replace_Value_with_Rolling_Average&oldid=69164"