Description
This test performes the basic read and write operations of the Connect agents PandasExcelReader and PandasExcelWriter Contributor: Clemens Westphal, March 2022
Small Model of Type : GAMS
Category : GAMS Test library
Main file : capdxlsrw.gms
$title 'Test Connect agents PandasExcelReader and PandasExcelWriter' (CAPDXLSRW,SEQ=892)
$ontext
This test performes the basic read and write operations
of the Connect agents PandasExcelReader and PandasExcelWriter
Contributor: Clemens Westphal, March 2022
$offtext
* On the major platforms (Windows, Linux, Mac), GMSPYTHONLIB gets automatically set
* to use the internal Python installation in sysdir/GMSPython.
$if not setEnv GMSPYTHONLIB $abort.noError Embedded code Python not ready to be used
$log --- Using Python library %sysEnv.GMSPYTHONLIB%
set i / i4, i3, i2, i1 /;
set j / j1*j4 /;
set k / k4, k3, k2, k1 /;
set l / l1*l4 /;
set s1(i) / i1, i3 "line 3" /;
set s4(i,j,k,l) /
#i.#j.#k.l1 "line 1"
#i.#j.#k.l2
#i.#j.#k.l3 "line 3"
#i.#j.#k.l4 "line 4"
/;
s4(i,j,k,l)$(uniform(0,1)>0.1) = no;
parameter p0 / 3.14 /;
parameter p1(i);
parameter p4(i,j,k,l);
p1(i)$(uniform(0,1)<0.5) = uniform(0,1);
p4(i,j,k,l)$(uniform(0,1)<0.1) = uniform(0,1);
variable v0 / l 3.14 /;
variable v1(i);
variable v4(i,j,k,l);
v1.l(i)$(uniform(0,1)<0.5) = uniform(0,1);
v1.m(i)$(uniform(0,1)<0.5) = uniform(0,1);
v4.l(i,j,k,l)$(uniform(0,1)<0.1) = uniform(0,1);
v4.m(i,j,k,l)$(uniform(0,1)<0.1) = uniform(0,1);
* PandasExcelWriter
embeddedCode Connect:
- GAMSReader:
readAll: True
- PythonCode:
code: |
import os
symbols = \
[
('p0', 'p0_{0}.xlsx', 1),
('p1', 'p1_{0}.xlsx', 1),
('p4', 'p4_{0}.xlsx', 1),
('v0', 'v0_{0}.xlsx', 2),
('v1', 'v1_{0}.xlsx', 2),
('v4', 'v4_{0}.xlsx', 2),
('v0.lower', 'vs0_{0}.xlsx', 1),
('v1.marginal', 'vs1_{0}.xlsx', 1),
('v4.level', 'vs4_{0}.xlsx', 1),
('s1', 's1_{0}.xlsx', 1),
('s4', 's4_{0}.xlsx', 1),
]
for s,f,d in symbols:
sym = s.split('.')[0]
for rdim in range(0, connect.db[sym].dimension+d):
if os.path.isfile(f.format(rdim)):
os.remove(f.format(rdim))
instructions.append( \
{
'PandasExcelWriter':
{
'file': f.format(rdim),
'symbols': [{'name': s, 'rowDimension': rdim, 'range': sym+'!B3'}]
}
})
endEmbeddedCode
* PandasExcelReader
$onechoV > read.gms
set i, j, k, l, m, n, o, q, r, t;
set s4(n<,o<,q<,r<)
set s1(t<);
scalar p0;
parameter p1(m<);
parameter p4(i<,j<,k<,l<);
$if not set vsK $set vsK "''"
$if not set vsV $set vsV "''"
$onEmbeddedCode Connect:
- PandasExcelReader:
file: %sym%_%rdim%.xlsx
symbols:
- name: %sym%
type: %type%
rowDimension: %rdim%
columnDimension: %cdim%
range: %sym%!B3
valueSubstitutions: { %vsK%: %vsV% }
- GAMSWriter:
symbols:
- name: %sym%
$offEmbeddedCode
$offEcho
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=4 --rdim=0 --vsK=.nan gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=3 --rdim=1 --vsK='Y' gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=2 --rdim=2 --vsK='Y' gdx=2.gdx && gdxdiff 0.gdx 2.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=1 --rdim=3 --vsK='Y' gdx=3.gdx && gdxdiff 0.gdx 3.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=0 --rdim=4 --vsK=.nan gdx=4.gdx && gdxdiff 0.gdx 4.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s1 --type=set --cdim=1 --rdim=0 --vsK=.nan gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s1 --type=set --cdim=0 --rdim=1 --vsK=.nan gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=4 --rdim=0 gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=3 --rdim=1 gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=2 --rdim=2 gdx=2.gdx && gdxdiff 0.gdx 2.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=1 --rdim=3 gdx=3.gdx && gdxdiff 0.gdx 3.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=0 --rdim=4 gdx=4.gdx && gdxdiff 0.gdx 4.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p1 --type=par --cdim=1 --rdim=0 gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p1 --type=par --cdim=0 --rdim=1 gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p0 --type=par --cdim=0 --rdim=0 gdx=0.gdx > %system.NullFile%";
scalar p0_x; execute_load '0.gdx', p0_x=p0; abort$(abs(p0_x-3.14)>1e-6) 'wrong p0', p0_x;
* Test correct (cDim) order of sparse parameter written by PandasExcelWriter
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- PandasExcelWriter:
file: p4_order.xlsx
symbols:
- name: p4
endEmbeddedCode
embeddedCode Connect:
- PandasExcelReader:
file: p4_order.xlsx
symbols:
- name: p4
rowDimension: 3
columnDimension: 1
range: p4!A1
- PythonCode:
code: |
# we expect the original UEL order: l1, l2, l3, l4
expected = [('i4', 'j1', 'k3', 'l4', 0.414599358),
('i4', 'j2', 'k4', 'l3', 0.3741985),
('i4', 'j2', 'k2', 'l1', 0.480878863),
('i4', 'j2', 'k2', 'l4', 0.681039283),
('i4', 'j3', 'k3', 'l1', 0.57775716),
('i4', 'j4', 'k4', 'l3', 0.839795967),
('i4', 'j4', 'k1', 'l2', 0.010171892),
('i3', 'j1', 'k4', 'l4', 0.55303282),
('i3', 'j1', 'k2', 'l1', 0.430502537),
('i3', 'j2', 'k3', 'l4', 0.31014418),
('i3', 'j2', 'k2', 'l1', 0.82116568),
('i3', 'j3', 'k3', 'l1', 0.6863915740000001),
('i3', 'j4', 'k4', 'l1', 0.806006615),
('i3', 'j4', 'k4', 'l3', 0.5721642610000001),
('i3', 'j4', 'k4', 'l4', 0.7420390320000001),
('i2', 'j1', 'k2', 'l3', 0.8109937880000001),
('i2', 'j3', 'k4', 'l2', 0.866509716),
('i2', 'j3', 'k4', 'l3', 0.428284381),
('i2', 'j3', 'k1', 'l4', 0.503866822),
('i2', 'j4', 'k2', 'l1', 0.377722234),
('i1', 'j1', 'k4', 'l1', 0.5039530520000001),
('i1', 'j1', 'k4', 'l2', 0.521365665),
('i1', 'j1', 'k4', 'l4', 0.76064865),
('i1', 'j1', 'k2', 'l2', 0.9094419050000001),
('i1', 'j1', 'k2', 'l3', 0.820356749),
('i1', 'j2', 'k3', 'l3', 0.8145443840000001),
('i1', 'j4', 'k2', 'l2', 0.925196663),
('i1', 'j4', 'k2', 'l3', 0.618022658),
('i1', 'j4', 'k2', 'l4', 0.405575083),
('i1', 'j4', 'k1', 'l4', 0.045967406)]
data = ([(*r.keys, r.value) for r in connect.db['p4']])
if data != expected:
raise Exception("Unexpected Data p4")
endEmbeddedCode