Null, NaN, and infinity values

Null, not-a-number (NaN), and infinity values can unexpectedly appear in your datasets and disrupt calculations if you don't handle them. In a real-time table, any update can bring in a new one, so it pays to handle them in the query itself.

This guide shows you how to keep your data analysis running smoothly. It lists the constants Deephaven provides for these special values, then shows how built-in functions treat them and how to replace them, filter them out, and check for them in your own Python functions.

Nulls represent missing data. For a primitive numeric type or char, Deephaven reserves one value of the type as its null. For example, it reserves -32768 as the null value for the primitive short data type, so the minimum short value is -32767 instead of the traditional -32768. Booleans and object types use Java null instead.

NaNs represent undefined mathematical results. For instance, the logarithm of a negative number is undefined, so log returns NaN for a negative input.

Infinities represent mathematically infinite values. For example, in floating-point arithmetic, dividing a positive number by 0.0 produces positive infinity.

Available constants

Deephaven provides a null constant for each primitive numeric type and char, plus NULL_BOOLEAN, which equals Java null, for Boolean columns. Only float and double have NaN and infinity constants.

Each constant comes in two copies. Use the built-in constants in query strings, and use the deephaven.constants module in Python code. In each subsection below, the first example prints the Python values, and the second uses the built-in constants in a query string and shows the column types with meta_table.

Nulls

The following null constants are available in Deephaven:

For float and double, the null value printed above is the most negative finite value of the type. The smallest non-null finite value is one step above it. For double, that value is MIN_FINITE_DOUBLE.

NaNs

The following NaN constants are available in Deephaven:

Infinities

The following infinity constants are available in Deephaven:

Handle null, NaN, and infinity values

Each example in this section builds columns that hold special values in selected rows. To choose those rows, the examples use the ternary conditional operator with ii, the built-in variable that holds the row position.

Built-in functions

The functions built into the query language accept null, NaN, and infinity values without extra checks in your query, but they treat each kind differently:

  • Null: most functions skip null inputs or return null.
  • NaN and infinity: functions follow IEEE floating-point rules, so a NaN or infinite input usually gives a NaN or infinite result.

For example, avg skips null values but returns NaN for an array that contains a NaN.

The following example passes each kind of value to the built-in sin function. Every fourth row holds the special value:

Where the input is null, Y1 is null. Where the input is NaN or infinite, Y2, Y3, and Y4 are NaN.

Arithmetic operators in query strings treat nulls the same way: if either operand is null, the result is null. To test for a null, use X == NULL_DOUBLE. To test for a NaN, use isNaN(X), because X == NAN_DOUBLE is never true. Comparison operators covers the other null and NaN comparison rules.

Replace values

Replacing null, NaN, and infinity values is a common practice when a dataset should have a default value. The following code block uses built-in functions to replace each kind of value with -1:

Filter out values

Use these built-in functions to filter out special values:

  • isNull returns true for null values.
  • isNaN returns true for NaN values.
  • isInf returns true for infinite values.
  • isFinite returns false for null, NaN, and infinite values, so a single check removes all three.

The following example negates the first three functions with the logical NOT operator ! to keep the rows without that value. It uses isFinite as is:

Python functions

To check for special values in a Python function, test each input as follows:

The following function's parameter has no type hint, so it compares the input against NULL_DOUBLE and the infinity constants. Its -> float return hint makes each result column a double column. The function returns a different value for each kind of input: