# Lab 3-1 Cleaning Messy Data
# R CODE ONLY

# =========================
# Setup
# =========================
library(dplyr)

# =========================
# Create Messy Dataset
# =========================
messy_data <- data.frame(
  CustomerID = 1:10,
  
  Name = c(
    "John Smith", "JANE DOE", "Bob Johnson",
    "jane doe", "Mike Wilson", "Sarah Brown",
    "Tom Davis", "Lisa Garcia",
    "David Lee", "Amy Chen"
  ),
  
  Age = c(
    25, NA, 150, 28, 35,
    -5, 42, 29, 38, 31
  ),
  
  Phone = c(
    "555-1234", "555-234-5678", "5552345678",
    "555-234-5678", "(555) 789-0123",
    "555.456.7890", "555-999-8888",
    "555-111-2222", "555-333-4444",
    "555-555-5555"
  ),
  
  Email = c(
    "john@email.com", "jane.doe@gmail.com",
    "bob@company.com", "jane.doe@gmail.com",
    "mike.wilson@email.com", "sarah@email.com",
    "tom.davis@company.com", "lisa@email.com",
    "", "amy.chen@gmail.com"
  ),
  
  Income = c(
    45000, 62000, 48000, 61500, 75000,
    52000, 999999, 58000, 67000, 71000
  ),
  
  JoinDate = c(
    "2023-01-15", "Jan 20 2023", "1/25/23",
    "2023-01-20", "2023-02-10", "2023-02-15",
    "2023-03-01", "2023-03-05",
    "2023-03-10", "2023-03-15"
  ),
  
  City = c(
    "Boston", "new york", "Chicago",
    "New York", "Los Angeles",
    "Houston", "Phoenix",
    "Philadelphia", "San Antonio",
    "San Diego"
  ),
  
  State = c(
    "MA", "NY", "IL", "ny", "CA",
    "TX", "AZ", "PA", "TX", "ca"
  ),
  
  stringsAsFactors = FALSE
)

messy_data

# =========================
# Data Quality Assessment
# =========================
print("=== DATA QUALITY ASSESSMENT ===")

print(
  paste(
    "Dataset size:",
    nrow(messy_data),
    "rows,",
    ncol(messy_data),
    "columns"
  )
)

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

print("Missing data summary:")

missing_summary <- messy_data %>%
  summarise_all(~sum(is.na(.) | . == ""))

print(missing_summary)

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

print("Missing data percentages:")
print(missing_percentages)

print("=== ANOMALY DETECTION ===")

age_issues <- messy_data[
  !is.na(messy_data$Age) &
    (messy_data$Age < 0 |
       messy_data$Age > 120),
]

print(
  paste(
    "Customers with impossible ages:",
    nrow(age_issues)
  )
)

if(nrow(age_issues) > 0) {
  
  print(
    age_issues[c(
      "CustomerID",
      "Name",
      "Age"
    )]
  )
}

income_mean <- mean(
  messy_data$Income,
  na.rm = TRUE
)

income_sd <- sd(
  messy_data$Income,
  na.rm = TRUE
)

income_outliers <- messy_data[
  abs(messy_data$Income - income_mean) >
    3 * income_sd,
]

print(
  paste(
    "Customers with unusual incomes:",
    nrow(income_outliers)
  )
)

if(nrow(income_outliers) > 0) {
  
  print(
    income_outliers[c(
      "CustomerID",
      "Name",
      "Income"
    )]
  )
}

print("=== DUPLICATE DETECTION ===")

messy_data$Name_Clean <-
  tolower(trimws(messy_data$Name))

duplicate_names <- messy_data[
  duplicated(messy_data$Name_Clean) |
    duplicated(
      messy_data$Name_Clean,
      fromLast = TRUE
    ),
]

print(
  paste(
    "Potential duplicate customers:",
    nrow(duplicate_names)
  )
)

if(nrow(duplicate_names) > 0) {
  
  print(
    duplicate_names[c(
      "CustomerID",
      "Name",
      "Phone",
      "Email"
    )]
  )
}

# =========================
# Manual Data Cleaning
# =========================
print("=== DATA CLEANING ===")

cleaned_data <- messy_data

cleaned_data$Age[
  is.na(cleaned_data$Age)
] <- 30

cleaned_data$Age[
  cleaned_data$Age < 0 |
    cleaned_data$Age > 120
] <- 30

cleaned_data$Name <-
  tools::toTitleCase(
    tolower(cleaned_data$Name)
  )

cleaned_data$State <-
  toupper(cleaned_data$State)

cleaned_data$Email[
  cleaned_data$Email == ""
] <- NA

cleaned_data$Phone <-
  gsub(
    "[^0-9]",
    "",
    cleaned_data$Phone
  )

cleaned_data

# =========================
# AI-Assisted Cleaning Example
# =========================
ai_input_data <- messy_data[, c(
  "CustomerID",
  "Name",
  "Age",
  "Phone",
  "Email",
  "JoinDate",
  "State"
)]

ai_input_data

messy_data$Name <-
  tools::toTitleCase(
    tolower(messy_data$Name)
  )

messy_data$Age[
  is.na(messy_data$Age) |
    messy_data$Age < 0 |
    messy_data$Age > 120
] <- NA

messy_data$Phone <-
  gsub(
    "[^0-9]",
    "",
    messy_data$Phone
  )

messy_data$State <-
  toupper(messy_data$State)

messy_data$JoinDate <- as.Date(
  messy_data$JoinDate,
  tryFormats = c(
    "%Y-%m-%d",
    "%m/%d/%y",
    "%b %d %Y"
  )
)

cleaned_data <- messy_data

cleaned_data$Name <-
  tools::toTitleCase(
    tolower(cleaned_data$Name)
  )

cleaned_data$Age[
  cleaned_data$Age < 0 |
    cleaned_data$Age > 120
] <- NA

cleaned_data$Phone <-
  gsub(
    "[^0-9]",
    "",
    cleaned_data$Phone
  )

cleaned_data$State <-
  toupper(cleaned_data$State)

cleaned_data

# =========================
# Data Quality Validation
# =========================
print("=== DATA QUALITY VALIDATION ===")

original_missing <- sum(
  is.na(messy_data) |
    messy_data == "",
  na.rm = TRUE
)

cleaned_missing <- sum(
  is.na(cleaned_data) |
    cleaned_data == "",
  na.rm = TRUE
)

cat(
  "Original missing values:",
  original_missing,
  "\n"
)

cat(
  "Cleaned missing values:",
  cleaned_missing,
  "\n"
)

original_age_issues <- sum(
  messy_data$Age < 0 |
    messy_data$Age > 120,
  na.rm = TRUE
)

cleaned_age_issues <- sum(
  cleaned_data$Age < 0 |
    cleaned_data$Age > 120,
  na.rm = TRUE
)

cat(
  "Original age issues:",
  original_age_issues,
  "\n"
)

cat(
  "Cleaned age issues:",
  cleaned_age_issues,
  "\n"
)

cat("\n=== FINAL CLEANED DATASET PREVIEW ===\n")

cleaned_data[c(
  "CustomerID",
  "Name",
  "Age",
  "Phone",
  "State"
)]

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

practice_data <- data.frame(
  CustomerID = 101:115,
  
  Name = c(
    "alice johnson", "BOB SMITH",
    "Charlie Brown", "Alice Johnson",
    "david lee", "Eva Green",
    "Frank Moore", "Grace Kim",
    "Henry Clark", "Isla Scott",
    "Jack White", "kate young",
    "Liam Hall", "Mia King",
    "Noah Adams"
  ),
  
  Age = c(
    22, NA, 135, 22, -3,
    45, 38, NA, 29, 41,
    200, 33, 27, NA, 36
  ),
  
  Phone = c(
    "603-111-2222", "(603)222-3333",
    "6033334444", "603-111-2222",
    "603.444.5555", "603-555-6666",
    "6036667777", "603-777-8888",
    "6038889999", "603-999-0000",
    "6030001111", "603-123-4567",
    "(603) 234-5678", "603.345.6789",
    "6034567890"
  ),
  
  Email = c(
    "alice@email.com", "bob@email.com",
    "", "alice@email.com",
    "david@email", "eva@email.com",
    "frank@email.com", "grace@email.com",
    "", "isla@email.com",
    "jack@email.com", "kate@email.com",
    "liam@email.com", "mia@email.com",
    "noah@email.com"
  ),
  
  Income = c(
    52000, 61000, 48000, 52000, 45000,
    1200000, 58000, 60000, 62000, 64000,
    30000, 70000, 72000, 71000, 69000
  ),
  
  JoinDate = c(
    "2023-01-05", "02/10/23",
    "March 1 2023", "2023-01-05",
    "2023-02-20", "2023-03-01",
    "3/15/23", "2023-03-20",
    "2023-04-01", "Apr 5 2023",
    "2023-04-10", "2023-04-15",
    "2023-04-20", "2023-04-25",
    "2023-05-01"
  ),
  
  City = c(
    "manchester", "Boston", "boston",
    "Manchester", "Chicago", "Seattle",
    "seattle", "Austin", "Austin",
    "Denver", "denver", "Miami",
    "Miami", "Dallas", "dallas"
  ),
  
  State = c(
    "nh", "MA", "ma", "NH",
    "IL", "WA", "wa", "TX",
    "tx", "CO", "co", "FL",
    "fl", "TX", "tx"
  ),
  
  stringsAsFactors = FALSE
)

practice_data
