Pivot Widgets

Just like the pivot tables you're familiar with from popular spreadsheet software, Deephaven's Pivot Widget feature enables you to rearrange and aggregate a large detailed dataset into an at-a-glance summary with one straightforward line of code. Within a single query you can display multiple values, embed auto-filters, and include Grand Totals of your chosen aggregations. Moreover, Pivot Widgets can be combined with Deephaven's OneClick and Linked Views features.

For example, the Pivot Widget below shows a breakdown of the prices of trades for each underlying security based on the Exchange on which they were traded:

Configured via a Deephaven query, the widget "pivots" the values from a "Header Column" - in this case Exchange - from the source table to become the new header titles in the widget. There are two main sections - the Row table (highlighted in red) derived from the Sym column, and the Value table (highlighted in yellow) containing aggregations from the Price column - and an optional Grand Totals section (highlighted in blue).

Read on to learn how to configure a Pivot Widget using the Deephaven query language.

Basic syntax

To create a Pivot Widget, you must first import the Deephaven PivotWidgetBuilder class then apply the pivot() method to the source table. The basic syntax follows:

import com.illumon.iris.console.utils.PivotWidgetBuilder

widget = PivotWidgetBuilder.pivot(t, "rowCol", "headerCol", "valueCol").show()

from deephaven import *

widget = PivotWidgetBuilder.pivot(t, "rowCol", "headerCol", "valueCol").show()

The query first creates a new instance of the PIvotWidgetBuilder class, applies the pivot() method to the source table (t), and then defines the three required arguments:

rowCol - This column specifies the key values for the Pivot Widget rows, which appear in the Row pane at the left side of the widget. One Row Column is required, but additional Row Columns can be set if desired.
headerCol - This column from the source table is pivoted to become the new column headers in the widget's Value pane. Only one Header Column can be set.
valueCol - These columns produce the values that will display in the main Value pane in the widget. Only one set of data can be displayed at one time. When multiple Value Columns are assigned in the query, the widget will include a drop-down list of the available choices.

The show() method is then applied so the widget can be presented in the Deephaven console.

The following example query demonstrates how to create a simple Pivot Widget using data from the StockTrades table in the LearnDeephaven namespace. This widget presents a new row for each unique USym and a new header title column for each unique Exchange value. The value cells are then populated with arrays containing the Last values for each key/header pair.

import com.illumon.iris.console.utils.PivotWidgetBuilder

t=db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-21`")
w1 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last").show()

from deephaven import *

t=db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-21`")
w1 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last").show()

Add additional row columns

The previous query used only one Row Column for the Row pane. However, additional Row Columns can be added to the widget using the addRows() method:

.addRows("rowCol")

The following query creates a Pivot Widget using data from the table StockQuotes in the LearnDeephaven namespace. In this example, two Row Columns are included in the Row pane:

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

w2 = PivotWidgetBuilder.pivot(t2, "USym", "Exchange", "Bid")
        .addRows("Status")
        .show()

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

w2 = PivotWidgetBuilder.pivot(t2, "USym", "Exchange", "Bid") \
    .addRows("Status") \
    .show()

Add additional value columns

You can add multiple Value Columns to any Pivot Widget in Deephaven using the addValueColumns() method:

.addValueColumns("valueCol" , valueCol")

Only one set of values can be viewed at a time. When additional Value Columns are present in the query, a drop-down list at the top of the widget will enable users to view their chosen set of values.

The example below creates a Pivot Widget that enable users to switch among two Value Columns chosen from the source table, t (StockTrades). The first Value Column is included as an argument to the pivot() method. The second Value Column is included as the argument to the addValueColumns() method.

w3 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last")
        .addValueColumns("Size")
        .show()

w3 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last") \
    .addValueColumns("Size") \
    .show()

Add aggregations

By default, a Pivot Widget displays the data from the chosen Value Column as an array. The data in these arrays can be aggregated by adding either sum() or avg() to the query.

The example below creates a Pivot Widget that averages the values in the Exchange column arrays for each unique USym:

w4 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last")
        .addValueColumns("Size")
        .avg()
        .show()

w4 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last") \
    .addValueColumns("Size") \
    .avg() \
    .show()

Add Grand Totals

If an aggregation operation is included in the query for a Pivot Widget, the data for each row and/or each column can be further summarized into Grand Totals.

The following methods are available, and can be used individually or collectively:

across() - Using this method will add a Grand Total pane to the right side of the widget. Data in that pane will be aggregated by row.
down() - Using this method will add a Grand Total row to the bottom of your widget. Data in that row will be aggregated by column.

The example query below includes a Grand Total pane and Grand Total row that sums the values in the widget rows and columns, respectively:

w5 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last")
        .addValueColumns("Size")
        .sum()
        .across()
        .down()
        .show()

w5 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last") \
    .addValueColumns("Size") \
    .sum() \
    .across() \
    .down() \
    .show()

By default, the data in the Grand Total pane will be aggregated using the same aggregation method as the value table. However, you can specifically set the aggregation for the Grand Totals, which may differ from the aggregation set for the value table, by including one of the following additional methods:

sumTotals() - Data in the Grand Total pane will sum the values in each row or column.
avgTotals() - Data in the Grand Total pane will average the values in each row or column.

The example query below sums the pivot widget and includes a Grand Total pane and Grand Total row that averages the values in the widget rows and columns, respectively.

w5 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last")
        .addValueColumns("Size")
        .sum()
        .across()
        .down()
        .avgTotals()
        .show()

w5 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last") \
    .addValueColumns("Size") \
    .sum() \
    .across() \
    .down() \
    .avgTotals() \
    .show()

The Grand Total pane on the right now shows the averaged values of each row. The Grand Total row at the bottom now shows the averaged values of each column.

Add filter columns

Deephaven AutoFilters can be added to your Pivot Widget using the following syntax:

.addFilterColumns("colName")

There are no restrictions on which columns can be set as Filter columns. The example below includes two AutoFilters that generate a list from the values in the USym and SaleCondition columns respectively in the source table:

w6 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last")
        .addValueColumns("Size")
        .addFilterColumns("USym", "SaleCondition")
        .sum().across().down()
        .show()

w6 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last") \
    .addValueColumns("Size") \
    .addFilterColumns("USym", "SaleCondition") \
    .sum().across().down() \
    .show()

Selecting SaleCondition at the top of the widget opens the AutoFilter dialog window, which contains the distinct values available within the source table's SaleCondition column:

Select the desired value(s) and click Ok.

The data in the Pivot Widget will filter accordingly. Note: the name of the AutoFilter will be highlighted in blue when active, as shown below:

Fetch size

The default initial fetch size in the AutoFilter dialog is 1,000 unique items. This can be changed by including the following method in the Pivot Widget query:

.autoFilterFetchSize(filterColumn, initialFetchSize)

In the following example, the query for table w6 has been updated to limit the fetch size on the SaleCondition AutoFilter to five items.

w6A = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last")
        .addValueColumns("Size")
        .addFilterColumns("USym", "SaleCondition")
        .autoFilterFetchSize("SaleCondition", 5)
        .sum().across().down()
        .show()

w6 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last") \
    .addValueColumns("Size") \
    .addFilterColumns("USym", "SaleCondition") \
    .autoFilterFetchSize("SaleCondition", 5) \
    .sum().across().down() \
    .show()

Tip

The default fetch size also can be globally by modifying the property:

AutoFilterSelectionPanel.defaultInitialFetchSize=[size]

Additionally, modifying the maximum cache size will keep selected values in the cache so that they always appear in the AutoFilter dialog regardless of the fetch size.

AutoFilterSelectionPanel.maxCacheSize=[size]

This property defaults to 100. Cached items appear first (even after they become unselected).

These should be configured in the iris_console and interactive_console stanza.

Require filter columns

Creating Pivot Widgets with a large amount of data may take time to calculate. This time can be reduced by requiring the Pivot Widget to first be filtered on one or more of the columns in the table using the requireFilteredColumns() method in the query:

.requireFilteredColumns("colName")

Before the Pivot Widget is created in the Deephaven console, all columns specified in the method's argument must be filtered using an AutoFilter or OneClick filter.

w7 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last")
        .addValueColumns("Size")
        .addFilterColumns("SaleCondition")
        .requireFilteredColumns("Sym")
        .sum().across().down()
        .show()

w7 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last") \
    .addValueColumns("Size") \
    .addFilterColumns("SaleCondition") \
    .requireFilteredColumns("Sym") \
    .sum().across().down() \
    .show()

After the query runs, the user will be prompted to filter the Sym column, which can be accomplished using an AutoFilter or a OneClick filter as shown below:

Add a First or Last aggregator

You can apply a first() or last() aggregator to the Pivot Widget:

first() returns the first array value from the value column for the particular header value,
last() returns the last array value from the value column for the particular header value

For example, in the following query where the Pivot Widget is sorted on the "Last" column, applying a first() aggregation returns the minimum array value.

import com.illumon.iris.console.utils.PivotWidgetBuilder
t=db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-21`")

pwNone = PivotWidgetBuilder.pivot(t.sort("USym", "Exchange", "Last"), "USym", "Exchange", "Last").show()

pwFirst = PivotWidgetBuilder.pivot(t.sort("USym", "Exchange", "Last"), "USym", "Exchange", "Last")
    .first()
    .show()

from deephaven import *
t=db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-21`")

pwNone = PivotWidgetBuilder.pivot(t.sort("USym", "Exchange", "Last"), "USym", "Exchange", "Last").show()

pwFirst = PivotWidgetBuilder.pivot(t.sort("USym", "Exchange", "Last"), "USym", "Exchange", "Last")\
    .first()\
    .show()

In the next example, applying a last() aggregation returns the maximum array value.

pwLast = PivotWidgetBuilder.pivot(t.sort("USym", "Exchange", "Last"), "USym", "Exchange", "Last")    .last()    .show()

pwLast = PivotWidgetBuilder.pivot(t.sort("USym", "Exchange", "Last"), "USym", "Exchange", "Last")\    .last()\    .show()

Specify ordering

Apply a Sort Order

Various sort orders can be added to your query using the following methods:

sortRows(columnName) - Sorts in ascending order on a specified column within the main value table or row pane.
sortRowsDescending(columnName) - Sorts in descending order on a specified column within the main value table or row pane.
sortRowsByTotals() - Sorts in ascending order on the total column if it is included within the query.
sortRowsByTotalsDescending() - Sorts in descending order on the total column if it is included within the query.
sortColumnTitles() - Sorts the Header Titles/columns in ascending order.

Specify Column Order

Pivot Widget "row" columns are ordered based on occurrence of their values in the underlying table. However, you may specify column ordering in your query without have to re-sort the source table, though this is recommended in cases where values are well-known. The following methods are available:

sortRowsCustom(firstKey) - takes a list of values that define the first item that should be displayed. The number of parameter items depends on the number of row keys.
- If you are only pivoting on USym, there should only be a single parameter: e.g., .sortRowsCustom("AAPL")
- If you are pivoting on "USym" and "Exchange" you should provide a pair of values: e.g., .sortRowsCustom("AAPL", "NYSE")
- After this method is invoked, it may be followed by as many invocations of followedBy(...) as required to continue defining the order.
- Invoke .done() to return to the PivotWidgetBuilder.
sortColumnsCustom(firstKey) - follows the same rules as illustrated above, except that it controls the column ordering instead of the row ordering. These methods may be combined, as in the example query below.

import com.illumon.iris.console.utils.PivotWidgetBuilder

t=db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-21`")

CustomSort = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last")
    .addValueColumns("Size")
    .addFilterColumns("Exchange")
    .sum().across().down()
	.sortRowsCustom("AAPL")
		.followedBy("BAC")
		.followedBy("INTC")
		.followedBy("AXP")
		.followedBy("CSCO")
	.done()
	.sortColumnsCustom("Nasdaq")
		.followedBy("EdgX")
		.followedBy("Arca")
		.followedBy("Internal")
	.done()
    .show()

from deephaven import *

t=db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-21`")

CustomSort = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last") \
    .addValueColumns("Size") \
    .addFilterColumns("Exchange") \
    .sum().across().down() \
	.sortRowsCustom("AAPL") \
		.followedBy("BAC") \
		.followedBy("INTC") \
		.followedBy("AXP") \
		.followedBy("CSCO") \
	.done() \
	.sortColumnsCustom("Nasdaq") \
		.followedBy("EdgX") \
		.followedBy("Arca") \
		.followedBy("Internal") \
	.done() \
    .show()

A new sorting icon will be displayed for rows and columns (*) that have been ordered by the query. These orders are used when the rows/columns are in the "unsorted" state. Changing the sorting order to Ascending or Descending will then sort the rows/columns in their natural ascending or descending order.

Rename header columns

In order to rename the Header title columns in a pivot widget, the .setColumnNameTransform method, which takes a Groovy closure, should be used. The syntax follows:

.setColumnNameTransform({value -> value.equals("colName") ? "newColName" : value})

For example, the following query shortens the name of the "Chicago" column in widget w6:

w8 = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last")
        .addValueColumns("Size")
        .addFilterColumns("USym", "SaleCondition")
        .sum().across().down()
        .setColumnNameTransform({value -> value.equals("Chicago") ? "Chic" : value})
        .show()

from deephaven import *
t = db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-21`")

renamer = PythonFunction(lambda value: "Chic" if value == "Chicago" else value, 'java.lang.String')
pwRename = PivotWidgetBuilder.pivot(t, "USym", "Exchange", "Last")\
   .addValueColumns("Size")\
   .addFilterColumns("USym", "SaleCondition")\
   .sum()\
   .across()\
   .setColumnNameTransform(renamer)\
   .down()\
   .show()

Format color

It is possible to adjust the color formatting of pivot widgets using the Deephaven query language:

.setColorFormat(Function<Object, Object> colorFormat)

The Closure in setColorFormat takes one of these three values:

Long: e.g., { val -> val > 0 ? 143833713130660064L : 0L}
Color: e.g., {val -> val < 100 ? COLOR_BLUE : COLOR_GREEN}
String value representing a Color : e.g., {val -> val < 100 ? "BLUE" : "GREEN"}

Both user-defined and predefined variables will work in the closure.

Heat Maps

The following method will display a heat map in the Pivot Widget:

.heatMap(boolean, Color min, Color max)

"True" is the default value.
The next two parameters are optional:
- "Color min" is used for lower values
- "Color max" for higher values. If not set, the heat map colors will be orange and blue by default. Otherwise, the coloration conforms to the user's choices in the setColorFormat method, as demonstrated in our examples.

Opening and closing Pivot Widgets

Pivot Widgets created by persistent queries can be accessed using the Show Widget button in the Query Config panel. Alternatively, if a Pivot Widget is created by a query running in the console and then closed, the Pivot Widget can be reopened in that session using the Show Widget button in the workspace.

Working with Pivot Widgets in the UI

Right-click menus

Each pane in the Pivot Widget has its own unique right-click menu. This section discusses the options available in these menus and how to perform basic operations like filtering and sorting data in a Pivot Widget.

Row right-click menu	Value right-click menu	Grand Total right-click menu
Header menu: Table data menu:

Filtering

A Pivot Widget can be filtered by:

adding AutoFilters to the query that creates it
configuring Linked Views
pairing the widget with a OneClick panel in the Deephaven console
using the Filter By Value option in its table data menu

Once a filter has been applied to the Pivot Widget, the Clear All Filtering and Clear Filter on... options become available in the applicable right-click menu.

The optional Grand Total pane will change according to any filters applied to the two core tables. It cannot be filtered individually. Note that selecting Clear All Filtering from its right-click menu will clear all filters within the widget.

Filter By Value

Right-clicking within a cell in the Row pane opens the table data menu. Select Filter By Value to access eight simple filter options:

Linked Views

This section discusses how to configure a Pivot Widget as either a link source (Make Link To...) or link target (Make Link From...). Pivot Widgets can be linked to or from any Deephaven table.

Note

Make Link To...

To properly configure Linked Views when the Pivot Widget is a link Source, you must link at least one of the Row Columns and the Header Column. Double-clicking on a cell in the Value pane will filter the link target so that its table data matches the values used to compute that cell value in the Pivot Widget.

In the following example, the link Source is Pivot Widget w6, and the link Target is the StockTrades table filtered to August 21, 2017:

Double-clicking a Pivot Widget cell in the GOOG row in the Nyse column filters StockTrades to show all the sizes that were averaged together to produce that Size value (a sum of 310):

Filters applied to Source tables and widgets are also linked to the respective Targets. For example, if the AutoFilter on the Source widget (w6) was active, its target StockTrades table would be filtered accordingly.

As shown below, the Pivot Widget is first filtered to three SaleCondition values, then double-clicking on the GOOG size value of 201 filters the source table:

Make Link From...

When the Pivot Widget is a Target for Linked Views, filters applied in the Source will also apply in the Target. If the Target column in the Linked View Filter Editor is a Row Column in the Target widget, clicking within the Source table will filter out entire rows from the Pivot Widget. If the Target column is a Header Column, clicking within the Source table will filter out columns from the Value pane.

OneClick filtering

OneClick filters can be used with Pivot Widgets in the same manner as with tables and plots. Click the OneClick button at the top of the console and nest the new OneClick panel with the widget you wish to filter. Then, choose a column to filter and set the desired value.

Note

Sorting

As with a typical Deephaven table, clicking a column header will Sort/Unsort that column.

In the Row pane, you can select Sort Row Columns Ascending, Sort Row Columns Descending, or Clear All Sorting.
In the Value pane, selecting Sort Header Titles Ascending or Sort Header Titles Descending will rearrange all the columns accordingly.
To revert the Value pane to its original order, select Clear Header Titles Sorting.
Selecting Clear All Sorting from any right-click menu will clear all sorts within the entire widget.
The optional Grand Total pane will automatically sort when the Row pane is sorted.
A null value (or any non-sortable) type in the main Value pane header will disable Header Title sorting.

Note

Column title sorting occurs pre-transform: i.e., the sort operation is performed before the specified Header columns are renamed. See Renaming Header Columns.

In the Pivot Widget below, the Header Titles/columns have been sorted in ascending order:

Formatting numeric data

It is possible to change the numeric format of the data within any of the panes within the Pivot Widget using the right-click options:

Format Integer
Format Decimal

Your selection will only apply to the pane within which you clicked to access the menu.

In this case, the Grand Totals pane may be formatted independently of the other two tables.

Note

See: Format

This may also be accomplished using the Deephaven query language. The following methods are available:

setMainDecimalFormat(String) - Sets the decimal format for the Value pane.
setRowDecimalFormat(String) - Sets the decimal format for the Row pane.
setTotalsDecimalFormat(String) - Sets the decimal format for the Totals pane.
setMainIntegerFormat(String) - Sets the integer format for the Value pane.
setRowIntegerFormat(String) - Sets the integer format for the Row pane.
setTotalsIntegerFormat(String) - Sets the integer format for the Totals pane.

Note

See: Numeric formatting

Pivot Widgets

Build a Pivot Widget

Basic syntax

Add additional row columns

Add additional value columns

Add aggregations

Add Grand Totals

Add filter columns

Fetch size

Require filter columns

Add a First or Last aggregator

Specify ordering

Apply a Sort Order

Specify Column Order

Rename header columns

Format color

Heat Maps

Opening and closing Pivot Widgets

Working with Pivot Widgets in the UI

Right-click menus

Filtering

Filter By Value

Linked Views

Make Link To...

Make Link From...

OneClick filtering

Sorting

Formatting numeric data