3
$\begingroup$

Using DOcplex module an optimization problem is solved.

There are many decision variables in the solution, for the validation of the mathematical model I want to check them.

How can I write the solution (decision variables) to Excel worksheets?

$\endgroup$

3 Answers 3

7
$\begingroup$

A great option is to use the pandas library, and specifically pandas.DataFrame.to_excel is what you need.

I suggest you use one column per variable index, and one line per non zero variable.

Here is one way of puting the solution into dataframe form. Something like:

variable_name = []
variable_value = []
for x in variables:
   if value(x)>0:
      variable_name.append(x)
      variable_value.append(value(x))

keys = ["variable_name", "variable_value"]
values = [variable_name, variable_value]
df_solution = DataFrame(dict(zip(keys,values)),columns=keys)
df_solution.to_excel("my_solution.xlsx")
$\endgroup$
3
  • $\begingroup$ I tried this option but the variables in the solution are not in dataframe form.So that doesn't work. Even so, thank you :) $\endgroup$
    – GTek
    Mar 22, 2022 at 19:32
  • 1
    $\begingroup$ Well of course they are not in dataframe form. But it is very easy to put them into this form :) $\endgroup$
    – Kuifje
    Mar 22, 2022 at 20:03
  • $\begingroup$ How can I put my variables into dataframe form? $\endgroup$
    – GTek
    May 28, 2022 at 18:24
4
$\begingroup$

I have used openpyxl several times to create or alter excel files. It is quite easy if one is familiar with Python. With this module you can iterate through your variables and write its value to a cell.

$\endgroup$
1
  • $\begingroup$ I use this too. Formatting with it can be a pain but I've found that a few helper functions I carry around with me are worth a few minutes to make. $\endgroup$ Mar 23, 2022 at 18:28
1
$\begingroup$

There are both pandas and openpyxl recommendations here. In my experience, if you want to go read and write on the cell level, or do more than basic analysis, use Pandas. Use openpyxl If you want to script basic excel functions

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.