Create a dataframe with all combinations of two columns

August 6, 2022

I have the following data example:

df1 <- tibble(V1 = c("a", "b", "c"), 
              V2 = c("b", "b", "f"), 
              v3 = c(1:3))

I would like to generate this output from the df1:

# A tibble: 16 × 3
   V1    V2       v3
   <chr> <chr> <dbl>
 1 a     b         1
 2 b     b         2
 3 c     f         3
 4 a     a         0
 5 a     c         0
 6 a     f         0
 7 b     a         0
 8 b     c         0
 9 b     f         0
10 c     a         0
11 c     b         0
12 c     c         0
13 f     a         0
14 f     b         0
15 f     c         0
16 f     f         0

I try it, but not works:

df1 %>% complete(V1, V2,
                 fill = list(V3 = 0))

Thanks all

>Solution :

We may need to add the levels – convert the columns ‘V1’, ‘V2’ to factor with levels specified as the unique levels from both the columns, and then use complete

library(dplyr)
library(tidyr)
lvls <- sort(unique(unlist(df1[1:2])))
df1 %>% 
  complete(V1 = factor(V1, levels = lvls),
   V2 = factor(V2, levels = lvls), 
   fill = list(v3 = 0)) %>% 
   arrange(v3 == 0)

-output

# A tibble: 16 × 3
   V1    V2       v3
   <chr> <chr> <int>
 1 a     b         1
 2 b     b         2
 3 c     f         3
 4 a     a         0
 5 a     c         0
 6 a     f         0
 7 b     a         0
 8 b     c         0
 9 b     f         0
10 c     a         0
11 c     b         0
12 c     c         0
13 f     a         0
14 f     b         0
15 f     c         0
16 f     f         0