Subtotals in columns using reshape2

Question

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 a tutorial on the blog of mrdwab

# 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 --- # Please ignore this cast this was a mistake 
# Tab1 <- dcast(data = m.book.sales, 
#         formula = Region + Representative ~ Publisher + variable,
#         fun.aggregate = sum, margins = c('Region', 'Representative'))

Tab1 <- dcast(data = m.book.sales, formula = Region + Representative ~ 
  SubjCat + Subject + variable, fun.aggregate = sum,
              margins = c('Region', 'Representative', 'SubjCat', 'Subject'))

Now my questions :

1. 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.

2. There is a problem with the columns with “ratio”. How can I get “mean” instead of “sum” for this variable ?

Please note: Question number one (about subtotals in margins) could be solved.

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

Answer 1

Not sure exactly what you want for question 1, but if you want total of stock for Publisher would you not just do this?

 totalofstock <- ddply(book.sales, ('Publisher'), function(x)   
                      data.frame=c(subtotals  =  sum(x$Stock)))

and if you want to add it to Tab1 you just do this:

Tab1$bloomsburytotalofstock<-totalofstock[1,][[2]]
head(Tab1)

As for question 2 getting a mean instead of a sum surely you would be changing the function from sum to mean

e.g.

ratiomeans <- ddply(book.sales1, ('Publisher'), function(x)   
                      data.frame=c(ratioMEAN  =  mean(x$Ratio)))

Also I would suggest sticking with reshape2. reshape2 is basically the new version of reshape. As far as I know reshape is no longer being worked on but still exists so that people with old code using reshape do not have to rewrite everything.

EDIT

justratio<-(m.book.sales[m.book.sales$variable=="Ratio",])
Tab2 <- dcast(data = justratio, 
        formula = Region + Representative ~ SubjCat + Subject + variable,
        fun.aggregate = mean,
        margins = c('Region', 'Representative', 'SubjCat', 'Subject'))
final<-merge(Tab1,Tab2,by=c("Region","Representative"))