Decision Optimization

 View Only
Expand all | Collapse all

Export Tuple to Excel/ For-loop questions

  • 1.  Export Tuple to Excel/ For-loop questions

    Posted Sun April 24, 2022 10:11 PM
    Edited by Jia Zhong Mon April 25, 2022 07:44 AM
    I am still learning the tricks of coding the model. I am still digesting the tricks of OPL.

    1. I have a variable "Lifetimecost_est" having multiple dimensions. I use tuple to export the data to excel. It is a data set of 16×7×4×3×24 (32,256). I checked many answers from the community, and was confused by the setup for the excel range. I am not sure for a tuple like this, how could I set up the range of the excel correctly? I have now 24 columns (segments) and 50,000 rows. But still got the errors "".

    tuple Lifetimecost_tup{
      int t;
      string cost;
      string vehicle;
      string vmtgrp;
      string segment;
      float value;
    }
    {Lifetimecost_tup} Lifetimecost_exp= {<i,j,k,z,l,Lifetimecost_est[i][j][k][z][l]> | i in t, j in cost, k in vehicle, z in vmtgrp,l in segment};​
    
    Lifetimecost_exp to SheetWrite(Outputs,"Lifetimecost!F2:AC50000");


    2. I have a parameter needs to multiply a yearly -0.5% growth rate starting 2016. It is also a multi-dimensional data MileB_seg[ti][s][vmt], ti is the year set. Math.pow((1-0.005),(ti-2016)) applies the compounded annual growth rate.
    What I did is putting it in the script 

    float MileB_seg[t][segment][vmtgrp];
    execute{
    for (var ti in t) {
       MileB_seg[ti][s][vmt]=MileB_seg[ti][s][vmt]*Math.pow((1-0.005),(ti-2016));
    }}
    However, I found the MileB_seg still does not change with the time. Wondering what should I code it correctly to apply this compounded growth rate?

    Thanks a lot!

    ------------------------------
    Jia
    ------------------------------


  • 2.  RE: Export Tuple to Excel/ For-loop questions

    Posted Mon April 25, 2022 04:16 AM
    Dear Jia,

    About question 2.  You seem to use the same array cell in both sides of your assignment. In your loop you are doing x = x Math.pow(...) with x which is not initialized.
    I don't think it is what you want.
    I hope this helps.
    Cheers,

    ------------------------------
    Renaud Dumeur
    ------------------------------



  • 3.  RE: Export Tuple to Excel/ For-loop questions

    Posted Mon April 25, 2022 03:20 PM
    Edited by Jia Zhong Tue April 26, 2022 08:14 AM
    Hi Renaud,

    Thanks for the reply.

    For question 1, the simplified example is as follows:
    //In *.mod file:
    
    range t=2016..2031; //
    {string} cost=...;
    float Lifetimecost_est[ti in t][c in cost]=ti;
    
    
    
    tuple Lifetimecost_tup{
      int t;
      string cost;
      float value;
    }
    
    {Lifetimecost_tup} Lifetimecost_exp= {<i,j,Lifetimecost_est[i][j]> | i in t, j in cost};
    
    execute { 
    	
    writeln(Lifetimecost_tup,Lifetimecost_exp); 
    
    };
    
    //In *.dat file
    cost={purchase,maintanence,fuel,detour,range,wait,idio};
    SheetConnection Outputs("C:\\Users\\jzhong17\\OneDrive - azureford\\Documents\\CPLEX\\OPL project\\Outputs.xlsx");
    Lifetimecost_exp to SheetWrite(Outputs,"Test!F2:ZZ500");​

    This simplified model will only generate a 14×7data, but I leave a F2:zz500 space in excel still generate the error of "Description Resource Path Location Type
    Exception from IBM ILOG Concert: excel: range width does not fit the arity of the tuple. Test Unknown OPL Problem Marker".


    For question 2, I did have initialize the MileB_seg[ti][s][vmt]​ before this statement in a different for-loop, and MileB_seg[ti][s][vmt] is the same across ti set. Even if I have coded the math.pow() statement, MileB_seg still remain the same.
    What I concerned about is that in the script I only apply the for-loop for year "ti", not for other sets (segment and vmtgrp), but I still use the index for these set. ​

    ------------------------------
    Jia
    ------------------------------



  • 4.  RE: Export Tuple to Excel/ For-loop questions

    Posted Tue April 26, 2022 05:12 AM
    Dear Jia,

    Your tuple has three elements so your excel write range should cover 3 excel columns.
    However, vertically the range could go further than the 7*16 rows,  but extra rows for which no tuple exist will be ignored.

    Cheers,

    ------------------------------
    Renaud Dumeur
    ------------------------------



  • 5.  RE: Export Tuple to Excel/ For-loop questions

    Posted Tue April 26, 2022 07:57 AM

    Hi Jia,

     

    Instead of reading the data from Excel, you could convert your Excel file to CSV file and load it into .mod.

     

    You could also redirect the output from .mod to either .txt or csv file.

     

    In the attached example, I am solving a version of Sudoku with CP, reading the data from a csv file, and writing the output into csv file.

    You can run it on your machine but renaming the path of the model (saved as attached rtf file).

     

    I hope this will help you organize better your model avoiding hard coding in reading/writing Excel files.

     

    Best regards,

     

     

     


     

    Description: Description: email-signature-image_0014_H_COC-CTR_Surprint_1_-TO-3__BIL_ENG.png

    Nourredine Hail, PhD in Applied Mathematics
    Senior Operations Research & Data Scientist

    Data Analytics Governance team
    Canadian Tire Corporation
    2111 Steeles Avenue East, Brampton, ON, L6T4L5
    Phone: 905.792.5983  
    nourredine.hail@cantire.com

    "Anyone who stops learning is old, whether at twenty or eighty. Anyone who keeps learning stays young. The greatest thing in life is to keep your mind young."  Henry Ford

     




    Attachment(s)

    rtf
    SUDOKU.rtf   6 KB 1 version


  • 6.  RE: Export Tuple to Excel/ For-loop questions

    Posted Tue April 26, 2022 08:18 AM
    Thank you, Nourredine. But I did not see the attached example. Can you please upload it again?

    Thanks,
    Jia

    ------------------------------
    Jia
    ------------------------------



  • 7.  RE: Export Tuple to Excel/ For-loop questions

    Posted Tue April 26, 2022 08:21 AM

    Jia,

     

    The files have been uploaded as attached files (see my screenshot).

     

    Thanks

     


     

    Description: Description: email-signature-image_0014_H_COC-CTR_Surprint_1_-TO-3__BIL_ENG.png

    Nourredine Hail, PhD in Applied Mathematics
    Senior Operations Research & Data Scientist

    Data Analytics Governance team
    Canadian Tire Corporation
    2111 Steeles Avenue East, Brampton, ON, L6T4L5
    Phone: 905.792.5983  
    nourredine.hail@cantire.com

    "Anyone who stops learning is old, whether at twenty or eighty. Anyone who keeps learning stays young. The greatest thing in life is to keep your mind young."  Henry Ford

     






  • 8.  RE: Export Tuple to Excel/ For-loop questions

    Posted Tue April 26, 2022 08:22 AM

    Click on view attached to see them.

     


     

    Description: Description: email-signature-image_0014_H_COC-CTR_Surprint_1_-TO-3__BIL_ENG.png

    Nourredine Hail, PhD in Applied Mathematics
    Senior Operations Research & Data Scientist

    Data Analytics Governance team
    Canadian Tire Corporation
    2111 Steeles Avenue East, Brampton, ON, L6T4L5
    Phone: 905.792.5983  
    nourredine.hail@cantire.com

    "Anyone who stops learning is old, whether at twenty or eighty. Anyone who keeps learning stays young. The greatest thing in life is to keep your mind young."  Henry Ford

     






  • 9.  RE: Export Tuple to Excel/ For-loop questions

    Posted Mon April 25, 2022 04:19 AM
    Dear Jia,

    About question 1, please provide a minimal model and data so that we can reproduce your problem.
    Thank you in advance,

    Cheers,

    ------------------------------
    Renaud Dumeur
    ------------------------------



  • 10.  RE: Export Tuple to Excel/ For-loop questions

    Posted Tue April 26, 2022 11:09 AM
    Edited by Frederic Delhoume Tue April 26, 2022 11:10 AM
    The export to Excel code must be in a dat file, and is called after a  solve.

    //sample.mod
    tuple toto {
    int num;
    string str;
    }

    {toto} mytuple = {
    <10, "text1">,
    <20, "text2">
    };

    execute {
    mytuple;
    }


    //sample.dat
    SheetConnection sheet("exported.xls");
    mytuple to SheetWrite(sheet, "A1:B2");


    running this updates the xls file (that file must exist as it will not be created by opl).



    ------------------------------
    Frederic Delhoume
    ------------------------------



  • 11.  RE: Export Tuple to Excel/ For-loop questions

    Posted Wed April 27, 2022 11:36 AM
    Thanks Frederic!




    ------------------------------
    Jia
    ------------------------------



  • 12.  RE: Export Tuple to Excel/ For-loop questions

    Posted Wed April 27, 2022 03:03 PM
    //*.mod 
    range t=2016..2031; //
    {string} cost=...;
    float Lifetimecost_est[ti in t][c in cost]=ti;
    float data[ti in t]=ti;
    
    execute{
          for (var ti in t) {
           Lifetimecost_est[ti][c in cost]=Lifetimecost_est[ti][c]*Math.pow((1-0.005),(ti-2016));
    }
    writeln(Lifetimecost_est);
    };
    
    //*.dat
    cost={purchase,maintanence,fuel,detour,range,wait,idio};​

    Hi Renaud, here is the example. I have this multi-dimensional parameter Lifetimecost_est, I would like to let it degrades by 5% annually. In this simplified example, the values are the same across set c. But the idea is let the value change by 5% for all cost categories.
    The above statement will yield error message. I need help in correcting this code. I have been searching online for a while about the solution of this. 

    Thanks a lot!



    ------------------------------
    Jia
    ------------------------------