Modifying the Whole Table or Plot - Creating Rolling Averages

From Q
Jump to: navigation, 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.

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 () {
        var total = 0;
        var i = this.length; 
 
        while (i--) {
            total += this[i];
        }
 
        return total;
    }
 
    var n_columns = x[0].length;
    var n_rows = x.length;
    var result = new Array(n_rows);
    for (var c = 0; c < n_columns; c++) {
        var sum = 0;
        for (var r = 0; r < n_rows; r++) {
            if (c == 0)
                result[r] = new Array(n_columns);
 
            if (r >= n_periods - 1) {
                var 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");
 
// Create the form 
form.setHeading('Rolling (moving) average');
var number_label = form.newLabel('Number of periods to roll:'); 
var 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); 
var within_rows_cb = form.newCheckBox('check',"Dates are in the rows");
within_rows_cb.setDefault(false);
form.setInputControls([number_label, numeric_up_down, within_rows_cb]);
var within_rows = within_rows_cb.getValue()
var n_periods = numeric_up_down.getValue()
form.setSummary(n_periods + " period rolling average (within " + (within_rows ? "columns" : "rows") + ")");
 
 
 
var 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 where the question containing dates contants no NET or Totals.");
 
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");
 
// Peforming rolling average on main table.
var values = table.get(table.statistics[0]);
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)) { 
    var marginal_values = within_rows ? right_table.get(right_table.statistics[0]) : below_table.get(below_table.statistics[0]);
    var 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

Personal tools
Namespaces

Variants
Actions
Navigation
Categories
Toolbox