How to write output to excel file in gurobi in python?
Answered
Dear Python / Gurobi Professional,
I found the codes below can write the output to csv file. The var.varName will be written into the first cell in each row on excel file But how can I write the var.x into the second cell in each row on excel?
For example var.varName = 'A' var.x =2
var.varName = 'B' var.x =3
The excel file is written as below:
| A | 2 | |
| B | 3 |
** Code to write to excel as below:
var_names = []
for var in test.getVars():
if var.x > 0:
var_names.append(str(var.varName))
# Write to csv
with open('testout.csv', 'wb') as myfile:
wr = csv.writer(myfile, quoting=csv.QUOTE_ALL)
wr.writerows(zip(var_names))
-
Official comment
This post is more than three years old. Some information may not be up to date. For current information, please check the Gurobi Documentation or Knowledge Base. If you need more help, please create a new post in the community forum. Or why not try our AI Gurobot?. -
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 -
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 -
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 -
Dear Eli,
Thank you for your help. I will try your codes to see how it works.
Again, thank you
Robert
0 -
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 -
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
Post is closed for comments.
Comments
7 comments