Mean per group in a data.frame
This type of operation is exactly what aggregate
was designed for:
d <- read.table(text=
'Name Month Rate1 Rate2
Aira 1 12 23
Aira 2 18 73
Aira 3 19 45
Ben 1 53 19
Ben 2 22 87
Ben 3 19 45
Cat 1 22 87
Cat 2 67 43
Cat 3 45 32', header=TRUE)
aggregate(d[, 3:4], list(d$Name), mean)
Group.1 Rate1 Rate2
1 Aira 16.33333 47.00000
2 Ben 31.33333 50.33333
3 Cat 44.66667 54.00000
Here we aggregate columns 3 and 4 of data.frame d
, grouping by d$Name
, and applying the mean
function.
Or, using a formula interface:
aggregate(. ~ Name, d[-2], mean)
Calculate the mean by group
There are many ways to do this in R. Specifically, by
, aggregate
, split
, and plyr
, cast
, tapply
, data.table
, dplyr
, and so forth.
Broadly speaking, these problems are of the form split-apply-combine. Hadley Wickham has written a beautiful article that will give you deeper insight into the whole category of problems, and it is well worth reading. His plyr
package implements the strategy for general data structures, and dplyr
is a newer implementation performance tuned for data frames. They allow for solving problems of the same form but of even greater complexity than this one. They are well worth learning as a general tool for solving data manipulation problems.
Performance is an issue on very large datasets, and for that it is hard to beat solutions based on data.table
. If you only deal with medium-sized datasets or smaller, however, taking the time to learn data.table
is likely not worth the effort. dplyr
can also be fast, so it is a good choice if you want to speed things up, but don't quite need the scalability of data.table
.
Many of the other solutions below do not require any additional packages. Some of them are even fairly fast on medium-large datasets. Their primary disadvantage is either one of metaphor or of flexibility. By metaphor I mean that it is a tool designed for something else being coerced to solve this particular type of problem in a 'clever' way. By flexibility I mean they lack the ability to solve as wide a range of similar problems or to easily produce tidy output.
Examples
base
functions
tapply
:
tapply(df$speed, df$dive, mean)
# dive1 dive2
# 0.5419921 0.5103974
aggregate
:
aggregate
takes in data.frames, outputs data.frames, and uses a formula interface.
aggregate( speed ~ dive, df, mean )
# dive speed
# 1 dive1 0.5790946
# 2 dive2 0.4864489
by
:
In its most user-friendly form, it takes in vectors and applies a function to them. However, its output is not in a very manipulable form.:
res.by <- by(df$speed, df$dive, mean)
res.by
# df$dive: dive1
# [1] 0.5790946
# ---------------------------------------
# df$dive: dive2
# [1] 0.4864489
To get around this, for simple uses of by
the as.data.frame
method in the taRifx
library works:
library(taRifx)
as.data.frame(res.by)
# IDX1 value
# 1 dive1 0.6736807
# 2 dive2 0.4051447
split
:
As the name suggests, it performs only the "split" part of the split-apply-combine strategy. To make the rest work, I'll write a small function that uses sapply
for apply-combine. sapply
automatically simplifies the result as much as possible. In our case, that means a vector rather than a data.frame, since we've got only 1 dimension of results.
splitmean <- function(df) {
s <- split( df, df$dive)
sapply( s, function(x) mean(x$speed) )
}
splitmean(df)
# dive1 dive2
# 0.5790946 0.4864489
External packages
data.table:
library(data.table)
setDT(df)[ , .(mean_speed = mean(speed)), by = dive]
# dive mean_speed
# 1: dive1 0.5419921
# 2: dive2 0.5103974
dplyr
:
library(dplyr)
group_by(df, dive) %>% summarize(m = mean(speed))
plyr
(the pre-cursor of dplyr
)
Here's what the official page has to say about plyr
:
It’s already possible to do this with
base
R functions (likesplit
and
theapply
family of functions), butplyr
makes it all a bit easier
with:
- totally consistent names, arguments and outputs
- convenient parallelisation through the
foreach
package- input from and output to data.frames, matrices and lists
- progress bars to keep track of long running operations
- built-in error recovery, and informative error messages
- labels that are maintained across all transformations
In other words, if you learn one tool for split-apply-combine manipulation it should be plyr
.
library(plyr)
res.plyr <- ddply( df, .(dive), function(x) mean(x$speed) )
res.plyr
# dive V1
# 1 dive1 0.5790946
# 2 dive2 0.4864489
reshape2:
The reshape2
library is not designed with split-apply-combine as its primary focus. Instead, it uses a two-part melt/cast strategy to perform a wide variety of data reshaping tasks. However, since it allows an aggregation function it can be used for this problem. It would not be my first choice for split-apply-combine operations, but its reshaping capabilities are powerful and thus you should learn this package as well.
library(reshape2)
dcast( melt(df), variable ~ dive, mean)
# Using dive as id variables
# variable dive1 dive2
# 1 speed 0.5790946 0.4864489
Benchmarks
10 rows, 2 groups
library(microbenchmark)
m1 <- microbenchmark(
by( df$speed, df$dive, mean),
aggregate( speed ~ dive, df, mean ),
splitmean(df),
ddply( df, .(dive), function(x) mean(x$speed) ),
dcast( melt(df), variable ~ dive, mean),
dt[, mean(speed), by = dive],
summarize( group_by(df, dive), m = mean(speed) ),
summarize( group_by(dt, dive), m = mean(speed) )
)
> print(m1, signif = 3)
Unit: microseconds
expr min lq mean median uq max neval cld
by(df$speed, df$dive, mean) 302 325 343.9 342 362 396 100 b
aggregate(speed ~ dive, df, mean) 904 966 1012.1 1020 1060 1130 100 e
splitmean(df) 191 206 249.9 220 232 1670 100 a
ddply(df, .(dive), function(x) mean(x$speed)) 1220 1310 1358.1 1340 1380 2740 100 f
dcast(melt(df), variable ~ dive, mean) 2150 2330 2440.7 2430 2490 4010 100 h
dt[, mean(speed), by = dive] 599 629 667.1 659 704 771 100 c
summarize(group_by(df, dive), m = mean(speed)) 663 710 774.6 744 782 2140 100 d
summarize(group_by(dt, dive), m = mean(speed)) 1860 1960 2051.0 2020 2090 3430 100 g
autoplot(m1)
As usual, data.table
has a little more overhead so comes in about average for small datasets. These are microseconds, though, so the differences are trivial. Any of the approaches works fine here, and you should choose based on:
- What you're already familiar with or want to be familiar with (
plyr
is always worth learning for its flexibility;data.table
is worth learning if you plan to analyze huge datasets;by
andaggregate
andsplit
are all base R functions and thus universally available) - What output it returns (numeric, data.frame, or data.table -- the latter of which inherits from data.frame)
10 million rows, 10 groups
But what if we have a big dataset? Let's try 10^7 rows split over ten groups.
df <- data.frame(dive=factor(sample(letters[1:10],10^7,replace=TRUE)),speed=runif(10^7))
dt <- data.table(df)
setkey(dt,dive)
m2 <- microbenchmark(
by( df$speed, df$dive, mean),
aggregate( speed ~ dive, df, mean ),
splitmean(df),
ddply( df, .(dive), function(x) mean(x$speed) ),
dcast( melt(df), variable ~ dive, mean),
dt[,mean(speed),by=dive],
times=2
)
> print(m2, signif = 3)
Unit: milliseconds
expr min lq mean median uq max neval cld
by(df$speed, df$dive, mean) 720 770 799.1 791 816 958 100 d
aggregate(speed ~ dive, df, mean) 10900 11000 11027.0 11000 11100 11300 100 h
splitmean(df) 974 1040 1074.1 1060 1100 1280 100 e
ddply(df, .(dive), function(x) mean(x$speed)) 1050 1080 1110.4 1100 1130 1260 100 f
dcast(melt(df), variable ~ dive, mean) 2360 2450 2492.8 2490 2520 2620 100 g
dt[, mean(speed), by = dive] 119 120 126.2 120 122 212 100 a
summarize(group_by(df, dive), m = mean(speed)) 517 521 531.0 522 532 620 100 c
summarize(group_by(dt, dive), m = mean(speed)) 154 155 174.0 156 189 321 100 b
autoplot(m2)
Then data.table
or dplyr
using operating on data.table
s is clearly the way to go. Certain approaches (aggregate
and dcast
) are beginning to look very slow.
10 million rows, 1,000 groups
If you have more groups, the difference becomes more pronounced. With 1,000 groups and the same 10^7 rows:
df <- data.frame(dive=factor(sample(seq(1000),10^7,replace=TRUE)),speed=runif(10^7))
dt <- data.table(df)
setkey(dt,dive)
# then run the same microbenchmark as above
print(m3, signif = 3)
Unit: milliseconds
expr min lq mean median uq max neval cld
by(df$speed, df$dive, mean) 776 791 816.2 810 828 925 100 b
aggregate(speed ~ dive, df, mean) 11200 11400 11460.2 11400 11500 12000 100 f
splitmean(df) 5940 6450 7562.4 7470 8370 11200 100 e
ddply(df, .(dive), function(x) mean(x$speed)) 1220 1250 1279.1 1280 1300 1440 100 c
dcast(melt(df), variable ~ dive, mean) 2110 2190 2267.8 2250 2290 2750 100 d
dt[, mean(speed), by = dive] 110 111 113.5 111 113 143 100 a
summarize(group_by(df, dive), m = mean(speed)) 625 630 637.1 633 644 701 100 b
summarize(group_by(dt, dive), m = mean(speed)) 129 130 137.3 131 142 213 100 a
autoplot(m3)
So data.table
continues scaling well, and dplyr
operating on a data.table
also works well, with dplyr
on data.frame
close to an order of magnitude slower. The split
/sapply
strategy seems to scale poorly in the number of groups (meaning the split()
is likely slow and the sapply
is fast). by
continues to be relatively efficient--at 5 seconds, it's definitely noticeable to the user but for a dataset this large still not unreasonable. Still, if you're routinely working with datasets of this size, data.table
is clearly the way to go - 100% data.table for the best performance or dplyr
with dplyr
using data.table
as a viable alternative.
group by in group by and average
If you want to first take mean on the combination of ['cluster', 'org']
and then take mean on cluster
groups, you can use:
In [59]: (df.groupby(['cluster', 'org'], as_index=False).mean()
.groupby('cluster')['time'].mean())
Out[59]:
cluster
1 15
2 54
3 6
Name: time, dtype: int64
If you want the mean of cluster
groups only, then you can use:
In [58]: df.groupby(['cluster']).mean()
Out[58]:
time
cluster
1 12.333333
2 54.000000
3 6.000000
You can also use groupby
on ['cluster', 'org']
and then use mean()
:
In [57]: df.groupby(['cluster', 'org']).mean()
Out[57]:
time
cluster org
1 a 438886
c 23
2 d 9874
h 34
3 w 6
Mean per group and with count of variables in group
With dplyr
:
df $ Labels <- cut(df$Variable, breaks = c(0,5, 9))
df %>%
group_by(Labels) %>%
summarise(Mean = mean(Shap), N = n())
# A tibble: 2 x 3
Labels Mean N
<fct> <dbl> <int>
1 (0,5] 0.218 8
2 (5,9] 0.244 7
Data:
df <- structure(list(Variable = c(1L, 6L, 7L, 5L, 9L, 9L, 2L, 9L, 5L,
5L, 1L, 4L, 5L, 8L, 6L), Shap = c(0.1, 0.5, 0.3, 0.4, 0.1, 0.25,
0.24, 0.23, 0.22, 0.21, 0.2, 0.19, 0.18, 0.17, 0.16)), class = "data.frame", row.names = c(NA,
-15L))
Average by group for 'n' number of rows per group - R
Here is an option with tidyverse
. Assuming we have dynamic inputs ('n1', 'n2'), arrange
the data by 'Wells' and in desc
ending order of 'Date', group_by
the 'Well', use slice_head
to get the first (n1 + n2
) rows, then summarise
to create the 'Avg' mean
columns by taking the head
and tail
of 'Index' based on the 'n1' and 'n2' respectively
library(dplyr)
library(stringr)
n1 <- 3
n2 <- 6
df %>%
arrange(Wells, desc(Date)) %>%
group_by(Wells) %>%
slice_head(n = n1 + n2) %>%
summarise(!! str_c('Avg', n1) := mean(head(Index, n1)),
!! str_c('Avg', n2) := mean(tail(Index, n2)), .groups = 'drop')
-output
# A tibble: 2 x 3
# Wells Avg3 Avg6
# <chr> <dbl> <dbl>
#1 A 110 65
#2 B 220 130
Or using base R
df1 <- df[order(df$Wells, -as.numeric(df$Date)),]
out <- do.call(data.frame, aggregate(Index ~ Wells,
subset(df1, ave(seq_along(Wells),
Wells, FUN = seq_along) <= (n1 + n2)), FUN = function(x)
c(Avg3 = mean(head(x, n1)), Avg6 = mean(tail(x, n2)))))
How to calculate mean of all columns, by group?
Edit2: Recent version of dplyr
suggests using regular summarise
with across
function, as in:
library(dplyr)
mtcars %>%
group_by(cyl, gear) %>%
summarise(across(everything(), mean))
What you're looking for is either ?summarise_all
or ?summarise_each
from dplyr
Edit: full code:
library(dplyr)
mtcars %>%
group_by(cyl, gear) %>%
summarise_all("mean")
# Source: local data frame [8 x 11]
# Groups: cyl [?]
#
# cyl gear mpg disp hp drat wt qsec vs am carb
# <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
# 1 4 3 21.500 120.1000 97.0000 3.700000 2.465000 20.0100 1.0 0.00 1.000000
# 2 4 4 26.925 102.6250 76.0000 4.110000 2.378125 19.6125 1.0 0.75 1.500000
# 3 4 5 28.200 107.7000 102.0000 4.100000 1.826500 16.8000 0.5 1.00 2.000000
# 4 6 3 19.750 241.5000 107.5000 2.920000 3.337500 19.8300 1.0 0.00 1.000000
# 5 6 4 19.750 163.8000 116.5000 3.910000 3.093750 17.6700 0.5 0.50 4.000000
# 6 6 5 19.700 145.0000 175.0000 3.620000 2.770000 15.5000 0.0 1.00 6.000000
# 7 8 3 15.050 357.6167 194.1667 3.120833 4.104083 17.1425 0.0 0.00 3.083333
# 8 8 5 15.400 326.0000 299.5000 3.880000 3.370000 14.5500 0.0 1.00 6.000000
R Mean of multiple groups by quartiles
You can use output from ntile
as a group and get the average value for all the x
variables.
library(dplyr)
Quantile_Var <- Var %>%
group_by(Quartile = ntile(BE, 5)) %>%
summarise(across(starts_with('x'), mean, na.rm = TRUE))
Related Topics
How to Add Row and Column to a Dataframe of Different Length
Remove Space Between Plotted Data and the Axes
Convert Dataframe Column to 1 or 0 for "True"/"False" Values and Assign to Dataframe
Saving Output of Confusionmatrix as a .Csv Table
How to Sum a Variable by Group
Order Discrete X Scale by Frequency/Value
Unique Combination of All Elements from Two (Or More) Vectors
Plot a Legend Outside of the Plotting Area in Base Graphics
R: Rjava Package Install Failing
Storing Ggplot Objects in a List from Within Loop in R
If Else Statements to Check If a String Contains a Substring in R
How to Dplyr Rename a Column, by Column Index
Duplicating Rows in R Merge Function
How to Combine Multiple Variable Data to a Single Variable Data
How to Make a List of Data Frames
Split Data.Frame Based on Levels of a Factor into New Data.Frames