Session 13 lab: Is workforce change creating a capacity problem?¶

# HR data setup (synthetic). Run this cell first.
# Downloads the course's synthetic workforce data from GitHub and checks it is the exact version this course uses.
import hashlib
import io
import os
import urllib.request

import pandas as pd

HR_URL = "https://raw.githubusercontent.com/Aayush181509/AnalyticsLecture/main/data/synthetic/hr-v1/"
HR_SHA256 = {
    "employees.csv": "7d59a66675d53b7d6de8cce6a4d3369ddd6268e51913e86f30e9560c019342a9",
    "department_history.csv": "12e6524a7838e9d5426c2bef116769ad850d5eaf11e10958a4e4ae13d21dca36",
    "recruitment.csv": "3a6c2046fd5cbb4a07d435f50e3d10e5df160314decd1e651718a5d0bd7fbc05",
}
HR_DATES = {
    "employees.csv": ["hire_date", "exit_date"],
    "department_history.csv": ["effective_from", "effective_to"],
    "recruitment.csv": ["opened_date", "offer_accepted_date", "hire_date"],
}
HR_DIR = os.environ.get("COURSE_HR_DIR", "")
CUTOFF = pd.Timestamp("2025-12-31")


def load_hr(name):
    """Load one hr-v1 file: 'employees.csv', 'department_history.csv' or 'recruitment.csv'."""
    if HR_DIR:
        with open(os.path.join(HR_DIR, name), "rb") as f:
            data = f.read()
    else:
        data = urllib.request.urlopen(HR_URL + name).read()
    if hashlib.sha256(data).hexdigest() != HR_SHA256[name]:
        print("WARNING: this file differs from the course version. Ask your facilitator.")
    return pd.read_csv(io.BytesIO(data), parse_dates=HR_DATES[name])


print("HR setup complete.")

HR setup complete.

import datetime

import matplotlib.pyplot as plt
import numpy as np

ONE_DAY = datetime.timedelta(days=1)

pd.set_option("display.float_format", "{:,.2f}".format)
plt.rcParams.update({
    "figure.figsize": (8, 4),
    "axes.spines.top": False,
    "axes.spines.right": False,
    "axes.grid": True,
    "axes.axisbelow": True,
    "grid.color": "#e6e5e1",
    "axes.prop_cycle": plt.cycler(color=["#2a78d6", "#eb6834", "#1baf7a"]),
    "axes.titleweight": "bold",
    "axes.titlelocation": "left",
})

employees = load_hr("employees.csv")
history = load_hr("department_history.csv")
requisitions = load_hr("recruitment.csv")
print("Employees:", len(employees), "| department intervals:", len(history), "| requisitions:", len(requisitions))

Employees: 520 | department intervals: 543 | requisitions: 160

checks = {
    "employees: employee_id is unique": employees["employee_id"].is_unique,
    "history: (employee_id, effective_from) is unique": not history.duplicated(["employee_id", "effective_from"]).any(),
    "requisitions: requisition_id is unique": requisitions["requisition_id"].is_unique,
    "history employees all exist in employees": history["employee_id"].isin(employees["employee_id"]).all(),
    "exit after hire, when there is an exit": (employees["exit_date"].isna()
                                               | (employees["exit_date"] > employees["hire_date"])).all(),
    "nothing dated after the cutoff": (employees[["hire_date", "exit_date"]].max() <= CUTOFF).all(),
}
for name, ok in checks.items():
    print("PASS" if ok else "FAIL", "-", name)

print()
print("Employees hired before 2025:", (employees["hire_date"] < "2025-01-01").sum())
print("Employees with an exit date:", employees["exit_date"].notna().sum())

PASS - employees: employee_id is unique
PASS - history: (employee_id, effective_from) is unique
PASS - requisitions: requisition_id is unique
PASS - history employees all exist in employees
PASS - exit after hire, when there is an exit
PASS - nothing dated after the cutoff

Employees hired before 2025: 379
Employees with an exit date: 131

def is_active(table, day, start="hire_date", end="exit_date"):
    """True for each row whose interval [start, end) contains `day`. A blank end means still open."""
    day = pd.Timestamp(day)
    return (table[start] <= day) & (table[end].isna() | (table[end] > day))


def active_on(table, day, start="hire_date", end="exit_date"):
    """How many rows are active on `day`."""
    return int(is_active(table, day, start, end).sum())

# 1. Run the cell. Which of the five made-up employees are active on 2025-03-31?
# 2. Change day to "2025-04-01". Who drops out, and why?
# 3. Change day to "2025-03-01". Is C active? Is B? Explain using the rule above.
day = "2025-03-31"

fixture = pd.DataFrame({
    "employee_id": ["A", "B", "C", "D", "E"],
    "hire_date": pd.to_datetime(["2024-06-10", "2025-03-01", "2023-02-13", "2022-09-05", "2021-11-22"]),
    "exit_date": pd.to_datetime([None, None, "2025-03-01", "2025-03-17", "2025-04-01"]),
})
fixture.assign(active=is_active(fixture, day))

def month_bridge(table, month_start):
    """Opening, hires, exits and closing for the month that starts on `month_start`."""
    start = pd.Timestamp(month_start)
    end = start + pd.offsets.MonthEnd(0)                       # last day of the month
    return {
        "opening": active_on(table, start - ONE_DAY),  # active on the last day of the previous month
        "hires": int(table["hire_date"].between(start, end).sum()),
        "exits": int(table["exit_date"].between(start, end).sum()),
        "closing": active_on(table, end),                     # active on the last day of this month
    }


months = pd.date_range("2025-01-01", "2025-12-01", freq="MS")

bridge = pd.DataFrame([month_bridge(employees, m) for m in months], index=months.strftime("%Y-%m"))
bridge.index.name = "month"

# Two checks that must always pass.
assert (bridge["closing"] == bridge["opening"] + bridge["hires"] - bridge["exits"]).all(), "bridge does not balance"
assert (bridge["opening"].iloc[1:].to_numpy() == bridge["closing"].iloc[:-1].to_numpy()).all(), "months do not chain"
print("Bridge balances in all 12 months, and each closing equals the next opening.")
bridge

Bridge balances in all 12 months, and each closing equals the next opening.

# 1. Run the cell. It lists who joined and who left in the chosen month.
# 2. Change month to "2025-06". Why is this month unusual?
# 3. Find an exit dated on the 1st of a month (try "2025-07"). Is that person in the month's opening? Why?
month = "2025-11"

start = pd.Timestamp(month + "-01")
end = start + pd.offsets.MonthEnd(0)
joined = employees[employees["hire_date"].between(start, end)]
left = employees[employees["exit_date"].between(start, end)]
print(bridge.loc[month].to_dict())
print(f"Hires listed: {len(joined)} | exits listed: {len(left)}")
left.sort_values("exit_date").head(10)

{'opening': 404, 'hires': 9, 'exits': 21, 'closing': 392}
Hires listed: 9 | exits listed: 21

bridge["avg_headcount"] = (bridge["opening"] + bridge["closing"]) / 2
bridge["turnover"] = (bridge["exits"] / bridge["avg_headcount"]).where(bridge["avg_headcount"] > 0)
annual_exits = bridge["exits"].sum()
annual_turnover = annual_exits / bridge["avg_headcount"].mean()

fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(9, 6), sharex=True)
x = np.arange(12)
ax1.plot(x, bridge["closing"], marker="o")
ax1.set_title("Closing headcount, 2025")
ax1.set_ylabel("Employees")
ax2.bar(x, bridge["turnover"] * 100, color="#eb6834")
ax2.set_title("Monthly turnover, 2025")
ax2.set_ylabel("Exits as % of avg headcount")
ax2.set_xticks(x, bridge.index, rotation=45)
ax2.set_xlabel("Month")
fig.tight_layout()
plt.show()

print(f"Annual exits: {annual_exits} | annual turnover: {annual_turnover:.1%}")
print(f"Wrong, ÷ December closing headcount: {annual_exits / bridge['closing'].iloc[-1]:.1%}")
print(f"Wrong, mean of monthly rates:        {bridge['turnover'].mean():.1%}  (a monthly figure)")

Annual exits: 131 | annual turnover: 33.6%
Wrong, ÷ December closing headcount: 33.7%
Wrong, mean of monthly rates:        2.8%  (a monthly figure)

def add_interval_flags(history):
    """Mark each employee's first and last department interval."""
    h = history.sort_values(["employee_id", "effective_from"]).copy()
    h["first_interval"] = ~h.duplicated("employee_id", keep="first")
    h["last_interval"] = ~h.duplicated("employee_id", keep="last")
    return h


def department_month(h, department, month_start):
    """Department bridge for one month: a transfer is neither a hire nor an exit."""
    d = h[h["department"] == department]
    start = pd.Timestamp(month_start)
    end = start + pd.offsets.MonthEnd(0)
    starts = d["effective_from"].between(start, end)
    ends = d["effective_to"].between(start, end)
    return {
        "opening": active_on(d, start - ONE_DAY, "effective_from", "effective_to"),
        "hires": int((starts & d["first_interval"]).sum()),
        "transfers_in": int((starts & ~d["first_interval"]).sum()),
        "exits": int((ends & d["last_interval"]).sum()),
        "transfers_out": int((ends & ~d["last_interval"]).sum()),
        "closing": active_on(d, end, "effective_from", "effective_to"),
    }


def department_bridge(h, department):
    """Monthly 2025 bridge for one department, checked to balance."""
    b = pd.DataFrame([department_month(h, department, m) for m in months], index=months.strftime("%Y-%m"))
    b.index.name = "month"
    expected = b["opening"] + b["hires"] + b["transfers_in"] - b["exits"] - b["transfers_out"]
    assert (b["closing"] == expected).all(), f"{department} bridge does not balance"
    return b


history = add_interval_flags(history)
departments = sorted(history["department"].unique())
dept_bridges = {d: department_bridge(history, d) for d in departments}
print("Built and checked bridges for:", ", ".join(departments))

Built and checked bridges for: Finance and Admin, Marketing, Operations, People, Sales, Technology

# 1. Run the cell. For which departments does "÷ current" overstate turnover, and for which does it understate it?
# 2. Link each gap to the department's headcount path: did it grow or shrink during 2025?
# 3. Change MIN_COUNT to 10. Which departments are now flagged? Should a flagged department be ranked?
MIN_COUNT = 5

rows = {}
for d, b in dept_bridges.items():
    avg = ((b["opening"] + b["closing"]) / 2).mean()
    rows[d] = {"Jan opening": b["opening"].iloc[0], "Dec closing": b["closing"].iloc[-1],
               "exits": b["exits"].sum(), "avg headcount": round(avg, 1),
               "correct": b["exits"].sum() / avg, "÷ current": b["exits"].sum() / b["closing"].iloc[-1]}
dept = pd.DataFrame(rows).T.astype({"Jan opening": int, "Dec closing": int, "exits": int})
dept["flag"] = np.where(dept["exits"] < MIN_COUNT, "small: do not rank", "")
dept.sort_values("correct", ascending=False).assign(
    correct=lambda t: t["correct"].map("{:.1%}".format),
    **{"÷ current": lambda t: t["÷ current"].map("{:.1%}".format)},
)

# 1. Run the cell. Does the bridge balance every month? How many people moved in and out during 2025?
# 2. Change department to "People", then "Sales". Which department grew mostly through transfers?
# 3. Explain in one sentence why the transfers columns are needed for a department but not for the company.
department = "Technology"

b = dept_bridges[department]
print(f"{department}: hires {b['hires'].sum()}, transfers in {b['transfers_in'].sum()}, "
      f"exits {b['exits'].sum()}, transfers out {b['transfers_out'].sum()}")
b

Technology: hires 34, transfers in 4, exits 13, transfers out 6

flows = ["opening", "hires", "transfers_in", "exits", "transfers_out", "closing"]
total = sum(b[flows] for b in dept_bridges.values())
print("Transfers cancel across departments:", (total["transfers_in"] == total["transfers_out"]).all())
for col in ["opening", "hires", "exits", "closing"]:
    print(f"Department {col} add up to the company:", (total[col] == bridge[col]).all())

Transfers cancel across departments: True
Department opening add up to the company: True
Department hires add up to the company: True
Department exits add up to the company: True
Department closing add up to the company: True

# 1. Run the cell. Describe the shape of the tenure distribution. Is the mean or the median a better summary?
# 2. Change group_by to "department". Which group has the newest workforce? Is any group too small to compare?
# 3. Change who to "leavers". The cell then shows completed service. Why must the two never be mixed?
group_by = "job_family"
who = "active"

if who == "active":
    people = employees[is_active(employees, CUTOFF)].copy()
    people["days"] = (CUTOFF - people["hire_date"]).dt.days
    label = "Tenure at the cutoff, active employees"
else:
    people = employees[employees["exit_date"].notna()].copy()
    people["days"] = (people["exit_date"] - people["hire_date"]).dt.days
    label = "Completed service, leavers"
people["years"] = people["days"] / 365.25
if group_by == "department":
    last = history[history["last_interval"]].set_index("employee_id")["department"]
    people["department"] = people["employee_id"].map(last)

ax = plt.gca()
ax.hist(people["years"], bins=np.arange(0, 11.5, 0.5))
ax.set_title(label)
ax.set_xlabel("Years (approx., days ÷ 365.25)")
ax.set_ylabel("People")
plt.show()

people.groupby(group_by)["years"].agg(["count", "median", "mean"]).round(1)

req = requisitions.copy()
req["days_to_fill"] = (req["offer_accepted_date"] - req["opened_date"]).dt.days
req["open_age_days"] = (CUTOFF - req["opened_date"]).dt.days.where(req["status"] == "open")
print(req["status"].value_counts().to_string())

status
filled      143
canceled      8
open          5
accepted      4

# 1. Run the cell. Which group is slowest to fill roles? Which has roles open the longest?
# 2. Change group_by to "job_family". Does the picture change?
# 3. Look at the "accepted" column. Which medians rest on so few roles that you would not compare them?
group_by = "department"

summary = req.groupby(group_by).agg(
    accepted=("days_to_fill", "count"),
    median_days_to_fill=("days_to_fill", "median"),
    open_roles=("open_age_days", "count"),
    oldest_open_age=("open_age_days", "max"),
    canceled=("status", lambda s: int((s == "canceled").sum())),
)
display(summary)
print("Roles still open at the cutoff:")
req.loc[req["status"] == "open", ["requisition_id", group_by, "opened_date", "open_age_days"]]

Roles still open at the cutoff:

# 1. Run the cell. Which hire quarter kept the smallest share of its starters for 90 days?
# 2. Change department to "All". Is the pattern specific to one department?
# 3. Change follow_up_days to 180. Why does one cohort shrink to only a few starters? Can you still compare it fairly?
department = "Operations"
follow_up_days = 90

first_dept = history[history["first_interval"]].set_index("employee_id")["department"]
hires = employees[employees["hire_date"] >= "2025-01-01"].copy()
hires["department"] = hires["employee_id"].map(first_dept)
if department != "All":
    hires = hires[hires["department"] == department]
hires["check_day"] = hires["hire_date"] + datetime.timedelta(days=follow_up_days)
hires = hires[hires["check_day"] <= CUTOFF].copy()      # only hires we can follow long enough
hires["stayed"] = hires["exit_date"].isna() | (hires["exit_date"] > hires["check_day"])
hires["hire_quarter"] = hires["hire_date"].dt.to_period("Q").astype(str)

hires.groupby("hire_quarter").agg(starters=("stayed", "size"), stayed=("stayed", "sum"),
                                  share_stayed=("stayed", "mean")).round(2)

def department_turnover(b):
    """Annual turnover for one department bridge."""
    return b["exits"].sum() / b["closing"].iloc[-1]          # FIX ME


result = pd.Series({d: department_turnover(b) for d, b in dept_bridges.items()})
reference = pd.Series({d: b["exits"].sum() / ((b["opening"] + b["closing"]) / 2).mean()
                       for d, b in dept_bridges.items()})
print("CHECK:", "PASS" if np.allclose(result, reference) else "FAIL - not the contract's denominator")
result.map("{:.1%}".format).to_frame("turnover")

CHECK: FAIL - not the contract's denominator

def active_count(table, day):
    """How many employees are active on `day`."""
    day = pd.Timestamp(day)
    return int(((table["hire_date"] <= day)
                & (table["exit_date"].isna() | (table["exit_date"] >= day))).sum())   # FIX ME


rows = []
for m in months:
    start, end = m, m + pd.offsets.MonthEnd(0)
    rows.append({"month": m.strftime("%Y-%m"),
                 "opening": active_count(employees, start - ONE_DAY),
                 "hires": int(employees["hire_date"].between(start, end).sum()),
                 "exits": int(employees["exit_date"].between(start, end).sum()),
                 "closing": active_count(employees, end)})
test = pd.DataFrame(rows).set_index("month")
broken = test.index[test["closing"] != test["opening"] + test["hires"] - test["exits"]]
print("CHECK:", "PASS" if len(broken) == 0 else f"FAIL - bridge does not balance in {list(broken)}")

CHECK: FAIL - bridge does not balance in ['2025-02', '2025-03', '2025-06', '2025-07', '2025-08', '2025-10']

timed = requisitions[requisitions["status"] != "canceled"].copy()
timed["days_to_fill"] = (timed["offer_accepted_date"] - timed["opened_date"]).dt.days.fillna(0)   # FIX ME

medians = timed.groupby("department")["days_to_fill"].median()
zero_days = int((timed["days_to_fill"] == 0).sum())
print("CHECK:", "PASS" if zero_days == 0 else f"FAIL - {zero_days} roles recorded as 0 days to fill")
medians.to_frame("median days to fill")

CHECK: FAIL - 5 roles recorded as 0 days to fill

in_2025 = history["effective_from"].between("2025-01-01", CUTOFF)
out_2025 = history["effective_to"].between("2025-01-01", CUTOFF)
dept_hires = history[in_2025].groupby("department").size()            # FIX ME
dept_exits = history[out_2025].groupby("department").size()           # FIX ME

company_hires = int(employees["hire_date"].between("2025-01-01", CUTOFF).sum())
company_exits = int(employees["exit_date"].between("2025-01-01", CUTOFF).sum())
ok = dept_hires.sum() == company_hires and dept_exits.sum() == company_exits
print(f"Department hires add to {dept_hires.sum()} (company {company_hires}); "
      f"department exits add to {dept_exits.sum()} (company {company_exits})")
print("CHECK:", "PASS" if ok else "FAIL - department hires or exits do not add up to the company")

Department hires add to 164 (company 141); department exits add to 154 (company 131)
CHECK: FAIL - department hires or exits do not add up to the company

# 1. Run the cell with focus = "Operations", then try "Technology".
# 2. For each, note headcount path, exits, turnover, job-family mix and open roles.
# 3. Decide which ONE investigation you would recommend, then write the note below.
focus = "Operations"

colours = ["#eb6834" if d == focus else "#b8b6b0" for d in departments]
fig, axes = plt.subplots(2, 2, figsize=(11, 7.5))

ax = axes[0, 0]
for d in departments:
    ax.plot(range(12), dept_bridges[d]["closing"], color="#eb6834" if d == focus else "#b8b6b0",
            linewidth=2.5 if d == focus else 1)
ax.set_title(f"Headcount by department ({focus} in orange)")
ax.set_xticks(range(0, 12, 3), ["Jan", "Apr", "Jul", "Oct"])
ax.set_ylabel("Employees")

ax = axes[0, 1]
correct = pd.Series({d: dept.loc[d, "correct"] for d in departments}) * 100
ax.barh(departments, correct, color=colours)
ax.set_title("Annual turnover, 2025 (average headcount)")
ax.set_xlabel("Exits as % of average headcount")

ax = axes[1, 0]
fill = req.groupby("department")["days_to_fill"].median().reindex(departments)
ax.barh(departments, fill, color=colours, label="Median days to fill")
open_age = req.groupby("department")["open_age_days"].max().reindex(departments)
ax.scatter(open_age, departments, marker=">", s=70, color="#2a78d6", label="Oldest open role", zorder=3)
ax.set_title("Recruitment speed and open roles")
ax.set_xlabel("Days")
ax.legend(fontsize=8)

ax = axes[1, 1]
fam = employees.set_index("employee_id")["job_family"]
in_focus = history[(history["department"] == focus) & is_active(history, CUTOFF, "effective_from", "effective_to")]
mix = in_focus["employee_id"].map(fam).value_counts()
ax.barh(mix.index, mix.values, color="#2a78d6")
ax.set_title(f"Job families in {focus} at the cutoff")
ax.set_xlabel("Active employees")

fig.tight_layout()
plt.show()