I am having issues with setting up constraints using both input arrays from excel and variable arrays within PuLP.

It appears the model only works with square matrices and my final code has a matrix that is 365x24. The code below has a matrix of 5x6 and throws an error when running saying index out of range.

I have provided the code that I am working with so far.

Sheet11 Matrix

91  37  36  38  33  16
1   36  59  29  23  4
25  74  72  39  69  1
22  68  48  70  12  41
98  86  75  16  99  12

Sheet12 Matrix

59  63  66  57  4   15
26  33  75  71  21  2
37  88  89  1   90  3
91  48  27  24  23  14
68  13  61  37  77  20
from pulp import *
from pandas import *
import pandas as pd
import numpy as np
import xlrd

model = pulp.LpProblem("Basic Model", pulp.LpMinimize)

YPER = 5
HE = 6

yearlyhours = []
yearlyhours = [(i,j) for i in range(YPER) for j in range(HE)]

book = xlrd.open_workbook('Stack.xlsx')
sheet11 = book.sheet_by_name('Sheet11')
sheet12 = book.sheet_by_name('Sheet12')
sheet13 = book.sheet_by_name('Sheet13')

TEST = [[sheet11.cell_value(i,j) for i in range(YPER)] for j in range(HE)]
YAPR = [[sheet12.cell_value(i,j) for i in range(YPER)] for j in range(HE)]
MAPR = [[sheet13.cell_value(i,j) for i in range(YPER)] for j in range(HE)]

YAHL = pulp.LpVariable.dicts("YAHL", (range(YPER), range(HE)), lowBound=0, cat='Continuous')
MAHL = pulp.LpVariable.dicts("MAHL", (range(YPER), range(HE)), lowBound=0, cat='Continuous')

model += pulp.lpSum([YAPR[i][j] * YAHL[i][j] + MAPR[i][j] * MAHL[i][j] for i in range(YPER) for j in range(HE)]), 'Sum_of_Value'

for i,j in yearlyhours:
    model += pulp.lpSum([YAHL[i][j] + MAHL[i][j]]) == ([TEST[i][j]])

LpSolverDefault.msg = 1
print("Status:", LpStatus[model.status])
obj = value(model.objective)
print("Total Cost: ${}".format(obj,2))

When changing to a non-square matrix the following error message is given:

IndexError: list index out of range

Any help appreciated as I am still learning.


I think that the issue is the following:

YAPR = [[sheet12.cell_value(i,j) for i in range(YPER)] for j in range(HE)]

Here you create a list of list (YAPR) to represent your matrix. The outer list has a length of HE and the inner lists have a length of YPER.

model += pulp.lpSum([YAPR[i][j] * YAHL[i][j] + MAPR[i][j] * MAHL[i][j] for i in range(YPER) for j in range(HE)]), 'Sum_of_Value'

Here you address the list of list YAPR as if the outer list had a size of YPER and the inner list a size of HE. This is causing your out of bounds exception. You do not have the issue with square matrix because then YPER=HE which means that swapping the dimensions does not cause to go out of bounds.

You probably want to review your complete model because I saw those indices inversion on multiple lines.

  • $\begingroup$ This makes complete sense...not sure how I didn't see that problem. The real difficulty lies in the fact that I am unsure how to properly set up the indices such that this issue does not arise. Would you or anyone else be able to give me one example of the correct way to re-write the Sum_of_Value line so that I can see the right way of doing it? Thanks! $\endgroup$ – bathtub2007 Jun 23 '19 at 3:12
  • $\begingroup$ Well the easiest is probably to change your definition of YAPR into: YAPR = [[sheet12.cell_value(i,j) for j in range (HE)] for i in range(YPER)] $\endgroup$ – Renaud M. Jun 23 '19 at 18:08
  • $\begingroup$ Dude...Renaud...you are amazing. Was just able to implement this across my full model (not just the sample above) and it finally works the way I wanted it to. Appreciate the help! $\endgroup$ – bathtub2007 Jun 24 '19 at 14:29
  • $\begingroup$ Happy to help :-) $\endgroup$ – Renaud M. Jun 24 '19 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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