Build a Training Compliance Matrix

Health and safety, data protection, first aid, forklift licences, food hygiene: most organisations have a list of training that certain people must hold and renew. The question auditors and managers ask is simple — is everyone who needs it trained, and is anyone about to lapse? — but answering it from a learning system export is fiddly. The export lists every course completion ever recorded; what matters is the latest completion per person and course, whether that course is required for the person's role at all, and whether it has expired given that each course has its own validity period. The usual result is a matrix kept by hand: people down the side, courses across the top, coloured cells. This page builds that matrix with pandas from three tables — people, role requirements and completions — classifies every cell, measures compliance by department and writes a colour-coded workbook with an action list. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Four small tables: people with department and role; requirements listing which courses each role must hold; courses with how many months each certificate stays valid; and completions, the learning system's export of every completion recorded. The report date is 5 October 2026.

Step 1 — Who needs what

import pandas as pd

today = pd.Timestamp("2026-10-05")
people = pd.read_excel("people.xlsx")
required = pd.read_excel("requirements.xlsx")
courses = pd.read_excel("courses.xlsx")
done = pd.read_excel("completions.xlsx", parse_dates=["Completed"])

needs = people.merge(required, on="Role", how="left", validate="many_to_many")
print(needs.groupby("Name")["Course"].apply(", ".join).to_string())

Requirements belong to roles, not to people: a forklift operator needs a forklift licence, manual handling and fire safety, whoever holds the job. Joining people to the requirements by role produces one row per person per course they must hold — eighteen rows for the seven people here, since each role needs two or three courses. That table is the backbone of the report, and getting it from a role mapping rather than a hand-kept list means that when someone changes role, or a role gains a requirement, the matrix follows without anyone editing it.

Step 2 — Latest completion, expiry and status

latest = done.sort_values("Completed").groupby(["Employee", "Course"], as_index=False).last()
cells = needs.merge(latest, on=["Employee", "Course"], how="left").merge(courses, on="Course", how="left")
cells["Expires"] = [c + pd.DateOffset(months=int(m)) if pd.notna(c) else pd.NaT
                    for c, m in zip(cells["Completed"], cells["Valid (months)"])]

def status(row):
    if pd.isna(row["Completed"]):
        return "Missing"
    if row["Expires"] < today:
        return "Expired"
    if row["Expires"] < today + pd.DateOffset(days=60):
        return "Expiring"
    return "Valid"

cells["Status"] = cells.apply(status, axis=1)
print(cells[["Name", "Course", "Completed", "Expires", "Status"]]
      .assign(Completed=cells["Completed"].dt.date, Expires=cells["Expires"].dt.date).to_string(index=False))

The completions export often contains several records for the same person and course — Asha has done fire safety twice — and only the most recent counts. Sorting by date and taking the last record per person and course keeps exactly that. Each course's validity period then gives an expiry date, using calendar months so a certificate valid for twelve months from 20 October expires on 20 October the next year. Every required course ends up in one of four states: missing if there is no record at all, expired if the expiry date has passed, expiring if it falls within the next sixty days, and valid otherwise. Sixty days is a common warning window because it leaves time to book a course; it is one number in the code if a different notice period suits, and some organisations use ninety days for courses with long waiting lists.

Step 3 — The matrix and compliance by department

matrix = cells.pivot_table(index=["Department", "Name"], columns="Course", values="Status", aggfunc="first")
matrix = matrix.reindex(columns=courses["Course"]).fillna("—")         # "—" = not required for the role
print(matrix.to_string())

cells["Compliant"] = cells["Status"].isin(["Valid", "Expiring"])
by_dept = cells.groupby("Department").agg(required=("Course", "size"), compliant=("Compliant", "sum"))
by_dept["Compliance"] = (by_dept["compliant"] / by_dept["required"]).round(3)
print(by_dept.to_string())
print("overall:", f"{cells['Compliant'].mean():.0%}")

Pivoting the statuses gives the familiar matrix: one row per person, grouped by department, and one column per course. Courses a person's role does not require stay empty in the pivot and are shown as a dash, which is different from missing — the forklift licence is not missing for the office administrators, it simply does not apply to them. A certificate held but not required, such as Dan's first aid, does not appear at all, because the matrix is driven by requirements. Compliance is the share of required course-person pairs that are currently valid, counting expiring as still compliant — they are valid today, they just need booking. The department view shows where the gaps are. The office is weakest at 50%: Eva's data protection certificate expired in September and she has no fire safety record at all. The warehouse is at 75%, with Ben's forklift licence and Chloe's manual handling both expired — the licence matters most, because Ben should not be driving a forklift until it is renewed. The kitchen is at 83%, but only because Femi's first aid is still valid for another two weeks; Grace has never done it. Overall, 72% of required certificates are current.

Step 4 — The workbook and the action list

from openpyxl import load_workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill

actions = (cells[cells["Status"] != "Valid"]
           .assign(Due=lambda d: d["Expires"].fillna(today).dt.date)
           .sort_values(["Status", "Due"])[["Department", "Name", "Course", "Status", "Due"]])
path = "training_matrix_2026-10.xlsx"
with pd.ExcelWriter(path) as xw:
    matrix.to_excel(xw, sheet_name="Matrix")
    by_dept.reset_index().to_excel(xw, sheet_name="By department", index=False)
    actions.to_excel(xw, sheet_name="Actions", index=False)

wb = load_workbook(path)
ws = wb["Matrix"]
area = f"C2:{ws.cell(1, ws.max_column).column_letter}{ws.max_row}"
for text, colour in [("Valid", "C6EFCE"), ("Expiring", "FFEB9C"), ("Expired", "FFC7CE"), ("Missing", "F4B084")]:
    ws.conditional_formatting.add(area, CellIsRule(operator="equal", formula=[f'"{text}"'], fill=PatternFill("solid", bgColor=colour)))
ws.freeze_panes = "C2"
wb.save(path)
print(actions.to_string(index=False))

The matrix sheet colours each status with conditional formatting rather than fixed fills, so if someone corrects a status by hand the colour follows. The department sheet gives the compliance percentages for a management summary, and the action list is what the training coordinator works from: every course that is missing, expired or about to expire, with the date it lapsed or will lapse, sorted so the expired items come first. In this run that is three expired certificates, five expiring in the next two months — four of them in October and November — and two that have never been completed, ten actions in all for seven people. Run monthly, with the action list emailed to line managers, the matrix replaces the hand-coloured spreadsheet completely; the only manual inputs left are the role requirements and validity periods, which change rarely.

Keeping the inputs honest

The matrix is only as reliable as the joins behind it, and three things commonly break them. First, people must be matched on an employee ID, never on names: the HR system says Asha Patel, the learning system A. Patel, and a name join silently turns her valid certificates into missing. Second, course names drift — Fire Safety, Fire safety awareness, Fire Safety (refresher) — so map every variant in the export to one canonical course name before matching, and list any course in the completions that is not in the course table, rather than dropping it. Third, new starters need a grace period: someone who joined last week cannot have done their induction training yet, so a missing certificate for a person whose start date is within, say, thirty days is better shown as due than counted against the department. Each of these is a few lines of pandas, and together they decide whether managers trust the report.

Common pitfalls

• Counting every completion. Keep only the latest record per person and course.
• Requirements kept per person. Derive them from roles so changes flow through.
• Not required shown as missing. Distinguish the two; a dash is not a gap.
• Expiry by days instead of months. Use calendar months for certificates valid for N months.
• Expiring treated as non-compliant. They are valid today; flag them for booking, not as failures.

Conclusion

A training compliance matrix joins people to the courses their role requires, takes each person's latest completion of each course, adds the course's validity period to get an expiry date, and classifies every pair as valid, expiring, expired or missing. Pivoting gives the matrix, a group-by gives compliance by department, and a filtered list of everything not valid becomes the action list, all written to a colour-coded workbook.

Frequently asked questions

How do I get the latest training completion per person in pandas? Sort completions by date and use groupby(["Employee", "Course"]).last().

How do I calculate certificate expiry dates in pandas? Add the course's validity in months with pd.DateOffset(months=n) to the completion date.

How do I show "not required" differently from "missing"? Build the matrix only from required courses; cells for courses a role does not need stay empty and can be shown as a dash.

How do I colour a training matrix in Excel from Python? Add CellIsRule conditional formatting rules for each status text with openpyxl.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports.
• Build a Headcount and Attrition Report in Excel with Python — other people reporting.
• Highlight Cells Containing Text with openpyxl — colouring by status text.
• Build an Employee Leave Balance Report — another HR report.