当前位置： 文江博客话题详情

如何按组对变量求和

发布于 2024-08-09 20:51:29 字数 386 浏览 4 评论 0原文

我有一个包含两列的数据框。第一列包含“第一”、“第二”、“第三”等类别，第二列的数字代表我看到“类别”中特定组的次数。

例如：

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 中执行此操作？

原文

I have a data frame with two columns. First column contains categories such as "First", "Second", "Third", and the second column has numbers that represent the number of times I saw the specific groups from "Category".

For example:

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

I want to sort the data by Category and sum all the Frequencies:

Category     Frequency
First        30
Second       5
Third        34

How would I do this in R?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（19）

七度光 2024-08-16 20:51:29

使用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))

Using aggregate:

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

In the example above, multiple dimensions can be specified in the list. Multiple aggregated metrics of the same data type can be incorporated via cbind:

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

(embedding @thelatemail comment), aggregate has a formula interface too

aggregate(Frequency ~ Category, x, sum)

Or if you want to aggregate multiple columns, you could use the . notation (works for one column too)

aggregate(. ~ Category, x, sum)

or tapply:

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

Using this data:

x <- data.frame(Category=factor(c("First", "First", "First", "Second",
                                      "Third", "Third", "Second")), 
                    Frequency=c(10,15,5,2,14,20,3))
回复 原文
盛夏尉蓝 2024-08-16 20:51:29

您还可以使用 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))

以下是如何通过以下方式汇总数据的更多示例使用内置数据集 mtcars 使用 dplyr 函数进行分组：

# 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 1.1.0 开始，您可以使用 .by 参数作为 group_by() 的替代。

mtcars %>% 
  summarise(max_hp = max(hp), mean_mpg = mean(mpg), .by = c(cyl, gear))

有关详细信息，包括 %>% 运算符，请参阅 dplyr 简介。

You can also use the dplyr package for that purpose:

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

Or, for multiple summary columns (works with one column too):

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

Here are some more examples of how to summarise data by group using dplyr functions using the built-in dataset 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)))

As of dplyr 1.1.0, you can use the .by argument as an alternative to group_by().

mtcars %>% 
  summarise(max_hp = max(hp), mean_mpg = mean(mpg), .by = c(cyl, gear))

For more information, including the %>% operator, see the introduction to dplyr.

回复 原文
苦妄 2024-08-16 20:51:29

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

The answer provided by rcs works and is simple. However, if you are handling larger datasets and need a performance boost there is a faster alternative:

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

Let's compare that to the same thing using data.frame and the above above:

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

And if you want to keep the column this is the syntax:

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

The difference will become more noticeable with larger datasets, as the code below demonstrates:

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

For multiple aggregations, you can combine lapply and .SD as follows

data[, lapply(.SD, sum), by = Category]
#    Category Frequency
# 1:    First        30
# 2:   Second         5
# 3:    Third        34
回复 原文
不甘平庸 2024-08-16 20:51:29

您还可以使用 by() 函数：

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

那些其他包（plyr、reshape）具有返回 data.frame 的优点，但值得熟悉 by()，因为它是一个基本函数。

You can also use the by() function:

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

Those other packages (plyr, reshape) have the benefit of returning a data.frame, but it's worth being familiar with by() since it's a base function.

回复 原文
明月夜 2024-08-16 20:51:29

几年后，只是为了添加另一个简单的基本 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

Several years later, just to add another simple base R solution that isn't present here for some reason- xtabs

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

Or if you want a data.frame back

as.data.frame(xtabs(Frequency ~ Category, df))
#   Category Freq
# 1    First   30
# 2   Second    5
# 3    Third   34
回复 原文
羞稚 2024-08-16 20:51:29
library(plyr)
ddply(tbl, .(Category), summarise, sum = sum(Frequency))

library(plyr)
ddply(tbl, .(Category), summarise, sum = sum(Frequency))
回复 原文
何其悲哀 2024-08-16 20:51:29

如果 x 是包含您的数据的数据框，那么以下将执行您想要的操作：

require(reshape)
recast(x, Category ~ ., fun.aggregate=sum)

If x is a dataframe with your data, then the following will do what you want:

require(reshape)
recast(x, Category ~ ., fun.aggregate=sum)
回复 原文
没企图 2024-08-16 20:51:29

虽然我最近已将大多数此类操作转换为 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

While I have recently become a convert to dplyr for most of these types of operations, the sqldf package is still really nice (and IMHO more readable) for some things.

Here is an example of how this question can be answered with 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
回复 原文
孤星 2024-08-16 20:51:29

只是添加第三个选项：

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

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

Just to add a third option:

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

EDIT: this is a very old answer. Now I would recommend the use of group_by and summarise from dplyr, as in @docendo answer.

回复 原文
z祗昰~ 2024-08-16 20:51:29

另一种解决方案按矩阵或数据帧中的组返回总和，并且简短而快速：

rowsum(x$Frequency, x$Category)

Another solution that returns sums by groups in a matrix or a data frame and is short and fast:

rowsum(x$Frequency, x$Category)
回复 原文
夜灵血窟げ 2024-08-16 20:51:29

我发现 ave< /a> 当您需要在不同列上应用不同的聚合函数（并且您必须/想要坚持使用基本 R）时非常有用（并且高效）：

例如，

给定此输入：

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

I find ave very helpful (and efficient) when you need to apply different aggregation functions on different columns (and you must/want to stick on base R) :

e.g.

Given this input :

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

we want to group by Categ1 and Categ2 and compute the sum of Samples and mean of Freq.
Here's a possible solution using 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),]

Result :

> 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
回复 原文
夏有森光若流苏 2024-08-16 20:51:29

从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

并使用选择助手选择变量：

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)

Since dplyr 1.0.0, the across() function could be used:

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

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

If interested in multiple variables:

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

And the selection of variables using select helpers:

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

Sample data:

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)
回复 原文
深居我梦 2024-08-16 20:51:29


You could use the function `group.sum` from **package *Rfast***.

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



***Rfast*** has many group functions and `group.sum` is one of them.

Rfast 已弃用组函数，并将其替换为名为 group 的新函数。使用参数 method 您可以选择正确的算法。因此，group.sum 是 group(...,method = "sum")。

    Category <- as.numeric(Category,result.sort=FALSE) #R has fixed the bug.
    result <- Rfast::group(Frequency,Category, method = "sum")
    names(result) <- Rfast::Sort(unique(Category)
    # 30 5 34


You could use the function `group.sum` from **package *Rfast***.

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



***Rfast*** has many group functions and `group.sum` is one of them.

Rfast has deprecated the group functions and replaced them with a new called group. Using argument method you can choose the correct algorithm. So, group.sum is group(...,method = "sum").

    Category <- as.numeric(Category,result.sort=FALSE) #R has fixed the bug.
    result <- Rfast::group(Frequency,Category, method = "sum")
    names(result) <- Rfast::Sort(unique(Category)
    # 30 5 34
回复 原文
空城之時有危險 2024-08-16 20:51:29

使用 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

using cast instead of recast (note 'Frequency' is now '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)

to get:

Category (all)
First     30
Second    5
Third     34
回复 原文
§对你不离不弃 2024-08-16 20:51:29
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)


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)
回复 原文
超可爱的懒熊 2024-08-16 20:51:29

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

rowsum(numericToBeSummedUp, groups)

来自base。这里只有 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 快两倍，比 快 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

A good way to sum a variable by group is

rowsum(numericToBeSummedUp, groups)

from base. Here only collapse::fsum and Rfast::group.sum have been faster.

Regarding speed and memory consumption

collapse::fsum(numericToBeSummedUp, groups)

was the best in the given example which could be speed up when using a grouped data frame.

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

Which comes close to the timings when the dataset was split in subdatasets per group.

A benchmark on different methods shows that for summing up a single column collapse::fsum was two times faster than Rfast::group.sum and 7 times faster than rowsum. They were followed by tapply, data.table, by and dplyr. xtabs and aggregate are the slowest.

Aggregating two columns collapse::fsum is again the fastest, 3 times faster than Rfast::group.sum and 5 times faster then rowsum. They are followed by data.table, tapply, by and dplyr. Again xtabs and aggregate are the slowest.

Benchmark

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

library(magrittr)

Some methods allow to do tasks which might help to speed up the aggregation.

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)

Summing up one column.

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

Summing up two columns

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
回复 原文
撞了怀 2024-08-16 20:51:29

您可以使用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

You can use rowsum function to calculate the frequency.

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

head looks as follows:

               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

then,

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

4   293.3
6   138.2
8   211.4
回复 原文
冷弦 2024-08-16 20:51:29

对于 dplyr 1.1.0 及更高版本，您可以在 summarise 中使用 .by。此快捷方式避免使用group_by并返回ungrouped数据框：

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

With dplyr 1.1.0 and above, you can use .by in summarise. This shortcut avoids to use group_by and returns an ungrouped data frame:

library(dplyr)
x %>%  
  summarise(Frequency = sum(Frequency), .by = Category)
回复 原文
南街九尾狐 2024-08-16 20:51:29

为了完整起见，我将使用 pivot_wider 和参数 values_fn=sum 添加另一个解决方案：

> pivot_wider(df, names_from=Category, values_from=Frequency, values_fn=sum)
# A tibble: 1 × 3
  First Second Third
  <dbl>  <dbl> <dbl>
1    30      5    34

如果数据集有两个分组变量，pivot_wider将创建一个漂亮的汇总表：

> pivot_wider(ToothGrowth, names_from=supp, values_from=len, values_fn=mean)
# A tibble: 3 × 3
   dose    VC    OJ
  <dbl> <dbl> <dbl>
1   0.5  7.98  13.2
2   1   16.8   22.7
3   2   26.1   26.1

For the sake of completeness, I will add another solution with pivot_wider with the argument values_fn=sum:

> pivot_wider(df, names_from=Category, values_from=Frequency, values_fn=sum)
# A tibble: 1 × 3
  First Second Third
  <dbl>  <dbl> <dbl>
1    30      5    34

If the dataset has two grouping variables, pivot_wider will create a nice summary table:

> pivot_wider(ToothGrowth, names_from=supp, values_from=len, values_fn=mean)
# A tibble: 3 × 3
   dose    VC    OJ
  <dbl> <dbl> <dbl>
1   0.5  7.98  13.2
2   1   16.8   22.7
3   2   26.1   26.1
回复 原文
~没有更多了~

关于作者

煞人兵器

暂无简介

0 文章
0 评论
21 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

qq_FjTq5B

文章 0 评论 0

18273202778

文章 0 评论 0

WordPress小学生

文章 0 评论 0

〃温暖了心ぐ

文章 0 评论 0

迷乱花海

文章 0 评论 0

niuniu

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文