Tabulating list of values in third variable in R

Question

I have following data:

ddf2 = structure(list(col1 = c(3, 3, 2, 1, 1, 1, 3, 2, 1, 1, 3, 1, 1, 
2, 1, 1, 1, 2, 3, 1, 1, 3, 2, 3, 3), col2 = c("c", "c", "b", 
"b", "b", "a", "b", "c", "b", "b", "c", "c", "b", "b", "a", "c", 
"c", "b", "a", "b", "b", "c", "a", "c", "a"), col3 = c("C", "E", 
"E", "B", "D", "E", "C", "C", "E", "E", "C", "A", "D", "D", "C", 
"E", "A", "A", "A", "D", "A", "A", "B", "A", "E")), .Names = c("col1", 
"col2", "col3"), row.names = c(NA, 25L), class = "data.frame")

head(ddf2)
  col1 col2 col3
1    3    c    C
2    3    c    E
3    2    b    E
4    1    b    B
5    1    b    D
6    1    a    E

For every combination of col1 and col2, there may be many values of col3:

with(ddf2, ddf2[col1==1 & col2=='b',])
   col1 col2 col3
4     1    b    B
5     1    b    D
9     1    b    E
10    1    b    E
13    1    b    D
20    1    b    D
21    1    b    A


with(ddf2, table(col1, col2))
    col2
col1 a b c
   1 2 7 3
   2 1 3 1
   3 2 1 5

I want to create a table/matrix of col1 and col2 as above but each cell should have a list of unique col3 entries for that set of col1 and col2. I expect following output:

    col2
col1    a       b           c
1       E,C     A,B,D,E     A,E
2       B       A,D,E       C
3       A,E     C           A,C,E

I tried following but it does not work:

with(ddf2, tapply(col3, list(col1,col2), c))
  a           b           c          
1 Character,2 Character,7 Character,3
2 "B"         Character,3 "C"        
3 Character,2 "C"         Character,5

How can this be done? Thanks for your help.

talat talat · Accepted Answer · 2014-08-20T11:32:56

One option:

d <- with(ddf2, aggregate(col3 ~ col2 + col1, FUN = function(x) paste0(unique(x))))
library(reshape2)
dcast(d, col1 ~ col2, value.var = "col3")
#  col1    a          b       c
#1    1 E, C B, D, E, A    A, E
#2    2    B    E, D, A       C
#3    3 A, E          C C, E, A

Most likely it's possible to do both steps in one, but I'll generously leave it to someone else to figure this out ;)

Or

library(dplyr)
library(tidyr)

ddf2 %>%
  group_by(col1, col2) %>%
  summarise(col3 = paste(unique(col3), collapse = ", ")) %>%
  spread(col2, col3)

#Source: local data frame [3 x 4]
#
#  col1    a          b       c
#1    1 E, C B, D, E, A    A, E
#2    2    B    E, D, A       C
#3    3 A, E          C C, E, A

Edit after comment:

Just tested with tapply and this seems to work (the problem was apparently in calling c()):

with(ddf2, tapply(col3, list(col1,col2), FUN = function(x) paste(unique(x), collapse = ", ")))
#  a      b            c        
#1 "E, C" "B, D, E, A" "A, E"   
#2 "B"    "E, D, A"    "C"      
#3 "A, E" "C"          "C, E, A"

Tabulating list of values in third variable in R

1 Answers