Converting Excel Solver Solution to Python Pulp

Question

I am finding it hard to convert an Excel Solver model into python pulp syntax. In my model, I am optimizing the HC and OT variables for each department, with an objective of minimizing the sum of the OT variables. The constraints require that the HC variables sum to no more than 92 and that the total production (=E2*C2*D2 + F2*C2in the spreadsheet below) meets a per-department requirement (the "Input" column of the excel spreadsheet below). The Excel solver formulation shown below works perfectly.

Problem

1. How can I write my objective function in pulp (in Excel F7 =SUM(F2:F6))?
2. Constraints E7 <= 92
3. Constraints G2:G6 >= B2:B6
4. I have two decision variable HC and OT. In the below python code I only created one variable.

Before

After Solver

import pulp
import numpy as np
import pandas as pd

idx = [0, 1, 2, 3, 4]

d = {'Dept': pd.Series(['Receiving', 'Picking', 'PPicking', 'QC', 'Packing'], index=idx),
     'Target': pd.Series([61,94,32,63,116], index=idx),
     'Hrs/day': pd.Series([7.75, 7.75, 7.75, 7.75, 7.75], index=idx),
     'Prod': pd.Series([11733, 13011, 2715, 13682, 14194], index=idx),
     'HC': pd.Series([24,18,6,28,16], index=idx), 
     'OT': pd.Series([0,0,42,0,0], index=idx)}

df = pd.DataFrame(d)

# Create variables and model
x = pulp.LpVariable.dicts("x", df.index, lowBound=0)
mod = pulp.LpProblem("OTReduction", pulp.LpMinimize)

# Objective function 
mod += sum(df['OT'])


# Lower and upper bounds:
for idx in df.index:
    mod += x[idx] <= df['Input'][idx]


# Total HC value should be less than or equal to 92
mod += sum([x[idx] for idx in df.index]) <= 92


# Solve model
mod.solve()

# Output solution
for idx in df.index:
    print idx, x[idx].value()


# Expected answer 
# HC,   OT 
# 19,   35.795 
# 18,   0
# 11,   0
# 28,   0 
# ----------------
# 92,  35.795  ->  **note:** SUM(HC), SUM(OT)

Answer 1

There are a few issues with your posted Pulp code.

You only declare one set of variables, x, but you have two sets in your excel formulation, HC and OT. You should declare two separate sets of variables, and name them appropriately:

HC = pulp.LpVariable.dicts("HC", df.index, lowBound=0)
OT = pulp.LpVariable.dicts("OT", df.index, lowBound=0)

When you add the objective as mod += sum(df['OT']), you are trying to add a column of a data frame to the model, which causes an error. Instead you want to add the sum of the OT variables, which can be achieved with:

mod += sum([OT[idx] for idx in df.index])

When you add the constraints x[idx] <= df['Input'][idx], you are requiring that your x variables are upper bounded by the Input data. However in reality you have a more complicated constraint -- note that in the excel code you were lower bounding E2*C2*D2 + F2*C2 by the Input column. Your constraint here should exhibit that same logic:

for idx in df.index:
    mod += df['Target'][idx] * df['Hrs/day'][idx] * HC[idx] + df['Target'][idx] * OT[idx] >= df['Prod'][idx]

Putting this all together yields the desired output:

import pulp
import pandas as pd

# Problem data
idx = [0, 1, 2, 3, 4]
d = {'Dept': pd.Series(['Receiving', 'Picking', 'PPicking', 'QC', 'Packing'], index=idx),
     'Target': pd.Series([61,94,32,63,116], index=idx),
     'Hrs/day': pd.Series([7.75, 7.75, 7.75, 7.75, 7.75], index=idx),
     'Prod': pd.Series([11346, 13011, 2715, 13682, 14194], index=idx)}
df = pd.DataFrame(d)

# Create variables and model                                                                                                 
HC = pulp.LpVariable.dicts("HC", df.index, lowBound=0)
OT = pulp.LpVariable.dicts("OT", df.index, lowBound=0)
mod = pulp.LpProblem("OTReduction", pulp.LpMinimize)

# Objective function                                                                                                         
mod += sum([OT[idx] for idx in df.index])

# Lower and upper bounds:                                                                                                    
for idx in df.index:
    mod += df['Target'][idx] * df['Hrs/day'][idx] * HC[idx] + df['Target'][idx] * OT[idx] >= df['Prod'][idx]

# Total HC value should be less than or equal to 92                                                                          
mod += sum([HC[idx] for idx in df.index]) <= 92

# Solve model                                                                                                                
mod.solve()

# Output solution                                                                                                            
for idx in df.index:
    print(idx, HC[idx].value(), OT[idx].value())
# 0 24.0 0.0
# 1 13.241236 35.795316
# 2 10.947581 0.0
# 3 28.022529 0.0
# 4 15.788654 0.0