User story: monthly headcount report

The story

Tomas needs a monthly headcount file: active count by department on month-end, plus hires and leavers in the month, plus turnover %.

The data

import pandas as pd
emp = pd.read_excel("employees.xlsx")
# emp cols: employee_id, name, department, hire_date, term_date (NaT if active)
emp["hire_date"] = pd.to_datetime(emp["hire_date"])
emp["term_date"] = pd.to_datetime(emp["term_date"], errors="coerce")

Active as of any date

def active_as_of(df, asof):
    asof = pd.Timestamp(asof)
    return df[(df["hire_date"] <= asof) & ((df["term_date"].isna()) | (df["term_date"] > asof))]

eom = pd.Timestamp("2026-04-30")
active = active_as_of(emp, eom)
print(f"Headcount on {eom:%Y-%m-%d}: {len(active)}")

By department

hc_by_dept = active.groupby("department").size().rename("active").reset_index()
hc_by_dept = hc_by_dept.sort_values("active", ascending=False)

Hires and leavers in the month

month_start = pd.Timestamp("2026-04-01")
hires   = emp[(emp["hire_date"] >= month_start) & (emp["hire_date"] <= eom)]
leavers = emp[(emp["term_date"] >= month_start) & (emp["term_date"] <= eom)]

events = pd.DataFrame({
    "department": hc_by_dept["department"],
    "hires":   hires.groupby("department").size().reindex(hc_by_dept["department"], fill_value=0).values,
    "leavers": leavers.groupby("department").size().reindex(hc_by_dept["department"], fill_value=0).values,
})
events["turnover_pct"] = (events["leavers"] / hc_by_dept["active"] * 100).round(1)

summary = hc_by_dept.merge(events, on="department")
summary.loc["Total"] = [
    "Total", summary["active"].sum(), summary["hires"].sum(),
    summary["leavers"].sum(),
    round(summary["leavers"].sum() / summary["active"].sum() * 100, 1),
]
summary.to_excel(f"headcount_{eom:%Y-%m}.xlsx", index=False)