I want to calculate a statistic on a number of subgroups of a dataframe,
then put the results into a dataframe. (What SAS PROC MEANS does, I
think, though it's been years since I used it.)
This is possible using by(), but it seems cumbersome and fragile. Is
there a more straightforward way than this?
Here's a simple example showing my current strategy:
> dataset <- data.frame(gp1 = rep(1:2, c(4,4)), gp2 = rep(1:4,
c(2,2,2,2)), value = rnorm(8))
> dataset
gp1 gp2 value
1 1 1 0.9493232
2 1 1 -0.0474712
3 1 2 -0.6808021
4 1 2 1.9894999
5 2 3 2.0154786
6 2 3 0.4333056
7 2 4 -0.4746228
8 2 4 0.6017522
>
> handleonegroup <- function(subset) data.frame(gp1 = subset$gp1[1],
+ gp2 = subset$gp2[1], statistic = mean(subset$value))
>
> bylist <- by(dataset, list(dataset$gp1, dataset$gp2), handleonegroup)
>
> result <- do.call('rbind', bylist)
> result
gp1 gp2 statistic
1 1 1 0.45092598
11 1 2 0.65434890
12 2 3 1.22439210
13 2 4 0.06356469
tapply() is inappropriate because I don't have all possible combinations
of gp1 and gp2 values, only some of them:
> tapply(dataset$value, list(dataset$gp1, dataset$gp2), mean)
1 2 3 4
1 0.450926 0.6543489 NA NA
2 NA NA 1.224392 0.06356469
In the real case, I only have a very sparse subset of all the
combinations, and tapply() and by() both die for lack of memory.
Any suggestions on how to do what I want, without using SAS?
Duncan Murdoch
I'm not entirely sure what you want, but maybe this does the trick?
data.frame.by <- function(data, variables, fun, ...) {
if (length(variables) == 0 ) {
df <- data.frame(results = 0)
df$results <- list(fun(data$value, ...))
return(df)
}
sorted <- sort.df(data, variables)[,c(variables), drop=FALSE]
duplicates <- duplicated(sorted[,variables, drop=FALSE])
index <- cumsum(!duplicates)
results <- by(data, index, fun, ...)
cols <- sorted[!duplicates,variables, drop=FALSE]
cols$results <- array(results)
cols
}
sort.df <- function(data, vars) {
data[do.call("order", data[,vars, drop=FALSE]), ,drop=FALSE]
}
dataset <- data.frame(gp1 = rep(1:2, c(4,4)), gp2 = rep(1:4,
c(2,2,2,2)), value = rnorm(8))
data.frame.by(dataset, c("gp1", "gp2"), function(data)
mean(data$value))
data.frame.by(dataset, "gp1", function(data) tapply(data$value,
data$gp2, mean))
data.frame.by(dataset, "gp1", function(data) lm(gp2 ~ value, data)) #
doesn't print, but everything is there ok
(note that the results column will be a list if necessary - this may
be a serious abuse of data frames, but I'm not sure and no one replied
when I queried the list)
Hadley
Duncan Murdoch <murdoch at stats.uwo.ca> writes:> I want to calculate a statistic on a number of subgroups of a dataframe, > then put the results into a dataframe. (What SAS PROC MEANS does, I > think, though it's been years since I used it.) > > This is possible using by(), but it seems cumbersome and fragile. Is > there a more straightforward way than this? > > Here's a simple example showing my current strategy: > > > dataset <- data.frame(gp1 = rep(1:2, c(4,4)), gp2 = rep(1:4, > c(2,2,2,2)), value = rnorm(8)) > > dataset > gp1 gp2 value > 1 1 1 0.9493232 > 2 1 1 -0.0474712 > 3 1 2 -0.6808021 > 4 1 2 1.9894999 > 5 2 3 2.0154786 > 6 2 3 0.4333056 > 7 2 4 -0.4746228 > 8 2 4 0.6017522 > > > > handleonegroup <- function(subset) data.frame(gp1 = subset$gp1[1], > + gp2 = subset$gp2[1], statistic = mean(subset$value)) > > > > bylist <- by(dataset, list(dataset$gp1, dataset$gp2), handleonegroup) > > > > result <- do.call('rbind', bylist) > > result > gp1 gp2 statistic > 1 1 1 0.45092598 > 11 1 2 0.65434890 > 12 2 3 1.22439210 > 13 2 4 0.06356469 > > tapply() is inappropriate because I don't have all possible combinations > of gp1 and gp2 values, only some of them: > > > tapply(dataset$value, list(dataset$gp1, dataset$gp2), mean) > 1 2 3 4 > 1 0.450926 0.6543489 NA NA > 2 NA NA 1.224392 0.06356469 > > > > In the real case, I only have a very sparse subset of all the > combinations, and tapply() and by() both die for lack of memory. > > Any suggestions on how to do what I want, without using SAS?Have you tried aggregate()? Alternatively, you migth split on interaction(...., drop=TRUE) -- O__ ---- Peter Dalgaard ?ster Farimagsgade 5, Entr.B c/ /'_ --- Dept. of Biostatistics PO Box 2099, 1014 Cph. K (*) \(*) -- University of Copenhagen Denmark Ph: (+45) 35327918 ~~~~~~~~~~ - (p.dalgaard at biostat.ku.dk) FAX: (+45) 35327907
Check out summaryBy in the doBy package at: http://genetics.agrsci.dk/~sorenh/misc e.g. summaryBy(value ~ gp1 + gp2, data = dataset) On 9/30/05, Duncan Murdoch <murdoch at stats.uwo.ca> wrote:> I want to calculate a statistic on a number of subgroups of a dataframe, > then put the results into a dataframe. (What SAS PROC MEANS does, I > think, though it's been years since I used it.) > > This is possible using by(), but it seems cumbersome and fragile. Is > there a more straightforward way than this? > > Here's a simple example showing my current strategy: > > > dataset <- data.frame(gp1 = rep(1:2, c(4,4)), gp2 = rep(1:4, > c(2,2,2,2)), value = rnorm(8)) > > dataset > gp1 gp2 value > 1 1 1 0.9493232 > 2 1 1 -0.0474712 > 3 1 2 -0.6808021 > 4 1 2 1.9894999 > 5 2 3 2.0154786 > 6 2 3 0.4333056 > 7 2 4 -0.4746228 > 8 2 4 0.6017522 > > > > handleonegroup <- function(subset) data.frame(gp1 = subset$gp1[1], > + gp2 = subset$gp2[1], statistic = mean(subset$value)) > > > > bylist <- by(dataset, list(dataset$gp1, dataset$gp2), handleonegroup) > > > > result <- do.call('rbind', bylist) > > result > gp1 gp2 statistic > 1 1 1 0.45092598 > 11 1 2 0.65434890 > 12 2 3 1.22439210 > 13 2 4 0.06356469 > > tapply() is inappropriate because I don't have all possible combinations > of gp1 and gp2 values, only some of them: > > > tapply(dataset$value, list(dataset$gp1, dataset$gp2), mean) > 1 2 3 4 > 1 0.450926 0.6543489 NA NA > 2 NA NA 1.224392 0.06356469 > > > > In the real case, I only have a very sparse subset of all the > combinations, and tapply() and by() both die for lack of memory. > > Any suggestions on how to do what I want, without using SAS? > > Duncan Murdoch > > ______________________________________________ > R-devel at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-devel >
On Fri, 2005-09-30 at 13:22 -0400, Duncan Murdoch wrote:> I want to calculate a statistic on a number of subgroups of a dataframe, > then put the results into a dataframe. (What SAS PROC MEANS does, I > think, though it's been years since I used it.) > > This is possible using by(), but it seems cumbersome and fragile. Is > there a more straightforward way than this? > > Here's a simple example showing my current strategy: > > > dataset <- data.frame(gp1 = rep(1:2, c(4,4)), gp2 = rep(1:4, > c(2,2,2,2)), value = rnorm(8)) > > dataset > gp1 gp2 value > 1 1 1 0.9493232 > 2 1 1 -0.0474712 > 3 1 2 -0.6808021 > 4 1 2 1.9894999 > 5 2 3 2.0154786 > 6 2 3 0.4333056 > 7 2 4 -0.4746228 > 8 2 4 0.6017522 > > > > handleonegroup <- function(subset) data.frame(gp1 = subset$gp1[1], > + gp2 = subset$gp2[1], statistic = mean(subset$value)) > > > > bylist <- by(dataset, list(dataset$gp1, dataset$gp2), handleonegroup) > > > > result <- do.call('rbind', bylist) > > result > gp1 gp2 statistic > 1 1 1 0.45092598 > 11 1 2 0.65434890 > 12 2 3 1.22439210 > 13 2 4 0.06356469 > > tapply() is inappropriate because I don't have all possible combinations > of gp1 and gp2 values, only some of them: > > > tapply(dataset$value, list(dataset$gp1, dataset$gp2), mean) > 1 2 3 4 > 1 0.450926 0.6543489 NA NA > 2 NA NA 1.224392 0.06356469 > > > > In the real case, I only have a very sparse subset of all the > combinations, and tapply() and by() both die for lack of memory. > > Any suggestions on how to do what I want, without using SAS? > > Duncan MurdochDuncan, Does this do what you want?> set.seed(1)> df <- data.frame(gp1 = rep(1:2, c(4,4)),gp2 = rep(1:4, c(2,2,2,2)), value = rnorm(8))> dfgp1 gp2 value 1 1 1 -0.6264538 2 1 1 0.1836433 3 1 2 -0.8356286 4 1 2 1.5952808 5 2 3 0.3295078 6 2 3 -0.8204684 7 2 4 0.4874291 8 2 4 0.7383247> means <- aggregate(df$value, list(gp1 = df$gp1, gp2 = df$gp2), mean)> meansgp1 gp2 x 1 1 1 -0.2214052 2 1 2 0.3798261 3 2 3 -0.2454803 4 2 4 0.6128769> merge(df, means, by = c("gp1", "gp2"))gp1 gp2 value x 1 1 1 -0.6264538 -0.2214052 2 1 1 0.1836433 -0.2214052 3 1 2 -0.8356286 0.3798261 4 1 2 1.5952808 0.3798261 5 2 3 0.3295078 -0.2454803 6 2 3 -0.8204684 -0.2454803 7 2 4 0.4874291 0.6128769 8 2 4 0.7383247 0.6128769 HTH, Marc Schwartz