linp linp - 1 month ago 13
R Question

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.

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:

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).

Answer

plyr Try using toString :

# plyr
library(plyr)
ddply(data, .(A,B), summarize, C = toString(C))

Here are some additional alternatives also using toString:

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

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 A dplyr alternative:

library(dplyr)
data %>%
  group_by(A, B) %>%
  summarise(test = toString(C)) %>%
  ungroup()