$title 'Test Connect agent CSVReader' (CACSVR,SEQ=897)

$onText
This test ensures the correctness of the Connect agent "CSVReader".

Contributor: Aileen Böhme, March 2022
$offText


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

$onEcho > empty.csv
$offEcho

$onEcho > scalar.csv
100
$offEcho

$onEcho > iPar.csv
i3,i2,i1
0.457,0.323,0.315
$offEcho

$onEcho > iPar_sub.csv
i3,i2,i1
0.457,,0.315
$offEcho

$onEcho > ijPar.csv
i1;j1;2,5
i1;;1,7
i2;j1;1,8
i2;;1,4
$offEcho

$onEcho > distance1.csv
,new-york,chicago,topeka
seattle,2.5,1.7,1.8
san-diego,2.5,1.8,1.4
$offEcho

$onEcho > distance2.csv
i;j;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
$offEcho

$onEcho > distance3.csv
#new-york# chicago#topeka
seattle#2.5#  1.7#1.8

 san-diego#2.5#NA#1.4
$offEcho

$onEcho > networkData.csv
plant;station;length;minCap;maxCap;stage;cost
p1;s1;100;50;100;1;1200
p1;s2;75;35;65;1;500
p1;s1;100;100;150;2;1800
p2;s1;150;50;100;1;1400
p2;s1;150;100;150;2;2000
p2;s1;150;150;200;3;2350
p2;s2;75;25;50;1;600
p2;s2;75;50;75;2;800
p3;s1;80;40;100;1;1050
$offEcho

$onEcho > mixedData.csv
one,two,three,four,five,six
red,red,,Undef,'3.3',red
red,red,"4.4",5.5,Eps,green
"red",'green',7.7e+02,8.8°,-Inf,blue
blue,blue,10,0,NA,purple
brown,blue,true,false,N/A,green
black,red,None,Null,"Yes",blue
$offEcho

$onEcho > EUCData.csv
565.0;575.0
25.0;185.0
345.0;750.0
945.0;685.0
845.0;655.0
880.0;660.0
25.0;230.0
525.0;1000.0
580.0;1175.0
$offEcho

$onEcho > ijSet.csv
i1,j1,text1
i1,j2,text2
i1,j3,
i2,j1,
i2,j2,text5
,j3,text6
$offEcho

$onEcho > jkSet.csv
j1,k1
j2,
j3,k3
$offEcho

$onEcho > ijkSet.csv
,,j1,j2,j3
i1,k1,Y,Y,Y
i2,k1,Y,Y,N
i3,k2,N,Y,Y
$offEcho

$onEcho > trailing_spaces.csv
,"new-york ","chicago   ",topeka
"seattle   ",2.5,1.7,1.8
"san-diego ",2.5,1.8,1.4
$offEcho

$onEcho > leading_zeros.csv
i,j,value
01,010,1.0
02,020,2.0
03,030,3.0
$offEcho

$onEcho > no_header.csv
12,3,4
2,7,8
18,2,5
$offEcho

$onEcho > multirow_header_special_values.csv
region,Region5,Region4,Region3,Region2,Region1
sector,Sector1,Sector2,Sector3,Sector4,Sector5
region,,,,,
Region1,,-Inf,,NA,0.01
,,0.0123,,,
Region3,,,0.0456,,
Region4,0.34,,Eps,0.0789,
Region5,,Undef,,,0.003212
Region6,,0.00452,0.521,0.221,0.6754
Region7,,0.0,,,
$offEcho

$onEcho > multirow_header_2x2.csv
region,,Region1,Region1,Region1,Region1,Region1
sector,,Sector1,Sector2,Sector3,Sector4,Sector5
region,sector,,,,,
Region1,Sector1,,,,,
Region1,Sector2,,0.0123,,,
Region1,Sector3,,,0.0456,,
Region1,Sector4,,,,0.0789,
Region1,Sector5,,,,,0.003212
Region1,Sector6,,0.00452,0.521,0.221,0.6754
Region1,Sector7,5,,,,
$offEcho

$onEcho > multirow_header_no_index_column_names.csv
,Region1,Region1,Region1,Region1,Region1
,Sector1,Sector2,Sector3,Sector4,Sector5
Region1,,,0.5,,
Region2,,0.0123,,,
Region3,,,0.0456,,
Region4,,,,0.0789,
Region5,,,,,0.003212
Region6,,0.00452,0.521,0.221,0.6754
Region7,5.0,,,,
$offEcho

$onEcho > multirow_header_2x2_sub.csv
j;j1;j1;j1;j2;j2;j2
k;k1;k2;k3;k1;k2;k3
i;;;;;;
;1;2;;4;5;6
i2;;100;3;4;5;
$offEcho

$onEcho > multirow_header_0x2.csv
j1,j1,j1,j2,j2,j2
k1,k2,k3,k1,k2,k3
1,2,,4,5,6
,,3,4,5,
$offEcho

$onEcho > distributed_multirow_header_1x2.csv
j,j1,j1,j1,j2,j2,j2
i1,1,2,,4,5,6
k,k1,k2,k3,k1,k2,k3
i2,,,3,4,5,
i3,1,,,4,,6
$offEcho

$onEcho > multirow_header_2x2_distributed_index_columns.csv
j,j1,j1,,j2,j2
k,k1,k2,,k1,k2
h1,1,2,i1,3,4
h1,5,6,i2,7,8
$offEcho

$onEcho > abc_data.csv
I,II,III,IV,V,VI,VII
a,b,1,e,3,h,6
b,c,2,f,4,i,7
c,d,3,g,5,j,8
$offEcho

* ### expected errors ###
$log test file missing
$onEmbeddedCode Connect:
- CSVReader:
    name: p 
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test name missing
$onEmbeddedCode Connect:
- CSVReader:
    file: iPar.csv 
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test stack without a header, names or autoColumn
$onEmbeddedCode Connect:
- CSVReader:
    file: iPar.csv
    name: p
    valueColumns: "1"
    stack: True
    header: False
    skipRows: 1
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test using lastCol without a header or names
$onEmbeddedCode Connect:
- CSVReader:
    file: iPar.csv
    name: p
    valueColumns: "1:lastCol"
    header: False
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test column assignation as string
$onEmbeddedCode Connect:
- CSVReader:
    file: iPar.csv
    name: p
    valueColumns: "i3,i2,i1"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test mixed column assignation
$onEmbeddedCode Connect:
- CSVReader:
    file: iPar.csv
    name: p
    valueColumns: "2,3"
    indexColumns: ["i3"]
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test more than one value column without header, names or autoColumn
$onEmbeddedCode Connect:
- CSVReader:
    file: iPar.csv
    name: p
    valueColumns: "1,2,3"
    header: False
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test set has no index column(s)
$onEmbeddedCode Connect:
- CSVReader:
    file: iPar.csv
    name: p
    valueColumns: "1"
    header: False
    type: set
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test header with multiple rows with valueColumns
$onEmbeddedCode Connect:
- CSVReader:
    file: multirow_header_2x2.csv
    name: p
    header: [1,2]
    indexColumns: [1,2]
    valueColumns: "3:lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test header with multiple rows for set
$onEmbeddedCode Connect:
- CSVReader:
    file: multirow_header_2x2.csv
    name: p
    header: [1,2]
    indexColumns: [1,2]
    valueColumns: "3:lastCol"
    type: set
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test header with multiple rows and indexColumns given as column names
$onEmbeddedCode Connect:
- CSVReader:
    file: multirow_header_2x2.csv
    name: p
    header: [1,2]
    indexColumns: ["region","sector"]
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test duplicates in indexColumns
$onEmbeddedCode Connect:
- CSVReader:
    file: abc_data.csv
    name: p
    header: True
    indexColumns: [3,3,2]
    valueColumns: [5]
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test duplicates in valueColumns
$onEmbeddedCode Connect:
- CSVReader:
    file: abc_data.csv
    name: p
    header: True
    indexColumns: [3,2]
    valueColumns: [5,5]
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test name already exists
$onEmbeddedCode Connect:
- PythonCode:
    code: |
        connect.container.addParameter("a", domain="*")
- CSVReader:
    file: iPar.csv
    name: a
    valueColumns: "1:lastCol"
    header: True
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test parameter without valueColumns
$onEmbeddedCode Connect:
- CSVReader:
    file: abc_data.csv
    name: p
    header: True
    indexColumns: [3,2]
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test parameter is missing valueColumns
$onEmbeddedCode Connect:
- CSVReader:
    file: abc_data.csv
    name: p
    header: True
    indexColumns: [3,2]
    valueColumns: []
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test multi-row header with stack=False
$onEmbeddedCode Connect:
- CSVReader:
    file: multirow_header_0x2.csv
    name: p
    header: [1,2]
    stack: False
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors


$log test more than one value column with stack=False
$onEmbeddedCode Connect:
- CSVReader:
    file: distance1.csv
    name: p
    valueColumns: "2:lastCol"
    indexColumns: "1"
    stack: False
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

* ### end expected errors ###

$log read 1-dim parameter from 1-dimensional data with a header
$onEmbeddedCode Connect:
- CSVReader:
    file: iPar.csv
    name: p
    valueColumns: "1:lastCol"
    header: True
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['i3', 0.457], ['i2', 0.323], ['i1', 0.315]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log read scalar
$onEmbeddedCode Connect:
- CSVReader:
    file: scalar.csv
    name: p
    valueColumns: 1
    header: False
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [[100.0,]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log test empty data with skipRows
$onEmbeddedCode Connect:
- CSVReader:
    file: scalar.csv
    name: p
    valueColumns: 1
    header: False
    skipRows: 1
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = []
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log read empty CSV file
$onEmbeddedCode Connect:
- CSVReader:
    file: empty.csv
    name: p
    valueColumns: [3,4]
    indexColumns: [1,2]
    header: True
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = []
        if data != expected:
            raise Exception("Unexpected data p.")
            
        dim = connect.container.data["p"].dimension
        expected = 3
        if dim != expected:
            raise Exception("Unexpected dimension.") 
$offEmbeddedCode

$log read scalar from 2-dimensional data with a header using skipRows
$onEmbeddedCode Connect:
- CSVReader:
    file: distance1.csv
    name: p
    valueColumns: 2
    skipRows: [1,3]
    header: False
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [[2.5,]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log read 1-dim parameter from 2-dimensional data with a header
$onEmbeddedCode Connect:
- CSVReader:
    file: distance1.csv
    name: p
    indexColumns: 1
    valueColumns: "2"
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['seattle', 2.5],
                    ['san-diego', 2.5]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log read 2-dim parameter from 2-dimensional data with a header
$onEmbeddedCode Connect:
- CSVReader:
    file: distance1.csv
    name: p
    indexColumns: 1
    valueColumns: "2:lastCol"
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['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]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log read 1-dim parameter from 4-dimensional data with a header and mixed index/value columns using skipRows and nrows via readCSVArguments
$onEmbeddedCode Connect:
- CSVReader:
    file: networkData.csv
    name: p
    indexColumns: 2
    valueColumns: 7
    skipRows: 1
    header: False
    fieldSeparator: ";"
    readCSVArguments: { nrows: 2 }

- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['s1', 1200.0],
                    ['s2', 500.0]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log read 3-/4-dim parameter from 4-dimensional data with a header and mixed index/value columns using nrows via readCSVArguments
$onEmbeddedCode Connect:
- PythonCode:
    code: |
        valueColumns = [5, [4,5], "3,4,5,7"]
        for i in range(3):
            instructions.append({"CSVReader": {"file": "networkData.csv", "name": f"p{i}", "indexColumns": "1,2,6", "valueColumns": valueColumns[i], "fieldSeparator": ";", "readCSVArguments": { "nrows": 3 }}})
- PythonCode:
    code: |
        expected = [
        [['p1', 's1', '1', 100.0], ['p1', 's2', '1', 65.0], ['p1', 's1', '2', 150.0]],      
        [['p1', 's1', '1', 'minCap', 50.0], ['p1', 's1', '1', 'maxCap', 100.0],
         ['p1', 's2', '1', 'minCap', 35.0], ['p1', 's2', '1', 'maxCap', 65.0],
         ['p1', 's1', '2', 'minCap', 100.0], ['p1', 's1', '2', 'maxCap', 150.0]],
        [['p1', 's1', '1', 'length', 100.0], ['p1', 's1', '1', 'minCap', 50.0],
         ['p1', 's1', '1', 'maxCap', 100.0], ['p1', 's1', '1', 'cost', 1200.0],
         ['p1', 's2', '1', 'length', 75.0], ['p1', 's2', '1', 'minCap', 35.0],
         ['p1', 's2', '1', 'maxCap', 65.0], ['p1', 's2', '1', 'cost', 500.0],
         ['p1', 's1', '2', 'length', 100.0], ['p1', 's1', '2', 'minCap', 100.0],
         ['p1', 's1', '2', 'maxCap', 150.0], ['p1', 's1', '2', 'cost', 1800.0]]
        ]
        for i in range(3):
            data = connect.container.data[f"p{i}"].records.values.tolist()
            if data != expected[i]:
                raise Exception(f"Unexpected data p{i}.")
$offEmbeddedCode

$log read 1-dim set from 2-dimensional data with a header
$onEmbeddedCode Connect:
- CSVReader:
    file: distance1.csv
    name: s
    type: set
    indexColumns: 1
- PythonCode:
    code: |
        data = connect.container.data["s"].records.values.tolist()
        expected = [['seattle', ''],
                    ['san-diego', '']]
        if data != expected:
            raise Exception("Unexpected data s.")
$offEmbeddedCode

$log read 2-dim set from 2-dimensional data
$onEmbeddedCode Connect:
- CSVReader:
    file: ijSet.csv
    name: s
    type: set
    indexColumns: [2,1]
    valueColumns: "3"
    header: False
- PythonCode:
    code: |
        data = connect.container.data["s"].records.values.tolist()
        expected = [['j1', 'i1', 'text1'],
                    ['j2', 'i1', 'text2'],
                    ['j2', 'i2', 'text5']]
        if data != expected:
            raise Exception("Unexpected data s.")
$offEmbeddedCode

$log read 2-dim set from 2-dimensional data with names turning header="infer" into False
$onEmbeddedCode Connect:
- CSVReader:
    file: ijSet.csv
    name: s
    type: set
    indexColumns: [2,1]
    valueColumns: "3"
    names: ["j", "i", "k1"]
    stack: True
- PythonCode:
    code: |
        data = connect.container.data["s"].records.values.tolist()
        expected = [['j1', 'i1', 'k1', 'text1'],
                    ['j2', 'i1', 'k1', 'text2'],
                    ['j2', 'i2', 'k1', 'text5']]
        if data != expected:
            raise Exception("Unexpected data s.")
$offEmbeddedCode

$log read 2-dim set from 2-dimensional data using valueSubstitutions and indexSubstitutions
$onEmbeddedCode Connect:
- CSVReader:
    file: ijSet.csv
    name: s
    type: set
    indexColumns: [1,2]
    indexSubstitutions: {.nan: i2}
    valueColumns: "3"
    valueSubstitutions: {.nan: ""}
    header: False
- PythonCode:
    code: |
        data = connect.container.data["s"].records.values.tolist()
        expected = [['i1', 'j1', 'text1'],
                    ['i1', 'j2', 'text2'],
                    ['i1', 'j3', ''],
                    ['i2', 'j1', ''],
                    ['i2', 'j2', 'text5'],
                    ['i2', 'j3', 'text6']]
        if data != expected:
            raise Exception("Unexpected data s.")
$offEmbeddedCode

$log read 2-dim set from 2-dimensional data
$onEmbeddedCode Connect:
- CSVReader:
    file: ijSet.csv
    name: s
    type: set
    indexColumns: "1,2"
    header: False
- PythonCode:
    code: |
        data = connect.container.data["s"].records.values.tolist()
        expected = [['i1', 'j1', ''],
                    ['i1', 'j2', ''],
                    ['i1', 'j3', ''],
                    ['i2', 'j1', ''],
                    ['i2', 'j2', '']]
        if data != expected:
            raise Exception("Unexpected data s.")
$offEmbeddedCode

$log read 1-dim set from 3-dimensional data with a header using skipRows and valueSubstitutions
$onEmbeddedCode Connect:
- CSVReader:
    file: ijkSet.csv
    name: s
    type: set
    valueColumns: "3:lastCol"
    valueSubstitutions: {"N": .nan}
    header: True
    skipRows: [2,4]
- PythonCode:
    code: |
        data = connect.container.data["s"].records.values.tolist()
        expected = [['j1', 'Y'], ['j2', 'Y']]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log read 3-dim set from 3-dimensional data with a header using valueSubstitutions
$onEmbeddedCode Connect:
- CSVReader:
    file: ijkSet.csv
    name: s
    type: set
    indexColumns: "1,2"
    valueColumns: "3:lastCol"
    valueSubstitutions: {"N": .nan}
    header: True
- PythonCode:
    code: |
        data = connect.container.data["s"].records.values.tolist()
        expected = [['i1', 'k1', 'j1', 'Y'],
                    ['i1', 'k1', 'j2', 'Y'],
                    ['i1', 'k1', 'j3', 'Y'],
                    ['i2', 'k1', 'j1', 'Y'],
                    ['i2', 'k1', 'j2', 'Y'],
                    ['i3', 'k2', 'j2', 'Y'],
                    ['i3', 'k2', 'j3', 'Y']]
        if data != expected:
            raise Exception("Unexpected data s.")
$offEmbeddedCode

$log read 3-dim set from 4-dimensional data with a header
$onEmbeddedCode Connect:
- CSVReader:
    file: networkData.csv
    name: s
    type: set
    indexColumns: ["plant","station","stage"]
    fieldSeparator: ";"
- PythonCode:
    code: |
        data = connect.container.data["s"].records.values.tolist()
        expected = [['p1', 's1', '1', ''],
                    ['p1', 's2', '1', ''],
                    ['p1', 's1', '2', ''],
                    ['p2', 's1', '1', ''],
                    ['p2', 's1', '2', ''],
                    ['p2', 's1', '3', ''],
                    ['p2', 's2', '1', ''],
                    ['p2', 's2', '2', ''],
                    ['p3', 's1', '1', '']]
        if data != expected:
            raise Exception("Unexpected data s.")
$offEmbeddedCode

$log read 4-dim parameter from 4-dimensional data with a header and mixed index/value columns using valueSubstitutions and readCSVArguments
$onEmbeddedCode Connect:
- CSVReader:
    file: mixedData.csv
    name: p
    indexColumns: ["one","two","six"]
    valueColumns: ["three","four","five"]
    valueSubstitutions: {"'3.3'": Undef, 8.8°: 123, "true": 1, "false": 0, N/A: NA, "None": 0, "Null": 0, "Yes": 1}
    readCSVArguments: {"keep_default_na": False, "na_values": [""]}
- PythonCode:
    code: |
        import pandas as pd
        data = pd.DataFrame(connect.container.data["p"].records.values.tolist())
        expected = pd.DataFrame([('red', 'red', 'red', 'four', float("nan")),
                    ('red', 'red', 'red', 'five', float("nan")),
                    ('red', 'red', 'green', 'three', 4.4),
                    ('red', 'red', 'green', 'four', 5.5),
                    ('red', 'red', 'green', 'five', -0.0),
                    ('red', "'green'", 'blue', 'three', 770.0),
                    ('red', "'green'", 'blue', 'four', 123),
                    ('red', "'green'", 'blue', 'five', float("-inf")),
                    ('blue', 'blue', 'purple', 'three', 10.0),
                    ('blue', 'blue', 'purple', 'four', 0.0),
                    ('blue', 'blue', 'purple', 'five', float("nan")),
                    ('brown', 'blue', 'green', 'three', 1.0),
                    ('brown', 'blue', 'green', 'four', 0.0),
                    ('brown', 'blue', 'green', 'five', float("nan")),
                    ('black', 'red', 'blue', 'three', 0.0),
                    ('black', 'red', 'blue', 'four', 0.0),
                    ('black', 'red', 'blue', 'five', 1.0)])
        if not data.equals(expected):
            raise Exception("Unexpected data p.")
- CSVWriter:
    file: mixedData_out.csv
    name: p
    unstack: True
$offEmbeddedCode
$onEcho > mixedData_ref.csv
one,two,six,three,four,five
red,red,red,,UNDEF,UNDEF
red,red,green,4.4,5.5,EPS
red,'green',blue,770.0,123.0,-INF
blue,blue,purple,10.0,0.0,NA
brown,blue,green,1.0,0.0,NA
black,red,blue,0.0,0.0,1.0
$offEcho
$call.checkErrorLevel diff -q mixedData_out.csv mixedData_ref.csv > %system.nullFile%

$log read 2-dim parameter from 2-dimensional data using indexSubstitutions
$onEmbeddedCode Connect:
- CSVReader:
    file: ijPar.csv
    name: p
    indexColumns: "1,2"
    indexSubstitutions: {.nan: j2}
    valueColumns: "3"
    header: False
    fieldSeparator: ";"
    decimalSeparator: ","
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['i1', 'j1', 2.5],
                    ['i1', 'j2', 1.7],
                    ['i2', 'j1', 1.8],
                    ['i2', 'j2', 1.4]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log read 2-dim parameter from 2-dimensional data with a header using names
$onEmbeddedCode Connect:
- CSVReader:
    file: distance1.csv
    name: p
    indexColumns: ["col0"]
    valueColumns: ["col1", "col2", "col3"]
    header: True
    names: ["col0", "col1", "col2","col3"]
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['seattle', 'col1', 2.5],
                    ['seattle', 'col2', 1.7],
                    ['seattle', 'col3', 1.8],
                    ['san-diego', 'col1', 2.5],
                    ['san-diego', 'col2', 1.8],
                    ['san-diego', 'col3', 1.4]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log read 2-dim parameter from 2-dimensional data with a header using autoColumn
$onEmbeddedCode Connect:
- CSVReader:
    file: distance1.csv
    name: p
    indexColumns: 1
    valueColumns: "2:lastCol"
    header: True
    autoColumn: "col"
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['seattle', 'col1', 2.5],
                    ['seattle', 'col2', 1.7],
                    ['seattle', 'col3', 1.8],
                    ['san-diego', 'col1', 2.5],
                    ['san-diego', 'col2', 1.8],
                    ['san-diego', 'col3', 1.4]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log read 3-dim parameter from 3-dimensional data with a header using stack
$log test relaxed domain information
$onEmbeddedCode Connect:
- CSVReader:
    file: distance2.csv
    name: p
    indexColumns: "1,2"
    valueColumns: "3"
    header: True
    stack: True
    fieldSeparator: ";"
    decimalSeparator: ","
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected_data = [['seattle', 'new-york', 'miles', 2.5],
                    ['seattle', 'chicago', 'miles', 1.7],
                    ['seattle', 'topeka', 'miles', 1.8],
                    ['san-diego', 'new-york', 'miles', 2.5],
                    ['san-diego', 'chicago', 'miles', 1.8],
                    ['san-diego', 'topeka', 'miles', 1.4]]
        if data != expected_data:
            raise Exception("Unexpected data p.")
        
        domain = connect.container.data["p"].domain_names
        expected_domain = ['i', 'j', '*']
        if domain != expected_domain:
            raise Exception("Unexpected domain for symbol p.")
$offEmbeddedCode

$log read 2-dim parameter from 2-dimensional data using autoColumn and autoRow
$onEmbeddedCode Connect:
- CSVReader:
    file: EUCData.csv
    name: p
    valueColumns: "1,2"
    header: False
    autoColumn: "x"
    autoRow: "city"
    fieldSeparator: ";"
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['city1', 'x1', 565.0], ['city1', 'x2', 575.0],
                    ['city2', 'x1', 25.0], ['city2', 'x2', 185.0],
                    ['city3', 'x1', 345.0], ['city3', 'x2', 750.0],
                    ['city4', 'x1', 945.0], ['city4', 'x2', 685.0],
                    ['city5', 'x1', 845.0], ['city5', 'x2', 655.0],
                    ['city6', 'x1', 880.0], ['city6', 'x2', 660.0],
                    ['city7', 'x1', 25.0], ['city7', 'x2', 230.0],
                    ['city8', 'x1', 525.0], ['city8', 'x2', 1000.0],
                    ['city9', 'x1', 580.0], ['city9', 'x2', 1175.0]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log read 1-dim parameter from 2-dimensional data using autoColumn and autoRow
$onEmbeddedCode Connect:
- CSVReader:
    file: EUCData.csv
    name: p
    valueColumns: "1"
    header: False
    autoColumn: "x"
    autoRow: "city"
    fieldSeparator: ";"
- PythonCode:
    code: |
        import pandas as pd
        data = connect.container.data["p"].records.values.tolist()
        expected = [['city1', 565.0],
                    ['city2', 25.0],
                    ['city3', 345.0],
                    ['city4', 945.0],
                    ['city5', 845.0],
                    ['city6', 880.0],
                    ['city7', 25.0],
                    ['city8', 525.0],
                    ['city9', 580.0]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log read 2-dim parameter from 2-dimensional data using autoColumn, autoRow and stack
$onEmbeddedCode Connect:
- CSVReader:
    file: EUCData.csv
    name: p
    valueColumns: "1"
    header: False
    autoColumn: "x"
    autoRow: "city"
    stack: True
    fieldSeparator: ";"
- PythonCode:
    code: |
        import pandas as pd
        data = connect.container.data["p"].records.values.tolist()
        expected = [['city1', 'x1', 565.0],
                    ['city2', 'x1', 25.0],
                    ['city3', 'x1', 345.0],
                    ['city4', 'x1', 945.0],
                    ['city5', 'x1', 845.0],
                    ['city6', 'x1', 880.0],
                    ['city7', 'x1', 25.0],
                    ['city8', 'x1', 525.0],
                    ['city9', 'x1', 580.0]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log read 2-dim parameter from 2-dimensional data using readCSVArguments
$onEmbeddedCode Connect:
- CSVReader:
    file: distance3.csv
    name: p
    indexColumns: 1
    valueColumns: "2:lastCol"
    readCSVArguments: {'sep': "#", 'skipinitialspace': True}
- PythonCode:
    code: |
        import pandas as pd
        data = connect.container.data["p"].records.values.tolist()
        expected = [['seattle', 'new-york', 2.5],
                    ['seattle', 'chicago', 1.7],
                    ['seattle', 'topeka', 1.8],
                    ['san-diego', 'new-york', 2.5],
                    ['san-diego', 'topeka', 1.4]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log read 2-dim set from 2-dimensional data without text and indexSubstitutions
$onEmbeddedCode Connect:
- CSVReader:
    file: jkSet.csv
    name: s
    type: set
    indexColumns: [1,2]
    header: False
    indexSubstitutions: {.nan: k2}
- PythonCode:
    code: |
        import pandas as pd
        data = connect.container.data["s"].records.values.tolist()
        expected = [['j1', 'k1', ''],
                    ['j2', 'k2', ''],
                    ['j3', 'k3', '']]
        if data != expected:
            raise Exception("Unexpected data s.")
$offEmbeddedCode

$log read labels with trailing spaces
$onEmbeddedCode Connect:
- CSVReader:
    file: trailing_spaces.csv
    name: p
    indexColumns: 1
    valueColumns: "2:lastCol"
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['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]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log test encodings
$onEmbeddedCode Python:
with open('cp1252.csv', 'w', encoding='cp1252') as f:
    f.write('ö, 1.0\nä, 2.0\nü, 3.0')
with open('cp1252_instructions.yaml', 'w', encoding='cp1252') as f:
    f.write('''# coding=cp1252
- CSVReader:
    file: cp1252.csv
    name: p
    indexColumns: 1
    valueColumns: 2
    readCSVArguments: {'encoding': 'cp1252'}
    header: false
    indexSubstitutions: {'ö': 'abc'}
- PythonCode:
    code: |     
        if connect.container.data["p"].records.values[:, 0].tolist() != ['abc', 'ä', 'ü']:
            raise Exception("Unexpected data p.")
''')
$offEmbeddedCode

$call.checkErrorLevel gamsconnect cp1252_instructions.yaml

$log test reading set with index and value columns as string
$onEmbeddedCode Connect:
- CSVReader:
    file: leading_zeros.csv
    name: p
    indexColumns: "1,2"
    valueColumns: "lastCol"
- CSVReader: 
    file: leading_zeros.csv
    name: s
    type: set
    indexColumns: "1"
    valueColumns: "2"
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['01', '010', 1.0],
                    ['02', '020', 2.0],
                    ['03', '030', 3.0]]
        if data != expected:
            raise Exception("Unexpected data p.")
            
        data = connect.container.data["s"].records.values.tolist()
        expected = [['01', '010'],
                    ['02', '020'],
                    ['03', '030']]
        if data != expected:
            raise Exception("Unexpected data s.")
$offEmbeddedCode

$log test indexSubstitutions on stacked columns
$onEmbeddedCode Connect:
- CSVReader:
    file: distance1.csv
    name: p
    valueColumns: "2:lastCol"
    indexColumns: "1"
    indexSubstitutions: {new-york: sub, chicago: .nan}
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['seattle', 'sub', 2.5],
                    ['seattle', 'topeka', 1.8],
                    ['san-diego', 'sub', 2.5],
                    ['san-diego', 'topeka', 1.4]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log test substitute NaN in value column
$onEmbeddedCode Connect:
- CSVReader:
    file: iPar_sub.csv
    name: p
    valueColumns: "1:lastCol"
    valueSubstitutions: {.nan: 1}
    header: True
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['i3', 0.457], ['i2', 1.0], ['i1', 0.315]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log test read unknown number of value columns
$onEmbeddedCode Connect:
- CSVReader:
    file: no_header.csv
    name: p
    valueColumns: "1:lastCol"
    header: False
    autoRow: r
    autoColumn: c
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['r1', 'c1', 12.0], 
                    ['r1', 'c2', 3.0], 
                    ['r1', 'c3', 4.0], 
                    ['r2', 'c1', 2.0], 
                    ['r2', 'c2', 7.0], 
                    ['r2', 'c3', 8.0], 
                    ['r3', 'c1', 18.0], 
                    ['r3', 'c2', 2.0], 
                    ['r3', 'c3', 5.0]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log test multi-row header (1x2) with special values
$onEmbeddedCode Connect:
- CSVReader:
    file: multirow_header_special_values.csv
    name: p
    header: [1,2]
    indexColumns: [1]
    readCSVArguments: {"keep_default_na": False, "na_values": [""]}
- PythonCode:
    code: |
        import pandas as pd
        sym = connect.container["p"]
        sym.records.sort_values(sym.records.columns[:-1].tolist(), inplace=True)
        data = pd.DataFrame(sym.records.values.tolist())
        expected = pd.DataFrame([('Region1', 'Region4', 'Sector2', float("-inf")),
                                 ('Region1', 'Region2', 'Sector4', float("nan")),
                                 ('Region1', 'Region1', 'Sector5', 0.01),
                                 ('Region3', 'Region3', 'Sector3', 0.0456),
                                 ('Region4', 'Region5', 'Sector1', 0.34),
                                 ('Region4', 'Region3', 'Sector3', -0.0),
                                 ('Region4', 'Region2', 'Sector4', 0.0789),
                                 ('Region5', 'Region4', 'Sector2', float("nan")),
                                 ('Region5', 'Region1', 'Sector5', 0.003212),
                                 ('Region6', 'Region4', 'Sector2', 0.00452),
                                 ('Region6', 'Region3', 'Sector3', 0.521),
                                 ('Region6', 'Region2', 'Sector4', 0.221),
                                 ('Region6', 'Region1', 'Sector5', 0.6754),
                                 ('Region7', 'Region4', 'Sector2', 0.0)])
        if not data.equals(expected):
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log test multi-row header (2x2)
$onEmbeddedCode Connect:
- CSVReader:
    file: multirow_header_2x2.csv
    name: p
    header: [1,2]
    indexColumns: [1,2]
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['Region1', 'Sector2', 'Region1', 'Sector2', 0.0123], 
                    ['Region1', 'Sector3', 'Region1', 'Sector3', 0.0456], 
                    ['Region1', 'Sector4', 'Region1', 'Sector4', 0.0789], 
                    ['Region1', 'Sector5', 'Region1', 'Sector5', 0.003212], 
                    ['Region1', 'Sector6', 'Region1', 'Sector2', 0.00452], 
                    ['Region1', 'Sector6', 'Region1', 'Sector3', 0.521], 
                    ['Region1', 'Sector6', 'Region1', 'Sector4', 0.221], 
                    ['Region1', 'Sector6', 'Region1', 'Sector5', 0.6754], 
                    ['Region1', 'Sector7', 'Region1', 'Sector1', 5.0]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log test multi-row header (1x2) without index or column names
$onEmbeddedCode Connect:
- CSVReader:
    file: multirow_header_no_index_column_names.csv
    name: p
    header: [1,2]
    indexColumns: [1]
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['Region1', 'Region1', 'Sector3', 0.5000],
                    ['Region2', 'Region1', 'Sector2', 0.0123], 
                    ['Region3', 'Region1', 'Sector3', 0.0456], 
                    ['Region4', 'Region1', 'Sector4', 0.0789], 
                    ['Region5', 'Region1', 'Sector5', 0.003212], 
                    ['Region6', 'Region1', 'Sector2', 0.00452], 
                    ['Region6', 'Region1', 'Sector3', 0.521], 
                    ['Region6', 'Region1', 'Sector4', 0.221], 
                    ['Region6', 'Region1', 'Sector5', 0.6754], 
                    ['Region7', 'Region1', 'Sector1', 5.0]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log test multi-row header (2x2) with index and value substitutions
$onEmbeddedCode Connect:
- CSVReader:
    file: multirow_header_2x2_sub.csv
    name: p
    header: [1,2]
    indexColumns: [1]
    fieldSeparator: ;
    indexSubstitutions: {j1: JX, .nan: i1}
    valueSubstitutions: {.nan: Eps, 100: .nan}
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['i1', 'JX', 'k1', 1.0],
                    ['i1', 'JX', 'k2', 2.0],
                    ['i1', 'JX', 'k3', -0.0],
                    ['i1', 'j2', 'k1', 4.0],
                    ['i1', 'j2', 'k2', 5.0],
                    ['i1', 'j2', 'k3', 6.0],
                    ['i2', 'JX', 'k1', -0.0],
                    ['i2', 'JX', 'k3', 3.0],
                    ['i2', 'j2', 'k1', 4.0],
                    ['i2', 'j2', 'k2', 5.0],
                    ['i2', 'j2', 'k3', -0.0]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log test multi-row header (0x2)
$onEmbeddedCode Connect:
- CSVReader:
    file: multirow_header_0x2.csv
    name: p
    header: [1,2]
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['j1', 'k1', 1.0],
                    ['j1', 'k2', 2.0],
                    ['j2', 'k1', 4.0],
                    ['j2', 'k2', 5.0],
                    ['j2', 'k3', 6.0],
                    ['j1', 'k3', 3.0],
                    ['j2', 'k1', 4.0],
                    ['j2', 'k2', 5.0]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log test distributed multi-row header (1x2)
$onEmbeddedCode Connect:
- CSVReader:
    file: distributed_multirow_header_1x2.csv
    name: p
    header: [1,3]
    indexColumns: 1
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['i2', 'j1', 'k3', 3.0],
                    ['i2', 'j2', 'k1', 4.0],
                    ['i2', 'j2', 'k2', 5.0],
                    ['i3', 'j1', 'k1', 1.0],
                    ['i3', 'j2', 'k1', 4.0],
                    ['i3', 'j2', 'k3', 6.0]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$onEmbeddedCode Connect:
- CSVReader:
    file: multirow_header_2x2_distributed_index_columns.csv
    name: p
    header: [1,2]
    indexColumns: "1,4"
- PythonCode:
    code: |
        data = connect.container.data["p"].records.values.tolist()
        expected = [['h1', 'i1', 'j1', 'k1', 1.0],
                    ['h1', 'i1', 'j1', 'k2', 2.0],
                    ['h1', 'i1', 'j2', 'k1', 3.0],
                    ['h1', 'i1', 'j2', 'k2', 4.0],
                    ['h1', 'i2', 'j1', 'k1', 5.0],
                    ['h1', 'i2', 'j1', 'k2', 6.0],
                    ['h1', 'i2', 'j2', 'k1', 7.0],
                    ['h1', 'i2', 'j2', 'k2', 8.0]]
        if data != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log test all value columns in index columns (set)
$onEmbeddedCode Connect:
- CSVReader:
    file: abc_data.csv
    name: s
    header: True
    type: set
    indexColumns: ["II","VI","I"]
    valueColumns: ["II","VI"]
- PythonCode:
    code: |
        sym = connect.container.data["s"]
        data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
        expected = [['b', 'h', 'a', 'II', 'b'],
                    ['b', 'h', 'a', 'VI', 'h'],
                    ['c', 'i', 'b', 'II', 'c'],
                    ['c', 'i', 'b', 'VI', 'i'],
                    ['d', 'j', 'c', 'II', 'd'],
                    ['d', 'j', 'c', 'VI', 'j']]
        if data_sorted != expected:
            raise Exception("Unexpected data s.")
$offEmbeddedCode

$log test all value columns in index columns (parameter)
$onEmbeddedCode Connect:
- CSVReader:
    file: abc_data.csv
    name: p
    header: True
    indexColumns: ["V","III","II"]
    valueColumns: ["V","III"]
- PythonCode:
    code: |
        sym = connect.container.data["p"]
        data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
        expected = [['3', '1', 'b', 'V', 3.0],
                    ['3', '1', 'b', 'III', 1.0],
                    ['4', '2', 'c', 'V', 4.0],
                    ['4', '2', 'c', 'III', 2.0],
                    ['5', '3', 'd', 'V', 5.0],
                    ['5', '3', 'd', 'III', 3.0]]
        if data_sorted != expected:
            raise Exception("Unexpected data p.")
$offEmbeddedCode

$log test order of value columns as specified and column both in index and value
$onEmbeddedCode Connect:
- CSVReader:
    file: abc_data.csv
    name: p
    header: True
    indexColumns: [4,2,3]
    valueColumns: [5,3,7]
- PythonCode:
    code: |
        sym = connect.container.data["p"]
        data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
        expected = [['e', 'b', '1', 'V', 3.0],
                    ['e', 'b', '1', 'III', 1.0],
                    ['e', 'b', '1', 'VII', 6.0],
                    ['f', 'c', '2', 'V', 4.0],
                    ['f', 'c', '2', 'III', 2.0],
                    ['f', 'c', '2', 'VII', 7.0],
                    ['g', 'd', '3', 'V', 5.0],
                    ['g', 'd', '3', 'III', 3.0],
                    ['g', 'd', '3', 'VII', 8.0]]
        if data_sorted != expected:
            raise Exception("Unexpected sorted data p.")
$offEmbeddedCode

$log test order of value columns as specified and column both in index and value (set)
$onEmbeddedCode Connect:
- CSVReader:
    file: abc_data.csv
    name: s
    type: set
    header: True
    indexColumns: ["IV","II","III"]
    valueColumns: ["V","III","VII"]
- PythonCode:
    code: |
        sym = connect.container.data["s"]
        data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
        expected = [['e', 'b', '1', 'V', '3'],
                    ['e', 'b', '1', 'III', '1'],
                    ['e', 'b', '1', 'VII', '6'],
                    ['f', 'c', '2', 'V', '4'],
                    ['f', 'c', '2', 'III', '2'],
                    ['f', 'c', '2', 'VII', '7'],
                    ['g', 'd', '3', 'V', '5'],
                    ['g', 'd', '3', 'III', '3'],
                    ['g', 'd', '3', 'VII', '8']]
        if data_sorted != expected:
            raise Exception("Unexpected sorted data s.")
$offEmbeddedCode

$log test order of value columns as specified and column both in index and value (with names)
$onEmbeddedCode Connect:
- CSVReader:
    file: abc_data.csv
    name: p
    header: True
    names: ["TEST1","TEST2","TEST3","TEST4","TEST5","TEST6","TEST7"]
    indexColumns: [4,2,3]
    valueColumns: [5,3,7]
- PythonCode:
    code: |
        sym = connect.container.data["p"]
        data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
        expected = [['e', 'b', '1', 'TEST5', 3.0],
                    ['e', 'b', '1', 'TEST3', 1.0],
                    ['e', 'b', '1', 'TEST7', 6.0],
                    ['f', 'c', '2', 'TEST5', 4.0],
                    ['f', 'c', '2', 'TEST3', 2.0],
                    ['f', 'c', '2', 'TEST7', 7.0],
                    ['g', 'd', '3', 'TEST5', 5.0],
                    ['g', 'd', '3', 'TEST3', 3.0],
                    ['g', 'd', '3', 'TEST7', 8.0]]
        if data_sorted != expected:
            raise Exception("Unexpected sorted data p.")
$offEmbeddedCode

$log test order of value columns as specified and column both in index and value (with autoCol)
$onEmbeddedCode Connect:
- CSVReader:
    file: abc_data.csv
    name: p
    header: True
    autoColumn: "col"
    indexColumns: [4,2,3]
    valueColumns: [5,3,7]
- PythonCode:
    code: |
        sym = connect.container.data["p"]
        data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
        expected = [['e', 'b', '1', 'col1', 3.0],
                    ['e', 'b', '1', 'col2', 1.0],
                    ['e', 'b', '1', 'col3', 6.0],
                    ['f', 'c', '2', 'col1', 4.0],
                    ['f', 'c', '2', 'col2', 2.0],
                    ['f', 'c', '2', 'col3', 7.0],
                    ['g', 'd', '3', 'col1', 5.0],
                    ['g', 'd', '3', 'col2', 3.0],
                    ['g', 'd', '3', 'col3', 8.0]]
        if data_sorted != expected:
            raise Exception("Unexpected sorted data p.")
$offEmbeddedCode