Modifying a map table based on multiple matches

Question

I have a map table with this structure:

    structure(list(REF_ID = structure(1:10, .Label = c("202533_s_at",  
"202534_x_at", "202551_s_at", "202552_s_at", "202555_s_at", "202565_s_at",  
"202566_s_at", "202580_x_at", "202581_at", "202589_at"), class = "factor"),  
GeneSymbol = structure(c(2L, 2L, 1L, 1L, 5L, 6L, 6L, 3L, 4L, 7L), .Label =  
c("CRIM1 /// LOC101929500", "DHFR", "FOXM1", "HSPA1A /// HSPA1B", "MYLK",  
"SVIL", "TYMS"), class = "factor")), .Names = c("REF_ID", "GeneSymbol"),  
class = "data.frame", row.names = c(NA, -10L))

In row 3, 4 and 9, there are multiple GeneSymbol that matches with a single REF_ID. (Here /// is the delimiter). Thus in row 3, two gene symbols matches with a single REF_ID.

I want a modified table (with all existing mapping) such that the REF_ID will be repeated as many times as it matches with a separate gene symbol.
Thus I want two separate rows for row 3 with entries: one row with REF_ID == 202551_s_at and GeneSymbol == CRIM1 and another row with REF_ID == 202551_s_at and GeneSymbol == LOC101929500.

Can you help me out please.

score 2 · Answer 1 · answered May 06 '18 at 06:42

Just to add to Rui Barradas's answer, one tidyverse approach might be to use separate_rows() included with the tidyr package:

library(tidyverse)
df %>% separate_rows(GeneSymbol, sep = " /// ")
#>         REF_ID   GeneSymbol
#> 1  202533_s_at         DHFR
#> 2  202534_x_at         DHFR
#> 3  202551_s_at        CRIM1
#> 4  202551_s_at LOC101929500
#> 5  202552_s_at        CRIM1
#> 6  202552_s_at LOC101929500
#> 7  202555_s_at         MYLK
#> 8  202565_s_at         SVIL
#> 9  202566_s_at         SVIL
#> 10 202580_x_at        FOXM1
#> 11   202581_at       HSPA1A
#> 12   202581_at       HSPA1B
#> 13   202589_at         TYMS

Data

df <- structure(list(REF_ID = structure(1:10, .Label = c("202533_s_at",  
                                                   "202534_x_at", "202551_s_at", "202552_s_at", "202555_s_at", "202565_s_at",  
                                                   "202566_s_at", "202580_x_at", "202581_at", "202589_at"), class = "factor"),  
               GeneSymbol = structure(c(2L, 2L, 1L, 1L, 5L, 6L, 6L, 3L, 4L, 7L), .Label =  
                                        c("CRIM1 /// LOC101929500", "DHFR", "FOXM1", "HSPA1A /// HSPA1B", "MYLK",  
                                          "SVIL", "TYMS"), class = "factor")), .Names = c("REF_ID", "GeneSymbol"),  
          class = "data.frame", row.names = c(NA, -10L))

score 1 · Accepted Answer · edited May 06 '18 at 09:28

The following does what you want. It uses base R only, maybe there are simpler solutions in the tidyverse.

map$GeneSymbol <- as.character(map$GeneSymbol)

out <- lapply(seq_along(map$GeneSymbol), function(i){
    g <- map$GeneSymbol[i]
    if(grepl("///", g)){
        g <- trimws(unlist(strsplit(g, "///")))
        data.frame(REF_ID = rep(map$REF_ID[i], length(g)), GeneSymbol = g)
    } else {
        data.frame(REF_ID = map$REF_ID[i], GeneSymbol = g)
    }

})

map$GeneSymbol <- as.factor(map$GeneSymbol)

out <- do.call(rbind, out)
out
#        REF_ID   GeneSymbol
#1  202533_s_at         DHFR
#2  202534_x_at         DHFR
#3  202551_s_at        CRIM1
#4  202551_s_at LOC101929500
#5  202552_s_at        CRIM1
#6  202552_s_at LOC101929500
#7  202555_s_at         MYLK
#8  202565_s_at         SVIL
#9  202566_s_at         SVIL
#10 202580_x_at        FOXM1
#11   202581_at       HSPA1A
#12   202581_at       HSPA1B
#13   202589_at         TYMS

@J.Smith I realized that using `length(g)` might improve the answer right after clicking to post but then I thought that there would be only one `///` and therefore `g` would always be split in just two. Anyway, your edit does make the answer more general. Thanks. — Rui Barradas, May 06 '18 at 09:32

Modifying a map table based on multiple matches

2 Answers2