CODEWITHSUNDEEP
rdata.table
Andrey Chetverikov
Andrey Chetverikov

Reputation: 558

remove rows that are same on one column but different on another from a data.table

I have a data.table like this:

dt<-data.table(v1=rep(c('a','b','c'),4), v2=rep(c(1,2),6))
    v1 v2
 1:  a  1
 2:  b  2
 3:  c  1
 4:  a  2
 5:  b  1
 6:  c  2
 7:  a  1
 8:  b  2
 9:  c  1
10:  a  2
11:  b  1
12:  c  2

I need to remove all rows that have the same v1 but different v2 (except the first row in each combination of v1 and v2). In this example, rows 4-6 and 10-12 should be removed. How can I do this?

Upvotes: 3

Views: 256

Answers (3)

Arun
Arun

Reputation: 118879

How about this?

tmp = dt[dt[, list(I=.I[1]), by=list(v1)]$I]
setkey(dt)[tmp]
   v1 v2
1:  a  1
2:  a  1
3:  b  2
4:  b  2
5:  c  1
6:  c  1

Bigger data and benchmarking:

# create some data
require(data.table)
require(microbenchmark)
set.seed(1)
ff <- function() paste0(sample(letters, sample(5:8, 1), TRUE), collapse="")
ll <- unique(replicate(1e4, ff()))
DT <- data.table(v1=sample(ll, 1e6, TRUE), v2=sample(1:1e4, 1e6, TRUE))

# add functions
eddi <- function(dt=copy(DT)) {
    dt[, list(v2=v2[v2 == v2[1]]), by = v1]
}

andrey <- function(dt=copy(DT)) {
    dt[, .SD[v2 == v2[1],], by = v1]
}

arun <- function(dt=copy(DT)) {
    tmp = dt[dt[, list(I=.I[1]), by=list(v1)]$I]
    setkey(dt)[tmp]
}

# benchmark    
microbenchmark(a1 <- eddi(), a2 <- andrey(), a3 <- arun(), times=2)
Unit: milliseconds
           expr       min        lq    median        uq       max neval
   a1 <- eddi()  342.4429  342.4429  348.1604  353.8780  353.8780     2
 a2 <- andrey() 5810.8947 5810.8947 5829.0742 5847.2537 5847.2537     2
   a3 <- arun()  494.6861  494.6861  509.3022  523.9182  523.9182     2

setkey(a3, NULL)
> identical(a1, a2) # [1] TRUE
> identical(a1, a3) # [1] TRUE

Upvotes: 2

TheComeOnMan
TheComeOnMan

Reputation: 12905

You could try using the mult argument. I'm not sure if the setkeyv will affect the rows that you are selecting though, please check that before you use it -

setkeyv(dt,c('v1'))
firstocc <- dt[unique(dt),,mult="first"][,v2.1 := NULL]

setkeyv(dt,c('v1','v2'))
setkeyv(firstocc,c('v1','v2'))
dt[firstocc]

Upvotes: 1

eddi
eddi

Reputation: 49448

This works I think:

dt[, v2[v2 == v2[1]], by = v1]
#   v1 V1
#1:  a  1
#2:  a  1
#3:  b  2
#4:  b  2
#5:  c  1
#6:  c  1

Upvotes: 3

Related Questions