Split delimited string into new rows with type conversion set to TRUE

user_680068 0
Viewed 89

data.table::tstrsplit has a useful type.convert argument. But it errors out when after the split each row gets converted into a different class, see example:

library(data.table)

x <- fread("CHROM POS REF ALT TYPE AF
chr1 1 A T MISSENSE 0.23
chr2 1 A T,G MISSENSE 0.17,0.09")

In ALT column we have "T" and "T,G", so first row gets converted to logical "TRUE", and the second row gets split and converted into character "T" and "G". As a result we get below error:

x[, lapply(.SD, function(x) unlist(tstrsplit(x, ",", fixed = TRUE, type.convert = TRUE))),
  by = .(CHROM, POS, REF, TYPE)]

# Error in `[.data.table`(x, , lapply(.SD, function(x) unlist(tstrsplit(x,  : 
#   Column 1 of result for group 2 is type 'character' but expecting type
#   'logical'. Column types must be consistent for each group.

We could avoid auto conversion, and convert later manually, all great:

x[, lapply(.SD, function(x) unlist(tstrsplit(x, ",", fixed = TRUE))),
  by = .(CHROM, POS, REF, TYPE)][, .(CHROM, POS, REF, ALT, TYPE, AF = as.numeric(AF))]
#    CHROM POS REF ALT     TYPE   AF
# 1:  chr1   1   A   T MISSENSE 0.23
# 2:  chr2   1   A   T MISSENSE 0.17
# 3:  chr2   1   A   G MISSENSE 0.09

But tidyr::separate doesn't have this issue:

tidyr::separate_rows(x, ALT, AF, convert = TRUE)
# # A tibble: 3 x 6
#   CHROM   POS REF   ALT   TYPE        AF
#   <chr> <int> <chr> <chr> <chr>    <dbl>
# 1 chr1      1 A     T     MISSENSE  0.23
# 2 chr2      1 A     T     MISSENSE  0.17
# 3 chr2      1 A     G     MISSENSE  0.09

The question: is there a better data.table way to achieve this? I need to use type conversion as AF column needs to be numeric. I'd want to split delimited column simultaneously. In real data there might more than 2 columns with delimiters.

data.table r split string
2 Answers

It can be done more easily with cSplit

library(splitstackshape)
cSplit(x, c("ALT", "AF"), ",", "long")
#   CHROM POS REF ALT     TYPE   AF
#1:  chr1   1   A   T MISSENSE 0.23
#2:  chr2   1   A   T MISSENSE 0.17
#3:  chr2   1   A   G MISSENSE 0.09

Regarding the data.table option, another way is to add a space

x[, lapply(.SD, function(x) 
 trimws(unlist(tstrsplit(gsub("([TF])+", " \\1", x), ",", 
    fixed = TRUE, type.convert = TRUE)))),
    by = .(CHROM, POS, REF, TYPE)]
#   CHROM POS REF     TYPE ALT   AF
#1:  chr1   1   A MISSENSE   T 0.23
#2:  chr2   1   A MISSENSE   T 0.17
#3:  chr2   1   A MISSENSE   G 0.09
user_37322710

As the problem is that type.convert identifies T of the first group as a logical TRUE, you could use explicitely type.convert on the resulting data.table where you have also G in the variable, so you don't have this problem:

x[, lapply(.SD, function(x) unlist(str_split(x,","))),
           by = .(CHROM, POS, REF, TYPE)] %>% 
   type.convert(.,as.is = T)

   CHROM POS REF     TYPE ALT   AF
1:  chr1   1   A MISSENSE   T 0.23
2:  chr2   1   A MISSENSE   T 0.17
3:  chr2   1   A MISSENSE   G 0.09


$CHROM
[1] "character"

$POS
[1] "integer"

$REF
[1] "character"

$TYPE
[1] "character"

$ALT
[1] "character"

$AF
[1] "numeric"
user_80538170
Related