Skip to main content

where

The where method filters rows of data from the source table.

note

The engine will address filters in series, consistent with the ordering of arguments. It is best practice to place filters related to partitioning and grouping columns first, as significant data volumes can then be excluded. Additionally, match filters are highly optimized and should usually come before conditional filters.

Syntax#

table.where()table.where(filterCriteria...)

Parameters#

ParameterTypeDescription
filterCriteriaString...

Formula for filtering.

Filters can be:

  • NULL - will produce a result that includes all rows from the table. This syntax will bring the entire table into memory, identically to select.
  • A match filter.
  • A conditional filter.

Returns#

A new table with only the rows meeting the filter criteria in the column(s) of the source table.

Examples#

The following example creates a new table with all the values from the source table, use where with no argument.

from deephaven.TableTools import newTable, stringCol, intCol, doubleColfrom deephaven.conversion_utils import NULL_INT
source = newTable(    stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),    intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),    stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),    intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),)
result = source.where()

The following example returns rows where Color is blue.

from deephaven.TableTools import newTable, stringCol, intCol, doubleColfrom deephaven.conversion_utils import NULL_INT
source = newTable(    stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),    intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),    stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),    intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),)
result = source.where("Color = `blue`")

The following example returns rows where Number is greater than 3.

from deephaven.TableTools import newTable, stringCol, intCol, doubleColfrom deephaven.conversion_utils import NULL_INT
source = newTable(    stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),    intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),    stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),    intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),)
result = source.where("Number > 3")

Related documentation#