Python Pandas to the Rescue: The Ultimate Data Cleanup Guide

Introduction

Every data science, analytics, business intelligence, and any other data-related report depends on a clean dataset. However, almost 90% of the time, we can't clean and process to begin with. In most cases, the datasets are characterized by inconsistent formatting, varying levels of missing values, and other structural anomalies.

While there are different tools for data preprocessing, Python's pandas is often utilized. Thus, to provide a detailed understanding of the pandas library's capabilities for data preparation, this project focused on HR data. The primary steps in data preprocessing were as follows.

Phase 1: Imported the Required Libraries and Loaded the Dataset

• Required libraries were imported as pandas (as pd) and numpy (as np).
• Read the raw dataset using pd.read_excel

#Import required libraries
import pandas as pd
import numpy as np

#Load Dataset
hrdata= pd.read_excel(r"C:\Users\HP\OneDrive\Desktop\LuxDev Tutorials\Python Projects\HR Data Analysis Project.ipynb\HR_Dirty_Data.xlsx")

Phase 2: Initial Data Quality Assessment

• Constructed an initial quality_report DataFrame to evaluate:
• Column names and total column count
• Data types per column
• Total row count
• Missing value counts and missing percentages
• Unique value counts per column

quality_report = pd.DataFrame({
    "Column": hrdata.columns,
    "Total Columns": len(hrdata.columns),
    "Data_Type": hrdata.dtypes.astype(str),
    "Total_Rows": len(hrdata),
    "Missing_Values": hrdata.isna().sum().values,
    "Missing_Percentage": (hrdata.isna().mean() * 100).round(2).values,
    "Unique_Values": hrdata.nunique().values,
})

quality_report

Phase 3: Data Cleaning Steps Employed

Step 1: Renamed and Standardized Column Heads

• Explored existing column headings using

print(f"Current Column Names Heading\n",hrdata.columns)

• Cleaned headers automatically using the created clean_column_names() function to:

1. Convert letters to lowercase (.str.lower())
2. Remove leading/trailing spaces (.str.strip())
3. Replace non-alphanumeric characters/spaces with underscores (.str.replace('[^a-z0-9_]', '_'))
4. Eliminate consecutive underscores (.str.replace('+', ''))

def clean_column_names(hrdata):
    """Converts all column headers to lowercase, replaces spaces with underscores, 
    and removes special characters automatically."""
    hrdata.columns = (
        hrdata.columns
        .str.lower()
        .str.strip()
        .str.replace('[^a-z0-9_]', '_', regex=True)  # Replaces spaces, hyphens, & () with _
        .str.replace('_+', '_', regex=True)          # Fixes double underscores like __
        .str.strip('_')                              # Removes trailing/leading underscores
    )
    return hrdata

# call the function
hrdata = clean_column_names(hrdata)

#Final Columns
print(f"Cleaned Headings\n",hrdata.columns)

Step 2: Identified and Removed Duplicates and Missing Values

1. Counted duplicate records using
2. Inspected duplicated rows using
3. Removed duplicated rows using
4. Inspected and permanently dropped rows with missing employee_id
5. Inspect rows with missing employee_id
6. Permanently drop rows where employee_id is null
7. Verify that nulls are now

# Check duplicate rows
print("Duplicates before:", hrdata.duplicated().sum())

# Remove duplicate rows
hrdata = hrdata.drop_duplicates()

#OR hrdata.drop_duplicates(inplace=True)

# Verify
print("Duplicates after:", hrdata.duplicated().sum())

# Inspected rows with missing employee_id 
missing_id_rows = hrdata[hrdata['employee_id'].isnull()]

# Permanently dropped rows where employee_id is null 
hrdata = hrdata.dropna(subset=["employee_id"])

# Verified that nulls are now 0
print(hrdata["employee_id"].isnull().sum())  # Now correctly returns 0

Step 3: Categorical Columns Cleaning and Standardization
Selected object/string/category columns

# Selected columns containing text/categorical data

categorical_columns = hrdata.select_dtypes(include=['object', 'string', 'category']).columns

# Displayed the categorical column names
categorical_columns

String Normalization & Typos Correction
Cleaned first_name and last_name by removing whitespace and applying title casing

# Clean and title case names
hrdata['first_name'] = hrdata['first_name'].str.strip().str.title()
hrdata['last_name'] = hrdata['last_name'].str.strip().str.title()

# Correctly verify column data types
print("first_name column dtype:", hrdata['first_name'].dtype)
print("last_name column dtype:", hrdata['last_name'].dtype)

department: Resolved misspellings (e.g., Finance, Ops, Human Resources, I.T) into standard departments.

# Convert to title case and remove leading and trailing spaces
hrdata["department"] = hrdata["department"].str.strip().str.title()

# List unique categories in 'department'
print(hrdata["department"].unique())

# See each category and how many times it appears
print(hrdata["department"].value_counts())

# Standardize categories only to Finance, Human Resources, Operations, Marketing, Information Tech, Sales, and Unknown
hrdata["department"] = hrdata["department"].replace({
    "Finanace": "Finance", 
    "Hr": "Human Resources", "H.R": "Human Resources", "Humna Resources": "Human Resources", "Human Resource": "Human Resources", "Humman Res.": "Human Resources",
    "Markting": "Marketing",
    "Ops": "Operations", "Operatons": "Operations",
    "Sale": "Sales",
    "I.T": "Information Tech", "Info Tech": "Information Tech", "It": "Information Tech",
    "nan": "Unknown"
})

# Verify the result
print(hrdata["department"].value_counts())

# Check data type
print(hrdata["department"].dtype)

# Check current null count
print("Nulls before:", hrdata['department'].isnull().sum())

# Fill null values in department with 'Unknown'
hrdata['department'] = hrdata['department'].fillna('Unknown')

# Verify null count is now 0
print("Nulls after:", hrdata['department'].isnull().sum())

gender: Standardized values to Male, Female, and Unknown.

# Convert to tiltle case and remove leading and training spaces
hrdata['gender']=hrdata['gender'].str.strip().str.title()

# List unique categories in 'Gender'
print(hrdata["gender"].unique())

# See each category and how many times it appears
print(hrdata["gender"].value_counts())

#  Standardize categories only to  Male,  Female ,Unknown ,Information Tech, Sales,and Unknown
# Replace 'nan', 'Prefer Not Say' to 'Unknown'
hrdata["gender"] = hrdata["gender"].replace({
    "M": "Male", 
    "F": "Female", "Femle": "Female",
    "Prefer Not Say": "Unknown","nan": "Unknown"
    })

# Verify the result
print(hrdata["gender"].value_counts())

#Check data type
print(type('gender'))

# Check current null count
print("Nulls before:", hrdata['gender'].isnull().sum())

# Fill null values in gender with 'Unknown'
hrdata['gender'] = hrdata['gender'].fillna('Unknown')

# Verify null count is now 0
print("Nulls after:", hrdata['gender'].isnull().sum())

full_time: Converted varied inputs (Y, Full Time, N, Part-Time) to Yes / No.

# Convert to tiltle case and remove leading and training spaces
hrdata['full_time']=hrdata['full_time'].str.strip().str.title()

# Replace Missing Values
hrdata['full_time'] = hrdata['full_time'].fillna('Unknown')

# See each category and how many times it appears
print(hrdata["full_time"].value_counts())

# Standardize attributes
hrdata["full_time"]=hrdata["full_time"].replace({
    "Y":"Yes","Full Time":"Yes",
    "N":"No","Part-Time":"No",
    "nan":"Unknown"
})

# Count category values
print(hrdata["full_time"].value_counts())

#Check data type
print(type('first_time'))

# Check current null count
print("Nulls before:", hrdata['full_time'].isnull().sum())

# Address missing values
hrdata['full_time'] = hrdata['full_time'].fillna('Unknown')

# Verify null count is now 0
print("Nulls after:", hrdata['full_time'].isnull().sum())

marital_status: Corrected typos (e.g., Widwowed, married) to standard statuses.

#Remove  leading and trailing  spaces and change to title case
#hrdata['marital_status'].str.title().str.strip() # transform only
hrdata['marital_status'] = hrdata['marital_status'].str.strip().str.title() # transform and save

#Identify  unique categories in the column
print(hrdata['marital_status'].unique())

#Count unique categories in the column
hrdata['marital_status'].value_counts()

#Standardize inconsistent categories
hrdata['marital_status']=hrdata['marital_status'].replace({
    "Widwowed":"Widowed",
    "maried":"Married",
    "single":"Single",
    "nan":"Unknown"
})

#Check data type
print(type('marital_status'))

# Check current null count
print("Nulls before:", hrdata['marital_status'].isnull().sum())

# Address Missing Values
hrdata['marital_status'] = hrdata['marital_status'].fillna('Unknown')

# Verify null count is now 0
print("Nulls after:", hrdata['marital_status'].isnull().sum())

education_level: Standardized values (PHD, high school, Bachelor's) into standard academic tiers.

#Remove  leading and trailing  spaces and change to title case
hrdata['education_level']=hrdata['education_level'].str.title().str.strip()

#Identify  unique categories in the column
print(hrdata['education_level'].unique())

#Count unique categories in the column
hrdata['education_level'].value_counts()

#Standardize inconsistent categories
hrdata['education_level']=hrdata['education_level'].replace({
    "PHD":"PhD","phd":"PhD",
    "Associates":"Associate's",
    "high school":"High School","High Sch":"High School",
    "Bachelor":"Bachelors","Bachelor's":"Bachelors",
    "Master's":"Masters", "MSc":"Masters",
    "nan":"Unknown"
})

#Check data type
print(type('education_level'))

# Check current null count
print("Nulls before:", hrdata['education_level'].isnull().sum())

# Address missing values
hrdata['education_level'] = hrdata['education_level'].fillna('Unknown')

# Verify null count is now 0
print("Nulls after:", hrdata['education_level'].isnull().sum())

employee_type: Standardized values (Inten, Perm, Contrct) into formal employment types.

# Convert the column to title case and remove leading and trailing spaces
hrdata['employee_type']=hrdata['employee_type'].str.title().str.strip()

# List usnique categories in the column
print(hrdata['employee_type'].unique())

#Count all unique categories
hrdata['employee_type'].value_counts()

#Standardize Inconsistent categories

hrdata['employee_type']=hrdata['employee_type'].replace({
    "Inten":"Intern",
    "Perm":"Permanent",
    "Contractor":"Contract","Contrct":"Contract",
    "nana":"Unknown"
})

#Check data type
print(type('employee_type'))

# Check current null count
print("Nulls before:", hrdata['employee_type'].isnull().sum())

# Address missing values
hrdata['employee_type'] = hrdata['employee_type'].fillna('Unknown')

# Verify null count is now 0
print("Nulls after:", hrdata['employee_type'].isnull().sum())

office_location: Standardized geographic names (e.g., Nairob, SF, Londn, Tokio).

#Remove leading and trailing  spaces and  change to title cae
hrdata['office_location']=hrdata['office_location'].str.title().str.strip()

#Identify unique categories in the column
print(hrdata['office_location'].unique())

#Count unique categories in the column
hrdata['office_location'].value_counts()

#Standardize inconsistent Categories
hrdata['office_location']=hrdata['office_location'].replace({
    "Nairob":"Nairobi","NAIROBI":"Nairobi",
    "San Fransisco":"San Francisco","SF":"San Francisco",
    "Londn":"London",
    "Tokio":"Tokyo",
    "Berln":"Berlin",
    " nan":"Unknown"
})

#Check data type
print(type('office_location'))

# Check current null count
print("Nulls before:", hrdata['office_location'].isnull().sum())

# Address missing values
hrdata['office_location'] = hrdata['office_location'].fillna('Unknown')

# Verify null count is now 0
print("Nulls after:", hrdata['office_location'].isnull().sum())

remote_work_status: Consolidated entries into On-Site, Remote, and Hybrid.

#Remove leading and trailing  spaces and  chnage to title cae
hrdata['remote_work_status']=hrdata['remote_work_status'].str.title().str.strip()

#Identify unique categories in the column
print(hrdata['remote_work_status'].unique())

#Count unique categories in the column
hrdata['remote_work_status'].value_counts()

#Standardize inconsistent Categories
hrdata['remote_work_status']=hrdata['remote_work_status'].replace({
    "On site":"On-Site","on-site":"On-Site","Onsite":"On-Site",
    "Fully Remote":"Remote","fully remote":"Remote",
    "Hybird":"Hybrid","hybrid":"Hybrid",
    "nan":"Unknown"
})

#Check data type
print(type('remote_work_status'))

# Check current null count
print("Nulls before:", hrdata['remote_work_status'].isnull().sum())

# Address missing values
hrdata['remote_work_status'] = hrdata['remote_work_status'].fillna('Unknown')

# Verify null count is now 0
print("Nulls after:", hrdata['remote_work_status'].isnull().sum())

Step 4: Numerical and Date Field Transformation

• Selected Categorical columns

# Select and display the names of all true numerical columns
numeric_columns = hrdata.select_dtypes(include=['number']).columns
list(numeric_columns)

1. Salary: Stripped currency symbols ($, KES), commas, and spaces using regular expressions (r'[\$, KRES\s]'), cast values to floats using pd.to_numeric(), and imputed missing entries with the column median.

# Converted salary values to strings and removed special characters
hrdata['salary'] = hrdata['salary'].astype(str).str.replace(r'KES|\$|,|\s', '', regex=True)

# Converted the clean text back into floats 
hrdata['salary'] = pd.to_numeric(hrdata['salary'], errors='coerce')

# Counted missing values before  imputation with column median
print(f"Missing values before:",hrdata['salary'].isnull().sum())

# Calculated the median salary 
median_salary = hrdata['salary'].median()
print(f"Median salary is: {median_salary}\n")

# Filled the 9 missing spaces with the median value
hrdata['salary'] = hrdata['salary'].fillna(median_salary)

# confirmed that there are no remaining missing values
print("Missing values after:", hrdata['salary'].isnull().sum())

# Changed data type back to numeric
hrdata['salary'] = pd.to_numeric(hrdata['salary'], errors='coerce')

2. Hire Date: Converted dates using pd.to_datetime(..., errors='coerce') and filled missing/invalid dates with the column median.

# Changed the column to a date format
hrdata['hire_date'] = pd.to_datetime(hrdata['hire_date'], errors='coerce')

# Checked for missing and broken dates 
missing_dates = hrdata['hire_date'].isnull().sum()
print(f"Missing values: {missing_dates}")

# Retained invalid/missing dates as NaT instead of replacing  missing values
hrdata['hire_date'] = pd.to_datetime(hrdata['hire_date'], errors='coerce')

3. Age: Converted written text (thirty) to numeric digits, extracted numbers via regex (r'(\d+.?\d*)'), filtered out invalid age outliers (keeping values between 18 and 75), imputed missing values with the median, and cast to integer.

# Converted to string, lowercase, and changed written words to numbers(thirty)
hrdata['age'] = hrdata['age'].astype(str).str.lower().str.strip()
hrdata['age'] = hrdata['age'].str.replace('thirty', '30')

# Extracted only numbers 
hrdata['age'] = hrdata['age'].str.extract(r'(\d+\.?\d*)')

# Changed to  numerical float type 
hrdata['age'] = pd.to_numeric(hrdata['age'], errors='coerce')

# Checked missing values before
print(f"Missing values before:",hrdata['age'].isnull().sum())

# Filled all missing values with median age
median_age = hrdata['age'].median()
hrdata['age'] = hrdata['age'].fillna(median_age).astype(int) # Convert to integer at the end

#Confirmed missing values after imputation
print(f"Missing values after:",hrdata['age'].isnull().sum())

4. Performance Score: Coerced text to numbers, imputed missing entries using the median, and cast to an integer.

# Changed to numeric datatype
hrdata['performance_score'] = pd.to_numeric(hrdata['performance_score'], errors='coerce')

# Filled missing values with  column median
median_score = hrdata['performance_score'].median()
hrdata['performance_score'] = hrdata['performance_score'].fillna(median_score).astype(int)

5. Bonus: Stripped currency/punctuation characters ($, KES, -, spaces), converted to numeric, filled missing values with 0, and rounded to 2 decimal places.

# Stripped out currency tags (KES, $) commas, and spaces using a regular expression
hrdata['bonus'] = hrdata['bonus'].astype(str).str.replace(r'[\$,KRES\s-]', '', regex=True)

# Changed to numeric data type

hrdata['bonus'] = pd.to_numeric(hrdata['bonus'], errors='coerce')

# Fill all missing values/NaNs with 0 (Assuming no data means $0 bonus)
hrdata['bonus'] = hrdata['bonus'].fillna(0)

6. Work Experience Years: Extracted numerical patterns from text, removed negative values, filled missing entries with the median experience, and converted to an integer.

# Converted to string, lowercase it, and remove extra spaces
hrdata['work_experience_years'] = hrdata['work_experience_years'].astype(str).str.lower().str.strip()

# Extracted only numbers and decimals
hrdata['work_experience_years'] = hrdata['work_experience_years'].str.extract(r'(\d+\.?\d*)')

# Chnaged numeric data type
hrdata['work_experience_years'] = pd.to_numeric(hrdata['work_experience_years'], errors='coerce')

# Handled negative numbers (I chnaged any value below 0 as a missing value (NaN))
hrdata.loc[hrdata['work_experience_years'] < 0, 'work_experience_years'] = np.nan

# Filled missing values using column median
median_experience = hrdata['work_experience_years'].median()
hrdata['work_experience_years'] = hrdata['work_experience_years'].fillna(median_experience).astype(int)

7. Project Count: Replaced word numbers (ten) with numeric strings (10), converted to numeric, filled missing values with the median, and cast to integer.

# Replace({'ten': '10'})
hrdata['project_count'] = hrdata['project_count'].astype(str).str.lower().str.strip().replace({'ten': '10'})

# Changed data type numeric
hrdata['project_count'] = pd.to_numeric(hrdata['project_count'], errors='coerce')

print(f"Missing values before:", hrdata['project_count'].isnull().sum())

#  Replaced missing values with column median
hrdata['project_count'] = hrdata['project_count'].fillna(hrdata['project_count'].median()).astype(int)

print(f"Missing values after:", hrdata['project_count'].isnull().sum())

8. Last Promotion Year: Coerced text entries (e.g., Never, N/A) to NaN, filled missing records with 0 (indicating no promotion), and cast to integer.

# Changed to numeric data type 
hrdata['last_promotion_year'] = pd.to_numeric(hrdata['last_promotion_year'], errors='coerce')

# Replaced the new NaN blanks with 0.(0 meant not yet promoted)
hrdata['last_promotion_year'] = hrdata['last_promotion_year'].fillna(0).astype(int)

9. Annual Training Hours: Imputed missing values using the median and cast to an integer.

# Calculated column median
training_median = hrdata['annual_training_hours'].median()

# Replaced missing values with column median
hrdata['annual_training_hours'] = hrdata['annual_training_hours'].fillna(training_median)

# Changed to int data type
hrdata['annual_training_hours'] = hrdata['annual_training_hours'].astype(int)

10. Manager Feedback Score: Mapped textual feedback (Excellent, Good, Poor) to a numeric scale (1–5), converted to a float, filled missing entries with the column median, and rounded to 1 decimal place.

# Created a dictionary to convert feedback words into standard numbers on a 1-5 scale.
score_mapping = {'Excellent': 5, 'Good': 4, 'Poor': 1, 'N/A': np.nan, 'None': np.nan}

# Replaced "Good" and  "Excellent" with their numeric equivalents.
hrdata['manager_feedback_score'] = hrdata['manager_feedback_score'].astype(str).str.strip().replace(score_mapping)

# Changed to numeric data type
hrdata['manager_feedback_score'] = pd.to_numeric(hrdata['manager_feedback_score'], errors='coerce')

# Replaced  missing values with median
feedback_median = hrdata['manager_feedback_score'].median()
hrdata['manager_feedback_score'] = hrdata['manager_feedback_score'].fillna(feedback_median).round(1)

Phase 4: Final Data Quality Report

• Re-generated a final quality_report DataFrame displaying the updated schema, row counts, missing value tallies (0 nulls remaining), and unique value distributions.

quality_report = pd.DataFrame({
    "Column": hrdata.columns,
    "Data_Type": hrdata.dtypes.astype(str),
    "Total_Rows": len(hrdata),
    "Missing_Values": hrdata.isna().sum().values,
    "Missing_Percentage": (hrdata.isna().mean() * 100).round(2).values,
    "Unique_Values": hrdata.nunique().values,
})

quality_report

Phase 5: Data Export

• Exported the sanitized DataFrame to a clean Excel spreadsheet named HR_Cleaned_Data.xlsx with index=False.

# Save cleaned DataFrame to a new Excel file
hrdata.to_excel('HR_Cleaned_Data.xlsx', index=False)

Project Link
https://github.com/ArapzRuto/Data-Science-and-Analytics-Portfolio/tree/main/Python-Based%20Projects/HR-Data-Cleaning-Project