# Lab 2-2 Data Profiling
# R CODE ONLY

# =========================
# Setup
# =========================
library(dplyr)
library(ggplot2)
library(lubridate)
library(tidyr)

# =========================
# Dataset Creation
# =========================
set.seed(42)

customer_data <- data.frame(
  CustomerID = c(
    paste0("CUST", sprintf("%03d", 1:18)),
    "CUST019",
    "CUST019"
  ),
  
  Age = c(
    25, 34, NA, 29, 150, 52, 31, 27, 41, 33,
    28, 39, 46, 32, 37, 44, 30, 35, 42, 26
  ),
  
  Income = c(
    45000, 62000, 85000, 51000, 73000,
    -5000, 58000, 49000, 78000, 64000,
    53000, 71000, 89000, 60000, 75000,
    82000, 56000, 67000, 80000, 48000
  ),
  
  PurchaseAmount = c(
    120, 250, 180, 90, 320,
    450, 200, 110, 380, 220,
    130, 290, 410, 190, 310,
    390, 170, 260, 350, 140
  ),
  
  CustomerType = c(
    "Budget", "Premium", "Standard", "Budget", "Premium",
    "Budget", "Standard", "Budget", "Premium", "Standard",
    "Budget", "Standard", "Premium", "Standard", "Standard",
    "Premium", "Budget", "Standard", "Premium", "Budget"
  ),
  
  LastPurchaseDate = c(
    "2024-01-15", "2024-02-20", "2024-01-08", "invalid-date",
    "2024-02-28", "2024-01-22", "2024-03-05", "2024-02-14",
    "2024-01-30", "2024-03-18", "2024-02-10", "2024-01-25",
    "2024-03-22", "2024-02-05", "2024-01-18", "2024-03-08",
    "2024-02-25", "2024-01-12", "2024-03-15", "2024-02-18"
  ),
  
  Email = c(
    "john@email.com", "jane@email.com", "bob@email.com",
    "invalid-email", "mary@email.com", "tom@email.com",
    "lisa@email.com", "mike@email.com", "sarah@email.com",
    "david@email.com", "emma@email.com", "james@email.com",
    "anna@email.com", "peter@email.com", "lucy@email.com",
    "mark@email.com", "kate@email.com", "paul@email.com",
    "nina@email.com", "alex@email.com"
  )
)

print("=== DATASET OVERVIEW ===")

print(
  paste(
    "Dataset dimensions:",
    nrow(customer_data),
    "rows,",
    ncol(customer_data),
    "columns"
  )
)

print("First 6 records:")
head(customer_data)

print("Data structure:")
str(customer_data)

# =========================
# Completeness Analysis
# =========================
print("=== COMPLETENESS ANALYSIS ===")

missing_counts <- customer_data %>%
  summarise_all(~sum(is.na(.)))

print("Missing value counts by column:")
print(missing_counts)

missing_percentages <- customer_data %>%
  summarise_all(
    ~round(sum(is.na(.)) / length(.) * 100, 1)
  )

print("Missing value percentages by column:")
print(missing_percentages)

completeness_summary <- data.frame(
  Column = names(customer_data),
  Total_Records = nrow(customer_data),
  Missing_Count = as.numeric(missing_counts),
  Missing_Percentage = as.numeric(missing_percentages),
  Complete_Records = nrow(customer_data) -
    as.numeric(missing_counts)
)

print("Completeness Summary Table:")
print(completeness_summary)

# =========================
# Consistency Analysis
# =========================
print("=== CONSISTENCY ANALYSIS ===")

consistency_check <- customer_data %>%
  filter(!is.na(Age), Income > 0) %>%
  group_by(CustomerType) %>%
  summarise(
    Count = n(),
    Avg_Income = round(mean(Income), 0),
    Avg_Purchase = round(mean(PurchaseAmount), 0),
    Min_Income = min(Income),
    Max_Income = max(Income),
    .groups = "drop"
  )

print("CustomerType vs Income/Purchase Consistency:")
print(consistency_check)

valid_data <- customer_data %>%
  filter(!is.na(Age), Income > 0, Age <= 100)

age_income_correlation <- cor(
  valid_data$Age,
  valid_data$Income
)

print(
  paste(
    "Age-Income Correlation:",
    round(age_income_correlation, 3)
  )
)

date_formats <- customer_data %>%
  mutate(
    date_valid = !is.na(
      as.Date(
        LastPurchaseDate,
        format = "%Y-%m-%d"
      )
    ),
    
    date_format_check = ifelse(
      grepl(
        "^\\d{4}-\\d{2}-\\d{2}$",
        LastPurchaseDate
      ),
      "Valid Format",
      "Invalid Format"
    )
  )

print("Date Format Consistency:")
table(date_formats$date_format_check)

# =========================
# Validity Analysis
# =========================
print("=== VALIDITY ANALYSIS ===")

validity_rules <- list(
  age_min = 18,
  age_max = 100,
  income_min = 0,
  income_max = 200000,
  purchase_min = 0,
  purchase_max = 1000
)

age_validity <- customer_data %>%
  filter(!is.na(Age)) %>%
  summarise(
    total_records = n(),
    valid_ages = sum(
      Age >= validity_rules$age_min &
        Age <= validity_rules$age_max
    ),
    
    invalid_ages = sum(
      Age < validity_rules$age_min |
        Age > validity_rules$age_max
    ),
    
    invalid_percentage = round(
      invalid_ages / total_records * 100,
      1
    )
  )

print("Age Validity Check:")
print(age_validity)

income_validity <- customer_data %>%
  summarise(
    total_records = n(),
    
    valid_incomes = sum(
      Income >= validity_rules$income_min &
        Income <= validity_rules$income_max,
      na.rm = TRUE
    ),
    
    invalid_incomes = sum(
      Income < validity_rules$income_min |
        Income > validity_rules$income_max,
      na.rm = TRUE
    ),
    
    invalid_percentage = round(
      invalid_incomes / total_records * 100,
      1
    )
  )

print("Income Validity Check:")
print(income_validity)

email_validity <- customer_data %>%
  mutate(
    valid_email = grepl(
      "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$",
      Email
    )
  ) %>%
  summarise(
    total_records = n(),
    valid_emails = sum(valid_email),
    invalid_emails = sum(!valid_email),
    
    invalid_percentage = round(
      invalid_emails / total_records * 100,
      1
    )
  )

print("Email Validity Check:")
print(email_validity)

duplicate_check <- customer_data %>%
  summarise(
    total_records = n(),
    unique_ids = n_distinct(CustomerID),
    duplicate_count = total_records - unique_ids
  )

print("Duplicate ID Check:")
print(duplicate_check)

# =========================
# Statistical Anomaly Detection
# =========================
print("=== STATISTICAL ANOMALY DETECTION ===")

clean_data <- customer_data %>%
  filter(
    !is.na(Age),
    Income > 0,
    Age <= 100
  )

clean_data <- clean_data %>%
  mutate(
    Income_ZScore = abs(scale(Income)[, 1]),
    Purchase_ZScore = abs(scale(PurchaseAmount)[, 1]),
    Age_ZScore = abs(scale(Age)[, 1])
  )

statistical_outliers <- clean_data %>%
  filter(
    Income_ZScore > 2 |
      Purchase_ZScore > 2 |
      Age_ZScore > 2
  ) %>%
  select(
    CustomerID,
    Age,
    Income,
    PurchaseAmount,
    CustomerType,
    Income_ZScore,
    Purchase_ZScore,
    Age_ZScore
  )

print("Statistical Outliers (Z-score > 2):")
print(statistical_outliers)

calculate_outliers_iqr <- function(x) {
  
  Q1 <- quantile(x, 0.25, na.rm = TRUE)
  Q3 <- quantile(x, 0.75, na.rm = TRUE)
  
  IQR <- Q3 - Q1
  
  lower_bound <- Q1 - 1.5 * IQR
  upper_bound <- Q3 + 1.5 * IQR
  
  return(x < lower_bound | x > upper_bound)
}

iqr_outliers <- clean_data %>%
  mutate(
    Income_Outlier = calculate_outliers_iqr(Income),
    Purchase_Outlier = calculate_outliers_iqr(PurchaseAmount),
    Age_Outlier = calculate_outliers_iqr(Age)
  ) %>%
  filter(
    Income_Outlier |
      Purchase_Outlier |
      Age_Outlier
  )

print("IQR Method Outliers:")

print(
  iqr_outliers[c(
    "CustomerID",
    "Age",
    "Income",
    "PurchaseAmount",
    "CustomerType"
  )]
)

# =========================
# Practice Dataset
# =========================
library(dplyr)

set.seed(123)

practice_data <- data.frame(
  CustomerID = c(
    paste0("ID", 101:115),
    "ID110",
    "ID116",
    "ID117"
  ),
  
  Age = c(
    22, 45, 31, NA, 28, 135,
    40, 29, 33, 27, 38, 41,
    36, 30, 25, 50, NA, 34
  ),
  
  Income = c(
    48000, 72000, 61000, 55000, -2000,
    68000, 75000, 52000, 58000, 60000,
    64000, 71000, 69000, 73000, 56000,
    80000, 62000, 59000
  ),
  
  PurchaseAmount = c(
    100, 220, 180, 150, 90,
    300, 350, 120, 200, 210,
    230, 260, 240, 270, 160,
    400, 190, 205
  ),
  
  CustomerType = c(
    "Budget", "Premium", "Standard",
    "Budget", "Budget", "Premium",
    "Premium", "Standard", "Standard",
    "Budget", "Standard", "Premium",
    "Standard", "Premium", "Budget",
    "Premium", "Standard", "Budget"
  ),
  
  LastPurchaseDate = c(
    "2024-01-10", "2024-02-15", "bad-date",
    "2024-01-20", "2024-03-01", "2024-02-28",
    "2024-01-30", "2024-02-12", "2024-03-05",
    "2024-01-18", "2024-02-22", "2024-03-10",
    "2024-01-25", "invalid", "2024-02-05",
    "2024-03-15", "2024-02-18", "2024-01-12"
  ),
  
  Email = c(
    "a@email.com", "b@email.com",
    "c@email.com", "invalid",
    "e@email.com", "f@email.com",
    "g@email.com", "h@email.com",
    "i@email.com", "j@email.com",
    "k@email.com", "l@email.com",
    "m@email.com", "n@email.com",
    "o@email.com", "p@email.com",
    "q@email.com", "wrong-email"
  )
)

practice_data