Geek Answers Handbook

Create a pivot table with a common row

Is there an elegant single line (using any R package) to accomplish the following?

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

package reshape2 may be smoothed out a bit here, doing it in two steps:

 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 details in the fields argument on? dcast

+3
source

Package tables

 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 small copy, for example, in the help files;) so the loan is Duncan Murdoch.

or in sqldf

 library(sqldf)

Library (sqldf)

 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 can be written a little more compactly as follows:

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

gives

  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
+6
source

More articles:


All Articles