title: Merging/Collapsing data.table rows based on common strings
body: <p>This is a followup to my previous question <a href="https://stackoverflow.com/questions/23387917/merging-vectors-of-strings-in-a-list-in-r/23406960?noredirect=1#23406960">Merging vectors of strings in a list in R</a></p>
  
  <p>I have tried an alternate approach using <code>data.table</code>. </p>
  
  <p>I have a <code>data.table</code> G as follows</p>
  
  <pre><code>d &lt;- list( c("SD1:LUSH", "SD44:CANCEL", "SD384:FR563", "SD32:TRUMPET"), c("SD23:SWITCH", "SD1:LUSH", "SD567:TREK"), c("SD42:CRAYON", "SD345:FOX", "SD183:WIRE"), c("SD345:HOLE", "SD340:DUST", "SD387:ROLL"), c("SD455:TOMATO", "SD39:MATURE"), c("SD12:PAINTING", "SD315:MONEY31", "SD387:SPRING"),  c("SD32:TRUMPET", "SD1:FIELD"))
  d2 &lt;-  lapply(d, function(x) sapply(strsplit(x, ":"), "[", 1))
  
  d &lt;- lapply(d, paste0, collapse=", ")
  d2 &lt;- lapply(d2, paste0, collapse=", ")
  
  d &lt;- as.data.frame(as.matrix(lapply(d, paste0, collapse=", ")))
  d2 &lt;- as.data.frame(as.matrix(lapply(d2, paste0, collapse=", ")))
  
  d &lt;- as.data.frame(cbind(d,d2))
  colnames(d) &lt;- c("sdw", "sd")
  d$sd &lt;- as.character(d$sd)
  d$sdw &lt;- as.character(d$sdw)
  
  
  
   G &lt;- data.table( d , key = "sd" )
                                                  sdw                     sd
  1: SD1:LUSH, SD44:CANCEL, SD384:FR563, SD32:TRUMPET SD1, SD44, SD384, SD32
  2:       SD12:PAINTING, SD315:MONEY31, SD387:SPRING     SD12, SD315, SD387
  3:                SD23:SWITCH, SD1:LUSH, SD567:TREK       SD23, SD1, SD567
  4:                          SD32:TRUMPET, SD1:FIELD              SD32, SD1
  5:               SD345:HOLE, SD340:DUST, SD387:ROLL    SD345, SD340, SD387
  6:               SD42:CRAYON, SD345:FOX, SD183:WIRE     SD42, SD345, SD183
  7:                        SD455:TOMATO, SD39:MATURE            SD455, SD39
  </code></pre>
  
  <p>I am trying to aggregate elements in column sdw based on elements in column sd. </p>
  
  <p>[1], [2] and [7] have <strong>SD1</strong> common between them. So their corresponding sdw elements should merge together. Also [1] and [7] have both <strong>SD1</strong> and <strong>SD32</strong> common.</p>
  
  <p>[4] has <strong>SD345</strong> common with [3] and <strong>SD387</strong> common with [5]. So [4], [3] and [5] sdw elements should merge together. </p>
  
  <p>[7] is not having any <strong>SD__</strong> common with other vectors, so it should remain as such. </p>
  
  <p>In short I want to aggregate G$sdw elements based on overlapping <strong>SD__</strong> terms in G$sd</p>
  
  <p>The output I am looking for is as follows with just three rows.</p>
  
  <pre><code>[1] "SD1:LUSH, SD1:FIELD,  SD23:SWITCH, SD32:TRUMPET, SD44:CANCEL, SD384:FR563,  SD567:TREK"            
  [2] "SD12:PAINTING, SD42:CRAYON, SD183:WIRE, SD340:DUST SD345:FOX, SD345:HOLE, SD387:SPRING, SD387:ROLL"
  [3] "SD455:TOMATO, SD39:MATURE"
  </code></pre>
  
  <p>I have tried <code>data.table</code> package as follows</p>
  
  <pre><code># Extract SDs from GN$sd
  G &lt;- G[ , list( ID = unlist( strsplit( sd , "," ) ) ) , by = list(sdw, sd) ]
  G$ID &lt;- gsub(" ", "", G$ID)
  G &lt;- data.table( G , key = "ID" )
  
  # Merge according to common IDs
  G2 &lt;- G[, list(Gp1 = paste0(sort(unique(unlist(strsplit(sdw, split=", ")))), collapse=", "),
                             Gp2 = paste0(sort(unique(unlist(strsplit(sd, split=", ")))), collapse=", "))  , by = "ID"]
  
  G2 &lt;- data.table( G, key="Gp2")
  G2 &lt;- unique(G2)
  G2
  
  ID                                                                                  Gp1                                 Gp2
  1:   SD1 SD1:FIELD, SD1:LUSH, SD23:SWITCH, SD32:TRUMPET, SD384:FR563, SD44:CANCEL, SD567:TREK SD1, SD23, SD32, SD384, SD44, SD567
  2:  SD23                                                    SD1:LUSH, SD23:SWITCH, SD567:TREK                    SD1, SD23, SD567
  3:  SD32                          SD1:FIELD, SD1:LUSH, SD32:TRUMPET, SD384:FR563, SD44:CANCEL              SD1, SD32, SD384, SD44
  4: SD387       SD12:PAINTING, SD315:MONEY31, SD340:DUST, SD345:HOLE, SD387:ROLL, SD387:SPRING    SD12, SD315, SD340, SD345, SD387
  5:  SD12                                           SD12:PAINTING, SD315:MONEY31, SD387:SPRING                  SD12, SD315, SD387
  6: SD345               SD183:WIRE, SD340:DUST, SD345:FOX, SD345:HOLE, SD387:ROLL, SD42:CRAYON    SD183, SD340, SD345, SD387, SD42
  7: SD183                                                   SD183:WIRE, SD345:FOX, SD42:CRAYON                  SD183, SD345, SD42
  8: SD340                                                   SD340:DUST, SD345:HOLE, SD387:ROLL                 SD340, SD345, SD387
  9:  SD39                                                            SD39:MATURE, SD455:TOMATO                         SD39, SD455
  </code></pre>
  
  <p>This can only merge based on duplication of <strong>SD__</strong> terms across rows in G$sd. It is not taking into consideration multiple common terms across elements and also same element having distinct common terms with other elements.</p>
  
  <p>Is there any way to achieve the desired output in <code>R</code>. My full dataset has thousands of such rows. </p>