Multiple Values in One Cell using R

Question

Suppose, there are 2 data.frames, for instance:

dat1 <- read.table("[path_dat1]", header=TRUE, sep=",")
    id   name  age
     1   Jack   21
     2  James   40

dat2 <- read.table("[path_dat2]", header=TRUE, sep=",")
    id      interests
     1       football
     1     basketball
     1         soccer
     2  pingpang ball

How do I join table 1 and table 2 into a data.frame like the one below?

  id   name age                       interests
1  1   Jack  21  (football, basketball, soccer)
2  2  James  40                 (pingpang ball)

How can I join these using plyr in the simplest way?

Please include short examples that can be actually loaded into R and examined. — Matthew Lundberg, Dec 10 '12 at 05:21
@Jack Fu Welcome to SO. I added what Matthew asked for with an additional soccer case to ensure it handles more than two instances. In future posts please provide some sort of reproducible example as I've done. This makes providing assistance easier. Here's a link that discusses what a good reproducible example is: http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Tyler Rinker, Dec 10 '12 at 05:37

Tyler Rinker · Accepted Answer · 2012-12-10T05:51:36.240

3

I can't tell you how to solve this in plyr but can in base:

dat3 <- aggregate(interests~id, dat2, paste, collapse=",")
merge(dat1, dat3, "id")

EDIT: If you really want the parenthesis you could use:

ppaste <- function(x) paste0("(", gsub("^\\s+|\\s+$", "", paste(x, collapse = ",")), ")")
dat3 <- aggregate(interests~id, dat2, ppaste)
merge(dat1, dat3, "id")

edited Dec 10 '12 at 05:51

answered Dec 10 '12 at 05:35

Tyler Rinker

108,132
65
322
519

score 1 · Answer 2 · answered Dec 10 '12 at 07:09

1

Using Tyler's example:

dat1$interests <- ave(dat1$id, dat1$id, 
        FUN=function(x) paste(dat2[ dat2$id %in% x, "interests"], collapse=",") )
> dat1
  id   name age                     interests
1  1   Jack  21  football, basketball, soccer
2  2  James  40                 pingpang ball

answered Dec 10 '12 at 07:09

IRTFM

258,963
21
364
487

Thank you. Your answer is also concise and inspiring. – Jack Fu Dec 11 '12 at 17:46

Multiple Values in One Cell using R

2 Answers2