$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 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:False$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 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)])ifnot 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)])ifnot 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