R语言如何将多分类变量数据框转换为频数统计宽表？

问题说明 #

需要对包含多列分类变量的数据框，按site、date、ID分组统计各类行为的出现频数，最终得到宽格式的频数统计结果。

原始数据样例 #

Site  Date          ID  X1      X2      X3
A     June - 01/16  1   aware   resting resting
B     June - 03/16  2   aware   feeding feeding
C     June - 01/16  1   resting aware   aware

预期输出样例 #

site   date           ID  aware   resting   feeding
A      June - 01/16   1   3       2         1 
B      June - 01/16   2   1       0         2 

尝试过的代码 #

data_frame %>% 
  dplyr::count((data_frame[c(1:3)]),cbind(data_frame[c(4:6)])) %>% 
  tidyr::spread(key = (data_frame[c(4:6)]),value = n)

示例数据集 #

data_frame <- structure(data.frame(site = c("A", "B", "C", "A", "B", "C", "D"), 
date = c("June - 01/16","June - 03/16", "June - 01/16", "June - 01/16", "June - 03/16", "June - 03/16", "June - 03/16"), 
ID = c("1", "2", "1", "3", "1", "2", "3"), 
X1= c("aware", "aware","resting","feeding","aware", "resting","feeding"),
X2 = c("resting","feeding","aware","na","na","aware","resting"),
X3 = c("resting","feeding","aware", "aware","resting","feeding","aware")))

解决方法 #

原有代码的问题在于count()无法直接对多列分类值同时计数，需要先将X1/X2/X3三列转为长格式，再统计频数，最后转回宽格式即可，完整可运行代码如下：

library(dplyr)
library(tidyr)

result <- data_frame %>%
  # 将X开头的三列行为列转为长格式，仅保留行为值
  pivot_longer(cols = starts_with("X"), names_to = NULL, values_to = "behavior") %>%
  # 过滤空值na
  filter(behavior != "na") %>%
  # 按分组字段+行为类型统计频数
  count(site, date, ID, behavior) %>%
  # 转回宽格式，无对应行为的频数默认填充0
  pivot_wider(names_from = behavior, values_from = n, values_fill = 0)

执行后得到的result对象就是符合要求的宽格式频数统计表。 #

内容的提问来源于stack exchange，提问作者CMai