修复Google OR Tools手术排班代码的get_values弃用及后续错误

问题背景 #

复现加拿大外科期刊附录中的手术室时间优化Python代码时，遇到以下问题：

1. AttributeError：DataFrame对象无get_values属性（该方法自pandas 0.25.0起弃用）
2. TypeError：int与datetime.time类型无法执行+=操作
3. ZeroDivisionError：天数和病例数均为0，导致除法运算时分母为0

错误修复步骤 #

1. 替换弃用的get_values()方法 #

将所有get_values()调用替换为iloc[:, :].values.tolist()，确保pandas版本兼容性：

# 原代码
dfD = dfD.get_values()
# 替换为
dfD = dfD.iloc[:, :].values.tolist()

2. 修复数据读取与空值判断逻辑 #

原代码的空值判断不可靠，且日期列未处理为纯日期对象，导致有效数据未被正确加载：

# 导入datetime模块（需在顶部添加）
import datetime

# 替换原数据输入循环
for i in range(len(dfE)):
    # 确保所有字段非空
    if pd.notna(dfE[i]) and pd.notna(dfD[i]) and pd.notna(dfA[i]) and pd.notna(dfP[i]):
        # 提取日期部分，避免带时间的datetime对象比较问题
        day_date = dfD[i].date()
        if day_date not in days:
            days.append(day_date)
        rawExpectedTimes.append(dfE[i])
        rawDays.append(day_date)
        rawActualTimes.append(dfA[i])
        rawProcedures.append(dfP[i])
        if dfP[i] not in procedureTypes:
            procedureTypes.append(dfP[i])

3. 时间类型转分钟数 #

将datetime.time或Timedelta类型的时长数据转换为分钟数，解决数值运算错误：

# 在顶部导入datetime后，添加转换函数
def time_to_minutes(t):
    if isinstance(t, pd.Timedelta):
        return int(t.total_seconds() // 60)
    elif isinstance(t, datetime.time):
        return t.hour * 60 + t.minute
    elif isinstance(t, (int, float)):
        return int(t)
    else:
        return 0

# 读取数据后转换时长列
dfE = pd.DataFrame(data, columns=["Book Dur"])
dfE = dfE.iloc[:, :].values.tolist()
dfE = [time_to_minutes(x) for y in dfE for x in y]

dfA = pd.DataFrame(data, columns=["Actual Dur"])
dfA = dfA.iloc[:, :].values.tolist()
dfA = [time_to_minutes(x) for y in dfA for x in y]

4. 避免除零错误 #

在计算频率前判断数据量是否有效，防止除以0：

# 替换原统计输出部分
if count > 0:
    dashboard.at["Original overtime frequency", "B"] = round(currentOvertimeCount / count, 2)
    print("current overtime frequency: %.0f%%" % (100 * currentOvertimeCount / count))
    dashboard.at["Original undertime frequency", "B"] = round(currentUndertimeCount / count,2)
    print("current undertime frequency: %.0f%%" % (100 * currentUndertimeCount/ count))
    dashboard.at["Model overtime frequency", "B"] = round(solver.Value(overtimeCount) /count, 2)
    print("model overtime frequency: %.0f%%" % (100 * solver.Value(overtimeCount) / count))
    dashboard.at["Model undertime frequency", "B"] = round(solver.Value(undertimeCount) /count, 2)
    print("model undertime frequency: %.0f%%" % (100 * solver.Value(undertimeCount) / count))
else:
    print("No valid days found in input data.")

5. 修复其他细节错误 #

• 修正变量名拼写错误：overtimeBlocklength → overtimeBlockLength
• 修复格式化字符串错误："%/d" → "%d"
• 调整Excel输出位置：将dashboard.to_excel移到循环外，避免重复写入

完整修正代码 #

from __future__ import print_function
from ortools.sat.python import cp_model
import pandas as pd
import sys
import math
import datetime


# Parameters
targetOvertimeFrequency = 0.2
targetUndertimeFrequency = 0
undertimeCostWeight = 1
overtimeCostWeight = 1

arguments = sys.argv
excelInputFile = 'input.xlsx'
excelOutputFile = 'output.xlsx'

overtimeBlockLength = 15 # length of overtime blocks in minutes
noOfNursesPerBlock = 2.5 # number of nurses per overtime block
overtimeSalary = 75 # pay by block for overtime nurses

# Optional: get excel filenames from command line argument
if (len(arguments) > 1):
    excelInputFile = str(arguments[1])
    excelOutputFile = str(arguments[2])


# Read data from an excel file
data = pd.read_excel(r'' + excelInputFile, header=4)

# 时间转换函数
def time_to_minutes(t):
    if isinstance(t, pd.Timedelta):
        return int(t.total_seconds() // 60)
    elif isinstance(t, datetime.time):
        return t.hour * 60 + t.minute
    elif isinstance(t, (int, float)):
        return int(t)
    else:
        return 0

# Input data from excel sheet columns
dfP = pd.DataFrame(data, columns=["Procedure"])
dfP = dfP.iloc[:, :].values.tolist()
dfP = [x for y in dfP for x in y]

dfD = pd.DataFrame(data, columns=["Date"])
dfD = dfD.iloc[:, :].values.tolist()
dfD = [x for y in dfD for x in y]

dfE = pd.DataFrame(data, columns=["Book Dur"])
dfE = dfE.iloc[:, :].values.tolist()
dfE = [time_to_minutes(x) for y in dfE for x in y]

dfA = pd.DataFrame(data, columns=["Actual Dur"])
dfA = dfA.iloc[:, :].values.tolist()
dfA = [time_to_minutes(x) for y in dfA for x in y]

rawProcedures = [] # a list of each procedure performed
procedureTypes = [] # a list of all theprocedure codes
rawDays = [] # a list of dates for each procedure performed
days = [] # a list of the dates in the data
rawExpectedTimes = [] # a list of scheduled times for each procedure performed
rawActualTimes = [] # a list of actual times for each procedure performed

# Input data into arrays
for i in range(len(dfE)):
    if pd.notna(dfE[i]) and pd.notna(dfD[i]) and pd.notna(dfA[i]) and pd.notna(dfP[i]):
        day_date = dfD[i].date()
        if day_date not in days:
            days.append(day_date)
        rawExpectedTimes.append(dfE[i])
        rawDays.append(day_date)
        rawActualTimes.append(dfA[i])
        rawProcedures.append(dfP[i])
        if dfP[i] not in procedureTypes:
            procedureTypes.append(dfP[i])

procedureTypes.sort() # sort procedure types alphabetically

proceduresPerDay = {w: [] for w in days} # a map from a day to a list of procedures performed on that day
for i in range(len(rawDays)):
    proceduresPerDay[rawDays[i]].append(rawProcedures[i])


expectedTimes = {w: [] for w in days} # a map from a day to a list of booking times for that day
for i in range(len(rawDays)):
    expectedTimes[rawDays[i]].append(rawExpectedTimes[i])

actualTimes = {w: [] for w in days} # a map from a day to a list of actual procedure times for that day
for i in range(len(rawDays)):
    actualTimes[rawDays[i]].append(rawActualTimes[i])

# Pre-processing
originalBookTimes = {p: 0 for p in procedureTypes} # a map with average booking times for each procedue
for p in procedureTypes:
    counter = 0
    for i in range(len(rawDays)):
        if p == rawProcedures[i]:
            counter +=  1
            originalBookTimes[p] += rawExpectedTimes[i]
    if counter > 0:
        originalBookTimes[p] = int(originalBookTimes[p]/counter)
    else:
        originalBookTimes[p] = 0

expectedTotalTime = {} # a map from a day to the sum of booking times for that day
for day in days:
    expectedTotalTime[day] = 0
    for time in expectedTimes[day]:
        expectedTotalTime[day] += time

actualTotalTime = {} # a map from a day to the sum of actual procedure times for that day
for day in days:
    actualTotalTime[day] = 0
    for time in actualTimes[day]:
        actualTotalTime[day] += time

currentOvertimeCount = 0 # counts how many days the room went overtime
currentUndertimeCount = 0 # counts how many days the room went undertime
for day in days:
    if expectedTotalTime[day] < actualTotalTime[day]:
        currentOvertimeCount += 1
    elif actualTotalTime[day] + 15 < expectedTotalTime[day]:
        currentUndertimeCount +=  1

minProcedureTime = {p: 1440 for p in procedureTypes} # a map from a procedure to its minimum case time
maxProcedureTime = {p: 0 for p in procedureTypes} # a map from a procedure to its maximum case time

for i in range(len(rawDays)):
    if rawActualTimes[i] < minProcedureTime[rawProcedures[i]]:
        minProcedureTime[rawProcedures[i]] = rawActualTimes[i]
    if rawActualTimes[i] > maxProcedureTime[rawProcedures[i]]:
        maxProcedureTime[rawProcedures[i]] = rawActualTimes[i]
        
count = len(days) # number of days
 
# Model
print("Starting model optimisation", file=sys.stderr)

# Decision variables
model = cp_model.CpModel() # Create the model

procedureSchedulingTimes = {} # Create a decision variable for the scheduling time of each procedure type
for p in procedureTypes:
    procedureSchedulingTimes[p] = model.NewIntVar(int(minProcedureTime[p]),
     int(maxProcedureTime[p]),
                                "procedureSchedulingTime" + str(p))

totalScheduledTime = {} # The total time scheduled for each day
overtimeTriggers={} # Set to 1 if overtime for each day
undertimeTriggers = {} # Set to 1 if undertime for each day
for day in days:
    totalScheduledTime[day] = model.NewIntVar(0, 1000, "day"+ str(day))
    overtimeTriggers[day] = model.NewIntVar(0, 1, "overtimeTrigger" + str(day))
    undertimeTriggers[day] = model.NewIntVar(0, 1, "undertimeTrigger" + str(day))

overtimeCount = model.NewIntVar(0, 1000, "overtimeCount") # Number of days that went overtime
undertimeCount = model.NewIntVar(0, 1000, "undertimeCount") # Number of days that went undertime
overtimeCost = model.NewIntVar(-1000, 1000, "overtimeCost") # Overtime cost
undertimeCost = model.NewIntVar(-1000, 1000, "undertimeCost") # Undertime cost
absOvertimeCost = model.NewIntVar(0, 100, "absOvertimeCost") # absolute overtime cost
absUndertimeCost = model.NewIntVar(0, 100, "absUndertimeCost") # absolute undertime cost
finalCost = model.NewIntVar(0, 100, "finalCost") # final cost is sum of overtime and undertime costs

# Constraints
for day in days:
    # set totalScheduledTime to the sum of scheduled time in a day
    model.Add(totalScheduledTime[day] == (sum(procedureSchedulingTimes[i] for i in proceduresPerDay[day])))

    # set overtime trigger to 1 if overtime
    model.Add(1000 * overtimeTriggers[day] >= int(actualTotalTime[day]) - totalScheduledTime[day] - 1)
 
    # set overtime trigger to 0 if not overtime
    model.Add(1500 * (1 - overtimeTriggers[day]) >= totalScheduledTime[day] - int(actualTotalTime[day]))

    # set undertime trigger to 1 if undertime
    model.Add(1000 * undertimeTriggers[day] >= totalScheduledTime[day] - 15 - int(actualTotalTime[day]) - 1)
    # set undertime trigger to 0 if not undertime
    model.Add(1000 * (1 - undertimeTriggers[day]) >= int(actualTotalTime[day]) - totalScheduledTime[day] + 15)

model.Add(overtimeCount == (sum(overtimeTriggers[day] for day in days))) # count how many days went overtime
model.Add(undertimeCount == (sum(undertimeTriggers[day] for day in days))) # count how many days went undertime

model.Add((overtimeCost == overtimeCount - int(count * targetOvertimeFrequency))) #calculate overtime cost
model.Add((undertimeCost == undertimeCount - int(count * targetUndertimeFrequency))) #calculate undertime cost

# calculate absolute value of overtime cost
model.AddAbsEquality(absOvertimeCost, int(overtimeCostWeight) * overtimeCost)
# calculate absolute value of undertime cost
model.AddAbsEquality(absUndertimeCost, int(undertimeCostWeight) * undertimeCost)
model.Add((finalCost == overtimeCost + undertimeCost)) # calculate final cost

model.Minimize(finalCost) # Objective is to minimize final cost

solver= cp_model.CpSolver()
print("Starting to solve", file=sys.stderr)
status = solver.Solve(model)

# Print output to console and excel sheet
if status == cp_model.OPTIMAL or status == cp_model.FEASIBLE:
    print("Success!")
    rows= ["Number of days","Number of cases","Original overtime frequency","Original undertime frequency","Model overtime frequency",
            "Model undertime frequency","Model cases achieved", "Original Overtime Minutes Used","Model Overtime Minutes Used",
            "Original Overtime Cost","Model Overtime Cost", "Original OR minutes used", "Model OR minutes used", "","Procedure Type"] + procedureTypes

    dashboard= pd.DataFrame(index=rows,columns=["A", "B", "C",])

    for r in rows:
        dashboard.at[r,"A"]= r

    # Output basic stats
    dashboard.at["", ""] = ""
    dashboard.at["Number of days", "B"] = count
    print("number of days: ", count)
    dashboard.at["Number of cases", "B"] = len(rawExpectedTimes)
    print("number of cases: ", len(rawExpectedTimes))

    if count > 0:
        dashboard.at["Original overtime frequency", "B"] = round(currentOvertimeCount / count, 2)
        print("current overtime frequency: %.0f%%" % (100 * currentOvertimeCount / count))
        dashboard.at["Original undertime frequency", "B"] = round(currentUndertimeCount / count,2)
        print("current undertime frequency: %.0f%%" % (100 * currentUndertimeCount/ count))
        dashboard.at["Model overtime frequency", "B"] = round(solver.Value(overtimeCount) /count, 2)
        print("model overtime frequency: %.0f%%" % (100 * solver.Value(overtimeCount) / count))
        dashboard.at["Model undertime frequency", "B"] = round(solver.Value(undertimeCount) /count, 2)
        print("model undertime frequency: %.0f%%" % (100 * solver.Value(undertimeCount) / count))
    else:
        print("No valid days found in input data.")

    # Output overtime minutes and overtime cost
    originalOverMinutes = 0
    modelOverMinutes = 0

    originalCost = 0
    modelCost = 0

    for day in days:
        if solver.Value(totalScheduledTime[day]) < actualTotalTime[day]:
            modelOverMinutes += actualTotalTime[day] - solver.Value(totalScheduledTime[day])
            modelCost += math.ceil((actualTotalTime[day] - solver.Value(totalScheduledTime[day])) / overtimeBlockLength) * noOfNursesPerBlock * overtimeSalary
        if expectedTotalTime[day] < actualTotalTime[day]:
            originalOverMinutes += actualTotalTime[day] - expectedTotalTime[day]
            originalCost += math.ceil((actualTotalTime[day] - expectedTotalTime[day]) / overtimeBlockLength) * noOfNursesPerBlock * overtimeSalary

    print("Original overtime minutes used: ", originalOverMinutes)
    dashboard.at["Original Overtime Minutes Used", "B"] = originalOverMinutes
    print("Model