Tidying and Reshaping Data
Pivot between wide and long with tidyr, split and combine columns, unnest list-columns from JSON, and fill the gaps a time series leaves behind.
Most real data arrives in the wrong shape. tidyr is four ideas — pivot, split, nest, fill —
that convert it into the shape every other package expects.
Wide to long
A report exported from a spreadsheet, with months as columns:
suppressPackageStartupMessages(library(tidyverse))
wide <- tibble(
channel = c("web", "app", "phone"),
`2026-01` = c(41200.50, 28100.00, 9400.25),
`2026-02` = c(43880.75, 31020.40, 8800.00),
`2026-03` = c(45120.00, 34410.10, 9120.60)
)
print(wide)# A tibble: 3 × 4
channel `2026-01` `2026-02` `2026-03`
<chr> <dbl> <dbl> <dbl>
1 web 41200. 43881. 45120
2 app 28100 31020. 34410.
3 phone 9400. 8800 9121.Readable for a person, useless for a computer — “which month” is stored in a column name, so you cannot filter, group or plot by it.
long <- wide |>
pivot_longer(
cols = -channel,
names_to = "month",
values_to = "revenue"
)
print(long)# A tibble: 9 × 3
channel month revenue
<chr> <chr> <dbl>
1 web 2026-01 41200.
2 web 2026-02 43881.
3 web 2026-03 45120
4 app 2026-01 28100
5 app 2026-02 31020.
6 app 2026-03 34410.
7 phone 2026-01 9400.
8 phone 2026-02 8800
9 phone 2026-03 9121.Now month is data. Everything downstream works:
long |> summarise(revenue = sum(revenue), .by = month) |> print()
long |> slice_max(revenue, n = 1, by = channel) |> print()# A tibble: 3 × 2
month revenue
<chr> <dbl>
1 2026-01 78701.
2 2026-02 83701.
3 2026-03 88651.
# A tibble: 3 × 3
channel month revenue
<chr> <chr> <dbl>
1 web 2026-03 45120
2 app 2026-03 34410.
3 phone 2026-01 9400.Convert types during the pivot rather than afterwards:
long <- wide |>
pivot_longer(-channel, names_to = "month", values_to = "revenue",
names_transform = list(month = \(x) as.Date(paste0(x, "-01"))))
print(long, n = 3)# A tibble: 9 × 3
channel month revenue
<chr> <date> <dbl>
1 web 2026-01-01 41200.
2 web 2026-02-01 43881.
3 web 2026-03-01 45120 Column names carrying two variables
messy <- tibble(
channel = c("web", "app"),
jan_orders = c(1204L, 880L), jan_revenue = c(41200.50, 28100.00),
feb_orders = c(1288L, 942L), feb_revenue = c(43880.75, 31020.40)
)
tidy <- messy |>
pivot_longer(
-channel,
names_to = c("month", ".value"),
names_sep = "_"
)
print(tidy)# A tibble: 4 × 4
channel month orders revenue
<chr> <chr> <int> <dbl>
1 web jan 1204 41200.
2 web feb 1288 43881.
3 app jan 880 28100
4 app feb 942 31020..value is the piece worth learning: it tells pivot_longer that part of the name is the
column the value belongs in. One call turned four columns into two variables and a key, and
orders stayed an integer while revenue stayed a double — a plain pivot would have forced
both into one column and one type.
Long to wide
orders <- tibble(
order_id = rep(1001:1004, each = 2),
field = rep(c("status", "channel"), 4),
value = c("completed","web", "returned","app", "completed","phone", "pending","web")
)
print(orders, n = 4)
orders |> pivot_wider(names_from = field, values_from = value) |> print()# A tibble: 8 × 3
order_id field value
<int> <chr> <chr>
1 1001 status completed
2 1001 channel web
3 1002 status returned
4 1002 channel app
# ℹ 4 more rows
# A tibble: 4 × 3
order_id status channel
<int> <chr> <chr>
1 1001 completed web
2 1002 returned app
3 1003 completed phone
4 1004 pending web That key-value shape is what you get from an EAV table or a survey export, and pivot_wider
is the way back.
It warns when the combination is not unique, which is the failure worth knowing:
dupes <- bind_rows(orders, tibble(order_id = 1001L, field = "status", value = "refunded"))
dupes |> pivot_wider(names_from = field, values_from = value) |> print(n = 2)Warning message:
Values from `value` are not uniquely identified; output will contain list-cols.
• Use `values_fn = list` to suppress this warning.
# A tibble: 4 × 3
order_id status channel
<int> <list> <list>
1 1001 <chr [2]> <chr [1]>
2 1002 <chr [1]> <chr [1]>The columns became list-columns rather than values. Decide explicitly what a duplicate means:
dupes |>
pivot_wider(names_from = field, values_from = value, values_fn = dplyr::last) |>
print(n = 2)# A tibble: 4 × 3
order_id status channel
<int> <chr> <chr>
1 1001 refunded web
2 1002 returned app Splitting and combining columns
raw <- tibble(
ref = c("GB-2026-1001", "US-2026-1002", "NL-2026-1003"),
customer = c("Lovelace, Ada", "Hopper, Grace", "Dijkstra, Edsger")
)
clean <- raw |>
separate_wider_delim(ref, delim = "-", names = c("country", "year", "order_id")) |>
separate_wider_delim(customer, delim = ", ", names = c("last_name", "first_name")) |>
mutate(order_id = as.integer(order_id), year = as.integer(year))
print(clean)# A tibble: 3 × 5
country year order_id last_name first_name
<chr> <int> <int> <chr> <chr>
1 GB 2026 1001 Lovelace Ada
2 US 2026 1002 Hopper Grace
3 NL 2026 1003 Dijkstra Edsger A row that does not split into the expected number of pieces is an error, not a silent NA:
bad <- tibble(ref = c("GB-2026-1001", "MALFORMED"))
bad |> separate_wider_delim(ref, delim = "-", names = c("country", "year", "order_id"))Error in `separate_wider_delim()`:
! Expected 3 pieces in each element of `ref`.
! 1 value was too short.
ℹ Use `too_few = "debug"` to diagnose the problem.
ℹ Use `too_few = "align_start"/"align_end"` to silence this message.bad |>
separate_wider_delim(ref, delim = "-", names = c("country", "year", "order_id"),
too_few = "debug") |>
print()# A tibble: 2 × 6
country year order_id ref_ok ref ref_pieces
<chr> <chr> <chr> <lgl> <chr> <int>
1 GB 2026 1001 TRUE GB-2026-1001 3
2 MALFORMED NA NA FALSE MALFORMED 1too_few = "debug" adds _ok and _pieces columns so you can quarantine the bad rows rather
than guess at them. Going the other way is unite():
clean |> unite("ref", country, year, order_id, sep = "-") |> print(n = 2)# A tibble: 3 × 3
ref last_name first_name
<chr> <chr> <chr>
1 GB-2026-1001 Lovelace Ada
2 US-2026-1002 Hopper Grace List-columns and nested JSON
library(jsonlite)
events <- tibble(
order_id = c(1001L, 1002L),
payload = list(
list(customer = list(id = 1, country = "GB"),
items = list(list(sku = "BK-1041", qty = 1, price = 25.50),
list(sku = "BK-2277", qty = 2, price = 12.00))),
list(customer = list(id = 2, country = "US"),
items = list(list(sku = "BK-9001", qty = 1, price = 40.00)))
)
)
lines <- events |>
unnest_wider(payload) |>
unnest_wider(customer, names_sep = "_") |>
unnest_longer(items) |>
unnest_wider(items) |>
mutate(line_total = qty * price)
print(lines)# A tibble: 3 × 7
order_id customer_id customer_country sku qty price line_total
<int> <dbl> <chr> <chr> <dbl> <dbl> <dbl>
1 1001 1 GB BK-1041 1 25.5 25.5
2 1001 1 GB BK-2277 2 12 24
3 1002 2 US BK-9001 1 40 40 Two functions, used in a pattern worth memorising:
| Function | Turns | Into |
|---|---|---|
unnest_wider() | a list of named fields | one column per field |
unnest_longer() | a list of items | one row per item |
An object becomes columns; an array becomes rows. Chaining them flattens any JSON document, which is how you get from an event feed to a table you can aggregate.
Note the row count: two events became three order lines. Watch that number — an empty items
array drops the parent row entirely unless you pass keep_empty = TRUE:
with_empty <- tibble(order_id = 1003L, items = list(list()))
with_empty |> unnest_longer(items) |> nrow() |> print()
with_empty |> unnest_longer(items, keep_empty = TRUE) |> nrow() |> print()[1] 0
[1] 1Nesting on purpose
The reverse operation groups rows into sub-tables, which is how you fit one model per group in lesson 8:
sales <- tibble(
channel = rep(c("web", "app"), each = 4),
month = rep(1:4, 2),
revenue = c(41200, 43880, 45120, 47010, 28100, 31020, 34410, 37800)
)
nested <- sales |> nest(data = c(month, revenue))
print(nested)
print(nested$data[[1]])# A tibble: 2 × 2
channel data
<chr> <list>
1 web <tibble [4 × 2]>
2 app <tibble [4 × 2]>
# A tibble: 4 × 2
month revenue
<int> <dbl>
1 1 41200
2 2 43880
3 3 45120
4 4 47010nested |>
mutate(
growth = map_dbl(data, \(d) (last(d$revenue) - first(d$revenue)) / first(d$revenue))
) |>
select(channel, growth) |>
mutate(growth = scales::percent(growth, accuracy = 0.1)) |>
print()# A tibble: 2 × 2
channel growth
<chr> <chr>
1 web 14.1%
2 app 34.5% One row per group, the group’s data alongside it, and any function applied per group. This is R’s answer to a split-apply-combine that returns something more complicated than a number.
Filling gaps
daily <- tibble(
day = as.Date(c("2026-01-01","2026-01-02","2026-01-05","2026-01-06")),
channel = c("web","web","web","web"),
orders = c(120L, 138L, 141L, 129L)
)
filled <- daily |>
complete(day = seq(min(day), max(day), by = "day"), channel,
fill = list(orders = 0L))
print(filled)# A tibble: 6 × 3
day channel orders
<date> <chr> <int>
1 2026-01-01 web 120
2 2026-01-02 web 138
3 2026-01-03 web 0
4 2026-01-04 web 0
5 2026-01-05 web 141
6 2026-01-06 web 129The 3rd and 4th did not exist in the source. Without complete() a line chart draws straight
through them and a weekly average divides by four instead of six — a missing day and a
zero-order day look identical in the data and mean very different things.
For a value that should carry forward rather than reset, fill():
prices <- tibble(
day = as.Date(c("2026-01-01", "2026-01-02", "2026-01-03", "2026-01-04")),
list_price = c(25.50, NA, NA, 27.00)
)
prices |> fill(list_price, .direction = "down") |> print()# A tibble: 4 × 2
day list_price
<date> <dbl>
1 2026-01-01 25.5
2 2026-01-02 25.5
3 2026-01-03 25.5
4 2026-01-04 27 A price recorded only when it changes is the classic case. .direction takes "down",
"up", "downup" and "updown".
Practice
1. Pivot a monthly report long and total by month.
wide |>
pivot_longer(-channel, names_to = "month", values_to = "revenue") |>
summarise(revenue = sum(revenue), .by = month) |>
print()# A tibble: 3 × 2
month revenue
<chr> <dbl>
1 2026-01 78701.
2 2026-02 83701.
3 2026-03 88651.Impossible in the wide shape without naming every column. Pivoting first is nearly always cheaper than writing the wide version of an operation.
2. Pivot wider with a duplicated key.
Warning: Values from `value` are not uniquely identified; output will contain list-cols.
order_id status channel
<int> <list> <list>
1 1001 <chr [2]> <chr [1]>List-columns where you expected values. values_fn = last or first makes the tie-break
explicit — silently keeping one is the thing to avoid.
3. Flatten a nested JSON payload into order lines.
order_id customer_id customer_country sku qty price line_total
<int> <dbl> <chr> <chr> <dbl> <dbl> <dbl>
1 1001 1 GB BK-1041 1 25.5 25.5
2 1001 1 GB BK-2277 2 12 24 unnest_wider for objects, unnest_longer for arrays. Check the row count afterwards — an
empty array silently removes its parent row without keep_empty = TRUE.
4. Complete a date range with missing days.
3 2026-01-03 web 0
4 2026-01-04 web 0Two days that did not exist in the source now read as zero. A chart drawn from the incomplete version interpolates across the gap and shows a trend that never happened.
Next: strings, dates and factors — the three column types that need their own tools.