pop_wide <- data.frame(
country = c("Norway", "Sweden", "Denmark"),
pop_2020 = c(5380, 10350, 5831),
pop_2021 = c(5391, 10380, 5840),
pop_2022 = c(5408, 10420, 5857)
)
pop_wide
#> country pop_2020 pop_2021 pop_2022
#> 1 Norway 5380 5391 5408
#> 2 Sweden 10350 10380 10420
#> 3 Denmark 5831 5840 585716 Tidy data
You have a data frame with population figures for three countries, and the years are column names: pop_2020, pop_2021, pop_2022. You want to plot population over time, so you try mapping year to the x-axis. There is no year column. The years are there, but they live in the column headers, where no verb can reach them. Every dplyr verb from Chapter 14, every ggplot2 aesthetic you will write, every model formula you will fit assumes that each variable lives in its own column. When it doesn’t, nothing works. Getting data into that shape takes more of a working analyst’s time than any model or plot, and this chapter treats it as the real work.
You already have the pieces: data frames, verbs, pipes. What you’re missing is the target shape those tools expect.
16.1 What tidy means
Here is the population table from the opening, with years spread across the column names:
What is the variable here? Year. But year doesn’t appear as a column; it’s buried inside the names pop_2020, pop_2021, pop_2022, three columns where there should be one. The same nine values can be arranged with year as a column of its own:
pop_tidy <- data.frame(
country = rep(c("Norway", "Sweden", "Denmark"), each = 3),
year = rep(2020:2022, times = 3),
population = c(5380, 5391, 5408, 10350, 10380, 10420, 5831, 5840, 5857)
)
pop_tidy
#> country year population
#> 1 Norway 2020 5380
#> 2 Norway 2021 5391
#> 3 Norway 2022 5408
#> 4 Sweden 2020 10350
#> 5 Sweden 2021 10380
#> 6 Sweden 2022 10420
#> 7 Denmark 2020 5831
#> 8 Denmark 2021 5840
#> 9 Denmark 2022 5857Now you can filter by year, group by country, plot year on the x-axis. Every dplyr verb and every ggplot2 aesthetic works because each variable occupies its own column and each observation its own row.
Hadley Wickham’s 2014 paper “Tidy data” turns that into three rules:
- Each variable is a column.
- Each observation is a row.
- Each type of observational unit is a table.
They sound obvious, and most real-world data still breaks at least one of them. Recognizing which rule is broken (let alone fixing it) takes practice.
NoteCodd’s normal forms
The three rules are a simplified version of something database designers have used since the 1970s, when Edgar Codd defined a set of normal forms to remove redundancy from relational tables (Section 11.1). The structural problem R programmers wrestle with today is the one Codd solved before R existed.
But who wants to rearrange nine values by hand?
Exercises
- Look at this table. Which tidy-data rule does it break?
data.frame(
student = c("Alice", "Bob"),
math_score = c(90, 85),
english_score = c(78, 92)
)Sketch (on paper or in a comment) what the tidy version of that table would look like. How many rows? How many columns?
A data frame has columns
name,date, andtemperature. One row per city per day. Is this tidy? Why or why not?
16.2 pivot_longer(): columns to rows
pivot_longer() from the tidyr package does the reshaping in one call:
library(tidyr)
library(dplyr)
#>
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#>
#> filter, lag
#> The following objects are masked from 'package:base':
#>
#> intersect, setdiff, setequal, union
pop_wide |>
pivot_longer(
cols = pop_2020:pop_2022,
names_to = "year",
values_to = "population"
)
#> # A tibble: 9 × 3
#> country year population
#> <chr> <chr> <dbl>
#> 1 Norway pop_2020 5380
#> 2 Norway pop_2021 5391
#> 3 Norway pop_2022 5408
#> 4 Sweden pop_2020 10350
#> 5 Sweden pop_2021 10380
#> 6 Sweden pop_2022 10420
#> 7 Denmark pop_2020 5831
#> 8 Denmark pop_2021 5840
#> 9 Denmark pop_2022 5857The nine population values are all still there, one per row instead of three per row. pivot_longer() and pivot_wider() are inverses: apply one and then the other and you are back where you started, up to column order. A pair of reversible transformations like this is called an isomorphism, two structures holding the same information in different arrangements, so neither wide nor long is “more correct.”
What changes when you pivot is the alignment between the data’s shape and your next tool’s expectations. A report reader wants wide, because scanning countries across years in a single row is easier than scrolling. A ggplot() call wants long, because each aesthetic maps to one column and each observation occupies one row. Grouped summaries need the grouping variable to be a column, not a set of column names baked into the header. Choosing a format is choosing which tool you are about to hand the data to, and pivoting is the translation between those expectations.
The hard part is deciding what to reshape. Given a table with merged cells, hierarchical headers, and embedded metadata, the tidy structure depends on a judgment about what counts as a variable, an observation, and a value. Wickham’s paper defines those three terms precisely so that the judgment can be made: once you answer “is year a variable or a value?”, the reshaping follows mechanically.
The call above names three things. cols says which columns to pivot, as a range with : or a helper like starts_with() or where(is.numeric); names_to names the new column made from the old column names; values_to names the new column made from the cell values.
The column names still carry the pop_ prefix, and year is character, not integer. Two more arguments fix that:
pop_wide |>
pivot_longer(
cols = pop_2020:pop_2022,
names_to = "year",
names_prefix = "pop_",
names_transform = list(year = as.integer),
values_to = "population"
)
#> # A tibble: 9 × 3
#> country year population
#> <chr> <int> <dbl>
#> 1 Norway 2020 5380
#> 2 Norway 2021 5391
#> 3 Norway 2022 5408
#> 4 Sweden 2020 10350
#> 5 Sweden 2021 10380
#> 6 Sweden 2022 10420
#> 7 Denmark 2020 5831
#> 8 Denmark 2021 5840
#> 9 Denmark 2022 5857names_prefix strips the leading text; names_transform applies a function to the extracted names, here converting character years to integers.
For more structured column names, names_sep and names_pattern split each name into several new columns: bill_length_mm and bill_depth_mm can be split on _ into the measurement and the unit. The tidyr pivoting vignette covers these cases. But what about the opposite direction, when your data is too long?
Exercises
- Create this data frame and pivot it to tidy form with columns
city,quarter, andsales:
quarterly <- data.frame(
city = c("Oslo", "Bergen"),
Q1 = c(120, 95),
Q2 = c(135, 110),
Q3 = c(150, 105),
Q4 = c(160, 130)
)Modify your pivot to strip the
Qprefix from the quarter column and convert it to an integer.What happens if you set
values_toto the name of an existing column? Try it.
16.3 pivot_wider(): rows to columns
Sometimes the problem goes the other way: distinct variables are stacked into a single column, and you need to spread them out.
measurements <- data.frame(
site = rep(c("A", "B"), each = 2),
measurement = rep(c("temperature", "humidity"), times = 2),
value = c(22.1, 65, 19.8, 72)
)
measurements
#> site measurement value
#> 1 A temperature 22.1
#> 2 A humidity 65.0
#> 3 B temperature 19.8
#> 4 B humidity 72.0Temperature and humidity are different variables, and cramming them into a single value column conflates two things that should be separate. pivot_wider() pulls them apart:
measurements |>
pivot_wider(
names_from = measurement,
values_from = value
)
#> # A tibble: 2 × 3
#> site temperature humidity
#> <chr> <dbl> <dbl>
#> 1 A 22.1 65
#> 2 B 19.8 72names_from says which column’s values become the new column names, and values_from says which column fills the new cells.
When some combinations don’t exist in the data, pivot_wider() fills those cells with NA. The values_fill argument lets you choose a different default:
incomplete <- data.frame(
site = c("A", "A", "B"),
measurement = c("temperature", "humidity", "temperature"),
value = c(22.1, 65, 19.8)
)
incomplete |>
pivot_wider(
names_from = measurement,
values_from = value,
values_fill = 0
)
#> # A tibble: 2 × 3
#> site temperature humidity
#> <chr> <dbl> <dbl>
#> 1 A 22.1 65
#> 2 B 19.8 0
TipOpinion
If you reach for pivot_wider() during analysis, pause and ask whether your downstream tool really needs wide data. Usually it doesn’t. pivot_wider() is most useful for building display tables and for feeding data into functions that expect matrix-like input. The fact that wide format looks tidier to human eyes doesn’t mean it is tidier for your code.
Exercises
Start with
pop_tidy(the tidy population table from Section 16.1). Usepivot_wider()to recreate the wide format, with one column per year.What happens if the combination of id columns isn’t unique before widening? Create a data frame where two rows have the same
siteandmeasurementand trypivot_wider(). Read the warning.
16.4 When data isn’t tidy (and that’s fine)
Tidy data is the shape for analysis. A table also gets read, printed, and typed in, and those jobs want a different shape.
Wide tables are easier for humans to scan: a table with countries as rows and years as columns fits on a screen, while the same data in long form might scroll for hundreds of rows. Wide format is also what some matrix operations, correlation computations, and model interfaces expect when they want one column per predictor.
Data entry follows the same logic. If you’re recording daily temperatures for three cities, a spreadsheet with cities as rows and dates as columns feels natural; nobody fills in a three-column long-form table by hand (and anyone who tells you they do is lying, or masochistic, or both).
So reshape to tidy when you need dplyr and ggplot, reshape to wide when you need a human to read the table. Do not get religious about it.
Sometimes the information you need spans multiple tables.
16.5 Joins: combining tables
You have customers and orders, species and observations, sites and measurements. The data lives in separate tables, linked by a shared key. Joins are how you bring them together.
bands <- data.frame(
name = c("John", "Paul", "George", "Ringo"),
plays = c("guitar", "bass", "guitar", "drums")
)
albums <- data.frame(
name = c("John", "Paul", "George", "Mick"),
album = c("Imagine", "Ram", "All Things Must Pass", "Some Girls")
)left_join() keeps all rows from the left table and attaches matching columns from the right, filling NA where no match exists:
bands |> left_join(albums, by = "name")
#> name plays album
#> 1 John guitar Imagine
#> 2 Paul bass Ram
#> 3 George guitar All Things Must Pass
#> 4 Ringo drums <NA>Ringo has no album in the albums table, so his album comes back NA; Mick has no row in bands, so he’s dropped entirely. This is the workhorse join: keep everything on the left, enrich with what you can from the right.
inner_join() is stricter, keeping only rows that match in both tables:
bands |> inner_join(albums, by = "name")
#> name plays album
#> 1 John guitar Imagine
#> 2 Paul bass Ram
#> 3 George guitar All Things Must PassRingo and Mick are both gone. Only the three names appearing in both tables survive. Both joins add columns from the second table to the first; dplyr calls them mutating joins.
When the key columns have different names, use join_by():
instruments <- data.frame(
musician = c("John", "Paul", "George", "Ringo"),
instrument = c("guitar", "bass", "guitar", "drums")
)
albums |> left_join(instruments, by = join_by(name == musician))
#> name album instrument
#> 1 John Imagine guitar
#> 2 Paul Ram bass
#> 3 George All Things Must Pass guitar
#> 4 Mick Some Girls <NA>semi_join() keeps the rows in the left table that have a match in the right, and adds nothing:
bands |> semi_join(albums, by = "name")
#> name plays
#> 1 John guitar
#> 2 Paul bass
#> 3 George guitarSame three rows as inner_join(), but without the album column. You’re using albums as a filter, not as a source of new data.
anti_join() does the opposite, keeping rows in the left table that have no match in the right:
bands |> anti_join(albums, by = "name")
#> name plays
#> 1 Ringo drumsOnly Ringo: the one band member with no album in the albums table. anti_join() is your best tool for finding what’s missing. Which customers placed no orders? Which species have no observations? Which students didn’t submit? These two are filtering joins: they change which rows survive and leave the columns alone.
TipOpinion
Start with left_join(). Use anti_join() to debug. The others are situational. right_join() exists but is rarely needed: swap the table order and use left_join() instead. full_join() keeps everything from both sides, which sounds safe but usually signals that you haven’t decided what your unit of analysis actually is.
Exercises
Create two small data frames:
studentswith columnsidandname, andgradeswith columnsidandscore. Give them overlapping but not identicalidvalues. Useleft_join()to combine them.Use
anti_join()on the same tables to find students with no grades and grades with no student. (Hint: swap which table is on the left.)What is the difference between
semi_join(bands, albums, by = "name")andinner_join(bands, albums, by = "name") |> select(name, plays)? Try both.
16.6 Other tidyr tools
After pivoting and joining, a handful of other tidyr functions handle the messy details that remain.
Separating and uniting columns
separate_wider_delim() splits one column into several by a delimiter:
dates <- data.frame(
id = 1:3,
date = c("2024-03-15", "2024-07-22", "2024-11-01")
)
dates |>
separate_wider_delim(
cols = date,
delim = "-",
names = c("year", "month", "day")
)
#> # A tibble: 3 × 4
#> id year month day
#> <int> <chr> <chr> <chr>
#> 1 1 2024 03 15
#> 2 2 2024 07 22
#> 3 3 2024 11 01The older separate() still works and shows up in a lot of existing code. For patterns a single delimiter cannot express, separate_wider_regex() takes a regular expression.
unite() does the inverse, pasting columns together:
data.frame(year = 2024, month = "03", day = 15) |>
unite(col = "date", year, month, day, sep = "-")
#> date
#> 1 2024-03-15Completing and filling
complete() makes implicit missing values explicit. If your data has observations for some year/site combinations but not all, complete() adds rows for the gaps and fills them with NA:
obs <- data.frame(
year = c(2020, 2020, 2021),
site = c("A", "B", "A"),
count = c(5, 3, 7)
)
obs |> complete(year, site)
#> # A tibble: 4 × 3
#> year site count
#> <dbl> <chr> <dbl>
#> 1 2020 A 5
#> 2 2020 B 3
#> 3 2021 A 7
#> 4 2021 B NAfill() propagates the last non-missing value downward (or upward), which is useful for data where group labels are only written once and the rows beneath are left blank:
report <- data.frame(
group = c("Control", NA, NA, "Treatment", NA),
value = c(10, 12, 11, 20, 22)
)
report |> fill(group)
#> group value
#> 1 Control 10
#> 2 Control 12
#> 3 Control 11
#> 4 Treatment 20
#> 5 Treatment 22Nesting
nest() packs subsets of a data frame into list-columns, and unnest() unpacks them. This pattern shines when fitting models to groups, but it requires familiarity with list-columns and iteration, both covered in Section 19.3.
16.7 Summary
Tidy data is a structural contract: one variable per column, one observation per row, one observational unit per table. pivot_longer() turns columns into rows (the common direction), pivot_wider() turns rows into columns (the occasional direction), and joins combine tables by matching keys.
NoteThe mathematics underneath
In category theory, a join is a pullback: given two tables that each map to a shared key space, the join constructs the most general table consistent with both. inner_join() keeps only the rows whose keys appear on both sides; left_join() keeps every row on the left and fills the gaps with NA. The same construction appears in Codd’s relational algebra (Chapter 14), which is why joins look alike in R, SQL, and formal logic.
Shape is half the story. Once your data is tidy, every column is something a plot can map to an axis, a color, or a panel, and that is where the next chapter starts.