Looping over file names

Archive of Gamsworld Google Group
Archiver
User
User
Posts: 7876
Joined: 1 year ago

Re: Looping over file names

Post by Archiver » 2 years ago


I'm trying to explain my problem again:

I want to load parametres with a dimension like 3 or 5 (e.g. costs(p,c,a,i,j) ) from excel.
I will reduce the parametres by several loops (p, c, a) and optimize an objective variable z in a 2 dimension level for several p, c, a. (e.g. costs2(i,j))
I want to have these results of the 2 dimension optimization for each run as an output in excel.

Then I want to use the results in the outter loops p, c to find the minimum objective variable there of the 2 dimension results for each a, then for each c and then for each p.

So first I have the objective variable z(p,c,a) then z(p,c) then z(p) and then z.


Am Mittwoch, 6. Mai 2015 18:23:06 UTC+2 schrieb Alexander Strenge:

I guess you misunderstood me.
I know that the model is running fine. That was not the issue.

The issue is that my model is not solving if I use the %instance% approach.
Could you maybe send me the adjusted file back?
"I adjusted the file bringing back “instance” and it also solves fine."

Alex


Am Mittwoch, 6. Mai 2015 18:13:00 UTC+2 schrieb Renger van Nieuwkoop:

Hi Alex



Runs fine on my computer. Here the output in my dos window.

What might happen is that you have the excel file open (see in the gdxutils.pdf for closing it using XLStalk.exe).

I deleted the results in the excel file and when I run the model, the sheet is filled with data. I adjusted the file bringing back “instance” and it also solves fine.



Iteration: 1 Dual objective = 2000.035000

LP status(1): optimal

Cplex Time: 0.02sec (det. 0.51 ticks)



Optimal solution found.

Objective : 7200.160000



--- Restarting execution

--- Reading solution for model LCC_MODEL

--- GDX Point d:\Inbox\alex\LCC_MODEL_p.gdx



GDXXRW 24.4.1 r50296 Released Dec 20, 2014 VS8 x86 32bit/MS Windows

Input file : d:\Inbox\alex\GAMSOutput.gdx

Output file: d:\Inbox\alex\RunTest_2D_1.XLS

Substituting Index

Total time = 3171 Ms

*** Status: Normal completion

--- Job Model_05.gms Stop 05/06/15 17:58:46 elapsed 0:00:05.608



If you add trace = 3 to the gdx-line I get a more usable output for debugging.



GDXXRW 24.4.1 r50296 Released Dec 20, 2014 VS8 x86 32bit/MS Windows

Excel version 15.0

Input file : d:\Inbox\alex\GAMSOutput.gdx

Output file: d:\Inbox\alex\RunTest_2D_1.XLS

Substituting Index

Loading sheet GAMSOutput

Reading data from Excel: Width = 13 Height = 22

var=z.l Rng=GAMSResults!A4:B5

var=f.l Rng=GAMSResults!A6:BN100 rdim=1 cdim=1

Squeeze value : 0.00000000000000E+0000

Squeeze value : 0.00000000000000E+0000

Type Symbol Dim Sheet Data RowHeader ColHeader

Var z 0 GAMSResults A4:A4 --:-- --:--

Clear range: A4:A4

Write range: A4:A4 CellCount = 1

Var f 2 GAMSResults B7:BN100 A7:A100 B6:BN6

Clear range: A6:BN100

Write range: A7:A100 CellCount = 94

Write range: B6:BN6 CellCount = 65

Write range: B7:N19 CellCount = 169

Total time = 3234 Ms

*** Status: Normal completion





Hope this helps



Renger







From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Alexander Strenge
Sent: Mittwoch, 6. Mai 2015 17:57
To: gams...@googlegroups.com
Subject: Re: Looping over file names



Not my day... Sorry! And thank you again for your help.

Alexander



Am Mittwoch, 6. Mai 2015 17:49:42 UTC+2 schrieb Renger van Nieuwkoop:

There is only one file: the model_05.gms file

Renger



From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Alexander Strenge
Sent: Mittwoch, 6. Mai 2015 17:48
To: gams...@googlegroups.com
Subject: Re: Looping over file names



You just need the "Model_05.gms" and "RunTest_2D_1.XLS" in one folder and it should work with reading and also writing to excel

Am Mittwoch, 6. Mai 2015 16:31:18 UTC+2 schrieb Renger van Nieuwkoop:

The gdx file is missing

Cheers

Renger



Von: gams...@googlegroups.com [mailto:gams...@googlegroups.com] Im Auftrag von Alexander Strenge
Gesendet: Wednesday, May 6, 2015 4:28 PM
An: gams...@googlegroups.com
Betreff: Re: Looping over file names



Hi Renger,

thank you.

I know that the input gdx file is created but the model will not be solved and the (output) excel file is not changed. Do I have to type "execute" instead of "call"?

I sent you a working gams and excel file for one instance without the %instance% and run.gms file.


Alex



Am Dienstag, 24. Februar 2009 21:23:41 UTC+1 schrieb Alison:

Hi,

I've written a GAMS program using the xls2gms function to upload data
and the execute_unload function to save the final result. However, I
need to run the same GAMS code for 50 different countries, which means
I have 50 different xls input files.

Is there a way to loop over file names so that I can change the input
file and the output file each time (e.g. Input_file_CountryX.xls,
Results_CountryX.gdx) ?

A simplified version of my code is below.

Thanks for your help-
Alison

-----------------------
*Read data from external spreadsheet
$call =xls2gms i=Input_file_Country1.xls r1=Sets!set_i
o1=industries.set r2=Sets!set_o o2=occupations.set r3=Wages!matrixB
o3=matrixB.inc r4=Sets!MatrixA o4=Average.inc

set i /
$include industries.set
/;
set o /
$include occupations.set
/;
**************
*Define B from external spreadsheet
Table B(o,i) degree of belief
$include matrixB.inc
;
Parameter avg(i) average by industry/
$include Average.inc
/;
**************

Equations, Objective Function, etc.

**************
*Run the model to get results
Model WAGES /objective/;
Solve WAGES using dnlp minimizing find_min;
Display w.l, w.m;
**************
*Export results to Excel by creating a .gdx file
execute_unload "Results_Country1.gdx" w.L w.M
execute 'gdxxrw.exe Results_Country1.gdx var=w.L'

--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.
To post to this group, send email to gams...@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.
To post to this group, send email to gams...@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.
To post to this group, send email to gams...@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

Archiver
User
User
Posts: 7876
Joined: 1 year ago

RE: Looping over file names

Post by Archiver » 2 years ago


Hi Alex



Now I understand what you want to do…



I assume that you have scenarios numbered 1 to 3 (as example).

Where to send the output in the excel file is defined in the index in the excel file.

Let us assume you have two lines for two variables you want to send to the output file in the index, starting ab B4 ending at F6, the next at B7 ending at B9 (both with headers rdim and cdim).



You could then do the following by adding this at the top of your model file



$if not setglobal instance $set instance 1

$setglobal endrow



** If I have 2 variables I use the instance

$eval instance 3 * %instance% + 1

$eval endrow %instance% + 2



And then have your gdxxrw line like this



execute 'GDXXRW.exe i=GAMSOutput_%instance%.gdx o=RunTest_2D_1.XLS skipempty=0 index=GamsOutput!B%instance%:F%endrow % trace=3'





This would do the trick, if you run your run.gms with “instance” defined to be 1 and then 2 and 3.



I personally would do this using the gdxmerge utility (see gdx documentation) and then produce a pivot table where you can “scroll” easily through all scenarios.



Cheers



Renger









From: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] On Behalf Of Alexander Strenge
Sent: Mittwoch, 6. Mai 2015 18:23
To: gamsworld@googlegroups.com
Subject: Re: Looping over file names



I guess you misunderstood me.
I know that the model is running fine. That was not the issue.

The issue is that my model is not solving if I use the %instance% approach.
Could you maybe send me the adjusted file back?
"I adjusted the file bringing back “instance” and it also solves fine."

Alex


Am Mittwoch, 6. Mai 2015 18:13:00 UTC+2 schrieb Renger van Nieuwkoop:

Hi Alex



Runs fine on my computer. Here the output in my dos window.

What might happen is that you have the excel file open (see in the gdxutils.pdf for closing it using XLStalk.exe).

I deleted the results in the excel file and when I run the model, the sheet is filled with data. I adjusted the file bringing back “instance” and it also solves fine.



Iteration: 1 Dual objective = 2000.035000

LP status(1): optimal

Cplex Time: 0.02sec (det. 0.51 ticks)



Optimal solution found.

Objective : 7200.160000



--- Restarting execution

--- Reading solution for model LCC_MODEL

--- GDX Point d:\Inbox\alex\LCC_MODEL_p.gdx



GDXXRW 24.4.1 r50296 Released Dec 20, 2014 VS8 x86 32bit/MS Windows

Input file : d:\Inbox\alex\GAMSOutput.gdx

Output file: d:\Inbox\alex\RunTest_2D_1.XLS

Substituting Index

Total time = 3171 Ms

*** Status: Normal completion

--- Job Model_05.gms Stop 05/06/15 17:58:46 elapsed 0:00:05.608



If you add trace = 3 to the gdx-line I get a more usable output for debugging.



GDXXRW 24.4.1 r50296 Released Dec 20, 2014 VS8 x86 32bit/MS Windows

Excel version 15.0

Input file : d:\Inbox\alex\GAMSOutput.gdx

Output file: d:\Inbox\alex\RunTest_2D_1.XLS

Substituting Index

Loading sheet GAMSOutput

Reading data from Excel: Width = 13 Height = 22

var=z.l Rng=GAMSResults!A4:B5

var=f.l Rng=GAMSResults!A6:BN100 rdim=1 cdim=1

Squeeze value : 0.00000000000000E+0000

Squeeze value : 0.00000000000000E+0000

Type Symbol Dim Sheet Data RowHeader ColHeader

Var z 0 GAMSResults A4:A4 --:-- --:--

Clear range: A4:A4

Write range: A4:A4 CellCount = 1

Var f 2 GAMSResults B7:BN100 A7:A100 B6:BN6

Clear range: A6:BN100

Write range: A7:A100 CellCount = 94

Write range: B6:BN6 CellCount = 65

Write range: B7:N19 CellCount = 169

Total time = 3234 Ms

*** Status: Normal completion





Hope this helps



Renger







From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Alexander Strenge
Sent: Mittwoch, 6. Mai 2015 17:57
To: gams...@googlegroups.com
Subject: Re: Looping over file names



Not my day... Sorry! And thank you again for your help.

Alexander



Am Mittwoch, 6. Mai 2015 17:49:42 UTC+2 schrieb Renger van Nieuwkoop:

There is only one file: the model_05.gms file

Renger



From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Alexander Strenge
Sent: Mittwoch, 6. Mai 2015 17:48
To: gams...@googlegroups.com
Subject: Re: Looping over file names



You just need the "Model_05.gms" and "RunTest_2D_1.XLS" in one folder and it should work with reading and also writing to excel

Am Mittwoch, 6. Mai 2015 16:31:18 UTC+2 schrieb Renger van Nieuwkoop:

The gdx file is missing

Cheers

Renger



Von: gams...@googlegroups.com [mailto:gams...@googlegroups.com] Im Auftrag von Alexander Strenge
Gesendet: Wednesday, May 6, 2015 4:28 PM
An: gams...@googlegroups.com
Betreff: Re: Looping over file names



Hi Renger,

thank you.

I know that the input gdx file is created but the model will not be solved and the (output) excel file is not changed. Do I have to type "execute" instead of "call"?

I sent you a working gams and excel file for one instance without the %instance% and run.gms file.


Alex



Am Dienstag, 24. Februar 2009 21:23:41 UTC+1 schrieb Alison:

Hi,

I've written a GAMS program using the xls2gms function to upload data
and the execute_unload function to save the final result. However, I
need to run the same GAMS code for 50 different countries, which means
I have 50 different xls input files.

Is there a way to loop over file names so that I can change the input
file and the output file each time (e.g. Input_file_CountryX.xls,
Results_CountryX.gdx) ?

A simplified version of my code is below.

Thanks for your help-
Alison

-----------------------
*Read data from external spreadsheet
$call =xls2gms i=Input_file_Country1.xls r1=Sets!set_i
o1=industries.set r2=Sets!set_o o2=occupations.set r3=Wages!matrixB
o3=matrixB.inc r4=Sets!MatrixA o4=Average.inc

set i /
$include industries.set
/;
set o /
$include occupations.set
/;
**************
*Define B from external spreadsheet
Table B(o,i) degree of belief
$include matrixB.inc
;
Parameter avg(i) average by industry/
$include Average.inc
/;
**************

Equations, Objective Function, etc.

**************
*Run the model to get results
Model WAGES /objective/;
Solve WAGES using dnlp minimizing find_min;
Display w.l, w.m;
**************
*Export results to Excel by creating a .gdx file
execute_unload "Results_Country1.gdx" w.L w.M
execute 'gdxxrw.exe Results_Country1.gdx var=w.L'

--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.
To post to this group, send email to gams...@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.
To post to this group, send email to gams...@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.
To post to this group, send email to gams...@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

Archiver
User
User
Posts: 7876
Joined: 1 year ago

Re: Looping over file names

Post by Archiver » 2 years ago



Hello,

I'm pretty new to GAMS, I have a model which gives a different solution each time it runs through its loop, therefore I would like to get the data of each solution in an excel-file. I'm using the Mavrots-algorithm AUGMECON.

I have my excel-sheet, copy the data of the solution into the file and finally rename the xlsx-file to %RUN%.xlsx. I would like to change the value of "RUN" each time it runs through the loop, but I'm not able to or don't know how. (First Run --> RUN=1,..., Last Run --> RUN=20)

One more question: Should I place my calculations for the output-data somewhere different?

I'd very much appreciate any help on this matter.

$STitle eps-constraint method

***NEU**************************************************************************
$if not set RUN $set RUN 0
********************************************************************************

Set k1(k) the first element of k, km1(k) all but the first elements of k;
k1(k)$(ord(k)=1) = yes; km1(k)=yes; km1(k1) = no;
Set kk(k) active objective function in constraint allobj
Parameter
rhs(k) right hand side of the constrained obj functions in eps-constraint
maxobj(k) maximum value from the payoff table
minobj(k) minimum value from the payoff table
Variables
a_objval auxiliary variable for the objective function
obj auxiliary variable during the construction of the payoff table
Positive Variables
sl(k) slack or surplus variables for the eps-constraints
Equations
con_obj(k) constrained objective functions
augm_obj augmented objective function to avoid weakly efficient solutions
allobj all the objective functions in one expression;

con_obj(km1).. z(km1) - dir(km1)*sl(km1) =e= rhs(km1);

* We optimize the first objective function and put the others as constraints
* the second term is for avoiding weakly efficient points
augm_obj..
sum(k1,dir(k1)*z(k1))+1e-3*sum(km1,sl(km1)/(maxobj(km1)-minobj(km1))) =e= a_objval;

allobj.. sum(kk, dir(kk)*z(kk)) =e= obj;

Model mod_payoff / EMMA, allobj / ;
Model mod_epsmethod / EMMA, con_obj, augm_obj / ;

option limrow=0, limcol=0;
option solprint=off, solvelink=%solvelink.CallModule%;

Parameter
payoff(k,k) payoff tables entries;
Alias(k,kp);

* Generate payoff table applying lexicographic optimization
loop(kp,
kk(kp)=yes;
repeat
solve mod_payoff using lp maximizing obj;
payoff(kp,kk) = z.l(kk);
z.fx(kk) = z.l(kk);
kk(k++1) = kk(k);
until kk(kp); kk(kp) = no;
* release the fixed values of the objective functions for the new iteration
z.up(k) = inf; z.lo(k) =-inf;
);
if (mod_payoff.modelstat%modelstat.Optimal% and mod_payoff.modelstat%modelstat.FeasibleSolution%, abort 'no feasible solution for mod_payoff');


display payoff;
minobj(k)=smin(kp,payoff(kp,k));
maxobj(k)=smax(kp,payoff(kp,k));

$set fname p.%gams.scrext%
File fx solution points from eps-method / "%gams.scrdir%%fname%" /;

$if not set gridpoints $set gridpoints 10
Set g grid points /g0*g%gridpoints%/
grid(k,g) grid
Parameter
gridrhs(k,g) rhs of eps-constraint at grid point
maxg(k) maximum point in grid for objective
posg(k) grid position of objective
firstOffMax, lastZero some counters
numk(k) ordinal value of k starting with 1
numg(g) ordinal value of g starting with 0;
lastZero=1; loop(km1, numk(km1)=lastZero; lastZero=lastZero+1); numg(g) = ord(g)-1;

grid(km1,g) = yes; // Here we could define different grid intervals for different objectives
maxg(km1) = smax(grid(km1,g), numg(g));
gridrhs(grid(km1,g))$(%min%=dir(km1)) = maxobj(km1) - numg(g)/maxg(km1)*(maxobj(km1)- minobj(km1));
gridrhs(grid(km1,g))$(%max%=dir(km1)) = minobj(km1) + numg(g)/maxg(km1)*(maxobj(km1)- minobj(km1));
display gridrhs;

* Walk the grid points and take shortcuts if the model becomes infeasible
posg(km1) = 0;
repeat
rhs(km1) = sum(grid(km1,g)$(numg(g)=posg(km1)), gridrhs(km1,g));
solve mod_epsmethod maximizing a_objval using lp;
if (mod_epsmethod.modelstat%modelstat.Optimal%, // not optimal is in this case infeasible
lastZero = 0; loop(km1$(posg(km1)>0 and lastZero=0), lastZero=numk(km1));
posg(km1)$(numk(km1) res.txt
par=r_s rng=sys!a1 cdim=2
par=r_r rng=countries!a1 cdim=2
par=r_r2 rng=c2!a1 cdim=2
par=r_vf rng=vf!a1 cdim=2
par=r_vf2 rng=vf!n1 cdim=2
par=r_capa rng=capa!a1 cdim=2
par=r_capa2 rng=ca2!a1 cdim=2
par=r_ms rng=ms!a1 cdim=2
par=r_ms2 rng=ms!n1 cdim=2
par=r_gene rng=gene!a1 cdim=2
par=r_tec rng=tec!a1 cdim=2
par=r_sto rng=storage!a1 cdim=2
par=r_NTC rng=NTC!a1 cdim=2
par=r_set rng=p-setting!a1 rdim=1
par=r_set2 rng=p2!a1 rdim=1
par=r_mod rng=modelstats!a1
$offecho

$onecho > res_mv.txt
par=r_vf3 rng=out!a1 cdim=2
par=r_mod2 rng=out!n1 cdim=2
$offecho

$onecho > res_t.txt
par=r_tG rng=G!a3 rdim=1
par=r_tP rng=P!a3 rdim=1
par=r_day_year rng=day_year!a3 rdim=1
par=r_week_year rng=week_year!a3 rdim=1
par=r_hour_day rng=hour_day!a3 rdim=1
$offecho




$ontext
r_cal
r_cal2
r_cal3
r_cal4
-----
par=r_mo rng=merit-order!a20 rdim=2
par=r_cal rng=cal!a1 rdim=2
par=r_cal2 rng=cal!a25 rdim=3
par=r_cal3 rng=cal3!a1 rdim=4
par=r_cal4 rng=cal4!a1 rdim=4
$offtext




* Load excel templates

execute "move res*.xlsx °trash"
execute "copy °input\template_res.xlsx res.xlsx"
execute "copy °input\template_res_mv.xlsx res_mv.xlsx"
execute "copy °input\template_res_t.xlsx res_t.xlsx"



* export to excel files

execute "GDXXRW.EXE res.gdx EpsOut=0 o=res.xlsx @res.txt"
execute "GDXXRW.EXE res_mv.gdx EpsOut=0 o=res_mv.xlsx @res_mv.txt"
execute "GDXXRW.EXE res_t.gdx EpsOut=0 o=res_t.xlsx @res_t.txt"



* Rename XLSX and GDX files


execute "ren res.xlsx %RUN%.xlsx"
execute "ren res_mv.xlsx %RUN%_mv.xlsx"
execute "ren res_t.xlsx %RUN%_t.xlsx"
execute "ren all.gdx %PURPOSE%_%YDIM%_%HOURS%_%WINDPROFILE%_%SOLARPROFILE%_%SDIM%.gdx"


* Open excel file (for programming and testing)

$IF %OPEN% == "1"
execute "XLSTALK -o %PURPOSE%_%YDIM%_%HORIZON%_%HOURS%_%WINDPROFILE%_%SOLARPROFILE%_%SDIM%.xlsx"

********************************************************

until sum(km1$(posg(km1)=maxg(km1)),1)=card(km1) and firstOffMax=0;
putclose fx; // close the point file

* Get unique solutions from the point file using some Posix Tools (awk, (g)sort, uniq) that come with GAMS
$set awkscript awk.%gams.scrext%
file fa / "%gams.scrdir%%awkscript%" /; put fa 'BEGIN { printf("Table solutions(*,*)\n$ondelim\nsol';
loop(k, put ',' k.tl:0); putclose '\n"); }' / '{ print NR,$0 }' / 'END { print ";" }';
$if %system.filesys% == UNIX execute 'cd "%gams.scrdir%" && sort %fname% | uniq | awk -f %awkscript% > g.%gams.scrext% && gams g.%gams.scrext% o=gx.%gams.scrext% lo=0 gdx=soleps';
$if NOT %system.filesys% == UNIX execute 'cd "%gams.scrdir%" && gsort %fname% | uniq | awk -f %awkscript% > g.%gams.scrext% && gams g.%gams.scrext% o=gx.%gams.scrext% lo=0 gdx=soleps';
execute 'mv -f "%gams.scrdir%soleps.gdx" .';

Set sol Solutions /1*20/; Parameter solutions(sol,k) Unique solutions;
execute_load 'soleps', solutions; display solutions;

*$exit



--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at https://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

Post Reply