Diagnosing HR Attrition Patterns

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "emp_id":      ["E001","E002","E003","E004","E005","E006","E007","E008",
                    "E009","E010","E011","E012","E013","E014","E015","E016",
                    "E017","E018","E019","E020"],
    "department":  ["Engineering","Sales","Finance","Marketing","Engineering",
                    "Sales","Finance","Marketing","Engineering","Sales",
                    "Finance","Marketing","Engineering","Sales","Finance",
                    "Marketing","Engineering","Sales","Finance","Marketing"],
    "role_level":  ["Senior","Junior","Mid","Senior","Junior","Mid","Senior",
                    "Junior","Mid","Senior","Junior","Mid","Senior","Junior",
                    "Mid","Senior","Junior","Mid","Senior","Junior"],
    "tenure_years":[1.2,0.8,4.5,3.1,0.6,2.4,6.2,1.8,0.9,5.3,
                    1.4,3.8,2.1,0.5,7.1,4.2,1.1,3.3,0.7,2.6],
    "salary":      [92000,48000,68000,84000,62000,54000,76000,72000,
                    88000,51000,71000,78000,95000,46000,73000,82000,
                    64000,52000,69000,75000],
    "perf_rating": [3,2,4,5,3,2,4,3,4,3,2,4,5,2,4,5,3,2,3,4],
    "salary_band": ["High","Low","Mid","High","Mid","Low","Mid","High",
                    "High","Low","Mid","High","High","Low","Mid","High",
                    "Mid","Low","Mid","High"],
    "left":        [1,1,0,0,1,1,0,0,1,1,1,0,0,1,0,0,1,1,0,0]
})

# Headline attrition
attrition_rate = df["left"].mean()
leavers = df["left"].sum()
print(f"Employees: {len(df)} | Left: {leavers} | Attrition rate: {attrition_rate:.1%}")

# Tenure banding
df["tenure_band"] = pd.cut(
    df["tenure_years"],
    bins   = [0, 1, 2, 4, float("inf")],
    labels = ["Under 1yr","1–2 yrs","2–4 yrs","4+ yrs"],
    right  = False
)

# Department attrition
dept_stats = df.groupby("department").agg(
    headcount    = ("emp_id",        "count"),
    leavers      = ("left",          "sum"),
    attrition    = ("left",          "mean"),
    mean_salary  = ("salary",        "mean"),
    mean_tenure  = ("tenure_years",  "mean"),
    mean_perf    = ("perf_rating",   "mean")
).round(2).reset_index()
dept_stats["attrition_pct"] = (dept_stats["attrition"] * 100).round(0)
dept_stats = dept_stats.sort_values("attrition", ascending=False)

print("\nDepartment attrition — ranked:")
print(dept_stats[[
    "department","headcount","leavers","attrition_pct","mean_salary","mean_tenure"
]].to_string(index=False))

# Attrition by salary band
salary_stats = df.groupby("salary_band").agg(
    employees    = ("emp_id",   "count"),
    leavers      = ("left",     "sum"),
    attrition    = ("left",     "mean"),
    mean_salary  = ("salary",   "mean")
).round(2).reset_index()
salary_stats["attrition_pct"] = (salary_stats["attrition"] * 100).round(0)

# Force logical order: Low → Mid → High
band_order = ["Low","Mid","High"]
salary_stats["salary_band"] = pd.Categorical(salary_stats["salary_band"], categories=band_order, ordered=True)
salary_stats = salary_stats.sort_values("salary_band")
print("Attrition by salary band:")
print(salary_stats[["salary_band","employees","leavers","attrition_pct","mean_salary"]].to_string(index=False))

# Attrition by tenure band
tenure_stats = df.groupby("tenure_band", observed=True).agg(
    employees = ("emp_id", "count"),
    leavers   = ("left",   "sum"),
    attrition = ("left",   "mean")
).round(2).reset_index()
tenure_stats["attrition_pct"] = (tenure_stats["attrition"] * 100).round(0)
print("\nAttrition by tenure band:")
print(tenure_stats[["tenure_band","employees","leavers","attrition_pct"]].to_string(index=False))

# Attrition by performance rating
perf_stats = df.groupby("perf_rating").agg(
    employees = ("emp_id", "count"),
    leavers   = ("left",   "sum"),
    attrition = ("left",   "mean"),
    mean_sal  = ("salary", "mean")
).round(2).reset_index()
perf_stats["attrition_pct"] = (perf_stats["attrition"] * 100).round(0)
print("\nAttrition by performance rating (1=low, 5=high):")
print(perf_stats[["perf_rating","employees","leavers","attrition_pct","mean_sal"]].to_string(index=False))

# Key correlations
print("\nCorrelations with left (attrition):")
for col in ["salary","tenure_years","perf_rating"]:
    r = df[col].corr(df["left"]).round(3)
    print(f"  {col:<14}  r = {r:+.3f}")

# Replacement cost multipliers by role level (% of annual salary)
MULTIPLIERS = {"Junior": 0.50, "Mid": 0.75, "Senior": 1.25}

# Assign multiplier per row
df["replacement_multiplier"] = df["role_level"].map(MULTIPLIERS)
df["replacement_cost"]       = df["salary"] * df["replacement_multiplier"]

# Cost only for employees who left
leavers_df = df[df["left"] == 1].copy()

# Replacement cost by department
dept_cost = leavers_df.groupby("department").agg(
    leavers          = ("emp_id",            "count"),
    mean_salary      = ("salary",            "mean"),
    total_salary     = ("salary",            "sum"),
    total_repl_cost  = ("replacement_cost",  "sum"),
    mean_repl_cost   = ("replacement_cost",  "mean"),
    mean_multiplier  = ("replacement_multiplier", "mean")
).round(0).reset_index()
dept_cost = dept_cost.sort_values("total_repl_cost", ascending=False)

print("Replacement cost by department (leavers only):")
print(dept_cost[[
    "department","leavers","mean_salary","mean_multiplier","total_repl_cost"
]].to_string(index=False))

total_cost = leavers_df["replacement_cost"].sum()
print(f"\nTotal organisation replacement cost: ${total_cost:,.0f}")

# Cost by salary band (leavers only)
band_cost = leavers_df.groupby("salary_band")["replacement_cost"].agg(["sum","mean"]).round(0)
band_cost.index = pd.Categorical(band_cost.index, categories=["Low","Mid","High"], ordered=True)
band_cost = band_cost.sort_index()
print("\nReplacement cost by salary band (leavers):")
print(band_cost.to_string())

RETENTION_PROGRAMME_COST = 80_000   # Annual cost of retention programme ($)
TARGET_REDUCTION_PP      = 0.20     # Percentage point attrition reduction target

current_attrition   = df["left"].mean()           # 0.50
target_attrition    = current_attrition - TARGET_REDUCTION_PP  # 0.30
employees_saved     = (current_attrition - target_attrition) * len(df)
mean_repl_cost_all  = leavers_df["replacement_cost"].mean()
saving              = employees_saved * mean_repl_cost_all
net_saving          = saving - RETENTION_PROGRAMME_COST
roi_pct             = (net_saving / RETENTION_PROGRAMME_COST * 100)

print(f"Retention programme cost:      ${RETENTION_PROGRAMME_COST:,}")
print(f"Current attrition rate:        {current_attrition:.1%}")
print(f"Target attrition rate:         {target_attrition:.1%}")
print(f"Employees retained (projected):{employees_saved:.1f}")
print(f"Mean replacement cost:         ${mean_repl_cost_all:,.0f}")
print(f"Projected saving:              ${saving:,.0f}")
print(f"Net saving (saving - cost):    ${net_saving:,.0f}")
print(f"ROI:                           {roi_pct:.0f}%")

# Break-even analysis: minimum attrition reduction needed
breakeven_employees_saved = RETENTION_PROGRAMME_COST / mean_repl_cost_all
breakeven_reduction_pp    = breakeven_employees_saved / len(df)
print(f"\nBreak-even analysis:")
print(f"  Employees needed to retain:  {breakeven_employees_saved:.1f}")
print(f"  Required attrition reduction:{breakeven_reduction_pp:.1%} pp")

# High-priority retention targets: leavers with high perf AND high salary
high_value_leavers = leavers_df[
    (leavers_df["perf_rating"] >= 3) &
    (leavers_df["salary"] >= 70000)
].copy()
print(f"\nHigh-value leavers (perf >= 3 AND salary >= $70k):")
print(f"  Count: {len(high_value_leavers)}")
print(f"  Total replacement cost: ${high_value_leavers['replacement_cost'].sum():,.0f}")
print(high_value_leavers[["emp_id","department","role_level","salary","perf_rating","replacement_cost"]].to_string(index=False))

# Attrition rate pivot: department x salary band
pivot_attr = pd.pivot_table(
    df,
    index      = "department",
    columns    = "salary_band",
    values     = "left",
    aggfunc    = "mean",
    fill_value = np.nan
)

# Force column order: Low → Mid → High
pivot_attr = pivot_attr.reindex(columns=["Low","Mid","High"])
pivot_pct  = (pivot_attr * 100).round(0)
print("Attrition rate (%) by department x salary band:")
print(pivot_pct.to_string())

# Replacement cost pivot: total cost per department x salary band
pivot_cost = pd.pivot_table(
    leavers_df,
    index      = "department",
    columns    = "salary_band",
    values     = "replacement_cost",
    aggfunc    = "sum",
    fill_value = 0
).reindex(columns=["Low","Mid","High"], fill_value=0)
print("\nTotal replacement cost ($) by department x salary band:")
print(pivot_cost.to_string())

# Attrition by role level
role_attr = df.groupby("role_level")["left"].mean().round(3)
role_order = ["Junior","Mid","Senior"]
role_attr.index = pd.CategoricalIndex(role_attr.index, categories=role_order, ordered=True)
role_attr = role_attr.sort_index()
print("\nAttrition rate by role level:")
print((role_attr * 100).round(0).to_string())