How do I correctly assign data from an excel table to a parameter on python/pyomo (concrete model)

Question

I currently have a concrete model with the following sets:

model.m = Set(initialize= ['m1', 'm2', 'm3', 'm4'])
model.p = Set(initialize= ['p1','p2','p3','p4','p5','p6','p7','p8','p9','p10',\
                           'p11','p12','p13','p14','p15','p16','p17','p18','p19',\
                               'p20','p21','p22','p23','p24'])

I read the excel data using pandas:

df = pd.read_excel('data.xls', sheet_names= 'data1', )
df.head()

And now I am trying to assign the data to the parameter:

model.E1 = Param(model.m, model.p, initialize = df.iloc[0:4,1:25].values)

It assigns the values fine (I have checked using 'print'), but when I try:

print(model.E1['m1', 'p2'])

It prints all the values for all m and p, instead of the value for [m1, p2]. This means that the parameter cannot distinguish the values with respect to the sets. Please can someone tell me how to do it correctly?

Answer 1

Answering my own question.

After reading the data from excel:

Create an empty dictionary:

Dict1 = dict()

Iterate over the rows (denoted by m) and columns (denoted by p) and fill the empty dictionary with values:

   for m in df.index:
        for p in df.columns:
            Dict1[m, p] = float(df[p][m])

Note that m and p are temporary indicators; they can be replaced with row, column or anything you wish to denote them with. Contrarily, df.index denotes the row titles and df.columns denotes the column titles.

Initialise the parameter with the related dictionary:

  model.E = Param(model.m, model.p, initialize = Dict1)

Test by printing a value from the parameter:

print(model.E['m1', 'p2'])