10

I have a data frame that looks like this:

Chr start   stop    ref alt Hom/het ID  
chr1    5179574 5183384 ref Del Het 719  
chr1    5179574 5184738 ref Del Het 915  
chr1    5179574 5184738 ref Del Het 951  
chr1    5336806 5358384 ref Del Het 376  
chr1    5347979 5358384 ref Del Het 228

I would like to merge any duplicate rows, combining the last ID column so that all IDs are in one row/column, like this:

Chr start   stop    ref alt Hom/het ID  
chr1    5179574 5183384 ref Del Het 719  
chr1    5179574 5184738 ref Del Het 915, 951 
chr1    5336806 5358384 ref Del Het 376  
chr1    5347979 5358384 ref Del Het 228

I have found examples of people removing duplicates and summing a column, but I just want to combine all IDs with duplicate regions in a list in a single column.

m0nhawk
  • 22,980
  • 9
  • 45
  • 73
user1967407
  • 175
  • 1
  • 7
  • Although it starts at the same position, the first sequence does not appear to be a duplicate of the next two since it is a bit longer. Could you clarify what you are defining as a "duplicate" in this instance? – MattLBeck Jan 10 '13 at 16:37
  • Sorry, my mistake for not looking at my example closely. A duplicate will be an exact match in all columns except for the ID column. – user1967407 Jan 10 '13 at 16:40

1 Answers1

12

Some call to aggregate() should do the trick.

Here's an option that collects the ID's in a list object:

(df1 <- aggregate(df[7], df[-7], unique))
#   Chr   start    stop ref alt Hom.het       ID
# 1 chr1 5179574 5183384 ref Del     Het      719
# 2 chr1 5179574 5184738 ref Del     Het 915, 951
# 3 chr1 5336806 5358384 ref Del     Het      376
# 4 chr1 5347979 5358384 ref Del     Het      228

And here's one that collects them in a character vector: 

df2 <- aggregate(df[7], df[-7], 
                 FUN = function(X) paste(unique(X), collapse=", "))

Comparing the results of the two options:

str(df1$ID)
# List of 4
#  $ 0: int 719
#  $ 3: int [1:2] 915 951
#  $ 7: int 376
#  $ 8: int 228

str(df2$ID)
# chr [1:4] "719" "915, 951" "376" "228"
Josh O'Brien
  • 159,210
  • 26
  • 366
  • 455