Payroll analysis in Python with pandas — headcount, turnover, overtime
To analyse payroll in Python, load the employee list and the payslips into pandas, count who is on the payroll at each date from the hire and leaving dates, add up the payslips by year, and check every payslip against the employee's last working day. The 45 lines of pandas below do this on a DATA DARE HR and payroll practice set — a UAE retailer with about 520 people over nearly four years — and the results agree with the answers in the set's Question Book.
The sets live now
Mizan Retail: Who Works Here, and What Do They Cost? — HR & Payroll Analytics
Analyse a retailer's workforce and payroll: headcount, joiners, leavers and turnover, pay by element, overtime, leave, final settlements, the payroll's tie to t...
Get the data
Mizan Manufacturing and Logistics: Two Payrolls, One Ledger — HR & Payroll Analytics
Work two companies' payroll as the HR system exports it: append the new quarter, tie pay to the ledger's salary journal by cost centre, measure headcount agains...
Get the data
Mizan Group: 1,000 People, One Payroll — HR & Payroll Analytics
Run HR and payroll analytics for a three-company group: headcount and turnover, payroll tied to the ledger, overtime against the legal limit, attendance and lat...
Get the data
Mizan Group: Every Punch, Every Payslip — HR & Payroll Analytics
Audit time and pay at enterprise scale: every clock and gate event of three companies against rosters, overtime approvals and the payroll that ties to the ledge...
Get the data
Rawdah Services Group: Payroll Close Across Three Systems — HR & Payroll Analytics
Three companies, three payroll systems, one group staff cost. Build the pay-code mapping from the Group Payroll Manual, prove it against the signed-off manageme...
Get the dataThe steps
- Load the employees, the payslips and the cost centres. In this set they are three sheets of one Excel workbook.
- Count the headcount at a date: hired on or before it and not yet gone. Turnover is the year's leavers divided by the average of the twelve month-end headcounts.
- Add up the payslips by pay date to get the payroll cost of each year, with overtime pay and hours.
- Compare overtime with regular pay, store by store. Leave final settlements (gratuity and unused leave paid on leaving) out of regular pay, or one leaver distorts a small store.
- Check every payslip against the employee's last working day. A regular salary for a month after it is money paid to someone who has left.
The code
Download the L1 HR and payroll set (DD-HRM-001) and run this in the folder that holds its Excel workbook. It needs pandas and openpyxl (pip install pandas openpyxl).
import pandas as pd
pd.options.display.float_format = "{:,.2f}".format
workbook = "DD-HRM-001_HR_Payroll.xlsx"
staff = pd.read_excel(workbook, sheet_name="DimEmployee")
pay = pd.read_excel(workbook, sheet_name="FactPayslip")
centres = pd.read_excel(workbook, sheet_name="DimCostCentre")
# 1. Headcount at a date: hired on or before it, and not gone yet (no last day, or a later one).
def headcount(date):
gone = staff["LastWorkingDay"] <= date
return ((staff["HireDate"] <= date) & ~gone).sum()
joiners = (staff["HireDate"].dt.year == 2025).sum()
leavers = (staff["LastWorkingDay"].dt.year == 2025).sum()
month_ends = pd.date_range("2025-01-31", periods=12, freq="ME")
average = sum(headcount(d) for d in month_ends) / 12
print(f"Headcount 31 Dec 2025: {headcount(pd.Timestamp('2025-12-31'))}")
print(f"Joiners 2025: {joiners} leavers 2025: {leavers}")
print(f"Turnover 2025: {leavers / average:.2%} of an average {average:.1f} people\n")
# 2. Payroll cost by year, from the pay date: net pay, overtime pay and overtime hours.
pay["Year"] = pay["PayDate"].dt.year
cost = pay[pay["Year"].isin([2024, 2025])].groupby("Year")
by_year = cost[["NetPay", "Overtime", "OvertimeHours"]].sum()
print(by_year, "\n")
growth = by_year.loc[2025, "NetPay"] / by_year.loc[2024, "NetPay"] - 1
print(f"Payroll growth 2025 over 2024: {growth:.2%}")
# 3. Overtime share by store: overtime / regular pay (net pay without final settlements).
y25 = pay[pay["Year"] == 2025].merge(centres[["CostCentreCode", "CostCentreType"]])
cols = ["Overtime", "NetPay", "Gratuity", "LeaveEncashment"]
stores = y25[y25["CostCentreType"] == "Store"].groupby("CostCentreCode")[cols].sum()
regular = stores["NetPay"] - stores["Gratuity"] - stores["LeaveEncashment"]
share = (stores["Overtime"] / regular).nlargest(3)
print("Highest overtime share:\n" + share.map("{:.2%}".format).to_string(), "\n")
# 4. Control check: a regular salary paid for a month after the person's last working day.
check = pay.merge(staff[["EmployeeID", "LastWorkingDay"]], on="EmployeeID")
last_month = check["LastWorkingDay"].dt.year * 100 + check["LastWorkingDay"].dt.month
after = check[(check["Basic"] > 0) & (check["PeriodKey"] > last_month)]
print("Paid after leaving:")
print(after[["EmployeeID", "PeriodKey", "LastWorkingDay", "NetPay"]].to_string(index=False))
print(f"Total paid after leaving: {after['NetPay'].sum():,.2f}")
What it prints
2025, exactly as the script prints it:
Headcount 31 Dec 2025: 522
Joiners 2025: 135 leavers 2025: 129
Turnover 2025: 24.84% of an average 519.3 people
NetPay Overtime OvertimeHours
Year
2024 17,950,141.43 822,765.62 121,067.67
2025 19,477,833.72 874,663.43 122,943.36
Payroll growth 2025 over 2024: 8.51%
Highest overtime share:
CostCentreCode
RET-S019 10.79%
RET-S011 7.21%
RET-S014 6.32%
Paid after leaving:
EmployeeID PeriodKey LastWorkingDay NetPay
RET-E0646 202505 2025-04-12 3,251.00
RET-E0646 202506 2025-04-12 3,251.00
RET-E0646 202507 2025-04-12 3,251.00
Total paid after leaving: 9,753.00
How to read the result
- Mizan Retail had 522 people on 31 December 2025. 135 joined and 129 left during the year, so turnover was 24.84% of an average 519.3 people — about one person in four.
- Net pay grew 8.51% from 2024 to 2025, to 19,477,833.72. Overtime pay rose from 822,765.62 to 874,663.43.
- Store RET-S019 pays 10.79% of its regular pay as overtime, well above the next stores at 7.21% and 6.32%. That is the store to look at for staffing and rosters.
- Employee RET-E0646 last worked on 12 April 2025 but was paid a full salary for May, June and July: 9,753.00 paid after leaving. This is the error a monthly payroll check should catch.
- Headcount, joiners, leavers, turnover, net pay, payroll growth, overtime, the overtime store and the payment after leaving are all questions in the set, so each figure can be checked in its Question Book.
Mistakes this data will catch
- Testing "still here" instead of "gone". A blank last working day means still employed; pandas reads it as NaT, and every comparison with NaT is False. The code tests who is gone and keeps everyone else.
- Counting someone whose last working day is the count date as still on the payroll. The rule is a last working day after the date.
- Treating final settlements as regular pay. Gratuity and leave encashment are paid once, on leaving.
- Averaging headcount from the first and last day of the year only. Use the twelve month-ends; people join and leave all year.
The next levels
The higher levels of the set (DD-HRM-002 to DD-HRM-005) come as CSV, JSON and Parquet files and add leave, the headcount plan, salary lines in the general ledger, attendance and clock events against rosters, bank transfer files and, at L5, a group with three payroll systems. The pattern stays the same: one employee table, one pay table, then dates.
Python or Power BI
Python suits the monthly payroll checks: a script that runs the same tests on every payroll and lists the exceptions. Power BI suits the HR dashboard people open afterwards, with headcount, turnover and cost by store on a date slicer.
The course
- Basic Python Course Free recording Basic Python takes you from never having written a line of code to scripts that do real work on your own files. You will cover the language itself, then move quickly to the part th...
- Python for Finance — Automation & Controls Coming soon Python for Finance — Automation & Controls is about the monthly tasks that eat a week and shouldn't. You will automate reconciliations, build controls that check data instead of tr...
- Python for Finance — Advanced Analytics & Reporting Coming soon Python for Finance — Advanced Analytics & Reporting is where the scripts stop being utilities and start producing analysis. You will work with datasets larger than Excel will hold,...
- Python + AI for Finance Workflows Coming soon Python + AI for Finance Workflows is about putting a language model inside a process, not chatting with one. You will build workflows that read documents, extract what matters, che...
- Data Transformation in Python — At Scale Free recording One export of 6.18 million dirty rows, cleaned in Python and reconciled to a total you can defend. For Power Query users who have never written Python. Amounts stored as text, thre...