Tree Tables

A Tree Table provides a convenient way to display your data by allowing you to expand or collapse "branches" of the table as needed. When fully collapsed, the "tree" component resembles a navigation menu: clicking on the arrow in a root row opens the data for that particular branch.

The following section details how to create Tree Tables using the Deephaven query language. The examples below are also available for download as premade Deephaven Notebooks for Groovy or Python consoles. To learn more about using Deephaven Notebooks, please refer to the Notebook section of the User Manual.

Download the Tree Table Notebooks

Creating a Tree Table

Before you can create a Tree Table, you must first prepare your data set so that the query imposes a hierarchical order.  Your data set must have an ID column that uniquely identifies each row and a Parent column that indicates the parent of each row. If the value in the parent column is null, then the row is at the top level of the hierarchy.

A Tree Table can display any hierarchical dataset; the only constraint that Deephaven requires is the unique ID and then mapping to a Parent.

For example, we can start with a table of orders with a two-level hierarchy: parent orders and child orders. In this case, the hierarchy is already determined by existing values in the table. (Note:  The example order tables shown below have been greatly simplified so we can focus more on the concepts used to create Tree Tables.)

Order Table

In the previous table, the values found in the OrderID column are unique for all orders. This makes this column a good candidate to use for the unique ID column. The values in the ParentOrderID give us information needed to establish the relationship between child and parent orders. This is done by setting the child's  Parent column equal to the value in the ParentOrderID.  To establish parents as top-level nodes, the values in the parent's Parent column must be set to null.

Resulting Tree Table

Examples

In the following example, a Tree Table is created by first preparing tables that identify unique parent values that are then merged with the actual data using a SmartKey data structure (an object that allows you to use multiple values as your key) for row and parent identification.

Example 1

The following example creates a simple Tree Table branched by Sym. The original dataset being used comes from the StockTrades table in the LearnDeephaven namespace.  We must first prepare the data by creating a new table that determines and aggregates the data for our chosen parent (Sym).  We then need to add the additional ID and Parent columns into the actual data table we want to display.  Since the StockTrades table does not already contain a hierarchical order, it is by merging these tables that we create a data set to which we can apply the .treeTable() method.

from deephaven import *

syms = db.t("LearnDeephaven","StockTrades") \
    .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")

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

combo = ttools.merge(syms,data)

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

syms = db.t("LearnDeephaven","StockTrades")
        .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")

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

combo = merge(syms,data)

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

Let's walk through the query step by step.

The first part of the query creates the syms table using data from the StockTrades table. This is the table that will create the structure for the tree components.

• The firstBy method finds each distinct value or set of values for the column listed in the argument (Sym), groups the rows containing the same distinct value, and then returns 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). In this case, Sym does not have a parent of its own. Note: the ID and Parent columns can be named anything you'd like. 

The updateView method also sets all the remaining columns in the table as null so that they will not display any information in the resulting table. You must specify the data type for each column when casting the null value.  For example, the Exchange column contains string values, while the Size column contains integers.

The second part of the query creates the actual data table.  This is the data that opens for the user when they expand the branches of the table.

• The updateView method creates an ID and Parent column; this is required for Deephaven to later map the children rows to their appropriate parent branches after the tables are merged.
• The ID must be a unique value, such as k, which refers to the index of a row in the table.  ID = Integer.toString(k) ensures that the ID values across all tables are the same type, in this case Strings. Parent=Sym specifies the parent branch that the rows belong to.

The third part of the query creates the combo table that merges the rows from syms and data. The combo table contains all of the rows that the Tree Table will contain, except without the functionality of the branch structure. The ten distinct root rows appear in the beginning of the table. There are ten root rows because there are only 10 unique Syms in this table.

The final part of the query actually creates the Tree Table using the treeTable method defined with the ID and Parent columns as its arguments.

Note: The treeTable() method must be the last method called on the table.

To open a section of the Tree Table, click the right-facing arrow () in the root row, or press Enter/Spacebar.

When a branch of the tree is open (notice that the arrow now faces downward () we see the ID column includes the row number, and the Parent column includes the ID value of the root row, in this case the distinct Sym, which is AAPL. (Note: Some columns, including USym, Source, and SaleCondition, were manually hidden in these screenshots to better present the concepts being shared.)

Example 2

A Tree Table can include multiple branching levels as long as each level is assigned a unique identifier. The following query creates a similar table to the one shown above.  However, in this example, the table first branches by Sym, and then again to a sublevel of Date.

from deephaven import *

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")

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")

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

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

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

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")

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")

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

combo = merge(dates,syms,data)

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

As before, the first part of the query creates the same syms table.

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.

When we click on the root row GOOG, we now see rows for each individual Date in the table; expanding the row for August 22, 2017 shows the data associated with Google on that date.

The ID column shows the unique identifier for each level: Sym for the root level, Sym+Date for the first sublevel, and the row number for the second sublevel. The Parent column shows the level to which each child level applies.

Working with Tree Tables

Filtering and sorting Tree Tables works in the same fashion as any other table. Remember that the sections are not actually distinct partitions, so any sort applied to one section affects the whole table. Using our previous example, if the tree for AAPL is expanded and we then apply a sort on the Exchange column, the rest of the table branches in the table will also be sorted on the Exchange column. If the table is filtered to one particular Sym, only its root row will be available in that table. AutoFilters may be inherited from the parent table, or applied directly to the Tree Table.

Note: column header tooltips on Tree Tables do not currently support statistics.

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

Deephaven Documentation     Copyright 2016-2020  Deephaven Data Labs, LLC     All Rights Reserved