折叠/连接/聚合一列到每个组中的单个逗号分隔字符串 [英] 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"是我想折叠成逗号分隔的character字符串的变量.我试过了:

"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

但是当我尝试将测试列转换为 character 时，它变成了这样:

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)

如何保持 character 格式并用逗号分隔它们?例如，第 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)]

聚合这不使用任何包:

# alternative using aggregate from the stats package in the core of R
aggregate(C ~., data, toString)

sqldf

这里是使用 sqldf 包的 SQL 函数 group_concat 的替代方法:

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屋！