Convert between Deephaven tables and pandas DataFrames

pandas is a popular Python library for data analysis and manipulation. It centers around DataFrames, much as Deephaven centers around tables. Converting lets you take a snapshot of a live Deephaven table into pandas and its ecosystem of analysis tools, then bring the results back as a table that the rest of your queries can use.

This guide shows how to convert between Deephaven tables and pandas DataFrames. It also shows how to:

  • Choose which columns to convert.
  • Choose which pandas data types (dtypes) the DataFrame columns use.
  • Control how Deephaven nulls convert, in both directions.
  • Infer types for object columns.

The deephaven.pandas Python module provides only two functions:

  • to_pandas: Converts a Deephaven table to a pandas DataFrame.
  • to_table: Converts a pandas DataFrame to a Deephaven table.

Important

Converting between Deephaven tables and pandas DataFrames copies the data into memory. Be cautious when converting large datasets, and convert only the columns you need (see Convert a subset of columns). If the table is refreshing, to_pandas converts a snapshot of its current contents, and the DataFrame does not change when the table updates.

Convert a table to a DataFrame and back

The following example uses new_table to create a table, then converts it to a DataFrame with to_pandas and back to a table with to_table.

Each column keeps a matching data type through the round trip: the meta_table of result shows the same column types as the meta_table of source. The log shows each column's dtype in the intermediate DataFrame:

Deephaven char columns become UInt16 columns that hold character codes, and to_table turns them back into char columns.

Convert a subset of columns

Both to_pandas and to_table accept a cols argument to convert only some of the columns. The following example uses source and df from Convert a table to a DataFrame and back:

Choose a dtype backend

A dtype backend is the kind of array that stores each DataFrame column. The dtype_backend argument of to_pandas selects it:

  • "numpy_nullable" (the default) uses pandas nullable dtypes, which can hold a missing value, pandas.NA. Numeric, char, Boolean, and string columns get them, for example Int32 and boolean. Deephaven null values in those columns become pandas.NA. Nulls in date-time columns become NaT, the pandas missing value for date-times.
  • "pyarrow" uses PyArrow-backed dtypes for numeric, char, Boolean, string, and date-time columns, and Deephaven null values in them become pandas.NA. This option requires pandas 2.0.0 or later.
  • None builds each column from a NumPy array. Conversion of Deephaven null values to pandas.NA is still on by default, so numeric, char, and Boolean columns get pandas nullable dtypes, just as they do with "numpy_nullable". To get plain NumPy dtypes, turn null conversion off, as described in Keep null sentinel values.

The following example converts the source table from Convert a table to a DataFrame and back with the "pyarrow" and None backends and prints the dtypes of each DataFrame. Compare them with the default "numpy_nullable" dtypes printed in that section:

The pyarrow DataFrame uses [pyarrow] dtypes for every column. The None DataFrame has the same nullable dtypes as the default for Ints, Booleans, Doubles, and Chars, because null conversion is still on. Only Strings differs: it is built from a NumPy array instead of getting the pandas nullable string dtype.

Keep null sentinel values

Deephaven stores a null in a primitive column as a reserved value from that type's range, called a sentinel value, such as NULL_INT for int.

The conv_null argument of to_pandas controls whether Deephaven null values become pandas.NA. It defaults to True. You can set it to False only with dtype_backend=None. With any other backend, conv_null=False raises a DHError.

With conv_null=False, each column gets a plain NumPy dtype:

  • Numeric columns get dtypes such as int32 and float64, and char columns get uint16. Sentinel values stay in the DataFrame as ordinary numbers instead of becoming pandas.NA.
  • Boolean columns get the NumPy bool dtype, and a null Boolean value reads as True.

The following example converts the source table from Convert a table to a DataFrame and back with conv_null=False and prints the resulting dtypes:

The following example shows how one null int value converts with the default conv_null setting and with conv_null=False:

With the default, the null is <NA> in an Int32 column. With conv_null=False, it is -2147483648, the value of NULL_INT, in an int32 column.

Convert missing values back to Deephaven nulls

In the other direction, to_table converts pandas.NA in nullable integer, Boolean, string, and PyArrow columns to Deephaven nulls. Missing values in a NumPy float64 column, or in a nullable Float32 or Float64 column, become NaN instead. See Null, infinity, and NaN values for how Deephaven treats each.

The following example converts a DataFrame with a nullable Int32 column and a plain float64 column, each with one missing value. It then uses update to check the missing values with the built-in isNull and isNaN functions:

In the second row, IntIsNull is true: the missing Int32 value became a Deephaven null. FloatIsNull is false and FloatIsNaN is true: the missing float64 value stayed NaN.

Infer types for object columns

DataFrames sometimes contain generic object-dtype columns that don't directly translate to Deephaven column types. By default, to_table calls the pandas convert_dtypes method on each object-dtype column to infer a better type before conversion. Set infer_objects=False to skip this step.

In the following example, columns A and B have ordinary numeric dtypes, and column C is an object-dtype column because it holds pandas.NA. Inference affects only C:

In infer_meta, column C is a long column, and its missing value becomes a Deephaven null. In no_infer_meta, column C is a generic Python object (PyObject) column.