I have a data frame as such:
metric1 metric2 metric3 field1 field2
1 1.07809668 4.2569882 7.1710095 L S1
2 0.56174763 1.2660273 -0.3751915 L S2
3 1.17447327 5.5186679 11.6868322 L S2
4 0.32830724 -0.8374830 1.8973718 S S2
5 -0.51213503 -0.3076640 10.0730274 S S1
6 0.24133119 2.7984703 15.9622215 S S1
7 1.96664414 0.1818531 2.7416768 S S3
8 0.06669409 3.8652075 10.5066330 S S3
9 1.14660437 8.5703119 3.4294062 L S4
10 -0.72785683 9.3320762 1.3827989 L S4
I am showing 2 fields but have several more. I need to sum the metrics grouped by each field e.g. for field1:
DF %>% group_by(field1) %>% summarise_each(funs(sum),metric1,metric2,metric3)
I can do this for each field where the columns would be sum(metric1), sum(metric2), sum(metric3), but the table output I need is something like this:
L(field1) S(field1) S1(field2) S2(field2) S3(field2) S4(field2)
sum(metric1)
sum(metric2)
sum(metric3)
I believe there must be a way to do this using tidyr along with dplyr but cannot figure it out
Try recast
from reshape2
package
library(reshape2)
recast(DF, variable ~ field1 + field2, sum)
# variable L_S1 L_S2 L_S4 S_S1 S_S2 S_S3
# 1 metric1 1.078097 1.736221 0.4187475 -0.2708038 0.3283072 2.033338
# 2 metric2 4.256988 6.784695 17.9023881 2.4908063 -0.8374830 4.047061
# 3 metric3 7.171010 11.311641 4.8122051 26.0352489 1.8973718 13.248310
which is the same as
dcast(melt(DF, c("field1", "field2")), variable ~ field1 + field2, sum)
You also can combine it with tidyr::gather
if you want, but you can't use tidyr::spread
because it doesn't have fun.aggregate
argument
DF %>%
gather(variable, value, -(field1:field2)) %>%
dcast(variable ~ field1 + field2, sum)
# variable L_S1 L_S2 L_S4 S_S1 S_S2 S_S3
# 1 metric1 1.078097 1.736221 0.4187475 -0.2708038 0.3283072 2.033338
# 2 metric2 4.256988 6.784695 17.9023881 2.4908063 -0.8374830 4.047061
# 3 metric3 7.171010 11.311641 4.8122051 26.0352489 1.8973718 13.248310
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With