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