A very long winded title for what I thought should be short and easy to do and actually was - but only after a couple of hours of early morning brain warm-up though.

Say you have some data like so:

somedata <- data.table(read.table(header=TRUE, text="
	 id     coltomerge
	a1112      red
	a1112      green
	a1112      blue
	a1113      red
	a1113      red
	a1114      green
	a1115      yellow
	a1115      pink
	a1115      pink
"
))

and you want to group together all the “coltomerge” entries for each id into one cell. You can use paste to concatenate the data:

somedata[,list("mergedcol"=paste(coltomerge, collapse=", ")), by=id]

I did say it was short and easy (I’m writing this mostly as a note to myself. I’m not under any illusion I’m sharing some complicated technique, but on the off chance someone else is having a slow day like I was…). Which gets you:

        id          mergedcol
[1,] a1112   red, green, blue
[2,] a1113           red, red
[3,] a1114              green
[4,] a1115 yellow, pink, pink

If you don’t want duplicates (I didn’t) it’s easiest to remove them first:

#For this example, setkey again otherwise unique will only consider the id column
setkey(somedata, id, coltomerge) 
somedata <- unique(somedata)
somedata[,list("mergedcol"=paste(coltomerge, collapse=", ")), by=id]

Ta da!

        id        mergedcol
[1,] a1112 blue, green, red
[2,] a1113              red
[3,] a1114            green
[4,] a1115     pink, yellow