Joining ecological tables without losing zeros

dplyr

data wrangling

ecology tutorial

Joining tables in R with dplyr: why an inner join silently drops the sites where a species was absent and biases abundance, and how a left join keeps the zeros.

Author

Tidy Ecology

Published

2026-07-20

Field data almost never lives in one table. You keep a site table with every site you surveyed and its attributes (habitat, elevation, date), and a separate observation table with the counts, one row per species seen. To analyse anything you have to join them, and the default way of joining two tables in R does something that quietly corrupts abundance and occupancy summaries: it throws away the sites where your focal species was never recorded.

This post covers the join verbs in dplyr and then shows the measurable damage. The absent sites are real zeros, and dropping them biases mean abundance upward by two thirds and shrinks a genuine habitat effect fivefold.

The two tables

Here is a survey of ten sites for one focal species. Every site was visited (the survey frame is complete), and each site is either forest or open:

library(dplyr)
library(tidyr)
sites <- data.frame(
  site    = paste0("S", 1:10),
  habitat = c(rep("forest", 5), rep("open", 5))
)
sites

   site habitat
1    S1  forest
2    S2  forest
3    S3  forest
4    S4  forest
5    S5  forest
6    S6    open
7    S7    open
8    S8    open
9    S9    open
10  S10    open

The observation table holds the focal-species counts, but only for the sites where the species was actually recorded. The species was seen at all five forest sites and at one open site; the other four open sites turned up nothing, so they simply have no row here:

obs <- data.frame(
  site  = c("S1", "S2", "S3", "S4", "S5", "S6"),
  count = c(  12,    9,   15,    8,   11,    6)
)
obs

  site count
1   S1    12
2   S2     9
3   S3    15
4   S4     8
5   S5    11
6   S6     6

Those four missing sites are the whole problem. They are not missing data: the species was looked for and not found, so its count there is zero. But a zero count produces no row in a table built from observations, and now the join has to decide what to do with sites that appear in one table and not the other.

Inner join drops the absent sites

An inner join keeps only the sites present in both tables. This is what merge() does by default and what most people reach for first:

inner <- inner_join(sites, obs, by = "site")
nrow(inner)

[1] 6

Ten surveyed sites became 6 rows. The four open sites where the species was absent are gone, because they had no row in obs. Any summary you now compute is a summary over presence-only sites:

mean(inner$count)      # mean over the six sites where the species was seen

[1] 10.16667

This mean, 10.17, is not the mean abundance across your survey. It is the mean given the species was present, which is a different and almost always larger quantity.

Left join, then fill the zeros

A left join keeps every row of the left table (the survey frame) and attaches the observations where they exist. Sites with no observation get NA in the count column, and because we know those NA values are true zeros, we replace them:

full <- left_join(sites, obs, by = "site") |>
  mutate(count = replace_na(count, 0))
full

   site habitat count
1    S1  forest    12
2    S2  forest     9
3    S3  forest    15
4    S4  forest     8
5    S5  forest    11
6    S6    open     6
7    S7    open     0
8    S8    open     0
9    S9    open     0
10  S10    open     0

Now all 10 surveyed sites are present, four of them with the zero they earned:

mean(full$count)       # mean over every surveyed site

[1] 6.1

The mean abundance is 6.10, not 10.17. The inner join inflated it by 66.7 percent, purely by deleting zeros.

The zeros carry the ecology

The bias is not just a shifted average; it changes the pattern you are trying to detect. Group each version by habitat:

by_habitat <- bind_rows(
  inner |> mutate(join = "inner") ,
  full  |> mutate(join = "left+0")
) |>
  group_by(join, habitat) |>
  summarise(mean_count = mean(count), n_sites = n(), .groups = "drop")
by_habitat

# A tibble: 4 × 4
  join   habitat mean_count n_sites
  <chr>  <chr>        <dbl>   <int>
1 inner  forest        11         5
2 inner  open           6         1
3 left+0 forest        11         5
4 left+0 open           1.2       5

Under the inner join, “open” is represented by the single site where the species happened to be seen, so its mean is 6.0 across 1 site. Under the left join, open habitat includes its four zeros, and its mean drops to 1.2 across all five sites. The forest mean is the same either way. So the forest-to-open ratio, the effect size you would report, is 1.83 from the inner join and 9.17 from the correct one. The inner join understated a real habitat preference by a factor of about five, because the evidence for that preference was precisely the open-site zeros it discarded.

Grouped bar chart of mean count for forest and open habitat, faceted by inner join and left join. Forest is near 11 in both panels; open is near 6 under the inner join and near 1.2 under the left join. — Figure 1: Mean focal-species count by habitat under the two joins. The forest bars match, but the inner join puts the open bar far too high because it keeps only the one open site with a positive count and drops the four open zeros.

The honest limit: only fill zeros you can defend

replace_na(count, 0) is correct here for one reason: the site table is a complete survey frame, so a site with no observation was surveyed and the species was genuinely absent. That is a real zero. If instead a site is missing because it was never visited, then its NA is true missing data, and filling it with zero invents a false absence, the mirror image of the inner-join bias. This is the same distinction that separates a blank cell from a zero when reading field data: before you fill an NA with zero, be sure it means “looked for, not found” and not “never looked”. Keep a clear survey frame, and the join does the rest.

References

Wickham H, Francois R, Henry L, et al 2023. dplyr: A Grammar of Data Manipulation. R package. https://dplyr.tidyverse.org.

Zuur AF, Ieno EN, Elphick CS 2010. Methods in Ecology and Evolution 1(1):3 (10.1111/j.2041-210X.2009.00001.x).

--- title: "Joining ecological tables without losing zeros" description: "Joining tables in R with dplyr: why an inner join silently drops the sites where a species was absent and biases abundance, and how a left join keeps the zeros." date: 2026-07-20 09:00 categories: [R, dplyr, data wrangling, ecology tutorial] image: thumbnail.png image-alt: "Two bar charts comparing mean focal-species count by habitat under an inner join and a left join, showing the open-habitat bar much higher when zeros are dropped." --- Field data almost never lives in one table. You keep a **site table** with every site you surveyed and its attributes (habitat, elevation, date), and a separate **observation table** with the counts, one row per species seen. To analyse anything you have to join them, and the default way of joining two tables in R does something that quietly corrupts abundance and occupancy summaries: it throws away the sites where your focal species was never recorded. This post covers the join verbs in `dplyr` and then shows the measurable damage. The absent sites are real zeros, and dropping them biases mean abundance upward by two thirds and shrinks a genuine habitat effect fivefold. ## The two tables Here is a survey of ten sites for one focal species. Every site was visited (the survey frame is complete), and each site is either forest or open: ```{r} #| label: site-table #| message: false library(dplyr) library(tidyr) sites <- data.frame( site = paste0("S", 1:10), habitat = c(rep("forest", 5), rep("open", 5)) ) sites ``` The observation table holds the focal-species counts, but only for the sites where the species was actually recorded. The species was seen at all five forest sites and at one open site; the other four open sites turned up nothing, so they simply have no row here: ```{r} #| label: obs-table obs <- data.frame( site = c("S1", "S2", "S3", "S4", "S5", "S6"), count = c( 12, 9, 15, 8, 11, 6) ) obs ``` Those four missing sites are the whole problem. They are not missing data: the species was looked for and not found, so its count there is zero. But a zero count produces no row in a table built from observations, and now the join has to decide what to do with sites that appear in one table and not the other. ## Inner join drops the absent sites An **inner join** keeps only the sites present in *both* tables. This is what `merge()` does by default and what most people reach for first: ```{r} #| label: inner inner <- inner_join(sites, obs, by = "site") nrow(inner) ``` ```{r} #| label: inner-numbers #| include: false mean_inner <- mean(inner$count) ``` Ten surveyed sites became `r nrow(inner)` rows. The four open sites where the species was absent are gone, because they had no row in `obs`. Any summary you now compute is a summary over presence-only sites: ```{r} #| label: inner-mean mean(inner$count) # mean over the six sites where the species was seen ``` This mean, `r sprintf("%.2f", mean_inner)`, is not the mean abundance across your survey. It is the mean **given the species was present**, which is a different and almost always larger quantity. ## Left join, then fill the zeros A **left join** keeps every row of the left table (the survey frame) and attaches the observations where they exist. Sites with no observation get `NA` in the count column, and because we know those `NA` values are true zeros, we replace them: ```{r} #| label: left full <- left_join(sites, obs, by = "site") |> mutate(count = replace_na(count, 0)) full ``` ```{r} #| label: full-numbers #| include: false mean_full <- mean(full$count) bias <- (mean_inner - mean_full) / mean_full * 100 ``` Now all `r nrow(full)` surveyed sites are present, four of them with the zero they earned: ```{r} #| label: full-mean mean(full$count) # mean over every surveyed site ``` The mean abundance is `r sprintf("%.2f", mean_full)`, not `r sprintf("%.2f", mean_inner)`. The inner join inflated it by `r sprintf("%.1f", bias)` percent, purely by deleting zeros. ## The zeros carry the ecology The bias is not just a shifted average; it changes the pattern you are trying to detect. Group each version by habitat: ```{r} #| label: by-habitat by_habitat <- bind_rows( inner |> mutate(join = "inner") , full |> mutate(join = "left+0") ) |> group_by(join, habitat) |> summarise(mean_count = mean(count), n_sites = n(), .groups = "drop") by_habitat ``` ```{r} #| label: ratio-numbers #| include: false gi <- inner |> group_by(habitat) |> summarise(m = mean(count), .groups="drop") gf <- full |> group_by(habitat) |> summarise(m = mean(count), .groups="drop") ri <- gi$m[gi$habitat=="forest"] / gi$m[gi$habitat=="open"] rf <- gf$m[gf$habitat=="forest"] / gf$m[gf$habitat=="open"] open_inner <- gi$m[gi$habitat=="open"] open_full <- gf$m[gf$habitat=="open"] ``` Under the inner join, "open" is represented by the single site where the species happened to be seen, so its mean is `r sprintf("%.1f", open_inner)` across `r sum(by_habitat$n_sites[by_habitat$join=="inner" & by_habitat$habitat=="open"])` site. Under the left join, open habitat includes its four zeros, and its mean drops to `r sprintf("%.1f", open_full)` across all five sites. The forest mean is the same either way. So the forest-to-open ratio, the effect size you would report, is `r sprintf("%.2f", ri)` from the inner join and `r sprintf("%.2f", rf)` from the correct one. The inner join understated a real habitat preference by a factor of about five, because the evidence for that preference was precisely the open-site zeros it discarded. ```{r} #| label: fig-habitat #| echo: false #| fig-width: 6.6 #| fig-height: 3.8 #| fig-cap: "Mean focal-species count by habitat under the two joins. The forest bars match, but the inner join puts the open bar far too high because it keeps only the one open site with a positive count and drops the four open zeros." #| fig-alt: "Grouped bar chart of mean count for forest and open habitat, faceted by inner join and left join. Forest is near 11 in both panels; open is near 6 under the inner join and near 1.2 under the left join." library(ggplot2) te_green <- "#275139" te_grey <- "#9aa79a" pd <- by_habitat pd$join <- factor(pd$join, levels = c("inner", "left+0"), labels = c("inner join (drops zeros)", "left join + fill 0")) ggplot(pd, aes(x = habitat, y = mean_count, fill = habitat)) + geom_col(width = 0.62) + facet_wrap(~ join) + scale_fill_manual(values = c(forest = te_green, open = te_grey)) + scale_y_continuous(expand = expansion(mult = c(0, 0.06))) + labs(x = NULL, y = "mean focal count") + theme_minimal(base_size = 13) + theme(plot.background = element_rect(fill = "white", colour = NA), panel.background = element_rect(fill = "white", colour = NA), panel.grid.minor = element_blank(), panel.grid.major.x = element_blank(), legend.position = "none", strip.text = element_text(colour = "grey20"), axis.title = element_text(colour = "grey25")) ``` ## The honest limit: only fill zeros you can defend `replace_na(count, 0)` is correct here for one reason: the site table is a complete survey frame, so a site with no observation was surveyed and the species was genuinely absent. That is a real zero. If instead a site is missing because it was never visited, then its `NA` is true missing data, and filling it with zero invents a false absence, the mirror image of the inner-join bias. This is the same distinction that separates a blank cell from a zero when reading field data: before you fill an `NA` with zero, be sure it means "looked for, not found" and not "never looked". Keep a clear survey frame, and the join does the rest. ## Related tutorials - [Reading field data into R](../reading-field-data-into-r/) - [Summarising ecological data by group](../grouped-summaries-in-r/) - [Wide and long: reshaping species data](../wide-and-long-species-data/) - [From a field sheet to your first analysis](../from-field-sheet-to-first-analysis/) ## References Wickham H, Francois R, Henry L, et al 2023. dplyr: A Grammar of Data Manipulation. R package. https://dplyr.tidyverse.org. Zuur AF, Ieno EN, Elphick CS 2010. Methods in Ecology and Evolution 1(1):3 (10.1111/j.2041-210X.2009.00001.x).