如何按组和一个变量

我有一个有两列的数据帧。第一列包含类别，如“第一”，“第二”，“第三”，第二列有数字，表示我从“类别”中看到特定组的次数。

例如:

Category     Frequency
First        10
First        15
First        5
Second       2
Third        14
Third        20
Second       3

我想按类别对数据进行排序，并将所有频率相加:

Category     Frequency
First        30
Second       5
Third        34

在R中怎么做呢?

902942 次浏览
小开

如果x是一个包含你的数据的数据框架，那么下面的语句将完成你想要的:

require(reshape)
recast(x, Category ~ ., fun.aggregate=sum)
小开 
library(plyr)
ddply(tbl, .(Category), summarise, sum = sum(Frequency))
小开

再加上第三个选项:

require(doBy)
summaryBy(Frequency~Category, data=yourdataframe, FUN=sum)

编辑:这是一个非常古老的答案。现在我建议使用group_by和summarise from dplyr，如在@docendo answer中。

小开
最佳答案

使用aggregate:

aggregate(x$Frequency, by=list(Category=x$Category), FUN=sum)
Category  x
1    First 30
2   Second  5
3    Third 34

在上面的例子中，可以在list中指定多个维度。同一数据类型的多个聚合指标可以通过cbind合并:

aggregate(cbind(x$Frequency, x$Metric2, x$Metric3) ...

(嵌入@thelatemail注释)，aggregate也有一个公式接口

aggregate(Frequency ~ Category, x, sum)

或者如果你想聚合多个列，你可以使用.符号(也适用于一列)

aggregate(. ~ Category, x, sum)

或tapply:

tapply(x$Frequency, x$Category, FUN=sum)
First Second  Third
30      5     34

使用这些数据:

x <- data.frame(Category=factor(c("First", "First", "First", "Second",
"Third", "Third", "Second")),
Frequency=c(10,15,5,2,14,20,3))
小开

你也可以使用由()函数:

x2 <- by(x$Frequency, x$Category, sum)
do.call(rbind,as.list(x2))

其他那些包(plyr，重塑)的好处是返回data.frame，但是by()值得熟悉一下，因为它是一个基函数。

小开

rcs提供的答案很有效，也很简单。然而，如果你正在处理更大的数据集，需要性能提升，有一个更快的替代方案:

library(data.table)
data = data.table(Category=c("First","First","First","Second","Third", "Third", "Second"),
Frequency=c(10,15,5,2,14,20,3))
data[, sum(Frequency), by = Category]
#    Category V1
# 1:    First 30
# 2:   Second  5
# 3:    Third 34
system.time(data[, sum(Frequency), by = Category] )
# user    system   elapsed
# 0.008     0.001     0.009

让我们用data.frame和上面的比较一下:

data = data.frame(Category=c("First","First","First","Second","Third", "Third", "Second"),
Frequency=c(10,15,5,2,14,20,3))
system.time(aggregate(data$Frequency, by=list(Category=data$Category), FUN=sum))
# user    system   elapsed
# 0.008     0.000     0.015

如果你想保留列，这是语法:

data[,list(Frequency=sum(Frequency)),by=Category]
#    Category Frequency
# 1:    First        30
# 2:   Second         5
# 3:    Third        34

数据集越大，这种差异就越明显，如下图所示:

data = data.table(Category=rep(c("First", "Second", "Third"), 100000),
Frequency=rnorm(100000))
system.time( data[,sum(Frequency),by=Category] )
# user    system   elapsed
# 0.055     0.004     0.059
data = data.frame(Category=rep(c("First", "Second", "Third"), 100000),
Frequency=rnorm(100000))
system.time( aggregate(data$Frequency, by=list(Category=data$Category), FUN=sum) )
# user    system   elapsed
# 0.287     0.010     0.296

对于多个聚合，可以按照如下方式组合lapply和.SD

data[, lapply(.SD, sum), by = Category]
#    Category Frequency
# 1:    First        30
# 2:   Second         5
# 3:    Third        34
小开

你也可以使用dplyr包来实现这个目的:

library(dplyr)
x %>%
group_by(Category) %>%
summarise(Frequency = sum(Frequency))


#Source: local data frame [3 x 2]
#
#  Category Frequency
#1    First        30
#2   Second         5
#3    Third        34

或者，对于多个摘要列(也适用于一列):

x %>%
group_by(Category) %>%
summarise(across(everything(), sum))

下面是一些关于如何使用dplyr函数(使用内置数据集mtcars)分组总结数据的更多示例:

# several summary columns with arbitrary names
mtcars %>%
group_by(cyl, gear) %>%                            # multiple group columns
summarise(max_hp = max(hp), mean_mpg = mean(mpg))  # multiple summary columns


# summarise all columns except grouping columns using "sum"
mtcars %>%
group_by(cyl) %>%
summarise(across(everything(), sum))


# summarise all columns except grouping columns using "sum" and "mean"
mtcars %>%
group_by(cyl) %>%
summarise(across(everything(), list(mean = mean, sum = sum)))


# multiple grouping columns
mtcars %>%
group_by(cyl, gear) %>%
summarise(across(everything(), list(mean = mean, sum = sum)))


# summarise specific variables, not all
mtcars %>%
group_by(cyl, gear) %>%
summarise(across(c(qsec, mpg, wt), list(mean = mean, sum = sum)))


# summarise specific variables (numeric columns except grouping columns)
mtcars %>%
group_by(gear) %>%
summarise(across(where(is.numeric), list(mean = mean, sum = sum)))

有关更多信息，包括%>%操作符，请参见dplyr简介. c。

小开

几年后，为了添加另一个简单的基本R解，由于某种原因这里没有给出——xtabs

xtabs(Frequency ~ Category, df)
# Category
# First Second  Third
#    30      5     34

或者如果你想要返回data.frame

as.data.frame(xtabs(Frequency ~ Category, df))
#   Category Freq
# 1    First   30
# 2   Second    5
# 3    Third   34
小开

虽然我最近对大多数这些类型的操作都转换为dplyr，但sqldf包对于某些事情仍然非常好(恕我直言，可读性更强)。

下面是一个如何用sqldf回答这个问题的例子

x <- data.frame(Category=factor(c("First", "First", "First", "Second",
"Third", "Third", "Second")),
Frequency=c(10,15,5,2,14,20,3))


sqldf("select
Category
,sum(Frequency) as Frequency
from x
group by
Category")


##   Category Frequency
## 1    First        30
## 2   Second         5
## 3    Third        34
小开

使用cast代替recast(注意'Frequency'现在是'value')

df  <- data.frame(Category = c("First","First","First","Second","Third","Third","Second")
, value = c(10,15,5,2,14,20,3))


install.packages("reshape")


result<-cast(df, Category ~ . ,fun.aggregate=sum)

得到:

Category (all)
First     30
Second    5
Third     34
小开

你可以使用包< em > Rfast < / em >中的group.sum函数。

Category <- Rfast::as_integer(Category,result.sort=FALSE) # convert character to numeric. R's as.numeric produce NAs.
result <- Rfast::group.sum(Frequency,Category)
names(result) <- Rfast::Sort(unique(Category)
# 30 5 34

< em > Rfast < / em >有许多组函数，group.sum是其中之一。

小开

当你需要在不同的列上应用不同的聚合函数时(并且你必须/想要坚持基于R)，我发现ave非常有用(并且有效):

如。

假设输入如下:

DF <-
data.frame(Categ1=factor(c('A','A','B','B','A','B','A')),
Categ2=factor(c('X','Y','X','X','X','Y','Y')),
Samples=c(1,2,4,3,5,6,7),
Freq=c(10,30,45,55,80,65,50))


> DF
Categ1 Categ2 Samples Freq
1      A      X       1   10
2      A      Y       2   30
3      B      X       4   45
4      B      X       3   55
5      A      X       5   80
6      B      Y       6   65
7      A      Y       7   50

我们想通过Categ1和Categ2进行分组，并计算Samples和Freq的平均值 下面是一个可能的解决方案，使用ave:

# create a copy of DF (only the grouping columns)
DF2 <- DF[,c('Categ1','Categ2')]


# add sum of Samples by Categ1,Categ2 to DF2
# (ave repeats the sum of the group for each row in the same group)
DF2$GroupTotSamples <- ave(DF$Samples,DF2,FUN=sum)


# add mean of Freq by Categ1,Categ2 to DF2
# (ave repeats the mean of the group for each row in the same group)
DF2$GroupAvgFreq <- ave(DF$Freq,DF2,FUN=mean)


# remove the duplicates (keep only one row for each group)
DF2 <- DF2[!duplicated(DF2),]

结果:

> DF2
Categ1 Categ2 GroupTotSamples GroupAvgFreq
1      A      X               6           45
2      A      Y               9           40
3      B      X               7           50
6      B      Y               6           65
小开

另一种解决方案是在矩阵或数据帧中按组返回和，并且简短快速:

rowsum(x$Frequency, x$Category)
小开

由于dplyr 1.0.0，可以使用across()函数:

df %>%
group_by(Category) %>%
summarise(across(Frequency, sum))


Category Frequency
<chr>        <int>
1 First           30
2 Second           5
3 Third           34

如果对多个变量感兴趣:

df %>%
group_by(Category) %>%
summarise(across(c(Frequency, Frequency2), sum))


Category Frequency Frequency2
<chr>        <int>      <int>
1 First           30         55
2 Second           5         29
3 Third           34        190

以及使用select helper来选择变量:

df %>%
group_by(Category) %>%
summarise(across(starts_with("Freq"), sum))


Category Frequency Frequency2 Frequency3
<chr>        <int>      <int>      <dbl>
1 First           30         55        110
2 Second           5         29         58
3 Third           34        190        380

样本数据:

df <- read.table(text = "Category Frequency Frequency2 Frequency3
1    First        10         10         20
2    First        15         30         60
3    First         5         15         30
4   Second         2          8         16
5    Third        14         70        140
6    Third        20        120        240
7   Second         3         21         42",
header = TRUE,
stringsAsFactors = FALSE)
小开 
library(tidyverse)


x <- data.frame(Category= c('First', 'First', 'First', 'Second', 'Third', 'Third', 'Second'),
Frequency = c(10, 15, 5, 2, 14, 20, 3))


count(x, Category, wt = Frequency)
小开

按组对变量求和的一个好方法是

rowsum(numericToBeSummedUp, groups)

从基地。这里只有collapse::fsum和Rfast::group.sum更快。

关于速度和内存消耗

collapse::fsum(numericToBeSummedUp, groups)

在给定的例子中是最好的，当使用分组数据帧时可以加速。

GDF <- collapse::fgroup_by(DF, g) #Create a grouped data.frame with group g
#GDF <- collapse::gby(DF, g)      #Alternative


collapse::fsum(GDF)               #Calculate sum per group

这接近于将数据集分成每组子数据集的时间。

不同方法的基准测试表明，对于单个列的求和，collapse::fsum比Rfast::group.sum快两倍，比rowsum快7倍。其次是tapply， data.table， by和dplyr。xtabs和aggregate最慢。

聚合两列collapse::fsum同样是最快的，比Rfast::group.sum快3倍，比rowsum快5倍。它们后面是data.table， tapply， by和dplyr。同样，xtabs和aggregate是最慢的。

基准

set.seed(42)
n <- 1e5
DF <- data.frame(g = as.factor(sample(letters, n, TRUE))
, x = rnorm(n), y = rnorm(n) )


library(magrittr)

有些方法允许执行有助于加快聚合速度的任务。

DT <- data.table::as.data.table(DF)
data.table::setkey(DT, g)


DFG <- collapse::gby(DF, g)
DFG1 <- collapse::gby(DF[c("g", "x")], g)


# Optimized dataset for this aggregation task
# This will also consume time!
DFS <- lapply(split(DF[c("x", "y")], DF["g"]), as.matrix)
DFS1 <- lapply(split(DF["x"], DF["g"]), as.matrix)

对一列求和。

bench::mark(check = FALSE
, "aggregate" = aggregate(DF$x, DF["g"], sum)
, "tapply" = tapply(DF$x, DF$g, sum)
, "dplyr" = DF %>% dplyr::group_by(g) %>% dplyr::summarise(sum = sum(x))
, "data.table" = data.table::as.data.table(DF)[, sum(x), by = g]
, "data.table2" = DT[, sum(x), by = g]
, "by" = by(DF$x, DF$g, sum)
, "xtabs" = xtabs(x ~ g, DF)
, "rowsum" = rowsum(DF$x, DF$g)
, "Rfast" = Rfast::group.sum(DF$x, DF$g)
, "base Split" = lapply(DFS1, colSums)
, "base Split Rfast" = lapply(DFS1, Rfast::colsums)
, "collapse"  = collapse::fsum(DF$x, DF$g)
, "collapse2"  = collapse::fsum(DFG1)
)
#   expression            min   median `itr/sec` mem_alloc `gc/sec` n_itr  n_gc
#   <bch:expr>       <bch:tm> <bch:tm>     <dbl> <bch:byt>    <dbl> <int> <dbl>
# 1 aggregate         20.43ms  21.88ms      45.7   16.07MB    59.4     10    13
# 2 tapply             1.24ms   1.39ms     687.     1.53MB    30.1    228    10
# 3 dplyr              3.28ms   4.81ms     209.     2.42MB    13.1     96     6
# 4 data.table         1.59ms   2.47ms     410.     4.69MB    87.7    145    31
# 5 data.table2        1.52ms   1.93ms     514.     2.38MB    40.5    190    15
# 6 by                 2.15ms   2.31ms     396.     2.29MB    26.7    148    10
# 7 xtabs              7.78ms   8.91ms     111.    10.54MB    50.0     31    14
# 8 rowsum           951.36µs   1.07ms     830.     1.15MB    24.1    378    11
# 9 Rfast            431.06µs 434.53µs    2268.     2.74KB     0     1134     0
#10 base Split       213.42µs 219.66µs    4342.       256B    12.4   2105     6
#11 base Split Rfast  76.88µs  81.48µs   10923.    65.05KB    16.7   5232     8
#12 collapse         121.03µs 122.92µs    7965.       256B     2.01  3961     1
#13 collapse2         85.97µs  88.67µs   10749.       256B     4.03  5328     2

两列相加

bench::mark(check = FALSE
, "aggregate" = aggregate(DF[c("x", "y")], DF["g"], sum)
, "tapply" = list2DF(lapply(DF[c("x", "y")], tapply, list(DF$g), sum))
, "dplyr" = DF %>% dplyr::group_by(g) %>% dplyr::summarise(x = sum(x), y = sum(y))
, "data.table" = data.table::as.data.table(DF)[,.(sum(x),sum(y)), by = g]
, "data.table2" = DT[,.(sum(x),sum(y)), by = g]
, "by" = lapply(DF[c("x", "y")], by, list(DF$g), sum)
, "xtabs" = xtabs(cbind(x, y) ~ g, DF)
, "rowsum" = rowsum(DF[c("x", "y")], DF$g)
, "Rfast" = list2DF(lapply(DF[c("x", "y")], Rfast::group.sum, DF$g))
, "base Split" = lapply(DFS, colSums)
, "base Split Rfast" = lapply(DFS, Rfast::colsums)
, "collapse" = collapse::fsum(DF[c("x", "y")], DF$g)
, "collapse2" = collapse::fsum(DFG)
)
#   expression            min   median `itr/sec` mem_alloc `gc/sec` n_itr  n_gc
#   <bch:expr>       <bch:tm> <bch:tm>     <dbl> <bch:byt>    <dbl> <int> <dbl>
# 1 aggregate         25.87ms  26.36ms      37.7   20.89MB   132.       4    14
# 2 tapply             2.65ms   3.23ms     312.     3.06MB    22.5     97     7
# 3 dplyr              4.27ms   6.02ms     164.     3.19MB    13.3     74     6
# 4 data.table         2.33ms   3.19ms     309.     4.72MB    57.0    114    21
# 5 data.table2        2.22ms   2.81ms     355.     2.41MB    19.8    161     9
# 6 by                 4.45ms   5.23ms     190.     4.59MB    22.5     59     7
# 7 xtabs             10.71ms  13.14ms      76.1    19.7MB   145.      11    21
# 8 rowsum             1.02ms   1.07ms     850.     1.15MB    23.8    393    11
# 9 Rfast            841.57µs 846.88µs    1150.     5.48KB     0      575     0
#10 base Split       360.24µs 368.28µs    2652.       256B     8.16  1300     4
#11 base Split Rfast 113.95µs 119.81µs    7540.    65.05KB    10.3   3661     5
#12 collapse         201.31µs 204.83µs    4724.       512B     2.01  2350     1
#13 collapse2        156.95µs 161.79µs    5408.       512B     2.02  2683     1
小开

你可以使用rowsum函数来计算频率。

data("mtcars")
df <- mtcars
df$cyl <- as.factor(df$cyl)

头部看起来如下:

               wt    mpg    cyl
<dbl> <dbl>   <fct>
Mazda RX4     2.620  21.0   6
Mazda RX4 Wag 2.875  21.0   6
Datsun 710    2.320  22.8   4

然后,

rowsum(df$mpg, df$cyl) #values , group


4   293.3
6   138.2
8   211.4
小开

对于dplyr 1.1.0及以上版本，你可以在summarise中使用.by。此快捷方式避免使用group_by并返回ungrouped数据帧:

library(dplyr)
x %>%
summarise(Frequency = sum(Frequency), .by = Category)

提交答案