Friday, May 29, 2009

GAMS: Skipping columns in reading a spreadsheet

I have a spreadsheet like:

dropcol

The real spreadsheet has many more rows. How do I read this into a two-dimensional GAMS parameter (dropping columns Comment, Org and Type).

There are several possibilities:

1. If you can edit the spreadsheet, just remove columns B,C,D and read data using GDXXRW

2. If you can remove the A1 cell, we can read the data as a table using XLS2GMS. XLS2GMS allows multiple-area ranges:

$call xls2gms i=test2.xls o=out.inc r=a1:a5,e1:q5

table a(*,*)
$include out.inc
;
display a;

The comma in the range is the list separator character in Excel (it may be different for different language settings). The include file will look like:

* -----------------------------------------------------
* XLS2GMS 2.8      Aug  1, 2008 22.8.1 WIN 5778.6015 VIS x86/MS Windows
* Erwin Kalvelagen, GAMS Development Corp.
* -----------------------------------------------------
* Application: Microsoft Excel
* Version:     12.0
* Workbook:    C:\projects\test\test2.xls
* Sheet:       Sheet1
* Range:       $A$1:$A$5,$E$1:$Q$5
* -----------------------------------------------------
          2006A 2007A 2008A 2009A 2010A 2011A 2012A 2013A 2014A 2015A 2016A 2017A 2018A
OAF_PT_NT -852  -831  -940  -977  -937  -869  -952  -887  -960  -945  -822  -811  -913
OAF_PT_QC 3855  3293  3418  3164  3354  3431  3690  3233  3463  3248  3679  3516  3260
OAF_PT_QP 2864  2890  2674  2891  2486  2683  2789  2873  2395  2902  2148  2119  2407
OAF_PT_ST 3     3     3     4     4     3     5     3     4     4     4     4     3
* -----------------------------------------------------

3. If you cannot change the spreadsheet at all you can ask GAMS to delete these columns. E.g. by:

$call gdxxrw i=test.xls par=a rdim=4 cdim=1 rng=a1:q5 trace=2

parameter a(*,*,*,*,*);
$gdxin test
$load a

parameter b(*,*);
set i(*);
set j(*);

alias(*,s1,s2,s3,s4,s5);

loop((s1,s2,s3,s4,s5)$a(s1,s2,s3,s4,s5),
  i(s1) = yes;
  j(s5) = yes;
  b(s1,s5) = a(s1,s2,s3,s4,s5);
);

execute_unload 'test2.gdx',i,j,b;

I.e. we read in as 4 dimensional parameter A, and then convert to 2 dimensional parameter B. The loop is optimized by using a $ condition. The following will be stored in the new GDX file:

----     25 SET i 

OAF_PT_NT,    OAF_PT_QC,    OAF_PT_QP,    OAF_PT_ST

----     25 SET j 

2006A,    2007A,    2008A,    2009A,    2010A,    2011A,    2012A,    2013A,    2014A,    2015A,    2016A,    2017A
2018A

----     25 PARAMETER b 

                2006A       2007A       2008A       2009A       2010A       2011A       2012A       2013A       2014A

OAF_PT_NT    -838.000    -838.000    -976.000    -829.000    -971.000    -996.000    -841.000    -813.000    -849.000
OAF_PT_QC    3987.000    3980.000    3081.000    3643.000    3270.000    3739.000    3512.000    3780.000    3228.000
OAF_PT_QP    2542.000    2988.000    2824.000    2503.000    2747.000    2997.000    2611.000    2348.000    2767.000
OAF_PT_ST       3.000       3.000       5.000       5.000       3.000       3.000       5.000       4.000       3.000

Adding the sets i and j will make it easier to read b with proper domains:

set i,j;
parameter b(i,j);

$gdxin test2
$load i j
$loaddc b

display i,j,b;

This (real-world) example would benefit from having some multiple-area ranges facility in GDXXRW.