R: R Data Cleaning

Q: Should missing values be deleted or imputed?

If the missing value rate is < 5%, delete them; if it is 5–20%, impute them; if it is > 20%, consider deleting the column. Key fields (labels) cannot be deleted—they must be imputed or handled using other methods.

Q: What are the most commonly used functions in the janitor package?

clean_names() Standardize column names + get_dupes() Find duplicates + remove_empty() Remove empty rows/columns.

Last updated: 2026-08-26

In the previous 28 lessons, we learned about "analysis"—but in real-world projects, 80% of the time is spent on data cleaning. The CSV files provided by managers always contain missing values, outliers, duplicates, and incorrect data types—in this lesson, we’ll use R to thoroughly clean a set of "real-world dirty data."

After completing this lesson, you’ll be able to clean any CSV file: handle missing values, outliers, duplicates, incorrect data types, non-standard strings, and date formats—paving the way for analysis.

1. What You'll Learn

4 Major Issues in Data Cleaning (Missing Values, Outliers, Duplicates, Data Types)
Handling missing values (drop_na/fill/replace)
Outlier handling (IQR/3σ/replacement)
Duplicate values (distinct / unique)
Type conversion (as.numeric/as.Date)
String Cleaning (Comprehensive Guide to stringr)
Date and Time Processing (lubridate)
janitor package (clean_names)
Hands-On: Cleaning 1,000 Lines of Dirty Data

2. A Real-World Challenge with Dirty Data

(1) Pain Point: The "monster data" provided by the manager

Alice received a file named "Customer Data.csv" from her manager. She opened it and saw:

TEXT 📖 Display only

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

6 Major Issues: ① ID format ② Names containing spaces ③ Inconsistent mobile phone number formats ④ Email addresses with mixed case and spaces ⑤ Missing values ⑥ Duplicate rows ⑦ Inconsistent date formats ⑧ Invalid dates.

(2) Solution using R

# 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")))

1 Line of Code → Solves All 6 Major Problems.

3. Four Major Issues in Data Cleaning

(1) Overview of the Four Major Issues

graph TB
    A[Dirty Data] --> B[Missing values Missing]
    A --> C[Outliers Outliers]
    A --> D[Duplicate values Duplicates]
    A --> E[Type error Type]
    
    style A fill:#fff3cd
    style B fill:#cce5ff
    style C fill:#d4edda
    style D fill:#f8d7da
    style E fill:#e1d4ff

4. Handling Missing Values

(1) Identifying Missing Values

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)

(2) Three Management Strategies

Strategy	Applicability	R Function
Delete	Missing values (< 5%), random missing values	`drop_na()`
Fill in	Many missing entries; cannot be deleted	`replace_na()`
Field	Missing is meaningful ("No email" = left blank)	Add `is_missing` column

# 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))

(3) Advanced: Filling Before and After with `fill()`

# 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

5. Handling Outliers

(1) 3 Identification Methods

# 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)

(2) 4 Treatment Methods

# 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)))

6. Handling Duplicate Values

# 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))

7. Type Conversion

(1) Numeric

# 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))

(2) Date Type

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)

(3) Factor Type

# 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")))

8. String Cleaning (stringr)

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)
  )

9. The janitor package (a cleaning powerhouse)

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

10. Complete Example: Cleaning 1,000 Lines of Dirty Data

Below is an example of a complete workflow that ties together all the cleaning concepts covered in this lesson.

▶ Example: Complete Cleaning of 1,000 Customers' Dirty Data

R 📖 Display only

# ============================================
# 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)

89 logic lines (exceeds 40-line limit, display only)

Expected Output (Excerpt):

TEXT 📖 Display only

=== 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 ]

❓ FAQ

Q Should missing values be deleted or imputed?

A If the missing value rate is < 5%, delete them; if it is 5–20%, impute them; if it is > 20%, consider deleting the column. Key fields (labels) cannot be deleted—they must be imputed or handled using other methods.

Q What values should be used for imputation?

A There are three options: ① Mean/median (most common) ② Group mean (by group) ③ Model prediction (most accurate but slow). Business understanding > statistical methods.

Q Can outliers be deleted directly?

A First, determine whether it’s an “error”! Business anomalies cannot be deleted (such as unusual spending by VIP customers); only data entry errors can be deleted or modified.

Q What are the steps for string cleaning?

A ① Remove whitespace (trim) ② Convert to uppercase/lowercase ③ Remove special characters ④ Validate format ⑤ Extract key information.

Q What are some common pitfalls in date handling?

A 3 major pitfalls: ① Multiple formats (YYYY-MM-DD vs. MM/DD/YYYY) ② Time zones ③ Invalid dates (e.g., the 13th month).

Q What are the most commonly used functions in the janitor package?

A clean_names() Standardize column names + get_dupes() Find duplicates + remove_empty() Remove empty rows/columns.

📖 Summary

The 4 Major Issues in Data Cleaning: Missing Values / Outliers / Duplicates / Data Types
Missing: < 5% delete / 5-20% fill / > 20% delete col + naniar::vis_miss visualization
Anomaly: IQR method (< Q1 - 1.5×IQR or > Q3 + 1.5×IQR); first determine whether it is an error or a business anomaly
Duplicates: distinct() Duplicate Removal + duplicated() Detection
Type: as.numeric / parse_date_time / factor Three-piece set
Strings: trim + case conversion + special character removal + validation (stringr)
Date: lubridate::parse_date_time() Automatically recognizes multiple formats
janitor package: clean_names Standardized list of names + get_dupes + remove_empty
Cleaning Order: List → Duplicates → Strings → Missing → Exceptions → Types → Output
Cleaning is a must before modeling—you can’t model without cleaning first.

📝 Exercises

Basic Exercise: Perform a complete data cleaning using R’s built-in airquality dataset: ① Missing values (impute Ozone using the mean or median) ② Duplicates (there are no duplicates in airquality) ③ Data types (ensure all columns are correct) ④ Compare the output (before vs. after cleaning).
Basic Problem: Create a dirty data frame containing 100 rows (including NA values, duplicates, type errors, and non-standard strings), clean it thoroughly using stringr + tidyr + janitor, and output a comparison table.
Basic Exercise: Use parse_date_time to parse 5 different date formats ("2024-01-15," "2024/01/15," "15/01/2024," "January 15, 2024," "15-Jan-2024") and verify the results.
Advanced Exercise: Perform a complete data cleaning on the nycflights13::flights dataset: ① Missing values (dep_time, arr_time, air_time, dep_delay, arr_delay) ② Outliers (dep_delay > 1440 minutes = 24 hours) ③ Output statistics before and after cleaning.
Challenge: Create a 1,000-row "data monster" dataset (containing all four major issues), thoroughly clean it using the tools learned in this lesson, and output: ① a comparison table showing the data before and after cleaning; ② a data cleaning quality report; ③ the cleaned data in CSV format; ④ a summary of the cleaning process (200 characters).

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us