Aggregating Column Values in Tables

At this time, Table Totals are only available in Deephaven Classic.

For users who want to see aggregated column values in a table, such as the total sum of values in a column, or an average value of a particular column, Deephaven offers two options. Data aggregation can be accomplished through the Deephaven console using the Show Totals option in the right-click context menu, or it can be accomplished through the query language in a persistent query. To learn more about using Show Totals in the Deephaven console, see: Working With Tables > Show Totals.

The following section details how to configure totals using query methods.

Configuring Table Totals through a Query

A Grand Totals section can be added to the top of any table using the setTotalsTable method in a persistent query.

To build a table that presents aggregated values for one or more columns (filtered or not), users must first import the com.illumon.iris.db.v2.TotalsTableBuilder class. The TotalsTableBuilder class is used to define the default aggregations to be displayed in the Grand Totals and Totals section(s) of a particular table.

Example

The following example creates a table using data from the StockQuotes table in the LearnDeephaven namespace. The final generated table will include the Grand Totals section at the top of the table:

from deephaven import *

t1 = db.t("LearnDeephaven", "StockQuotes").where("Date=`2017-08-25`")
t2 = t1.setTotalsTable(TotalsTableBuilder()
    .setOperation("Bid", "Avg")
    .setOperation("Ask", "Avg"))

import com.illumon.iris.db.v2.TotalsTableBuilder
t1 = db.t("LearnDeephaven" , "StockQuotes").where("Date=`2017-08-25`")
t2 = t1.setTotalsTable(new TotalsTableBuilder()
        .setOperation("Bid", "Avg")
        .setOperation("Ask", "Avg"))

The first line tells Deephaven where to import the class needed for the query to run properly.
The second line of the query generates the t1 table using data from the StockQuotes table.
The third line of the query creates the t2 table by creating a new instance of the TotalsTableBuilder, chains together a series of operations to fully define the desired aggregation, and, finally, passes the result of the chaining to the setTotalsTable() method of the table.

In this example, the Grand Totals section (and Totals section if present) will display the average size of the Bid column and the average size of the Askcolumn. See below:

The query used for the example did not configure the setShowGrandTotalsByDefault argument to true (see the list of available methods below), so the Grand Totals section will appear in its collapsed state when the table is shown. The Totals section will only appear if the table is manually filtered.

The parameters set in the query become the Table Default However, they do not prevent individual users from customizing the aggregations in their workspace using the Edit Totals dialog. For more information about manually configuring the options available and restoring the default settings, please refer to Show Totals.

Available Methods

The following methods are included within TotalsTableBuilder:

Method	Description
`TotalsTableBuilder()`	Creates a new instance of a `TotalsTableBuilder`.
`TotalsTableBuilder.get(Table source)`	Creates a new instance of a `TotalsTableBuilder` from a specified `Table` source. Note that the `.get` method is unnecessary if your query already designates a source table in previous lines.
`addOperation("ColName", "Operation")`	Adds a column operation to the set of aggregations. You can select multiple aggregations on the same column by including distinct `addOperation` parameters; e.g, `addOperation("Bid," "Avg").addOperation("Bid," "Min")`. See the list of available aggregations below.
addOperation("ColName", "Operation", "Format")	Adds a column operation to the set of aggregations, and also a format by which to display the total value; e.g., "Decimal(`###,###.###`)". Note: only Date and Decimal are supported.
`setOperation("ColName", "Operation")`	Sets the aggregation for the specified column. Note that this will replace any other aggregations specified by `addOperation()`.
setOperation("ColName", "Operation", "Format")	Sets the aggregation for the specified column, and also a format by which to display the total value; e.g, "Date`dd?yyyy!MM-HH:mm:ss.SS %t`)". Note: only Date and Decimal are supported.
`setDefaultOperation("Operation")`	Sets the default aggregation to apply to columns that have not been explicitly set. If the query writer does not choose an alternative, the default aggregation is `Sum`.
`setFormat("ColName","Operation","Format")`	Sets the format for the designated column and operation. If "Operation" is set to an asterisk ("`*`"), the specified format string will be applied to all aggregation types for that column.
`setShowGrandTotalsByDefault(<true or false>)`	Instructs Deephaven to expand (`true`) or collapse (`false`) the Grand Totals section when the table is initially displayed. This will not override a user's workspace settings.
`setShowTotalsByDefault(<true or false>)`	Instructs Deephaven to expand (`true`) or collapse (`false`) the Totals bar when the table is initially displayed. This will not override a user's workspace settings.
`setTotalsTable()`	Passes the result of the specified operations to the table.

Available Aggregation Operations

The following aggregation operations may be used:

Aggregation Operation	Description
`First`	Display the first value of the column
`Last`	Display the last value of the column
`Min`	Display the minimum of the column
`Max`	Display the maximum of the column
`Sum`	Display the total sum of the column
`Avg`	Display the total average of the column
`Std`	Display the standard deviation of the column
`Var`	Display the variance of the column
`Count`	Display the number of non-null items present
`Skip`	Do not display any aggregation information for this column

Available Formatting Options

Only Date and Decimal are supported as arguments within the in TotalsTableBuilder methods. The expressions are the same those used to format columns in a table.

See: Formatting Tables.

Last Updated: 16 February 2021 18:07 -04:00 UTC Deephaven v.1.20200928 (See other versions)