Description

This test ensures the correctness of the Connect agent CSVWriter.

Contributor: Michael Bussieck, March 2022

Small Model of Type : GAMS

Category : GAMS Test library

Main file : cacsvw.gms

$title 'Test Connect agent CSVWriter' (CACSVW,SEQ=895)
$onText
This test ensures the correctness of the Connect agent CSVWriter.

Contributor: Michael Bussieck, March 2022
$offText


$log --- Using Python library %sysEnv.GMSPYTHONLIB%

$onEchoV > t.gms
Set i(*) canning plants /
'seattle',
'san-diego' /;

Set j(*) markets /
'new-york' '10',
'chicago' '20.0',
'topeka' '030'/;

Parameter a(i) capacity of plant i in cases /
'seattle' 350,
'san-diego' 600 /;

Parameter b(j) demand at market j in cases /
'new-york' 325,
'chicago' 300,
'topeka' 275 /;

Parameter d(i,j) distance in thousands of miles /
'seattle'.'new-york' 2.5,
'seattle'.'chicago' 1.7,
'seattle'.'topeka' 1.8,
'san-diego'.'new-york' 2.5,
'san-diego'.'chicago' 1.8,
'san-diego'.'topeka' 1.4 /;

Scalar f freight in dollars per case per thousand miles / 90 /;

Parameter c(i,j) transport cost in thousands of dollars per case /
'seattle'.'new-york' 0.225,
'seattle'.'chicago' 0.153,
'seattle'.'topeka' 0.162,
'san-diego'.'new-york' 0.225,
'san-diego'.'chicago' 0.162,
'san-diego'.'topeka' 0.126 /;

positive Variable x(i,j) shipment quantities in cases /
'seattle'.'new-york'.L 50,
'seattle'.'chicago'.L 300,
'seattle'.'topeka'.M 0.036,
'san-diego'.'new-york'.L 275,
'san-diego'.'chicago'.M 0.009,
'san-diego'.'topeka'.L 275 /;

free     Variable z total transportation costs in thousands of dollars /L 153.675 /;

Equation cost define objective function /M 1, LO 0, UP 0 /;

Equation supply(i) observe supply limit at plant i /
'seattle'.L 350,
'seattle'.M EPS,
'seattle'.LO -INF,
'seattle'.UP 350,
'san-diego'.L 550,
'san-diego'.LO -INF,
'san-diego'.UP 600 /;

Equation demand(j) satisfy demand at market j /
'new-york'.L 325,
'new-york'.M 0.225,
'new-york'.LO 325,
'new-york'.UP +INF,
'chicago'.L 300,
'chicago'.M 0.153,
'chicago'.LO 300,
'chicago'.UP +INF,
'topeka'.L 275,
'topeka'.M 0.126,
'topeka'.LO 275,
'topeka'.UP +INF /;

$offEmpty
$offEcho
$call.checkErrorLevel gams t.gms lo=%gams.lo% gdx t.gdx

$onEcho >  d_ref.csv
seattle,new-york,2.5
seattle,chicago,1.7
seattle,topeka,1.8
san-diego,new-york,2.5
san-diego,chicago,1.8
san-diego,topeka,1.4
$offEcho
$onEcho >  demand_ref.csv
new-york,325.0,0.225,325.0,INF,1.0
chicago,300.0,0.153,300.0,INF,1.0
topeka,275.0,0.126,275.0,INF,1.0
$offEcho
$onEcho >  f_ref.csv
90.0
$offEcho
$onEcho >  j_ref.csv
new-york,10
chicago,20.0
topeka,030
$offEcho
$onEcho >  x_ref.csv
seattle,new-york,50.0,0.0,0.0,INF,1.0
seattle,chicago,300.0,0.0,0.0,INF,1.0
seattle,topeka,0.0,0.036,0.0,INF,1.0
san-diego,new-york,275.0,0.0,0.0,INF,1.0
san-diego,chicago,0.0,0.009,0.0,INF,1.0
san-diego,topeka,275.0,0.0,0.0,INF,1.0
$offEcho
$onEcho >  xl_ref.csv
seattle,new-york,50.0
seattle,chicago,300.0
seattle,topeka,0.0
san-diego,new-york,275.0
san-diego,chicago,0.0
san-diego,topeka,275.0
$offEcho
$onEcho >  xm_ref.csv
seattle,new-york,0.0
seattle,chicago,0.0
seattle,topeka,0.036
san-diego,new-york,0.0
san-diego,chicago,0.009
san-diego,topeka,0.0
$offEcho
$onEcho >  xlo_ref.csv
seattle,new-york,0.0
seattle,chicago,0.0
seattle,topeka,0.0
san-diego,new-york,0.0
san-diego,chicago,0.0
san-diego,topeka,0.0
$offEcho
$onEcho >  xup_ref.csv
seattle,new-york,INF
seattle,chicago,INF
seattle,topeka,INF
san-diego,new-york,INF
san-diego,chicago,INF
san-diego,topeka,INF
$offEcho
$onEcho >  xscale_ref.csv
seattle,new-york,1.0
seattle,chicago,1.0
seattle,topeka,1.0
san-diego,new-york,1.0
san-diego,chicago,1.0
san-diego,topeka,1.0
$offEcho
$onEcho >  z_ref.csv
level,153.675
marginal,0.0
lower,-INF
upper,INF
scale,1.0
$offEcho
$onEcho >  d_header_ref.csv
i,j,value
seattle,new-york,2.5
seattle,chicago,1.7
seattle,topeka,1.8
san-diego,new-york,2.5
san-diego,chicago,1.8
san-diego,topeka,1.4
$offEcho
$onEcho >  demand_header_ref.csv
j,level,marginal,lower,upper,scale
new-york,325.0,0.225,325.0,INF,1.0
chicago,300.0,0.153,300.0,INF,1.0
topeka,275.0,0.126,275.0,INF,1.0
$offEcho
$onEcho >  f_header_ref.csv
value
90.0
$offEcho
$onEcho >  j_header_ref.csv
uni,element_text
new-york,10
chicago,20.0
topeka,030
$offEcho
$onEcho >  x_header_ref.csv
i,j,level,marginal,lower,upper,scale
seattle,new-york,50.0,0.0,0.0,INF,1.0
seattle,chicago,300.0,0.0,0.0,INF,1.0
seattle,topeka,0.0,0.036,0.0,INF,1.0
san-diego,new-york,275.0,0.0,0.0,INF,1.0
san-diego,chicago,0.0,0.009,0.0,INF,1.0
san-diego,topeka,275.0,0.0,0.0,INF,1.0
$offEcho
$onEcho >  xl_header_ref.csv
i,j,value
seattle,new-york,50.0
seattle,chicago,300.0
seattle,topeka,0.0
san-diego,new-york,275.0
san-diego,chicago,0.0
san-diego,topeka,275.0
$offEcho
$onEcho >  z_header_ref.csv
attribute,value
level,153.675
marginal,0.0
lower,-INF
upper,INF
scale,1.0
$offEcho
$onEcho >  d1_ref.csv
i;new-york;chicago;topeka
seattle;2.5;1.7;1.8
san-diego;2.5;1.8;1.4
$offEcho
$onEcho >  d2_ref.csv
i;new-york;chicago;topeka
seattle;2,5;1,7;1,8
san-diego;2,5;1,8;1,4
$offEcho
$onEcho >  d3_ref.csv
i,new-york,chicago,topeka
seattle,2.5,1.7,1.8
san-diego,2.5,1.8,1.4
$offEcho
$onEcho >  d4_ref.csv
"i","new-york","chicago","topeka"
"seattle","2.5","1.7","1.8"
"san-diego","2.5","1.8","1.4"
$offEcho
$onEcho >  d5_ref.csv
"i","new-york","chicago","topeka"
"seattle",2.5,1.7,1.8
"san-diego",2.5,1.8,1.4
$offEcho
$onEcho >  d6_ref.csv
seattle,2.5,1.7,1.8
san-diego,2.5,1.8,1.4
$offEcho
$onEcho >  x1_ref.csv
i,j,upper
seattle,new-york,INF
seattle,chicago,INF
seattle,topeka,INF
san-diego,new-york,INF
san-diego,chicago,INF
san-diego,topeka,INF
$offEcho
$onEcho > x2_ref.csv
i,new-york,chicago,topeka
seattle,50.0,300.0,0.0
san-diego,275.0,0.0,275.0
$offEcho

* test for sets and parameter
set sym / j, d, f /;
loop(sym,
   put_utility 'ecArguments' / '--SYM=' sym.tl:0;
   EmbeddedCode Connect:
   - GDXReader:
       file: t.gdx
       symbols:
          - name: %SYM%
   - CSVWriter:
       file: %SYM%.csv
       name: %SYM%
       header: False
   endEmbeddedCode
   put_utility 'shell.checkErrorLevel' / 'diff -q ' sym.tl:0 '.csv ' sym.tl:0 '_ref.csv > %system.nullFile%';
);   
loop(sym,
   put_utility 'ecArguments' / '--SYM=' sym.tl:0;
   EmbeddedCode Connect:
   - GDXReader:
       file: t.gdx
       symbols:
          - name: %SYM%
   - CSVWriter:
       file: %SYM%_header.csv
       name: %SYM%
       header: True
   endEmbeddedCode
   put_utility 'shell.checkErrorLevel' / 'diff -q ' sym.tl:0 '_header.csv ' sym.tl:0 '_header_ref.csv > %system.nullFile%';
);

* test for variables and equations
put_utility 'ecArguments' / '';
EmbeddedCode Connect:
- GDXReader:
    file: t.gdx
- Projection:
    name: demand.all(j)
    newName: pdemand(j)
- CSVWriter:
    file: demand.csv
    name: pdemand
    header: False
    unstack: True
- Projection:
    name: x.all(i,j)
    newName: px(i,j)
- CSVWriter:
    file: x.csv
    name: px
    header: False
    unstack: True
- Projection:
    name: x.l(i,j)
    newName: xl(i,j)
- CSVWriter:
    file: xl.csv
    name: xl
    header: False
- Projection:
    name: x.m(i,j)
    newName: xm(i,j)
- CSVWriter:
    file: xm.csv
    name: xm
    header: False
- Projection:
    name: x.lo(i,j)
    newName: xlo(i,j)
- CSVWriter:
    file: xlo.csv
    name: xlo
    header: False
- Projection:
    name: x.up(i,j)
    newName: xup(i,j)
- CSVWriter:
    file: xup.csv
    name: xup
    header: False
- Projection:
    name: x.scale(i,j)
    newName: xscale(i,j)
- CSVWriter:
    file: xscale.csv
    name: xscale
    header: False
- Projection:
    name: z.all
    newName: pz
- CSVWriter:
    file: z.csv
    name: pz
    header: False
endEmbeddedCode
put_utility 'shell.checkErrorLevel' / 'diff -q demand.csv demand_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q x.csv x_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q xl.csv xl_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q xm.csv xm_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q xlo.csv xlo_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q xup.csv xup_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q xscale.csv xscale_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q z.csv z_ref.csv > %system.nullFile%';

EmbeddedCode Connect:
- GDXReader:
    file: t.gdx
- Projection:
    name: demand.all(j)
    newName: pdemand(j)
- CSVWriter:
    file: demand_header.csv
    name: pdemand
    header: True
    unstack: True
- Projection:
    name: x.all(i,j)
    newName: px(i,j)
- CSVWriter:
    file: x_header.csv
    name: px
    header: True
    unstack: True
- Projection:
    name: x.l(i,j)
    newName: xl(i,j)
- CSVWriter:
    file: xl_header.csv
    name: xl
    header: True
- Projection:
    name: z.all
    newName: pz
- CSVWriter:
    file: z_header.csv
    name: pz
    header: True
endEmbeddedCode
put_utility 'shell.checkErrorLevel' / 'diff -q demand_header.csv demand_header_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q x_header.csv x_header_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q xl_header.csv xl_header_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q z_header.csv z_header_ref.csv > %system.nullFile%';

put_utility 'ecArguments' / '';
EmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols: all
- CSVWriter:
    file: d1.csv
    name: d
    header: True
    unstack: True
    fieldSeparator: ;
- CSVWriter:
    file: d2.csv
    name: d
    header: True
    unstack: True
    fieldSeparator: ;
    decimalSeparator: ","
- CSVWriter:
    file: d3.csv
    name: d
    header: True
    unstack: True
    quoting: 0
- CSVWriter:
    file: d4.csv
    name: d
    header: True
    unstack: True
    quoting: 1
- CSVWriter:
    file: d5.csv
    name: d
    header: True
    unstack: True
    quoting: 2
- CSVWriter:
    file: d6.csv
    name: d
    header: False
    unstack: True
- Projection:
    name: x.up(i,j)
    newName: xup(i,j)
- CSVWriter:
    file: x1.csv
    name: xup
    header: True
    setHeader: i,j,upper
- Projection:
    name: x.l(i,j)
    newName: xl(i,j)
- CSVWriter:
    file: x2.csv
    name: xl
    header: True
    unstack: True
endEmbeddedCode
put_utility 'shell.checkErrorLevel' / 'diff -q d1.csv d1_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q d2.csv d2_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q d3.csv d3_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q d4.csv d4_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q d5.csv d5_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q d6.csv d6_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q x1.csv x1_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q x2.csv x2_ref.csv > %system.nullFile%';

$onEcho > sv1_ref.csv
uni,value
1,EPS
2,-INF
3,INF
4,NA
5,UNDEF
$offEcho
$onEcho > sv2_ref.csv
uni,value
1,-0.0
2,-INF
3,INF
4,na
5,na
$offEcho
$onUNDF
parameter sv / 1 eps, 2 -inf, 3 +inf, 4 na, 5 undf /;
EmbeddedCode Connect:
- GAMSReader:
    symbols: all
- CSVWriter:
    file: sv1.csv
    name: sv
    header: True
- CSVWriter:
    file: sv2.csv
    name: sv
    header: True
    valueSubstitutions: {EPS: -0.0, NA: na, UNDEF: na}
endEmbeddedCode
put_utility 'shell.checkErrorLevel' / 'diff -q sv1.csv sv1_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q sv2.csv sv2_ref.csv > %system.nullFile%';

$onEcho > s1_ref.csv
uni,element_text
1,One
2,Two
3,Three
$offEcho
$onEcho > s2_ref.csv
uni
1
2
3
$offEcho
set s / 1 One, 2 Two, 3 Three /;
EmbeddedCode Connect:
- GAMSReader:
    symbols: all
- CSVWriter:
    file: s1.csv
    name: s
    header: True
- CSVWriter:
    file: s2.csv
    name: s
    header: True
    skipText: True
endEmbeddedCode
put_utility 'shell.checkErrorLevel' / 'diff -q s1.csv s1_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q s2.csv s2_ref.csv > %system.nullFile%';

$onEcho > t1_ref.csv
uni_0,uni_1,element_text
1,1,One
2,2,Two
3,3,Three
$offEcho
$onEcho > t2_ref.csv
uni_0,uni_1
1,1
2,2
3,3
$offEcho
$onEcho > t3_ref.csv
uni_0,1,2,3
1,One,,
2,,Two,
3,,,Three
$offEcho
$onEcho > t4_ref.csv
uni_0,1,2,3
1,Y,,
2,,Y,
3,,,Y
$offEcho
$onEcho > t5_ref.csv
1,Y,,
2,,Y,
3,,,Y
$offEcho
set t / 1.1 One, 2.2 Two, 3.3 Three /;
EmbeddedCode Connect:
- GAMSReader:
    symbols: all
- CSVWriter:
    file: t1.csv
    name: t
    header: True
- CSVWriter:
    file: t2.csv
    name: t
    header: True
    skipText: True
- CSVWriter:
    file: t3.csv
    name: t
    header: True
    unstack: True
- CSVWriter:
    file: t4.csv
    name: t
    header: True
    unstack: True
    skipText: True
- CSVWriter:
    file: t5.csv
    name: t
    header: True
    unstack: True
    skipText: True
    toCSVArguments: { mode: w } # overwrite mode=a from setHeader
    setHeader: 'i,1,2,3'
endEmbeddedCode
put_utility 'shell.checkErrorLevel' / 'diff -q t1.csv t1_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q t2.csv t2_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q t3.csv t3_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q t4.csv t4_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q t5.csv t5_ref.csv > %system.nullFile%';


set i / i4, i3, i2, i1 /;
set j / j1*j4 /;
set k / k4, k3, k2, k1 /;
set l / l1*l4 /;
parameter p4(i,j,k,l);
p4(i,j,k,l)$(uniform(0,1)<0.1) = uniform(0,1);

* test for correct order
EmbeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- CSVWriter:
    file: p4_order.csv
    name: p4
    unstack: True
endEmbeddedCode
EmbeddedCode Connect:
- CSVReader:
    file: p4_order.csv
    name: p4
    indexColumns: [1,2,3]
    valueColumns: "4:lastCol"
- PythonCode:
    code: |
      # we expect the original UEL order: l1, l2, l3, l4
      expected = [['i4', 'j1', 'k2', 'l1', 0.500210669],
                  ['i4', 'j3', 'k2', 'l3', 0.338550272],
                  ['i4', 'j4', 'k2', 'l1', 0.175661049],
                  ['i4', 'j4', 'k3', 'l1', 0.102514669],
                  ['i4', 'j4', 'k3', 'l4', 0.792360642],
                  ['i4', 'j4', 'k1', 'l1', 0.5851311730000001],
                  ['i3', 'j1', 'k2', 'l2', 0.202015557],
                  ['i3', 'j1', 'k2', 'l3', 0.269613052],
                  ['i3', 'j3', 'k1', 'l1', 0.401346257],
                  ['i3', 'j4', 'k3', 'l1', 0.7831020040000001],
                  ['i3', 'j2', 'k2', 'l3', 0.576299805],
                  ['i3', 'j2', 'k2', 'l4', 0.006008368],
                  ['i2', 'j1', 'k2', 'l4', 0.150347716],
                  ['i2', 'j3', 'k2', 'l1', 0.196093864],
                  ['i2', 'j3', 'k3', 'l1', 0.177822574],
                  ['i2', 'j3', 'k3', 'l2', 0.016643898],
                  ['i2', 'j4', 'k3', 'l4', 0.948836169],
                  ['i2', 'j2', 'k3', 'l3', 0.187448731],
                  ['i2', 'j2', 'k3', 'l4', 0.540400638],
                  ['i2', 'j2', 'k1', 'l4', 0.543870155],
                  ['i1', 'j1', 'k3', 'l1', 0.368630572],
                  ['i1', 'j1', 'k3', 'l4', 0.84181978],
                  ['i1', 'j1', 'k1', 'l1', 0.7737034340000001],
                  ['i1', 'j3', 'k2', 'l2', 0.32300194],
                  ['i1', 'j3', 'k4', 'l4', 0.263857554],
                  ['i1', 'j4', 'k1', 'l3', 0.3741985],
                  ['i1', 'j4', 'k4', 'l4', 0.414599358],
                  ['i1', 'j2', 'k2', 'l4', 0.485176103],
                  ['i1', 'j2', 'k1', 'l1', 0.698580858]]
      sym = connect.container.data["p4"]
      data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
      if data_sorted != expected:
        raise Exception("Unexpected sorted data p4")
- CSVWriter:
    file: p4_order_out.csv
    name: p4
    unstack: True
- CSVWriter:
    file: p4_order_out_alt.csv
    name: p4
    unstack: [4]
- CSVWriter:
    file: p4_order_out_2.csv
    name: p4
    unstack: [1,3]
endEmbeddedCode

$onEcho > p4_order_ref.csv
i,j,k,l1,l2,l3,l4
i4,j1,k2,0.500210669,,,
i4,j3,k2,,,0.338550272,
i4,j4,k2,0.175661049,,,
i4,j4,k3,0.102514669,,,0.792360642
i4,j4,k1,0.5851311730000001,,,
i3,j1,k2,,0.202015557,0.269613052,
i3,j3,k1,0.401346257,,,
i3,j4,k3,0.7831020040000001,,,
i3,j2,k2,,,0.576299805,0.006008368
i2,j1,k2,,,,0.150347716
i2,j3,k2,0.196093864,,,
i2,j3,k3,0.177822574,0.016643898,,
i2,j4,k3,,,,0.948836169
i2,j2,k3,,,0.187448731,0.540400638
i2,j2,k1,,,,0.543870155
i1,j1,k3,0.368630572,,,0.84181978
i1,j1,k1,0.7737034340000001,,,
i1,j3,k2,,0.32300194,,
i1,j3,k4,,,,0.263857554
i1,j4,k1,,,0.3741985,
i1,j4,k4,,,,0.414599358
i1,j2,k2,,,,0.485176103
i1,j2,k1,0.698580858,,,
$offEcho

* p4_order_out.csv and p4_order_out_alt.csv should have the same result
put_utility 'shell.checkErrorLevel' / 'diff -q p4_order_out.csv p4_order_ref.csv > %system.nullFile%';
put_utility 'shell.checkErrorLevel' / 'diff -q p4_order_out_alt.csv p4_order_ref.csv > %system.nullFile%';

$onEcho > p4_order_ref_2.csv
i,,i4,i4,i4,i3,i3,i3,i2,i2,i2,i1,i1,i1,i1
k,,k2,k3,k1,k2,k3,k1,k2,k3,k1,k2,k3,k1,k4
j,level_3,,,,,,,,,,,,,
j1,l1,0.500210669,,,,,,,,,,0.368630572,0.7737034340000001,
j1,l2,,,,0.202015557,,,,,,,,,
j1,l3,,,,0.269613052,,,,,,,,,
j1,l4,,,,,,,0.150347716,,,,0.84181978,,
j3,l1,,,,,,0.401346257,0.196093864,0.177822574,,,,,
j3,l2,,,,,,,,0.016643898,,0.32300194,,,
j3,l3,0.338550272,,,,,,,,,,,,
j3,l4,,,,,,,,,,,,,0.263857554
j4,l1,0.175661049,0.102514669,0.5851311730000001,,0.7831020040000001,,,,,,,,
j4,l3,,,,,,,,,,,,0.3741985,
j4,l4,,0.792360642,,,,,,0.948836169,,,,,0.414599358
j2,l1,,,,,,,,,,,,0.698580858,
j2,l3,,,,0.576299805,,,,0.187448731,,,,,
j2,l4,,,,0.006008368,,,,0.540400638,0.543870155,0.485176103,,,
$offEcho

put_utility 'shell.checkErrorLevel' / 'diff -q p4_order_out_2.csv p4_order_ref_2.csv > %system.nullFile%';

*do not through an exception if there is no data
parameter p1(*);
EmbeddedCode Connect:
- GAMSReader:
    symbols:
        - name: p1
- CSVWriter:
    file: p1_empty.csv
    name: p1
EndEmbeddedCode

*case insensitive symbols names
EmbeddedCode Connect:
- GAMSReader:
    symbols:
        - name: p1
- CSVWriter:
    file: p1_cicap.csv
    name: P1
EndEmbeddedCode


*** unstack more than one index column ***
* test for correct order in header (after CSVReader with multi-row header)
$onEcho > multirow_header_order_in.csv
region,Region5,Region4,Region3,Region2,Region1
sector,Sector1,Sector2,Sector3,Sector4,Sector5
region,,,,,
Region1,,,,,0.01
Region2,,0.0123,,,
Region3,,,0.0456,,
Region4,0.34,,,0.0789,
Region5,,,,,0.003212
Region6,,0.00452,0.521,0.221,0.6754
Region7,,0.0,,,
$offEcho

$onEmbeddedCode Connect:
- CSVReader:
    file: multirow_header_order_in.csv
    name: p
    header: [1,2]
    indexColumns: [1]
    fieldSeparator: ","
    indexSubstitutions: {Region1: RegionX}
- PythonCode:
    code: |
        sym = connect.container["p"]
        sym.records.sort_values(sym.records.columns[:-1].tolist(), inplace=True)
        data = sym.records.values.tolist()
        expected = [['RegionX', 'RegionX', 'Sector5', 0.01],
                    ['Region2', 'Region4', 'Sector2', 0.0123],
                    ['Region3', 'Region3', 'Sector3', 0.0456],
                    ['Region4', 'Region5', 'Sector1', 0.34],
                    ['Region4', 'Region2', 'Sector4', 0.0789],
                    ['Region5', 'RegionX', 'Sector5', 0.003212],
                    ['Region6', 'Region4', 'Sector2', 0.00452],
                    ['Region6', 'Region3', 'Sector3', 0.521],
                    ['Region6', 'Region2', 'Sector4', 0.221],
                    ['Region6', 'RegionX', 'Sector5', 0.6754],
                    ['Region7', 'Region4', 'Sector2', 0.0]]
        if data != expected:
            raise Exception("Unexpected data p.")
- CSVWriter:
    file: multirow_header_order_out.csv
    name: p
    unstack: [2,3]
$offEmbeddedCode

$onEcho > multirow_header_order_ref.csv
region_1,Region5,Region4,Region3,Region2,RegionX
sector_2,Sector1,Sector2,Sector3,Sector4,Sector5
region_0,,,,,
RegionX,,,,,0.01
Region2,,0.0123,,,
Region3,,,0.0456,,
Region4,0.34,,,0.0789,
Region5,,,,,0.003212
Region6,,0.00452,0.521,0.221,0.6754
Region7,,0.0,,,
$offEcho

put_utility 'shell.checkErrorLevel' / 'diff -q multirow_header_order_out.csv multirow_header_order_ref.csv > %system.nullFile%';

* test for correct order in index and header (unstack index 2,4)
EmbeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- CSVWriter:
    file: p4_order_unstack_2_4.csv
    name: p4
    unstack: [2,4]
endEmbeddedCode

$onEcho > p4_order_unstack_2_4_ref.csv
j,,j1,j1,j1,j1,j2,j2,j2,j3,j3,j3,j3,j4,j4,j4
l,,l1,l2,l3,l4,l1,l3,l4,l1,l2,l3,l4,l1,l3,l4
i,k,,,,,,,,,,,,,,
i4,k3,,,,,,,,,,,,0.102514669,,0.792360642
i4,k2,0.500210669,,,,,,,,,0.33855027200000004,,0.175661049,,
i4,k1,,,,,,,,,,,,0.5851311730000001,,
i3,k3,,,,,,,,,,,,0.7831020040000001,,
i3,k2,,0.202015557,0.269613052,,,0.576299805,0.006008368,,,,,,,
i3,k1,,,,,,,,0.40134625700000004,,,,,,
i2,k3,,,,,,0.187448731,0.540400638,0.177822574,0.016643898,,,,,0.948836169
i2,k2,,,,0.15034771600000002,,,,0.196093864,,,,,,
i2,k1,,,,,,,0.543870155,,,,,,,
i1,k4,,,,,,,,,,,0.263857554,,,0.41459935800000003
i1,k3,0.36863057200000005,,,0.84181978,,,,,,,,,,
i1,k2,,,,,,,0.485176103,,0.32300194000000004,,,,,
i1,k1,0.7737034340000001,,,,0.698580858,,,,,,,,0.37419850000000004,
$offEcho

put_utility 'shell.checkErrorLevel' / 'diff -q p4_order_unstack_2_4.csv p4_order_unstack_2_4_ref.csv > %system.nullFile%';

* test for correct order in index and header (unstack index 1,2,3)
EmbeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- CSVWriter:
    file: p4_order_unstack_1_2_3.csv
    name: p4
    unstack: [1,2,3]
endEmbeddedCode

$onEcho > p4_order_unstack_1_2_3_ref.csv
i,i4,i4,i4,i4,i4,i3,i3,i3,i3,i2,i2,i2,i2,i2,i2,i1,i1,i1,i1,i1,i1,i1,i1
j,j1,j3,j4,j4,j4,j1,j2,j3,j4,j1,j2,j2,j3,j3,j4,j1,j1,j2,j2,j3,j3,j4,j4
k,k2,k2,k3,k2,k1,k2,k2,k1,k3,k2,k3,k1,k3,k2,k3,k3,k1,k2,k1,k4,k2,k4,k1
l,,,,,,,,,,,,,,,,,,,,,,,
l1,0.500210669,,0.102514669,0.175661049,0.5851311730000001,,,0.40134625700000004,0.7831020040000001,,,,0.177822574,0.196093864,,0.36863057200000005,0.7737034340000001,,0.698580858,,,,
l2,,,,,,0.202015557,,,,,,,0.016643898,,,,,,,,0.32300194000000004,,
l3,,0.33855027200000004,,,,0.269613052,0.576299805,,,,0.187448731,,,,,,,,,,,,0.37419850000000004
l4,,,0.792360642,,,,0.006008368,,,0.15034771600000002,0.540400638,0.543870155,,,0.948836169,0.84181978,,0.485176103,,0.263857554,,0.41459935800000003,
$offEcho

put_utility 'shell.checkErrorLevel' / 'diff -q p4_order_unstack_1_2_3.csv p4_order_unstack_1_2_3_ref.csv > %system.nullFile%';

* test for correct order in index and header (unstack index 1)
EmbeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- CSVWriter:
    file: p4_order_unstack_1.csv
    name: p4
    unstack: [1]
endEmbeddedCode

$onEcho > p4_order_unstack_1_ref.csv
j,k,l,i4,i3,i2,i1
j1,k3,l1,,,,0.36863057200000005
j1,k3,l4,,,,0.84181978
j1,k2,l1,0.500210669,,,
j1,k2,l2,,0.202015557,,
j1,k2,l3,,0.269613052,,
j1,k2,l4,,,0.15034771600000002,
j1,k1,l1,,,,0.7737034340000001
j2,k3,l3,,,0.187448731,
j2,k3,l4,,,0.540400638,
j2,k2,l3,,0.576299805,,
j2,k2,l4,,0.006008368,,0.485176103
j2,k1,l1,,,,0.698580858
j2,k1,l4,,,0.543870155,
j3,k4,l4,,,,0.263857554
j3,k3,l1,,,0.177822574,
j3,k3,l2,,,0.016643898,
j3,k2,l1,,,0.196093864,
j3,k2,l2,,,,0.32300194000000004
j3,k2,l3,0.33855027200000004,,,
j3,k1,l1,,0.40134625700000004,,
j4,k4,l4,,,,0.41459935800000003
j4,k3,l1,0.102514669,0.7831020040000001,,
j4,k3,l4,0.792360642,,0.948836169,
j4,k2,l1,0.175661049,,,
j4,k1,l1,0.5851311730000001,,,
j4,k1,l3,,,,0.37419850000000004
$offEcho

put_utility 'shell.checkErrorLevel' / 'diff -q p4_order_unstack_1.csv p4_order_unstack_1_ref.csv > %system.nullFile%';

* test unstack index 3,4 for a set
Set ijkl(i,j,k,l);
ijkl(i,j,k,l)$(uniform(0,1)<0.1)=yes;
EmbeddedCode Connect:
- GAMSReader:
    symbols:
      - name: ijkl
- CSVWriter:
    file: ijkl_unstack.csv
    name: ijkl
    unstack: [3,4]
endEmbeddedCode

$onEcho > ijkl_unstack_ref.csv
k,,k4,k4,k4,k4,k3,k3,k3,k2,k2,k2,k2,k1,k1,k1,k1
l,,l1,l2,l3,l4,l1,l2,l3,l1,l2,l3,l4,l1,l2,l3,l4
i,j,,,,,,,,,,,,,,,
i4,j1,,,,,Y,,,Y,,,,,,,
i4,j2,,,Y,,,,,,,,,,,,
i4,j3,,Y,,,,,,,,,,,Y,Y,
i4,j4,,,,,Y,,,,,Y,,,,,
i3,j1,,,,,,,,,,Y,,Y,,,
i3,j2,,,,,,,,,Y,,,,,,
i3,j3,,,,,,,Y,,Y,,Y,,,,
i2,j1,,,,Y,,,,,,,,,,,
i2,j2,,,,,,,,,,,Y,,Y,,
i2,j3,,,,,,,,,,,Y,,,,
i2,j4,,,,,,Y,,,,,,,,Y,
i1,j1,Y,,,Y,,,,,,Y,,Y,,,
i1,j2,,,,,,,,,Y,,,,,,
i1,j4,,,,,,,,,,,,,Y,,Y
$offEcho

put_utility 'shell.checkErrorLevel' / 'diff -q ijkl_unstack.csv ijkl_unstack_ref.csv > %system.nullFile%';

* test CSVReader create categorical but never sort and CSVWriter always sort
$onEcho > ij_not_sorted.csv
i,j,value
i1,j2,1
i1,j1,2
i2,j1,4
i2,j2,3
$offEcho

EmbeddedCode Connect:
- CSVReader:
    file: ij_not_sorted.csv
    name: not_sorted
    valueColumns: 3
    indexColumns: [1,2]
- PythonCode:
    code: |
        # checks that categoricals are correct
        sym = connect.container.data["not_sorted"]
        data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
        expected = [['i1', 'j2', 1.0],
                    ['i1', 'j1', 2.0],
                    ['i2', 'j2', 3.0],
                    ['i2', 'j1', 4.0]]
        if data_sorted != expected:
            raise Exception("Unexpected sorted data not_sorted.")
- CSVWriter:
    file: ij_sorted.csv
    name: not_sorted
endEmbeddedCode

$onEcho > ij_sorted_ref.csv
i,j,value
i1,j2,1.0
i1,j1,2.0
i2,j2,3.0
i2,j1,4.0
$offEcho

put_utility 'shell.checkErrorLevel' / 'diff -q ij_sorted.csv ij_sorted_ref.csv > %system.nullFile%';

$onEcho > ijk_not_sorted.csv
i,j,k1,k2
i1,j2,,1
i1,j1,2,3
i2,j1,6,
i2,j2,4,5
$offEcho

EmbeddedCode Connect:
- CSVReader:
    file: ijk_not_sorted.csv
    name: not_sorted
    valueColumns: "3:lastCol"
    indexColumns: [1,2]
- PythonCode:
    code: |
        # checks that categoricals are correct
        sym = connect.container.data["not_sorted"]
        data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
        expected = [['i1', 'j2', 'k2', 1.0],
                    ['i1', 'j1', 'k1', 2.0],
                    ['i1', 'j1', 'k2', 3.0],
                    ['i2', 'j2', 'k1', 4.0],
                    ['i2', 'j2', 'k2', 5.0],
                    ['i2', 'j1', 'k1', 6.0]]
        if data_sorted != expected:
            raise Exception("Unexpected sorted data not_sorted.")
- CSVWriter:
    file: ijk_sorted.csv
    name: not_sorted
    unstack: True
endEmbeddedCode

$onEcho > ijk_sorted_ref.csv
i,j,k1,k2
i1,j2,,1.0
i1,j1,2.0,3.0
i2,j2,4.0,5.0
i2,j1,6.0,
$offEcho

put_utility 'shell.checkErrorLevel' / 'diff -q ijk_sorted.csv ijk_sorted_ref.csv > %system.nullFile%';


EmbeddedCode Connect:
- PythonCode:
    code: |
      connect.container.addSet("k", domain=["i", "*", "j", "*"])
- CSVWriter:
    file: none_set.csv
    name: k
endEmbeddedCode

$onEcho > none_set_ref.csv
i_0,uni_1,j_2,uni_3,element_text
$offEcho

put_utility 'shell.checkErrorLevel' / 'diff -q none_set.csv none_set_ref.csv > %system.nullFile%';


EmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: i
      - name: j
- PythonCode:
    code: |
      connect.container.addParameter("p", domain=["i", "*", "j", "i"])
- CSVWriter:
    file: none_par.csv
    name: p
endEmbeddedCode

$onEcho > none_par_ref.csv
i_0,uni_1,j_2,i_3,value
$offEcho

put_utility 'shell.checkErrorLevel' / 'diff -q none_par.csv none_par_ref.csv > %system.nullFile%';

* test writing of special values as strings (default)
$onEcho > sv_in.csv
uni,value
i1,EPS
i2,-INF
i3,INF
i4,UNDEF
i5,NA
i6,0.0
i7,1.0
$offEcho
embeddedCode Connect:
- CSVReader:
    file: sv_in.csv
    name: p
    indexColumns: 1
    valueColumns: 2
    readCSVArguments: {"keep_default_na": False}
- CSVWriter:
    file: sv.csv
    name: p
endEmbeddedCode

put_utility 'shell.checkErrorLevel' / 'diff -q sv.csv sv_in.csv > %system.nullFile%';


* test value substitutions for special values
$onEcho > sv_ref_1.csv
uni,value
i1,0.0
i2,1.0
i3,2.0
i4,3.0
i5,4.0
i6,0.0
i7,1.0
$offEcho
embeddedCode Connect:
- CSVReader:
    file: sv_in.csv
    name: p
    indexColumns: 1
    valueColumns: 2
    readCSVArguments: {"keep_default_na": False}
- CSVWriter:
    file: sv.csv
    name: p
    valueSubstitutions: {
      "EPS": 0,
      "-INF": 1,
      "INF": 2,
      "UNDEF": 3,
      "NA": 4
    }
endEmbeddedCode

put_utility 'shell.checkErrorLevel' / 'diff -q sv.csv sv_ref_1.csv > %system.nullFile%';


* test value substitutions for special values not being transitive
$onEcho > sv_ref_2.csv
uni,value
i2,0.0
i4,1.0
i5,2.0
i6,0.0
i7,1.0
$offEcho
embeddedCode Connect:
- CSVReader:
    file: sv_in.csv
    name: p
    indexColumns: 1
    valueColumns: 2
    readCSVArguments: {"keep_default_na": False}
- CSVWriter:
    file: sv.csv
    name: p
    valueSubstitutions: {
      "EPS": .nan,  # gets dropped - .nan is gt.SpecialValues.Undef and should not be replaced by the UNDEF:1 value substitution
      "-INF": 0,
      "INF": .nan,  # gets dropped
      "UNDEF": 1,
      "NA": 2
    }
- CSVReader:
    file: sv.csv
    name: p1
    indexColumns: 1
    valueColumns: 2
    #readCSVArguments: {"keep_default_na": False}
- CSVWriter:
    file: sv.csv
    name: p1
endEmbeddedCode

put_utility 'shell.checkErrorLevel' / 'diff -q sv.csv sv_ref_2.csv > %system.nullFile%';


* test 'decimalSeparator: ","' working properly if no (special) values are replaced by strings
$onEcho > sv_in2.csv
uni,value
i1,EPS
i2,INF
i3,NA
i4,3.0
i5,10.0
$offEcho
$onEcho > sv_ref_3.csv
uni,value
i1,"0,0"
i2,"1,0"
i3,"2,0"
i4,"3,0"
i5,"4,0"
$offEcho
embeddedCode Connect:
- CSVReader:
    file: sv_in2.csv
    name: p
    indexColumns: 1
    valueColumns: 2
    readCSVArguments: {"keep_default_na": False}
- CSVWriter:
    file: sv.csv
    name: p
    decimalSeparator: ","
    valueSubstitutions: {
      "EPS": 0.0,
      "INF": 1.0,
      "NA": 2.0,
      10.0: 4.0
    }
endEmbeddedCode

put_utility 'shell.checkErrorLevel' / 'diff -q sv.csv sv_ref_3.csv > %system.nullFile%';


* check that replacing with string values is warning free if the DataFrame was not yet converted to dtype object
$onEcho > sv_ref_4.csv
uni,value
i1,0.0
i2,1.0
i3,2.0
i4,3.0
i5,some string
$offEcho
embeddedCode Connect:
- CSVReader:
    file: sv_in2.csv
    name: p
    indexColumns: 1
    valueColumns: 2
    readCSVArguments: {"keep_default_na": False}
- CSVWriter:
    file: sv.csv
    name: p
    valueSubstitutions: {
      "EPS": 0.0,
      "INF": 1.0,
      "NA": 2.0,
      10.0: "some string"
    }
endEmbeddedCode

put_utility 'shell.checkErrorLevel' / 'diff -q sv.csv sv_ref_4.csv > %system.nullFile%';