按另一个因素对每个因素计数分组

Question

我知道这个问题的答案很简单，但我已经广泛搜索了论坛，却未能找到解决方案。

我有一个名为 Data_source 的列，它是我想按照因素分组变量的列。

我有一系列的 symptom* 变量，我希望根据 Data_source 计算它们的数量。

出于某种原因，我无法弄清如何做到这一点。通常的 group_by 函数似乎不能适当地工作。

以下是相关的数据框：

 df <- wrapr::build_frame(
   "Data_source"  , "Sex"   , "symptoms_decLOC", "symptoms_nausea_vomitting" |
     "1"          , "Female", NA_character_    , NA_character_               |
     "1"          , "Female", NA_character_    , NA_character_               |
     "1"          , "Female", "No"             , NA_character_               |
     "1"          , "Female", "Yes"            , "No"                        |
     "1"          , "Female", "Yes"            , "No"                        |
     "1"          , "Female", "Yes"            , "No"                        |
     "1"          , "Male"  , "Yes"            , "No"                        |
     "1"          , "Female", "Yes"            , "No"                        |
     "2"          , "Female", NA_character_    , NA_character_               |
     "2"          , "Male"  , NA_character_    , NA_character_               |
     "2"          , "Male"  , NA_character_    , NA_character_               |
     "2"          , "Female", "Yes"            , "No"                        |
     "2"          , "Female", "Yes"            , "No"                        |
     "2"          , "Male"  , NA_character_    , NA_character_               |
     "2"          , "Male"  , NA_character_    , NA_character_               |
     "2"          , "Male"  , NA_character_    , NA_character_               |
     "2"          , "Female", NA_character_    , NA_character_               |
     "2"          , "Female", NA_character_    , NA_character_               |
     "2"          , "Male"  , NA_character_    , NA_character_               |
     "2"          , "Female", NA_character_    , NA_character_               )

请注意，"Sex"和"symptoms"变量都是包含缺失值的因子。我尝试了以下方法：

df %>% na.omit() %>% group_by(Data_source) %>% count("symptoms_decLOC")

以下这种方式不太优秀，因为我必须对每一列都重复操作。理想的方法是使用类似于 lapply(df, count) 的东西，但是这样做不能为每个组提供描述。

编辑

针对下面的问题，我已经添加了预期输出。我在 Excel 中进行了编辑，为了清晰起见着色了group_by。

请注意，我正在获取每个可能答案的详细信息。当我使用 dplyr 运行时，以下是输出。

> df %>% na.omit() %>% group_by(Data_source) %>% count("symptoms_decLOC")
# A tibble: 2 x 3
# Groups:   Data_source [2]
  Data_source `"symptoms_decLOC"`     n
  <chr>       <chr>               <int>
1 1           symptoms_decLOC         5
2 2           symptoms_decLOC         2

- Patrick

---

你想要的输出是什么？ - MKa

谢谢您的评论。我应该在最初的问题中加入这个。我已经进行了编辑，以进一步澄清我的需求。 - Patrick

Answer 1

这个方法可以解决大部分问题：还没有想出如何包括零计数组... 据说添加.drop=FALSE可以解决这个问题，但是对我来说不起作用（使用dplyr v. 0.8.0.9001）。最初的回答来源于：https://dev59.com/318e5IYBdhLWcg3wPIV_。

library(dplyr)
library(tidyr)
(df
    %>% tidyr::gather(var,val,-Data_source)
    %>% count(Data_source,var,val, .drop=FALSE)
    %>% na.omit()
)

结果：

  Data_source var                       val        n
  <chr>       <chr>                     <chr>  <int>
1 1           Sex                       Female     7
2 1           Sex                       Male       1
3 1           symptoms_decLOC           No         1
4 1           symptoms_decLOC           Yes        5
5 1           symptoms_nausea_vomitting No         5
6 2           Sex                       Female     6
7 2           Sex                       Male       6
8 2           symptoms_decLOC           Yes        2
9 2           symptoms_nausea_vomitting No         2

- Ben Bolker

---

使用这个语法，我得到的输出与你不同。我得到了 Error in count(., Data_source, var, val, .drop = FALSE) : unused arguments (val, .drop = FALSE)。 - Patrick

find("count")和packageVersion("dplyr")的结果是什么？ - Ben Bolker

这是一个输出链接：`> find("count") [1] "package:plyr" "package:dplyr"

packageVersion("dplyr") [1] ‘0.8.0.1’`

- Patrick

好的，既然两个包都被加载了，如果我将你的代码更改为 dplyr::count，那么我会得到预期的输出！谢谢。 - Patrick

当你加载dplyr之后再加载plyr时，你可能会收到一条警告，提示某些函数被屏蔽了。 - Ben Bolker

Answer 2

使用 @Ben Bolker 的答案来获取每个组的计数，使用 spread 和 gather 来包括零计数组。

dplyr

library(dplyr)
library(tidyr)

# Count number of occurences by Data_source 
df2 <- 
  df %>% 
  gather(variable, value, -Data_source) %>% 
  count(Data_source, variable, value, name = "counter") %>%
  na.omit() 

# For variable = "Sex", leave as is
# For everything else, in this case symptom* convert into factor to include zero count group
# Then spread with dataframe will NAs filled with 0, re-convert back to long to bind rows
bind_rows(df2 %>%
            filter(variable == "Sex"), 

          df2 %>%
            filter(variable != "Sex") %>%
            mutate(value = factor(value, levels = c("Yes", "No"))) %>%
            spread(key = value, value = counter, fill = 0) %>%
            gather(value, counter, -Data_source, -variable))  %>%

  arrange(Data_source, variable)

data.table

library(data.table)
dt <- data.table(df)

# Melt data by Data source
dt_melt <- melt(dt, id.vars = "Data_source", value.factor = FALSE, variable.factor = FALSE)

# Add counter, if NA then 0 else 1
dt_melt[, counter := 0]
dt_melt[!is.na(value), counter := 1]

# Sum number of occurrences
dt_count <- dt_melt[,list(counter = sum(counter)), by = c("Data_source", "variable", "value")]

# Split into two dt
dt2a <- dt_count[variable == "Sex", ]
dt2b <- dt_count[variable != "Sex" ,]

# only on symptoms variables
# Convert into factor variable
dt2b$value <- factor(dt2b$value, levels = c("Yes", "No"))
dt2b_dcast <- dcast(data = dt2b, formula = Data_source + variable ~ value, value.var = "counter", fill = 0, drop = FALSE)
dt2b_melt <- melt(dt2b_dcast, id.vars = c("Data_source", "variable"), variable.name = "value", value.name = "counter") 

# combine
combined_d <- rbind(dt2a, dt2b_melt)
combined_d[order(Data_source, variable), ]

- MKa

---

是的，我仍然无法让dplyr方法正常工作。Data.table确实可以工作，但它会添加大量代码。 - Patrick

Answer 3

我不太明白你的问题，但我假设你想要计算每个 symptom_* 列中非 NA 值的数量。

这是一个 data.table 的解决方案：

# load library

library(data.table)

# Suppose the table is called "dt". Convert it to a data.table:

setDT(dt)

# convert the wide table to a long one, filter the values that
# aren't NA and count both, by Data_source and by variable
# (variable is the created column with the symptom_* names)

melt(dt, id.vars = 1:2)[!is.na(value), 
                        .N, 
                         by = .(Data_source, variable)]

代码的每个部分都在做什么：

melt(dt, id.vars = 1:2)将dt从宽格式转换为长格式，并保留第1和第2列（Data_source和sex）不变。

!is.na(value)过滤掉之前每个symptom_*标题下的不是NA的值。

.N计算行数。

by = .(Data_source, variable)是我们用来计数的分组方式。variable是在重塑过程中symptom_*所在的列的名称。

- PavoDive

---

当我运行这个程序时，我得到了以下结果： 数据源 变量 N 1: 1 symptoms_decLOC 6 2: 2 symptoms_decLOC 2 3: 1 symptoms_nausea_vomitting 5 4: 2 symptoms_nausea_vomitting 2这并没有为每个单独的响应提供统计数据。 - Patrick

Answer 4

毫无疑问，最难的是保留数据中不存在的组合……下面是一种分两步解决问题的方法：

1. 准备一个不带计数的数据库

你可以自由选择任何操作方式，但我选择了针对变量“性别”计算两个块，因为其方式不同。这里不需要将这些块绑定在一起。

chunk1 <- expand.grid(
  Data_source = c("1", "2"),
  name = c("symptoms_decLOC", "symptoms_nausea_vomitting"),
  value = c("Yes", "No"),
  stringsAsFactors = FALSE
)

chunk2 <- expand.grid(
  Data_source = c("1", "2"),
  name = "Sex",
  value = c("Female", "Male"),
  stringsAsFactors = FALSE
)

2. 完成所要求的工作

library(dplyr)
library(tidyr)

df %>% 
  pivot_longer(cols = c("Sex", "symptoms_decLOC", "symptoms_nausea_vomitting"))%>%
  group_by(Data_source, name, value) %>%
  summarise(count = n()) %>%
  right_join(bind_rows(chunk1, chunk2), by = c("Data_source", "name", "value")) %>%
  arrange(Data_source, name) %>%
  mutate(count = zoo::na.fill(count, 0))

Et voilà

# A tibble: 12 x 4
# Groups:   Data_source, name [6]
   Data_source name                      value  count
   <chr>       <chr>                     <chr>  <int>
 1 1           Sex                       Female     7
 2 1           Sex                       Male       1
 3 1           symptoms_decLOC           Yes        5
 4 1           symptoms_decLOC           No         1
 5 1           symptoms_nausea_vomitting Yes        0
 6 1           symptoms_nausea_vomitting No         5
 7 2           Sex                       Female     6
 8 2           Sex                       Male       6
 9 2           symptoms_decLOC           Yes        2
10 2           symptoms_decLOC           No         0
11 2           symptoms_nausea_vomitting Yes        0
12 2           symptoms_nausea_vomitting No         2

它不是很短，但使用简单的功能。过程与在Excel中进行的过程类似，即准备结构，然后完成计数。

我希望它可以帮助到你 ;-)

- Jeffery Petit