R: التنظيف العملي للبيانات في R

id  ,Name    ,Cell phone number           ,Email                ,Date of Registration
001 ,Alice    ,138-0000-1234    ,ZHANGSAN@163.COM    ,2024/01/15
002 ,Bob    ,+86 139 0000 5678, lisi@example.com   ,2024.01.20
003 , Alice   ,13800001111      ,                    ,2024-02-01
004 ,Charlie    ,138-0000-2222    ,wangwu@gmail.com    ,2024-02-05
005 ,Charlie    ,138-0000-2222    ,wangwu@gmail.com    ,2024-02-05
006 ,Eve    ,(139)0000-3333   ,qianqi@ qq.com      ,2024-13-45

# Use dplyr + stringr + tidyr + lubridate for cleaning
df_clean <- df |>
  janitor::clean_names() |>       # Standardization of Listing
  mutate(
    id = str_pad(id, 3, pad = "0"),
    name = str_trim(name),
    phone = str_replace_all(phone, "[^0-9]", ""),
    email = str_to_lower(str_trim(email))
  ) |>
  drop_na(email) |>
  distinct() |>
  filter(nchar(phone) == 11) |>
  mutate(register_date = parse_date_time(register_date, orders = c("Y/m/d", "Y.m.d", "Y-m-d")))

library(tidyr)

# Count the number of missing values in each column
df |> summarise(across(everything(), ~ sum(is.na(.))))

# Missing Value Rate
df |> summarise(across(everything(), ~ mean(is.na(.)) * 100))

# Missing-value patterns
naniar::vis_miss(df)

# 1. Delete: rows containing NA
df |> drop_na()

# 2. Delete: Specified Column
df |> drop_na(email, phone)

# 3. Fill: Fixed value
df |> replace_na(list(email = "unknown@example.com", phone = "Not provided"))

# 4. Fill: Mean/Median
df |>
  mutate(
    age = replace_na(age, mean(age, na.rm = TRUE)),
    income = replace_na(income, median(income, na.rm = TRUE))
  )

# 5. Mark
df |>
  mutate(email_missing = is.na(email))

# Time Series: Missing values are imputed with the preceding value
df |> fill(value, .direction = "down")   # Use the previous line
df |> fill(value, .direction = "up")     # Use the next line
df |> fill(value, .direction = "downup") # Two-way

# 1. IQR method (Robust, Recommended)
is_outlier_iqr <- function(x) {
  q1 <- quantile(x, 0.25, na.rm = TRUE)
  q3 <- quantile(x, 0.75, na.rm = TRUE)
  iqr <- q3 - q1
  x < q1 - 1.5 * iqr | x > q3 + 1.5 * iqr
}

# 2. 3-sigma method (Normal only)
is_outlier_z <- function(x, threshold = 3) {
  z <- (x - mean(x, na.rm = TRUE)) / sd(x, na.rm = TRUE)
  abs(z) > threshold
}

# 3. Business Rules (Such as age < 0 or > 150)
df |> filter(age < 0 | age > 150)

# 1. Delete
df |> filter(!is_outlier_iqr(income))

# 2. Replace with NA (Leave it for further analysis and processing)
df |> mutate(income = ifelse(is_outlier_iqr(income), NA, income))

# 3. Replace with the median (Robust)
df |>
  mutate(income = ifelse(is_outlier_iqr(income),
                          median(income, na.rm = TRUE), income))

# 4. Winsorize (Truncate to 5% / 95% quantile)
df |>
  mutate(income = DescTools::Winsorize(income, probs = c(0.05, 0.95)))

# 1. Exactly duplicate rows
df |> distinct()           # Duplicate Removal
sum(duplicated(df))        # Count

# 2. Remove duplicates based on the specified column
df |> distinct(name, phone, .keep_all = TRUE)

# 3. Mark as Duplicate
df |> mutate(is_dup = duplicated(.))

# 4. Find Duplicates
df |> filter(duplicated(df) | duplicated(df, fromLast = TRUE))

# Character -> Numeric (Processing "$1,000" Format)
df |>
  mutate(price = as.numeric(str_replace_all(price, "[$,]", "")))

# Processing Percentage "15%" → 0.15
df |>
  mutate(rate = as.numeric(str_replace(rate, "%", "")) / 100)

# Processing NA Conversion Error
df |>
  mutate(value = as.numeric(value),  # Failure leads to change NA + Warning
         bad = is.na(value) & !is.na(original_value))

library(lubridate)

# Automatic Parsing of Multiple Date Formats
df |>
  mutate(date = parse_date_time(date,
                                 orders = c("Y/m/d", "Y-m-d", "Y.m.d")))

# Extract Year, Month, and Day
df |>
  mutate(
    year = year(date),
    month = month(date),
    day = day(date),
    weekday = wday(date, label = TRUE)
  )

# Handling Invalid Dates
df |>
  mutate(
    date_parsed = parse_date_time(date, orders = "Y-m-d"),
    is_invalid = is.na(date_parsed) & !is.na(date)
  ) |>
  filter(!is_invalid)

# Character -> factor (In the specified order)
df |>
  mutate(grade = factor(grade, levels = c("Poor", "Mid", "Good", "Excellent"),
                         ordered = TRUE))

# Numeric -> factor (Bin Sorting)
df |>
  mutate(age_group = cut(age,
                          breaks = c(0, 18, 35, 60, 100),
                          labels = c("Boy", "Youth", "Middle Age", "Old Age")))

library(stringr)

df |>
  mutate(
    # Remove spaces
    name = str_trim(name),
    # Uppercase and lowercase
    email = str_to_lower(email),
    # Remove Special Characters
    phone = str_replace_all(phone, "[^0-9]", ""),
    # Validation Format
    email_valid = str_detect(email, "^[\\w.+-]+@[\\w.-]+\\.[a-z]+$"),
    # Extract
    phone_prefix = str_sub(phone, 1, 3)
  )

install.packages("janitor")
library(janitor)

# 1. Standardized column names (lowercase + underscore + remove special characters)
df |> clean_names()
# "First Name" → "first_name"
# "Age(yrs)" → "age"

# 2. Duplicate Removal + Report
df |> get_dupes()  # Show duplicate rows

# 3. Remove blank lines/col
df |> remove_empty(c("rows", "cols"))

# 4. Consistency Check
df |> tabyl(category)  # Similar table

# ============================================
# 1000 Complete Cleaning of Dirty Customer Data
# Features: All 6 major issues resolved
# ============================================

library(dplyr)
library(tidyr)
library(stringr)
library(lubridate)
library(janitor)

# 1. Constructing Dirty Data
set.seed(42)
n <- 1000
df_raw <- tibble(
  ID = sprintf("%04d", 1:n),
  Customer Name = paste0(" ", c("Alice", "Bob", "Charlie", "Diana", "Eve")[sample(5, n, TRUE)], " "),
  Cell Phone = sample(c("138-0000-1234", "+86 139 0000 5678", "(13900001111)",
                "138.0000.2222", "13900003333", "13900009999XX", NA), n, TRUE),
  Email = sample(c("user@163.COM", "lisi@example.com  ",
                "wangwu@gmail.com", "zhaoliu@ qq.com",
                NA, ""), n, TRUE),
  Date of Registration = sample(c("2024/01/15", "2024.01.20", "2024-02-01",
                    "2024-13-45", NA), n, TRUE),
  Age = sample(c(20:80, -5, 200, NA), n, TRUE)
)

# Add some repetition
df_raw <- bind_rows(df_raw, df_raw[1:50, ])

cat("=== Statistics on Raw Data Issues ===\n")
cat("Number of lines:", nrow(df_raw), "(includes 50 duplicate rows)\n")
cat("Email address missing:", sum(is.na(df_raw$Email) | df_raw$Email == ""), "rows\n")
cat("Missing Cell Phone:", sum(is.na(df_raw$Cell Phone)), "rows\n")
cat("Date Missing:", sum(is.na(df_raw$Date of Registration)), "rows\n")
cat("Age Anomaly (< 0 or > 150):",
    sum(df_raw$Age < 0 | df_raw$Age > 150, na.rm = TRUE), "rows\n\n")

# 2. Complete Cleaning Process
df_clean <- df_raw |>
  # 2.1 Standardized Listing
  clean_names() |>

  # 2.2 Remove duplicates
  distinct() |>

  # 2.3 Clean Name
  mutate(Customer Name = str_trim(Customer Name)) |>

  # 2.4 Clear Mobile Phone Number
  mutate(
    Cell Phone_Pure numbers = str_replace_all(Cell Phone, "[^0-9]", ""),  # Remove all non-numeric characters
    Cell Phone_Valid = nchar(Cell Phone_Pure numbers) == 11 & str_detect(Cell Phone_Pure numbers, "^1[3-9]")
  ) |>

  # 2.5 Clean the Mailbox
  mutate(
    Email_Cleaning = str_trim(Email) |> str_to_lower(),
    Email_Valid = str_detect(Email_Cleaning, "^[\\w.+-]+@[\\w.-]+\\.[a-z]+$")
  ) |>

  # 2.6 Handling Missing Values
  mutate(
    Cell Phone_Pure numbers = ifelse(Cell Phone_Valid, Cell Phone_Pure numbers, NA),
    Email_Cleaning = ifelse(Email_Valid, Email_Cleaning, NA)
  ) |>

  # 2.7 Handling Abnormal Ages
  mutate(
    Age_Cleaning = ifelse(Age < 0 | Age > 150, NA, Age)
  ) |>

  # 2.8 Analysis Date
  mutate(
    Date of Registration_parsed = parse_date_time(Date of Registration,
                                       orders = c("Y/m/d", "Y.m.d", "Y-m-d")),
    Date_Valid = !is.na(Date of Registration_parsed) | is.na(Date of Registration)
  ) |>

  # 2.9 Delete rows where a key field is missing
  drop_na(Customer Name, Date of Registration_parsed) |>

  # 2.10 Select the last column
  select(id, Name = Customer Name, Cell Phone = Cell Phone_Pure numbers, Email = Email_Cleaning,
         Age = Age_Cleaning, Date of Registration = Date of Registration_parsed)

cat("=== Data After Cleaning ===\n")
cat("Number of lines:", nrow(df_clean), "\n")
cat("Columns:", ncol(df_clean), "\n")
cat("Email address missing:", sum(is.na(df_clean$Email)), "\n")
cat("Missing Cell Phone:", sum(is.na(df_clean$Cell Phone)), "\n")
cat("Age Missing:", sum(is.na(df_clean$Age)), "\n")
cat("Date Missing:", sum(is.na(df_clean$Date of Registration)), "\n\n")

# 3. Verify the Quality of Cleaning
cat("=== Verify the Quality of Cleaning ===\n")

# 3.1 ID Format
cat("ID Length:", unique(nchar(df_clean$id)), "(Expected 4)\n")

# 3.2 No spaces before or after the name
cat("Name contains leading and trailing spaces:", sum(str_detect(df_clean$Name, "^\\s|\\s$")), "\n")

# 3.3 Mobile Phone Number Format
cat("Cell phone number 11 digits:", sum(nchar(df_clean$Cell Phone, na.rm = TRUE) == 11), "/",
    sum(!is.na(df_clean$Cell Phone)), "\n")

# 3.4 Email Format
cat("Email address includes @:", sum(str_detect(df_clean$Email, "@")), "/",
    sum(!is.na(df_clean$Email)), "\n")

# 3.5 Age Range
cat("Age Range: [", min(df_clean$Age, na.rm = TRUE), ",",
    max(df_clean$Age, na.rm = TRUE), "]\n")

# 4. Output clean data
write_csv(df_clean, "cleaned_data.csv")
cat("\n=== The cleaned data has been saved: cleaned_data.csv ===\n")

# 5. Before and After Cleaning Comparison
cat("\n=== Before and After Cleaning Comparison ===\n")
comparison <- tibble(
  Indicators = c("Total Number of Lines", "Columns", "Duplicate Rows", "Email address missing", "Missing Cell Phone", "Age Anomaly"),
  Before Cleaning = c(
    nrow(df_raw), ncol(df_raw), nrow(df_raw) - nrow(distinct(df_raw)),
    sum(is.na(df_raw$Email) | df_raw$Email == ""),
    sum(is.na(df_raw$Cell Phone)),
    sum(df_raw$Age < 0 | df_raw$Age > 150, na.rm = TRUE)
  ),
  After Cleaning = c(
    nrow(df_clean), ncol(df_clean), 0,
    sum(is.na(df_clean$Email)),
    sum(is.na(df_clean$Cell Phone)),
    sum(is.na(df_clean$Age))  # Outliers changed to NA
  )
)
print(comparison)

=== Data After Cleaning ===
Number of lines: 923
Columns: 6
Email address missing: 162
Missing Cell Phone: 85
Age Missing: 22
Date Missing: 0

=== Verify the Quality of Cleaning ===
ID Length: 4 (Expected 4)
Name contains leading and trailing spaces: 0
Cell phone number 11 digits: 838 / 838
Email address includes @: 761 / 761
Age Range: [ 20 , 80 ]