使用多列枢轴 [英] Pivot using multiple columns

问题描述

我有一个包含5列的数据集：

I have a data set with 5 columns:

store_id    year    event    item    units
123         2015     sale_2   abc      2
234         2015     sale_3   def      1
345         2015     sale_2   xyz      5

我尝试按 store_id，年份和事件轮换项以获得 sum 。例如

I'm trying to rotate out the items by store_id, year, and event to get the sum. For instance

store_id    year    event    abc     def   xyz 
123          2015    sale_2   2       0     0
234          2015    sale_3   0       1     0
345          2015    sale_2   0       0     5    

我很难确定最好的方法。通常，我会在插入符号中使用dummyVars来执行此操作，但我需要加总而不是标志。我看过tapply，但它不能处理2个以上的分组变量。

I'm having trouble figuring out the best method. Normally I'd use dummyVars in caret to do this but I need sums instead of flag. I've looked at tapply but it can't handle more than 2 grouping variables.

还有其他建议吗？

推荐答案

library(reshape2)
dcast(df, store_id + year + event ~ item, fun.aggregate = sum, value.var='units')
#    store_id year  event abc def xyz
# 1:      123 2015 sale_2   2   0   0
# 2:      234 2015 sale_3   0   1   0
# 3:      345 2015 sale_2   0   0   5

对于大型数据集，请考虑

For large datasets consider

# uses dcast.data.table, much faster
library(data.table)
setDT(df)
dcast(df, store_id + year + event ~ item, fun.aggregate = sum, value.var='units') 

这篇关于使用多列枢轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！