How to Add an Average For Specified Rows in a Table

This article describes how to add an average...

...of specified rows to a table:

Requirements

A table containing a categorical (nominal) variable. In this example, we are averaging the Coke brands (Coca-Cola, Diet Coke, Coke Zero) row percentages and creating a new row called Coke Average %.

Method - Average one statistic on select rows

Note, this rule only averages one statistic. If you're showing more than one statistic on your table, the other statistics will show as NaN.

1. Select your table.

2. Go to Data > Rules on the object inspector.

3. Click the Plus (+) button. 

4. Click New custom rule (write your own JavaScript) > Edit JavaScript.

5. Paste the below into the dialog:

// This rule adds a new row to the table showing the average of 
// rows selected by the user. This is just a simple average, ie
// the sum of the values from the rows, divided by the number of
// rows.

// Calculate a custom average and add to bottom of table.
includeWeb('JavaScript Array Functions');
includeWeb('Table JavaScript Utility Functions');
includeWeb('JavaScript Utilities');

form.setSummary("Simple Average Select Rows - One Statistic");

if (table.rowLabels.length != uniqueElementsInArray(table.rowLabels).length)
form.ruleNotAppliable("there are some duplicate row labels. All labels must be unique")

// Set up user inputs
var stat_label = form.newLabel("Statistic to use for custom average: ");
var stat_drop_down = form.newComboBox("st", table.availableStatistics);
stat_drop_down.lineBreakAfter = true;
stat_drop_down.setDefault(table.availableStatistics[0]);

var new_row_text = form.newLabel("Label for new row: ");
var new_row_box = form.newTextBox("nrl");
new_row_box.lineBreakAfter = true;

var rows_to_average_text = form.newLabel("Rows to average:");
rows_to_average_text.lineBreakAfter = true;
var row_selection = form.newComboBox("brb", table.rowLabels);
row_selection.setDefault(table.rowLabels[0]);
row_selection.lineBreakAfter = true;

var control_array = [stat_label, stat_drop_down, new_row_text, new_row_box, rows_to_average_text, row_selection];
form.setInputControls(control_array);

var row_label_options = [""].concat(table.rowLabels);

// Selection of rows to average
var rows_to_average = [];
var last_selection = row_selection.getValue();
var counter = 1;
var is_pm_next = true;
while (last_selection != "") {
rows_to_average.push(last_selection);
row_label_options = difference(row_label_options, rows_to_average);
var new_control = form.newComboBox("br" + counter, row_label_options);
new_control.lineBreakAfter = true;
new_control.setDefault("");
is_pm_next = true;
counter ++;
control_array.push(new_control);
form.setInputControls(control_array);
last_selection = new_control.getValue();
}

form.setInputControls(control_array);

var stat_to_use = stat_drop_down.getValue();

// Generate an array of the rows to exclude from the calculation of the sum
// for the custom average

var row_labels = table.rowLabels;
var row_indices_to_average = rows_to_average.map(function (label) { return row_labels.indexOf(label); });

// Add a new row at the bottom of the table
insertRowAfterComplete(table.numberRows - 1, new_row_box.getValue());

// Calculate the average for each Column
var values = table.get(stat_to_use);
for (var col = 0; col < table.numberColumns; col++) {
var sum = 0;
var count = 0;
row_indices_to_average.forEach(function (row) {
 if(!isNaN(values[row][col])){
 sum += values[row][col];
 count ++;
 }
})

// Set custom average in last row
values[table.numberRows - 1][col] = sum / count;
}
table.set(stat_to_use, values);

Method - Average all statistics on select rows

Follow the instructions above, but use the following code for step 5:

// This rule adds a new row to the table showing the average of 
// rows selected by the user. This is just a simple average, ie
// the sum of the values from the rows, divided by the number of
// rows.

// Calculate a custom average and add to bottom of table.
includeWeb('JavaScript Array Functions');
includeWeb('Table JavaScript Utility Functions');
includeWeb('JavaScript Utilities');

form.setSummary("Simple Average Select Rows - All Statistics");

if (table.rowLabels.length != uniqueElementsInArray(table.rowLabels).length)
form.ruleNotAppliable("there are some duplicate row labels. All labels must be unique")

// Set up user inputs
var new_row_text = form.newLabel("Label for new row: ");
var new_row_box = form.newTextBox("nrl");
new_row_box.lineBreakAfter = true;

var rows_to_average_text = form.newLabel("Rows to average:");
rows_to_average_text.lineBreakAfter = true;
var row_selection = form.newComboBox("brb", table.rowLabels);
row_selection.setDefault(table.rowLabels[0]);
row_selection.lineBreakAfter = true;

var control_array = [new_row_text, new_row_box, rows_to_average_text, row_selection];
form.setInputControls(control_array);

var row_label_options = [""].concat(table.rowLabels);

// Selection of rows to average
var rows_to_average = [];
var last_selection = row_selection.getValue();
var counter = 1;
var is_pm_next = true;
while (last_selection != "") {
rows_to_average.push(last_selection);
row_label_options = difference(row_label_options, rows_to_average);
var new_control = form.newComboBox("br" + counter, row_label_options);
new_control.lineBreakAfter = true;
new_control.setDefault("");
is_pm_next = true;
counter ++;
control_array.push(new_control);
form.setInputControls(control_array);
last_selection = new_control.getValue();
}

form.setInputControls(control_array);

// Generate an array of the rows to exclude from the calculation of the sum
// for the custom average
var row_labels = table.rowLabels;
var row_indices_to_average = rows_to_average.map(function (label) { return row_labels.indexOf(label); });

// Add a new row at the bottom of the table
insertRowAfterComplete(table.numberRows - 1, new_row_box.getValue());

///// do the calcs across all statistics on table
var stat_to_use = table.statistics;

//loop through each statistic
for(var stat = 0; stat < stat_to_use.length; stat++){
var thestat = stat_to_use[stat];
// Calculate the average for each Column
var values = table.get(thestat);
for (var col = 0; col < table.numberColumns; col++) {
var sum = 0;
var count = 0;
row_indices_to_average.forEach(function (row) {
 if(!isNaN(values[row][col])){
 sum += values[row][col];
 count ++;
 }
})

// Set custom average in last row
values[table.numberRows - 1][col] = sum / count;
}
//set the statistic on the table to the new values
table.set(thestat, values);
}

See Also

How to Use Rules in Displayr

How to Customize a Rule

How to Create a Custom Rule

How to Access Statistics from a Table in a Rule

How to Create a Custom Statistic or Calculation via a Rule