Basic Table Operations

This page covers the table operations that appear in almost every query: operations for column manipulation, filtering, sorting, grouping and aggregation, and combining tables. These operations use query strings extensively. The Query Strings chapter, later in this crash course, covers query strings in detail.

Table operations are integral to the Deephaven Query Language (DQL). Besides creating tables, the previous chapters of the crash course used five table operations:

• update_view, which adds columns to or modifies existing columns in a table.
• sum_by, which groups rows by the columns you name and sums every other column within each group.
• rename_columns, which renames columns in a table.
• sort, which sorts a table by the given columns from least to greatest.
• sort_descending, which sorts a table by the given columns from greatest to least.

You can think of these as different transformations applied to the data in a table. The rest of this page builds on them with the basic table operations that make up the backbone of the most common queries.

The operations on this page also work on ticking tables, such as the ones in Create Tables, the same way they work on the static tables used in these examples. Blink tables, ticking tables that keep only the rows added in the current update cycle, are the exception: a few operations on this page behave differently on them or aren't supported.

Many of the code blocks on this page use the following table, t, as the root table. This is a simple table with 100 rows and contains only a Timestamp column.

Basic column manipulation

Add columns

update creates in-memory columns. An in-memory column computes all of its values immediately and stores the results in memory. The following code block adds three columns to t. Most examples in the rest of this section and in the Filter and Sort sections use the result, t_updated.

• IntRowIndex: 32-bit integer row indices.
• LongRowIndex: 64-bit integer row indices.
• Group: modulo of IntRowIndex and 5.

update_view creates formula columns. A formula column stores only its formula. Deephaven computes each value on the fly when something reads it. Use update_view when calculations are simple or when queries read only small subsets of the data.

The following code block adds two new columns to t.

• IntRowIndex: 32-bit integer row indices.
• Group: modulo of IntRowIndex and 5.

lazy_update creates memoized columns. Like a formula column, a memoized column computes values on demand. Unlike a formula column, it caches the results. It computes the result for each unique set of input values only once and returns the cached result for later requests with the same inputs.

The following code block adds two new columns to t_updated.

• GroupSqrt: square root of Group.
• GroupSquared: square of Group.

Because Group has only five unique values, Deephaven performs only 10 calculations to populate the two new columns.

Select columns

select and view both create tables that contain only the columns you list: existing columns and new columns computed from them. They differ in the kind of column they create, which determines how much memory they allocate and when formulas are evaluated. Performance and memory considerations dictate the best method for a particular use case.

select, like update, creates in-memory columns. The following code block selects two existing columns from t_updated and adds a new column.

view, like update_view, creates formula columns. The following code block selects two existing columns from t_updated and adds a new column.

There's a lot to consider when choosing between in-memory, formula, and memoized columns in queries. For guidance, see Choose the right column selection method.

Drop columns

drop_columns removes columns from a table.

You can also use view and select to remove columns by omission. Both tables below drop IntRowIndex and LongRowIndex because the column list leaves them out.

Filter

By condition

where filters rows from a table based on a condition. The following code block keeps only rows in t_updated where IntRowIndex is an even number.

You can apply multiple filters in a single statement.

A where operation that applies multiple conditions keeps only rows that meet all of them. To keep rows that meet one or more of the conditions, use where_one_of.

In addition to where and where_one_of, Deephaven offers where_in and where_not_in, which filter table data based on another table.

See the filtering guide for more information.

By row position

Methods that filter data by row position keep only a portion of data at the top, middle, or bottom of a table.

• head: Keep the given number of rows from the top.
• head_pct: Keep the first fraction of rows, where 0.1 means 10%.
• tail: Keep the given number of rows from the bottom.
• tail_pct: Keep the last fraction of rows, where 0.1 means 10%.
• slice: Keep rows from a start position up to, but not including, an end position.
• slice_pct: Keep rows between two fractions of the table, such as 0.4 and 0.6.

See the filtering guide for more information.

Sort

You used sort in the previous chapter to order a table by one column. The following code block sorts t_updated by Group in ascending order.

You can also sort by more than one column.

To sort in descending order, use sort_descending.

To sort multiple columns in different directions, use sort with a SortDirection for each column.

See the sorting guide for more information.

Group and aggregate data

Grouping data places rows into groups based on zero or more key columns. Rows that have the same values in the key columns go into the same group. Aggregation calculates summary statistics over a group of data. Grouping and aggregation are key components of data analysis, especially in Deephaven queries. On a blink table, aggregations such as avg_by and agg_by cover every row the table delivers after the aggregation is created, not only the rows in the current update cycle.

The examples in this section use a new t with random X and Y columns plus Group and Letter columns to group by. It replaces the root table t from the top of this page.

Group and ungroup data

group_by groups table data into array columns. You can group an entire table.

You can also group data by one or more key columns.

ungroup expands array columns back into separate rows. It reverses group_by, except that rows come back in group order and any key columns come first.

See the grouping and ungrouping guide for more information.

Single aggregations

Single aggregations apply one type of aggregation to a table, either over the whole table or for each group. See the list of single aggregators.

The following code uses avg_by to calculate the average of columns X and Y in the table t. The code passes no key columns, so avg_by averages over the entire table.

Aggregations often operate on groups of data. The following example calculates the average of X and Y for each unique value in Group.

A single aggregation can also use multiple key columns.

Multiple aggregations

To apply multiple aggregations in a single operation, pass one or more of the aggregators into agg_by.

The following code block calculates the average of X and the median of Y, grouped by Group and Letter. It names the resulting columns AvgX and MedianY, respectively.

Cumulative and rolling aggregations

Deephaven also offers a large library of cumulative and rolling calculations. You use them through the update_by table operation and the deephaven.updateby Python module. Unlike avg_by and agg_by, which return one row per group, update_by keeps every row of the table and adds its results as new columns.

The following code block calculates the cumulative sum of X in t.

In the result of update_by, each row of SumX holds the running total of X from the first row through that row.

update_by can also limit these calculations to a window of rows. The following code block calculates the sum of the current row and the previous 9 rows in column X of table t.

update_by also supports performing these calculations over groups of data. The following code block repeats the cumulative sum and the 10-row rolling sum from the previous examples, this time grouped by Group and Letter.

You can also define windows by time. The following code block calculates a 16-minute rolling average of X, grouped by Group.

Windows can look backward, forward, or both ways. A forward-looking window counts the row ahead of the current row as the first row forward. The following example calculates rolling averages over these windows:

• The current row and the previous 9 minutes.
• The current row and the previous 8 rows.
• The current row, the previous 10 rows, and the next 10 rows.
• The next 8 rows.

See the update_by user guide to learn more.

Combine tables

Deephaven combines tables in two ways: merging and joining. You can visualize merging as stacking tables vertically, whereas joining is horizontal: it appends columns from matching rows of another table, based on values in key columns.

Each subsection below defines its own tables to demonstrate merging and joining tables in Deephaven.

Merge tables

merge combines any number of tables, as long as they all have the same schema: the same column names and data types. See the merge tables guide for more information.

Join tables

Joining combines two tables based on one or more key columns. Key columns hold the values that both tables share. The table you call the join method on is the left table, and the table you pass as an argument is the right table. The outer-join functions take the left table as l_table and the right table as r_table.

This page shows three kinds of join: exact joins, relational joins, and time-series joins. The exact and relational joins guide covers the first two in depth. The inexact, time-series, and range joins guide covers time-series joins and range joins.

Consider the following three tables.

The tables t1 and t2 have the common column Letter, and Letter contains matching values in both tables. The following example joins them on Letter with natural_join, which keeps every row of the left table and appends the columns of the matching row from the right table. Where the right table has no matching row, the appended columns are null. Exact joins covers natural_join in more detail.

Joins can use more than one key column. The tables t2 and t3 have both the Letter and Color columns in common, and they both have matching values. The following code block joins the two tables on both columns.

By default, the join operations on this page append all non-key columns from the right table onto the left table. The optional joins argument specifies which columns to append. The following code block joins t2 and t3 on the Letter column, but only appends the Value column from t3.

t2 and t3 share the Color column, so appending Color from t3 onto t2 results in a name conflict error. To avoid the error, rename the column with rename_columns before the join, or rename it in the joins argument.

The following example renames Color in t3 to Color2 in the joins argument.

Exact joins

Deephaven has two exact joins: natural_join, which you used above, and exact_join. Like natural_join, exact_join keeps every row of the left table. exact_join always raises an error if a key value appears more than once in the right table, and natural_join does so by default.

Consider the following tables.

t_left_1 and t_right_1 both have a Color column with the same data type. In t_right_1, Color has no duplicates. Additionally, all colors in t_left_1 have an exact match in t_right_1. In cases like this, exact_join is the most appropriate join operation.

The following tables are like the previous pair, except that t_left_2 contains the color Green, which is not in t_right_2.

Here, exact_join fails because it requires every key in the left table to have a match in the right table. Instead, use natural_join, which fills unmatched rows with nulls.

Relational joins

Relational joins are similar to SQL joins: a left-table row can match several right-table rows, and the result has one row for each match. The two outer joins on this page, left_outer_join and full_outer_join, also keep rows that have no match. Both are experimental, so their API may change.

Consider the following tables.

join keeps only rows whose key values match in both tables.

left_outer_join includes all rows from the left table as well as rows from the right table where a match exists. The result contains null values where no match exists.

full_outer_join includes all rows from both tables, regardless of whether a match exists. The result contains null values where no match exists.

Time-series (inexact) joins

Time-series (inexact) joins match on zero or more exact key columns plus a final key column that does not need to match exactly. The final key column is usually a timestamp. If the right table has no row that qualifies, the appended columns are null.

Consider the following tables, which contain quotes and trades for two different stocks.

aj matches each row in the left table with the closest row in the right table without going over. To see the most recent quote at or before a trade, use aj.

raj matches each row in the left table with the closest row in the right table without going under. To see the first quote at or after a trade, use raj.

Next, Time and Calendars shows how to work with date-time data and business calendars in these operations.