How to extract substring using regex in multiple columns using data.table

Question

I have the following process running with dplyr without any problems:

library(tidyverse)
my_dplyr_dat  <- structure(list(chrn = c("chr20", "chr6", "chr5"), start = c(52447674L, 
12962440L, 66453982L), end = c(52447689L, 12962455L, 66453997L
), motif_name_binned = c("ZNF263/MA0528.1/Jaspar.instid_chr20:52447338-52447738.bin22", 
"Klf12/MA0742.1/Jaspar.instid_chr6:12962360-12962760.bin6", "Hoxc9/MA0485.1/Jaspar.instid_chr5:66453806-66454206.bin12"
), motif_score = c(6.728401, -0.979777, 6.091471), strand = c("+", 
"+", "+"), read_count = c(0L, 0L, 0L)), .Names = c("chrn", "start", 
"end", "motif_name_binned", "motif_score", "strand", "read_count"
), row.names = c(NA, -3L), class = c("tbl_df", "tbl", "data.frame"
))

It looks like this:

# A tibble: 3 x 7
   chrn    start      end                                           motif_name_binned motif_score strand read_count
  <chr>    <int>    <int>                                                       <chr>       <dbl>  <chr>      <int>
1 chr20 52447674 52447689 ZNF263/MA0528.1/Jaspar.instid_chr20:52447338-52447738.bin22    6.728401      +          0
2  chr6 12962440 12962455    Klf12/MA0742.1/Jaspar.instid_chr6:12962360-12962760.bin6   -0.979777      +          0
3  chr5 66453982 66453997   Hoxc9/MA0485.1/Jaspar.instid_chr5:66453806-66454206.bin12    6.091471      +          0

The main task I want to achieve is to extract a column motif_name_binnedusing a regular expression and decompose it into 3 columns c('motif', 'inst', 'binno')using dplyr, this can be done as follows:

my_dplyr_dat %>% 
  extract(motif_name_binned, c('motif', 'inst', 'binno'), regex = "^(.*?\\/.*?)\\.instid_(.*?)\\.bin(\\d+)", remove = FALSE) %>% 
  select(-motif_name_binned)

What produces this:

# A tibble: 3 x 9
   chrn    start      end                  motif                    inst binno motif_score strand read_count
* <chr>    <int>    <int>                  <chr>                   <chr> <chr>       <dbl>  <chr>      <int>
1 chr20 52447674 52447689 ZNF263/MA0528.1/Jaspar chr20:52447338-52447738    22    6.728401      +          0
2  chr6 12962440 12962455  Klf12/MA0742.1/Jaspar  chr6:12962360-12962760     6   -0.979777      +          0
3  chr5 66453982 66453997  Hoxc9/MA0485.1/Jaspar  chr5:66453806-66454206    12    6.091471      +          0

How can I do this using data.table ?

This is the source data in data.table format. I have (for example, before retrieving a string, etc.):

library(data.table)
my_data_table <- structure(list(chrn = c("chr20", "chr6", "chr5"), start = c(52447674L, 
12962440L, 66453982L), end = c(52447689L, 12962455L, 66453997L
), motif_name_binned = c("ZNF263/MA0528.1/Jaspar.instid_chr20:52447338-52447738.bin22", 
"Klf12/MA0742.1/Jaspar.instid_chr6:12962360-12962760.bin6", "Hoxc9/MA0485.1/Jaspar.instid_chr5:66453806-66454206.bin12"
), motif_score = c(6.728401, -0.979777, 6.091471), strand = c("+", 
"+", "+"), read_count = c(0L, 0L, 0L)), .Names = c("chrn", "start", 
"end", "motif_name_binned", "motif_score", "strand", "read_count"
), class = c("data.table", "data.frame"), row.names = c(NA, -3L
))

Which looks like this:

    chrn    start      end                                           motif_name_binned motif_score strand read_count
1: chr20 52447674 52447689 ZNF263/MA0528.1/Jaspar.instid_chr20:52447338-52447738.bin22    6.728401      +          0
2:  chr6 12962440 12962455    Klf12/MA0742.1/Jaspar.instid_chr6:12962360-12962760.bin6   -0.979777      +          0
3:  chr5 66453982 66453997   Hoxc9/MA0485.1/Jaspar.instid_chr5:66453806-66454206.bin12    6.091471      +          0

Answer 1

gsub tstrsplit split 3

my_data_table[, c('motif', 'inst', 'binno') := tstrsplit(
   gsub("^(.*?\\/.*?)\\.instid_(.*?)\\.bin(\\d+)", "\\1$\\2$\\3", motif_name_binned), '$',
       fixed = TRUE)][, setdiff(names(my_data_table), "motif_name_binned"), with = FALSE]
#    chrn    start      end motif_score strand read_count                  motif                    inst binno
#1: chr20 52447674 52447689    6.728401      +          0 ZNF263/MA0528.1/Jaspar chr20:52447338-52447738    22
#2:  chr6 12962440 12962455   -0.979777      +          0  Klf12/MA0742.1/Jaspar  chr6:12962360-12962760     6
#3:  chr5 66453982 66453997    6.091471      +          0  Hoxc9/MA0485.1/Jaspar  chr5:66453806-66454206    12