Data binding. table
What is the best way to do the following:
Let's say I want to find out the index of all rows that have user_id <0, or even say that user_id has an average sales> 50.
I first create an indexed data table and then filter with what is essentially another data table. Is there a more efficient way?
In this example I want to have index 2 ie the index of the row where user_id is <0
x<-data.table(user_id=c(1,-1,2,3),iqlevel=c(40,50,60,70))
x[,I:=.I,][user_id<0,I,]
+3
source to share
2 answers
I would have another answer:
x[user_id<0, which=TRUE]
And run for a while with my helper tool:
library(data.table) # v1.9.5
# devtools::install_github("jangorecki/dtq")
# drat::addRepo("jangorecki"); install.packages("dtq")
library(dtq)
op <- data.table(user_id=c(1,-1,2,3),iqlevel=c(40,50,60,70))
sheffien <- copy(op)
jan <- copy(op)
r1 <- op[,I:=.I,][user_id<0,I,]
r2 <- sheffien[,.I[which(user_id<0)] ]
r3 <- jan[user_id<0, which=TRUE]
identical(r1,r2)
# [1] TRUE
identical(r2,r3)
# [1] TRUE
dtcalls <- dtl(print=TRUE) # collect logs
print(dtcalls)
# seq dtq_id dtq_seq src query timestamp env elapsed in_rows out_rows
# 1: 1 1 1 op [j = `:=`(I, .I)] 2015-08-07 14:01:10 R_GlobalEnv 0.001718847 4 4
# 2: 2 1 2 op [i = user_id < 0, j = I] 2015-08-07 14:01:10 R_GlobalEnv 0.008719112 4 NA
# 3: 3 2 1 sheffien [j = .I[which(user_id < 0)]] 2015-08-07 14:01:10 R_GlobalEnv 0.000662418 4 NA
# 4: 4 3 1 jan [i = user_id < 0, which = TRUE] 2015-08-07 14:01:10 R_GlobalEnv 0.000281067 4 NA
dtcalls[,.(query=paste(query, collapse=""), sec=sum(elapsed)),.(src)]
# src query sec
# 1: op [j = `:=`(I, .I)][i = user_id < 0, j = I] 0.010437959
# 2: sheffien [j = .I[which(user_id < 0)]] 0.000662418
# 3: jan [i = user_id < 0, which = TRUE] 0.000281067
Remember that this is just a one-time job on 4 pages of data.table, so be sure to check your data speed.
+3
source to share