Pandas DataFrames and Series can be used as function arguments and return types for Excel worksheet functions.
For polars DataFrames, see Polars DataFrames.
Tip
See Using Pandas in Excel for a more complete explaination of how pandas can be used in Excel.
When used as an argument, the range specified in Excel will be converted into a Pandas DataFrame or Series as specified by the function signature.
When returning a DataFrame or Series, a range of data will be returned to
Excel. PyXLL will automatically resize the range of the array formula to
match the returned data if auto_resize=True is set in xl_func.
The following shows returning a random dataframe, including the index:
from pyxll import xl_func
import pandas as pd
import numpy as np
@xl_func("int rows, int columns: dataframe<index=True>", auto_resize=True)
def random_dataframe(rows, columns):
data = np.random.rand(rows, columns)
column_names = [chr(ord('A') + x) for x in range(columns)]
return pd.DataFrame(data, columns=column_names)
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, xl_return
import pandas as pd
import numpy as np
@xl_func(auto_resize=True)
@xl_return(index=True) # pass index=True to the DataFrame return type
def random_dataframe(rows: int, columns: int) -> pd.DataFrame:
data = np.random.rand(rows, columns)
column_names = [chr(ord('A') + x) for x in range(columns)]
return pd.DataFrame(data, columns=column_names)
The following type parameters are available for the dataframe and series argument and return types:
dataframe, when used as an argument type
dataframe<kind=None, index=0, columns=1, dtype=None, dtypes=None, index_dtype=None>
kindSet to pandas to specify that a pandas 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.
indexNumber of columns to use as the DataFrame’s index. Specifying more than one will result in a DataFrame where the index is a MultiIndex.
columnsNumber of rows to use as the DataFrame’s columns. Specifying more than one will result in a DataFrame where the columns is a MultiIndex. If used in conjunction with index then any column headers on the index columns will be used to name the index.
trim
New in PyXLL 5.11
Trim leading and trailing empty rows and columns from the data before constructing the DataFrame.
dtypeDatatype for the values in the dataframe. May not be set with dtypes.
dtypesDictionary 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: pd.DataFrame):
....
Not all column dtypes need to be specified. Any that are not specified will default to var.
index_dtypeDatatype for the values in the dataframe’s index.
multi_sparse [1]Return sparse results for MultiIndexes. Can be set to True or False, or 'index' or
'columns' if it should only apply to one or the other.
dataframe, when used as a return type
dataframe<kind=None, index=None, columns=True>
kindSet to pandas force the kind of DataFrame expected to be a pandas DataFrame.
If not set, any supported kind of DataFrame can be used.
indexIf True include the index when returning to Excel, if False don’t. If None, only include if the index is named.
columnsIf True include the column headers, if False don’t.
series, when used as an argument type
series<index=1, transpose=None, dtype=None, index_dtype=None>
indexNumber of columns (or rows, depending on the orientation of the Series) to use as the Series index.
transposeSet to True if the Series is arranged horizontally or False if vertically. By default the orientation will be guessed from the structure of the data.
dtypeDatatype for the values in the Series.
index_dtypeDatatype for the values in the Series’ index.
multi_sparse [1]Return sparse results for MultiIndexes.
series, when used as a return type
series<index=True, transpose=False>
indexIf True include the index when returning to Excel, if False don’t.
transposeSet to True if the Series should be arranged horizontally, or False if vertically.
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 to return the full DataFrame
to Excel and 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 the dataframe
and series types can accept object handles.
See Using Pandas in Excel for more information.
Footnotes
