Originally posted by: VeronikaL

Hi,

I tried to figure this out for two days...unfortunately I failed.

How can I fill arrays with multiple dimensions (more than 3) from an excel spreadsheet?

For example: I have transportation costs which are specific considering the transported material (i), the starting point (k) and destination of the route (l) and also depend on the mode of transportation (t). One of my decision variables is the amount to be transported between two location of a certain material using a certain mode of transportation.

Materials, Location (Starting points and destinations) and transportation modes are initialized as set of string. All of these sets have a minimum of 5 elements, which leads to several thousand specific costs and decision variables.

From that document I learned how to initialize 3-dimensional arrays by reading 2-dimensional table while the first index is set to specific value. Due to the high number of elements in each set of the array and the need to set more than one index that solution is not really helpful for my model, especially since dimensions of sets are likely to change. At the moment my spreadsheet has a index in each column and the lines are representing the number of none-zero elements within my array of cost.

I also tried to solve that problem using tuples. But I could not find out how to address a specific element within constraints. E.g. the cost between location k and l, transporting material i and using transportation mode t, calculation the sum over all materials and transportation modes within a loop for all routes (between k and l).

I really hope that my question is somewhat clear and that somebody is able to help me out. Thank you very much for your effort in advance.

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

have you had a look at https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000014419923 ?

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: VeronikaL

i did. That topic explains how write into spreadsheets, but I am unable to transform that case into reading data from excel to fill an array.

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

is your issue about how transforming a tuple set you already built into an array ?

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: VeronikaL

Hi,

I don't necessarily want to use tuples. If there is a workaround using tuples with which I can fill the array I would be really happy to hear/see how that works. Though, calculation are all done using arrays.

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi

You wrote:

"At the moment my spreadsheet has a index in each column and the lines are representing the number of none-zero elements within my array of cost."

So, you may try

http://www-01.ibm.com/support/docview.wss?rs=0&context=SSCMS55&uid=swg21401340&loc=en_US&cs=utf-8&cc=us&lang=all

or share your xls file

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: VeronikaL

http://www-01.ibm.com/support/docview.wss?rs=0&context=SSCMS55&uid=swg21401340&loc=en_US&cs=utf-8&cc=us&lang=all

isn't practicable due to the high number of elements in each set of the array and the need to set more than one index, especially since dimensions of sets are likely to change. I attached a smal example of the spreadsheet.

My model includes:

{string} Materials=...;

{string} Start=...;

{string} End=...;

{string} Transportation=...;

{string} Locations= Start union End;

float cost [Start][End][Materials][Transportation]=...;

float budget [Locations][Materials]=...;

dvar float+ amount [Locations][Locations][Materials][Transportation];

within my constraints something like this appears:

forall (l in End, i in Materials)                                                    
    sum (k in Locations, t in Transportation) amount [k][l][i][t] * cost [k][l][i][t] <= budget [l][i];

I really appreciate your help, Alex!

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi

What you should do is read your data as a tuple set.

In the .mod, you ll have

tuple t
{
string k;
string l;
string i;
string t;
float cost;

}

{t} input=...;

and then in the .dat you ll have a SheetRead

Have a look at the example Oil in

CPLEX_Studio1261\opl\examples\opl\oil

regards

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: VeronikaL

If I do so I am unable to transform my calculation using that tuple. Or is there a way to transform the tuple back into an array?

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

yes once you have the tuple set you may compute K, L, I, T the sets of k, l, i, t.

And then you may do a loop:

float cost[K][L][I][T];

execute

{

for(var x in input) cost[x.k][x.l][x.i][x.t]=x.cost;

}

regards

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: VeronikaL

Hi Alex,

it looks like I finally figured it out, due to your help. Thank you so much!

regards, Veronika

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: BhavnaJha

Hi, 

 

I am facing the same issue. How did you do it? By using tuples?

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Kamran_Sr

Dear Alex,

 

Could you please explain it further. I have the same problem. I am trying to solve with this code you wrote but I could not fix that after one week attempt.

 

Thanks,

Kamran

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

have you had a look at

http://www-01.ibm.com/support/docview.wss?rs=0&context=SSCMS55&uid=swg21401340&loc=en_US&cs=utf-8&cc=us&lang=all

?

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Kamran_Sr

Hi Alex,

 

Yes I have. However, I have struggling with 4 Dimensional parameters. I have used the code you wrote above this page, but the loop you have mentioned does not work for me. My code is as follows:

 

 {string} commodity=...;

 {string} origin=...;

 {string} dest=...;

 {string} mode=...;

 {string} location=origin union dest;

 

 

 tuple ODcost

 {

  string origin;

  string dest;

  string commodity;

  string mode;

  float cost; 

 }

 

 {ODcost} ODcostData=...;

 float cost[origin][dest][commodity][mode];

 

 execute { 

 for(var x in ODcostData) cost[x.origin][x.dest][x.commodity][x.mode]=x.cost;

}

 

 tuple ODtime

 {

  string origin;

  string dest;

  string commodity;

  string mode;

  float time;

 }

 

 {ODtime} ODtimeData=...;

 

 float time[origin][dest][commodity][mode];

 execute { 

 for(var x in ODtimeData) time[x.origin][x.dest][x.commodity][x.mode]=x.time;

}

 

 tuple Empty

 {

 

  string origin;

  string dest;

  string mode;

  float emp;

 

 }

 

 {Empty} EmpData=...;

 

 float emp[origin][dest][mode];

 execute { 

 for(var x in EmpData) emp[x.origin][x.dest][x.mode]=x.emp;

}

 

 

The loop that I used does not work and got error. I have attached the files for more convenient.

I would be appreciated if you could help me with that.

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

the issues are not in the read part but in the model itself.

I fixed your errors and will let you work from there

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Kamran_Sr

Hi Alex,

 

Thanks for your reply. The problem that I have regarding to this issue is the range size of the tuple Set. I have a tuple as follows:

tuple

ODcost

{

string origin;

string ports;

string dest;

string commodity;

string mode;

float cost;

}

{

ODcost} ODcostData=...;

float cost[origin][ports][dest][commodity][mode];

execute {

for(var x in ODcostData) cost[x.origin][x.ports][x.dest][x.commodity][x.mode]=x.cost;

}

Suppose that I have 2 origins, 4 ports, 2 dest, 2 commodity and 4 modes. As I have found out so far, there should be 2*4*2*2*4 = 128 data in excel which are organized in columns. However, my problem is that, all the routes are not exist. For example there may no route between origin 1 to port 2 to dest 3 by mode 1. When I just use the data which are exist, I got the error of "excel: range size is not the size of tupleSet".

I am wondering if there is a solution that I can solve this problem.

Regards,

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

can t you change the range within SheetRead in order to adapt to Numbers of rows in Excel ?

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Nadia.pmz

Hi,

I just wrote a very simple code to see whether I have gotten the logic of transforming a three dimensional parameter to a tuple right, so that I can use it in my original code where I have a 5-dimensional parameter.

But I get the following error "range size is not the size of tuple set"

In my excel sheet I have entered a 1*48 (4*4*3) array for cprimedata

HERE IS THE CODE:

range I=1..4;
range J=1..4;
range T=1..3;

tuple cprime{int i;int j;int t; float c;};
{cprime} cprimedata=...;
float c[I][J][T];
execute{for(var X in cprimedata) c[X.I][X.J][X.T]=X.c;};

dvar float+ Y[I][J][T];

minimize sum(i in I, j in J, t in T) Y[i][j][t]*c[i][j][t];
subject to {
  forall(t in T) sum(i in I, j in J) Y[i][j][t]>=4;
}

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

be careful with lower and upper case

 

range I=1..4;
range J=1..4;
range T=1..3;

tuple cprime{int I;int J;int T; float c;};
{cprime} cprimedata={<1,2,3,4>};
float c[I][J][T];
execute{for(var X in cprimedata) c[X.I][X.J][X.T]=X.c;};

dvar float+ Y[I][J][T];

minimize sum(i in I, j in J, t in T) Y[i][j][t]*c[i][j][t];
subject to {
  forall(t in T) sum(i in I, j in J) Y[i][j][t]>=4;
}

works fine

 

regards

 

https://medium.com/@alexfleischer_84755/optimization-simply-do-more-with-less-zoo-buses-and-kids-66940178db6

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Nadia.pmz

Thank you for your help. I changed it but still getting the same error

I want to read cprimedata  from an excel sheet. So what is this {cprime} cprimedata={<1,2,3,4>} ?

 my parameter C[i][j][t] is of size 4*4*3 so I entered 1*48 for tuple cprimedata in excel. And get the error: "range size is not the size of tuple set"

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

 

{cprime} cprimedata={<1,2,3,4>};

is a tiny example of 4*1 that you could read through a SheetRead

If you have 48 of those then you would need to read 48*4

A tuple with 4 dimensions takes 4 cells not 1

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Nadia.pmz

Dear Alex

Thank you very much for your quick response.

But I did not really get your meaning So I will explain the problem clearly and I would appreciate if you can help me.

I have a parameter Cijt (i,j, t indices of sizes 4,4 and 3 respectively)

As I cannot enter it in its current form (like two dimensional matrices) in  Excel to read it for my code, I am using tuple:

 

range I=1..4;

range J=1..4;

range T=1..3;

 

tuple cprime{int I;int J;int T; float c;};

{cprime} cprimedata=...;

float c[I][J][T];

execute{for(var X in cprimedata) c[X.I][X.J][X.T]=X.c;};

dvar float+ Y[I][J][T];

 minimize sum(i in I, j in J, t in T) Y[i][j][t]*c[i][j][t];

subject to {

forall(t in T) sum(i in I, j in J) Y[i][j][t]>=4;

}

 

And in my .dat file I have:

SheetConnection my_sheet("Data.xlsx");

cprimedata from SheetRead (my_sheet, "MM");

 

Then how should I enter these elements of Cijt in form of  cprimedata in Excel? Cijt was of size 48 (4*4*3) and I thought I should enter these 48 elements respectively

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi

if you read a 1*48 in excel you may turn that into a 3D array

range I=1..4;
range J=1..4;
range T=1..3;

range R=1..48;

int v[n in 1..48]=n;

int v2[i in I][j in J][t in T]=v[(t-1)*16+(j-1)*4+i-1+1];

execute
{
  writeln(v2);
}

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer