Pandas Excel Writer on macOS

Problems with syntax of GAMS
Post Reply
marco
User
User
Posts: 4
Joined: 5 months ago

Pandas Excel Writer on macOS

Post by marco » 1 month ago

Hi everyone

I would like to export data from my GAMS-Model to excel. In the forum I have found the following solution viewtopic.php?f=3&t=11719. But the problem with the mentioned solution is, that it creates an excel file with way too much content. In fact it exports basically everything which in my case is an excel file with around 100 MB and unfortunately my computer can not handle this excel file, I guess its just too big.

So i found another solution which is the Pandas Excel Writer. This seems to be the perfect solution. I managed to export the the different variables to various excel files. But now i have a new problem with the way the data is displayed in excel. As i would like to evaluate the data with pivot tables i need the index of the multidementional variables to be in every single cell - but the Panda Excel Writer combines the cells of the multidimentional variables. And when I disconnect the cells in excel, the value of the cell remains in just one cell which is also not optimal (espacially because the whole excel sheet contains several 100k rows).

I guess the solution could be the option "toExcelArguments" but I'm just not able to get it run. I'm sorry, I'm still a total beginner in programming but i would greatly appreciate any help. It takes me hours and sometimes even days to get the stuff done.

Thanks in advanced

Best regards, Marco
Attachments
indata_WWB+NT_Klein_MobBat_221022.xlsx
(426.71 KiB) Downloaded 22 times
results_x.xlsx
(416.63 KiB) Downloaded 16 times
results_level.xlsx
(123.7 KiB) Downloaded 16 times
WWB+NT_Klein_MobBat_221022.gms
(8.76 KiB) Downloaded 15 times

aileen
User
User
Posts: 133
Joined: 2 years ago

Re: Pandas Excel Writer on macOS

Post by aileen » 1 month ago

Hi Marco,

The PandasExcelWriter is based on the pandas.DataFrame class and its I/O API method to_excel. pandas.to_excel per default writes merged cells but allows to change this behavior via the option merge_cells. You can pass merge_cells: False via toExcelArguments as follows:

Code: Select all

set i /i1*i2/;
set j /j1*j2/;
set k /k1*k2/;

parameter p(i,j,k);
p(i,j,k) = uniform(0,10);

embeddedCode Connect:
- GAMSReader:
    readAll : True
- PandasExcelWriter:
    file: out.xlsx
    symbols:
      - name: p
        range: p!A1
        rowDimension: 3
        toExcelArguments: {merge_cells: False}
endEmbeddedCode
toExcelArguments is a dictionary containing key and value pairs that will directly be passed to the pandas.to_excel method.

Note that with GAMS 40 we dropped the support for writing variables and equations with the PandasExcelWriter. Instead, you need to use the Projection agent to turn variables and equations into parameters. You can either use asParameter: True with the Projection agent to turn a variable/equation into a parameter with an extra index that contains the suffixes ("level", "marginal", etc.) or specify a specific suffix via the name option, e.g. name: x.l(index1,index2) to only keep the level of variable x with index space (index1,index2). I attached a modified version of your code that disables merged cells and also uses the Projection agent. For variable x, I used asParameter: True and for the remaining variables I only kept the level. Be aware that the PandasExcelWriter attempts to overlay data when the file exists already which may cause problems when the file contains merged cells, so you may need to delete the Excel files first before writing to them again.

Hope that helps.

Best,
Aileen
WWB+NT_Klein_MobBat_221022_mod.gms
(9.68 KiB) Downloaded 17 times
results_x.xlsx
(472.53 KiB) Downloaded 14 times
results_level.xlsx
(82.53 KiB) Downloaded 15 times

marco
User
User
Posts: 4
Joined: 5 months ago

Re: Pandas Excel Writer on macOS

Post by marco » 1 month ago

Hi Aileen

This is amazing, thank you so much for your help=)

Best,
Marco

Bolton51
User
User
Posts: 1
Joined: 3 weeks ago

Re: Pandas Excel Writer on macOS

Post by Bolton51 » 3 weeks ago

Hello,

Indeed, your advice was also a great help to me. Thank you very much for sharing.
My website here

Post Reply