Extract column from text in memory

Question

I'm looking for a fast way to read in a single column from tab-separated text that lives as a character vector in memory.

I'm using a file format specific to my field that roughly resembles a compressed tsv file. It is fast and easy to read in a subset of lines from such file, but unfeasible to read in data directly with read.table(), data.table::fread() or readr::read_tsv() due to memory limitations (and the rows I need aren't known a priori).

So, I end up with a character vector in memory, with an element for every line, but with the tab separators still in there. I'm a bit puzzled on how to quickly extract a specific column from this text. In the example below, what is the fastest way to extract the third column? There aren't any 'surprises' in the text such as comments or quoted names, but in my real case the columns don't have a fixed width. The fastest method I've found so far is to use the readr::read_tsv() function.

library(readr)

set.seed(0)
# About 88Mb of memory
n_examples <- 1e6
text <- paste(
  as.character(as.hexmode(sample(n_examples))),
  as.character(as.hexmode(sample(n_examples))),
  as.character(as.hexmode(sample(n_examples))),
  as.character(as.hexmode(sample(n_examples))),
  sep = "	"
)

fun_read.table <- function(x, i) {
  read.table(
    text = x, sep = "	", 
    colClasses = c("character", "character", "character", "character")
  )[[i]]
}

fun_read_tsv <- function(x, i) {
  read_tsv(file = I(x), col_select = all_of(i), 
           col_types = "cccc", col_names = LETTERS[1:4])[[1]]
}

bm <- bench::mark(
  fun_read.table(text, 3),
  fun_read_tsv(text, 3), 
  min_iterations = 5
)
#> Warning: Some expressions had a GC in every iteration; so filtering is disabled.

print(bm)
#> # A tibble: 2 x 13
#>   expression                   min   median `itr/sec` mem_alloc `gc/sec` n_itr
#>                             
#> 1 fun_read.table(text, 3)    1.34s    1.57s     0.619    93.2MB    1.11      5
#> 2 fun_read_tsv(text, 3)   879.36ms 903.72ms     1.10     35.1MB    0.219     5
#> # ... with 6 more variables: n_gc , total_time , result ,
#> #   memory , time , gc

Below are some alternatives that I've tried, but weren't faster than read_tsv(). The data.table::fread() method was surprisingly slow due to it writing the input text to a temporary file first. I haven't managed to figure out a regex-based method to capture the third column, so I don't know if that would be faster.

library(data.table)
#> Warning: package 'data.table' was built under R version 4.1.1

fun_tstrsplit <- function(x, i) {
  tstrsplit(x, "	", keep = i)[[1]]
}

fun_fread <- function(x, i) {
  fread(
    text = x, sep = "	",
    colClasses = c("character", "character", "character", "character"),
    select = i
  )[[1]]
}

fun_scan <- function(x, i) {
  ncols <- lengths(regmatches(x[[1]], gregexpr("	", x[[1]]))) + 1
  scan(
    text = x, sep = "	", what = "", quiet = TRUE
  )[seq_along(x) %% ncols == i]
}

^{Created on 2021-10-13 by the reprex package (v2.0.1)}

user_12500315 · Answer

A bespoke function written with Rcpp worked fastest for me here (a bit more than twice as fast as read_tsv), and uses about a quarter of the memory allocation of read_tsv, even though it involves some copying and could probably be optimised.

I've included a version using sub too, but this is slower than read_tsv, although again it doesn't require much memory.

Rcpp::cppFunction("

std::vector fun_rcpp(CharacterVector a, int col) {
  if(col < 1) Rcpp::stop(\"col must be a positive integer\");
  std::vector b = Rcpp::as>(a);
  std::vector result(a.size());
  for(uint32_t i = 0; i < a.size() ; i++)
  {
    int n_tabs = 0;
    std::string entry = \"\";
    for(uint16_t j = 0; j < b[i].size(); j++)              
    {
      if(n_tabs == (col - 1) & b[i][j] != '\t') entry.push_back(b[i][j]);
      if((b[i][j]) == '\t') n_tabs++;
      if(n_tabs == col) break;
    }
    result[i] = entry;
  }
  return result;
}

")

fun_sub <- function(x, i)
{
  s <- paste0("^", paste0(rep(".*?	", i - 1), collapse = ""), "(.*?)	.*$")
  sub(s, "\1", x)
}

These functions both give the expected output:

identical(fun_read_tsv(text, 3), fun_rcpp(text, 3))
#> [1] TRUE

identical(fun_read_tsv(text, 3), fun_sub(text, 3))
#> [1] TRUE

And the benchmarks are shown here for comparison:

bench::mark(
  fun_read.table(text, 3),
  fun_read_tsv(text, 3),
  fun_sub(text, 3),
  fun_rcpp(text, 3),
  min_iterations = 5
)

#> # A tibble: 4 x 13
#>   expression                   min   median `itr/sec` mem_alloc `gc/sec` n_itr  n_gc
#>                              
#> 1 fun_read.table(text, 3)    1.35s    1.35s     0.738   93.23MB    5.17      1     7
#> 2 fun_read_tsv(text, 3)   788.86ms 792.35ms     1.26    36.04MB    0.314     4     1
#> 3 fun_sub(text, 3)           1.27s    1.29s     0.777    7.63MB    0.194     4     1
#> 4 fun_rcpp(text, 3)       379.02ms 381.17ms     2.62     7.63MB    0.655     4     1
#> # ... with 5 more variables: total_time , result , memory ,
#   time , gc

Note that the Rcpp function behaves pretty much as expected, with appropriate errors emitted if you give a column number of less than 1 or use the wrong variable type to select a column. However, if you select a column number greater than the number of columns present it will return a vector of empty strings rather than throwing an error. You could easily write an R wrapper to the C++ function if you wanted different behaviour here, such as an error or a vector of NA

Extract column from text in memory

1 Answers