lundi 24 avril 2017

ifelse with three conditions

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