Aggregate rows with a common value retaining unique values

Question

I've tried to do the following starting from this data frame

    Chr                Gene.Symbols
2  chr1                       GSTM1
3  chr2                     MIR4432
4  chr2                      BCL11A
5  chr2                      PAPOLG
6  chr2                   LINC01185
7  chr2                   LINC01185
8  chr2              LINC01185, REL
9  chr2                         REL
10 chr2                         REL
11 chr2                         REL
12 chr2                         REL
13 chr2                            
14 chr2                       PUS10
15 chr2             PEX13, KIAA1841

I would like to have this result:

    Chr             Gene.Symbols
2  chr1             GSTM1
3  chr2             MIR4432,BCL11A,PAPOLG,LINC01185,REL,PUS10,PEX13,KIAA1841

I've managed to aggregate the gene symbols together using:

aggregate(Gene.Symbols~Chr, data, paste, collapse = ",")

that I learned from other questions like this one, but I wasn't able to remove duplicates.

Can someone help me, please?

UPDATE: I also need a file with only the genes names one per row (without the "Chr" column). How can I traspose the gene names? I am starting now with a file with as many rows as Chr and each row one has several genes in the Gene.Symbols column.

in row 8 , `LINC01185, REL` or `LINC01185`, also in row 15 `PEX13, KIAA1841` or `PEX13` — BENY, Sep 22 '17 at 14:33
@Sotos I think the question is a little bit different from the 'duplicated' one — BENY, Sep 22 '17 at 14:40
@Wen Hmm...I just noticed `LINC01185, REL` and also `REL`. You are right, I will reopen — Sotos, Sep 22 '17 at 14:43

Sotos · Answer 1 · 2017-09-25T07:28:41.370

3

An idea via base R in two steps,

dd <- aggregate(Gene.Symbols ~ Chr, df, paste, collapse = ', ')

dd$Gene.Symbols <- sapply(strsplit(dd$Gene.Symbols, ", "), function(i) 
                                                    paste(unique(i), collapse = ","))

which gives,

#   Chr                                              Gene.Symbols
#1 chr1                                                     GSTM1
#2 chr2 MIR4432,BCL11A,PAPOLG,LINC01185,REL,,PUS10,PEX13,KIAA1841

A one-liner (compliments of @Cath) would be,

aggregate(Gene.Symbols ~ Chr, df, function(gene) 
                              paste(unique(unlist(strsplit(gene, ", "))), collapse = ','))

edited Sep 25 '17 at 07:28

answered Sep 22 '17 at 14:52

Sotos

51,121
6
32
66

I tried the two steps but I keep having some duplicates `Chr Gene.Symbols 2 chr2 MIR4432, BCL11A, PAPOLG, LINC01185, REL, REL, , PUS10, PEX13, KIAA1841` – Francesca Scellato Sep 22 '17 at 18:22
@FrancescaScellato wrong delimiter. It should be `", "`. – Sotos Sep 25 '17 at 07:23
2

to avoid `sapply` step, you can directly do: `aggregate(Gene.Symbols ~ Chr, df, function(gene) paste(unique(unlist(strsplit(gene, ", "))), collapse = ','))` – Cath Sep 25 '17 at 07:27

score 3 · Accepted Answer · answered Sep 25 '17 at 08:05

Even another option:

library(splitstackshape) # automatically loads the 'data.table'-package
cSplit(mydf, 'Gene.Symbols', sep = ','
       , direction = 'long')[, .(Gene.Symbols = toString(unique(Gene.Symbols)))
                             , by = Chr]

which gives:

    Chr                                                    Gene.Symbols
1: chr1                                                           GSTM1
2: chr2 MIR4432, BCL11A, PAPOLG, LINC01185, REL, PUS10, PEX13, KIAA1841

score 2 · Answer 3 · answered Sep 22 '17 at 14:29

2

collapse_unique <- function(x) {
    paste(unique(x), collapse = ",")
}

aggregate(Gene.Symbols~Chr, data, collapse_unique)

answered Sep 22 '17 at 14:29

Nathan Werth

5,093
18
25

score 2 · Answer 4 · answered Sep 22 '17 at 14:43

By using dplyr and tidyr

#1st unnest your string
df=df %>%
    transform( Gene.Symbols = strsplit( Gene.Symbols, ",")) %>%
    unnest( Gene.Symbols)
# then group by 
df%>%group_by(Chr)%>%summarise(Gene.Symbols=toString(unique(Gene.Symbols)))

# A tibble: 2 x 2
    Chr                                                           Gene.Symbols
  <chr>                                                                  <chr>
1  chr1                                                                  GSTM1
2  chr2       MIR4432, BCL11A, PAPOLG, LINC01185, REL, PUS10, PEX13,  KIAA1841

Aggregate rows with a common value retaining unique values

4 Answers4

Linked