Determine workload from ITMO study plan

main.py

import pandas as pd
from scipy.optimize import minimize

df = pd.read_excel("study_plan.xlsx", skiprows=8)

total = df.iloc[-1].dropna().iloc[1:]
total.rename(
    {
        "Unnamed: 7": "Трудоемкость в з.ед.",
        "Unnamed: 8": "Трудоемкость в час.",
    },
    inplace=True,
    errors="raise",
)

workload_total = total.filter(regex="^Трудоемкость")
points_per_sem = total.filter(regex="семестр$")
points_per_sem = points_per_sem.where(points_per_sem > 0).dropna()

hours_per_point = (
    workload_total.loc["Трудоемкость в час."]
    / workload_total.loc["Трудоемкость в з.ед."]
)

hours_per_sem = points_per_sem.map(lambda x: x * hours_per_point)
hours_per_sem.rename("Час./сем.", inplace=True)

print(
    hours_per_sem.to_markdown(
        colalign=("center", "center"),
        tablefmt="rounded_grid",
    )
)

hours_median = hours_per_sem.median()
print(f":> Медиана: {hours_median} часов")

WEEKS_PER_SEM = 16
ALLOC_HRS_PER_DAY_MAX = 15
ALLOC_DAYS_PER_WEEK_MAX = 7


def constraint(var):
    var_h, var_d = var
    return var_h * var_d - (hours_median / WEEKS_PER_SEM)


def goal(var):
    var_h, var_d = var
    return var_h * var_d


sol = minimize(
    goal,
    [ALLOC_HRS_PER_DAY_MAX, ALLOC_DAYS_PER_WEEK_MAX],
    constraints=[{"type": "ineq", "fun": constraint}],
    bounds=[
        (0, ALLOC_HRS_PER_DAY_MAX),
        (0, ALLOC_DAYS_PER_WEEK_MAX),
    ],
)

print(f"\n:> {sol.message}")

if sol.success:
    alloc_hrs, alloc_days = sol.x
    print(f":> Результат: {alloc_days:1.2f} дней в неделю по {alloc_hrs:1.2f} часов")

    TOTAL_TIME = ALLOC_HRS_PER_DAY_MAX * ALLOC_DAYS_PER_WEEK_MAX
    total_study = alloc_hrs * alloc_days
    remainder = TOTAL_TIME - total_study

    print(f":> Остаток: {remainder:1.2f} часов в неделю")

Pipfile

[[source]]
url = "https://pypi.org/simple"
verify_ssl = true
name = "pypi"

[packages]
pandas = "2.0.3"
openpyxl = "3.1.2"
tabulate = "0.9.0"
scipy = "1.10.1"

[dev-packages]