[R] Calculating Weeks Since Last Event

[jim holtman]

Try this:

################################
# supplied data
library(zoo)  # need the 'na.locf' function

x <- structure(list(ScanDate = structure(c(16433, 16440, 16447, 16454,
                                           16461, 16468, 16475, 16482,
16489, 16496, 16503, 16510, 16517,
                                           16524, 16531, 16538, 16545,
16552, 16559, 16566, 16573, 16580,
                                           16587, 16594, 16601, 16608,
16615, 16622), class = "Date"), OnPromotion =
                      c(0,
                        0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1,
0, 1, 1, 1, 1, 0,
                        0, 0, 1, 1, 1, 1)), .Names = c("ScanDate",
"OnPromotion"), sorted =
                 "ScanDate", class = c("data.table",
                                       "data.frame"), row.names = c(NA, -28L))


# find where the promotions start and then create a flag that indicates when
# the previous promotion started
indx <- which(x$OnPromotion == 1)[1]  # get initial promotion
if (length(indx) == 0) stop('no promtions')  # make sure there is one
in the data

# add a column with the running total of promotions
x$count <- c(rep(0, indx - 1), seq(0, length = nrow(x) - indx + 1))
x$flag <- x$count  # save a copy

# now replace no promotions with NAs so we can use 'na.locf'
indx <- (x$OnPromotion == 0) & (x$count != 0)
x$flag[indx] <- NA
x$flag <- zoo::na.locf(x$flag)

# determine weeks since
x$weeks_since <- ifelse(x$count != 0,
                        x$count - x$flag + 1,
                        0
)

x  # print out the result


##########################


Jim Holtman
Data Munger Guru

What is the problem that you are trying to solve?
Tell me what you want to do, not how you want to do it.


On Fri, Sep 15, 2017 at 5:02 AM, Abhinaba Roy <abhinabaroy09 at gmail.com> wrote:
> Hi,
>
> I have an input data
>
>> dput (input)
>
> structure(list(ScanDate = structure(c(16433, 16440, 16447, 16454,
> 16461, 16468, 16475, 16482, 16489, 16496, 16503, 16510, 16517,
> 16524, 16531, 16538, 16545, 16552, 16559, 16566, 16573, 16580,
> 16587, 16594, 16601, 16608, 16615, 16622), class = "Date"), OnPromotion =
> c(0,
> 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 1, 1, 0,
> 0, 0, 1, 1, 1, 1)), .Names = c("ScanDate", "OnPromotion"), sorted =
> "ScanDate", class = c("data.table",
> "data.frame"), row.names = c(NA, -28L))
>
> I am looking for an output
>
>> dput(output)
>
> structure(list(ScanDate = structure(c(16433, 16440, 16447, 16454,
> 16461, 16468, 16475, 16482, 16489, 16496, 16503, 16510, 16517,
> 16524, 16531, 16538, 16545, 16552, 16559, 16566, 16573, 16580,
> 16587, 16594, 16601, 16608, 16615, 16622), class = "Date"), OnPromotion =
> c(0,
> 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 1, 1, 0,
> 0, 0, 1, 1, 1, 1), Weeks_Since_Last_Promo = c(0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 2, 3, 4, 1,
> 1, 1)), .Names = c("ScanDate", "OnPromotion", "Weeks_Since_Last_Promo"
> ), sorted = "ScanDate", class = c("data.table", "data.frame"), row.names =
> c(NA,
> -28L))
>
> The logic :
>
> The data is weekly.
>
> I want to calculate the number of weeks elapsed since the last promotion
> (OnPromotion : 1 indicates promotion for that week and 0 indicates no
> promotion).
>
> As, there are no promotion initially we set the value for
> 'Weeks_Since_Last_Promo' to 0 (zero). The first promo occurs on
> '2015-03-02' and 'Weeks_Since_Last_Promo' is still 0. Moving to
> '2015-03-09' there was a promotion the week before and so 1 week elapsed
> after the last promo.
>
> If we look at '2015-06-15' then there was a promo 4 weeks back in the week
> of '2015-05-18' and so 'Weeks_Since_Last_Promo' = 4.
>
> How can we do it in R?
>
> Thanks,
> Abhinaba
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.