R: How to separate and add numerical values separated by ";" (maxed out memory problem)

user_1134241 0
Viewed 122

I have 5000 rows of up to 2000 values separated by ";" which are currently stuffed into a single column. I would like to separate them, add them and throw away the original column. Problem: I max out memory quickly. Currently using more than 60GB RAM. What is a good way to deal with this?

So far I have:

Option 1: Dplyr

#Not every line has the same number of values
MaxNumSemiColon=2001 #Max amount

    df %>%
        separate(dose_close, sep=";",
        remove = TRUE,
        into = paste0("dummy",1: MaxNumSemiColon),
        convert = TRUE,
        fill = "right")%>%
        rowwise() %>% 
        mutate(dose_close = sum(c_across(dummy1:paste0("dummy",numSemiColon)),na.rm = TRUE))%>%
        select(-contains("dummy"))

Option 2: Data.table

  setDT(df)[, paste0("dose_close", 1:numSemiColon) := tstrsplit(value, ";", type.convert = TRUE, fixed = TRUE)]
  temp<-temp[,-1]
  temp<-temp%>% rowSums() %>% as.data.frame() %>% set_colnames("dose_close")
  df$dose_close<-NULL
  df<-cbind(df,temp) #Bind dose_close

NB: I saw data.table::fread has sep2=";" which would be ideal but does anyone know when it might be available?

data.table dplyr r
3 Answers

I don't see any reason you should have trouble using fread directly on the pasted column. The problems you might encounter for data this size is more likely to be incorrect detection of the number of resulting columns, and not maxing out on memory.

Here's some sample data to play with:

library(data.table)
n <- 5000
set.seed(1)
vals_row <- sample(2000, n, TRUE)
DT <- data.table(ID = 1:n, 
                 vals = sapply(vals_row, function(x) 
                   paste(sample(100, x, TRUE), collapse = ";")))
DT[sample(n, n*.05), vals := NA]

Reading the data with fread.

system.time({test <- fread(text = c(paste0("V", sequence(max(vals_row)), collapse = ";"),
                                    DT$vals), sep = ";", fill = TRUE, header = TRUE)})
#    user  system elapsed 
#   0.728   0.036   0.263 
dim(test)
# [1] 5000 2000
test[1:10, 1:10]
#     V1 V2 V3 V4 V5 V6 V7 V8 V9 V10
#  1: 45 42  6 57 70 24 12 15 11  68
#  2: 94 47 12 46 33 80 66 12 93  89
#  3:  6 21 59 16 20 30 57  9 67 100
#  4:  6 39 59 87 83 23 24 12 63  62
#  5: 78 90 34 26 31 51 60 41 36  82
#  6: 68 76 53 16  7 64 18 34 49  56
#  7: 33 94 87 80 25 95 70 42 53  39
#  8: 77 96 55 43 61 37 11  9 48  94
#  9: NA NA NA NA NA NA NA NA NA  NA
# 10:  9 66 44 31 83 30 45 92 17  24

bench::mark(fread(text = c(paste0("V", sequence(max(vals_per_row)), collapse = ";"),
  DT$vals), sep = ";", fill = TRUE, header = TRUE))[, c("min", "median", "mem_alloc")]
# # A tibble: 1 x 3
#        min   median mem_alloc
#   <bch:tm> <bch:tm> <bch:byt>
# 1    250ms    250ms     114MB

For reference, I did try fread directly without making the fake column names first, but the guessing of the maximum number of columns failed. This is a known issue, and a workaround is to insert a line at the start with the desired number of columns. If the number of columns in each row is consistent, this shouldn't be a problem.

Once you have test, you can just do:

rowSums(test, na.rm = TRUE)

to get your desired output.

This, and the other options shared here have been benchmarked at this Gist.

Another good alternative would be to use dstrsplit or mstrsplit from the "iotools" package:

library(iotools)
vals <- max(vals_row)
# dstrsplit
dstrsplit(DT$vals, col_types = rep("integer", vals), sep = ";")
# mstrsplit
mstrsplit(DT$vals, sep = ";", type = "integer", ncol = vals)
user_12706950

Maybe you can do something like this? Should be low on memory usage since he only splits one string at a time and then returns the value as a double. Works on my 32GB RAM computer.

split_sum <- function(string){
string %>%
stringr::str_split(';') %>%
magrittr::extract2(1) %>%
as.double() %>%
sum()
}
tibble::tibble(dose_close = rep(paste0(runif(2000), collapse = ';'), 5000)) %>% 
dplyr::mutate(
    dose_close = purrr::map_dbl(dose_close, split_sum)
)
# A tibble: 5,000 x 1
   dose_close
        <dbl>
 1      1002.
 2      1002.
 3      1002.
 4      1002.
 5      1002.
 6      1002.
 7      1002.
 8      1002.
 9      1002.
10      1002.
# ... with 4,990 more rows
user_92743230

One option is to use 'read_delim()`from the "readr" package.

#test data
test<-c("a;b;c;d;e", "1;2;3;4;5", 'r;s;t;v')
df<-data.frame(test, stringsAsFactors = FALSE)

library(readr)
read_delim(df$test, delim=";", col_names = FALSE)


# A tibble: 3 x 5
 X1    X2    X3    X4    X5   
  <chr> <chr> <chr> <chr> <chr>
1 a     b     c     d     e    
2 1     2     3     4     5    
3 r     s     t     v     NA
user_57922440
Related