Aggregators

`aggregator` parameter to `pivot()`

The aggregator parameter of the pivot() function defines what will end up in the cells of the pivot table. It needs to be a function which gets called once per cell in the pivot table and returns an object with the following keys:

push: a function which takes a record from the input, this function is called once per record which matches the cell
value: a function which returns the value to be stored in the cell
format: a function which takes a value and returns a formatted string representation
label: a string which can be used as a label for this aggregator

Aggregator functions receive 3 parameters: a PivotData object, a rowKey and a colKey. The key parameters define where in the table this aggregator is located, and the reference to the PivotData object allows cell values to depend upon each other (a null value for either or both indicates a total cell). See the Renderers documentation for a description of the PivotData object. These parameters are useful for accessing row/col/overall 'total' values for normalization. See the fractionOf aggregatorTemplate in the source for an example of how to do this.

Here is an example aggregator "count", which will just return the number of records which match the cell:

var count = function() {
  return {
    count: 0,
    push: function(record) { this.count++; },
    value: function() { return this.count; },
    format: function(x) { return x; },
    label: "Count"
 };
};

`aggregators` parameter to `pivotUI()`

The aggregators parameter of the pivotUI() function, despite its similar name to the entity above, is actually a dictionary of functions which can used to generate an aggregator parameter for the pivot() function. The values of the aggregators dictionary need to be functions which take as an argument an array of attribute-names, which the user can specify in the UI by dragging them into the aggregator box with the dropdown, and which returns a function consumable by pivot() as an aggregator parameter.

Here is an example of an aggregator-generating function "countUnique", which can generate an aggregator which will count the number of unique values of the given attribute for records which match the cell. Note that the return value is very similar to the function above, save that it 'closes over' the variable attribute:

var countUnique = function(attributeArray) {
  var attribute = attributeArray[0];
  return function() {
    return {
      uniq: [],
      push: function(record) {
        var _ref;
        if (_ref = record[attribute], __indexOf.call(this.uniq, _ref) < 0) {
          this.uniq.push(record[attribute]);
        }
      },
      value: function() { return this.uniq.length; },
      format: function(x) { return x; },
      label: "Count Unique " + attribute
    };
  };
}

##Writing your own aggregators

If you are using PivotTable.js in a context where you already know the attribute-names in the data, as well as the types of cell-values your users will want to see (i.e. you are writing a reporting system) then you can create your own aggregation functions so that users will not have to drag attributes into the aggregator-function box in the UI.

As an example, let's say you are using PivotTable.js to generate summary tables for data which has a trials attribute and a successes attribute and you know that your users will care a lot about the success rate, which is defined as the sum of successes over the sum of trials. You could therefore create an aggregator-generating function to pass into pivotUI() in the aggregators dictionary defined as follows:

var successRate = function() {
  return function() {
    return {
      sumSuccesses: 0,
      sumTrials: 0,
      push: function(record) {
        if (!isNaN(parseFloat(record.successes))) {
          this.sumSuccesses += parseFloat(record.successes);
        }
        if (!isNaN(parseFloat(record[denom]))) {
          return this.sumTrials += parseFloat(record.trials);
        }
      },
      value: function() { return this.sumSuccesses / this.sumTrials; },
      format: function(x) { return x; },
      label: "Success Rate"
    };
  };
};

##Built-in aggregators

Some built-in aggregator generators are available under $.pivotUtilities.aggregators

As it happens, calculating the sort of rate above is pretty common, so PivotTable.js already includes a sumOverSum aggregator generator that you can wrap for this purpose, like this:

var sumOverSum = $.pivotUtilities.aggregators.sumOverSum;
var successRate = function() { return sumOverSum(["successes", "trials"]); }

Note that if you wanted to use the successRate defined above as a aggregator parameter to pivot(), you will need to use only the inner function, which returns the object, rather than the generator function thereof.

The full list of built-in aggregators available are:

count(): returns a count of records
countUnique([x]): counts unique values of the attribute x
listUnique([x]): returns comma-separated list of unique values of attribute x
sum([x]): returns the sum of the values of attribute x
intSum([x]): returns the sum of the values of attribute x, without the portion after the decimal point
average([x]): returns the mean of the values of attribute x
sumOverSum([x, y]): returns the sum of attribute x over the sum of attribute y
ub80([x, y]), lb80([x, y]): returns the upper or lower bound of the 80% binomial confidence interval of the sum of attribute x over the sum of attribute y, assuming that y represents Bernouilli trials and x represents successes
sumAsFractionOfTotal([x]), sumAsFractionOfRow([x]), sumAsFractionOfCol([x]), countAsFractionOfTotal() etc...: returns the sum of the values of attribute x (or the record count) as a percentage of the overall/row/column total