User story: payroll prep

The data

ts = pd.read_csv("timesheets.csv", parse_dates=["date"])
# columns: employee_id, date, hours, project

Validate

# No more than 16 hours in a day
bad = ts.groupby(["employee_id","date"])["hours"].sum()
bad = bad[bad > 16]
if len(bad):
    print(f"⚠️  {len(bad)} employee-days over 16 hours")
    print(bad)

Aggregate to pay period (e.g., bi-weekly)

ts["pay_period"] = ts["date"].dt.to_period("2W-SUN")    # bi-weekly ending Sunday
daily = ts.groupby(["employee_id","date"])["hours"].sum().reset_index()

# Apply 40h/week overtime rule
daily["week"] = daily["date"].dt.to_period("W-SUN")
weekly = (daily.groupby(["employee_id","week"])["hours"]
                .sum()
                .reset_index())
weekly["regular_hrs"]  = weekly["hours"].clip(upper=40)
weekly["overtime_hrs"] = (weekly["hours"] - 40).clip(lower=0)

Roll up to pay period

weekly["pay_period"] = weekly["week"].apply(lambda w: pd.Period(w.start_time, freq="2W-SUN"))
payroll = (weekly.groupby(["employee_id","pay_period"])
                  .agg(regular = ("regular_hrs", "sum"),
                       overtime= ("overtime_hrs", "sum"))
                  .reset_index())

payroll["pay_period"] = payroll["pay_period"].astype(str)
payroll.to_csv("payroll_import.csv", index=False)
print(f"Wrote payroll for {payroll['employee_id'].nunique()} employees")