Real data almost never arrives ready to use. Column names are inconsistent, values are entered five different ways, numbers come in as text, rows and columns are empty, and the shape is wrong for what you want to do. This page is a quick tour of the everyday cleaning moves in R and the packages that handle them. You don’t need to memorize the code, the goal is to know the concepts and the names of the tools, so you can point an AI assistant at the right one.
Almost everything here lives in the tidyverse: {dplyr} for reshaping rows and columns, {tidyr} for changing a table’s shape, {stringr} for text, {forcats} for categorical variables, and {readr} and {readxl} for reading files. The {janitor} package fills in the cleaning-specific gaps.
The guiding idea underneath all of it is tidy data (Hadley Wickham’s term): each variable is a column, each observation is a row, each cell is a single value. Most of cleaning is just moving a messy dataset toward that shape.
Importing messy data
Cleaning starts at import, because the reader can fix a lot before the
data ever lands in a data frame. For delimited text files,
{readr} gives you read_csv() and
friends; for Excel, {readxl} gives you
read_excel().
The arguments are where the cleaning happens. skip = drops junk rows
above the real header, na = tells R which strings mean missing
("NA", "-", "N/A", ""), and col_types = forces a column to be
read as text or number instead of letting R guess wrong. Here is a messy
file of dog-breed popularity rankings, read straight from the course
repo:
library(tidyverse)
dogranks <- read_csv(
"https://raw.githubusercontent.com/rfortherestofus/data-cleaning-course/refs/heads/master/data/dogranks-your.csv"
)
dogranks
# A tibble: 5 × 7
Breed `2019` `2018` `2017` Size `2016` `2015`
<chr> <dbl> <dbl> <dbl> <chr> <dbl> <dbl>
1 Retrievers (Labrador) 1 1 1 large 1 1
2 German Shepherd Dogs 2 2 2 large 2 2
3 Retrievers (Golden) 3 3 3 large 3 3
4 French Bulldogs 4 4 4 small 6 6
5 Bulldogs 5 5 5 medium 4 4
read_excel() takes the same idea further, since a spreadsheet often
has several sheets and stray notes in the margins. Its most useful
arguments are sheet = (by name or number), skip =, and range = to
grab just the rectangle that holds the data:
library(readxl)
read_excel("marine-protected-areas.xlsx", sheet = "MPAs", range = "A3:H120")
When a spreadsheet is genuinely broken (header text split across two
rows, category labels embedded as bare rows in the middle of the data),
the {unheadr} package by Luis Verde
Arregoitia is built exactly for that, with mash_colnames() to fold
multi-row headers into one and untangle2() to pull embedded subheaders
into their own column. It is the tool to reach for when the mess is
structural rather than just untidy.
Tidy column names
The first thing I do after importing is fix the column names. Names like
Protected Area NAME, 2019, and Extent (km2) are painful to type
and reference. janitor::clean_names() rewrites every name to a
consistent snake_case: lowercase, underscores for spaces, no
punctuation, and no names that start with a digit.
messy <- tribble(
~"First Name", ~"Height (cm)", ~"2019 Rank",
"Fido", 52, 1,
"Rex", 60, 2
)
messy |>
janitor::clean_names()
# A tibble: 2 × 3
first_name height_cm x2019_rank
<chr> <dbl> <dbl>
1 Fido 52 1
2 Rex 60 2
clean_names() takes a case = argument if you prefer something other
than snake case, but the default is the right choice almost every time.
Selecting, renaming, and filtering
With clean names in hand, the core {dplyr} verbs pare a dataset down to
the rows and columns you care about. I’ll use dplyr::starwars, which
ships with the package:
select()keeps or drops columns, and pairs with selection helpers likestarts_with(),contains(),matches()(a regular expression), andwhere()(a condition, such aswhere(is.numeric)).rename()changes a column name (new_name = old_name).filter()keeps rows that meet a condition.
starwars |>
select(name, height, mass, species, homeworld) |>
rename(planet = homeworld) |>
filter(species == "Human", height > 180)
# A tibble: 15 × 5
name height mass species planet
<chr> <int> <dbl> <chr> <chr>
1 Darth Vader 202 136 Human Tatooine
2 Biggs Darklighter 183 84 Human Tatooine
3 Obi-Wan Kenobi 182 77 Human Stewjon
4 Anakin Skywalker 188 84 Human Tatooine
5 Boba Fett 183 78.2 Human Kamino
6 Qui-Gon Jinn 193 89 Human <NA>
7 Padmé Amidala 185 45 Human Naboo
8 Ric Olié 183 NA Human Naboo
9 Quarsh Panaka 183 NA Human Naboo
10 Mace Windu 188 84 Human Haruun Kal
11 Cliegg Lars 183 NA Human Tatooine
12 Dooku 193 80 Human Serenno
13 Bail Prestor Organa 191 NA Human Alderaan
14 Jango Fett 183 79 Human Concord Dawn
15 Raymus Antilles 188 79 Human Alderaan
Creating and recoding columns
mutate() adds or overwrites a column. Paired with it, case_when()
recodes a variable into categories by testing a series of conditions in
order, using the first that matches. The modern form ends with
.default = for the catch-all case (older code wrote TRUE ~ ...):
starwars |>
mutate(
bmi = mass / (height / 100)^2,
size = case_when(
height < 100 ~ "short",
height < 200 ~ "average",
.default = "tall"
)
) |>
select(name, height, mass, bmi, size)
# A tibble: 87 × 5
name height mass bmi size
<chr> <int> <dbl> <dbl> <chr>
1 Luke Skywalker 172 77 26.0 average
2 C-3PO 167 75 26.9 average
3 R2-D2 96 32 34.7 short
4 Darth Vader 202 136 33.3 tall
5 Leia Organa 150 49 21.8 average
6 Owen Lars 178 120 37.9 average
7 Beru Whitesun Lars 165 75 27.5 average
8 R5-D4 97 32 34.0 short
9 Biggs Darklighter 183 84 25.1 average
10 Obi-Wan Kenobi 182 77 23.2 average
# ℹ 77 more rows
To apply the same transformation to many columns at once, wrap it in
across(). This is the modern replacement for the old mutate_at(),
mutate_if(), and mutate_all() variants. Here every character column
has its stray whitespace squished in one line:
starwars |>
mutate(across(where(is.character), str_squish))
# A tibble: 87 × 14
name height mass hair_color skin_color eye_color birth_year sex gender
<chr> <int> <dbl> <chr> <chr> <chr> <dbl> <chr> <chr>
1 Luke Sk… 172 77 blond fair blue 19 male mascu…
2 C-3PO 167 75 <NA> gold yellow 112 none mascu…
3 R2-D2 96 32 <NA> white, bl… red 33 none mascu…
4 Darth V… 202 136 none white yellow 41.9 male mascu…
5 Leia Or… 150 49 brown light brown 19 fema… femin…
6 Owen La… 178 120 brown, gr… light blue 52 male mascu…
7 Beru Wh… 165 75 brown light blue 47 fema… femin…
8 R5-D4 97 32 <NA> white, red red NA none mascu…
9 Biggs D… 183 84 black light brown 24 male mascu…
10 Obi-Wan… 182 77 auburn, w… fair blue-gray 57 male mascu…
# ℹ 77 more rows
# ℹ 5 more variables: homeworld <chr>, species <chr>, films <list>,
# vehicles <list>, starships <list>
Handling missing values
Missing values need two separate decisions: how to represent them, and what to do with them.
First, get them represented as real NAs. Data often smuggles missing
values in as "-", "none", or an empty string. na_if() converts a
specific value to NA:
tibble(range = c("5", "none", "12", "none")) |>
mutate(range = na_if(range, "none"))
# A tibble: 4 × 1
range
<chr>
1 5
2 <NA>
3 12
4 <NA>
Once they are proper NAs, {tidyr} and {dplyr} give you the common
moves:
drop_na()removes rows with missing values (optionally in named columns only).fill()carries the last value down (or up) to fill blanks, which is how spreadsheets often “imply” a repeated grouping value.coalesce()takes the first non-missing value across several columns, useful when the number you want is spread across a few partly-filled columns.
The course’s fish-landings data shows all three: the Fish and Lake
columns are filled in only on the first row of each group, and the total
is scattered across three columns.
fish <- read_csv(
"https://raw.githubusercontent.com/rfortherestofus/data-cleaning-course/refs/heads/master/data/fish-landings.csv"
)
fish |>
fill(Fish, Lake) |>
mutate(
total = coalesce(
`Commission reported total`,
`Official total`,
`Previous year total`
)
) |>
select(Fish, Lake, Month, total)
# A tibble: 12 × 4
Fish Lake Month total
<chr> <chr> <chr> <dbl>
1 Tilapia Jipe Jan 9553
2 Tilapia Jipe Feb 9320
3 Tilapia Jipe Mar 8447
4 Tilapia Jipe Apr 8948
5 Tilapia Jipe May 8850
6 Tilapia Jipe Jun 8661
7 Clarias Jipe Jan 1521
8 Clarias Jipe Feb 1524
9 Clarias Jipe Mar 1496
10 Clarias Jipe Apr 1312
11 Clarias Jipe May 1204
12 Clarias Jipe Jun 1250
For a quick audit of where the gaps are, count() on the missing flag,
or the {naniar} package, gives you a
fast picture of missingness across the whole table.
Reshaping: longer and wider
Data often arrives in the wrong shape for analysis. The two operations that fix this both come from {tidyr}, and they are inverses of each other:
pivot_longer()turns columns into rows. Use it when column names are really data (a column per year, per month, per survey question).pivot_wider()turns rows into columns, the reverse.
(These replace the older gather() and spread(), which are retired.)
The dog-rankings data has a column per year, which is exactly the case
for pivot_longer():
dogranks_long <- dogranks |>
pivot_longer(
cols = c(`2019`, `2018`, `2017`, `2016`, `2015`),
names_to = "year",
values_to = "rank"
)
dogranks_long
# A tibble: 25 × 4
Breed Size year rank
<chr> <chr> <chr> <dbl>
1 Retrievers (Labrador) large 2019 1
2 Retrievers (Labrador) large 2018 1
3 Retrievers (Labrador) large 2017 1
4 Retrievers (Labrador) large 2016 1
5 Retrievers (Labrador) large 2015 1
6 German Shepherd Dogs large 2019 2
7 German Shepherd Dogs large 2018 2
8 German Shepherd Dogs large 2017 2
9 German Shepherd Dogs large 2016 2
10 German Shepherd Dogs large 2015 2
# ℹ 15 more rows
pivot_wider() puts it back, spreading year back out into one column
each:
dogranks_long |>
pivot_wider(names_from = year, values_from = rank)
# A tibble: 5 × 7
Breed Size `2019` `2018` `2017` `2016` `2015`
<chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Retrievers (Labrador) large 1 1 1 1 1
2 German Shepherd Dogs large 2 2 2 2 2
3 Retrievers (Golden) large 3 3 3 3 3
4 French Bulldogs small 4 4 4 6 6
5 Bulldogs medium 5 5 5 4 4
Cleaning strings
Text columns are where the mess concentrates: stray whitespace, mixed
capitalization, and several values crammed into one cell.
{stringr} handles all of it, and every
function starts with str_ so they are easy to find. The ones that come
up constantly:
str_squish()andstr_trim()remove leading, trailing, and repeated internal whitespace.str_to_lower(),str_to_upper(),str_to_title()standardize case.str_detect()tests whether a pattern is present (great insidefilter()).str_replace()andstr_remove()swap or delete matched text.
tibble(country = c(" Cook Islands ", "ANTARCTICA", "united states ")) |>
mutate(
country = str_squish(country),
country = str_to_title(country)
)
# A tibble: 3 × 1
country
<chr>
1 Cook Islands
2 Antarctica
3 United States
Most of these functions take a regular expression as the pattern, which is worth a section of its own.
Regular expressions
A regular expression (regex) is a small pattern language for
describing text: “three digits in a row”, “starts with a capital
letter”, “everything before the first comma”. It is the engine inside
most of the str_ functions, and it collapses a whole class of fiddly
string problems into a single line. Regex is famously cryptic, but a
little goes a long way, and because it works the same everywhere (R,
other languages, your editor’s find-and-replace) it is a great thing to
hand to an AI: describe the pattern in plain words and let it write the
expression.
The pieces you reach for most:
- Character classes.
[abc]matches any of those letters,[a-z]any lowercase letter,[0-9]a digit. There are shorthands:\\dis a digit,\\wa word character,\\swhitespace, and the capitalized versions\\D,\\W,\\Sare their opposites. A bare.matches any single character. - Anchors.
^matches the start of the string,$the end, and\\ba word boundary. - Quantifiers.
*is “zero or more”,+is “one or more”,?is “optional”,{3}is “exactly three”, and{2,4}is “two to four”. - Groups and alternation. Parentheses
( )group part of a pattern (and capture it for reuse), and|means “or”, socat|dogmatches either word.
One R quirk to know: backslashes are special in R strings too, so you
write a regex backslash as a double backslash, "\\d" for a digit.
The {stringr} functions that take a
pattern are str_detect() (test for a match, pairs well with
filter()), str_extract() and str_extract_all() (pull matches out),
str_replace() and str_replace_all() (find and replace),
str_remove() and str_remove_all() (delete matches), and
str_view(), which shows you exactly what a pattern matches as you
build it. Here are a few on some messy contact strings:
library(stringr)
contacts <- c("Call 503-555-0142", "Reach me at (971) 555-8890.", "no phone")
# Which entries contain a phone number?
str_detect(contacts, "\\d{3}-\\d{4}")
[1] TRUE TRUE FALSE
# Pull the phone number out (with or without a parenthesized area code)
str_extract(contacts, "\\(?\\d{3}\\)?[ -]?\\d{3}-\\d{4}")
[1] "503-555-0142" "(971) 555-8890" NA
# Strip everything that is not a digit
str_remove_all(contacts, "\\D")
[1] "5035550142" "9715558890" ""
Parentheses capture, and you refer back to a captured group with \\1,
\\2, and so on. That makes reordering easy, for example turning “Last,
First” into “First Last”:
str_replace(c("Wilke, Claus", "Mock, Tom"), "(\\w+), (\\w+)", "\\2 \\1")
[1] "Claus Wilke" "Tom Mock"
When a pattern will not behave, build it up piece by piece with
str_view(), and lean on the {stringr}
cheatsheet or an AI
assistant to translate “what I want to match” into the actual
expression.
Factors
Categorical variables (a fixed set of groups: species, region, survey
response) are stored as factors.
{forcats} manages them, and its
functions all start with fct_. The moves that matter for cleaning:
fct_recode()renames levels.fct_lump_n()collapses all but the most common n levels into a single"Other", which tames a column with a long tail of rare categories.fct_infreq()andfct_reorder()set the level order (by frequency, or by another variable), which mostly matters for how tables and plots come out.
starwars |>
filter(!is.na(species)) |>
mutate(species = fct_lump_n(species, n = 3)) |>
count(species, sort = TRUE)
# A tibble: 4 × 2
species n
<fct> <int>
1 Other 39
2 Human 35
3 Droid 6
4 Gungan 3
Joining tables
Cleaning frequently means combining a dataset with another: a lookup
table of full country names, a set of labels, a second year of data. The
{dplyr} *_join() functions match rows between two tables on a shared
key column:
left_join()keeps every row of the first table, adding columns from the second where the key matches (the workhorse).inner_join()keeps only rows that match in both.full_join()keeps everything from both.
{dplyr} bundles two tiny tables to demonstrate. band_members and
band_instruments share a name column, which becomes the join key:
band_members |>
left_join(band_instruments, by = "name")
# A tibble: 3 × 3
name band plays
<chr> <chr> <chr>
1 Mick Stones <NA>
2 John Beatles guitar
3 Paul Beatles bass
A join is also the cleanest way to spot mismatches: rows that fail to
match (a country spelled differently in the two tables, a stray trailing
space in a key) surface as NAs in the joined columns, pointing you
straight at the next thing to clean.