How to write output to excel file in gurobi in python?
AnsweredDear 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))

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
Please sign in to leave a comment.
Comments
6 comments