ExcelWriter valueSubstitutions question

GAMS
1

I’m running a large model with CPLEX using parallelmode -1, so it isn’t deterministic, and most “0” values are not zero and have very small values.

Just for output reading clarity, I was hoping that I would be able to use valueSubstitutions to replace values <0.000001 with zero: valueSubstitutions: {<.000001: 0}

Is it possible to do this?

2

Hi,

The valueSubstitutions option of the ExcelWriter does not work with expressions like <0.00001. It will be considered as a string when the YAML syntax gets parsed. There is the Filter agent that allows actual filtering of records. The following example should give you an idea on how to use it. Also you might want to check the documentation: https://www.gams.com/latest/docs/UG_GAMSCONNECT.html#UG_GAMSCONNECT_FILTER

set i /i1*i10/;
parameter p(i);
p(i) = uniform(0, 0.1);

embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p
- Filter:
    name: p
    newName: p_new
    valueFilters:
      - column: value
        rule: x<0.05
- ExcelWriter:
    file: out.xlsx
    symbols:
      - name: p_new
        range: p!A1
endEmbeddedCode

Hope that helps,
Clemens

3

Hi Clemens,

Thanks. If I use the filter and let’s say for a given row of output, if one column meets the criteria to be filtered but the other columns in that row do not meet the criteria, is the whole row filtered out? Or is the one value in the relevant column blank?

Eric

4

Eric,

All records that do not meet the given expression are removed, also for variables and equations where you might filter on the level only. See the documentation: https://www.gams.com/latest/docs/UG_GAMSCONNECT.html#UG_GAMSCONNECT_FILTER_RULE
This is not exactly what you tried to achieve in the first place, since you wanted to turn small values into zeroes and not filter them out. But perhaps it helps anyways. Alternatively you could consider to turn the small values into 0 directly in GAMS before you call Connect.

Edit: When you use the same workbook and the same sheet for writing, you probably want to specify “clearSheet: True” for the ExcelWriter since the shape of your data might be different for each solve when you use the Filter agent since it will remove records depending on the given condition and the given data. Otherwise you might see old values where things do not get overwritten.

Best,
Clemens

5

Thanks. I think I’ll try replacing small values with 0 in GAMS prior to calling ExcelWriter.