Skip to main content

How to write output to excel file in gurobi in python?

Answered

Comments

6 comments

  • Eli Towle
    Gurobi Staff Gurobi Staff

    Hi Han,

    You can simply create a new list that stores the variable's values, then add that list to the zip() function. Building on your code, this looks like:

    var_names = []
    var_values = []

    for var in test.getVars():
    if var.X > 0:
    var_names.append(str(var.varName))
    var_values.append(var.X)

    # Write to csv
    with open('testout.csv', 'wb') as myfile:
    wr = csv.writer(myfile, quoting=csv.QUOTE_ALL)
    wr.writerows(zip(var_names, var_values))

    This could be done even more succinctly with a list comprehension:

    varInfo = [(v.varName, v.X) for v in test.getVars() if v.X > 0]

    # Write to csv
    with open('testout.csv', 'wb') as myfile:
        wr = csv.writer(myfile, quoting=csv.QUOTE_ALL)
        wr.writerows(varInfo)

    Also, it may be better to use QUOTE_NONNUMERIC instead of QUOTE_ALL in this case. This makes the file data easier to read and potentially easier to programmatically access.

    I hope this helps!

    0
  • Han Shih
    Gurobi-versary
    Conversationalist
    Curious

    Dear Eli,

     

    Thank you so much for your help. It works and save me a lot of time to manipulate the data.

    Can I ask you one more question?

    Can you write output to a matrix form?

    For example:

               A1    A2     A3

    B1       1       2       3

    B2       3       4       5

    Thank you again

    Robert

     

     

     

    0
  • Eli Towle
    Gurobi Staff Gurobi Staff

    Hi Robert,

    I'll assume you have defined A and B to be the respective index sets, and have a tupledict x that maps those indices to the respective Var objects, e.g.,

    x = model.addVars(A, B)

    Then you can do this with the csv module:

    with open('solution.csv', 'wb') as myFile:
        wr = csv.writer(myFile, quoting=csv.QUOTE_NONNUMERIC)

        # Write header
        wr.writerow([""] + [a for a in A])

        # Write each row
        wr.writerows([b] + [x[a,b].X for a in A] for b in B)

    This might be a little cleaner to do with something like pandas:

    import pandas as pd

    # Rest of code goes here

    # Create matrix
    matrix = [[x[a,b].X for a in A] for b in B]

    # Create DataFrame
    df = pd.DataFrame(matrix, columns=A, index=B)

    # Write to CSV
    df.to_csv('solution.csv')

    Does this help? Thanks!

    Eli

    0
  • Han Shih
    Gurobi-versary
    Conversationalist
    Curious

    Dear Eli,

     

    Thank you for your help. I will try your codes to see how it works.

    Again, thank you

    Robert

    0
  • Han Shih
    Gurobi-versary
    Conversationalist
    Curious

    Dear Eli,

    I check my code regarding matrix output in excel. Can you halp me?

    The codes I have as below:

    m.addConstrs(( EACHDAYSCEN[TT,KK] == EACHDAYSCENCOST[TT,KK] for TT in set_T
                                                                                                                  for KK in set_OMEGA), name='constraint3')

    For example: TT=5, KK=3

    How can you write EACHDAYSCEN[TT,KK] into excel with matrix such that

                        K1       K2        K3

    T1                 1           2         3

    T2                 5           6         7

    T3                   .             .       .

    T4                  .              .       .

    T5                 .               .         .

     

    Thank you very much

    Robert

    0
  • Eli Towle
    Gurobi Staff Gurobi Staff

    Hi Robert,

    It looks like you're adding a constraint that fixes a set of variables to constant values. It's a better idea to simply add the fixed values directly to the model wherever those variables appear.

    Did you try the code snippet(s) in my earlier post? If EACHDAYSCEN is a tupledict of Gurobi variable objects, you should be able to write the values to an output file by programmatically accessing the X attributes of the variables (after optimizing) in the manner I described.

    Thanks!

    Eli

    0

Please sign in to leave a comment.