Data manipulation with dplyr, visualized
There are two ways to reshape data in R, and you will meet both this week. The apply family works on vectors, lists and matrices; dplyr works on data frames, and reads much more like a sentence. This page is the companion to that one, with the same idea, the same worked data, and different tools. Where the two do the same job, it says so.
The one idea
dplyr is built on a single, strict promise: a data frame goes in, a data frame comes out. That is why the verbs chain. Each one takes the table the last one produced, changes exactly one thing about it, and passes it on.
The pipe is what makes the chain readable. x |> f() means f(x): it takes the thing on the left and drops it in as the first argument on the right. So this
summarise(group_by(filter(surveys, count > 0), site), mean = mean(count))
which you have to read inside-out, becomes this, which you read top to bottom in the order it happens:
surveys |> filter(count > 0) |> group_by(site) |> summarise(mean = mean(count))
|> is built into R (4.1 and later) and needs no packages. %>% comes from magrittr and arrives with dplyr; you will see it in older code and in this course's Day 5 scripts. For everything in this page they are interchangeable. Prefer |> in new work, since it always exists.count, not surveys$count and not "count". That is the convenience of dplyr and also its strangeness. Those names are not objects in your workspace, and they only mean anything inside the verb. It is why a stray surveys$ inside filter() so often gives a baffling result rather than an error.The verbs, one by one
Everything below uses one table. It is the same survey data as the apply page, in the shape dplyr wants: one row per observation rather than a list of vectors.
library(dplyr); library(tidyr) surveys <- tibble( site = c("A","A","A","B","B","C","C","C","C","D"), habitat = c("forest","forest","forest","meadow","meadow", "forest","forest","forest","forest","edge"), visit = c(1,2,3,1,2,1,2,3,4,1), count = c(3,5,4,0,1,7,6,9,8,2), hours = c(1.5,2,1,2,1.5,1,1,1.5,2,1) ) #> # A tibble: 10 x 5 #> site habitat visit count hours #> <chr> <chr> <dbl> <dbl> <dbl> #> 1 A forest 1 3 1.5 #> 2 A forest 2 5 2 #> 3 A forest 3 4 1 #> 4 B meadow 1 0 2 #> ... 6 more rows
filter()to keep rows,select()to keep columns, and the two are constantly confusedmutate()to add or change a column, keeping every rowsummarise()to collapse many rows into onegroup_by()beforesummarise()to collapse into one row per group, the workhorsearrange()to sort,count()to tallyacross()to do the same thing to several columns at oncepivot_longer()/pivot_wider()when the data is the wrong shape entirely
filter() keep some rows, all columns
Reach for it when you want a subset of observations. The condition is evaluated for every row, and rows where it is TRUE survive.
filter(surveys, count > 4) #> # A tibble: 5 x 5 #> site habitat visit count hours #> 1 A forest 2 5 2 #> 2 C forest 1 7 1 #> 3 C forest 2 6 1 #> 4 C forest 3 9 1.5 #> 5 C forest 4 8 2
Several conditions separated by commas are joined with AND: filter(surveys, count > 4, habitat == "forest"). Use | for OR. Note ==, not =.
Watch for NA. filter() keeps only rows where the condition is definitely TRUE, so rows where it is NA are dropped, which is usually what you want, but silently loses data if you did not expect missing values.
select() keep some columns, all rows
Reach for it when a table has more columns than you want to look at. This is the one people mix up with filter(): filter takes rows, select takes columns.
select(surveys, site, count) #> # A tibble: 10 x 2 #> site count #> 1 A 3 #> 2 A 5 #> ... 8 more rows select(surveys, -hours) # everything EXCEPT hours select(surveys, site:count) # a range of adjacent columns select(surveys, starts_with("h")) # habitat and hours
mutate() add or change a column, keep every row
Reach for it when you need a derived variable. The row count never changes. That is the difference between mutate() and summarise(), and it is the whole distinction.
mutate(surveys, rate = count / hours) #> # A tibble: 10 x 6 #> site habitat visit count hours rate #> 1 A forest 1 3 1.5 2 #> 2 A forest 2 5 2 2.5 #> 3 A forest 3 4 1 4 #> ... 7 more rows
The calculation is vectorised: count / hours divides all ten pairs at once. This is why you almost never need mapply() in a data frame, because the parallel walk over two columns is what mutate() already does.
Use case_when() inside mutate() for conditional recoding, and if_else() for a simple two-way split.
arrange() reorder the rows
Reach for it when you want to see the largest or smallest first. It changes only the order, and no rows or columns are gained or lost.
arrange(surveys, desc(count)) #> # A tibble: 10 x 5 #> site habitat visit count hours #> 1 C forest 3 9 1.5 #> 2 C forest 4 8 2 #> 3 C forest 1 7 1 #> ... 7 more rows
Default is ascending; wrap a column in desc() for descending. Extra columns break ties: arrange(surveys, site, desc(count)).
summarise() collapse many rows into one
Reach for it when you want a number that describes the whole table. On its own it returns a single row, which is rarely what you want. Its real use is with group_by(), below.
summarise(surveys, n = n(), mean = mean(count)) #> # A tibble: 1 x 2 #> n mean #> 10 4.5
n() is special. It counts the rows in the current group and can only be used inside these verbs. summarise and summarize are the same function.
group_by() + summarise() one row per group
Reach for it when you want per-site, per-species, per-year numbers. This pairing is the single most useful thing in dplyr, and it is the direct equivalent of base R's tapply().
surveys |> group_by(site) |> summarise(visits = n(), total = sum(count), mean = mean(count)) #> # A tibble: 4 x 4 #> site visits total mean #> 1 A 3 12 4 #> 2 B 2 1 0.5 #> 3 C 4 30 7.5 #> 4 D 1 2 2
group_by() changes nothing you can see. It only attaches a note saying "treat these rows as belonging together." The next verb is what acts on it. In the app below, watch the table stay exactly the same at the group_by step and then collapse at the summarise step; that is the point.
Those four means (4, 0.5, 7.5, 2) are the same four numbers sapply(sites, mean) produces on the apply page, from the same data in a different shape.
count() how many rows per group
Reach for it when you only want tallies. It is shorthand for group_by(x) |> summarise(n = n()), and it is the fastest way to check whether your data is what you think it is.
count(surveys, habitat) #> # A tibble: 3 x 2 #> habitat n #> 1 edge 1 #> 2 forest 7 #> 3 meadow 2
Run this on a new dataset before anything else. Unexpected group sizes are how you find duplicated rows, stray spellings and missing levels.
across() the same operation on several columns
Reach for it when you would otherwise repeat yourself once per column. It lives inside summarise() or mutate() and is dplyr's answer to sapply() over columns.
surveys |> summarise(across(c(count, hours), mean)) #> # A tibble: 1 x 2 #> count hours #> 4.5 1.45 surveys |> group_by(habitat) |> summarise(across(c(count, hours), mean)) #> # A tibble: 3 x 3 #> habitat count hours #> 1 edge 2 1 #> 2 forest 6 1.429 #> 3 meadow 0.5 1.75
Column selection works exactly like select(): across(where(is.numeric), mean) or across(everything(), mean). Day 1's answer key mentions this form.
pivot_longer() and pivot_wider() change the shape, not the content
These come from tidyr, not dplyr, and they are the ones that feel like magic until they don't. Almost every dplyr verb expects long data: one row per observation, one column per variable. Data collected by hand is usually wide: one row per site, one column per visit.
surveys |> select(site, visit, count) |> pivot_wider(names_from = visit, values_from = count, names_prefix = "v") #> # A tibble: 4 x 5 #> site v1 v2 v3 v4 #> 1 A 3 5 4 NA #> 2 B 0 1 NA NA #> 3 C 7 6 9 8 #> 4 D 2 NA NA NA
Notice the NAs. Sites were visited a different number of times, so the wide rectangle has to invent cells that were never observed. That is the honest cost of the wide shape, and the reason the long form is the default for analysis.
pivot_longer() goes back the other way, and is what Day 1 uses to stack four simulated models into one column before grouping:
sims |> pivot_longer(everything(), names_to = "model", values_to = "y") |> group_by(model) |> summarise(mean = mean(y), var = var(y))
left_join() bring columns from another table
Reach for it when the information you need lives in a second table, species traits, site coordinates, observer names, keyed by a shared column.
effort <- tibble(site = c("A","B","C","D"), observer = c("Kim","Lee","Kim","Ana")) left_join(surveys, effort, by = "site") #> # A tibble: 10 x 6 #> site habitat visit count hours observer #> 1 A forest 1 3 1.5 Kim #> 2 A forest 2 5 2 Kim #> ... 8 more rows
left_join keeps every row of the left table and fills NA where the right table has no match, the safe default, because your data never silently shrinks. Check nrow() before and after anyway. If the right table has duplicate keys, rows multiply, which is the most common way a join goes wrong.
Watch a pipeline run
Pick a task, then step through it one verb at a time. The highlighted line is the verb being applied; the table shows what it did. Watch the row and column counts under the table. That is what each verb is really changing.
The same job in base R
Neither idiom is more correct. dplyr reads better and is easier to chain; base R has no dependencies and works on objects that are not data frames, matrices, lists, MCMC output, which is why this course uses both. What matters is recognising the same job in either dialect.
| The job | dplyr | base R |
|---|---|---|
| Keep some rows | filter(d, count > 4) | d[d$count > 4, ] |
| Keep some columns | select(d, site, count) | d[, c("site","count")] |
| Add a column | mutate(d, rate = count/hours) | d$rate <- d$count / d$hours |
| Sort | arrange(d, desc(count)) | d[order(-d$count), ] |
| One summary per group | d |> group_by(g) |> summarise(m = mean(x)) | tapply(d$x, d$g, mean) |
| Group sizes | count(d, g) | table(d$g) |
| Same summary, several columns | summarise(across(c(x, y), mean)) | sapply(d[c("x","y")], mean) |
| Split into groups, keep the pieces | group_split(d, g) | split(d, d$g) |
| Do something complicated per group | d |> group_by(g) |> group_modify(...) | do.call(rbind, lapply(split(d, d$g), f)) |
| Element-wise over two columns | mutate(z = f(x, y)), vectorised | mapply(f, d$x, d$y) |
| Bring in another table | left_join(d, e, by = "site") | merge(d, e, by = "site", all.x = TRUE) |
| Long ↔ wide | pivot_longer() / pivot_wider() | reshape(), or as.data.frame(as.table(m)) |
Worth seeing side by side, because it is the row that comes up most:
dplyr
surveys |> group_by(site) |> summarise(mean = mean(count)) #> site mean #> 1 A 4 #> 2 B 0.5 #> 3 C 7.5 #> 4 D 2
base R
tapply(surveys$count, surveys$site, mean) #> A B C D #> 4.0 0.5 7.5 2.0
Same four numbers. The difference in the result is the thing to notice. dplyr gives you back a data frame with the grouping variable as a column, so the next verb can keep working on it. tapply gives you a named vector, which is compact and immediately printable but awkward to build on. You would need as.data.frame() and some naming before you could join or plot it.
apply() and sapply() show up there and dplyr does not.Four traps
1. summarise() peels off one grouping level, quietly
After grouping by two variables, summarise() returns a result still grouped by the first. The next verb then behaves in a way that looks inexplicable.
d |> group_by(site, visit) |> summarise(n = n()) # still grouped by site! #> `summarise()` has grouped output by 'site'. You can override using `.groups`.
That message is a warning worth reading, not noise. End the chain with ungroup(), or pass .groups = "drop", whenever the result is going anywhere else.
2. filter() silently drops NA rows
NA > 4 is NA, not TRUE, and filter() keeps only definite TRUE. So rows with a missing value in the tested column vanish without comment. If that matters, be explicit: filter(d, count > 4 | is.na(count)).
3. select() and filter() get swapped
They sound interchangeable in English and are not. filter takes rows, select takes columns. If a call errors with "object not found", check you have not asked filter() for a column name. The mnemonic that sticks is that you filter a liquid to remove some of what is in it (rows), and you select from a menu of names (columns).
4. Masking: dplyr and base R both have filter()
Loading dplyr prints a message that filter() and lag() now mask the stats versions. Usually harmless, but if a time-series function starts misbehaving, that is why. Be explicit when it matters: dplyr::filter() or stats::filter().
## The following objects are masked from 'package:stats':
## filter, lag
Where you'll meet these
The course leans on base R more than the tidyverse, because most of what Days 3–5 manipulate are matrices and MCMC output rather than data frames. Where dplyr does appear, it is these lines:
| Day | Line | What it does |
|---|---|---|
| Day 1 | sims |> pivot_longer(everything(), names_to="model", values_to="y") | stacks four simulated model columns into one long column, so they can be grouped |
| Day 1 | group_by(model) |> summarise(mean = mean(y), var = var(y)) | one row of summary statistics per simulated model |
| Day 1 | tibble(...) | builds the simulation results table in the first place |
| Day 5 | N2OEmission %>% group_by(group.index) %>% summarise(mean = mean(carbon)) | site-level carbon means, used as a predictor in the multilevel model |
| Day 5 | ... %>% arrange(group.index) | sorts so the summary lines up with the model's group indices, an easy thing to get wrong |
That last row is worth pausing on. When a summary is going to be fed into JAGS or NIMBLE as a vector, its order has to match the index the model expects. arrange() is doing real work there, not cosmetics.
Check yourself
Shape questions again: how many rows and columns come back.
1. surveys has 10 rows. How many rows does mutate(surveys, rate = count / hours) return?
2. Which pair does the same job?
3. group_by(surveys, habitat) on its own. What comes back?
4. You need the mean of every posterior draw column in a 4000 × 12 matrix of MCMC output. Which is the right tool?
5. After pivot_wider() on these data, some cells are NA. Why?
FW 536 · companion to the apply family page. Back to the course home page.