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

B.34.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)'
              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'
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:
Author email:
Mailing list: