1. Task: Enhance the cleaning script to handle additional columns and edge cases in the dataset.

2. Deliverable: A Python script that successfully cleans and normalizes the employees.csv dataset, addressing all identified issues.

Cleaning and Normalizing Employee Profiles

Objective

The goal of this hands-on activity is to clean and normalize the employees.csv dataset to ensure the data is consistent, accurate, and ready for downstream tasks such as embedding generation or machine learning.

Dataset Description

• File Name: employees.csv

• Sample Columns:

  • name: Employee names, which may include special characters or inconsistent capitalization.

  • role: Job roles, which may have duplicates or inconsistencies.

  • department: Department names, which might contain missing values or special characters.

Steps to Clean and Normalize the Dataset

1. Load the Dataset

• Task: Use the pandas library to load the dataset and inspect its structure.

Sample Code:

python

Copy code

import pandas as pd

# Load the dataset

file_path = "employees.csv"

df = pd.read_csv(file_path)

# Display the first few rows

print(df.head())

2. Remove Duplicates

• Reason: Duplicate records can distort analysis and lead to incorrect conclusions.

Task: Use drop_duplicates() to remove duplicate rows.

Sample Code:

python

Copy code

# Remove duplicate rows

df.drop_duplicates(inplace=True)

# Verify

print(f"Number of rows after removing duplicates: {len(df)}")

3. Handle Missing Values

• Reason: Missing data can lead to errors in processing or skewed results.

Task: Use fillna() to fill missing values with appropriate defaults or placeholders.

Sample Code:

python

Copy code

# Fill missing values with placeholders

df['role'].fillna('Unknown Role', inplace=True)

df['department'].fillna('Unknown Department', inplace=True)

# Verify

print(df.isnull().sum())

4. Normalize Text Data

• Reason: Inconsistent capitalization and special characters can create redundancies and reduce data quality.

Task: Normalize text data by:

1. Converting to lowercase.

2. Removing special characters and punctuation.

Sample Code:

python

Copy code

import string

# Function to clean and normalize text

def clean_text(text):

    text = text.lower()  # Convert to lowercase

    text = text.translate(str.maketrans('', '', string.punctuation))  # Remove punctuation

    return text

# Apply text normalization to relevant columns

df['name'] = df['name'].apply(clean_text)

df['role'] = df['role'].apply(clean_text)

df['department'] = df['department'].apply(clean_text)

# Verify

print(df.head())

5. Save the Cleaned Dataset

• Reason: Save the cleaned dataset for reuse and further processing.

Task: Save the cleaned DataFrame as a new CSV file.

Sample Code:

python

Copy code

# Save the cleaned dataset

cleaned_file_path = "employees_cleaned.csv"

df.to_csv(cleaned_file_path, index=False)

print(f"Cleaned data saved to {cleaned_file_path}")

Expected Output

After completing the cleaning and normalization:

• All rows are unique.

• Missing values are replaced with default placeholders.

• Text data is consistently lowercase and free of special characters.

• The cleaned dataset is saved as employees_cleaned.csv.

Extensions

1. Validate Data:

   • Check for additional inconsistencies, such as outliers or incorrect data types.

2. Standardize Columns:

   • Use consistent formats for specific fields, like dates or numeric values.

Validation Code:

python

Copy code

# Check for data type inconsistencies

print(df.info())

# Look for potential outliers

print(df.describe())

Learning Outcomes

By completing this activity, you will:

1. Understand common data cleaning techniques.

2. Learn to handle missing values, duplicates, and inconsistent text data.

3. Normalize data for better usability in analysis or machine learning tasks.

Let me know if you'd like to add more complexity or if you need additional insights! 😊

4o

You said:

Assignment: Task: Enhance the cleaning script to handle additional columns and edge cases in the dataset. Deliverable: A Python script that successfully cleans and normalizes the employees.csv dataset, addressing all identified issues.

ChatGPT said:

ChatGPT

Assignment: Enhance the Cleaning Script

Objective

Enhance the data cleaning script to handle additional columns and address edge cases in the employees.csv dataset, ensuring comprehensive data quality and consistency.

Steps to Complete the Assignment

1. Inspect the Dataset

Before enhancing the script, inspect the dataset to identify additional columns and potential edge cases:

• Missing values in unexpected columns.

• Special characters in unexpected places.

• Numeric columns with outliers or invalid entries.

• Categorical columns with inconsistent entries.

Sample Code for Inspection:

python

Copy code

import pandas as pd

# Load the dataset

file_path = "employees.csv"

df = pd.read_csv(file_path)

# Check for missing values and data types

print(df.info())

print("\nMissing values:\n", df.isnull().sum())

# Display unique values for categorical columns

categorical_columns = ['role', 'department']

for column in categorical_columns:

    print(f"\nUnique values in '{column}':\n", df[column].unique())

# Display statistics for numeric columns

print("\nNumeric column statistics:\n", df.describe())

2. Enhance the Cleaning Script

Key Enhancements:

1. Handle Additional Columns:

   • Ensure numeric columns are cleaned for invalid or outlier values.

   • Normalize categorical columns for uniformity.

2. Address Edge Cases:

   • Remove leading/trailing whitespaces.

   • Handle invalid characters or numeric values in text columns.

   • Replace null values with context-specific placeholders.

Enhanced Cleaning Script

python

Copy code

import pandas as pd

import string

# Function to clean and normalize text

def clean_text(text):

    if pd.isnull(text):  # Handle NaN values

        return ""

    text = text.lower()  # Convert to lowercase

    text = text.strip()  # Remove leading/trailing whitespaces

    text = text.translate(str.maketrans('', '', string.punctuation))  # Remove punctuation

    return text

# Function to handle numeric outliers

def handle_numeric_outliers(column, threshold=1.5):

    q1 = column.quantile(0.25)  # First quartile

    q3 = column.quantile(0.75)  # Third quartile

    iqr = q3 - q1  # Interquartile range

    lower_bound = q1 - threshold * iqr

    upper_bound = q3 + threshold * iqr

    return column.clip(lower_bound, upper_bound)

# Load the dataset

file_path = "employees.csv"

df = pd.read_csv(file_path)

# Remove duplicates

df.drop_duplicates(inplace=True)

# Handle missing values

df['role'].fillna('unknown role', inplace=True)

df['department'].fillna('unknown department', inplace=True)

if 'salary' in df.columns:

    df['salary'].fillna(df['salary'].median(), inplace=True)  # Replace missing salaries with median

# Clean text columns

text_columns = ['name', 'role', 'department']

for column in text_columns:

    df[column] = df[column].apply(clean_text)

# Handle numeric columns

if 'salary' in df.columns:

    df['salary'] = handle_numeric_outliers(df['salary'])

# Normalize categorical columns

if 'employment_type' in df.columns:

    df['employment_type'] = df['employment_type'].str.lower().str.strip()

# Save the cleaned dataset

output_file_path = "employees_cleaned.csv"

df.to_csv(output_file_path, index=False)

print(f"Cleaned dataset saved to {output_file_path}")

Enhancements in the Script

1. Text Cleaning:

   • Removes punctuation and normalizes case.

   • Strips whitespaces and replaces NaN with an empty string.

2. Numeric Handling:

   • Detects and caps outliers in numeric columns like salary using the interquartile range (IQR).

3. Handling Categorical Columns:

   • Normalizes categorical columns to lowercase with no leading/trailing spaces.

4. Missing Value Treatment:

   • Replaces missing values in text and numeric columns with meaningful defaults.

5. Edge Case Coverage:

   • Handles null, empty strings, special characters, and inconsistent formats.

Deliverable

• File: Enhanced Python script (e.g., clean_employees_data.py).

• Output: Cleaned dataset saved as employees_cleaned.csv.

Validation

Run the script and verify the following:

1. No duplicates in the cleaned dataset.

2. All missing values are replaced appropriately.

3. Text columns are normalized and free from special characters.

4. Numeric columns are free of outliers and null values.

Validation Code:

python

Copy code

# Validate the cleaned dataset

cleaned_df = pd.read_csv(output_file_path)

print(cleaned_df.info())

print("\nSample Data:\n", cleaned_df.head())

Learning Outcomes

• Develop advanced data cleaning scripts.

• Handle edge cases systematically.

• Prepare datasets for downstream tasks with high quality and consistency.