7

I am trying to create an optimal shift schedule where employees are assigned to shift times. The output should aim to spend the least amount of money. The tricky part is I need to account for specific constraints. These being:

1) At any given time period, you must meet the minimum staffing requirements
2) A person has a minimum and maximum amount of hours they can do
3) An employee can only be scheduled to work within their available hours
4) A person can only work one shift per day

The staff_availability df contains the employees to choose from ['Person'], the available min - max hours they can work ['MinHours']-['MaxHours'], how much they get paid ['HourlyWage'], and availability, expressed as hours ['Availability_Hr'] and 15min segments ['Availability_15min_Seg'].

Note: Available employees don't have to be assigned shifts if not required. They're just available to do so.

The staffing_requirements df contains the time of day ['Time'] and the staff required ['People'] during those periods.

The script returns a df 'availability_per_member' that displays how many employees are available at each point in time. So 1 indicates available to be scheduled and 0 indicates not available. It then aims to allocate shift times, while accounting for the constraints using pulp.

I am getting an output but the shift times aren't applied to employees consecutively.

I am not meeting the 4th constraint in that employees can only work one shift a day

import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as dates
import pulp

staffing_requirements = pd.DataFrame({
    'Time' : ['0/1/1900 8:00:00','0/1/1900 9:59:00','0/1/1900 10:00:00','0/1/1900 12:29:00','0/1/1900 12:30:00','0/1/1900 13:00:00','0/1/1900 13:02:00','0/1/1900 13:15:00','0/1/1900 13:20:00','0/1/1900 18:10:00','0/1/1900 18:15:00','0/1/1900 18:20:00','0/1/1900 18:25:00','0/1/1900 18:45:00','0/1/1900 18:50:00','0/1/1900 19:05:00','0/1/1900 19:07:00','0/1/1900 21:57:00','0/1/1900 22:00:00','0/1/1900 22:30:00','0/1/1900 22:35:00','1/1/1900 3:00:00','1/1/1900 3:05:00','1/1/1900 3:20:00','1/1/1900 3:25:00'],                 
    'People' : [1,1,2,2,3,3,2,2,3,3,4,4,3,3,2,2,3,3,4,4,3,3,2,2,1],                      
     })

staff_availability = pd.DataFrame({
    'Person' : ['C1','C2','C3','C4','C5','C6','C7','C8','C9','C10','C11'],                 
    'MinHours' : [3,3,3,3,3,3,3,3,3,3,3],    
    'MaxHours' : [10,10,10,10,10,10,10,10,10,10,10],                 
    'HourlyWage' : [26,26,26,26,26,26,26,26,26,26,26],  
    'Availability_Hr' : ['8-18','8-18','8-18','9-18','9-18','9-18','12-1','12-1','17-3','17-3','17-3'],                              
    'Availability_15min_Seg' : ['1-41','1-41','1-41','5-41','5-41','5-41','17-69','17-79','37-79','37-79','37-79'],                              
    })

staffing_requirements['Time'] = ['/'.join([str(int(x.split('/')[0])+1)] + x.split('/')[1:]) for x in staffing_requirements['Time']]
staffing_requirements['Time'] = pd.to_datetime(staffing_requirements['Time'], format='%d/%m/%Y %H:%M:%S')
formatter = dates.DateFormatter('%Y-%m-%d %H:%M:%S') 

# 15 Min
staffing_requirements = staffing_requirements.groupby(pd.Grouper(freq='15T',key='Time'))['People'].max().ffill()
staffing_requirements = staffing_requirements.reset_index(level=['Time'])

staffing_requirements.index = range(1, len(staffing_requirements) + 1) 

staff_availability.set_index('Person')

staff_costs = staff_availability.set_index('Person')[['MinHours', 'MaxHours', 'HourlyWage']]
availability = staff_availability.set_index('Person')[['Availability_15min_Seg']]
availability[['first_15min', 'last_15min']] =  availability['Availability_15min_Seg'].str.split('-', expand=True).astype(int)

availability_per_member =  [pd.DataFrame(1, columns=[idx], index=range(row['first_15min'], row['last_15min']+1))
 for idx, row in availability.iterrows()]

availability_per_member = pd.concat(availability_per_member, axis='columns').fillna(0).astype(int).stack()
availability_per_member.index.names = ['Timeslot', 'Person']
availability_per_member = (availability_per_member.to_frame()
                        .join(staff_costs[['HourlyWage']])
                        .rename(columns={0: 'Available'}))


''' Generate shift times based off availability  '''

prob = pulp.LpProblem('CreateStaffing', pulp.LpMinimize) # Minimize costs

timeslots = staffing_requirements.index
persons = availability_per_member.index.levels[1]

# A member is either staffed or is not at a certain timeslot
staffed = pulp.LpVariable.dicts("staffed",
                                   ((timeslot, staffmember) for timeslot, staffmember 
                                    in availability_per_member.index),
                                     lowBound=0,
                                     cat='Binary')

# Objective = cost (= sum of hourly wages)                              
prob += pulp.lpSum(
    [staffed[timeslot, staffmember] * availability_per_member.loc[(timeslot, staffmember), 'HourlyWage'] 
    for timeslot, staffmember in availability_per_member.index]
)

# Staff the right number of people
for timeslot in timeslots:
    prob += (sum([staffed[(timeslot, person)] for person in persons]) 
    == staffing_requirements.loc[timeslot, 'People'])

# Do not staff unavailable persons
for timeslot in timeslots:
    for person in persons:
        if availability_per_member.loc[(timeslot, person), 'Available'] == 0:
            prob += staffed[timeslot, person] == 0

# Do not underemploy people
for person in persons:
    prob += (sum([staffed[(timeslot, person)] for timeslot in timeslots])
    >= staff_costs.loc[person, 'MinHours']*4) # timeslot is 15 minutes => 4 timeslots = hour

# Do not overemploy people
for person in persons:
    prob += (sum([staffed[(timeslot, person)] for timeslot in timeslots])
    <= staff_costs.loc[person, 'MaxHours']*4) # timeslot is 15 minutes => 4 timeslots = hour


prob.solve()
print(pulp.LpStatus[prob.status])

output = []
for timeslot, staffmember in staffed:
    var_output = {
        'Timeslot': timeslot,
        'Staffmember': staffmember,
        'Staffed': staffed[(timeslot, staffmember)].varValue,
    }
    output.append(var_output)
output_df = pd.DataFrame.from_records(output)#.sort_values(['timeslot', 'staffmember'])
output_df.set_index(['Timeslot', 'Staffmember'], inplace=True)
if pulp.LpStatus[prob.status] == 'Optimal':
    print(output_df)

Below is an output for the first two hours (8 15 min time slots). The issue is the shifts aren't consecutive. The employees scheduled for the first 8 time slots are mainly different. I'd have 5 people starting within the first 2 hours. Employees should only work one shift per day.

   Timeslot   C
0         1  C2
1         2  C2
2         3  C1
3         4  C3
4         5  C6
5         6  C1
6         7  C5
7         8  C2
Community
  • 1
  • 1
jonboy
  • 556
  • 1
  • 9
  • 29
  • If you have a soft constraint (e.g. MinHours should be 5 but doesn't strictly have to be), I would recommend associating a cost and adding it to your objective function. – Thomas Boeck Apr 10 '19 at 03:17
  • Thanks @ThomasBoeck. Should I therefore include all constraints in the `objective`? – jonboy Apr 10 '19 at 03:20
  • Very cool question, I work in a similar field but more in the space of forecasting hours and filling skills gaps. I assume this is for a contact centre? do you have a github would love to see more of your code. – Umar.H Apr 22 '19 at 19:52
  • 1
    Not a contact centre. It's in hospitality. I don't have GitHub unfortunately. I've tried to determine a working option before sharing with other users. – jonboy Apr 24 '19 at 13:07
  • Very cool, did you find your solution? I've toyed with making something similar but don't have a use-case at the mo. – Umar.H Apr 26 '19 at 00:23
  • 1
    I haven't as yet. Assigning the right number of people was a simple fix. But getting the shift times applied to employees consecutively is giving me grief. I'm not meeting my 4th constraint effectively. – jonboy Apr 26 '19 at 00:58

2 Answers2

4

Note: This is an answer to an earlier version of the question.

I think the solution returned by the solver is correct; each person is working their MinHours, they're just not consecutive. I ran your code, then said 

for person in persons:
    print("{}: {}".format(person, sum([staffed[(timeslot, person)].value() for timeslot in timeslots])))

and got:

C1: 12.0
C2: 12.0
C3: 12.0
C4: 20.0
C5: 23.0
C6: 18.0
C7: 22.0
C8: 29.0
C9: 22.0
C10: 27.0
C11: 32.0

So everyone is working at least 12 shifts, i.e., 3 hours.

If you want the shifts to be consecutive (i.e., a person can't work slot 1 and then slot 3), then the typical way to handle this is to use a decision variable that says what time each employee starts their shift, rather than a variable that specifies every time period they are working. Then, introduce a parameter like a[j][t], which equals 1 if an employee who starts a shift at slot j is working in slot t. From there, you can calculate who is working during which slots.

The reason the problem is infeasible when you set MinHours to 5 is that it forces too many people to be working during certain hours. For example, 6 people have to complete their shifts before time slot 41. That means 6 x 4 x 5 = 120 person-slots need to be worked before slot 41. But only 97 person-slots are required between slots 1 and 41.

This problem can be fixed by changing the "Staff the right number of people" constraint to >= instead of ==, assuming that is allowable for the staffing system. (If it's not, then you just have an infeasible instance on your hands.)

(By the way -- you might be interested in the proposed new Stack Exchange site on Operations Research and Analytics. We'll be all over questions like this one over there. :-) )

LarrySnyder610
  • 2,127
  • 10
  • 21
  • Thanks @grendelsdad. This is helpful. I'll check out that site – jonboy Apr 14 '19 at 23:22
  • Good, glad it helps. If you think my answer is adequate, don’t forget to accept it. :) – LarrySnyder610 Apr 14 '19 at 23:32
  • No worries. I'll just see how I go first. It's very helpful but. I'll post there too. Thankyou – jonboy Apr 16 '19 at 11:56
  • I know it's been a while and this should be a separate question but I have a query about "Staffing the right number of people". I don't require every staff member to be allocated a shift. The availability df was determined to cover the every situation. I'm hoping to staff the minimum amount of people. for e.g if one person is required, just staff one person. I don't require everyone to get a shift. Does this make sense? – jonboy Oct 11 '19 at 03:12
2

Here's an answer to your revised question, i.e., how to add a constraint that requires each employee to work consecutive time periods.

I suggest that you add the following constraint (written here algebraically):

x[t+1,p] <= x[t,p] + (1 - (1/T) * sum_{s=1}^{t-1} x[s,p])    for all p, for all t < T

where x is your staffed variable (written here as x for compactness), t is the time index, T is the number of time periods, and p is the employee index.

The logic of the constraint is: If x[t,p] = 0 (the employee is not working in period t) and x[s,p] = 1 for any s < t (the employee was working in any prior period), then x[t+1,p] must = 0 (the employee cannot be working in period t+1. Thus, once the employee stops working, they can't re-start. Note that if x[t,p] = 1 or x[s,p] = 0 for every s < t, then x[t+1,p] can equal 1.

Here's my implementation of this constraint in pulp:

# If an employee works and then stops, they can't start again
num_slots = max(timeslots)
for timeslot in timeslots:
    if timeslot < num_slots:
        for person in persons:
            prob += staffed[timeslot+1, person] <= staffed[timeslot, person] + \
                (1 - (1./num_slots) *
                 sum([staffed[(s, person)] for s in timeslots if s < timeslot]))

I ran the model and got:

Optimal
                      Staffed
Timeslot Staffmember         
1        C2               1.0
2        C2               1.0
3        C2               1.0
4        C2               1.0
5        C2               1.0
6        C2               1.0
7        C2               1.0
8        C2               1.0
9        C2               1.0
         C6               1.0
10       C2               1.0
         C6               1.0
11       C2               1.0
         C6               1.0
12       C2               1.0
         C6               1.0
13       C3               1.0
         C6               1.0
14       C3               1.0
         C6               1.0

etc. So, employees are working in consecutive time periods.

Note that the new constraints slow down the model a bit. It still solves in <30 seconds or so. But if you are solving much larger instances, you might have to re-think the constraints.

LarrySnyder610
  • 2,127
  • 10
  • 21