CODEWITHSUNDEEP
rdata.table
MaliUk Bager
MaliUk Bager

Reputation: 35

How to use efficiently data.table for this problem? Please look at new version

I have a data and I need to subtract a value from another data.minus from top to bottom (by id and variables x1, x2,..xn).

library(data.table)
data <- data.table(id=c(rep("A",5),rep("B",5)),n=c(rep(1:5,2)), x1=c(1, 0, 0, 4, 5, 2, 0, 0, 8, 10), x2=c(rep(0,3), 5,5, rep(0,3),10,10))
data.sub <- data.table(id=c("A","B"), x1.sub=c(8,5), x2.sub=c(7,3))

I got data.after for id="A" with this code: Old version with Bug for id=="B"

{
xx <- data[id=="A",]$x1
yy <- data.sub[id=="A",]$x1.sub
qq <- cumsum(xx)-yy
x1.after <- c(rep(0,which(qq>0)-1),xx[which(qq>0):length(xx)])
x1.subs <- x1.after-xx

data.after <- data.frame(id=rep("A",length(xx)), x1=x1.subs)
}

New version haven't bugs

xx <- data[id=="A",]$x1
yy <- data.sub[id=="A",]$x1.sub
qq <- cumsum(xx)-yy

index <- intersect(which(!qq<0), which(xx>0))[1]

x1.after <- c(rep(0,index-1),qq[index], xx[(index+1):length(xx)])[1:length(xx)]
x1.subs <- x1.after-xx

data.after <- data.frame(id=rep("A",length(xx)), x1=x1.subs)
data.after

But I have many x-variables: x1,x2,...,xn and many id's and problem is very complex. I do not have good idea how to solve effectively this problem with data.table. Help me please.

Code from akrun adopted for New version:

v1 <- grep('x\\d+', names(data), value = TRUE)
v2 <- paste0(v1, ".sub")
data[data.sub, Map(function(x, y) {
  qq <- cumsum(x) -y
  i1<- intersect(which(!qq<0), which(x>0))[1]
  
  after <- c(rep(0, i1-1), qq[i1], x[(i1+1):.N])[1:length(x)]
  return(after - x)   
},      
mget(v1), mget(v2)) , on = .(id), by = .EACHI]

Upvotes: 1

Views: 48

Answers (1)

akrun
akrun

Reputation: 887531

An option will be a join on by the 'id'

v1 <- grep('x\\d+', names(data), value = TRUE)
v2 <- paste0(v1, ".sub")
data[data.sub, Map(function(x, y) {
        qq <- cumsum(x) -y
       i1 <- which(qq > 0)[1]
       after <- c(rep(0, i1-1),x[i1:.N])
     return(after - x)   
     },      
    mget(v1), mget(v2)) , on = .(id), by = .EACHI]

Upvotes: 2

Related Questions