Complete dataframe with missing combinations of values
Combining tidyr::pivot_wider()
and tidyr::pivot_longer()
also makes implicit missing values explicit.
# Load packages
library(tidyverse)
# Your data
df <- tibble(distance = c(rep("NPR",3), rep(100, 3)),
years = c(3,4,7,1,5,6),
area = seq(10, 60, by = 10))
# Solution
df %>%
pivot_wider(names_from = years,
values_from = area) %>% # pivot_wider() makes your implicit missing values explicit
pivot_longer(2:7, names_to = "years",
values_to = "area") %>% # Turn to your desired format (long)
mutate(area = replace_na(area, 0)) # Replace missing values (NA) with 0s
You can use the tidyr::complete
function:
complete(df, distance, years = full_seq(years, period = 1), fill = list(area = 0))
# A tibble: 14 x 3
distance years area
<fct> <dbl> <dbl>
1 100 1. 40.
2 100 2. 0.
3 100 3. 0.
4 100 4. 0.
5 100 5. 50.
6 100 6. 60.
7 100 7. 0.
8 NPR 1. 0.
9 NPR 2. 0.
10 NPR 3. 10.
11 NPR 4. 20.
12 NPR 5. 0.
13 NPR 6. 0.
14 NPR 7. 30.
or slightly shorter:
complete(df, distance, years = 1:7, fill = list(area = 0))