r - 以有效的方式生成包含按组所有组合计数的表

Question

我有以下数据集示例:

df <- tibble(group = c(rep(1, 6), rep(2, 6)),
             class1 = c("A", "A", "B", "B", "B", "C", "B", "B", "B", "C", "C", "C"),
             class2 = c("A", "B", "B", "B", "C", "B", "B", "B", "A", "C", "A", "B"))
df

我想按group快速制作一个包含class1和class2之间所有组合的表格。

我尝试了下面的代码，但对于我的数据来说，它慢得令人痛苦(数据量巨大，> 1000 万行)。需要30多分钟。

output <- df %>% table() %>% as.data.table()

所需输出:

output <- tibble(group = c(rep(1, 9), rep(1, 9)),
                 class1 = c(rep("A", 3), rep("B", 3), rep("C", 3),
                            rep("A", 3), rep("B", 3), rep("C", 3)),
                 class2 = rep(c("A", "B", "C"), 6),
                 N = c(1, 1, 0, 0, 2, 1, 0, 1, 0, 0, 0, 0, 1, 2, 0, 1, 1, 1))
output

感谢您的帮助

Answer 1

这有效吗:

library(dplyr)
library(tidyr)

df %>% mutate(N = 1) %>% complete( group, class1, class2) %>% 
                distinct() %>% mutate(N = replace_na(N, 0))
# A tibble: 18 × 4
   group class1 class2     N
   <dbl> <chr>  <chr>  <dbl>
 1     1 A      A          1
 2     1 A      B          1
 3     1 A      C          0
 4     1 B      A          0
 5     1 B      B          1
 6     1 B      C          1
 7     1 C      A          0
 8     1 C      B          1
 9     1 C      C          0
10     2 A      A          0
11     2 A      B          0
12     2 A      C          0
13     2 B      A          1
14     2 B      B          1
15     2 B      C          0
16     2 C      A          1
17     2 C      B          1
18     2 C      C          1