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))

Tags:

R

Tidyr