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
objectcolumns.
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 exampleInt32andboolean. Deephaven null values in those columns becomepandas.NA. Nulls in date-time columns becomeNaT, 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 becomepandas.NA. This option requires pandas 2.0.0 or later.Nonebuilds each column from a NumPy array. Conversion of Deephaven null values topandas.NAis 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
int32andfloat64, andcharcolumns getuint16. Sentinel values stay in the DataFrame as ordinary numbers instead of becomingpandas.NA. - Boolean columns get the NumPy
booldtype, and a null Boolean value reads asTrue.
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.