New in PyXLL 5.6
Polars DataFrames can be used as function arguments and return types for Excel worksheet functions in a similar way to Pandas DataFrames.
When used as an argument, the range specified in Excel will be converted into a Polars DataFrame as specified by the function signature.
The dataframe
type specifier is the same as the one used for pandas DataFrames. When passing a polars
DataFrame as an argument you need to specify the kind of DataFrame you want by setting kind=polars
:
from pyxll import xl_func
@xl_func("dataframe<kind=polars>: dataframe", auto_resize=True)
def polars_dataframe_sum(df):
# df is a polars.DataFrame
return df.sum()
When returning a polars DataFrame there is no need to specify the kind as that will be infered from the type of the returned object.
The following shows returning a random polars DataFrame:
from pyxll import xl_func
import polars as pl
import numpy as np
@xl_func("int rows, int columns: dataframe", auto_resize=True)
def random_polars_dataframe(rows, columns):
data = np.random.rand(columns, rows)
column_names = [chr(ord('A') + x) for x in range(columns)]
return pl.DataFrame(data, columns=column_names)
Tip
The type alias polars.dataframe
can be used in place of dataframe<kind="polars">
, and
pandas.dataframe
can be used in place of dataframe<kind="pandas">
. Both forms are equivalent.
Python type annoations can be used instead of the type signature shown above.
Where type parameters are required you can use xl_arg
and xl_return
in conjunction with type annotations to specify the type parameters.
The above functions can be written using type annoations as follows:
from pyxll import xl_func
import polars as pl
import numpy as np
@xl_func(auto_resize=True)
def random_polars_dataframe(rows: int, columns: int) -> pl.DataFrame:
data = np.random.rand(columns, rows)
column_names = [chr(ord('A') + x) for x in range(columns)]
return pl.DataFrame(data, columns=column_names)
@xl_func(auto_resize=True)
def polars_dataframe_sum(df: pl.DataFrame) -> pl.DataFrame:
# df is a polars.DataFrame
return df.sum()
The following type parameters are available for dataframe
argument and return types:
dataframe, when used as an argument type
dataframe<kind=None, dtype=None, dtypes=None>
kind
Set to polars
to specify that a polars DataFrame is required. If not set the default DataFrame type will be used.
The default DataFrame type can be set by the default_dataframe_kind
option in the [PYXLL]
section of the pyxll.cfg file.
Defaults to pandas
if not set otherwise.
dtype
Datatype for the values in the dataframe. May not be set with dtypes.
dtypes
Dictionary of column name -> datatype for the values in the dataframe. May not be set with dtype.
The dictionary can be specified using standard Python dictionary syntax as part of a function
signature string. However, often it is more convenient to use the xl_arg
or xl_return
decorators. These allow you to set type multiple complex parameters more easily, for example:
@xl_func
@xl_arg("df", dtypes={"A": "date"})
def your_function(df: pl.DataFrame):
....
Not all column dtypes need to be specified. Any that are not specified will default to var
.
dataframe, when used as a return type
dataframe<kind=None>
kind
Set to polars
force the kind of DataFrame expected to be a polars DataFrame.
If not set, any supported kind of DataFrame can be used.
Tip
For specifying multiple or complex type parameters it can be easier to use the xl_arg
and
xl_return
decorators.
See @xl_arg and @xl_return Decorators for more details about how to use xl_arg
and xl_return
to specify type parameters.
When passing large DataFrames between Python functions it is not always necessary, or desirable, to return the full DataFrame
to Excel. It can be expensive reconstructing the DataFrame from the Excel range each time. In those cases you
can use the object
return type to return a handle to the Python object. Functions taking dataframe<kind=polars>
can accept object handles.