R Studio - How to add count of values in another column in next 7 days?

Question

I have a dataset which contains a list of dates and columns containing R and S. R indicates the regular and S indicates for special

date <- c('01/01', '01/02', '01/03', '01/04', '01/05', '01/06', '01/07', '01/08', '01/09')

day <- c('S', 'S', 'R', 'S', 'R', 'S', 'R', 'R', 'S')

data <- data.frame(date, day)

It looks like this: 

date . . . day

01/01. . . S

01/02. . . S

01/03. . . R

01/04. . . S

01/05. . . R

01/06. . . S

01/07. . . R

01/08. . . R

01/09. . . S

....

I'm trying to add a column now to indicate how many "special" days are coming up in the next 7 days, based on the date. For example, for 01/01, 

I want to know how many cells contain Special days in the upcoming 7 days and store then in a new column. For example, 01/01 should give me 3, from 01/02 to 01/08 there are 'S' days (01/02, 01/04, and 01/06).

I am thinking of using mutate but I am not sure will it work or not. Can anyone help me solve it?

Answer 1

You can convert the column named to 'Date' to 'Date class' by lopping over the 'Date'. Create a logical index between the Data column and the elements of the Data until 7 days using the logical vector hear Day has to be 'S' and get the sum of them.

library(dplyr)

library(purrr)

df1$Date <- as.Date(df1$Date, "%m/%d/%Y")   

df1 %>% 

   mutate(nspecial = map_int(Date, ~ 

       sum(df1$Day == 'S' & between(df1$Date, .x + days(1), .x + days(7)))))

OR

sapply(df1$Date, function(x) sum(df1$Day == 'S' & 

          df1$Date > x & df1$Date <= (x + 7)))

#[1] 3 3 3 2 2 1 1 1 0

The data which I am using is:

df1 <- structure(list(Date = c("01/01/2012", "01/02/2012", "01/03/2012", 

"01/04/2012", "01/05/2012", "01/06/2012", "01/07/2012", "01/08/2012", 

"01/09/2012"), Day = c("S", "S", "R", "S", "R", "S", "R", "R", 

"S")), class = "data.frame", row.names = c(NA, -9L))

 Improve your knowledge in data science from scratch using Data science online courses