Create Roll-ups and Tree Tables

If you have a large, detailed dataset it is helpful to quickly drill down to specific information or pull out aggregations for easy analysis. In this tutorial, we demonstrate two options: Roll-up tables and Tree tables.

Roll-up tables

Roll-up Tables group your data according to a specified key value (like Sym) and show certain, aggregated column values.

For example, we can "roll-up" the StockQuotes table by Sym at the top level, and Exchange at the sub-level into a table, and include four columns with aggregations specified in the query:

from deephaven import *

t1=db.t("LearnDeephaven", "StockQuotes").where("Date=`2017-08-25`")

# first use the rollup method then the AggCombo method to define multiple aggregated columns
t2=t1.rollup(caf.AggCombo(caf.AggMin("Ask_Min=Ask"),\
    caf.AggAvg("AskSize_Avg=AskSize"),\
    caf.AggMax("Bid_Max=Bid"),\
    caf.AggAvg("BidSize_Avg=BidSize")),\
"Sym", "Exchange")

img

Let's break down that query:

  • The second line of the query applies the .rollup method.
  • Then we chain a series of aggregations using the AggCombo method. The aggregation AggMin("Ask_Min=Ask") obviously calculates the minimum ask price, but also defines a column name. Make sure each column has a unique name. Find the list of available operations here.
  • Finally, the query specifies the two columns by which to group data.

As with any other tables created in Deephaven, the columns in the Roll-up Table can also be sorted, moved, or hidden in the UI. However, any changes to the aggregation parameters must be made in the original query.

Note that Roll-up tables can also be created from the table data menu context menu.

Tree Tables

While Roll-up tables create a table hierarchy and display only certain aggregated data, Tree Tables include all your data but allow you to expand or collapse "branches" of the table as needed. In essence, these tables create a navigation menu: clicking on the arrow in a root row opens the data for that particular branch.

To demonstrate, we'll source our data from the StockTrades table and prepare the data by creating separate tables for our keys - Sym and Date. These get merged with a data table, to ultimately form one Tree Table.

from deephaven import *

# create a parent table to house unique Syms

syms= db.t("LearnDeephaven" , "StockTrades").where() \
    .firstBy("Sym") \
    .updateView("ID=Sym","Parent=(String)null","Date=(String) null", "Timestamp=(com.illumon.iris.db.tables.utils.DBDateTime) null", "SecurityType = (String) null", "Exchange=(String)null","Last=(Double) null","Size=(Integer)null","Source=(String)null","ExchangeId=(Long) null","ExchangeTimestamp=(DBDateTime)null","SaleCondition=(String)null", "USym=(String)null")

# create a parent table to house unique Dates

dates = db.t("LearnDeephaven","StockTrades").where() \
    .firstBy("Sym","Date") \
    .updateView("ID=Sym+Date", "Parent=Sym", "Timestamp=(com.illumon.iris.db.tables.utils.DBDateTime)null", "SecurityType=(String)null", "Exchange=(String)null", "Last=(Double) null", "Size=(Integer)null","Source=(String)null", "ExchangeId=(Long) null", "ExchangeTimestamp=(DBDateTime)null", "SaleCondition=(String)null","USym=(String)null")

# prepare the data

data = db.t("LearnDeephaven","StockTrades").where() \
    .updateView("ID=Long.toString(k)","Parent=Sym+Date")

# combine the parent tables and the data table

combo = ttools.merge(dates,syms,data)

#create the actual tree table

comboTree = combo.treeTable("ID","Parent")

img

This Tree Table organizes all five partitions of the StockTrades table, first by the 10 distinct Symbols, then by the five distinct dates.

To dissect this query a bit further, we can see that:

  • The first part of the query creates a syms table, using the firstBy method to find each distinct Sym and return the first row for each group. The updateView method creates a new ID column and Parent column. The ID column specifies the parent value that children rows will have to match. Parent column values are null because this table contains the root rows of the Tree Table (the rows that open each table level).
  • The second part of this query creates another parent table, dates, that also uses the firstBy method to find each distinct set of values for the columns listed in the argument. In this case, the arguments are Sym and Date.
  • The dates table sets Parent to the ID value of its parent, Sym, because this will be a sub-level of the root rows in the syms table. However, as a sublevel, ID will be set to the Parent value joined with its own unique identifier, Date. Then, the other columns are set to null so that no other data will be displayed.
  • Now we create our table of data. The Parent column is set to the ID value of Sym+Date, but the ID column will still be a row number as a string.
  • Finally, we combine all three of the tables together in the combo table, and use the treeTable method with our two arguments, ID and Parent, to create comboTree.

Note

See: Tree Tables