我有一个有两列的数据帧。第一列包含类别，如“第一”，“第二”，“第三”，第二列有数字，表示我从“类别”中看到特定组的次数。
例如:
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中怎么做呢?
当前回答
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，但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
几年后，只是为了添加另一个简单的基本R解决方案，由于某种原因这里没有给出——xtabs
xtabs(Frequency ~ Category, df)
# Category
# First Second Third
# 30 5 34
或者你想要回数据帧
as.data.frame(xtabs(Frequency ~ Category, df))
# Category Freq
# 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介绍。
可以使用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
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)
