subtotals in columns usind reshape2 in R
- by user1043144
I have spent some time now learning RESHAPE2 and plyr but I still do not get it. This time I have a problem with (a) subtotals and (b) passing different aggregate functions .
Here an example using data from the excellent tutorial on the blog of mrdwab http://news.mrdwab.com/
# libraries
library(plyr)
library(reshape2)
# get data and add few more variables
book.sales = read.csv("http://news.mrdwab.com/data-booksales")
book.sales$Stock = book.sales$Quantity + 10
book.sales$SubjCat[(book.sales$Subject == 'Economics') | (book.sales$Subject == 'Management') ] <- '1_EconSciences'
book.sales$SubjCat[book.sales$Subject %in% c('Anthropology', 'Politics', 'Sociology') ] <- '2_SocSciences'
book.sales$SubjCat[book.sales$Subject %in% c('Communication', 'Fiction', 'History', 'Research', 'Statistics') ] <- '3_other'
# to get to my starting dataframe (close to the project I am working on)
book.sales1 <- ddply(book.sales, c('Region', 'Representative', 'SubjCat', 'Subject', 'Publisher'), summarize,
Stock = sum(Stock),
Sold = sum(Quantity),
Ratio = round((100 * sum(Quantity)/ sum(Stock)), digits = 1))
#melt it
m.book.sales = melt(data = book.sales1,
id.vars = c('Region', 'Representative', 'SubjCat', 'Subject', 'Publisher'),
measured.vars = c('Stock', 'Sold', 'Ratio'))
# cast it
Tab1 <- dcast(data = m.book.sales,
formula = Region + Representative ~ Publisher + variable,
fun.aggregate = sum,
margins = c('Region', 'Representative'))
Now my questions :
I have been able to add the subtotals in rows. But is it possible also to add margins in the columns. Say for example, Totals of Stock for one Publisher ?
Sorry I meant to say example total sold for all publishers
There is a problem with the columns with “ratio”. How can I get “mean” instead of “sum” for this variable ?
P.S: I have seen some examples using reshape. Will you recommend to use it instead of reshape2 (which seems not to include the functionalities of two functions).
