dplyr::count() 多列计数

Question

我有如下数据集：

dat = structure(list(C86_1981 = c("Outer London", "Buckinghamshire", 
NA, "Ross and Cromarty", "Cornwall and Isles of Scilly", NA, 
"Kirkcaldy", "Devon", "Kent", "Renfrew"), C96_1981 = c("Outer London", 
"Buckinghamshire", NA, "Ross and Cromarty", "Not known/missing", 
NA, "Kirkcaldy", NA, NA, NA), C00_1981 = c("Outer London", "Inner London", 
"Lancashire", "Ross and Cromarty", NA, "Humberside", "Kirkcaldy", 
NA, NA, NA), C04_1981 = c("Kent", NA, NA, "Ross and Cromarty", 
NA, "Humberside", "Not known/missing", NA, NA, "Renfrew"), C08_1981 = c("Kent", 
"Oxfordshire", NA, "Ross and Cromarty", "Cornwall and Isles of Scilly", 
"Humberside", "Dunfermline", NA, NA, "Renfrew"), C12_1981 = c("Kent", 
NA, NA, "Ross and Cromarty", "Cornwall and Isles of Scilly", 
"Humberside", "Dunfermline", NA, NA, "Renfrew")), row.names = c(NA, 
-10L), class = c("tbl_df", "tbl", "data.frame"), .Names = c("C86_1981", 
"C96_1981", "C00_1981", "C04_1981", "C08_1981", "C12_1981"))

我想对每一列使用dplyr::count()。期望输出：

# A tibble: 10 x 3
                       C86_1981 dat86_n dat96_n ...
                          <chr>   <int>   <int>
 1              Buckinghamshire       1       1
 2 Cornwall and Isles of Scilly       1      NA
 3                        Devon       1      NA
 4                         Kent       1      NA
 5                    Kirkcaldy       1       1
 6                 Outer London       1       1
 7                      Renfrew       1      NA
 8            Ross and Cromarty       1       1
 9                         <NA>       2       5
10            Not known/missing      NA       1

目前我正在手动完成这个任务，然后使用dplyr::full_join()来合并结果：

library("tidyverse")

dat86_n = dat %>%
  count(C86_1981) %>%
  rename(dat86_n = n)
dat96_n = dat %>%
  count(C96_1981) %>%
  rename(dat96_n = n)
# ...

dat_counts = dat86_n %>%
  full_join(dat96_n, by = c("C86_1981" = "C96_1981"))
  # ...

这个方法可以实现功能，但如果我的数据稍后有任何更改，它就不够健壮。我希望能以编程方式解决这个问题。

我尝试了一个循环：

lapply(dat, count)
# Error in UseMethod("groups") : 
# no applicable method for 'groups' applied to an object of class "character"

(purrr::map() 也会出现相同的错误)。我认为这个错误是因为 count() 函数期望接收一个tbl和一个单独的变量作为参数，所以我也尝试了这种方式：

lapply(dat, function(x) {
  count(dat, x)
})
# Error in grouped_df_impl(data, unname(vars), drop) : 
# Column `x` is unknown

再次运行purrr::map()时出现相同的错误。我也尝试了summarise_all()的变体：

dat %>% 
  summarise_all(count)
  # Error in summarise_impl(.data, dots) : 
  # Evaluation error: no applicable method for 'groups' applied to an object of class "character".

我感觉我错过了一些显而易见的东西，解决方案应该很简单。dplyr 的解决方案特别受欢迎，因为这是我经常使用的。

- Phil

Answer 1

同时使用tidyr包，以下代码可达到目的：

dat %>% tidyr::gather(name, city) %>% dplyr::group_by(name, city) %>% dplyr::count() %>% dplyr::ungroup %>% tidyr::spread(name, n)

结果：

# A tibble: 15 x 7
                           city C00_1981 C04_1981 C08_1981 C12_1981 C86_1981 C96_1981
 *                        <chr>    <int>    <int>    <int>    <int>    <int>    <int>
 1              Buckinghamshire       NA       NA       NA       NA        1        1
 2 Cornwall and Isles of Scilly       NA       NA        1        1        1       NA
 3                        Devon       NA       NA       NA       NA        1       NA
 4                  Dunfermline       NA       NA        1        1       NA       NA
 5                   Humberside        1        1        1        1       NA       NA
 6                 Inner London        1       NA       NA       NA       NA       NA
 7                         Kent       NA        1        1        1        1       NA
 8                    Kirkcaldy        1       NA       NA       NA        1        1
 9                   Lancashire        1       NA       NA       NA       NA       NA
10            Not known/missing       NA        1       NA       NA       NA        1
11                 Outer London        1       NA       NA       NA        1        1
12                  Oxfordshire       NA       NA        1       NA       NA       NA
13                      Renfrew       NA        1        1        1        1       NA
14            Ross and Cromarty        1        1        1        1        1        1
15                         <NA>        4        5        3        4        2        5

- You-leee

---

是的！谢谢你！调整一下：你可以不用 ungroup()，因为 count() 已经帮你做了这个，但除此之外完美无缺。 - Phil

Answer 2

@You-leee刚刚比我更快一步；）

使用tidyverse；

library(tidyverse)

df <- 
  dat %>% 
  gather (year, county) %>% 
  group_by(year, county) %>% 
  summarise(no = n()) %>% 
  spread (year, no)

# A tibble: 15 x 7
                         county C00_1981 C04_1981 C08_1981 C12_1981 C86_1981 C96_1981
 *                        <chr>    <int>    <int>    <int>    <int>    <int>    <int>
 1              Buckinghamshire       NA       NA       NA       NA        1        1
 2 Cornwall and Isles of Scilly       NA       NA        1        1        1       NA
 3                        Devon       NA       NA       NA       NA        1       NA
 4                  Dunfermline       NA       NA        1        1       NA       NA
 5                   Humberside        1        1        1        1       NA       NA  
 6                 Inner London        1       NA       NA       NA       NA       NA
 7                         Kent       NA        1        1        1        1       NA
 8                    Kirkcaldy        1       NA       NA       NA        1        1
 9                   Lancashire        1       NA       NA       NA       NA       NA
10            Not known/missing       NA        1       NA       NA       NA        1
11                 Outer London        1       NA       NA       NA        1        1
12                  Oxfordshire       NA       NA        1       NA       NA       NA
13                      Renfrew       NA        1        1        1        1       NA
14            Ross and Cromarty        1        1        1        1        1        1
15                         <NA>        4        5        3        4        2        5

- sorearm

---

感谢@Phil，总是需要这些点来提高声誉！;) - sorearm

Answer 3

之前的答案使用gather+count+spread方法可以很好地工作，但对于非常大的数据集（无论是大组还是多个变量），该方法并不适用。这里提供另一种选择，使用map-count + join方法，在处理非常大的数据时，速度似乎快了2倍：

library(tidyverse)
N <-  1000000
df <- tibble(x1=sample(letters, N, replace = TRUE),
             x2=sample(letters, N, replace = TRUE),
             x3=sample(letters, N, replace = TRUE),
             x4=sample(letters, N, replace = TRUE),
             x5=sample(letters, N, replace = TRUE))


res1 <- map(c("x1", "x2", "x3", "x4", "x5"), function(x) select_at(df, x) %>%  count(!!rlang::sym(x)) %>% 
         rename(value=!!rlang::sym(x),
                !!rlang::sym(x):=n)) %>% 
  reduce(full_join, by = "value")

res2 <- df %>% 
  tidyr::gather(variable, value) %>% 
  dplyr::group_by(variable, value) %>%
  dplyr::count() %>% dplyr::ungroup()%>%
  tidyr::spread(variable, n)

all.equal(res1, res2)
#> [1] TRUE

library(microbenchmark)
microbenchmark(s1=map(c("x1", "x2", "x3", "x4", "x5"), function(x) select_at(df, x) %>%  count(!!rlang::sym(x)) %>% 
                     rename(value=!!rlang::sym(x),
                            !!rlang::sym(x):=n)) %>% 
                 reduce(full_join, by = "value"),
               s2= df %>% 
                 tidyr::gather(variable, value) %>% 
                 dplyr::group_by(variable, value) %>%
                 dplyr::count() %>% dplyr::ungroup()%>%
                 tidyr::spread(variable, n),
               times = 50, check = "equal")
#> Unit: milliseconds
#>  expr      min       lq     mean   median       uq      max neval
#>    s1 214.9027 220.2292 241.8811 229.0913 242.2507 368.5147    50
#>    s2 412.8934 447.5347 515.2612 528.0221 561.7649 692.5999    50

^{此内容由 reprex软件包 (v0.3.0) 于2020年5月19日创建。}

- Matifou