Session 13 lecture: HR: workforce analytics¶

# 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

emp_ok = employees["exit_date"].isna() | (employees["exit_date"] > employees["hire_date"])
hist_ok = history["effective_to"].isna() | (history["effective_to"] > history["effective_from"])
checks = {
    "employees: one row per employee_id": employees["employee_id"].is_unique,
    "history: one row per (employee_id, effective_from)": not history.duplicated(["employee_id", "effective_from"]).any(),
    "requisitions: one row per requisition_id": requisitions["requisition_id"].is_unique,
    "every history row belongs to a known employee": history["employee_id"].isin(employees["employee_id"]).all(),
    "exit date is after hire date (when there is one)": emp_ok.all(),
    "each department interval ends after it starts": hist_ok.all(),
    "no hire or exit after the cutoff": (employees[["hire_date", "exit_date"]].max() <= CUTOFF).all(),
}
pd.Series(checks).to_frame("passes")

employees["hire_date"].dt.year.value_counts().sort_index().to_frame("employees hired")

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())


print("Active at the cutoff:", active_on(employees, CUTOFF))

Active at the cutoff: 389

fixture = pd.DataFrame({
    "employee_id": ["A", "B", "C", "D", "E", "F"],
    "hire_date": pd.to_datetime(["2024-06-10", "2025-03-01", "2023-02-13", "2022-09-05", "2021-11-22", "2022-04-04"]),
    "exit_date": pd.to_datetime([None, None, "2025-03-01", "2025-03-17", "2025-04-01", None]),
    "story": [
        "Still employed",
        "Hired on 1 Mar",
        "Last day 28 Feb",
        "Leaves mid-March",
        "Last day 31 Mar",
        "Sales to Marketing 20 Mar",
    ],
})
fixture

fig, ax = plt.subplots(figsize=(9, 3.2))
view_start, view_end = pd.Timestamp("2025-02-01"), pd.Timestamp("2025-05-01")
for i, row in fixture.iterrows():
    left = max(row["hire_date"], view_start)
    right = row["exit_date"] if pd.notna(row["exit_date"]) else view_end
    ax.hlines(i, left, min(right, view_end), linewidth=9, color="#2a78d6")
ax.axvline(pd.Timestamp("2025-03-01"), color="black", linestyle="--", linewidth=1)
ax.axvline(pd.Timestamp("2025-04-01"), color="black", linestyle="--", linewidth=1)
ax.set_yticks(range(len(fixture)), fixture["employee_id"])
ax.invert_yaxis()
ax.set_xlim(view_start, view_end)
ax.set_title("Fixture: each bar runs from hire date up to (not including) the exit date")
ax.set_xlabel("Date (dashed lines: 1 March and 1 April)")
ax.set_ylabel("Employee")
plt.show()

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
    }


trace = pd.DataFrame({
    "in opening (active 28 Feb)": is_active(fixture, "2025-02-28"),
    "hire in March": fixture["hire_date"].between("2025-03-01", "2025-03-31"),
    "exit in March": fixture["exit_date"].between("2025-03-01", "2025-03-31"),
    "in closing (active 31 Mar)": is_active(fixture, "2025-03-31"),
})
trace.index = fixture["employee_id"]
display(trace)
print("March bridge:", month_bridge(fixture, "2025-03-01"))

March bridge: {'opening': 5, 'hires': 1, 'exits': 2, 'closing': 4}

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.

fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(9, 6), sharex=True)
x = np.arange(len(bridge))
ax1.plot(x, bridge["closing"], marker="o", color="#2a78d6")
ax1.set_title("Closing headcount at the end of each month, 2025")
ax1.set_ylabel("Employees")
ax2.bar(x, bridge["hires"], color="#1baf7a", label="Hires")
ax2.bar(x, -bridge["exits"], color="#eb6834", label="Exits (shown below zero)")
ax2.axhline(0, color="black", linewidth=0.8)
ax2.set_title("What changed it: hires and exits each month")
ax2.set_ylabel("Employees")
ax2.set_xticks(x, bridge.index, rotation=45)
ax2.set_xlabel("Month")
ax2.legend(fontsize=8)
fig.tight_layout()
plt.show()

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()
mean_headcount = bridge["avg_headcount"].mean()
annual_turnover = annual_exits / mean_headcount
print(f"Exits in 2025: {annual_exits} | mean monthly average headcount: {mean_headcount:,.1f}")
print(f"Annual turnover: {annual_turnover:.1%}")

ax = plt.gca()
ax.bar(bridge.index, bridge["turnover"] * 100, color="#eb6834")
ax.set_title("Monthly turnover, 2025")
ax.set_xlabel("Month")
ax.set_ylabel("Exits as % of average headcount")
ax.tick_params(axis="x", rotation=45)
plt.show()

Exits in 2025: 131 | mean monthly average headcount: 390.2
Annual turnover: 33.6%

current_headcount = bridge["closing"].iloc[-1]
methods = pd.Series({
    "Correct: exits ÷ mean of monthly average headcounts": annual_turnover,
    "(a) exits ÷ current (December closing) headcount": annual_exits / current_headcount,
    "(b) mean of the 12 monthly rates": bridge["turnover"].mean(),
    "(c) 12 × mean of the monthly rates": 12 * bridge["turnover"].mean(),
})
methods.map("{:.1%}".format).to_frame("reported annual turnover")

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("Departments:", ", ".join(departments))

Departments: Finance and Admin, Marketing, Operations, People, Sales, Technology

fig, axes = plt.subplots(2, 3, figsize=(11, 5.5), sharex=True)
for ax, d in zip(axes.flat, departments):
    b = dept_bridges[d]
    avg = ((b["opening"] + b["closing"]) / 2).mean()
    ax.plot(range(12), b["closing"], marker="o", markersize=3, color="#2a78d6")
    ax.axhline(avg, color="#b8b6b0", linestyle="--", linewidth=1)
    ax.plot(11, b["closing"].iloc[-1], marker="o", markersize=8, color="#eb6834")
    ax.set_title(d, fontsize=10)
    ax.set_ylim(0, None)
for ax in axes[1]:
    ax.set_xticks(range(0, 12, 3), ["Jan", "Apr", "Jul", "Oct"])
    ax.set_xlabel("Month of 2025")
for ax in axes[:, 0]:
    ax.set_ylabel("Employees")
fig.suptitle("Closing headcount by department (grey dashed: average; orange: December)", fontweight="bold", x=0.01, ha="left")
fig.tight_layout()
plt.show()

rows = {}
for d, b in dept_bridges.items():
    avg = ((b["opening"] + b["closing"]) / 2).mean()
    exits = b["exits"].sum()
    rows[d] = {
        "Jan opening": b["opening"].iloc[0],
        "peak": b["closing"].max(),
        "Dec closing": b["closing"].iloc[-1],
        "exits": exits,
        "avg headcount": avg,
        "correct": exits / avg,
        "÷ current": exits / b["closing"].iloc[-1],
    }
dept_turnover = pd.DataFrame(rows).T
dept_turnover["gap (points)"] = (dept_turnover["÷ current"] - dept_turnover["correct"]) * 100
dept_turnover["small group"] = (dept_turnover["exits"] < 5) | (dept_turnover["avg headcount"] < 20)

dept_turnover.assign(**{
    c: dept_turnover[c].astype(int) for c in ["Jan opening", "peak", "Dec closing", "exits"]
} | {
    "avg headcount": dept_turnover["avg headcount"].map("{:.1f}".format),
    "correct": dept_turnover["correct"].map("{:.1%}".format),
    "÷ current": dept_turnover["÷ current"].map("{:.1%}".format),
    "gap (points)": dept_turnover["gap (points)"].map("{:+.1f}".format),
})

fixture_history = pd.DataFrame({
    "employee_id": ["A", "B", "C", "D", "E", "F", "F"],
    "department": ["Sales", "Marketing", "Sales", "Marketing", "Sales", "Sales", "Marketing"],
    "effective_from": pd.to_datetime(["2024-06-10", "2025-03-01", "2023-02-13", "2022-09-05",
                                      "2021-11-22", "2022-04-04", "2025-03-20"]),
    "effective_to": pd.to_datetime([None, None, "2025-03-01", "2025-03-17", "2025-04-01", "2025-03-20", None]),
})
fixture_history = add_interval_flags(fixture_history)

march = pd.DataFrame({d: department_month(fixture_history, d, "2025-03-01") for d in ["Sales", "Marketing"]}).T
march.loc["All departments"] = march.sum()
march

flows = ["opening", "hires", "transfers_in", "exits", "transfers_out", "closing"]
all_departments = sum(b[flows] for b in dept_bridges.values())

assert (all_departments["transfers_in"] == all_departments["transfers_out"]).all(), "transfers do not cancel"
for col in ["opening", "hires", "exits", "closing"]:
    assert (all_departments[col] == bridge[col]).all(), f"department {col} do not add up to the company"
print("Transfers cancel every month, and department totals equal the company bridge.")
print("Transfers between departments in 2025:", all_departments["transfers_in"].sum())

Transfers cancel every month, and department totals equal the company bridge.
Transfers between departments in 2025: 23

dept_bridges["People"]

active_now = employees[is_active(employees, CUTOFF)]
leavers = employees[employees["exit_date"].notna()]
tenure_days = (CUTOFF - active_now["hire_date"]).dt.days
service_days = (leavers["exit_date"] - leavers["hire_date"]).dt.days
wrong_days = (CUTOFF - employees["hire_date"]).dt.days

pd.DataFrame({
    "people": [len(tenure_days), len(service_days), len(wrong_days)],
    "median days": [tenure_days.median(), service_days.median(), wrong_days.median()],
    "median years (approx.)": [tenure_days.median() / 365.25, service_days.median() / 365.25,
                               wrong_days.median() / 365.25],
}, index=["Tenure of active employees", "Completed service of leavers", "Wrong: everyone to the cutoff"]).round(1)

fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(11, 3.8), sharex=True)
bins = np.arange(0, 11.5, 0.5)
ax1.hist(tenure_days / 365.25, bins=bins, color="#2a78d6")
ax1.set_title("Tenure of active employees at the cutoff")
ax1.set_xlabel("Years (approx., days ÷ 365.25)")
ax1.set_ylabel("Employees")
ax2.hist(service_days / 365.25, bins=bins, color="#eb6834")
ax2.set_title("Completed service of 2025 leavers")
ax2.set_xlabel("Years (approx., days ÷ 365.25)")
ax2.set_ylabel("Leavers")
fig.tight_layout()
plt.show()

req = requisitions.copy()
req["days_to_fill"] = (req["offer_accepted_date"] - req["opened_date"]).dt.days          # blank if not accepted
req["open_age_days"] = (CUTOFF - req["opened_date"]).dt.days.where(req["status"] == "open")  # open roles only

print(req["status"].value_counts().to_string())
recruit = req.groupby("department").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"),
)
recruit

status
filled      143
canceled      8
open          5
accepted      4

jitter = np.random.default_rng(seed=13)
order = list(recruit.index)
ax = plt.gca()
for i, d in enumerate(order):
    filled = req.loc[(req["department"] == d) & req["days_to_fill"].notna(), "days_to_fill"]
    ax.scatter(filled, i + jitter.uniform(-0.15, 0.15, len(filled)), s=12, color="#2a78d6", alpha=0.6,
               label="Time to fill (accepted roles)" if i == 0 else None)
    still_open = req.loc[(req["department"] == d) & req["open_age_days"].notna(), "open_age_days"]
    ax.scatter(still_open, [i] * len(still_open), s=70, marker=">", color="#eb6834",
               label="Open at cutoff: age so far" if i == len(order) - 1 else None)
ax.set_yticks(range(len(order)), order)
ax.set_title("Recruitment: completed fills and roles still open")
ax.set_xlabel("Days since the role was opened")
ax.set_ylabel("Department")
ax.legend(fontsize=8, loc="lower right")
plt.show()

zero_filled = req[req["status"] != "canceled"].assign(days=lambda t: t["days_to_fill"].fillna(0))
pd.DataFrame({
    "correct median (accepted only)": recruit["median_days_to_fill"],
    "wrong: open roles as 0 days": zero_filled.groupby("department")["days"].median(),
    "open roles": recruit["open_roles"],
})

last_department = history[history["last_interval"] & history["effective_to"].notna()]
exits_by = last_department.merge(employees[["employee_id", "job_family"]], on="employee_id")
counts = pd.crosstab(exits_by["department"], exits_by["job_family"])

suppressed = counts.astype(object).where((counts >= 5) | (counts == 0), "<5")
suppressed

groups = {"Operations": ["Operations"], "Technology": ["Technology"], "Sales": ["Sales"],
          "Corporate functions (combined)": ["Finance and Admin", "Marketing", "People"]}
quarter = pd.PeriodIndex(months, freq="Q").astype(str)

trend = {}
for name, members in groups.items():
    b = sum(dept_bridges[d][["opening", "closing", "exits"]] for d in members)
    b["avg"] = (b["opening"] + b["closing"]) / 2
    q = b.groupby(quarter).agg(exits=("exits", "sum"), avg=("avg", "mean"))
    trend[name] = q["exits"] / q["avg"]
trend = pd.DataFrame(trend)

ax = trend.mul(100).plot(marker="o", color=["#eb6834", "#2a78d6", "#1baf7a", "#b8b6b0"])
ax.set_title("Quarterly turnover by department group, 2025")
ax.set_xlabel("Quarter")
ax.set_ylabel("Exits as % of average headcount")
ax.legend(fontsize=8)
plt.show()
trend.map("{:.1%}".format)

active_mix = history[is_active(history, CUTOFF, "effective_from", "effective_to")].merge(
    employees[["employee_id", "job_family"]], on="employee_id")
pd.crosstab(active_mix["department"], active_mix["job_family"], normalize="index").map("{:.0%}".format)

ops = dept_bridges["Operations"]
ops_row = dept_turnover.loc["Operations"]
print(
    f"Operations started 2025 with {ops['opening'].iloc[0]} people, peaked at {ops['closing'].max()} "
    f"in {ops['closing'].idxmax()} and ended the year with {ops['closing'].iloc[-1]}. "
    f"It recorded {int(ops_row['exits'])} exits, an annual turnover of {ops_row['correct']:.0%} "
    f"against {annual_turnover:.0%} for the whole company (both on average headcount). "
    f"Its roles are almost all Warehouse. It had {int(recruit.loc['Operations', 'open_roles'])} open roles "
    f"at the cutoff, and accepted roles took a median of {recruit.loc['Operations', 'median_days_to_fill']:.0f} days to fill."
)

Operations started 2025 with 162 people, peaked at 192 in 2025-07 and ended the year with 151. It recorded 89 exits, an annual turnover of 54% against 34% for the whole company (both on average headcount). Its roles are almost all Warehouse. It had 0 open roles at the cutoff, and accepted roles took a median of 18 days to fill.