This article describes how to add an average...
...of specified columns to a table:
Requirements
A categorical table. In this example, we are averaging the Coke brand column percentages and creating a new column called Coke Average %.
Please note these steps require a Displayr license.
Method - Average one statistic on select columns
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 column to the table showing the average of
// columns selected by the user. This is just a simple average, ie
// the sum of the values from the columns, divided by the number of
// columns.
// Calculate a custom average and add to right of table.
includeWeb('JavaScript Array Functions');
includeWeb('Table JavaScript Utility Functions');
includeWeb('JavaScript Utilities');
form.setSummary("Simple Average Select Columns - One Statistic");
if (table.columnLabels.length != uniqueElementsInArray(table.columnLabels).length)
form.ruleNotAppliable("there are some duplicate column 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_col_text = form.newLabel("Label for new column: ");
var new_col_box = form.newTextBox("ncl");
new_col_box.lineBreakAfter = true;
var cols_to_average_text = form.newLabel("Columns to average:");
cols_to_average_text.lineBreakAfter = true;
var col_selection = form.newComboBox("bcb", table.columnLabels);
col_selection.setDefault(table.columnLabels[0]);
col_selection.lineBreakAfter = true;
var control_array = [stat_label, stat_drop_down, new_col_text, new_col_box, cols_to_average_text, col_selection];
form.setInputControls(control_array);
var col_label_options = [""].concat(table.columnLabels);
// Selection of columns to average
var cols_to_average = [];
var last_selection = col_selection.getValue();
var counter = 1;
var is_pm_next = true;
while (last_selection != "") {
cols_to_average.push(last_selection);
col_label_options = difference(col_label_options, cols_to_average);
var new_control = form.newComboBox("bc" + counter, col_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 columns to exclude from the calculation of the sum
// for the custom average
var col_labels = table.columnLabels;
var col_indices_to_average = cols_to_average.map(function (label) { return col_labels.indexOf(label); });
// Add a new column at the right of the table
insertColumnAfterComplete(table.numberColumns-1, new_col_box.getValue());
// Calculate the average for each Row
var values = table.get(stat_to_use);
for (var row = 0; row < table.numberRows; row++) {
var sum = 0;
var count = 0;
col_indices_to_average.forEach(function (col) {
if(!isNaN(values[row][col])){
sum += values[row][col];
count ++;
}
})
// Set custom average in last column
values[row][table.numberColumns-1] = sum / count;
}
table.set(stat_to_use, values);
6. Press the blue Play button > OK > OK.
7. On the following screen, enter the information as prompted and a new row will be added to the table.
Method - Average all statistics on select columns
Follow the instructions above, but use the following code for step 5:
// This rule adds a new column 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 columns, divided by the number of
// columns.
// Calculate a custom average and add to right of table.
includeWeb('JavaScript Array Functions');
includeWeb('Table JavaScript Utility Functions');
includeWeb('JavaScript Utilities');
form.setSummary("Simple Average Select Columns - All Statistics");
if (table.columnLabels.length != uniqueElementsInArray(table.columnLabels).length)
form.ruleNotAppliable("there are some duplicate column labels. All labels must be unique")
// Set up user inputs
var new_col_text = form.newLabel("Label for new column: ");
var new_col_box = form.newTextBox("ncl");
new_col_box.lineBreakAfter = true;
var cols_to_average_text = form.newLabel("Columns to average:");
cols_to_average_text.lineBreakAfter = true;
var col_selection = form.newComboBox("bcb", table.columnLabels);
col_selection.setDefault(table.columnLabels[0]);
col_selection.lineBreakAfter = true;
var control_array = [new_col_text, new_col_box, cols_to_average_text, col_selection];
form.setInputControls(control_array);
var col_label_options = [""].concat(table.columnLabels);
// Selection of columns to average
var cols_to_average = [];
var last_selection = col_selection.getValue();
var counter = 1;
var is_pm_next = true;
while (last_selection != "") {
cols_to_average.push(last_selection);
col_label_options = difference(col_label_options, cols_to_average);
var new_control = form.newComboBox("bc" + counter, col_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 columns to exclude from the calculation of the sum
// for the custom average
var col_labels = table.columnLabels;
var col_indices_to_average = cols_to_average.map(function (label) { return col_labels.indexOf(label); });
// Add a new row at the right of the table
insertColumnAfterComplete(table.numberColumns - 1, new_col_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 row = 0; row < table.numberRows; row++) {
var sum = 0;
var count = 0;
col_indices_to_average.forEach(function (col) {
if(!isNaN(values[row][col])){
sum += values[row][col];
count ++;
}
})
// Set custom average in last row
values[row][table.numberColumns - 1] = sum / count;
}
//set the statistic on the table to the new values
table.set(thestat, values);
}