Filter table data

This guide covers filtering table data in Deephaven. Many different table operations filter data in a table. Some keep rows based on filter expressions, whereas others keep rows based on values in another table or on row positions.

Deephaven filters work on live data. When a source table ticks — which means rows are added, removed, or modified — Deephaven keeps each filtered result up to date, so a filter you write once keeps working as the data changes.

Filters in query strings covers the filter expressions themselves in more depth, including includes string filters, NaN handling in range filters, and filter performance.

These table operations keep rows that match one or more filter expressions:

Conditional filtering and Combine filters show how to use them.

These table operations keep rows based on matching values in one or more columns of a separate table:

Filter one table based on another shows how to use them.

These table operations keep rows based on row position:

Filter by row position shows how to use them.

The last section shows how to use an incremental release filter to release a table's rows over time, which simulates ticking data.

The examples on this page filter the Iris dataset from Deephaven's example repository, which the following code block imports:

Conditional filtering

Conditional filtering keeps the rows that meet one or more filters. A filter is usually a query-string expression, and any expression that evaluates to true or false works. Deephaven keeps a row when the filter is true and excludes it when the filter is false. The common kinds of filters are:

Match filters

A match filter tests one of the following conditions:

  • Equality (= and ==): If a value is equal to another value, the result is true. Otherwise, the result is false.
  • Inequality (!=): If a value is not equal to another value, the result is true. Otherwise, the result is false.
  • in: If a value exists within a set of values, the result is true. Otherwise, the result is false.
  • not in: If a value does not exist within a set of values, the result is true. Otherwise, the result is false.
  • icase in: The same as in, but ignores capitalization of letters. Only use this to filter string columns.
  • icase not in: The same as not in, but ignores capitalization of letters. Only use this to filter string columns.

The following code block applies an equality filter and an inequality filter:

The following code block applies in, not in, icase in, and icase not in:

Range filters

Range filters keep rows where values fall within a specified range. Range filters use the following comparison operators:

  • <: Less than
  • <=: Less than or equal to
  • >: Greater than
  • >=: Greater than or equal to

The inRange function checks whether a value falls in a given range, including both endpoints.

The following code block applies each operator and the inRange function to filter data:

String filters

Deephaven stores strings as Java strings. Any Java String method that returns a boolean can filter string data. Methods such as startsWith, endsWith, contains, and matches are particularly useful. Deephaven's includes any and includes all filters match one or more substrings without a method call. For details, see String filters in the filters guide.

The following code block applies these methods to filter the iris table on its string column, Class:

Regular expression (regex) filters

The matches method in the previous section already takes a regular expression, but the expression must match the whole string. To build a regex filter as an object rather than in a query string, use the pattern function in Deephaven's filters submodule, and pass the filter it returns to where. Its FIND mode matches the expression anywhere in the string, so the expression needs no .* at either end.

When performing regex filtering with pattern, you apply a specific pattern mode from PatternMode. Two pattern modes exist:

  • FIND looks for a subsequence match in the string.
  • MATCHES matches an entire string against the given pattern.

The following code block performs regular expression filtering on the iris table with both pattern modes. The first finds strings that are eleven characters long, whereas the second looks for the subsequence virginica in the Class column:

Combine filters

You can combine multiple filters to filter data in a table. These combinations can be either conjunctive or disjunctive. The order and structure of combined filters can affect performance. For details, see Filter performance.

Conjunctive

Conjunctive (AND) filtering returns a table where all filters in a where call return true. Achieve conjunctive filtering by passing a list of filters to the filters parameter of where, or by using the && operator in a single filter.

The following example applies a conjunctive filter to the iris table to produce a new table of only Iris setosa flowers with a petal length in a specific range.

Disjunctive

Disjunctive (OR) filtering returns a table where one or more of the filters return true. You can accomplish this by using the || operator in a where call, or by using where_one_of to combine multiple filters.

In the following example, two filters work disjunctively to return a new table where the petal length is greater than 6.0 cm or the petal width is less than 0.2 cm.

Filter one table based on another

The where_in and where_not_in methods filter one table based on another table. A where result updates when its source table changes. A where_in or where_not_in result also updates when the filter table changes. The following example uses where_in and where_not_in to find Iris virginica petal widths that match and do not match Iris versicolor petal widths:

In this example, virginica is the table being filtered (the left table) and versicolor is the filter table (the right table).

Caution

where_in and where_not_in are inefficient if the filter table updates frequently.

Tip

where_in keeps or removes rows and never adds columns. It keeps every left-table row whose values in the match columns appear anywhere in the filter table, however many times they appear.

To add columns from the right table, use an exact or relational join instead. By default, natural_join raises an error when more than one right-table row matches a left-table row. join allows any number of matches.

Filter by row position

Filtering by row position removes unwanted data at the beginning, middle, or end of a table. You can specify row positions as absolute values or as a percentage of the total table size.

The following example uses head, slice, and tail to keep the first 10 rows, the middle 10 rows (positions 70 through 79 of the 150-row table), and the last 10 rows of iris.

The following example uses head_pct, slice_pct, and tail_pct to keep only 10% of the rows at the beginning, middle, and end of the table, respectively.

Simulate ticking data with an incremental release filter

The filters earlier on this page keep a fixed set of rows from the static iris table. The incremental_release filter instead releases the rows of a static or add-only table over time. The result is a ticking table, which is a table whose rows change as Deephaven processes updates.

Use it to simulate ticking data during development, or to limit the number of rows a complex query processes at one time.

The incremental_release filter takes two parameters:

  • The initial number of rows to present in the resulting table.
  • The number of rows to release at the beginning of each update graph cycle, which is one round in which Deephaven processes table updates.

iris_incremental starts with the first 10 rows of iris and gains 5 rows each update graph cycle until all 150 rows are released.

The IncrementalReleaseFilter Javadoc contains more details.