Image by Editor | ChatGPT
This article shows how to build a reliable data cleaning workflow in R that you can reuse across projects. It follows a practical path: load data correctly, audit its quality, standardize the schema, fix missingness and types, reshape to a tidy form, validate joins, and wrap everything into a pipeline that writes clean outputs. The audience is comfortable with basic R and wants an intermediate-to-advanced playbook that runs without drama.
This is fully hands-on, so get ready to get your hands dirty. With the tidyverse and a few targeted helpers, you’ll build a compact toolkit for robust cleaning. The patterns scale from exploratory notebooks to production scripts. By the end, you’ll have a template pipeline that reads, cleans, validates, and writes CSV and Parquet files, with decisions captured in code rather than scattered across ad-hoc notes.
Setup
Install packages once per project, then load everything in a single block. If you need reproducibility for teammates, initialize renv after installing so that versions are locked.
# Install packages if needed
# install.packages(c("tidyverse", "janitor", "naniar", "lubridate",
# "stringr", "forcats", "skimr", "readxl", "arrow", "here"))
library(tidyverse) # readr, dplyr, tidyr, ggplot2, etc.
library(janitor) # clean_names(), get_dupes()
library(naniar) # missingness summaries and visuals
library(lubridate) # date-time parsing
library(stringr) # text normalization
library(forcats) # factor handling
library(skimr) # quick structure and stats
library(readxl) # Excel support
library(arrow) # parquet read/write
library(here) # stable project paths
These packages cover reading, transforming, dates, strings, factors, missingness, and exporting. here::here() keeps file paths stable across machines and CI, so your scripts don’t break when the working directory changes.
Importing Data Correctly
The most frustrating bugs usually start at read time. Give readr explicit types where you can, and check problems() right after reading. Fix issues at the source if possible; otherwise, correct them immediately in your code.
# Example file paths
raw_csv <- here::here("data", "raw", "sales.csv")
raw_xlsx <- here::here("data", "raw", "customers.xlsx")
# Read CSV with explicit column types where known
sales <- readr::read_csv(
file = raw_csv,
col_types = cols(
order_id = col_character(),
order_date = col_character(), # parse later with lubridate
product_id = col_character(),
qty = col_double(),
unit_price = col_double(),
customer_id = col_character(),
region = col_character()
),
locale = locale(decimal_mark = ".", grouping_mark = ",")
)
# Check for parse issues immediately
readr::problems(sales)
# Read Excel, pick a sheet, and standardize names
customers <- readxl::read_excel(
path = raw_xlsx,
sheet = "Sheet1"
) |> janitor::clean_names()
The goal here is to prevent silent type guessing. Thousand separators, currency symbols, and odd decimal marks are common troublemakers. Standardize header names early so that every downstream step uses the same identifiers.
Quick Audit of Data Quality
Before changing anything, scan the structure and missingness. This gives you a prioritized list of what to fix and what to leave alone.
# Structure and summary in one pass skimr::skim(sales) # Missingness by variable naniar::miss_var_summary(sales) # Potential duplicates on a natural key janitor::get_dupes(sales, order_id, product_id)
- skim() surfaces types, unique counts, quantiles, and missing value percentages.
- miss_var_summary() ranks columns by missingness, so you can decide where to start.
- get_dupes() flags rows that repeat key fields; confirm whether repeats are legitimate multi-line orders or actual duplicates.
Standardize Schema and Names
Normalize headers, parse dates, coerce categories, and compute derived fields early in the flow. This stabilizes everything that follows.
sales <- sales |> janitor::clean_names() |> # snake_case headers mutate( order_date = lubridate::ymd(order_date, quiet = TRUE), # parse dates safely region = forcats::as_factor(region), # control category levels revenue = qty * unit_price # derived metric ) # Basic type assertion: any NA dates after parsing? sum(is.na(sales$order_date))
Cleaning names prevents headaches from spaces and punctuation. Dates should be parsed once and reused. Factors keep category order predictable in plots and tables. A quick NA count after ymd() catches bad date formats before they spread.
Handle Missing Data with Intent
Treat missingness as a series of small, explicit policies. Some fields can be dropped. Others should be imputed. When you impute, keep an indicator so that models can learn from the fact that data was missing.
# Example policy: # - qty and unit_price: drop rows where either is missing # - region: fill with "Unknown" and flag # - revenue: recompute post-imputation if needed sales <- sales |> filter(!is.na(qty), !is.na(unit_price)) |> mutate( region = fct_explicit_na(region, na_level = "Unknown"), region_missing = as.integer(region == "Unknown") ) # Group-aware fill: if a product has rare NAs for qty, fill with median by product sales <- sales |> group_by(product_id) |> mutate(qty = if_else(is.na(qty), median(qty, na.rm = TRUE), qty)) |> ungroup()
This keeps decisions close to the code that executes them. Group-aware fills reduce bias compared to a global mean. The indicator preserves information about absence, which can matter downstream.
Fix Types, Dates, and Categories
Real-world data often stores numbers as text with currency symbols and separators. Clean them into true numerics, extract date parts once, and align category labels and order.
# Suppose unit_price arrived as "€1,234.50" in some rows sales <- sales |> mutate( unit_price = readr::parse_number(unit_price, locale = locale(grouping_mark = ",", decimal_mark = ".")), year = lubridate::year(order_date), month = lubridate::month(order_date, label = TRUE, abbr = TRUE) ) # Fix category spelling and order sales <- sales |> mutate( region = fct_recode(region, "North" = "NORTH", "South" = "SOUTH"), region = fct_relevel(region, "North", "South", "East", "West", "Unknown") )
After parse_number(), confirm that there are no lingering characters. After ymd(), check that the date ranges make sense. After fct_recode, ensure there are no stray levels. Explicit releveling keeps output stable across runs.
Text Normalization that Sticks
Normalize whitespace and case, then apply consistent pattern fixes with str_replace_all. Centralizing these rules keeps grouping and joins honest.
# Product names may contain inconsistent casing and spacing
sales <- sales |>
mutate(
product_id = str_squish(product_id),
product_name = str_squish(product_name),
product_name = str_to_title(product_name),
# Normalize common variants: "XL ", "Extra Large", "X-Large" -> "XL"
product_size = str_squish(product_size),
product_size = str_replace_all(product_size, regex("extra\\s*large|x[-\\s]*large", ignore_case = TRUE), "XL"),
product_size = str_replace_all(product_size, regex("^large$", ignore_case = TRUE), "L")
)
- str_squish() trims and collapses internal whitespace so pattern matching works as intended.
- Using a compiled regex with case-insensitive flags reduces the chance of missing variants.
- A quick count() before and after can confirm that level consolidation is working.
Outliers and Duplicates
Start by flagging extreme values rather than removing them. If dropping them isn’t acceptable, cap them. For duplicates, de-duplicate by keys first, then handle near-duplicates with a review list.
# IQR-based flagging for numeric columns
numeric_cols <- c("qty", "unit_price", "revenue")
iqr_bounds <- sales |>
summarise(across(all_of(numeric_cols), list(
q1 = ~quantile(.x, 0.25, na.rm = TRUE),
q3 = ~quantile(.x, 0.75, na.rm = TRUE)
))) |>
pivot_longer(everything(), names_to = c("var", ".value"), names_sep = "_")
# Join bounds back and flag outliers for review
sales_flagged <- sales |>
pivot_longer(all_of(numeric_cols), names_to = "var", values_to = "val") |>
left_join(iqr_bounds, by = "var") |>
mutate(
iqr = q3 - q1,
lower = q1 - 1.5 * iqr,
upper = q3 + 1.5 * iqr,
is_outlier = val < lower | val > upper
) |>
select(-iqr, -lower, -upper) |>
pivot_wider(names_from = var, values_from = c(val, is_outlier), names_sep = "_")
# Hard cap (winsorize) as a policy example
cap_value <- function(x, p_low = 0.01, p_high = 0.99) {
lo <- quantile(x, p_low, na.rm = TRUE)
hi <- quantile(x, p_high, na.rm = TRUE)
pmin(pmax(x, lo), hi)
}
sales <- sales |>
mutate(across(all_of(numeric_cols), cap_value))
# Remove strict duplicate order lines
sales <- sales |>
distinct(order_id, product_id, .keep_all = TRUE)
This approach separates detection from action. The Interquartile Range (IQR) step creates a separate sales_flagged dataframe to identify candidates for review. The next step, winsorizing, is an alternative policy applied to the original data that caps only the most extreme tails while keeping row counts stable. Finally, exact duplicates by key are removed, and possible near-duplicates can be exported for manual review.
Make Data Tidy
Tidy data gives you one variable per column and one observation per row. It unlocks direct grouping, faceting, and modeling without the need for custom reshape code later.
# Suppose sales contains monthly columns like qty_jan, qty_feb, ...
# Gather them into a tidy month column
sales_tidy <- sales |>
pivot_longer(
cols = matches("^qty_"),
names_to = "month_lbl",
values_to = "qty_month"
) |>
mutate(
# Derive month from label and align with order_date month when needed
month_from_lbl = str_remove(month_lbl, "^qty_"),
month_from_lbl = factor(month_from_lbl, levels = tolower(month.abb), labels = month.abb)
)
# If a spreadsheet had a single "city, state" column, split it
customers <- customers |>
separate(col = city_state, into = c("city", "state"), sep = ",\\s*", remove = TRUE, fill = "right")
Move wide month fields into a long format early. If the source mixes values in one cell, split them and validate with simple counts so that you don’t carry hidden structure further into your analysis.
Joins and Consistency Checks
Join with explicit keys and then audit the result. Orphans should be visible and countable. Add simple arithmetic checks so that you spot drift as soon as it appears.
# Join sales and customers with explicit keys df <- sales |> left_join(customers, by = "customer_id") # Check for orphans: orders with no matching customer orphans <- sales |> anti_join(customers, by = "customer_id") nrow(orphans) # Example rule: ensure revenue equals qty * unit_price after any changes df <- df |> mutate(revenue_check = qty * unit_price) |> mutate(revenue_diff = abs(revenue - revenue_check)) |> mutate(revenue_flag = revenue_diff > 1e-6) sum(df$revenue_flag, na.rm = TRUE)
Using by= makes your intent obvious. anti_join gives you a concrete list of missing keys that you can hand back to the source team. Arithmetic checks protect against double conversions or unintended type coercion.
Building a Reusable Pipeline
Small functions are easier to test and reuse. Compose them in a single chain and return a list with the cleaned data and diagnostics. That way, consumers of the function can inspect what happened without diving into the internals.
# Small helpers -------------------------------------------------------------
coerce_schema <- function(.df) {
.df |>
janitor::clean_names() |>
mutate(
order_date = lubridate::ymd(order_date, quiet = TRUE),
region = forcats::as_factor(region),
unit_price = readr::parse_number(unit_price)
)
}
handle_missing <- function(.df) {
.df |>
filter(!is.na(qty), !is.na(unit_price)) |>
mutate(
region = fct_explicit_na(region, na_level = "Unknown"),
region_missing = as.integer(region == "Unknown")
)
}
normalize_text <- function(.df) {
.df |>
mutate(
across(c(product_id, product_name, product_size), str_squish),
product_name = str_to_title(product_name)
)
}
winsorize_numeric <- function(.df, cols) {
cap_value <- function(x, p_low = 0.01, p_high = 0.99) {
lo <- quantile(x, p_low, na.rm = TRUE)
hi <- quantile(x, p_high, na.rm = TRUE)
pmin(pmax(x, lo), hi)
}
.df |>
mutate(across(all_of(cols), cap_value))
}
audit_counts <- function(.df, label) {
tibble(step = label, rows = nrow(.df), missing_revenue = sum(is.na(.df$revenue)))
}
# Pipeline ------------------------------------------------------------------
clean_sales <- function(path_sales_csv, path_customers_xlsx) {
raw_sales <- readr::read_csv(path_sales_csv,
col_types = cols(.default = col_guess(),
order_id = col_character(),
customer_id = col_character()))
raw_customers <- readxl::read_excel(path_customers_xlsx) |> janitor::clean_names()
stats <- list()
stats[[length(stats) + 1]] <- audit_counts(raw_sales, "read_sales")
sales1 <- raw_sales |>
coerce_schema() |>
mutate(revenue = qty * unit_price) |>
handle_missing() |>
normalize_text() |>
winsorize_numeric(c("qty", "unit_price", "revenue")) |>
distinct(order_id, product_id, .keep_all = TRUE)
stats[[length(stats) + 1]] <- audit_counts(sales1, "after_cleaning")
df <- sales1 |>
left_join(raw_customers, by = "customer_id")
orphans <- sales1 |> anti_join(raw_customers, by = "customer_id")
list(
data = df,
orphans = orphans,
stats = bind_rows(stats)
)
}
# Run the pipeline
paths <- list(
sales_csv = raw_csv,
cust_xlsx = raw_xlsx
)
result <- clean_sales(paths$sales_csv, paths$cust_xlsx)
cleaned <- result$data
result$stats
nrow(result$orphans)
Each helper does one job and returns a data frame for the next step. The main function reads the inputs, applies the steps, and returns clean data plus diagnostics. You can call it from a notebook, a script, or a scheduler with the same interface.
Writing Clean Outputs and Using Efficient Formats
CSV is universal and easy to inspect. Parquet is fast and compact for pipelines. Writing both keeps collaborators happy and jobs fast.
# Output folders
dir.create(here::here("data", "clean"), showWarnings = FALSE, recursive = TRUE)
# Write CSV
readr::write_csv(cleaned, here::here("data", "clean", "sales_clean.csv"))
# Write Parquet
arrow::write_parquet(cleaned, here::here("data", "clean", "sales_clean.parquet"))
Parquet preserves column types and compresses well. CSV is the format that most teams will open in a spreadsheet. Keeping both avoids churn later.
Here is a template script you can copy so that when you need the smallest working baseline for a new project, you can start here and adapt names and rules to your context.
# scripts/clean_sales.R
suppressPackageStartupMessages({
library(tidyverse); library(janitor); library(naniar)
library(lubridate); library(stringr); library(forcats)
library(readxl); library(arrow); library(here)
})
# 1) Read
path_sales <- here::here("data", "raw", "sales.csv")
path_cust <- here::here("data", "raw", "customers.xlsx")
sales <- readr::read_csv(path_sales, show_col_types = FALSE)
cust <- readxl::read_excel(path_cust) |> clean_names()
# 2) Clean
sales <- sales |>
clean_names() |>
mutate(
order_date = ymd(order_date, quiet = TRUE),
unit_price = parse_number(unit_price),
revenue = qty * unit_price,
region = fct_explicit_na(as_factor(region), "Unknown"),
product_name = str_to_title(str_squish(product_name))
) |>
filter(!is.na(qty), !is.na(unit_price)) |>
distinct(order_id, product_id, .keep_all = TRUE)
# 3) Validate join keys
orphans <- sales |> anti_join(cust, by = "customer_id")
if (nrow(orphans) > 0) warning("Orphan orders: ", nrow(orphans))
# 4) Join
df <- sales |> left_join(cust, by = "customer_id")
# 5) Export
dir.create(here::here("data", "clean"), showWarnings = FALSE)
write_csv(df, here::here("data", "clean", "sales_clean.csv"))
write_parquet(df, here::here("data", "clean", "sales_clean.parquet"))
This is a clean starting point. You can swap the inline cleaning for the function-based pipeline from the previous section when you want more structure and diagnostics.
Conclusion
You now have a clear path for cleaning data in R. Let’s review what we have learned in this article. You read inputs with known types and checked for parsing issues. You audited the structure and missingness before changing anything. You standardized names and schema, set rules for missing values, and fixed numbers, dates, and factors. You normalized text so that joins and groupings behave as expected. You flagged outliers, handled duplicates, reshaped to a tidy form, validated joins and simple rules, and wrapped everything into a pipeline that writes both CSV and Parquet files.
A few practices will make this workflow robust. Keep decisions in the code where they can be reviewed. Record basic metrics before and after each major step. Use explicit keys in joins and make orphan counts visible. Pin package versions with renv so that results are repeatable. If your data has strict rules, add a layer of validation with a package like pointblank and run it in CI.
The next steps are simple: Point the template at your own files. Run the pipeline end to end. Review and adjust the policies to match your domain. Export the clean tables and reload them to confirm types and ranges. Do this on one messy dataset, and then promote the script to your project template.
