CODEWITHSUNDEEP
r
Seqenenre Tao
Seqenenre Tao

Reputation: 69

Cumulative Columns Based on a Rule in R

I have an incremental development data. Each Dev# columns shows individual payments of the ClaimNo.

ClaimNo<-c("3333","11111","01010","15060")
AccidentPeriod <- c(0,3,1,2)
Information<-c("Infor","Infor","Infor","Infor")
Dev0<-c(100,40,50,0)
Dev1<-c(0,0,100,15)
Dev2<-c(50,0,0,0)
Dev3<-c(0,0,0,0)

IncrementalData<-data.frame(ClaimNo,AccidentPeriod,Information,Dev0,Dev1,Dev2,Dev3)

enter image description here

I need to obtain make it cumulative. Summing the columns should stop at the diagonal. For example we know that we have 4 development periods. When the claim's AccidentPeriod is 2, summing should stop at Dev2 because AccidentPeriod + Dev# cannot exceed 3(we start from 0).

If the AccidentPeriod is 0, summing of Dev# should stop at 3
If the AccidentPeriod is 2, summing of Dev# should stop at 1
If the AccidentPeriod is 1, summing of Dev# should stop  at 2

I can write ifelse function each cumulative columns in order to get this cumulative data. But there are too many columns. There should be a short way..

This is What I need.

enter image description here

If you want to write this on VBA:

DevCum_0=Dev0
for a=1 to 3
b=a-1
DevCum_a=Deva+DevCum_b

Thanks!

Upvotes: 1

Views: 69

Answers (2)

ThomasIsCoding
ThomasIsCoding

Reputation: 101663

You can try the code below with apply

indsDev <- grep("^Dev",names(IncrementalData))
dfout <- cbind(
    IncrementalData[-indsDev],
    t(apply(
        IncrementalData,
        1,
        function(x) {
            x[min(indsDev):(max(indsDev) - as.numeric(x[2]))] <- head(cumsum(x[indsDev]), length(indsDev) - as.numeric(x[2]))
            `class<-`(x[indsDev], "numeric")
        }
    ))
)

such that

> dfout
  ClaimNo AccidentPeriod Information Dev0 Dev1 Dev2 Dev3
1    3333              0       Infor  100  100  150  150
2   11111              3       Infor   40    0    0    0
3   01010              1       Infor   50  150  150    0
4   15060              2       Infor    0   15    0    0

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 389012

We can use apply : 

cols <- grep('Dev', names(IncrementalData), value = TRUE)
last_col <- length(cols) + 1

IncrementalData[cols] <- t(apply(IncrementalData[c('AccidentPeriod', cols)], 1, 
   function(x) {
         if(x[1] == 0) cumsum(x[2:last_col])
         else if(x[1] == 1) c(cumsum(x[2:4]), x[5:last_col])
         else if(x[1] == 2) x[2:last_col]
         else if(x[1] == 3) c(cumsum(x[2:3]), x[4:last_col])
}))

#  ClaimNo AccidentPeriod Information Dev0 Dev1 Dev2 Dev3
#1    3333              0       Infor  100  100  100  100
#2   11111              3       Infor   40   40    0    0
#3   01010              1       Infor   50  150  150    0
#4   15060              2       Infor    0   15    0    0

Upvotes: 1

Related Questions