csv2gdx creates <empty> rows when trying to read in set

Problems with syntax of GAMS
Post Reply
pecenak21
User
User
Posts: 24
Joined: 5 years ago

csv2gdx creates <empty> rows when trying to read in set

Post by pecenak21 »

Hello team,

We are trying to create a csv file from which we can read in all the sets in our model, instead of declaring them inline.

The csvfile looks like this in excel :
image.png

and like this in a text editor
image.png
The command to read this in is as follows:

Code: Select all

$call csv2gdx ./setDeclarations.csv output=dummy1.gdx id=text1 index=1,2,3,4,5 colCount=5 useheader=yes


The issue is when trying to load the sets, the .gdx file has the element <empty> appended to the first empty row in each column, see below:
image.png
Any idea how we can eliminate the <empty>, since thos obviously should not be a part of the set
User avatar
Renger
Posts: 639
Joined: 7 years ago

Re: csv2gdx creates <empty> rows when trying to read in set

Post by Renger »

Hi
It is not clear what you want to do: text1 seems to be defined over all sets, but then the csv file should have all combinations of the five sets and if it is a parameter values for those combinations.
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist
pecenak21
User
User
Posts: 24
Joined: 5 years ago

Re: csv2gdx creates <empty> rows when trying to read in set

Post by pecenak21 »

Thanks for the response Renger. Let me clarify.

What we are trying to do is reduce the number of sets defined directly in the code using this style:

Code: Select all

sets
   SetName1 'here is an example set'
      / setName1_el1, setName1_el2, setName1_el3, setName1_el4/
   SetName2 'here is another example set'
      / setName2_el1, setName2_el2, setName2_el3, setName2_el4/
Instead, we want to read all sets in dynamically from a single .csv file. This is where we convert the .csv using csv2gdx, to a .gdx containing all of the sets. Then we would unload the sets as follows:

Code: Select all

$call csv2gdx ./setDeclarations.csv output=dummy1.gdx id=text1 index=1,2,3,4,5 colCount=5 useheader=yes

$gdxin ".\dummy1.gdx"
$load SetName1 <=text1.Dim2
$load SetName2 <=text1.Dim3
etc...
You are correct, id=text1 is not needed, since we do not plan to use that id. Again, our main concern is the placement of <empty> in the .gdx file, which get read in a set elements when declaring them.
User avatar
Renger
Posts: 639
Joined: 7 years ago

Re: csv2gdx creates <empty> rows when trying to read in set

Post by Renger »

Hi
OK, I tried to get the same problem, but when I run my code, there are <empty> in my gdx file, but there is no problem when reading the sets:

Code: Select all

set set1, set2;
$call csv2gdx sets.csv output=dummy1.gdx  id=text1 index=1,2 colCount=2 useheader=yes
$gdxin "dummy1.gdx"
$load Set1 <=text1.Dim1
$load Set2 <=text1.Dim2

display set1, set2;
where sets.csv is:

Code: Select all

set_1,set_2
set_1_1,set_2_1
set_1_2,set_2_2
,set_1_3 
,set_1_4 
,set_1_5 
My code gives me this as result, which is probably what you want:

Code: Select all

----      7 SET set1  Domain loaded from text1 position 1

set_1_1,    set_1_2,           


----      7 SET set2  Domain loaded from text1 position 2

set_2_1,    set_2_2,    set_1_3,    set_1_4,    set_1_5
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist
pecenak21
User
User
Posts: 24
Joined: 5 years ago

Re: csv2gdx creates <empty> rows when trying to read in set

Post by pecenak21 »

That is strange. When I use my data and code, I get what is shown below in a display call:

Code: Select all

----     13 SET opexstreams  Domain loaded from text1 position 2

             ,    setName2_el1 ,    setName2_el2 ,    setName2_el3 
setName2_el4 ,    setName2_el5 ,    setName2_el6 ,    setName2_el7 
setName2_el8 ,    setName2_el9 ,    setName2_el10,    setName2_el11


----     13 SET valuestream  Domain loaded from text1 position 1

setName1_el1,    setName1_el2,    setName1_el3,    setName1_el4,    setName1_el5
and this is how it looks in the execute_unload output .gdx
image.png
Here is my whole code, you can copy+paste to your environment:

Code: Select all

$call csv2gdx ./setDeclarations.csv output=dummy1.gdx id=text1 index=1,2,3,4,5 colCount=5 useheader=yes

sets
   valuestream
   opexstreams
;

$gdxin "dummy1.gdx"
$load valuestream<=text1.dim1
$load opexstreams<=text1.dim2

display opexstreams, valuestream

execute_unload "test.gdx"
Here is my .csv file:

Code: Select all

setName1,setName2,setName3,setName4,setName5
setName1_el1,setName2_el1,setName3_el1,serName4_el1,setName5_el1
setName1_el2,setName2_el2,setName3_el2,serName4_el2,setName5_el2
setName1_el3,setName2_el3,setName3_el3,serName4_el3,setName5_el3
setName1_el4,setName2_el4,setName3_el4,serName4_el4,setName5_el4
setName1_el5,setName2_el5,setName3_el5,serName4_el5,setName5_el5
,setName2_el6,setName3_el6,serName4_el6,setName5_el6
,setName2_el7,setName3_el7,serName4_el7,setName5_el7
,setName2_el8,,serName4_el8,setName5_el8
,setName2_el9,,serName4_el9,setName5_el9
,setName2_el10,,serName4_el10,setName5_el10
,setName2_el11,,serName4_el11,setName5_el11
,,,serName4_el12,setName5_el12
,,,serName4_el13,setName5_el13
,,,serName4_el14,setName5_el14
,,,serName4_el15,setName5_el15
,,,serName4_el16,setName5_el16
,,,,setName5_el17
,,,,setName5_el18
Here is the version we are using, in case this makes a difference:
image.png
image.png (8.8 KiB) Viewed 4542 times
User avatar
Renger
Posts: 639
Joined: 7 years ago

Re: csv2gdx creates <empty> rows when trying to read in set

Post by Renger »

Hi
You are correct. My example has the same problem: set1 has a comma and then an empty as set element.
Here is what happens when I assign values:

Code: Select all

----     12 PARAMETER test  

set_1_1 0.172,    set_1_2 0.843,            0.550
I think this is not possible as long as csv2gdx is not corrected for this problem.

Sorry
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist
Fred
Posts: 372
Joined: 7 years ago

Re: csv2gdx creates <empty> rows when trying to read in set

Post by Fred »

Hi,

You read text1 as a 5-dimensional set. Hence, given the structure of the data in the csv file, it is expected that the domain sets Dim1,...,Dim5 contain the empty element.
If you need to read the sets separately, the only way to do this with csv2gdx is to put them in different csv file.

You could also try to remove the empty set element from the one dimensional set. Below is an example doing that using gdxdump.

Code: Select all

$onecho > setDeclarations.csv
setName1,setName2,setName3,setName4,setName5
setName1_el1,setName2_el1,setName3_el1,serName4_el1,setName5_el1
setName1_el2,setName2_el2,setName3_el2,serName4_el2,setName5_el2
setName1_el3,setName2_el3,setName3_el3,serName4_el3,setName5_el3
setName1_el4,setName2_el4,setName3_el4,serName4_el4,setName5_el4
setName1_el5,setName2_el5,setName3_el5,serName4_el5,setName5_el5
,setName2_el6,setName3_el6,serName4_el6,setName5_el6
,setName2_el7,setName3_el7,serName4_el7,setName5_el7
,setName2_el8,,serName4_el8,setName5_el8
,setName2_el9,,serName4_el9,setName5_el9
,setName2_el10,,serName4_el10,setName5_el10
,setName2_el11,,serName4_el11,setName5_el11
,,,serName4_el12,setName5_el12
,,,serName4_el13,setName5_el13
,,,serName4_el14,setName5_el14
,,,serName4_el15,setName5_el15
,,,serName4_el16,setName5_el16
,,,,setName5_el17
,,,,setName5_el18
$offecho


$oncheckerrorlevel
$call csv2gdx ./setDeclarations.csv output=dummy1.gdx id=text1 index=1,2,3,4,5 colCount=5 useheader=yes

$call gdxdump dummy1.gdx > removeEmptyElements.gms
$call gdxdump dummy1.gdx symbolsAsSet > x.gms
$onecho >> x.gms
file f / removeEmptyElements.gms /; f.ap=1;
loop(gdxitems(Symbol,'1',Type),
  put f Symbol.tl:0:0 '("") = no;' /;
);
putclose f;
$offecho
$call gams x
$call gams removeEmptyElements.gms gdx dummy2.gdx

sets
   valuestream
   opexstreams
;

$gdxin "dummy2.gdx"
$load valuestream=dim1
$load opexstreams=dim2

display opexstreams, valuestream

execute_unload "test.gdx"
Not very elegant but it works. You could probably achieve the same using embedded Python.

I hope this helps!

Fred
Post Reply