Results not coming out as expected. Have created a loop to go through it three times, but it still leaves lots of shifts as unallocated.
Using the below code, it does the job fairly well, but I want to figure out why its not grabbing the remaining shifts and attempting to create lines of work out of it.
Last time I ran it, it left approx. 57 shifts as “unallocated” but when I went through them manually, I was able to place most of the shifts in a line of work with other unallocated shifts.
import pandas as pd
import random
from collections import defaultdict
# Define parse_days function
def parse_days(days_string):
if pd.isna(days_string):
return []
return list(days_string.lower())
# Load Excel file
file_path = r'E:Header.xlsx'
sheet_name = input('Please type the Sheet Name to generate rosters from: ')
df = pd.read_excel(file_path, sheet_name=sheet_name)
# Mapping for days
day_map = {
'm': 'Monday',
'u': 'Tuesday',
'w': 'Wednesday',
't': 'Thursday',
'f': 'Friday',
'a': 'Saturday',
's': 'Sunday'
}
# Define line limits (easily editable)
MAX_MONDAY_FRIDAY_LINES = int(input('How many Monday-Friday Lines would you like? '))
MAX_MONDAY_SATURDAY_LINES = int(input('How many Monday-Saturday Lines would you like? '))
MAX_MONDAY_SUNDAY_LINES = int(input('How many Monday-Sunday Lines would you like? '))
MAX_SHIFTS = 5 # set to 5 for Full-Timers, 3 for Flexi's etc.
# Hour constraints
MAX_HOURS = 39.3333
MIN_HOURS = 37.0000
BUFFER_HOURS = 33.0000
MAX_START_TIME_DIFF = pd.Timedelta(minutes=90) # Max allowable start time difference between days within the same line - EBA states 90 minutes, this limits it to 1 hour. To change to 90 minutes, change to minutes=90
# Prepare data structure to store shifts
shifts = []
for idx, row in df.iterrows():
try:
days = parse_days(row['Days'])
except KeyError:
print(f"Warning: 'Days' column is missing or invalid in row {idx}. Skipping...")
continue
try:
start_time = (pd.Timestamp('1900-01-01') + row['ON 1']).time() # Convert timedelta to time
except KeyError:
print(f"Warning: StartTime not found in row {idx}. Skipping...")
continue
try:
hours = row['HRS WKD']
if isinstance(hours, pd.Timedelta):
hours = hours.total_seconds() / 3600 # Convert Timedelta to hours
except KeyError:
print(f"Warning: Hours not found in row {idx}. Skipping...")
continue
try:
shift_number = str(row['Shift']) # Ensure ShiftNumber is a string
except KeyError:
print(f"Warning: Shift not found in row {idx}. Skipping...")
continue
for day in days:
if day not in day_map:
print(f"Warning: Day '{day}' from row {idx} not found in day_map. Skipping...")
continue
shift = {
'ShiftNumber': shift_number,
'Day': day_map[day],
'StartTime': start_time,
'Hours': hours
}
shifts.append(shift)
# Shuffle shifts to randomize
random.shuffle(shifts)
# Prepare output structure
roster = defaultdict(lambda: defaultdict(list))
line_hours = defaultdict(float)
shift_count = defaultdict(int)
line_notes = defaultdict(str)
unallocated_shifts = []
# Define the different line categories
monday_friday_lines = 0
monday_saturday_lines = 0
monday_sunday_lines = 0
def can_allocate_shift(line, shift):
"""Check if a shift can be allocated to a line."""
if shift_count[line] >= MAX_SHIFTS or line_hours[line] + shift['Hours'] > MAX_HOURS:
return False
if shift['Day'] in roster[line] and any(s.get('Day') == shift['Day'] for s in roster[line][shift['Day']]):
return False
if line_notes[line] == 'Monday_Friday' and shift['Day'] in ['Saturday', 'Sunday']:
return False
if line_notes[line] == 'Monday_Saturday' and shift['Day'] == 'Sunday':
return False
# Ensure all shifts between Monday and Friday start within the allowed time difference
if shift['Day'] in ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday']:
start_time = pd.Timestamp.combine(pd.Timestamp('1900-01-01'), shift['StartTime'])
for existing_shifts in roster[line].values():
for existing_shift in existing_shifts:
existing_start_time = pd.Timestamp.combine(pd.Timestamp('1900-01-01'), existing_shift['StartTime'])
if abs(start_time - existing_start_time) > MAX_START_TIME_DIFF:
return False
return True
def allocate_shift(shift, max_lines, line_counter, line_note):
"""Attempt to allocate a shift to an appropriate line."""
global monday_friday_lines, monday_saturday_lines, monday_sunday_lines
for line, hours in sorted(line_hours.items(), key=lambda x: abs(x[1] + shift['Hours'] - (MAX_HOURS + MIN_HOURS) / 2)):
if line_notes[line] == line_note and can_allocate_shift(line, shift):
roster[line][shift['Day']].append({
'ShiftNumber': shift['ShiftNumber'],
'StartTime': shift['StartTime'],
'Hours': shift['Hours'],
'Day': shift['Day']
})
line_hours[line] += shift['Hours']
shift_count[line] += 1
return True
# If shift could not be added to any existing line, create a new line
if line_counter < max_lines:
new_line = max([key for key in roster.keys() if isinstance(key, int)], default=0) + 1
line_notes[new_line] = line_note
roster[new_line][shift['Day']] = [{
'ShiftNumber': shift['ShiftNumber'],
'StartTime': shift['StartTime'],
'Hours': shift['Hours'],
'Day': shift['Day']
}]
line_hours[new_line] += shift['Hours']
shift_count[new_line] += 1
if line_note == 'Monday_Friday':
monday_friday_lines += 1
elif line_note == 'Monday_Saturday':
monday_saturday_lines += 1
elif line_note == 'Monday_Sunday':
monday_sunday_lines += 1
return True
return False
# Process shifts to generate roster
for shift in shifts:
allocated = False
if shift['Day'] in ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday']:
allocated = allocate_shift(shift, MAX_MONDAY_FRIDAY_LINES, monday_friday_lines, 'Monday_Friday')
if not allocated and shift['Day'] in ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday']:
allocated = allocate_shift(shift, MAX_MONDAY_SATURDAY_LINES, monday_saturday_lines, 'Monday_Saturday')
if not allocated:
if not allocate_shift(shift, MAX_MONDAY_SUNDAY_LINES, monday_sunday_lines, 'Monday_Sunday'):
unallocated_shifts.append(shift)
# Attempt to reallocate unallocated shifts
def reallocate_unallocated_shifts():
"""Attempt to reallocate unallocated shifts into new lines."""
global unallocated_shifts, monday_friday_lines, monday_saturday_lines, monday_sunday_lines
remaining_shifts = []
for shift in unallocated_shifts:
allocated = False
if shift['Day'] in ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday']:
allocated = allocate_shift(shift, MAX_MONDAY_FRIDAY_LINES, monday_friday_lines, 'Monday_Friday')
if not allocated and shift['Day'] in ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday']:
allocated = allocate_shift(shift, MAX_MONDAY_SATURDAY_LINES, monday_saturday_lines, 'Monday_Saturday')
if not allocated:
allocated = allocate_shift(shift, MAX_MONDAY_SUNDAY_LINES, monday_sunday_lines, 'Monday_Sunday')
if not allocated:
remaining_shifts.append(shift)
unallocated_shifts = remaining_shifts
# Loop through reallocation 3 times
for _ in range(3):
reallocate_unallocated_shifts()
# Additional reallocation into lines with <= 3 shifts
def reallocate_into_lines_with_few_shifts():
"""Reallocate unallocated shifts into lines with 3 or fewer shifts if possible."""
global unallocated_shifts
remaining_shifts = []
for shift in unallocated_shifts:
allocated = False
for line in [line for line, count in shift_count.items() if count <= 3]:
if can_allocate_shift(line, shift):
roster[line][shift['Day']].append({
'ShiftNumber': shift['ShiftNumber'],
'StartTime': shift['StartTime'],
'Hours': shift['Hours'],
'Day': shift['Day']
})
line_hours[line] += shift['Hours']
shift_count[line] += 1
allocated = True
break
if not allocated:
remaining_shifts.append(shift)
unallocated_shifts = remaining_shifts
reallocate_into_lines_with_few_shifts()
# Ensure lines are within the required hour constraints
def enforce_hour_constraints():
"""Ensure lines are within the required hour constraints, marking those that need additional hours."""
global roster, line_hours, line_notes
for line, hours in line_hours.items():
if hours < MIN_HOURS and hours >= BUFFER_HOURS:
line_notes[line] += ' (TBA)'
enforce_hour_constraints()
# Check if the roster is empty
if not roster:
print("Roster is empty. No shifts were added.")
else:
print("Roster has been populated.")
# Prepare output DataFrame
rows = []
for line, days in roster.items():
line_data = {'Line': line, 'Monday': '', 'Tuesday': '', 'Wednesday': '', 'Thursday': '', 'Friday': '', 'Saturday': '', 'Sunday': '', 'Total Hours': '', 'Notes': ''}
for day, shifts in days.items():
shift_numbers = ', '.join([shift['ShiftNumber'] for shift in shifts])
line_data[day] = shift_numbers
line_data['Total Hours'] = line_hours[line]
line_data['Notes'] = line_notes.get(line, 'Unallocated')
rows.append(line_data)
# Add unallocated shifts to the DataFrame
for shift in unallocated_shifts:
unallocated_data = {'Line': 'Unallocated', 'Monday': '', 'Tuesday': '', 'Wednesday': '', 'Thursday': '', 'Friday': '', 'Saturday': '', 'Sunday': '', 'Total Hours': shift['Hours'], 'Notes': 'Unallocated'}
unallocated_data[shift['Day']] = shift['ShiftNumber']
rows.append(unallocated_data)
output = pd.DataFrame(rows, columns=['Line', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday', 'Total Hours', 'Notes'])
# Save to Excel
output_file = f'output_{sheet_name}_roster.xlsx' # Replace with your desired output file path
output.to_excel(output_file, index=False)
print(f'Roster generated and saved to {output_file}')
New contributor
Paul Gartner is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.