By linp


2013-04-10 18:47:13 8 Comments

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

2 comments

@G. Grothendieck 2013-04-10 19:53:27

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

@A5C1D2H2I1M1N2O1R2T1 2013-04-10 18:54:36

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.

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:

> 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"

Related Questions

Sponsored Content

3 Answered Questions

2 Answered Questions

[SOLVED] Aggregating all unique values of each column of data frame

  • 2016-01-24 23:54:08
  • bab2155
  • 1936 View
  • 8 Score
  • 2 Answer
  • Tags:   r aggregate

6 Answered Questions

[SOLVED] How to use GROUP BY to concatenate strings in MySQL?

2 Answered Questions

1 Answered Questions

[SOLVED] data table string concatenation of SD columns for by group values

  • 2015-06-03 13:00:57
  • Sri
  • 3507 View
  • 4 Score
  • 1 Answer
  • Tags:   r data.table

3 Answered Questions

[SOLVED] In R, how do I average rows of a data frame over groups without renaming each column?

  • 2014-10-26 06:55:21
  • user1744318
  • 269 View
  • 1 Score
  • 3 Answer
  • Tags:   r

1 Answered Questions

[SOLVED] summarize all numeric columns of data frame by group in R

0 Answered Questions

Using ddply with multiple functions on lists of columns

  • 2014-04-10 07:18:55
  • ninjasnowman
  • 1466 View
  • 1 Score
  • 0 Answer
  • Tags:   r aggregate plyr

3 Answered Questions

Sponsored Content