Create a dataframe with all combinations of two columns

user_9421846 0
Viewed 89

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

tidyr dplyr dataframe r
3 Answers

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
user_37322710
user_197077910

One possible way to solve your problem using data.table:

library(data.table)

cases = unique(c(df1$V1, df1$V2))

CJ(V1=cases, V2=cases, V3=0)[setDT(df1), V3 := i.v3, on=.(V1, V2)][order(V3==0)]

        V1     V2    V3
 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
user_108488980
Related