Create tabular summary with a total row

Question

Is there an elegant one-liner (using any R package) to accomplish the following?

Click to copy

tab <- aggregate(. ~ Species, dat=iris, mean)
total <- data.frame(Species='Overall', t(colMeans(iris[,-5])))
rbind(tab, total)

Answer 1

Package tables

Click to copy

library(tables)
tabular( (Species + 1) ~ All(iris)*(mean),data=iris)
> tabular( (Species + 1) ~ All(iris)*(mean),data=iris)

            Sepal.Length Sepal.Width Petal.Length Petal.Width
 Species    mean         mean        mean         mean       
 setosa     5.006        3.428       1.462        0.246      
 versicolor 5.936        2.770       4.260        1.326      
 virginica  6.588        2.974       5.552        2.026      
 All        5.843        3.057       3.758        1.199  

but I cheated and made a slight copy to the example in the help files ;) so credit to Duncan Murdoch.

or in sqldf

Click to copy

library(sqldf)

library(sqldf)

Click to copy

sqldf(" 
   select Species, 
       avg(Sepal_Length) `Sepal.Length`,
       avg(Sepal_Width) `Sepal.Width`,
       avg(Petal_Length) `Petal.Length`,
       avg(Petal_Width) `Petal.Width` 
    from iris 
    group by Species 
union all 
    select 'All', 
       avg(Sepal_Length) `Sepal.Length`,
       avg(Sepal_Width) `Sepal.Width`, 
       avg(Petal_Length) `Petal.Length`,
       avg(Petal_Width) `Petal.Width` 
    from iris"
)

which could be written a bit more compactly like this:

Click to copy

variables <- "avg(Sepal_Length) `Sepal.Length`,
    avg(Sepal_Width) `Sepal.Width`,
    avg(Petal_Length) `Petal.Length`,
    avg(Petal_Width) `Petal.Width`"

fn$sqldf(" select Species, $variables from iris group by Species 
    union all select 'All', $variables from iris")

giving

Click to copy

     Species Sepal.Length Sepal.Width Petal.Length Petal.Width
1     setosa     5.006000    3.428000        1.462    0.246000
2 versicolor     5.936000    2.770000        4.260    1.326000
3  virginica     6.588000    2.974000        5.552    2.026000
4        All     5.843333    3.057333        3.758    1.199333

Answer 2

package reshape2 is perhaps a bit slicker here, getting it into two steps:

Click to copy

library(reshape2)
iris.m <- melt(iris, id.vars = "Species")
dcast(Species ~ variable, data = iris.m, fun.aggregate = mean, margins = "Species")
#-----
     Species Sepal.Length Sepal.Width Petal.Length Petal.Width
1     setosa     5.006000    3.428000        1.462    0.246000
2 versicolor     5.936000    2.770000        4.260    1.326000
3  virginica     6.588000    2.974000        5.552    2.026000
4      (all)     5.843333    3.057333        3.758    1.199333

See the details to the margins argument on ?dcast