Skip to content

Calculating Weeks Since Last Event

2 messages · Abhinaba Roy, jim holtman

Abhinaba Roy
# 
Hi,

I have an input data
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
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
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: