Faster way to transform text vector to numeric matrix/data.frame in R?

Question

I'm using R to parse out some server logs that produce lists that look like this:

myLog <- c("[1,2,3]","[4,5,6]","[7,8,9]")

What I want to produce from them is a matrix that looks like this:

myMatrix <- matrix(c(c(1,2,3),c(4,5,6),c(7,8,9)),nrow=3,byrow=T)

They come from querying a database field of type varchar so I don't think I can use any file-reading tricks.

I tend to have lots of these, millions of rows at a time.

What I've been doing is the following, it's quite slow:

splitDat <- sapply(inputVector,function(y){
  y1 <- gsub("\\[","",y)
  y2 <- gsub("\\]","",y1)
  y3 <- strsplit(y2,split=", ")
  y4 <- unlist(y3)
})

Is there a more efficient way? A one-liner regex?

Answer 1

You could try to vectorize this using the stringi package

library(stringi)
matrix(as.numeric(unlist(stri_extract_all_regex(myLog, pattern = "\\d"))), 
                  nrow = 3, byrow = TRUE)

#      [,1] [,2] [,3]
# [1,]    1    2    3
# [2,]    4    5    6
# [3,]    7    8    9

---

Benchmarks

library(stringi)
library(gsubfn)
library(microbenchmark)

set.seed(123)
myLog <- c("[1,2,3]","[4,5,6]","[7,8,9]")
myLog <- sample(myLog, 1e4, replace = TRUE)

Res <- microbenchmark(
               David = matrix(as.numeric(unlist(stri_extract_all_regex(myLog, pattern = "\\d"))), nrow = 3, byrow = TRUE),
               Thela = matrix(as.numeric(unlist(strsplit(myLog,"\\[|\\]|,"))),nrow=length(myLog),byrow=TRUE)[,-1],
               BD1 =  matrix(as.numeric(scan(text=gsub("\\D"," ",myLog),what="")), nrow=length(myLog),byrow=T),
               BD2 = matrix(as.numeric(scan(text=gsub("[],[]"," ",myLog), what="")),nrow=length(myLog), byrow=T),
               GG1 = read.table(text = gsub("\\D", " ", myLog)),
               GG2 = read.pattern(text = myLog, pat = "\\d")
)

Res
# Unit: milliseconds
# expr       min        lq      mean    median        uq       max neval
# David  12.01351  12.90111  16.41127  13.98826  15.62786 101.65117   100
# Thela  25.49944  27.09937  29.83234  28.32153  30.24141  80.79836   100
#   BD1  92.39541  94.81445 101.20524  98.07333 102.41877 172.60835   100
#   BD2  91.91578  94.66958 104.02773  96.94019 103.99383 206.37865   100
#   GG1  91.28813  94.29219  98.63825  96.57544 100.57172 140.97998   100
#   GG2 470.43382 514.58552 551.94922 540.86479 570.88711 815.75789   100

boxplot(Res)

Answer 2

1) I haven't checked how fast this is but the code is very short:

library(gsubfn)
read.pattern(text = myLog, pat = "\\d")

where myLog is as in the question.

2) Here is a base solution:

read.table(text = gsub("\\D", " ", myLog))