Manpower Planning¶
Reference¶
SAS/OR example: http://go.documentation.sas.com/?docsetId=ormpex&docsetTarget=ormpex_ex5_toc.htm&docsetVersion=15.1&locale=en
SAS/OR code for example: http://support.sas.com/documentation/onlinedoc/or/ex_code/151/mpex05.html
Model¶
import sasoptpy as so
import pandas as pd
import math
def test(cas_conn):
# Input data
demand_data = pd.DataFrame([
[0, 2000, 1500, 1000],
[1, 1000, 1400, 1000],
[2, 500, 2000, 1500],
[3, 0, 2500, 2000]
], columns=['period', 'unskilled', 'semiskilled', 'skilled'])\
.set_index(['period'])
worker_data = pd.DataFrame([
['unskilled', 0.25, 0.10, 500, 200, 1500, 50, 500],
['semiskilled', 0.20, 0.05, 800, 500, 2000, 50, 400],
['skilled', 0.10, 0.05, 500, 500, 3000, 50, 400]
], columns=['worker', 'waste_new', 'waste_old', 'recruit_ub',
'redundancy_cost', 'overmanning_cost', 'shorttime_ub',
'shorttime_cost']).set_index(['worker'])
retrain_data = pd.DataFrame([
['unskilled', 'semiskilled', 200, 400],
['semiskilled', 'skilled', math.inf, 500],
], columns=['worker1', 'worker2', 'retrain_ub', 'retrain_cost']).\
set_index(['worker1', 'worker2'])
downgrade_data = pd.DataFrame([
['semiskilled', 'unskilled'],
['skilled', 'semiskilled'],
['skilled', 'unskilled']
], columns=['worker1', 'worker2'])
semiskill_retrain_frac_ub = 0.25
downgrade_leave_frac = 0.5
overmanning_ub = 150
shorttime_frac = 0.5
# Sets
WORKERS = worker_data.index.tolist()
PERIODS0 = demand_data.index.tolist()
PERIODS = PERIODS0[1:]
RETRAIN_PAIRS = [i for i, _ in retrain_data.iterrows()]
DOWNGRADE_PAIRS = [(row['worker1'], row['worker2'])
for _, row in downgrade_data.iterrows()]
waste_old = worker_data['waste_old']
waste_new = worker_data['waste_new']
redundancy_cost = worker_data['redundancy_cost']
overmanning_cost = worker_data['overmanning_cost']
shorttime_cost = worker_data['shorttime_cost']
retrain_cost = retrain_data['retrain_cost'].unstack(level=-1)
# Initialization
m = so.Model(name='manpower_planning', session=cas_conn)
# Variables
numWorkers = m.add_variables(WORKERS, PERIODS0, name='numWorkers', lb=0)
demand0 = demand_data.loc[0]
for w in WORKERS:
numWorkers[w, 0].set_bounds(lb=demand0[w], ub=demand0[w])
numRecruits = m.add_variables(WORKERS, PERIODS, name='numRecruits', lb=0)
worker_ub = worker_data['recruit_ub']
for w in WORKERS:
for p in PERIODS:
numRecruits[w, p].set_bounds(ub=worker_ub[w])
numRedundant = m.add_variables(WORKERS, PERIODS, name='numRedundant', lb=0)
numShortTime = m.add_variables(WORKERS, PERIODS, name='numShortTime', lb=0)
shorttime_ub = worker_data['shorttime_ub']
for w in WORKERS:
for p in PERIODS:
numShortTime.set_bounds(ub=shorttime_ub[w])
numExcess = m.add_variables(WORKERS, PERIODS, name='numExcess', lb=0)
retrain_ub = pd.DataFrame()
for i in PERIODS:
retrain_ub[i] = retrain_data['retrain_ub']
numRetrain = m.add_variables(RETRAIN_PAIRS, PERIODS, name='numRetrain',
lb=0, ub=retrain_ub)
numDowngrade = m.add_variables(DOWNGRADE_PAIRS, PERIODS,
name='numDowngrade', lb=0)
# Constraints
m.add_constraints((numWorkers[w, p]
- (1-shorttime_frac) * numShortTime[w, p]
- numExcess[w, p] == demand_data.loc[p, w]
for w in WORKERS for p in PERIODS), name='demand')
m.add_constraints((numWorkers[w, p] ==
(1 - waste_old[w]) * numWorkers[w, p-1]
+ (1 - waste_new[w]) * numRecruits[w, p]
+ (1 - waste_old[w]) * numRetrain.sum('*', w, p)
+ (1 - downgrade_leave_frac) *
numDowngrade.sum('*', w, p)
- numRetrain.sum(w, '*', p)
- numDowngrade.sum(w, '*', p)
- numRedundant[w, p]
for w in WORKERS for p in PERIODS),
name='flow_balance')
m.add_constraints((numRetrain['semiskilled', 'skilled', p] <=
semiskill_retrain_frac_ub * numWorkers['skilled', p]
for p in PERIODS), name='semiskill_retrain')
m.add_constraints((numExcess.sum('*', p) <= overmanning_ub
for p in PERIODS), name='overmanning')
# Objectives
redundancy = so.Expression(numRedundant.sum('*', '*'), name='redundancy')
cost = so.Expression(so.expr_sum(redundancy_cost[w] * numRedundant[w, p] +
shorttime_cost[w] * numShortTime[w, p] +
overmanning_cost[w] * numExcess[w, p]
for w in WORKERS for p in PERIODS)
+ so.expr_sum(
retrain_cost.loc[i, j] * numRetrain[i, j, p]
for i, j in RETRAIN_PAIRS for p in PERIODS),
name='cost')
m.set_objective(redundancy, sense=so.MIN, name='redundancy_obj')
res = m.solve()
if res is not None:
print('Redundancy:', redundancy.get_value())
print('Cost:', cost.get_value())
print(so.get_solution_table(
numWorkers, numRecruits, numRedundant, numShortTime, numExcess))
print(so.get_solution_table(numRetrain))
print(so.get_solution_table(numDowngrade))
m.set_objective(cost, sense=so.MIN, name='cost_obj')
res = m.solve()
if res is not None:
print('Redundancy:', redundancy.get_value())
print('Cost:', cost.get_value())
print(so.get_solution_table(numWorkers, numRecruits, numRedundant,
numShortTime, numExcess))
print(so.get_solution_table(numRetrain))
print(so.get_solution_table(numDowngrade))
return m.get_objective_value()
Output¶
In [1]: import os
In [2]: hostname = os.getenv('CASHOST')
In [3]: port = os.getenv('CASPORT')
In [4]: from swat import CAS
In [5]: cas_conn = CAS(hostname, port)
In [6]: import sasoptpy
In [7]: from examples.client_side.manpower_planning import test
In [8]: test(cas_conn)
NOTE: Initialized model manpower_planning.
NOTE: Added action set 'optimization'.
NOTE: Converting model manpower_planning to OPTMODEL.
NOTE: Submitting OPTMODEL code to CAS server.
NOTE: Problem generation will use 8 threads.
NOTE: The problem has 63 variables (0 free, 3 fixed).
NOTE: The problem has 24 linear constraints (6 LE, 18 EQ, 0 GE, 0 range).
NOTE: The problem has 108 linear constraint coefficients.
NOTE: The problem has 0 nonlinear constraints (0 LE, 0 EQ, 0 GE, 0 range).
NOTE: The OPTMODEL presolver is disabled for linear problems.
NOTE: The LP presolver value AUTOMATIC is applied.
NOTE: The LP presolver time is 0.01 seconds.
NOTE: The LP presolver removed 36 variables and 12 constraints.
NOTE: The LP presolver removed 52 constraint coefficients.
NOTE: The presolved problem has 27 variables, 12 constraints, and 56 constraint coefficients.
NOTE: The LP solver is called.
NOTE: The Dual Simplex algorithm is used.
Objective
Phase Iteration Value Time
D 2 1 -1.032250E+03 0
P 2 17 8.417969E+02 0
NOTE: Optimal.
NOTE: Objective = 841.796875.
NOTE: The Dual Simplex solve time is 0.01 seconds.
NOTE: The output table 'SOLUTION' in caslib 'CASUSER(casuser)' has 63 rows and 6 columns.
NOTE: The output table 'DUAL' in caslib 'CASUSER(casuser)' has 24 rows and 4 columns.
NOTE: The CAS table 'solutionSummary' in caslib 'CASUSER(casuser)' has 13 rows and 4 columns.
NOTE: The CAS table 'problemSummary' in caslib 'CASUSER(casuser)' has 18 rows and 4 columns.
Redundancy: 841.796875
Cost: 1668750.0
numWorkers numRecruits numRedundant numShortTime numExcess
unskilled 0 2000.00000 NaN NaN NaN NaN
unskilled 1 1157.03125 0.0 442.968750 50.0 132.03125
unskilled 2 675.00000 0.0 166.328125 50.0 150.00000
unskilled 3 175.00000 0.0 232.500000 50.0 150.00000
semiskilled 0 1500.00000 NaN NaN NaN NaN
semiskilled 1 1442.96875 0.0 0.000000 50.0 17.96875
semiskilled 2 2025.00000 800.0 0.000000 50.0 0.00000
semiskilled 3 2500.00000 800.0 0.000000 0.0 0.00000
skilled 0 1000.00000 NaN NaN NaN NaN
skilled 1 1025.00000 0.0 0.000000 50.0 0.00000
skilled 2 1500.00000 500.0 0.000000 0.0 0.00000
skilled 3 2000.00000 500.0 0.000000 0.0 0.00000
numRetrain
(unskilled, semiskilled, 1) 200.000000
(unskilled, semiskilled, 2) 200.000000
(unskilled, semiskilled, 3) 200.000000
(semiskilled, skilled, 1) 256.250000
(semiskilled, skilled, 2) 262.276786
(semiskilled, skilled, 3) 364.285714
numDowngrade
(semiskilled, unskilled, 1) 0.000000e+00
(semiskilled, unskilled, 2) -1.421085e-14
(semiskilled, unskilled, 3) 0.000000e+00
(skilled, semiskilled, 1) 1.684375e+02
(skilled, semiskilled, 2) 1.729129e+02
(skilled, semiskilled, 3) 2.210714e+02
(skilled, unskilled, 1) 0.000000e+00
(skilled, unskilled, 2) 0.000000e+00
(skilled, unskilled, 3) 0.000000e+00
NOTE: Added action set 'optimization'.
NOTE: Converting model manpower_planning to OPTMODEL.
NOTE: Submitting OPTMODEL code to CAS server.
NOTE: Problem generation will use 8 threads.
NOTE: The problem has 63 variables (0 free, 3 fixed).
NOTE: The problem has 24 linear constraints (6 LE, 18 EQ, 0 GE, 0 range).
NOTE: The problem has 108 linear constraint coefficients.
NOTE: The problem has 0 nonlinear constraints (0 LE, 0 EQ, 0 GE, 0 range).
NOTE: The OPTMODEL presolver is disabled for linear problems.
NOTE: The LP presolver value AUTOMATIC is applied.
NOTE: The LP presolver time is 0.00 seconds.
NOTE: The LP presolver removed 38 variables and 13 constraints.
NOTE: The LP presolver removed 56 constraint coefficients.
NOTE: The presolved problem has 25 variables, 11 constraints, and 52 constraint coefficients.
NOTE: The LP solver is called.
NOTE: The Dual Simplex algorithm is used.
Objective
Phase Iteration Value Time
D 2 1 -4.018114E+04 0
D 2 6 4.986773E+05 0
NOTE: Optimal.
NOTE: Objective = 498677.28532.
NOTE: The Dual Simplex solve time is 0.01 seconds.
NOTE: The output table 'SOLUTION' in caslib 'CASUSER(casuser)' has 63 rows and 6 columns.
NOTE: The output table 'DUAL' in caslib 'CASUSER(casuser)' has 24 rows and 4 columns.
NOTE: The CAS table 'solutionSummary' in caslib 'CASUSER(casuser)' has 13 rows and 4 columns.
NOTE: The CAS table 'problemSummary' in caslib 'CASUSER(casuser)' has 18 rows and 4 columns.
Redundancy: 1423.7188365650968
Cost: 498677.2853185596
numWorkers numRecruits numRedundant numShortTime numExcess
unskilled 0 2000.0 NaN NaN NaN NaN
unskilled 1 1000.0 0.000000 812.500000 0.0 0.0
unskilled 2 500.0 0.000000 257.617729 0.0 0.0
unskilled 3 0.0 0.000000 353.601108 0.0 0.0
semiskilled 0 1500.0 NaN NaN NaN NaN
semiskilled 1 1400.0 0.000000 0.000000 0.0 0.0
semiskilled 2 2000.0 800.000000 0.000000 0.0 0.0
semiskilled 3 2500.0 800.000000 0.000000 0.0 0.0
skilled 0 1000.0 NaN NaN NaN NaN
skilled 1 1000.0 55.555556 0.000000 0.0 0.0
skilled 2 1500.0 500.000000 0.000000 0.0 0.0
skilled 3 2000.0 500.000000 0.000000 0.0 0.0
numRetrain
(unskilled, semiskilled, 1) 0.000000
(unskilled, semiskilled, 2) 142.382271
(unskilled, semiskilled, 3) 96.398892
(semiskilled, skilled, 1) 0.000000
(semiskilled, skilled, 2) 105.263158
(semiskilled, skilled, 3) 131.578947
numDowngrade
(semiskilled, unskilled, 1) 2.500000e+01
(semiskilled, unskilled, 2) 2.842171e-14
(semiskilled, unskilled, 3) -2.842171e-14
(skilled, semiskilled, 1) 0.000000e+00
(skilled, semiskilled, 2) 0.000000e+00
(skilled, semiskilled, 3) 0.000000e+00
(skilled, unskilled, 1) 0.000000e+00
(skilled, unskilled, 2) 1.136868e-13
(skilled, unskilled, 3) 1.421085e-14
Out[8]: 498677.2853185596