0

I have a relatively large data.table (around 1 billion rows, and 30 columns), and am trying to subset it to remove some categories I'm not interested in. The category variable is a factor with around 30 labels. However, when I do this my session is consistently killed. Is there a way to subset a data.table in place?

Given my data.table is dt, the line in question which causes the crash is:

dt <- dt[!category %in% c('f', 'g')]

Any suggestions for how to avoid this issue? Apologies for the lack of a reproducible example, it's obviously difficult with this scale of data. I'm using R version 3.6.1 and data.table version 1.12.9.

mlinegar
  • 1,389
  • 1
  • 11
  • 19
  • _Is there a way to subset a data.table in place?_ [data.table can't delete rows by reference yet](https://stackoverflow.com/questions/10790204/how-to-delete-a-row-by-reference-in-data-table/10791729#10791729) – Henrik May 13 '20 at 18:47
  • 2
    Have you tried a "not-join subset" based on binary search, `dt[!c('f', 'g'), on = "category"]`. See also _2. Fast subsetting using on argument and secondary indices_ in the vignette [Secondary indices and auto indexing](https://cran.r-project.org/web/packages/data.table/vignettes/datatable-secondary-indices-and-auto-indexing.html) – Henrik May 13 '20 at 19:01
  • 1
    I hadn't tried a "not-join subset" previously - this resolves the issue, thanks @Henrik! – mlinegar May 13 '20 at 19:21
  • Good! See also the Examples section in `?data.table`: `# joins as subsets`; `# fast *keyed* subsets`. Cheers. – Henrik May 13 '20 at 19:30
  • _NOT IN_ used to be very inefficient in SQL, maybe this is also valid here – jangorecki May 13 '20 at 22:58

1 Answers1

2

I tried some approaches with 500 million rows and 5 columns.

I made about a 8% improvement in memory allocation with a couple optimizations:

Edit: You can get another 3-4% with @Henrik's suggestion.

library(data.table)
library(bench)
set.seed(3)
#sample.size <- 500000000 #Don't try this on your home laptop folks
sample.size <- 1000000
test.dt <- data.table(category = sample(as.factor(letters),size = sample.size, replace = TRUE),
                      as.data.table(lapply(1:5,function(x)as.integer(runif(sample.size,1,100)))))

mark(result <- test.dt[!category %in% c('f', 'g')],
     result <- test.dt[!(category == 'f' | category == 'g')],
     result <- test.dt[!c('f','g'),on = "category"])


 expression                                                   min   median `itr/sec` mem_alloc `gc/sec` n_itr  n_gc total_time 
  <bch:expr>                                              <bch:tm> <bch:tm>     <dbl> <bch:byt>    <dbl> <int> <dbl>   <bch:tm>
1 result <- test.dt[!category %in% c("f", "g")]             18.15s   18.15s    0.0551    23.2GB    0.110     1     2     18.15s
2 result <- test.dt[!(category == "f" | category == "g")]    8.43s    8.43s    0.119     21.3GB    0.119     1     1      8.43s
3 result <- test.dt[!c("f", "g"), on = "category"]           7.83s    7.83s    0.128     20.6GB    0.383     1     3      7.83s
Ian Campbell
  • 23,484
  • 14
  • 36
  • 57
  • This resolves the issue! Thanks so much @Ian Campbell - I think the improvement in efficiency was exactly enough to avoid overloading the server I'm using. – mlinegar May 13 '20 at 19:22
  • are you aware that `mem_alloc` is not reflecting real memory usage? – jangorecki May 13 '20 at 22:59
  • What better approach would you suggest? – Ian Campbell May 13 '20 at 23:00
  • Thanks for the link, I'll check it out. I'm not sure that the effort to implement an accurate test is worth the benefit. Whatever surrogate that `bench::mark` provides at least seems to reflect relative improvements. – Ian Campbell May 13 '20 at 23:07
  • It is not worth, nevertheless it is not making bench::mark memory measure less inaccurate. – jangorecki May 13 '20 at 23:09