Transforming Data with dplyr
The five verbs that cover most pipeline work, grouped summaries with .by, across() for column sets, and the grouped-tibble trap that silently wrongs your next step.
Five verbs cover most of what a transformation does. They all take a data frame first and return one, which is why they chain.
The data
suppressPackageStartupMessages(library(tidyverse))
orders <- tibble(
order_id = 1001:1008,
customer_id = c(1L, 2L, 1L, 3L, 2L, 9L, 3L, 1L),
ordered_at = as.Date(c("2026-01-04","2026-01-05","2026-01-07","2026-01-09",
"2026-01-11","2026-01-12","2026-01-14","2026-01-15")),
status = c("completed","completed","returned","completed",
"pending","completed","refunded","completed"),
amount = c(25.50, 12.00, 40.00, 8.75, 63.20, 19.99, 31.20, 45.00),
channel = c("web","app","web","phone","web","app","web","app")
)
customers <- tibble(
customer_id = 1:4,
full_name = c("Ada Lovelace","Grace Hopper","Alan Turing","Katherine Johnson"),
country = c("GB","US","GB","US")
)filter and select
orders |>
filter(status == "completed", amount > 15) |>
select(order_id, ordered_at, amount) |>
print()# A tibble: 3 × 3
order_id ordered_at amount
<int> <date> <dbl>
1 1001 2026-01-04 25.5
2 1006 2026-01-12 20.0
3 1008 2026-01-15 45 Multiple conditions in filter() are combined with AND, so the comma reads as “and”. Use |
for OR and %in% for a set:
orders |>
filter(status %in% c("returned", "refunded") | amount > 60) |>
print()# A tibble: 3 × 6
order_id customer_id ordered_at status amount channel
<int> <int> <date> <chr> <dbl> <chr>
1 1003 1 2026-01-07 returned 40 web
2 1005 2 2026-01-11 pending 63.2 web
3 1007 3 2026-01-14 refunded 31.2 web select() takes helpers as well as names:
orders |> select(order_id, starts_with("order")) |> names() |> print()
orders |> select(where(is.numeric)) |> names() |> print()
orders |> select(!c(channel, customer_id)) |> names() |> print()
orders |> select(id = order_id, when = ordered_at) |> print(n = 2)[1] "order_id" "ordered_at"
[1] "order_id" "customer_id" "amount"
[1] "order_id" "ordered_at" "status" "amount"
# A tibble: 8 × 2
id when
<int> <date>
1 1001 2026-01-04
2 1002 2026-01-05
# ℹ 6 more rowsRenaming inside select() is new = old, which is the opposite order from some other
languages and worth fixing in your head early.
mutate
orders |>
mutate(
amount_with_vat = round(amount * 1.20, 2),
is_large = amount >= 25,
month = format(ordered_at, "%Y-%m"),
band = case_when(
amount >= 40 ~ "high",
amount >= 15 ~ "medium",
.default = "low"
)
) |>
select(order_id, amount, amount_with_vat, is_large, month, band) |>
print(n = 4)# A tibble: 8 × 6
order_id amount amount_with_vat is_large month band
<int> <dbl> <dbl> <lgl> <chr> <chr>
1 1001 25.5 30.6 TRUE 2026-01 medium
2 1002 12 14.4 FALSE 2026-01 low
3 1003 40 48 TRUE 2026-01 high
4 1004 8.75 10.5 FALSE 2026-01 low
# ℹ 4 more rowsColumns created in one mutate() are usable by later expressions in the same call, which is
why the chain reads top to bottom.
case_when() evaluates conditions in order and takes the first match — so ordering matters,
and .default catches the rest. Without it, unmatched rows become NA:
orders |>
mutate(band = case_when(amount >= 40 ~ "high", amount >= 25 ~ "medium")) |>
count(band) |>
print()# A tibble: 3 × 2
band n
<chr> <int>
1 high 2
2 medium 2
3 NA 4Four silent NAs. Always supply .default, even if only to make the gap explicit.
Grouped summaries
orders |>
filter(status == "completed") |>
summarise(
orders = n(),
revenue = sum(amount),
avg = round(mean(amount), 2),
largest = max(amount),
.by = channel
) |>
arrange(desc(revenue)) |>
print()# A tibble: 3 × 5
channel orders revenue avg largest
<chr> <int> <dbl> <dbl> <dbl>
1 app 3 76.99 25.66 45
2 web 1 25.5 25.5 25.5
3 phone 1 8.75 8.75 8.75.by is the modern form: it groups for this call only and returns an ungrouped result.
That matters, because the older group_by() sticks:
result <- orders |>
group_by(channel, status) |>
summarise(revenue = sum(amount), .groups = "drop_last")
print(group_vars(result))
result |> mutate(share = revenue / sum(revenue)) |> print(n = 4)[1] "channel"
# A tibble: 6 × 4
# Groups: channel [3]
channel status revenue share
<chr> <chr> <dbl> <dbl>
1 app completed 77.0 1
2 phone completed 8.75 1
3 web completed 25.5 0.211
4 web pending 63.2 0.522
# ℹ 2 more rowsThe shares are wrong — sum(revenue) computed within each remaining channel group, so the
app row shows 1 instead of its share of the total. The # Groups: line in the print output is
the warning sign, and it is easy to scroll past.
Two ways to avoid it entirely:
orders |>
summarise(revenue = sum(amount), .by = c(channel, status)) |>
mutate(share = round(revenue / sum(revenue), 3)) |>
print(n = 3)# A tibble: 6 × 4
channel status revenue share
<chr> <chr> <dbl> <dbl>
1 web completed 25.5 0.113
2 app completed 77.0 0.34
3 web returned 40 0.177
# ℹ 3 more rowsCorrect, and no # Groups: line. Use .by unless you specifically want grouping to persist,
and call ungroup() explicitly when you do use group_by().
across
orders |>
summarise(
across(c(amount), list(total = sum, avg = mean, max = max)),
n = n(),
.by = status
) |>
mutate(across(where(is.numeric), \(x) round(x, 2))) |>
print()# A tibble: 4 × 5
status amount_total amount_avg amount_max n
<chr> <dbl> <dbl> <dbl> <dbl>
1 completed 111. 27.8 45 4
2 returned 40 40 40 1
3 pending 63.2 63.2 63.2 1
4 refunded 31.2 31.2 31.2 1across() applies functions to a set of columns chosen with the same helpers as select().
On a table with thirty numeric columns it replaces thirty near-identical lines.
\(x) is R’s lambda shorthand, equivalent to function(x).
Joins
orders |>
left_join(customers, by = "customer_id") |>
select(order_id, full_name, country, amount) |>
print(n = 8)# A tibble: 8 × 4
order_id full_name country amount
<int> <chr> <chr> <dbl>
1 1001 Ada Lovelace GB 25.5
2 1002 Grace Hopper US 12
3 1003 Ada Lovelace GB 40
4 1004 Alan Turing GB 8.75
5 1005 Grace Hopper US 63.2
6 1006 NA NA 19.99
7 1007 Alan Turing GB 31.2
8 1008 Ada Lovelace GB 45 Order 1006 belongs to customer 9, who does not exist. A left_join keeps it with NA
columns; an inner_join would drop it silently, taking £19.99 of revenue with it.
Check rather than assume:
orphans <- orders |> anti_join(customers, by = "customer_id")
cat("orphaned orders:", nrow(orphans), "\n")
print(orphans |> select(order_id, customer_id))orphaned orders: 1
# A tibble: 1 × 2
order_id customer_id
<int> <int>
1 1006 9anti_join() returns rows in the left table with no match — the cheapest referential
integrity check there is, and worth running after every join in a pipeline.
Watch row counts, too. A join key that is not unique multiplies rows:
dupes <- bind_rows(customers, customers |> filter(customer_id == 1))
cat("before:", nrow(orders), " after:", nrow(orders |> left_join(dupes, by = "customer_id")), "\n")before: 8 after: 11 Warning message:
In left_join(orders, dupes, by = "customer_id") :
Detected an unexpected many-to-many relationship between `x` and `y`.dplyr warns on many-to-many joins, which is more than most tools do. Make it an error in a pipeline:
orders |> left_join(dupes, by = "customer_id", relationship = "many-to-one")Error in `left_join()`:
! Each row in `x` must match at most 1 row in `y`.
✖ Row 1 of `x` matches multiple rows in `y`.Declaring the expected relationship turns a silent row explosion into a failed run.
Counting and slicing
orders |> count(status, sort = TRUE) |> print()
orders |>
slice_max(amount, n = 1, by = channel) |>
select(channel, order_id, amount) |>
print()
orders |> distinct(customer_id) |> nrow() |> print()# A tibble: 4 × 2
status n
<chr> <int>
1 completed 4
2 pending 1
3 refunded 1
4 returned 1
# A tibble: 3 × 3
channel order_id amount
<chr> <int> <dbl>
1 web 1005 63.2
2 app 1008 45
3 phone 1004 8.75
[1] 4slice_max(..., by = ) is “top N per group” in one line — the window-function pattern without
writing a window function.
Practice
1. Compute revenue per country for completed orders.
orders |>
filter(status == "completed") |>
left_join(customers, by = "customer_id") |>
summarise(revenue = sum(amount), orders = n(), .by = country) |>
arrange(desc(revenue)) |>
print()# A tibble: 3 × 3
country revenue orders
<chr> <dbl> <int>
1 GB 79.2 3
2 US 12 1
3 NA 19.99 1The NA row is the orphaned order made visible. An inner_join would have hidden it, and the
GB and US figures would look complete while £19.99 went missing.
2. Use case_when without a default.
# A tibble: 3 × 2
band n
<chr> <int>
1 high 2
2 medium 2
3 NA 4Half the rows fell through to NA. .default is not optional in pipeline code — an
unhandled category should either get a value or fail, not become missing.
3. Summarise on a grouped tibble and compute a share.
# Groups: channel [3]
channel status revenue share
<chr> <chr> <dbl> <dbl>
1 app completed 77.0 1A share of 1 for a row that is a third of the total. The # Groups: header is the only visible
clue — switching to .by removes the failure mode.
4. Join on a duplicated key with relationship declared.
Error in `left_join()`:
! Each row in `x` must match at most 1 row in `y`.
✖ Row 1 of `x` matches multiple rows in `y`.An error rather than three extra rows and inflated totals. Declaring the relationship on every join in a pipeline costs one argument and catches a whole class of bug.
Next: reading and writing data — CSV, Parquet, and files too large for memory.