I have a data like this:
Brand Time1 Value1 value2
Apple 2015-11 119.7268 3380
Apple 2016-01 336.8033 7710
I want to generate new data like following:
Brand Time1 Time2 LapseMonth value1 value2
Apple 2015-11-01 2015-11-01 0 119.7268 3380
Apple 2015-11-01 2015-12-01 1 286.2842 0
Apple 2015-11-01 2016-01-01 2 286.2842 0
Apple 2015-11-01 2016-02-01 3 267.8142 0
Apple 2015-11-01 2016-03-01 4 286.2842 0
Apple 2015-11-01 2016-04-01 5 277.0492 0
Apple 2015-11-01 2016-05-01 6 286.2842 0
Apple 2015-11-01 2016-06-01 7 277.0492 0
Apple 2015-11-01 2016-07-01 8 286.2842 0
Apple 2015-11-01 2016-08-01 9 286.2842 0
Apple 2015-11-01 2016-09-01 10 277.0492 0
Apple 2015-11-01 2016-10-01 11 286.2842 0
Apple 2015-11-01 2016-11-01 12 157.3224 0
Apple 2016-01-01 2016-01-01 0 336.8033 7710
Apple 2016-01-01 2016-02-01 1 610.9016 0
Apple 2016-01-01 2016-03-01 2 653.0328 0
Apple 2016-01-01 2016-04-01 3 631.9672 0
Apple 2016-01-01 2016-05-01 4 653.0328 0
Apple 2016-01-01 2016-06-01 5 631.9672 0
Apple 2016-01-01 2016-07-01 6 653.0328 0
Apple 2016-01-01 2016-08-01 7 653.0328 0
Apple 2016-01-01 2016-09-01 8 631.9672 0
Apple 2016-01-01 2016-10-01 9 653.0328 0
Apple 2016-01-01 2016-11-01 10 631.9672 0
Apple 2016-01-01 2016-12-01 11 653.0328 0
I explain the new data here:
1. I will generate 2 new columns(Time2 & LapseMonth)
2. I calculate the value1
3. The most important thing is:
If Time1 is 2015 and LapseMonth is 12, value1 = value2 * days_in_month(Time2) / 366 - original value1.
See above, 157.3224 = 3380 * 30 / 366 - 119.7268.
Here is my code:
data$Time1 <- ymd(paste( data$Time1, 01, sep = "-"))
data <- as.data.table(data)
data <- data[,Time1:=as.Date(Time1,"%Y/%m/%d")]
data <- data[,rep:=ifelse(year(Time1)==2016, 12-month(Time1)+1, 13)][rep(1:.N,rep)]
data <- data[,LapseMonth := seq_len(.N)-1, by = Brand,Time1,value2) ]
data <- data[,Time2:= Time1 - days(mday(Time1)-1) + months(LapseMonth)]
data <- data[,`value1`:=ifelse(Time1==Time2,value1,value2*days_in_month(Time2)/366)]
data <- data[,`value2`:=ifelse(Time1==Time2,value2,0)]
I don't know how to use ifelse to do value1 when Time1 is 2015 & LapseMonth is 12.
Any idea?
Aucun commentaire:
Enregistrer un commentaire