Code
library(dplyr)
library(tidyr)
library(ggplot2)Finding hidden missing data with tidyr::complete()
The question
What proportion of tagged fish was detected at each monitoring station? This example uses tidyr::complete() to include combinations that have no recorded row, so the calculation includes all tagged fish in its denominator.
Packages and functions
The main function is tidyr::complete(). I also use dplyr::n_distinct(), summarise(), mutate(), and filter() to count fish and compare detections, and ggplot2 to plot the station summaries. The dataset is included with tidyr, so no separate data file or API key is needed. This example uses dplyr 1.1.0 or later for .by.
The data
The built-in tidyr::fish_encounters dataset records detections of tagged fish moving downstream. Each recorded row identifies a fish and a station, with seen = 1. There are no explicit rows for nondetections.
Code
fish_encounters |> head(10)# A tibble: 10 × 3
fish station seen
<fct> <fct> <int>
1 4842 Release 1
2 4842 I80_1 1
3 4842 Lisbon 1
4 4842 Rstr 1
5 4842 Base_TD 1
6 4842 BCE 1
7 4842 BCW 1
8 4842 BCE2 1
9 4842 BCW2 1
10 4842 MAE 1Code
# Count the tagged fish and include every station factor level.
n_fish <- n_distinct(fish_encounters$fish)
n_stations <- nlevels(fish_encounters$station)
n_possible <- n_fish * n_stations
tibble(
fish = n_fish,
stations = n_stations,
possible_combinations = n_possible,
recorded_rows = nrow(fish_encounters),
missing_combinations = n_possible - nrow(fish_encounters)
)# A tibble: 1 × 5
fish stations possible_combinations recorded_rows missing_combinations
<int> <int> <int> <int> <int>
1 19 11 209 114 95There are 19 fish and 11 stations, giving 209 possible combinations. Only 114 are recorded. The other 95 combinations are implicit missing values: the rows do not exist.
Why the original average is misleading
Code
# This average includes only recorded detections, so every value is one.
recorded_summary <- fish_encounters |>
summarise(share_detected = mean(seen), .by = station)
recorded_summary# A tibble: 11 × 2
station share_detected
<fct> <dbl>
1 Release 1
2 I80_1 1
3 Lisbon 1
4 Rstr 1
5 Base_TD 1
6 BCE 1
7 BCW 1
8 BCE2 1
9 BCW2 1
10 MAE 1
11 MAW 1Every average equals 1 because every recorded row is a detection. This answers a question about recorded encounters, rather than the proportion of all tagged fish detected at a station.
Make the missing combinations explicit
Code
# Add every fish-station combination; fill new rows with zero.
# Preserve existing NA values with explicit = FALSE.
fish_full <- fish_encounters |>
complete(fish, station, fill = list(seen = 0), explicit = FALSE)
fish_full |> head(15)# A tibble: 15 × 3
fish station seen
<fct> <fct> <int>
1 4842 Release 1
2 4842 I80_1 1
3 4842 Lisbon 1
4 4842 Rstr 1
5 4842 Base_TD 1
6 4842 BCE 1
7 4842 BCW 1
8 4842 BCE2 1
9 4842 BCW2 1
10 4842 MAE 1
11 4842 MAW 1
12 4843 Release 1
13 4843 I80_1 1
14 4843 Lisbon 1
15 4843 Rstr 1complete(fish, station) creates every combination of fish and station. fill = list(seen = 0) marks the new combinations as nondetections. explicit = FALSE limits filling to newly created rows, preserving any existing NA values.
Here, zero means no detection was recorded. It does not prove that a fish was absent, died, or never passed the station. Interpreting a missing row as a nondetection is appropriate for this encounter dataset; in other datasets, a missing row could mean a measurement was never taken.
Code
# Check that each pair is unique and all original detections remain.
stopifnot(
anyDuplicated(fish_full[c("fish", "station")]) == 0L,
nrow(fish_full) == n_possible,
sum(fish_full$seen == 1) == nrow(fish_encounters),
sum(fish_full$seen == 0) == n_possible - nrow(fish_encounters)
)
tibble(
original_detections = sum(fish_full$seen == 1),
added_nondetections = sum(fish_full$seen == 0),
total_combinations = nrow(fish_full)
)# A tibble: 1 × 3
original_detections added_nondetections total_combinations
<int> <int> <int>
1 114 95 209Calculate the proportion using all tagged fish
Code
# With the complete grid, each station has the full cohort as its denominator.
station_summary <- fish_full |>
summarise(
fish_detected = sum(seen),
fish_in_cohort = n(),
share_detected = mean(seen),
.by = station
) |>
arrange(station)
station_summary |> knitr::kable(digits = 3)| station | fish_detected | fish_in_cohort | share_detected |
|---|---|---|---|
| Release | 19 | 19 | 1.000 |
| I80_1 | 19 | 19 | 1.000 |
| Lisbon | 13 | 19 | 0.684 |
| Rstr | 12 | 19 | 0.632 |
| Base_TD | 11 | 19 | 0.579 |
| BCE | 8 | 19 | 0.421 |
| BCW | 8 | 19 | 0.421 |
| BCE2 | 7 | 19 | 0.368 |
| BCW2 | 7 | 19 | 0.368 |
| MAE | 5 | 19 | 0.263 |
| MAW | 5 | 19 | 0.263 |
Each station now has the same denominator: all 19 tagged fish. The resulting proportions describe detections within this cohort, rather than the reliability of the monitoring equipment or fish survival.
Code
# Plot detections in the upstream-to-downstream order stored in the factor.
ggplot(station_summary, aes(station, share_detected, group = 1)) +
geom_line(color = "#1F4E79") +
geom_point(color = "#1F4E79", size = 2.5) +
scale_y_continuous(labels = scales::percent, limits = c(0, 1)) +
labs(
title = "Share of tagged fish detected at each station",
subtitle = "Stations ordered from upstream to downstream",
x = "Monitoring station", y = "Proportion of tagged fish detected"
) +
theme_minimal(base_size = 12) +
theme(axis.text.x = element_text(angle = 35, hjust = 1))
station is a factor whose levels follow river order. complete() uses all factor levels, and the plot retains this order.
A nondetection followed by a later detection
The complete grid also lets us identify fish missed at a station but detected farther downstream.
Code
# Find nondetections upstream of the last recorded detection for each fish.
missed_then_seen <- fish_full |>
mutate(position = as.integer(station)) |>
mutate(furthest_detection = max(position[seen == 1]), .by = fish) |>
filter(seen == 0, position < furthest_detection) |>
select(fish, station)
missed_then_seen# A tibble: 1 × 2
fish station
<fct> <fct>
1 4850 Lisbon This returns 1 fish-station combination(s). A downstream detection supports the interpretation that the earlier nondetection does not establish that the fish stopped migrating. It does not identify the reason for the missed detection.
Why explicit = FALSE matters
Suppose an existing row has an unknown measurement. We may want to keep that unknown value while filling only combinations that were absent entirely.
Code
# Include an existing unknown measurement to distinguish it from an absent row.
example <- tibble(
fish = c("A", "A", "B"),
station = c("upstream", "downstream", "upstream"),
seen = c(1, NA, 1)
)
example |>
complete(fish, station, fill = list(seen = 0), explicit = FALSE)# A tibble: 4 × 3
fish station seen
<chr> <chr> <dbl>
1 A downstream NA
2 A upstream 1
3 B downstream 0
4 B upstream 1The existing unknown value for fish A stays NA. The newly added downstream row for fish B becomes zero. Without explicit = FALSE, the default behavior would replace the existing NA with zero as well.
Takeaways
- Check whether missing information is a blank cell or an absent row.
- Use
complete()to build the combinations relevant to your question. - Fill with zero only when zero has a defensible meaning.
- Use
explicit = FALSEwhen existing unknown measurements should remain unknown.
References
- tidyr. Complete a data frame with missing combinations.
- tidyr. Fish encounters dataset, supplied by Myfanwy Johnston.
AI Use
I used Claude to understand the topic, code, and explanations. Numerical statements in the vignette are generated from the R code.