Description

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

Contributor: Aileen Böhme, March 2022

Small Model of Type : GAMS

Category : GAMS Test library

Main file : cacsvr.gms

$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


* 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%

$onecho > scalar.csv
100
$offEcho

$onecho > iPar.csv
i3,i2,i1
0.457,0.323,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 > ijkSet.csv
,,j1,j2,j3
i1,k1,Y,Y,Y
i2,k1,Y,Y,N
i3,k2,N,Y,Y
$offecho

$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 specifying valueColumns and textColumns
$onEmbeddedCode Connect:
- CSVReader:
    file: iPar.csv
    name: p
    valueColumns: "1,2,3"
    textColumns: "1,2,3"
$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
    textColumns: "1,2,3"
    header: False
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors
$log test DataFrame not valid
$onEmbeddedCode Connect:
- CSVReader:
    file: iPar.csv
    name: p
    valueColumns: "1:lastCol"
    indexSubstitutions: {"i2": "i1"}
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$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 = [(*r.keys,r.value) for r in connect.db["p"]]
        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 = [(*r.keys,r.value) for r in connect.db["p"]]
        expected = [(100.0,)]
        if data != expected:
            raise Exception("Unexpected data p.")
$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 = [(*r.keys,r.value) for r in connect.db["p"]]
        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 = [(*r.keys,r.value) for r in connect.db["p"]]
        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 = [(*r.keys,r.value) for r in connect.db["p"]]
        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 = [(*r.keys,r.value) for r in connect.db["p"]]
        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:lastCol"]
        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', 's1', '2', 150.0), ('p1', 's2', '1', 65.0)],
        [('p1', 's1', '1', 'minCap', 50.0), ('p1', 's1', '1', 'maxCap', 100.0),
         ('p1', 's1', '2', 'minCap', 100.0), ('p1', 's1', '2', 'maxCap', 150.0),
         ('p1', 's2', '1', 'minCap', 35.0), ('p1', 's2', '1', 'maxCap', 65.0)],
        [('p1', 's1', '1', 'minCap', 50.0), ('p1', 's1', '1', 'maxCap', 100.0),
         ('p1', 's1', '1', 'length', 100.0), ('p1', 's1', '1', 'cost', 1200.0),
         ('p1', 's1', '2', 'minCap', 100.0), ('p1', 's1', '2', 'maxCap', 150.0),
         ('p1', 's1', '2', 'length', 100.0), ('p1', 's1', '2', 'cost', 1800.0),
         ('p1', 's2', '1', 'minCap', 35.0), ('p1', 's2', '1', 'maxCap', 65.0),
         ('p1', 's2', '1', 'length', 75.0), ('p1', 's2', '1', 'cost', 500.0)]
        ]
        for i in range(3):
            data = [(*r.keys,r.value) for r in connect.db[f"p{i}"]]
            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
    indexColumns: 1
- PythonCode:
    code: |
        data = [(*r.keys,r.text) for r in connect.db["s"]]
        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
    indexColumns: [2,1]
    textColumns: "3"
    header: False
- PythonCode:
    code: |
        data = [(*r.keys,r.text) for r in connect.db["s"]]
        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 using textSubstitutions and indexSubstitutions
$onEmbeddedCode Connect:
- CSVReader:
    file: ijSet.csv
    name: s
    indexColumns: [1,2]
    indexSubstitutions: {.nan: i2}
    textColumns: "3"
    textSubstitutions: {.nan: ""}
    header: False
- PythonCode:
    code: |
        data = [(*r.keys,r.text) for r in connect.db["s"]]
        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
    indexColumns: "1,2"
    header: False
- PythonCode:
    code: |
        data = [(*r.keys,r.text) for r in connect.db["s"]]
        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 textSubstitutions
$onEmbeddedCode Connect:
- CSVReader:
    file: ijkSet.csv
    name: s
    textColumns: "3:lastCol"
    textSubstitutions: {"N": .nan}
    header: True
    skipRows: [2,4]
- PythonCode:
    code: |
        data = [(*r.keys,r.text) for r in connect.db["s"]]
        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 textSubstitutions
$onEmbeddedCode Connect:
- CSVReader:
    file: ijkSet.csv
    name: s
    indexColumns: "1,2"
    textColumns: "3:lastCol"
    textSubstitutions: {"N": .nan}
    header: True
- PythonCode:
    code: |
        data = [(*r.keys,r.text) for r in connect.db["s"]]
        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
    indexColumns: ["plant","station","stage"]
    fieldSeparator: ";"
- PythonCode:
    code: |
        data = [(*r.keys,r.text) for r in connect.db["s"]]
        expected = [('p1', 's1', '1', ''),
                    ('p1', 's1', '2', ''),
                    ('p1', 's2', '1', ''),
                    ('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([(*r.keys,r.value) for r in connect.db["p"]])
        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.")
$offEmbeddedCode

$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 = [(*r.keys,r.value) for r in connect.db["p"]]
        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 = [(*r.keys,r.value) for r in connect.db["p"]]
        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 = [(*r.keys,r.value) for r in connect.db["p"]]
        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
$onEmbeddedCode Connect:
- CSVReader:
    file: distance2.csv
    name: p
    indexColumns: "1,2"
    valueColumns: "3"
    header: True
    stack: True
    fieldSeparator: ";"
    decimalSeparator: ","
- PythonCode:
    code: |
        data = [(*r.keys,r.value) for r in connect.db["p"]]
        expected = [('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:
            raise Exception("Unexpected data 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 = [(*r.keys,r.value) for r in connect.db["p"]]
        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 = [(*r.keys,r.value) for r in connect.db["p"]]
        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 = [(*r.keys,r.value) for r in connect.db["p"]]
        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 = [(*r.keys,r.value) for r in connect.db["p"]]
        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 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 [r.key(0) for r in connect.db['p']] != ['abc', 'ä', 'ü']:
            raise Exception("Unexpected data p.")
''')
$offEmbeddedCode

$call.checkErrorLevel gamsconnect cp1252_instructions.yaml