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
$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'),
('p1', 'p1_{0}.xlsx'),
('p4', 'p4_{0}.xlsx'),
('s1', 's1_{0}.xlsx'),
('s4', 's4_{0}.xlsx'),
]
for s,f in symbols:
for rdim in range(0, self._cdb.container.data[s].dimension+1):
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': s+'!B3'}]
}
})
- Projection:
name: v0
newName: pv0
asParameter: True
- Projection:
name: v1(i)
newName: pv1(i)
asParameter: True
- Projection:
name: v4(i,j,k,l)
newName: pv4(i,j,k,l)
asParameter: True
- Projection:
name: v0.lo
newName: v0_lower
- Projection:
name: v1.m(i)
newName: v1_marginal(i)
- Projection:
name: v4.l(i,j,k,l)
newName: v4_level(i,j,k,l)
- PythonCode:
code: |
import os
symbols = \
[
('pv0', 'v0_{0}.xlsx'),
('pv1', 'v1_{0}.xlsx'),
('pv4', 'v4_{0}.xlsx'),
('v0_lower', 'vs0_{0}.xlsx'),
('v1_marginal', 'vs1_{0}.xlsx'),
('v4_level', 'vs4_{0}.xlsx'),
]
for s,f in symbols:
for rdim in range(0, self._cdb.container.data[s].dimension+1):
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': s+'!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;
parameter p1_empty(*);
* Test correct (cDim) order of sparse parameter written by PandasExcelWriter
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p1_empty
- name: p4
- PandasExcelWriter:
file: p4_order.xlsx
symbols:
# do not through an exception if there is no data
- name: p1_empty
- 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]]
sym = connect.container.data["p4"]
sym.records.sort_values(sym.records.columns[:-1].tolist(), inplace=True)
data = sym.records.values.tolist()
if data != expected:
raise Exception("Unexpected Data p4")
endEmbeddedCode
* Test case insensitive symbol names and append content to Excel file
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- PandasExcelWriter:
file: p4_cicap.xlsx
symbols:
- name: P4
- PandasExcelWriter:
file: p4_cicap.xlsx
symbols:
- name: p4
range: append!A1
endEmbeddedCode