Search code examples
pythonpython-3.xpyomo

Concrete Model reading xlsx document Pyomo

I am currently working on a Concrete Model, however, due to the parameters size, it seems very dificult to write almost 200 parameters with this form:

model.time = Param(model.WORKS, model.MODELS, initialize={(1,1):2, (2,1):1, (3,1):4, (4,1):2, (5,1):3, (6,1):3, (7,1):1, (8,1):2, (1,2):1, (2,2):2, (3,2):1, (4,2):3, (5,2):3, (6,2):2, (7,2):1, (8,2):1.....})

And this this is just one of the parameters. I would like to read tables from a xls document, something like this:

data.load(filename="excel.xlsx", range="Btable", format='set', set='B')

It is very difficult to tranform the model into an Abstract model due to the quantities of dictionary and concrete formulas.

Some idea how to do it, please? Thanks in advance.

Solution

  • This is absolutely something that you are able to do. Bill Hart is more familiar with the Pyomo-provided DataPortal interface than I am.

    I would personally import the Excel data using pandas and then write a function (rule) to create the Param using the pandas DataFrame object xls_data:

    xls_data = pandas.read_excel(**see pandas documentation**)

print(xls_data)  # This can help with debugging

@model.Param(model.WORKS, model.MODELS)
def time(m, wrks, mods):
    return float(xls_data[**appropriate index from pandas**])