capdxlsrw.gms : Test Connect agents PandasExcelReader and PandasExcelWriter

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