# ====================================================================
# Week 4: Data Cleaning and Preparation
# Kmex Consult - R Data Science Course
# Open in RStudio and run each block with Ctrl+Enter (Cmd+Enter on Mac).
# ====================================================================

# The most important step in data science

# ====================================================================
# WEEK 4 OVERVIEW
# ====================================================================
# In real data science, 70% of time is spent on data cleaning.
# This week teaches you how to clean data professionally.
# REALITY OF DATA SCIENCE:
# Most datasets are "messy":
# - Missing values (blanks, NA, NULL)
# - Duplicates (same record multiple times)
# - Inconsistent formats ("01/01/2023" vs "01-01-2023")
# - Typos ("Jhon" instead of "John")
# - Outliers (extreme values)
# - Wrong data types (age stored as text)
# - Inconsistent categories ("USA", "US", "United States")
# CLEANING WORKFLOW:
# 1. Load and inspect data
# 2. Check for missing values
# 3. Remove duplicates
# 4. Fix data types
# 5. Handle outliers
# 6. Standardize text
# 7. Handle dates properly
# 8. Validate data quality
# 9. Document all decisions
# 10. Create clean dataset
# KEY GOALS THIS WEEK:
# ✓ Identify data quality issues
# ✓ Remove/handle missing values appropriately
# ✓ Detect and handle duplicates
# ✓ Fix inconsistent data types
# ✓ Standardize categorical values
# ✓ Handle dates and times
# ✓ Detect and handle outliers
# ✓ Validate cleaned data
# ✓ Document cleaning process
# ✓ Create reproducible cleaning pipeline

# ====================================================================
# SESSION 1: MISSING VALUES AND DUPLICATES
# ====================================================================

# --- Duration: 2 hours ---

# --- 1.1 Understanding and Handling Missing Data ---
# MISSING DATA - FUNDAMENTAL PROBLEM:
# Why data is missing:
# - Data not collected
# - Measurement failed
# - Data lost/corrupted
# - Not applicable
# - Respondent refused
# TYPES OF MISSING:
# 1. MCAR - Missing Completely At Random
# - Missingness unrelated to data
# - Safe to remove
# - Example: Random sensor failure
# 2. MAR - Missing At Random
# - Missingness related to other variables
# - Can impute carefully
# - Example: Higher income people skip salary question
# 3. MNAR - Missing Not At Random
# - Missingness related to the missing value itself
# - Problematic
# - Example: Unemployed skip income question
# IDENTIFYING MISSING DATA:


df <- tibble(
name = c("Alice", "Bob", NA, "David"),
age = c(25, NA, 28, 30),
score = c(85, 90, 78, NA)
)

# Check for missing
is.na(df$age) # [1] FALSE TRUE FALSE FALSE
sum(is.na(df)) # Total missing values = 3
colSums(is.na(df)) # Missing per column
rowSums(is.na(df)) # Missing per row

# Percentage missing
colMeans(is.na(df)) * 100 # 33% age, 33% score

# Visualize missing pattern
library(VIM)
aggr(df, prop = TRUE) # Shows missing pattern

# HANDLING MISSING VALUES:
# OPTION 1: REMOVE (Safest, but lose data)


# Remove rows with ANY missing value
df_clean <- df |> drop_na()

# Remove rows missing in specific column
df_clean <- df |> drop_na(age)

# Remove rows missing in any of these columns
df_clean <- df |> drop_na(age, score)

# When to use: Missing is MCAR, small amount missing (<5%)

# OPTION 2: IMPUTATION (Keep data, estimate values)


# Mean imputation (for numeric)
df_imputed <- df |>
mutate(age = replace_na(age, mean(age, na.rm=TRUE)))

# Forward/backward fill (for time series)
df_imputed <- df |>
fill(age, .direction = "down") # Forward fill

# Multiple imputation (advanced, better)
library(mice)
df_imputed <- complete(mice(df, m=5))

# OPTION 3: KEEP AS SPECIAL CATEGORY (for categorical)


# Create new category for missing
df <- df |>
mutate(age_category = case_when(
age < 30 ~ "Young",
age >= 30 ~ "Adult",
is.na(age) ~ "Unknown"
))

# BEST PRACTICES:
# ✓ Investigate WHY data is missing
# ✓ Document your approach
# ✓ Don't hide missing data
# ✓ Check assumptions when imputing
# ✓ Consider domain knowledge
# ✗ Don't silently remove data
# ✗ Don't always use mean imputation



DUPLICATES - ANOTHER COMMON PROBLEM:

# df <- tibble(
# id = c(1, 1, 2, 2, 3),
# name = c("Alice", "Alice", "Bob", "Bob", "Charlie")
# )
# # Detect duplicates
# duplicated(df) # [1] F T F T F
# which(duplicated(df)) # [1] 2 4 (row numbers)
# # Count duplicates per ID
# df |>
# group_by(id) |>
# filter(n() > 1) # Shows duplicated records
# # Remove exact duplicates (keep first)
# df_unique <- df |> distinct()
# # Remove duplicates by specific column
# df_unique <- df |> distinct(id, .keep_all = TRUE)
# # Find and investigate duplicates BEFORE removing
# df_dup <- df |>
# filter(duplicated(id)) |>
# arrange(id)
# # INVESTIGATE - Why duplicated? Bug? Real records?
# # Then decide how to handle



HANDLING DUPLICATES:

# # Option 1: Keep only first occurrence
# df_clean <- df |> distinct(id, .keep_all = TRUE)
# # Option 2: Keep record with most complete data
# df_clean <- df |>
# mutate(missing_count = rowSums(is.na(across(everything())))) |>
# group_by(id) |>
# slice_min(missing_count) |>
# ungroup() |>
# select(-missing_count)
# # Option 3: Aggregate duplicates
# df_summary <- df |>
# group_by(id) |>
# summarize(
# name = first(name), # Keep first
# count = n() # Count occurrences
# )

# --- 1.2 Data Type Fixes and Standardization ---
# WRONG DATA TYPES - SILENT KILLER:


# Load data - everything becomes character!
df <- read_csv("data.csv")

# Check types
str(df)
# May see: age = "25" instead of numeric

# FIX: Specify types when reading
df <- read_csv("data.csv",
col_types = cols(
age = col_integer(),
salary = col_double(),
hire_date = col_date(),
department = col_factor()
)
)

# Fix after loading
df <- df |>
mutate(
age = as.numeric(age),
score = as.integer(score),
employed = as.logical(employed),
hire_date = as.Date(hire_date)
)

# Problems with conversion
as.numeric("25") # [1] 25 (works!)
as.numeric("25 years") # [1] NA (fails!)
as.numeric("$1,000") # [1] NA (fails!)

# Solution: Clean first, then convert
df <- df |>
mutate(
age_clean = str_remove(age, " years"),
age = as.numeric(age_clean)
) |>
select(-age_clean)

# STANDARDIZING TEXT/CATEGORIES:


# Inconsistent categories - common problem
df <- tibble(
city = c("New York", "new york", "NYC", "ny", "NEW YORK"),
status = c("Active", "active", "ACTIVE", "inactive", "Active")
)

# Standardize: lowercase
df <- df |>
mutate(city = tolower(city))
# Result: "new york", "new york", "nyc", "ny", "new york"

# Standardize: Create mapping
city_mapping <- c(
"new york" = "New York",
"nyc" = "New York",
"ny" = "New York",
"new york" = "New York"
)
df <- df |>
mutate(city = tolower(city)) |>
mutate(city = recode(city, !!!city_mapping))

# Better: Use factor with levels
df <- df |>
mutate(status = factor(tolower(status),
levels = c("active", "inactive")))

# Trim whitespace (very common issue!)
df <- df |>
mutate(across(where(is.character), str_trim))

# Remove special characters
df <- df |>
mutate(name = str_remove_all(name, "[^a-zA-Z ]"))

# DATES AND TIMES:


# Common date formats that fail
dates <- c("01/15/2023", "2023-01-15", "15-01-2023")

# Specify format explicitly
dates_fixed <- as.Date(dates, format = "%m/%d/%Y")
# OR
dates_fixed <- as.Date(dates, format = "%Y-%m-%d")

# lubridate package (easier!)
library(lubridate)
dates_fixed <- mdy(c("01/15/2023", "01-15-2023"))
dates_fixed <- ymd("2023-01-15")
dates_fixed <- dmy("15-01-2023")

# Extract parts of dates
today <- Sys.Date()
year(today) # [1] 2024
month(today) # [1] 1
day(today) # [1] 15
wday(today) # [1] 2 (Monday)
week(today) # [1] 3 (week number)

# Calculate differences
df <- tibble(
hire_date = ymd("2020-01-15"),
today_date = Sys.Date()
)
df <- df |>
mutate(tenure_years = as.numeric(
today_date - hire_date) / 365.25)

# OUTLIERS - EXTREME VALUES:


df <- tibble(
age = c(25, 28, 30, 125, 32, 29), # 125 is outlier
salary = c(50000, 55000, 60000, 200000, 58000, 52000)
)

# Detect outliers - IQR method
detect_outliers_iqr <- function(x) {
Q1 <- quantile(x, 0.25, na.rm=TRUE)
Q3 <- quantile(x, 0.75, na.rm=TRUE)
IQR <- Q3 - Q1
lower <- Q1 - 1.5 * IQR
upper <- Q3 + 1.5 * IQR
x < lower | x > upper
}

# Find outliers
df |>
mutate(is_outlier_age = detect_outliers_iqr(age),
is_outlier_sal = detect_outliers_iqr(salary))

# Z-score method
df <- df |>
mutate(age_zscore = scale(age)[,1],
is_outlier = abs(age_zscore) > 3)

# Visualize outliers
library(ggplot2)
ggplot(df, aes(x = age)) +
geom_boxplot() + # Shows outliers
geom_point(aes(color = is_outlier))

# DECIDE: Keep or remove?
# - Keep if real (e.g., CEO salary)
# - Remove if data error (e.g., age = 125)
# - Document decision!