How to aggregate column values
Several functions are available to aggregate values of a value column. Aggregation means that you get one aggregate value, for instance the average or the sum, of a whole column, or one value per group of class names. In this way, you can for instance calculate the total area of each class. Sometimes one item is considered to be more important than another item. Then, weight values may be used during the calculation to express this difference in importance.
Aggregations:
- aggregating values of all records of column a,
- aggregating values in column a for a group of records of the same class (key/group column g) , or
- aggregating values in column a, while using weight factors in a weight column w.
- aggregating values in column a for a group of records of the same class (key/group column g) while using weight factors in a weight column w.
To perform an aggregation through the menu of a table window:
- open a table containing the column to be aggregated,
- open the Columns menu, choose Aggregation.
The Aggregate Column dialog box appears.
In the Aggregate Column dialog box:
- select the value column which contains the data to be aggregated,
- select the aggregation function that you want to use,
- if you to group the data (e.g. according to soil classes), mark the Group By check box and select a 'group by' column,
- if you want to use weights in a weight column (e.g. large areas are more important than smaller ones), mark the Weight check box and select a weight column,
- if you want to write the aggregation results into a separate table, mark the Output Table check box and type a table name,
- type an Output Column Name of the aggregation.
For examples of aggregations, click the aggregations functions described below.
To perform aggregations using the command line of a table window:
- open a table containing the column to be aggregated,
- on the command line of the table window, type a TabCalc expression, using one of the following aggregation functions: AGGAVG, AGGCNT, AGGMIN, AGGMED, AGGMAX, AGGPRD, AGGSTD, AGGSUM.
Aggregation 'functions' can have 1, 2, or sometimes 3 arguments:
- aggregation functions with one argument (col) return one answer for all entries of column col.
- aggregation functions with two parameters (col, g) return one answer per group g.
- aggregation function with two parameters (col, , w) return one answer for all entries of column col, and use weights in column w during the calculation. In fact parameter g is left blank.
- aggregation functions with three parameters (col, g, w) return one answer per group g, and use weights in column w during the calculation.
Notes:
- When no weight column is specified, then all data get the same weight value which is per default 1.
- When you want to use a weight column but when an aggregation by group is not necessary, then only omit the group column name but leave the two commas in the syntax. For example: AGGAVG(col, ,w).
If you would use only one comma, ILWIS will interpret the second column specified as the group column and not as the weight column.
- Within the brackets of an aggregation function, no expressions can be used.
- For ILWIS 1.41 users: argument g can be considered as the key column.
See also:
Aggregate functions