0

I have a data frame of the following columns: Customer, Month, Active/Inactive, Total Sales ($). Active/Inactive - 1 if sales >0 in specific month, 0 otherwise. I would like to calculate rolling slope for each customer dependent on whether he's active or not. For example if for Jan-March he was active and then inactive for April and then active again in May- Dec, then each row till March will present rolling slope of his sales up to that month, then 0 in April and then a new rolling slope indicating his activity in May - Dec (ignoring what happened before).

I understand that it might be involving rollapply() or ave(), but that's pretty much it. Thanks in advance!

Followed example with expected outcome

structure(list(X = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,

1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L ), .Label = c("Alex", "Michael"), class = "factor"), Month = structure(c(5L, 4L, 8L, 1L, 9L, 7L, 6L, 2L, 12L, 11L, 10L, 3L, 5L, 4L, 8L, 1L, 9L, 7L, 6L, 2L, 12L, 11L, 10L, 3L), .Label = c("Apr-18", "Aug-18", "Dec-18", "Feb-18", "Jan-18", "Jul-18", "Jun-18", "Mar-18", "May-18", "Nov-18", "Oct-18", "Sep-18"), class = "factor"), Activity = c(1L, 1L, 1L, 0L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 0L, 0L, 1L, 1L, 1L, 0L, 0L, 1L, 1L, 1L), Sales.... = c(12L, 71L, 83L, 0L, 77L, 22L, 32L, 69L, 81L, 98L, 84L, 73L, 24L, 85L, 0L, 0L, 89L, 53L, 66L, 0L, 0L, 35L, 67L, 13L), Expected_Rolling_Slope = c(0, 1.9, 1.22, 0, 0, -1.77, -0.74, -0.05, 0.18, 0.3, 0.26, 0.19, 0.19, 0.11, 0, 0, 0, -1.16, -0.38, 0, 0, 0, 1.03, -0.35)), class = "data.frame", row.names = c(NA, -24L))

Alex
  • 149
  • 1
  • 1
  • 11
  • 2
    Can you provide a small reproducible example with `dput` and expected output – akrun Jul 07 '19 at 15:23
  • `roll <- rollapply(...); transform(DF, roll = ave(..., FUN = roll))` – G. Grothendieck Jul 07 '19 at 15:30
  • @akrun - I've added an example – Alex Jul 07 '19 at 17:48
  • 3
    [See here](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) on making an R question that folks can help with. That includes a sample of data (not a picture of it), all necessary code, and a clear explanation of what you're trying to do and what hasn't worked. – camille Jul 07 '19 at 18:11
  • For some more context against images of code/data, consider: it cannot be copied or searched (SEO), it breaks screen-readers, and it may not fit well on some mobile devices. Ref: https://meta.stackoverflow.com/a/285557/3358272 (and https://xkcd.com/2116/). Plus you're asking us to transcribe data from a picture, when it is much easier/faster for you to provide a reproducible and unambiguous data sample, such as the output from `dput(head(x,n=20))` (or however you subset/filter on `x` to get that data). – r2evans Jul 08 '19 at 00:22
  • I've added dput with example and expected result – Alex Jul 08 '19 at 15:57

0 Answers0