Module 3 Lab Companion

from pathlib import Path
import pandas as pd

# Load the protected Week 3 raw employee data.
raw_path = Path("../data/raw/employee_data.csv")
df = pd.read_csv(raw_path)

df.head()

# How many rows and columns?
df.shape

# What data types did Pandas assign?
df.info()

# Count missing values by column
df.isna().sum()

# First look at all rows for this small demo dataset
df

# Keep the raw df unchanged and clean a copy
raw_df = df.copy()
df_clean = df.copy()

# Start a simple cleaning log
cleaning_log = []

print("Raw data shape:", raw_df.shape)
print("Working copy shape:", df_clean.shape)

# Convert employee_id to a numeric value.
# Invalid values such as "error" become NaN.
df_clean["employee_id"] = pd.to_numeric(df_clean["employee_id"], errors="coerce")

# Use pandas nullable integer type so missing IDs can exist temporarily
df_clean["employee_id"] = df_clean["employee_id"].astype("Int64")

cleaning_log.append("Converted employee_id to numeric using pd.to_numeric(errors='coerce'). Invalid IDs became missing values.")

df_clean[["employee_id"]]

# Show the original salary values before conversion
df_clean["salary"]

# Remove dollar signs and commas, then convert to numeric
salary_cleaned = (
    df_clean["salary"]
    .astype("string")
    .str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
    .str.strip()
)

df_clean["salary"] = pd.to_numeric(salary_cleaned, errors="coerce")

cleaning_log.append("Removed dollar signs and commas from salary, then converted salary to numeric. Invalid salary values became missing values.")

df_clean[["salary"]]

df_clean["age"] = pd.to_numeric(df_clean["age"], errors="coerce")

cleaning_log.append("Converted age to numeric using pd.to_numeric(errors='coerce'). Invalid age values became missing values.")

df_clean[["age"]]

df_clean["hire_date"] = pd.to_datetime(
    df_clean["hire_date"],
    errors="coerce",
    format="mixed",
)

cleaning_log.append("Converted hire_date to datetime with mixed-format parsing and errors='coerce'. Invalid dates became NaT.")

df_clean[["hire_date"]]

df_clean.info()

df_clean.isna().sum()

# Full-row duplicate count
df_clean.duplicated().sum()

# Key-based duplicate count for employee_id
df_clean["employee_id"].duplicated().sum()

# Show rows with duplicated employee_id values
# keep=False marks all occurrences of the duplicate ID, not just the later ones.
df_clean[df_clean["employee_id"].duplicated(keep=False)].sort_values("employee_id")

before_rows = df_clean.shape[0]

df_clean = df_clean.drop_duplicates(subset=["employee_id"], keep="first")

after_rows = df_clean.shape[0]
rows_removed = before_rows - after_rows

cleaning_log.append(f"Removed {rows_removed} duplicate row(s) based on employee_id, keeping the first occurrence.")

print("Rows before:", before_rows)
print("Rows after:", after_rows)
print("Rows removed:", rows_removed)

df_clean.isna().sum()

# Show rows with any missing values
missing_rows = df_clean[df_clean.isna().any(axis=1)]
missing_rows

before_rows = df_clean.shape[0]

df_clean = df_clean.dropna(subset=["employee_id", "hire_date"])

after_rows = df_clean.shape[0]
rows_removed = before_rows - after_rows

cleaning_log.append(f"Dropped {rows_removed} row(s) with missing critical employee_id or hire_date.")

print("Rows before:", before_rows)
print("Rows after:", after_rows)
print("Rows removed:", rows_removed)

# Calculate medians before filling missing values
salary_median = df_clean["salary"].median()
age_median = df_clean["age"].median()

print("Salary median:", salary_median)
print("Age median:", age_median)

# Fill missing salary and age values with their medians
df_clean["salary"] = df_clean["salary"].fillna(salary_median)
df_clean["age"] = df_clean["age"].fillna(age_median)

cleaning_log.append(f"Imputed missing salary values with the median salary: {salary_median}.")
cleaning_log.append(f"Imputed missing age values with the median age: {age_median}.")

df_clean[["salary", "age"]].head()

# Show unusually high ages
suspicious_age = df_clean[df_clean["age"] > 100]
suspicious_age

if not suspicious_age.empty:
    cleaning_log.append("Flagged age values above 100 for further review. These were not automatically changed in this lab.")

df_clean.info()

df_clean.isna().sum()

df_clean.shape

df_clean.head()

for i, item in enumerate(cleaning_log, start=1):
    print(f"{i}. {item}")

processed_dir = Path("../data/processed")
processed_dir.mkdir(parents=True, exist_ok=True)

output_path = processed_dir / "employee_data_cleaned_week3.csv"
df_clean.to_csv(output_path, index=False)
print(f"Cleaned data saved to: {output_path}")

# Offline Assignment
offline_check = pd.read_csv(output_path)
offline_check.head()

# Offline Assignment - Complete the four TODO values.
# Hints:
# - Use .duplicated().sum() on employee_id for duplicate IDs.
# - Select the named columns first, then use .isna().sum().sum() for missing totals.
# - Use a Boolean condition on age, then sum the True values.
offline_validation_summary = pd.Series({
    "total_rows": len(offline_check),
    "unique_nonmissing_employee_ids": offline_check["employee_id"].dropna().nunique(),
    "duplicate_employee_ids": None,  # TODO
    "missing_critical_values": None,  # TODO: employee_id plus hire_date
    "missing_numeric_values": None,  # TODO: salary plus age
    "ages_above_100": None,  # TODO
}, name="result")

offline_validation_summary

practice_raw = pd.DataFrame({
    "employee_id": ["201", "202", "202", "bad", "205"],
    "salary": ["$52,000", "61,000", "$61,000", "not available", ""],
    "hire_date": ["2024-01-15", "02/01/2024", "02/01/2024", "bad date", "2024-03-10"],
    "age": ["29", "unknown", "35", "41", "150"],
    "department": ["Sales", "Finance", "Finance", "IT", "Operations"],
})

practice_raw

# Run this self-check after you create practice_clean.
if "practice_clean" not in globals():
    print("Complete the optional practice first, then rerun this self-check.")
else:
    practice_check = pd.Series({
        "raw_rows_unchanged": len(practice_raw) == 5,
        "clean_rows": len(practice_clean),
        "duplicate_employee_ids": practice_clean["employee_id"].duplicated().sum(),
        "missing_salary_or_age": practice_clean[["salary", "age"]].isna().sum().sum(),
        "ages_above_100": (practice_clean["age"] > 100).sum(),
    }, name="result")
    display(practice_check)
    print("Expected: 3 clean rows, 0 duplicate IDs, 0 missing salary/age values, and 1 age above 100.")