How to speed up this string split operation within data.table

Question

I have a data.table with some IDs pasted together as a single character column, separated by underscores. I'm trying to split the ids into separate columns, but my best method is really slow for my large dataset (~ 250M rows). Interestingly, the operation doesn't appear to take O(N) time which is what I'd expect. In other words, it's pretty fast until about 50M+ rows and then gets really slow. Make some data ``` require(data.table) set.seed(2016) sim_rows <- 40000000 dt <- data.table( LineId = rep("L0123", times=sim_rows), StationId = rep("S0123", times=sim_rows), TimeId = rep("T0123", times=sim_rows) ) dt[, InfoId := paste(LineId, StationId, TimeId, sep="_")] dt[, c("LineId", "StationId", "TimeId") := NULL] gc(reset=T) # free up 1.5Gb of memory dt InfoId 1: L0123_S0123_T0123 2: L0123_S0123_T0123 3: L0123_S0123_T0123 4: L0123_S0123_T0123 5: L0123_S0123_T0123 --- 39999996: L0123_S0123_T0123 39999997: L0123_S0123_T0123 39999998: L0123_S0123_T0123 39999999: L0123_S0123_T0123 40000000: L0…

Accepted Answer

`stringr` is newer, based on `stringi` internally and is often even faster.

Also, both stringi and to a lesser extent stringr have multiple variants of each string operation (`fixed/coll/regex/words/boundaries/charclass`) which are optimized for the type of operand.

Try `stri_split_fixed(..., '_')`, it should be very fast.

```
require(stringi)
> system.time( dt[1:1e6, c("LineId", "StationId", "TimeId") := stri_split_fixed(InfoId, "_")] )
   user  system elapsed 
  2.635   0.497   3.379  # on my old machine; please tell us your numbers?
```

How to speed up this string split operation within data.table

Solution

Problem