通过多列进行聚合，并从长到宽进行整形 [英] Aggregate by multiple columns and reshape from long to wide

问题描述

在SO上存在一些与此主题类似的问题，但与我的用例并不完全相同.我有一个数据集，列的布局如下图所示

There are some questions similar to this topic on SO but not exactly like my usecase. I have a dataset where the columns are laid out as shown below

     Id        Description          Value
     10        Cat                  19
     10        Cat                  20
     10        Cat                  5
     10        Cat                  13
     11        Cat                  17
     11        Cat                  23
     11        Cat                  7
     11        Cat                  14  
     10        Dog                  19
     10        Dog                  20
     10        Dog                  5
     10        Dog                  13
     11        Dog                  17
     11        Dog                  23
     11        Dog                  7
     11        Dog                  14    

我想做的是通过ID，描述捕获值"列的均值.最终的数据集看起来像这样.

What I am trying to do is capture the mean of the Value column by Id, Description. The final dataset would look like this.

     Id       Cat         Dog 
     10       14.25       28.5
     11       15.25       15.25

我可以像这样不太有效地以非常粗糙的方式做到这一点

I can do this in a very rough manner not very efficient like this

tempdf1 <- df %>%
  filter(str_detect(Description, "Cat")) %>%
   group_by(Id, Description) %>%
  summarize(Mean_Value = mean(Value) , na.rm = TRUE))

这不是很方便.非常感谢任何有关如何更有效地实现预期结果的建议.

This is not very convenient. Any advise on how how to accomplish the expected results more efficiently is much appreciated.

推荐答案

使用reshape2()包中的dcast甚至acast

dcast(dat,Id~Description,mean)
   Id   Cat   Dog
 1 10 14.25 14.25
 2 11 15.25 15.25

Base R可能会更长一些:

 reshape(aggregate(.~Id+Description,dat,mean),direction = "wide",v.names  = "Value",idvar = "Id",timevar = "Description")
  Id Value.Cat Value.Dog
1 10     14.25     14.25
2 11     15.25     15.25

这篇关于通过多列进行聚合，并从长到宽进行整形的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！