User story: PTO accrual and balances

The rules (example)

• Default accrual: 15 days/year, prorated.
• After 5 years tenure: 20 days/year.
• After 10 years: 25 days/year.
• Max balance carried into a year: 30 days.

Compute

from datetime import date
import pandas as pd

emp = pd.read_excel("employees.xlsx")
leave = pd.read_excel("leave_taken.xlsx")   # employee_id, days_taken_ytd

today = pd.Timestamp(date.today())
emp["tenure_yrs"] = ((today - pd.to_datetime(emp["hire_date"])).dt.days / 365.25)

def annual_rate(years):
    if years >= 10: return 25
    if years >= 5:  return 20
    return 15

emp["annual_rate"] = emp["tenure_yrs"].apply(annual_rate)
ytd_fraction = (today.dayofyear / 365.25)
emp["accrued_ytd"] = (emp["annual_rate"] * ytd_fraction).round(1)

emp = emp.merge(leave, on="employee_id", how="left")
emp["days_taken_ytd"] = emp["days_taken_ytd"].fillna(0)
emp["balance"] = (emp["accrued_ytd"] - emp["days_taken_ytd"]).round(1)

emp["over_cap"] = emp["balance"] > 30
print(f"{emp['over_cap'].sum()} employees are over the 30-day cap.")

emp[["employee_id","name","tenure_yrs","annual_rate","accrued_ytd","days_taken_ytd","balance","over_cap"]].to_excel("pto_balances.xlsx", index=False)