DEV Community: Ruto Kipkirui Robert

How I Cleaned Messy HR Dataset Using PostgreSQL: A Step-by-Step Guide

Ruto Kipkirui Robert — Mon, 14 Sep 2026 10:15:29 +0000

Introduction

At first glance, I realized that the HR dataset was an absolute nightmare. The dataset was characterized by spelling errors, missing values, and corrupted data formats. In this article, I am sharing simple yet detailed step-by-step processes that any beginner in the data field can use to safely clean these records and make them ready for intuitive business reporting.

Project Objectives

This project transformed raw, unstructured HR data into an analysis-ready dataset.

Project outline

Phase 1. Create Database
Phase 2. Initial Data Quality Assessment Report
Phase 3. Data Cleaning Steps Employed
Step 1: Rename and Standardize Column Heads
Step 2: Based on employee_id; Identify and Remove Duplicates, Missing Rows and Special Characters
Step 3. Categorical Columns Cleaning and Standardization
Step 4. Numerical and Date Columns Cleaning and Standardization
Phase 4. Final Quality Report
Phase 5.Data Export

Data Cleaning Documentation

Phase 1. Create Database
Step 1. Create Database

I used the already created MyPracticeProjects database.

Step 2: Create Schema

I created the HR_Data_Cleaning_Project schema.
Having created a schema, I imported the data directly using the PostgreSQL import wizard.
I loaded all columns as TEXT to avoid import errors.

Step 3: Create a Working Copy/Backup Table/Staging Table

Never clean the original data.
Always preserve the raw data.

create table hr_cleaned as select *from hr_dirty_datacsv;

Phase 2. Initial Data Quality Assessment Report

Initial run to profile and understand the dirty text data before modifications.

Phase 3. Data Cleaning Steps Employed

Step 1: Rename and Standardize Column Heads

Needed to rename and standardize all column names
To replace awkward capitalization and spaces with a clean snake_case format.

alter table hr_cleaned rename column "Employee ID" to employee_id;
alter table hr_cleaned rename column "First Name" to first_name;
alter table hr_cleaned rename column "Last Name" to last_name;
alter table hr_cleaned rename column "Department" to department;
alter table hr_cleaned rename column "Salary" to salary;
alter table hr_cleaned rename column "Hire Date" to hire_date;
alter table hr_cleaned rename column "Age" to age;
alter table hr_cleaned rename column "Gender" to gender;
alter table hr_cleaned rename column "Performance Score" to performance_score;
alter table hr_cleaned rename column "Full-Time" to full_time;
alter table hr_cleaned rename column "Bonus" to bonus;
alter table hr_cleaned rename column "Marital Status" to marital_status;
alter table hr_cleaned rename column "Education Level" to education_level;
alter table hr_cleaned rename column "Work Experience (Years)" to work_experience_years;
alter table hr_cleaned rename column "Employee Type" to employee_type;
alter table hr_cleaned rename column "Office Location" to office_location;
alter table hr_cleaned rename column "Project Count" to project_count;
alter table hr_cleaned rename column "Last Promotion Year" to last_promotion_year;
alter table hr_cleaned rename column "Remote Work Status" to remote_work_status;
alter table hr_cleaned rename column "Annual Training Hours" to annual_training_hours;
alter table hr_cleaned rename column "Manager Feedback Score" to manager_feedback_score;

Step 2: Based on employee_id, identify and remove duplicates and special characters.

Before removing duplicate records and missing rows, standardize the employee_id column.
Step 1. Removed Text From employee_id

update hr_cleaned hc 
set employee_id=10081
where employee_id='EMP-10081';

update hr_cleaned hc 
set employee_id=10308
where employee_id='EMP-10308';

update hr_cleaned hc 
set employee_id=10383
where employee_id= 'EMP-10383';

update hr_cleaned hc 
set employee_id=10540
where employee_id= 'EMP-10540';

Step 2. Removed null employee ids

delete from hr_cleaned
where employee_id=null or employee_id='';

select count(*) as count_of_employees 
from hr_cleaned hc ;--852

select count(*) as count_of_employees 
from hr_dirty_datacsv hdd ;--876

Step 3. Trim the employee_id column to remove leading and trailing spaces.

select trim(employee_id)
from hr_cleaned;

Step 4. Find and preview duplicates: -_ First Approach_

select employee_id,
count(*) as total_duplicates
from hr_cleaned hc 
group by employee_id
having count(*) > 1;--There are 17 duplicates as per employee_id

Second Approach

- Identify using row_number

with duplicates as 
(
    select employee_id,
    row_number() over (
    partition by employee_id
    order by employee_id
    ) as ranked
    from hr_cleaned
)
select *
from duplicates
where ranked > 1;

Having identified duplicates, delete them using ctid.

- Why ctid?

_ctid is PostgreSQL's internal row identifier.
It uniquely identifies each physical row.
It is commonly used for removing duplicates when there is no unique primary key._

with duplicates as 
(
select ctid,
row_number() over (
partition by employee_id
order by employee_id
) as ranked
from hr_cleaned
)
delete from hr_cleaned
where ctid in 
(
select ctid
from duplicates
where ranked > 1
);

select count(*)
from hr_cleaned; -- After id column cleaning, 852 records remained.

Step 3. Categorical Columns Cleaning and Standardization

Fix category misspellings, typos, and missing values
both NULLs and blank strings.
Count missing values per column:

select
    sum(case when employee_id is null or trim(employee_id) = '' then 1 else 0 end) as missing_employee_id,
    sum(case when first_name is null or trim(first_name) = '' then 1 else 0 end) as missing_first_name,
    sum(case when last_name is null or trim(last_name) = '' then 1 else 0 end) as missing_last_name,
    sum(case when department is null or trim(department) = '' then 1 else 0 end) as missing_department,
    sum(case when gender is null or trim(gender) = '' then 1 else 0 end) as missing_gender,
    sum(case when full_time is null or trim(full_time) = '' then 1 else 0 end) as missing_full_time,    
    sum(case when marital_status is null or trim(marital_status) = '' then 1 else 0 end) as missing_marital_status,
    sum(case when education_level is null or trim(education_level) = '' then 1 else 0 end) as missing_education_level,    
    sum(case when employee_type is null or trim(employee_type) = '' then 1 else 0 end) as missing_employee_type,
    sum(case when office_location is null or trim(office_location) = '' then 1 else 0 end) as missing_office_location,    
    sum(case when remote_work_status is null or trim(remote_work_status) = '' then 1 else 0 end) as missing_remote_work_status    
from hr_cleaned;

- Address Nissing Values

For text or categorical columns, i replaced blank fields with a placeholder "Unknown"

update hr_cleaned
set department = 'Unknown'
where department is null or department = '';

update hr_cleaned
set gender = 'Unknown'
where gender is null or gender = '';

update hr_cleaned
set full_time = 'Unknown'
where full_time is null or full_time = '';

update hr_cleaned
set marital_status = 'Unknown'
where marital_status is null or marital_status = '';

update hr_cleaned
set education_level = 'Unknown'
where education_level is null or education_level = '';

update hr_cleaned
set employee_type = 'Unknown'
where employee_type is null or employee_type = '';

update hr_cleaned
set office_location = 'Unknown'
where office_location is null or office_location = '';

update hr_cleaned
set remote_work_status = 'Unknown'
where remote_work_status is null or remote_work_status = '';

Standard Categories Per Column

Standardize department

select distinct trim(department)
from hr_cleaned hc ;

update hr_cleaned hc 
set department = 'IT' 
where department in ('Information Tech','I.T','Info Tech');

update hr_cleaned hc 
set department = 'HR' 
where department in ('Humna Resources','Hr','H.R','human resources','Humman Res.','Human Resources','Human Resource');

update hr_cleaned hc 
set department = 'Operations' 
where department in ('Operatons','Ops');

update hr_cleaned hc 
set department = 'Finance' 
where department in ('finance','Finanace');

update hr_cleaned hc 
set department = 'Sales' 
where department in ('Sale');

update hr_cleaned hc 
set department = 'Marketing' 
where department in ('Markting');

Standardize gender

select distinct trim(gender)
from hr_cleaned hc ;

update hr_cleaned hc 
set gender = 'Female' 
where gender in ('female','Femle','F');

update hr_cleaned hc 
set gender = 'Male' 
where gender in ('M','MALE','male');

update hr_cleaned hc 
set gender = 'Not Provided' 
where gender in ('Prefer not say','');

Standardize marital status

select distinct trim(marital_status) 
from hr_cleaned hc ;

update hr_cleaned hc 
set marital_status= 'Widowed' 
where hc.marital_status  in ('Widwowed');

update hr_cleaned hc 
set marital_status= 'Married' 
where hc.marital_status  in ('maried');

update hr_cleaned hc 
set marital_status= 'Single' 
where hc.marital_status  in ('single');

standardize education_level

select distinct trim(education_level) 
from hr_cleaned hc  

update hr_cleaned hc 
set education_level= 'High School' 
where hc.education_level   in ('high school','High Sch');

update hr_cleaned hc 
set education_level= 'PhD' 
where hc.education_level   in ('PHD','phd');

update hr_cleaned hc 
set education_level= 'Masters' 
where hc.education_level   in ('MSc','Master''s');

update hr_cleaned hc 
set education_level= 'Bachelors' 
where hc.education_level   in ('Bachelor','Bachelor''s','Bachelor''s','Bachelor');

update hr_cleaned hc 
set education_level= 'Associates' 
where hc.education_level   in ('Associate''s');

standardize employee_type

select distinct trim(employee_type) 
from hr_cleaned hc 

update hr_cleaned hc 
set employee_type= 'Contract' 
where hc.employee_type    in ('Contrct','contractor');

update hr_cleaned hc 
set employee_type= 'Permanent' 
where hc.employee_type    in ('Perm','permanent');

update hr_cleaned hc 
set employee_type= 'Intern' 
where hc.employee_type    in ('Inten','intern');

standardize office_location

select distinct trim(office_location) 
from hr_cleaned hc 

update hr_cleaned hc 
set office_location= 'San Fransisco' 
where hc.office_location   in ('SF','San Francisco');

update hr_cleaned hc 
set office_location= 'London' 
where hc.office_location   in ('Londn');

update hr_cleaned hc 
set office_location= 'Nairobi' 
where hc.office_location   in ('NAIROBI','Nairob');

update hr_cleaned hc 
set office_location= 'Berlin' 
where hc.office_location   in ('Berln');

update hr_cleaned hc 
set office_location= 'Tokyo' 
where hc.office_location   in ('Tokio');


update hr_cleaned hc 
set office_location= 'Unknown' 
where hc.office_location   in ('Remote');

Step 4. Numerical and Date Columns Cleaning and Standardization

Fix word numbers, nan, N/A, & data types
Column By Column Cleanup
Check data types

select column_name, data_type
from information_schema.columns
where table_schema = 'HR_Data_Cleaning_Project'
and table_name = 'hr_cleaned'
order by  ordinal_position;--Indicates that all columns are text (Character Varying)

1. Salary Column

Remove both currency prefixes

update hr_cleaned
set salary = trim(replace(replace(replace(salary, 'KES ', ''),'$', ''),',',''));

--Convert to integer

alter table hr_cleaned
alter column salary type integer
using nullif(trim(salary), '')::integer;

3. Age Column

update hr_cleaned 
set age=30
where age='thirty';

alter table hr_cleaned
alter column age type numeric
using nullif(trim(age), '')::numeric;

4.performance_score Column

--Check for formatting problems
select distinct performance_score
from hr_cleaned
where performance_score is not null
order by performance_score;

--There two issues, Excellent ,N/A and Poor

update hr_cleaned hc 
set performance_score=9
where performance_score='Excellent';

update hr_cleaned hc 
set performance_score=0
where performance_score='Poor';

update hr_cleaned hc 
set performance_score=''
where performance_score='N/A';

--Now convert data type

alter table hr_cleaned 
alter column performance_score type numeric
using nullif(trim(performance_score), '')::numeric;

5.bonus Column

--Check for formatting problems
select distinct bonus
from hr_cleaned
where bonus is not null
order by bonus;-- The formatting issue is KES and -

--Remove KES

update hr_cleaned hc 
set bonus= trim(replace(replace(replace(bonus,'KES',''),'-',''),'N/A',''));

--Now convert

alter table hr_cleaned 
alter column bonus type numeric
using nullif(trim(bonus),'')::numeric;

6. work_experience_years

--Check for formatting problems
select distinct work_experience_years
from hr_cleaned
where work_experience_years is not null
order by work_experience_years;

--Remove years,N/A, -

update hr_cleaned hc 
set work_experience_years= trim(replace(replace(replace(work_experience_years,'years',''),'-',''),'N/A',''));

--Now convert

alter table hr_cleaned 
alter column work_experience_years type numeric
using nullif(trim(work_experience_years),'')::numeric;

7.project_count

--Check for formatting problems
select distinct project_count
from hr_cleaned
where project_count is not null
order by project_count;

--Remove ten,N/A, -

update hr_cleaned hc 
set project_count= trim(replace(replace(project_count,'ten','10'),'N/A',''));

--Now convert

alter table hr_cleaned 
alter column project_count type numeric
using nullif(trim(project_count),'')::numeric;

8.last_promotion_year Column

--Check for formatting problems
select distinct last_promotion_year
from hr_cleaned;


--Replace never and N/A with blank

update hr_cleaned hc 
set last_promotion_year=''
where last_promotion_year='Never';

update hr_cleaned hc 
set last_promotion_year=''
where last_promotion_year='N/A';

--Now convert

alter table hr_cleaned 
alter column last_promotion_year type date
using to_date (nullif(trim(last_promotion_year),''),'YYYY');

9. annual_training_hours

--Check for formatting problems
select distinct annual_training_hours
from hr_cleaned;


--Replace never and N/A with blank

update hr_cleaned hc 
set annual_training_hours=''
where annual_training_hours='None';

update hr_cleaned hc 
set annual_training_hours=''
where annual_training_hours='N/A';

--Now convert

alter table hr_cleaned 
alter column annual_training_hours type numeric
using nullif(trim(annual_training_hours),'')::numeric;

10. manager_feedback_score

--Check for formatting problems
select distinct manager_feedback_score
from hr_cleaned;


--Replace Good and N/A with blank

update hr_cleaned hc 
set manager_feedback_score=4
where manager_feedback_score='Good';

update hr_cleaned hc 
set manager_feedback_score=''
where manager_feedback_score='N/A';

--Now convert

alter table hr_cleaned 
alter column manager_feedback_score type numeric
using nullif(trim(manager_feedback_score),'')::numeric;

Handle Remaining Missing Values (Numerical Columns)

Identify Missing Values

select    
    sum(case when salary is null  then 1 else 0 end) as missing_salary,
    sum(case when hire_date is null or trim(hire_date) = '' then 1 else 0 end) as missing_hire_date,
    sum(case when age is null  then 1 else 0 end) as missing_age,    
    sum(case when performance_score is null  then 1 else 0 end) as missing_performance_score,    
    sum(case when bonus is null  then 1 else 0 end) as missing_bonus,    
    sum(case when work_experience_years is null  then 1 else 0 end) as missing_work_experience_years,    
    sum(case when project_count is null then 1 else 0 end) as missing_project_count,
    sum(case when last_promotion_year is null then 1 else 0 end) as missing_last_promotion_year,    
    sum(case when annual_training_hours is null  then 1 else 0 end) as missing_annual_training_hours,
    sum(case when manager_feedback_score is null then 1 else 0 end) as missing_manager_feedback_score
from hr_cleaned;

Replace Missing Values with Column Median

Except for hire_date and last promotion_year (just change data types)
Salary Column

-- Claculate median
select percentile_cont(0.5) within group(order by salary) as median_salary
from hr_cleaned hc 

--Now replace null values
update hr_cleaned hc 
set salary=76023.0
where salary is null;

Age Column

select percentile_cont(0.5) within group(order by age) as median_age
from hr_cleaned hc 

--Now replace null values
update hr_cleaned hc 
set age=41
where age is null;

performance_score Column

select percentile_cont(0.5) within group(order by performance_score) as performance_score
from hr_cleaned hc 

--Now replace null values
update hr_cleaned hc 
set performance_score=5
where performance_score is null;

bonus column

select percentile_cont(0.5) within group(order by bonus) as median_bonus
from hr_cleaned hc 

--Now replace null values
update hr_cleaned hc 
set bonus=5097.905000000001
where bonus is null;

work_experience_years Column

select percentile_cont(0.5) within group(order by work_experience_years) as median_work_experience_years
from hr_cleaned hc 

--Now replace null values
update hr_cleaned hc 
set work_experience_years=20
where work_experience_years is null;

project_count Column

select percentile_cont(0.5) within group(order by project_count ) as median_project_count 
from hr_cleaned hc 

--Now replace null values
update hr_cleaned hc 
set project_count =10
where project_count  is null;

annual_training_hours Column

alter table hr_cleaned 
alter column annual_training_hours type int 
using nullif(trim(annual_training_hours), '')::integer;


select percentile_cont(0.5) within group(order by annual_training_hours) as median_annual_training_hours
from hr_cleaned hc 

--Now replace null values
update hr_cleaned hc 
set annual_training_hours=107.5
where annual_training_hours is null;

manager_feedback_score Column

select percentile_cont(0.5) within group(order by manager_feedback_score) as median_manager_feedback_score
from hr_cleaned hc 

--Now replace null values
update hr_cleaned hc 
set manager_feedback_score=3.1
where manager_feedback_score is null;

Phase 4. Final Quality Report

Perform final checks:

row count
duplicate check
missing values
data types
summary statistics

Phase 5. Data Export

Python Pandas to the Rescue: The Ultimate Data Cleanup Guide

Ruto Kipkirui Robert — Thu, 10 Sep 2026 15:47:15 +0000

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:

Convert letters to lowercase (.str.lower())
Remove leading/trailing spaces (.str.strip())
Replace non-alphanumeric characters/spaces with underscores (.str.replace('[^a-z0-9_]', '_'))
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

Counted duplicate records using
Inspected duplicated rows using
Removed duplicated rows using
Inspected and permanently dropped rows with missing employee_id
Inspect rows with missing employee_id
Permanently drop rows where employee_id is null
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

How Excel is Used in Real-World Data Analysis

Ruto Kipkirui Robert — Sat, 06 Jun 2026 15:38:51 +0000

Introduction

In today's fast-paced business environments, data is considered the cornerstone of decision-making, policy formulation, and other organizational needs. MS Excel is a robust spreadsheet developed by Microsoft for organizing, analyzing, and visualizing data in rows and columns. In the data science and analytics domain, MS Excel is critical for analyzing and managing data to generate insights that enhance decision-making. Excel's polarity is characterized by its ease of use, flexibility, automation, and visualization.

Ways Excel Is Used in Real-World Data Analysis

Across the data science and analytics domain, MS Excel is frequently employed in the following ways;

a) Data Cleaning and Preprocessing

At the beginning of every data science and analytics project, data cleaning is required, and MS Excel is the primary tool. Typical Excel features and functions applied during data cleaning include Text to Columns, Remove Duplicates, Find and Replace, and Power Query.

b) Exploratory Data Analysis

Before performing data science and analytics activities, it is crucial to understand the dataset at hand, its structure, and trends. MS Excel features Pivot Tables, Pivot Charts, and Slicers that provide instant aggregation, sorting, and visualizations.

c) Data Analysis and Reporting

Modern organizations and businesses operate based on insights generated from data. MS Excel features such as pivot tables, charts, and conditional formatting help data analysts analyze and visualize data for clear, actionable insights that enhance decision-making.

MS Excel Features or Formulas

The typical MS Excel features and formulas employed in the data science and analytics domain include the following.

Data Cleaning Functions

Function	Purpose	Example	Result
`UPPER()`	Converts text to uppercase	`=UPPER("john")`	JOHN
`LOWER()`	Converts text to lowercase	`=LOWER("JOHN")`	john
`PROPER()`	Capitalizes the first letter of each word	`=PROPER("john doe")`	John Doe
`TRIM()`	Removes extra spaces from text	`=TRIM(" John Doe ")`	John Doe
`LEFT()`	Extracts characters from the left side	`=LEFT("John",2)`	Jo
`RIGHT()`	Extracts characters from the right side	`=RIGHT("John",2)`	hn
`MID()`	Extracts characters from the middle of the text	`=MID("John",2,2)`	oh
`LEN()`	Returns the number of characters in a text string	`=LEN("John")`	4
`FIND()`	Returns the position of a character or substring (case-sensitive)	`=FIND("o", "John")`	2
`SEARCH()`	Returns the position of a character or substring (not case-sensitive)	`=SEARCH("o", "JOHN")`	2
`SUBSTITUTE()`	Replaces specific text within a string	`=SUBSTITUTE("John Doe", "Doe", "Smith")`	John Smith
`REPLACE()`	Replaces text based on position	`=REPLACE("John Doe",6,3,"Smith")`	John Smith
`CONCAT()`	Combines multiple text strings	`=CONCAT("John"," ", "Doe")`	John Doe
`TEXTJOIN()`	Combines text with a specified delimiter	`=TEXTJOIN(", ", TRUE, "John", "Doe")`	John, Doe

Statistical Functions

Function	Purpose	Example	Result
`AVERAGE()`	Calculates the mean value	`=AVERAGE(B2:B10)`	Average of values
`MEDIAN()`	Returns the middle value	`=MEDIAN(B2:B10)`	Median value
`MODE()`	Returns the most frequent value	`=MODE(B2:B10)`	Most common value
`MIN()`	Returns the smallest value	`=MIN(B2:B10)`	Minimum value
`MAX()`	Returns the largest value	`=MAX(B2:B10)`	Maximum value
`COUNT()`	Counts cells containing numbers	`=COUNT(B2:B10)`	Number of numeric cells
`COUNTA()`	Counts non-empty cells	`=COUNTA(B2:B10)`	Number of filled cells
`COUNTBLANK()`	Counts empty cells	`=COUNTBLANK(B2:B10)`	Number of blank cells
`STDEV.S()`	Calculates sample standard deviation	`=STDEV.S(B2:B10)`	Sample variability
`STDEV.P()`	Calculates population standard deviation	`=STDEV.P(B2:B10)`	Population variability
`VAR.S()`	Sample variance	`=VAR.S(B2:B10)`	Sample variance
`VAR.P()`	Population variance	`=VAR.P(B2:B10)`	Population variance
`LARGE()`	Returns the nth largest value	`=LARGE(B2:B10,1)`	Largest value
`SMALL()`	Returns the nth smallest value	`=SMALL(B2:B10,1)`	Smallest value
`RANK()`	Returns the rank of a value	`=RANK(B2,B2:B10)`	Position in list
`PERCENTILE()`	Returns a percentile value	`=PERCENTILE(B2:B10,0.75)`	75th percentile

Personal Reflection

As a beginner data scientist, learning MS Excel as a foundational tool has changed how I interact with data. While previously I thought it was all about big tools like SQL and Python, I have come to appreciate that Excel is a source of clean datasets and can also be used at scale to generate insights that improve decision-making.

Git and Git Bash for Beginners

Ruto Kipkirui Robert — Sun, 25 Jan 2026 12:52:51 +0000

Git and Git Bash for Beginners
Introduction to Git Bash
Git Bash is a command-line tool that provides Git command-line functionality.
It enables developers to work in a Bash-like terminal environment while efficiently managing Git repositories.
It supports core Git operations such as cloning repositories, committing changes, pushing and pulling updates, and managing branches.
Overview of Git
Git is a distributed version control system (DVCS).
Git is a tool that helps you:
• save and manage different versions of your files and code.
• work with others, keep track of changes, and undo mistakes.
Git allows each developer to maintain a complete copy of a repository, including its full history, on their local machine.
This decentralized model improves performance, reliability, and collaboration.
Developers can work offline, experiment freely, and merge changes efficiently.
Why Use Git Bash?
Git Bash is widely used because of its compatibility, flexibility, and power.
It is fully compatible with Git and supports all Git commands.
Git Bash also offers a familiar environment for users transitioning from Linux or macOS to Windows, reducing the learning curve.
Installing Git Bash on Windows
Installing Git Bash involves downloading the Git for Windows installer and following a guided setup process. Users can select components, choose an installation directory, and complete the installation. Once installed, Git Bash can be launched from the Start menu or desktop shortcut.
Basic Git Bash Commands
Git Bash supports Git commands that help users navigate directories, manage files, and control version history.
Common Navigation Commands:

ls – Lists files and directories
cd – Changes the current directory
pwd – Displays the current working directory Common Git Commands:
git init – Initializes a Git repository
git status – Displays repository status
git add. – Stages all changes
git commit -m "message" – Commits changes
git log – Displays commit history Using Git Bash: Basic Workflow Using Git Bash begins by configuring Git with a username and email address. After configuration, users navigate to a project directory, initialize a repository, stage files, and commit changes. Key Commands: git config --global user.name "Your Name" git config --global user.email "you@example.com" Connecting Local Repositories to GitHub Git Bash allows users to link local repositories to remote GitHub repositories. This enables pushing local changes to GitHub and pulling updates from collaborators. Key Commands: git remote add origin git push origin master Branch Management in Git Bash Branches allow multiple developers to work on different features independently. Git Bash supports creating, switching, listing, and deleting branches. Key Commands:
git branch – Lists branches
git branch branch_name – Creates a new branch
git checkout -b branch_name – Creates and switches to a branch Merging and Cloning Repositories Merging combines changes from one branch into another, ensuring code integration. Cloning creates a local copy of a remote repository. Key Commands: git merge branch_name git clone Undoing Commits Git Bash allows users to modify the most recent commit using the --amend option. This is useful when files are missed or commit messages need correction. Key Command: git commit --amend Conclusion Git Bash is a powerful and flexible tool that enables. It supports version control, collaboration, automation, and branch management. With its combination of Git commands and Bash utilities, Git Bash remains an essential tool for developers seeking efficiency and control in modern software development environments.