I want to aggregate one column in a data frame according to two grouping variables, and separate the individual values by a comma.
我想根据两个分组变量在数据框架中聚合一列,并用逗号分隔各个值。
Here is some data:
这里有一些数据:
data <- data.frame(A = c(rep(111, 3), rep(222, 3)), B = rep(1:2, 3), C = c(5:10))
data
# A B C
# 1 111 1 5
# 2 111 2 6
# 3 111 1 7
# 4 222 2 8
# 5 222 1 9
# 6 222 2 10
"A" and "B" are grouping variables, and "C" is the variable that I want to collapse into a comma separated character
string. I have tried:
“A”和“B”是分组变量,“C”是我要折叠成逗号分隔的字符串的变量。我有尝试:
library(plyr)
ddply(data, .(A,B), summarise, test = list(C))
A B test
1 111 1 5, 7
2 111 2 6
3 222 1 9
4 222 2 8, 10
but when I tried to convert test column to character
it becomes like this:
但当我试图将测试列转换为字符时,结果是这样的:
ddply(data, .(A,B), summarise, test = as.character(list(C)))
# A B test
# 1 111 1 c(5, 7)
# 2 111 2 6
# 3 222 1 9
# 4 222 2 c(8, 10)
How can I keep the character
format and separate them by a comma? For example, row 1 should be only "5,7"
, and not as c(5,7).
如何保持字符格式并使用逗号分隔?例如,第1行应该只有“5,7”,而不是c(5,7)。
2 个解决方案
#1
35
plyr Try using toString
:
尝试使用toString:
# plyr
library(plyr)
ddply(data, .(A,B), summarize, C = toString(C))
Here are some additional alternatives also using toString
:
下面是一些使用toString的其他选项:
data.table
data.table
# alternative using data.table
library(data.table)
as.data.table(data)[, toString(C), by = list(A, B)]
aggregate This uses no packages:
合计这个使用没有包:
# alternative using aggregate from the stats package in the core of R
aggregate(C ~., data, toString)
sqldf
sqldf
And here is an alternative using the SQL function group_concat
using the sqldf package :
下面是使用sqldf包的SQL函数group_concat的一种替代方法:
library(sqldf)
sqldf("select A, B, group_concat(C) C from data group by A, B", method = "raw")
dplyr A dplyr
alternative:
dplyr dplyr替代:
library(dplyr)
data %>%
group_by(A, B) %>%
summarise(test = toString(C)) %>%
ungroup()
#2
10
Change where you put as.character
:
改变你放置的位置。
> out <- ddply(data, .(A, B), summarise, test = list(as.character(C)))
> str(out)
'data.frame': 4 obs. of 3 variables:
$ A : num 111 111 222 222
$ B : int 1 2 1 2
$ test:List of 4
..$ : chr "5" "7"
..$ : chr "6"
..$ : chr "9"
..$ : chr "8" "10"
> out
A B test
1 111 1 5, 7
2 111 2 6
3 222 1 9
4 222 2 8, 10
Note, however, that each item is still actually a separate character, not a single character string. That is, this is not an actual string that looks like "5, 7", but rather, two characters, "5" and "7", which R displays with a comma between them.
但是,请注意,每个条目实际上仍然是一个单独的字符,而不是单个字符串。也就是说,这不是一个实际的字符串,看起来像“5,7”,而是两个字符,“5”和“7”,在它们之间有一个逗号。
Compare with the following:
与以下:
> out2 <- ddply(data, .(A, B), summarise, test = paste(C, collapse = ", "))
> str(out2)
'data.frame': 4 obs. of 3 variables:
$ A : num 111 111 222 222
$ B : int 1 2 1 2
$ test: chr "5, 7" "6" "9" "8, 10"
> out
A B test
1 111 1 5, 7
2 111 2 6
3 222 1 9
4 222 2 8, 10
The comparable solution in base R is, of course, aggregate
:
以R为基础的可比较的解,当然是集合:
> A1 <- aggregate(C ~ A + B, data, function(x) c(as.character(x)))
> str(A1)
'data.frame': 4 obs. of 3 variables:
$ A: num 111 222 111 222
$ B: int 1 1 2 2
$ C:List of 4
..$ 0: chr "5" "7"
..$ 1: chr "9"
..$ 2: chr "6"
..$ 3: chr "8" "10"
> A2 <- aggregate(C ~ A + B, data, paste, collapse = ", ")
> str(A2)
'data.frame': 4 obs. of 3 variables:
$ A: num 111 222 111 222
$ B: int 1 1 2 2
$ C: chr "5, 7" "9" "6" "8, 10"
#1
35
plyr Try using toString
:
尝试使用toString:
# plyr
library(plyr)
ddply(data, .(A,B), summarize, C = toString(C))
Here are some additional alternatives also using toString
:
下面是一些使用toString的其他选项:
data.table
data.table
# alternative using data.table
library(data.table)
as.data.table(data)[, toString(C), by = list(A, B)]
aggregate This uses no packages:
合计这个使用没有包:
# alternative using aggregate from the stats package in the core of R
aggregate(C ~., data, toString)
sqldf
sqldf
And here is an alternative using the SQL function group_concat
using the sqldf package :
下面是使用sqldf包的SQL函数group_concat的一种替代方法:
library(sqldf)
sqldf("select A, B, group_concat(C) C from data group by A, B", method = "raw")
dplyr A dplyr
alternative:
dplyr dplyr替代:
library(dplyr)
data %>%
group_by(A, B) %>%
summarise(test = toString(C)) %>%
ungroup()
#2
10
Change where you put as.character
:
改变你放置的位置。
> out <- ddply(data, .(A, B), summarise, test = list(as.character(C)))
> str(out)
'data.frame': 4 obs. of 3 variables:
$ A : num 111 111 222 222
$ B : int 1 2 1 2
$ test:List of 4
..$ : chr "5" "7"
..$ : chr "6"
..$ : chr "9"
..$ : chr "8" "10"
> out
A B test
1 111 1 5, 7
2 111 2 6
3 222 1 9
4 222 2 8, 10
Note, however, that each item is still actually a separate character, not a single character string. That is, this is not an actual string that looks like "5, 7", but rather, two characters, "5" and "7", which R displays with a comma between them.
但是,请注意,每个条目实际上仍然是一个单独的字符,而不是单个字符串。也就是说,这不是一个实际的字符串,看起来像“5,7”,而是两个字符,“5”和“7”,在它们之间有一个逗号。
Compare with the following:
与以下:
> out2 <- ddply(data, .(A, B), summarise, test = paste(C, collapse = ", "))
> str(out2)
'data.frame': 4 obs. of 3 variables:
$ A : num 111 111 222 222
$ B : int 1 2 1 2
$ test: chr "5, 7" "6" "9" "8, 10"
> out
A B test
1 111 1 5, 7
2 111 2 6
3 222 1 9
4 222 2 8, 10
The comparable solution in base R is, of course, aggregate
:
以R为基础的可比较的解,当然是集合:
> A1 <- aggregate(C ~ A + B, data, function(x) c(as.character(x)))
> str(A1)
'data.frame': 4 obs. of 3 variables:
$ A: num 111 222 111 222
$ B: int 1 1 2 2
$ C:List of 4
..$ 0: chr "5" "7"
..$ 1: chr "9"
..$ 2: chr "6"
..$ 3: chr "8" "10"
> A2 <- aggregate(C ~ A + B, data, paste, collapse = ", ")
> str(A2)
'data.frame': 4 obs. of 3 variables:
$ A: num 111 222 111 222
$ B: int 1 1 2 2
$ C: chr "5, 7" "9" "6" "8, 10"