Analyzing Employee Compensation

Given a compensation dataset (CSV/Excel with columns like employee_id, role, department, level, gender, tenure, base_salary, bonus, total_comp), run this baseline analysis:

Python
import pandas as pd

df = pd.read_csv("compensation.csv")

# 1. Normalize comp to a single comparable figure
df["total_comp"] = df["base_salary"] + df["bonus"].fillna(0)

# 2. Compare pay by role/level (compa-ratio style)
by_role = df.groupby(["role", "level"])["total_comp"].agg(["mean", "median", "std", "count"])
print(by_role)

# 3. Flag potential equity gaps by gender within same role/level
gap = df.groupby(["role", "level", "gender"])["total_comp"].mean().unstack()
gap["pct_gap"] = (gap.iloc[:,0] - gap.iloc[:,1]) / gap.iloc[:,1] * 100
print(gap)

This surfaces the two questions HR usually needs first: "Are we paying market-consistent, level-appropriate salaries?" and "Are there unexplained gaps between comparable employees?"

Progress:

• Step 1: Clean and standardize data (roles, levels, comp components)
• Step 2: Define comparable groups (same role + level + location, minimum)
• Step 3: Calculate descriptive stats per group (mean, median, range, spread)
• Step 4: Benchmark against external market data (if available)
• Step 5: Run regression or gap analysis controlling for legitimate factors (tenure, performance, level)
• Step 6: Identify outliers and unexplained disparities
• Step 7: Summarize findings with recommendations, not just numbers

Step 1: Clean and Standardize

• Convert all comp to same time basis (annualized) and currency
• Combine base + bonus + equity into a "total comp" field, but keep components separate too — some analyses need base-only (e.g., pay equity by law in some jurisdictions)
• Standardize job titles/levels — inconsistent naming is the #1 cause of bad comparisons

Step 2: Define Comparable Groups

Never compare raw salaries across the whole company. Minimum grouping: role + level. Better: role + level + location (cost of living matters). Add tenure bands if the org ties pay progression to tenure.

Step 3: Descriptive Stats

For each group, calculate:

• Mean, median (median resists outlier distortion)
• Standard deviation / coefficient of variation (spread within group — high spread = inconsistent pay practices)
• Min/max (range check for outliers)
• Headcount (groups under 5 people are statistically unreliable — flag but don't over-interpret)

Step 4: Market Benchmarking

Compare median comp per role/level against external survey data (Radford, Mercer, Levels.fyi, etc.). Compute a compa-ratio: employee_salary / market_midpoint. Compa-ratio of 0.9–1.1 is typically "at market"; below 0.8 signals retention risk.

Step 5: Controlled Gap Analysis

Raw averages by gender/ethnicity are misleading if roles/tenure differ. Use regression:

Python
import statsmodels.formula.api as smf

model = smf.ols("total_comp ~ C(role) + C(level) + tenure + C(gender)", data=df).fit()
print(model.summary())

The coefficient on gender (holding role, level, tenure constant) is the "unexplained gap" — this is what pay equity audits report, not the raw average difference.

Step 6: Identify Outliers

Flag individuals whose comp is >1.5 standard deviations from their group mean in either direction. Investigate: over-paid outliers (retention deals, legacy negotiations) and under-paid outliers (compression, missed adjustments).

Step 7: Summarize

Translate stats into decisions: who needs an adjustment, what budget is required, what policy gap caused it (e.g., no structured offer bands), and how to prevent recurrence.

Example 1: Input: Dataset shows Software Engineers, Level 3: 12 men averaging $118K, 8 women averaging $109K. Output: Raw gap = 7.6%. After regression controlling for tenure and performance rating, unexplained gap = 3.1% ($3,400/year across 8 women). Recommendation: adjust 3 employees whose individual gap exceeds 2 standard deviations; budget impact ~$10,200/year; monitor next cycle since sample size (n=20) is small.

Example 2: Input: Compa-ratios for the Sales department show median 0.82 against market data. Output: Department is underpaid relative to market by ~18%. Cross-check turnover data — if voluntary attrition in Sales is above company average, recommend a market adjustment budget for the next review cycle, prioritizing employees with compa-ratio below 0.75.