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
$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 > 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 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 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 with textColumns
$onEmbeddedCode Connect:
- CSVReader:
file: multirow_header_2x2.csv
name: p
header: [1,2]
indexColumns: [1,2]
textColumns: "3:lastCol"
$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
* ### 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 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
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
indexColumns: [2,1]
textColumns: "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 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 = 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
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 textSubstitutions
$onEmbeddedCode Connect:
- CSVReader:
file: ijkSet.csv
name: s
textColumns: "3:lastCol"
textSubstitutions: {"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 textSubstitutions
$onEmbeddedCode Connect:
- CSVReader:
file: ijkSet.csv
name: s
indexColumns: "1,2"
textColumns: "3:lastCol"
textSubstitutions: {"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
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,,,
red,red,green,4.4,5.5,-0.0
red,'green',blue,770.0,123.0,-inf
blue,blue,purple,10.0,0.0,
brown,blue,green,1.0,0.0,
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
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 read index and text columns as string
$onEmbeddedCode Connect:
- CSVReader:
file: leading_zeros.csv
name: p
indexColumns: "1,2"
valueColumns: "lastCol"
- CSVReader:
file: leading_zeros.csv
name: s
indexColumns: "1"
textColumns: "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 text columns in index columns (set)
$onEmbeddedCode Connect:
- CSVReader:
file: abc_data.csv
name: s
header: True
indexColumns: ["II","VI","I"]
textColumns: ["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 text 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 text columns as specified and column both in index and text
$onEmbeddedCode Connect:
- CSVReader:
file: abc_data.csv
name: s
header: True
indexColumns: ["IV","II","III"]
textColumns: ["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