Categories
aggregate r r-faq

Collapse / concatenate / aggregate a column to a single comma separated string within each group

100

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

0

    110

    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

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

    plyr

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

    1

    • 3

      To keep unique values only: as.data.table(data)[, toString(unique(C)), by = list(A, B)]

      – ddunn801

      Aug 17, 2020 at 19:26


    34

    Here’s the stringr/tidyverse solution:

    library(tidyverse)
    library(stringr)
    
    data <- data.frame(A = c(rep(111, 3), rep(222, 3)), B = rep(1:2, 3), C = c(5:10))
    
    
    data %>%
     group_by(A, B) %>%
     summarize(text = str_c(C, collapse = ", "))
    
    # A tibble: 4 x 3
    # Groups:   A [2]
          A     B text 
      <dbl> <int> <chr>
    1   111     1 5, 7 
    2   111     2 6    
    3   222     1 9    
    4   222     2 8, 10
    

    1

    • 4

      One can also substitute stringr::str_c for paste from base R.

      Jun 1, 2019 at 6:25

    15

    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 in this case 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"
    

    0