Skip to main content

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

The steps

  1. Load the employees, the payslips and the cost centres. In this set they are three sheets of one Excel workbook.
  2. 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.
  3. Add up the payslips by pay date to get the payroll cost of each year, with overtime pay and hours.
  4. 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.
  5. 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).

Python (pandas)
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:

Output
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...

Data Drill

Updated 16 Nov 2026 · findi360