How to write pyomo solutions (including only parameters, objective and variables) of many iterations into excel/csv file?

Question

I have a pyomo model with a mutable parameter "model.Prices". With a for loop, the model is able to iterate and find optimal solutions. But I don't know how to write solutions (including value of parameters, objective, variables) of all iterations into a excel/csv file.

Here is the for loop

for RapeseedPrice in range(300, 600, 100):
    # change the value of parameter model.Prices
    model.Prices["rapeseed"] = RapeseedPrice
    print("When price of rapeseed = {}".format(RapeseedPrice))
    opt = SolverFactory('ipopt')
    opt.solve(model, tee = True) 
    results = opt.solve
    model.display()

if I include the following three for loops, I can access all the values I want:

for RapeseedPrice in range(300, 600, 100):
    # change the value of parameter model.Prices
    model.Prices["rapeseed"] = RapeseedPrice
    print("When price of rapeseed = {}".format(RapeseedPrice))
    opt = SolverFactory('ipopt')
    opt.solve(model, tee = True) 
    results = opt.solve
    model.display()
    for parmobject in model.component_objects(Param, active=True):
        nametoprint = str(str(parmobject.name))
        print ("Parameter ", nametoprint) # doctest: +SKIP
        for index in parmobject:
            vtoprint = value(parmobject[index])
            print (" ",index, vtoprint) # doctest: +SKIP
    for o in model.component_data_objects(Objective, active=True):
        print(o, value(o)) 
    for v in model.component_data_objects(Var, active=True):
        print(v, value(v)) 

I expect an excel file which has different interactions as columns and all parameters, objective, variables as rows.

            it1        it2       it3
param 1
param 2
param 3
...
objective
var 1
var 2
var 3
...

Answer 1

Let me answer my own question.

for this we need import:

import pandas as pd
import numpy as np
from copy import deepcopy 

first, you need to get headers:

header0 = []
header1 = []

for parmobject in model.component_objects(Param, active=True):
        nametoprint = str(str(parmobject.name))        
        for index in parmobject:
                header0.append(nametoprint)
                header1.append(index)
for o in model.component_data_objects(Objective, active=True):
        header0.append("Objective")
        header1.append(str(o.name))
for v in model.component_data_objects(Var, active=True):
        header0.append("Variable")
        header1.append(str(v.name))

MultiHeaders = [header0, header1]

Secondly, you must get values:

AllData = []
pov_data = []

for RapeseedPrice in range(100, 500, 100): 

    model.Prices['rapeseed'] = RapeseedPrice

    # solve the model
    opt.solve(model) 

    # access pov_dat  
    for parmobject in model.component_objects(Param, active=True):
            for index in parmobject:
                    vtoprint = value(parmobject[index])
                    pov_data.append(vtoprint)
    for o in model.component_data_objects(Objective, active=True):
            pov_data.append(value(o))

    for v in model.component_data_objects(Var, active=True):
            pov_data.append(value(v)) 

    AllData.append(deepcopy(pov_data))
    pov_data.clear()

Thridly, you combine your data with your headers into a data frame:

CDFarm_results = pd.DataFrame(data = np.array(AllData), columns = 
     MultiHeaders)

Last, you save your dataframe into an excel file.

writer = pd.ExcelWriter('CDFarm_results_pandas.xlsx', engine='xlsxwriter')
CDFarm_results.to_excel(writer, 'Sheet1')
writer.save()

Please look at my gist here: this script is able to save names and values of all parameters, objectives, and variables into an excel file my-gist-link

the final excel will look like this: