Aggregate Node
Aggregation is a data preparation task frequently used to reduce the size of a dataset. Before proceeding with aggregation, you should take time to clean the data, concentrating especially on missing values. Once you have aggregated, potentially useful information regarding missing values may be lost.
You can use an Aggregate node to replace a sequence of input records with summary, aggregated output records. For example, you might have a set of input sales records such as those shown in the following table.
| Age | Sex | Region | Branch | Sales |
|---|---|---|---|---|
| 23 | M | S | 8 | 4 |
| 45 | M | S | 16 | 4 |
| 37 | M | S | 8 | 5 |
| 30 | M | S | 5 | 7 |
| 44 | M | N | 4 | 9 |
| 25 | M | N | 2 | 11 |
| 29 | F | S | 16 | 6 |
| 41 | F | N | 4 | 8 |
| 23 | F | N | 6 | 2 |
| 45 | F | N | 4 | 5 |
| 33 | F | N | 6 | 10 |
You can aggregate these records with Sex and Region as key fields. Then choose to aggregate Age with the mode Mean and Sales with the mode Sum. Select Include record count in field in the Aggregate node dialog box and your aggregated output would be as shown in the following table.
| Age (mean) | Sex | Region | Sales (sum) | Record Count |
|---|---|---|---|---|
| 35.5 | F | N | 25 | 4 |
| 29 | F | S | 6 | 1 |
| 34.5 | M | N | 20 | 2 |
| 33.75 | M | S | 20 | 4 |
From this you learn, for example, that the average age of the four female sales staff in the North region is 35.5, and the sum total of their sales was 25 units.
Note: Fields such as Branch are automatically discarded when no aggregate mode is specified.