Next Previous Up Contents
Next: tjoin: Joins multiple tables side-to-side
Up: tgroup: Calculates aggregate functions on groups of rows
Previous: Usage

### B.38.2 Examples

Here are some examples of using `tgroup`:

    stilts tgroup in=B_sn.vot keys='MType' aggcols='null;count' out=type_counts.csv

  This produces an output table with two columns: the first column (defined by the `keys` parameter) gives all the distinct values of the `MType` column in the input table, and the second column (defined by the `aggcols` parameter) gives a count of how many rows in the input table have that MType value. The output is written to a CSV file.

  Since the `count` aggregator pays no attention to the values it is counting, the quantity before the semicolon is irrelevant in this case, so for instance "`0;count`", "`100;count`" or "`MType;count`" would work just as well. If the `aggcols` parameter is omitted altogether, the output table will be the same but without the `count` column, i.e. it will just list all the distinct values of the `MType` column.

    stilts tgroup in=B_sn.vot
                  icmd='colmeta -name Discoverer disc' icmd='select MaxMag<20'
                  keys='Discoverer' aggcols='0;count;SNae'
                  ocmd='sorthead -down 10 SNae'

  This does a similar job to the previous example, but with some additional pre- and post-processing, to produce a league table of discoverers of bright supernovae. The `icmd` parameters define pre-processing filters that rename one column, and select only those rows for which the `MaxMag` column is below a certain threshold. The `keys` parameter groups rows by the contents of the `Discoverer` (née `disc`) column, and the `aggcols` parameter counts how many sources are listed for each discoverer, naming the resulting column "`SNae`". Finally, the `ocmd` filter sorts the results by the count column, listing only the top ten to standard output.

    stilts tgroup in=exomercat.fits
                  keys='discovery_method status'
                  aggcols='null;count mass;mean mass;stdev r;max;max_radius r;ngood;num_radii'
                  ocmd='sort "discovery_method status"'

  In this case there are two columns listed in the `keys` parameter, which means each output row groups all the input rows with the same `discovery_method` and `status` values. For each such group several values are calculated and output in separate columns: number of entries, mean mass, standard deviation of mass, maximum radius, and number of entries with a non-blank radius.

    stilts tgroup in=dr16qso.fits
                  icmd='addcol -units yr year (int)mjdToDecYear(mjd)'
                  keys='year'
                  aggcols='z;ngood;nz'
                  aggcols='z;array;z_values'
                  ocmd='addcol z_q1 quantile(z_values,0.25)'
                  ocmd='addcol z_q2 quantile(z_values,0.50)'
                  ocmd='addcol z_q3 quantile(z_values,0.75)'
                  ocmd='delcols z_values'
                  out=zq-by-year.fits

  This groups the rows in an input table by calendar year, and for each year calculates the number of non-blank items, and the quartiles, of the values in the `z` column. Since no quartile aggregator is provided, aggregation is first done to an array of all the z values per year, and quartiles are calculated using the quantile function, which takes an array value as well as the quantile point required. Before writing the output, the bulky array column is removed.

  In this example, the `aggcols` parameter has been given three times for the three aggregated values. This is just another way to specify multiple entries, and it could equally have been written in one go with a space as a delimiter, i.e. "`aggcols='z;ngood;nz z;array;z_values'`".

  Note that since the grouped quantity here is numeric and evenly spaced, this job could equally have been done using the `tgridmap` command, like

          stilts tgridmap in=dr16qso.fits
                          icmd='addcol -units yr year mjdToDecYear(mjd)'
                          coords=year binsizes=1
                          cols='1;count;nz z;Q1;z_q1 z;median;z_q2 z;Q3;z_q3'

Next Previous Up Contents
Next: tjoin: Joins multiple tables side-to-side
Up: tgroup: Calculates aggregate functions on groups of rows
Previous: Usage

STILTS - Starlink Tables Infrastructure Library Tool Set
Starlink User Note256
STILTS web page: http://www.starlink.ac.uk/stilts/
Author email: m.b.taylor@bristol.ac.uk
Mailing list: topcat-user@jiscmail.ac.uk