Calculating Weeks Since Last Event

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Calculating Weeks Since Last Event

abhinabaroy09
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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Calculating Weeks Since Last Event

jholtman
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 <[hidden email]> 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]]
>
> ______________________________________________
> [hidden email] 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.

______________________________________________
[hidden email] 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.