Week 4: Data Cleaning and Preparation

Follow along in RStudioDownload the Week 4 R script — every example ready to run with Ctrl+Enter.

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:

Load and inspect data
Check for missing values
Remove duplicates
Fix data types
Handle outliers
Standardize text
Handle dates properly
Validate data quality
Document all decisions
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:

MCAR - Missing Completely At Random
Missingness unrelated to data
Safe to remove
Example: Random sensor failure

MAR - Missing At Random
Missingness related to other variables
Can impute carefully
Example: Higher income people skip salary question

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

← Previous Week Next Week →

In this week

Week 4 Overview

SESSION 1: Missing Values and Duplicates

Duration: 2 hours

1.1 Understanding and Handling Missing Data

1.2 Data Type Fixes and Standardization