折叠/连接/聚合一列为每个组中单个逗号分隔的字符串 [英] Collapse / concatenate / aggregate a column to a single comma separated string within each group
问题描述
我想根据两个分组变量在数据框中汇总一列,并用逗号分隔各个值。
I want to aggregate one column in a data frame according to two grouping variables, and separate the individual values by a comma.
以下是一些数据:
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和 B是分组变量,而 C是我要折叠成逗号分隔的字符$的变量。 c $ c>字符串。我试过了:
"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:
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
,但是当我尝试将测试列转换为个字符$时c $ c>变成这样:
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)
如何保持字符
格式并用逗号分隔?例如,第1行应仅为 5,7
,而不应为c(5,7)。
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).
推荐答案
以下是使用 toString
的一些选项,该函数使用逗号和空格将字符串向量连接起来,以分隔各个组成部分。如果您不想使用逗号,则可以将 paste()
与 collapse
参数一起使用。
Here are some options using toString
, a function that concatenates a vector of strings using comma and space to separate components. If you don't want commas, you can use paste()
with the collapse
argument instead.
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
这是使用SQL函数 group_concat的替代方法
使用 sqldf软件包:
And here is an alternative using the SQL function group_concat
using the sqldf package :
library(sqldf)
sqldf("select A, B, group_concat(C) C from data group by A, B", method = "raw")
dplyr 另一种 dplyr
:
library(dplyr)
data %>%
group_by(A, B) %>%
summarise(test = toString(C)) %>%
ungroup()
plyr
# plyr
library(plyr)
ddply(data, .(A,B), summarize, C = toString(C))
这篇关于折叠/连接/聚合一列为每个组中单个逗号分隔的字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!