UserSpace.new_pandas#

UserSpace.new_pandas(name, path, data, file_type=None, sheet=None, filetype=None)#

Create a Reference bound to a pandas DataFrame or Series associating a new PandasData object.

This method creates a Reference named name bound to a pandas DataFrame or Series passed as data, creates a PandasData object from path, file_type and optionally sheet, and associate it with the pandas object.

pandas objects can be assigned to References by the normal assignment operation, such as space.x = df, but the pandas objects assigned this way are saved in a binary file together with other Reference objects by write() or write_model(). This method allows the assigned pandas object to be saved in a separate file by write() or write_model() using information stored in the associated PandasData.

When the model is saved, the DataFrame or Series is written to a file whose path is given by the path parameter, and whose format is specified by the file_type parameter. If path is relative, it is interpreted relative to the model folder. The file_type can take either “excel” or “csv”.

If “excel” is given to file_type, the pandas object is written to an Excel file. The file name in path must have either “.xlsx”, “.xlsm” or “.xls” extention. The optional sheet paramter is to specify the sheet name in the Excel file. Multiple PandasData objects can be associated with the same Excel file, as long as their sheet names are all different. If “csv” is given to file_type, the pandas object is written to a CSV file. Only one object can be saved in one file.

This method internally uses pandas.read_excel function and to_excel method for reading from and writing to Excel files, so appropriate Excel engines for reading and writing Excel files must be installed, depending on the types of Excel files. See pandas’ document for the required packeges for Excel engines.

Example

The script below creates a sample DataFrame df:

>>> index = pd.date_range("20210101", periods=3)

>>> df = pd.DataFrame(np.random.randn(3, 3), index=index, columns=list("XYZ"))

>>> df
                   X         Y         Z
2021-01-01  0.184497  0.140037 -1.599499
2021-01-02 -1.029170  0.588080  0.081129
2021-01-03  0.028450 -0.490102  0.025208

The code below assigns the DataFrame created above to a Reference named x in space, and at the same time creates a PandasData object:

>>> space.new_pandas("x", "Space1/df.xlsx", data=df, file_type="excel", sheet="df1")

>>> space.x
                   X         Y         Z
2021-01-01  0.184497  0.140037 -1.599499
2021-01-02 -1.029170  0.588080  0.081129
2021-01-03  0.028450 -0.490102  0.025208

>>> model.iospecs
[<PandasData path='Space1/df.xlsx' file_type='excel' sheet='df1'>]

When the model is saved, the DataFrame is written to an Excel file named df.xlsx placed under the Space1 folder in model.

>>> model.write("model")    # `model` is the parent of `space`

When the model is read back by modelx.read_model() function, the DataFrame is read from the file:

>>> model2 = mx.read_model("model", name="Model2")

>>> model2.Space1.x
                   X         Y         Z
2021-01-01  0.184497  0.140037 -1.599499
2021-01-02 -1.029170  0.588080  0.081129
2021-01-03  0.028450 -0.490102  0.025208

Parameters:

name (str) – Name of the Reference
path – A path to a file to save the Pandas object. If a relative path is given, it is relative to the model folder.
data – pandas DataFrame or Series
file_type – String to indicate file format. (“excel” or “csv”)
sheet (str, optional) – If file_type is “excel”, the name of the sheet to write the object on.

Changed in version 0.20.0:

The sheet parameter is added to allow writing objects to multiple sheets in an Excel file.
The filetype parameter is replaced with file_type. filetype still works but raises a deprecation warning.

Changed in version 0.13.0: The expose_data parameter is removed.

Changed in version 0.13.0: Add the expose_data parameter. By default, data is assigned instead of its PandasData object

New in version 0.12.0.