Data Cleaning — HR Analytics¶
Setup and Audit¶
import pandas as pd
import numpy as np
df = pd.read_csv("WA_Fn-UseC_-HR-Employee-Attrition.csv")
print(f"Shape: {df.shape}")
print(f"Missing: {df.isnull().sum().sum()}")
print(f"Duplicates: {df.duplicated().sum()}")
Step 1 — Drop Constant Columns¶
# These columns have no variance — they're useless for analysis
constant_cols = ["EmployeeCount", "StandardHours", "Over18"]
for col in constant_cols:
print(f"{col}: {df[col].unique()}")
df = df.drop(columns=constant_cols + ["EmployeeNumber"])
print(f"Shape after dropping: {df.shape}")
Step 2 — Convert Target to Binary¶
df["Attrition_flag"] = (df["Attrition"] == "Yes").astype(int)
print(f"Attrition rate: {df['Attrition_flag'].mean():.1%}")
Step 3 — Decode Ordinal Categories for Readability¶
satisfaction_map = {1: "Low", 2: "Medium", 3: "High", 4: "Very High"}
wlb_map = {1: "Bad", 2: "Good", 3: "Better", 4: "Best"}
education_map = {1: "Below College", 2: "College", 3: "Bachelor", 4: "Master", 5: "Doctor"}
df["JobSatisfaction_label"] = df["JobSatisfaction"].map(satisfaction_map)
df["WorkLifeBalance_label"] = df["WorkLifeBalance"].map(wlb_map)
df["Education_label"] = df["Education"].map(education_map)
Step 4 — Feature Engineering¶
# Tenure buckets
df["tenure_bucket"] = pd.cut(
df["YearsAtCompany"],
bins=[-1, 2, 5, 10, 50],
labels=["0-2 yrs", "3-5 yrs", "6-10 yrs", "10+ yrs"]
)
# Age buckets
df["age_bucket"] = pd.cut(
df["Age"],
bins=[17, 30, 40, 50, 100],
labels=["18-30", "31-40", "41-50", "50+"]
)
# Income bands
df["income_band"] = pd.qcut(df["MonthlyIncome"], q=4,
labels=["Q1 (Lowest)", "Q2", "Q3", "Q4 (Highest)"])
# Promotion stagnation flag
df["overdue_promotion"] = (df["YearsSinceLastPromotion"] >= 5).astype(int)
# Long commute flag
df["long_commute"] = (df["DistanceFromHome"] > 15).astype(int)
Step 5 — Validate Logical Consistency¶
# YearsAtCompany should not exceed TotalWorkingYears
inconsistent = df[df["YearsAtCompany"] > df["TotalWorkingYears"]]
print(f"Logically inconsistent rows: {len(inconsistent)}")
# YearsInCurrentRole should not exceed YearsAtCompany
inconsistent2 = df[df["YearsInCurrentRole"] > df["YearsAtCompany"]]
print(f"Role tenure > company tenure: {len(inconsistent2)}")
Step 6 — Correlation with Attrition¶
# Numeric correlation with attrition flag
numeric_cols = df.select_dtypes(include=[np.number]).columns
corr_with_attrition = (
df[numeric_cols].corr()["Attrition_flag"]
.drop("Attrition_flag")
.sort_values(key=abs, ascending=False)
)
print("Top correlates with attrition:")
print(corr_with_attrition.head(10).round(3))
Expected top correlates:
OverTime (encoded) +0.246
TotalWorkingYears -0.171
JobLevel -0.169
YearsInCurrentRole -0.160
MonthlyIncome -0.160
Age -0.159
StockOptionLevel -0.137
YearsAtCompany -0.134
What the signs mean
Positive correlation (OverTime) = more of it → more attrition. Negative correlation (income, tenure, age) = more of it → less attrition. Younger, lower-paid, newer employees who work overtime are the highest risk.