How to clean a messy HR data using Python

Overview of the dataset

The HR dataset we’re working with represents a typical real-world dataset that hasn’t been fastidiously curated.

You may obtain the messy HR dataset utilizing this GitHub link.

Listed below are the precise points current within the dataset:

1. Inconsistent formatting

• Numerical Columns with Textual content Entries: Columns like ‘Age’ and ‘Wage’ include each numeric and textual content entries. For instance, the ‘Age’ column embrace values comparable to “30”, “twenty-five”, and “thirty”, which ought to all be numeric. Equally, the ‘Wage’ column consists of numbers represented as phrases like “SIXTY THOUSAND” alongside numeric values like “60000”.
• Areas in Textual content Fields: A number of text-based columns, comparable to ‘Title’, ‘Division’, and ‘Place’, have further main or trailing areas.

2. Incorrect knowledge varieties:

• Dates Saved as Strings: The ‘Becoming a member of Date’ column, which needs to be in a date format, is saved as a string with various codecs. As an example, you’ll encounter dates like “2021-01-15”, “15/01/2021”, and “January 15, 2021” throughout the similar column, making it tough to carry out time-based evaluation or comparisons.

3. Lacking values

• Essential Columns with Lacking Knowledge: A number of important columns, comparable to ‘E mail’, and ‘Telephone Quantity’ have lacking entries. This could result in inaccurate analyses if not dealt with correctly.

4. Placeholder and Invalid Knowledge

• Incorrect Placeholders: Some fields include invalid placeholders that shouldn’t be current. For instance, the ‘Wage’ column have placeholders like “NAN” as a substitute of correct null values or numeric entries which might result in errors throughout numerical calculations.
• Inconsistent or Incorrect Telephone Numbers and Emails: Contact data fields like ‘Telephone Quantity’ and ‘E mail’ include invalid or placeholder knowledge, which have to be recognized and cleaned for correct record-keeping.

Step 1: Loading the messy dataset

First, let’s load the dataset utilizing pandas and numpy, that are highly effective libraries in Python for knowledge manipulation.

 import pandas as pd 
 import numpy as np 
 
 # Load the messy knowledge 
 file_path_large_messy = '/path_to/messy_HR_data.csv' 
 df_large_messy = pd.read_csv(file_path_large_messy)  

NB: Please change the “path_to” with the precise location of the saved messy knowledge in your system.

Because the HR datasets will not be fastidiously curated, they include further areas round textual content. These may cause issues throughout evaluation. Let’s take away these pointless areas.

 # Strip main and trailing areas from string columns 
 for column in df_large_messy.select_dtypes(embrace=['object']).columns: 
 df_large_messy[column] = df_large_messy[column].str.strip() 

Step 3: Correcting the “Age” column

Within the ‘Age’ column, you’ll encounter textual representations of numbers (like ‘thirty’ as a substitute of 30) or incorrect knowledge varieties. We’ll standardise this column by changing all values to numeric and dealing with any errors.

 # Right the Age column 
 df_large_messy['Age'] = df_large_messy['Age'].change('thirty', 30) 
 df_large_messy['Age'] = pd.to_numeric(df_large_messy['Age'], errors="coerce") 

# Fill NaN values within the Age column earlier than changing to int
df_large_messy['Age'] = df_large_messy['Age'].fillna(0).spherical(0).astype(int)  # Take away decimals and convert to int 

Step 4: Cleansing the “Wage” column

Equally, the ‘Wage’ column does include inconsistent knowledge, comparable to numbers written in phrases or placeholders for lacking knowledge. Let’s clear these up.

# Right the Wage column 
 df_large_messy['Salary'] = df_large_messy['Salary'].str.change('SIXTY THOUSAND', '60000') 
 df_large_messy['Salary'] = df_large_messy['Salary'].str.change(' NAN ', 'NaN') 
 df_large_messy['Salary'] = pd.to_numeric(df_large_messy['Salary'], errors="coerce") 

Step 5: Standardising date codecs

Dates in datasets have inconsistent format. We’ll standardise the ‘Becoming a member of Date’ column to make sure it’s in a constant date format.

 # Standardise the Becoming a member of Date column 
 df_large_messy['Joining Date'] = pd.to_datetime(df_large_messy['Joining Date'], errors="coerce")

Step 6: Dealing with lacking values

Lacking values can distort evaluation, so we have to deal with them fastidiously. We’ll fill in lacking numerical knowledge with the imply and categorical knowledge with essentially the most frequent worth or a placeholder.

# Fill NaN values with a default worth or technique 
 df_large_messy['Age'].fillna(df_large_messy['Age'].imply(), inplace=True) 
 df_large_messy['Salary'].fillna(df_large_messy['Salary'].imply(), inplace=True) 
 
 # Fill NaN values for categorical columns 
 df_large_messy['Gender'].fillna('Unknown', inplace=True) 
 df_large_messy['Department'].fillna(df_large_messy['Department'].mode()[0], inplace=True) 
 df_large_messy['Position'].fillna(df_large_messy['Position'].mode()[0], inplace=True) 
 df_large_messy['Performance Score'].fillna('Unknown', inplace=True) 
 df_large_messy['Email'].fillna('no_email@instance.com', inplace=True) 
 df_large_messy['Phone Number'].fillna('000-000-0000', inplace=True) 

Step 7: Saving the cleaned dataset

Lastly, after all of the cleansing, we save the cleaned dataset to a brand new file for additional evaluation.

 # Save the cleaned knowledge to a brand new CSV file 
 df_large_messy.to_csv('/path_to/cleaned_messy_HR_data.csv', index=False) 

NB: Please change the “path_to” with the precise location the place you want to the cleaned dataset to be saved in your system.

You may obtain the cleaned HR dataset utilizing this github link.

Abstract of information cleansing steps

Loading the Dataset

• Import crucial libraries (pandas, numpy).
• Load the dataset from a CSV file right into a pandas DataFrame.

• Take away main and trailing areas from string columns utilizing the str.strip() technique.

Correcting Knowledge Varieties

• Convert textual content representations within the ‘Age’ and ‘Wage’ columns to numeric utilizing pd.to_numeric(), dealing with non-numeric values gracefully.
• Substitute particular string representations (e.g., changing “thirty” with 30 within the ‘Age’ column).

Standardising Date Codecs

• Convert the ‘Becoming a member of Date’ column to a uniform datetime format utilizing pd.to_datetime(), dealing with errors to keep away from knowledge loss.

Dealing with Categorical Variables

• Standardise categorical entries (e.g., ‘Division’, ‘Place’) by formatting strings for consistency (like changing all to title case).

Coping with Lacking Values

• Determine and fill lacking numerical values utilizing the imply or median of the column.
• Fill lacking categorical values with the mode of the column or a delegated placeholder (e.g., ‘Unknown’ for ‘Gender’).

Remaining Knowledge Validation

• Carry out remaining checks to make sure that all columns are within the appropriate format and that there are not any remaining null values.
• Use df.data() and df.head() to visually examine the cleaned dataset.

Saving the Cleaned Knowledge

• Save the cleaned dataset again to a CSV file for additional evaluation or operational use, guaranteeing no index column is included.

You may obtain the cleaned HR dataset utilizing this GitHub link.

Beneath is only a snippet of the cleaned dataset:

Conclusion

Cleansing a messy dataset is a crucial step within the data analysis course of. By utilizing Python and pandas, you may effectively deal with widespread points like inconsistent formatting, lacking values, and incorrect knowledge varieties.

This tutorial has walked you thru the important steps to remodel a messy HR dataset right into a clear, usable format prepared for evaluation. With these expertise, you’ll be higher geared up to deal with real-world knowledge challenges in your initiatives.