Delete rows by reference

Submitted by: Matt Dowle; Assigned to: Nobody; R-Forge link

Since deleting 1 column is DT[,colname:=NULL], and deleting rows is the same as deleting all columns for those rows, and we wish to use hierarchical indexes to find the rows to delete by reference, we just need a LHS to indicate "all" columns, leading to :

 DT[i,.:=NULL]   # delete rows by reference

 DT[,.:=NULL]    # error("must specify i to delete rows. To delete all rows from a table use DT[TRUE,.:=NULL], or, DT=DT[0].  This is deliberately a little harder, to avoid accidents such as "delete from table" a coomon accident in SQL.")

We can also add an attribute "read only" or "protect" to a data.table, and if the user had protected the data.table in that way, .:= would not work on it.

Subset data table without using <-

http://stackoverflow.com/questions/10790204/how-to-delete-a-row-by-reference-in-r-data-table

Just delete by reference is not that hard. The benefit would be mainly memory efficiency rather than speed so much.

How about adding both
delete(DT, b>=8 | a<=3)
and
DT[b>=8 | a<=8, .ROW:=NULL]
The advantage of the latter would be combining with other features of [] such as row numbers in i, join in i and roll. All benefiting from [i,j,by] optimization.
As per : http://stackoverflow.com/questions/10790204/how-to-delete-a-row-by-reference-in-r-data-table/10791729?noredirect=1#comment54633906_10791729

More advanced example :

DT[ b>=8, .SD[1, .ROW:=NULL], by=group]
# remove by reference the 1st observation in each group within a subset

Is .ROW the right name for this new symbol?

Re right name: doesn't .SD already carry the right meaning for that (instead of introducing a new name a la .ROW)?

I think syntax for selecting rows to keep (which just deletes their complement) would be convenient.

delete(DT, b >= 8 | a <= 3) # or
keep(  DT, b <  8 & a >  3)

I don't know that there's a sensible way to extend this logic to work inside j. I'd just as well have Matt's second example only work via

badrows = DT[b >= 8, .I[1], by=g]$V1
delete(DT, badrows)

Just as new columns cannot be created by set (last I checked), it could be that row modifications cannot be done inside [.data.table.

if anyone needs a quick-and-dirty solution, as I did, here is a memory-efficient function to select rows for each col then delete by reference based on a SO answer by vc273.

## ---- Deleting rows by reference using data.table*
## ---- *not exactly!

# Example dt
DT = data.table(col1 = 1:1e6)
cols = paste0('col', 2:100)
for (col in cols){ DT[, col := 1:1e6, with = F] }
keep.idxs = sample(1e6, 9e4, FALSE) # keep 90% of

delete <- function(DT, keep.idxs){
cols <- copy(names(DT))
DT_subset <- DT[[1]][keep.idxs] %>% as.data.table
setnames(DT_subset, ".", cols[1])
for (col in cols){
  DT_subset[, (col) := DT[[col]][keep.idxs]]
  set(DT, NULL, col, NULL)
}
return(DT_subset)
}

str(delete(DT, keep.idxs))
str(DT)

@andrewrech

@andrewrech I can't get your code to work. I'm on the dev version of data.table, and when I run your code, I end up with an empty data.table:

> dim(d1)
[1] 0 0

@andrewrech

To complement @andrewrech's answer. Here is code as function and example of its usage.

delete <- function(DT, del.idxs) {           # pls note 'del.idxs' vs. 'keep.idxs'
  keep.idxs <- setdiff(DT[, .I], del.idxs);  # select row indexes to keep
  cols = names(DT);
  DT.subset <- data.table(DT[[1]][keep.idxs]); # this is the subsetted table
  setnames(DT.subset, cols[1]);
  for (col in cols[2:length(cols)]) {
    DT.subset[, (col) := DT[[col]][keep.idxs]];
    DT[, (col) := NULL];  # delete
  }
   return(DT.subset);
}

And example of its usage:

dat <- delete(dat, del.idxs)

Where "dat" is a data.table. Removing 14k rows from 1.4M rows takes 0.25 sec on my laptop.

> dim(dat)
[1] 1419393      25
> system.time(dat <- delete(dat,del.idxs))
   user  system elapsed 
   0.23    0.02    0.25 
> dim(dat)
[1] 1404715      25
>

This is my very first GitHub post, btw.

@jarno-p

What kind of work needs to be done in order to add this functionality to data.table ? Would be glad to help, but not totally sure where to start !

The delete function could be added using @jarno-p awnser and later on modified to be more efficient and works with [] references, don't you think ?

Proof of concept based on #4345 (comment)

setsubset = function(x, i) {
  stopifnot(is.data.table(x), is.integer(i))
  if (!length(i)) return(x)
  if (anyNA(i) || anyDuplicated(i) || any(i < 1L || i > nrow(x) || is.unsorted(i))) stop("i must be non-NA, no dups, in range of 1:nrow(x) and sorted")
  drop = setdiff(1:nrow(x), i)
  last_ii = drop[1L]-1L
  do_i = i[i > last_ii]
  for (ii in do_i) {
    last_ii = last_ii+1L
    set(x, last_ii, names(x), as.list(x[ii]))
  }
  ## we need to set true length here but this needs C
  invisible(x)
}

x = data.table(a = 1:8, b = 8:1)
X = copy(x)
i = c(1:2, 6:7)
address(x)
sapply(x, address)
setsubset(x, i)
address(x)
sapply(x, address)
all.equal(x[seq_along(i)], X[i])

x = data.table(a = 1:8, b = 8:1)
X = copy(x)
i = c(3L, 5L, 7L)
address(x)
sapply(x, address)
setsubset(x, i)
address(x)
sapply(x, address)
all.equal(x[seq_along(i)], X[i])

working example using setsubset branch

library(data.table)
setsubset = data.table:::setsubset

x = data.table(a = 1:8, b = 8:1)
X = copy(x)
i = c(1:2, 6:7)
x
#       a     b
#   <int> <int>
#1:     1     8
#2:     2     7
#3:     3     6
#4:     4     5
#5:     5     4
#6:     6     3
#7:     7     2
#8:     8     1
mem = c(address(x), sapply(x, address))
setsubset(x, i)
x
#       a     b
#   <int> <int>
#1:     1     8
#2:     2     7
#3:     6     3
#4:     7     2
all.equal(x, X[i])
#[1] TRUE
all.equal(c(address(x), sapply(x, address)), mem)
#[1] TRUE

x = data.table(a = 1:8, b = 8:1)
X = copy(x)
i = c(3L, 5L, 7L)
x
#       a     b
#   <int> <int>
#1:     1     8
#2:     2     7
#3:     3     6
#4:     4     5
#5:     5     4
#6:     6     3
#7:     7     2
#8:     8     1
mem = c(address(x), sapply(x, address))
setsubset(x, i)
x
#       a     b
#   <int> <int>
#1:     3     6
#2:     5     4
#3:     7     2
all.equal(x, X[i])
#[1] TRUE
all.equal(c(address(x), sapply(x, address)), mem)
#[1] TRUE

Cool!

I'm wondering if the set loop can be avoided along these lines:

x[i, .keep := TRUE]
setorder(x, .keep, na.last=TRUE)
# set truelength, drop .keep

Or if setorder isn't reliably order-preserving (the documentation doesn't advertise it):

x[, .old_index := .I]
x[i, .keep := TRUE]
setorder(x, .keep, .old_index, na.last=TRUE)
# set truelength, drop .keep, .old_index

It looks like memory addresses survive these operations (?).

x = data.table(a = 1:8, b = 8:1)
X = copy(x)
i = c(1:2, 6:7)

mem = c(address(x), sapply(x, address))
x[i, .keep := TRUE]
setorder(x, .keep, na.last=TRUE)
x[, .keep := NULL]
all.equal(x[seq_along(i)], X[i])
all.equal(c(address(x), sapply(x, address)), mem)

If the current approach is necessary, I think you could swap...

  drop = setdiff(seq_len(nrow(x)), i)
  last_ii = drop[1L]-1L

for something like

  first_drop = match(FALSE, seq_along(i) == i, nomatch = tail(i, 1L)+1L)
  last_ii = first_drop - 1L

Why? Speed (avoiding setdiff and scaling with nrow(x)) and handling the edge case where all rows are kept

Wonderful idea!
Would you like to take over the branch?
Ultimately we want this functionality to be inside [ rather than just new function...

In case of updates, you will see comments here, or in exists, in a linked PR.

BTW. I have impression that many readers misunderstood benefits of this function. It will most likely be slower than copy as it has to be made now. It will not have to allocate memory for extra copy of data.table, but that allocated memory is released just after assignment, so it is really relevant only when you run into OOM error, that could be avoidable if your data would be twice smaller.

@ben-schwen

@ben-schwen has the recent update to move growable vectors inside the API for R changed what's possible here (for better or worse)?

@ben-schwen has the recent update to move growable vectors inside the API for R changed what's possible here (for better or worse)?

Neither, I would say. The new resizable vectors API now allows allocating larger vectors and resizing them in-place, smth we have down since a long time "ab"using TRUELENGTH.

To implement this feature cleanly we would need to maintain a row index either as

attribute (volatile since we now that a lot of attributes get swiped)
sidecar state
the representation itself e.g. via ALTREP

where I currently would favor 2) or 3).

If we want only to delete rows and not add by reference then maintaining index is not needed. We just shrink each column after moving its data to front.

I just wrote a POC in C for this. As pointed out by Matt, speed will be the same. Used memory will be roughly half.

N = 1e6
dt1 = data.table(x=seq.int(N), y=sample(letters, N, TRUE))
dt2 = copy(dt1)

del1 = function(x,n) for (i in seq(n,1)) x=x[-i]
del2 = function(x,n) for (i in seq(n,1)) x[i, .ROW := NULL]

bench::mark(
    base = del1(dt1, 100),
    data.table = del2(dt2, 100),
)
# # A tibble: 2 × 13
#   expression      min median `itr/sec` mem_alloc `gc/sec` n_itr  n_gc total_time
#   <bch:expr> <bch:tm> <bch:>     <dbl> <bch:byt>    <dbl> <int> <dbl>   <bch:tm>
# 1 base          680ms  680ms      1.47    1.63GB     57.4     1    39      680ms
# 2 data.table    673ms  673ms      1.48  782.17MB     23.8     1    16      673ms
# # ℹ 4 more variables: result <list>, memory <list>, time <list>, gc <list>

Benchmarking with cgmemtime shows exactly the same ratio of saving half the memory (as expected).

Navigation Menu

Repository navigation

Delete rows by reference #635

7 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Delete rows by reference #635

Description

Activity

Co0olCat commented on May 7, 2015

zx8754 commented on Oct 1, 2015

mattdowle commented on Oct 1, 2015

mattdowle commented on Oct 1, 2015

mattdowle commented on Oct 29, 2015

mattdowle commented on Oct 29, 2015

eantonya commented on Oct 29, 2015

franknarf1 commented on Oct 29, 2015

andrewrech commented on Aug 23, 2016

vinhdizzo commented on Aug 25, 2016

jarppiko commented on Nov 18, 2016

juanpide commented on Jan 9, 2017

vikram-rawat commented on Aug 16, 2017

MiloParigi commented on Jan 3, 2018

7 remaining items

jangorecki commented on Apr 7, 2020

jangorecki commented on Nov 17, 2022

franknarf1 commented on Nov 17, 2022

jangorecki commented on Nov 17, 2022

michaelsimmler commented on Oct 23, 2023

waynelapierre commented on Nov 16, 2023

jangorecki commented on Nov 16, 2023

MichaelChirico commented on Dec 26, 2025

ben-schwen commented on Dec 26, 2025

jangorecki commented on Dec 27, 2025

ben-schwen commented on Dec 27, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions