r - 数据转换: spread categorical data frame to counts R

Question

我正在尝试将具有多个分类变量的数据框转换为频率计数。
数据如下所示:

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 

我尝试使用 dplyr 但没有设法选择我想要的所有变量(X1、X2 和 X3)

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")))

Answer 1

我们可以 reshape 成“长”格式，得到 count基于列并使用 pivot_wider reshape 回“宽”

library(dplyr)
library(tidyr)
data_frame %>% 
   pivot_longer(cols = X1:X3) %>% 
   select(-name) %>%
   count(site, date, ID, value) %>% 
   pivot_wider(names_from = value, values_from = n, values_fill = 0)

或者我们可以使用 values_fn

data_frame %>% 
   pivot_longer(cols = X1:X3) %>% 
   select(-name) %>%       
   pivot_wider(names_from = value, values_from = n, values_fill = 0,
        values_fn = length)