Generate indicator variable for all days within one week of an event

user_4623441 0
Viewed 170

I have a simple problem which I have only been able to find unattractive solutions to.

I have time series data with days as the unit of analysis. On certain days, an event takes place. I want to create a variable which indicates all dates following one week of the event, and another variable indicating how many days have passed since the event within this week. I am including an example of what I want to achieve below.

         date event week_following_event days_since_event
1  2000-01-01     0                    0               NA
2  2000-01-02     0                    0               NA
3  2000-01-03     1                    0               NA
4  2000-01-04     0                    1                1
5  2000-01-05     0                    1                2
6  2000-01-06     0                    1                3
7  2000-01-07     0                    1                4
8  2000-01-08     0                    1                5
9  2000-01-09     0                    1                6
10 2000-01-10     0                    1                7
11 2000-01-11     0                    0               NA
12 2000-01-12     0                    0               NA
13 2000-01-13     0                    0               NA
14 2000-01-14     0                    0               NA
15 2000-01-15     0                    0               NA

I am pretty sure I could do this by writing a loop, but I am ideally looking for a neater solution.

Here is the dput() output for reproduction purposes:

structure(list(date = structure(c(10957, 10958, 10959, 10960, 
10961, 10962, 10963, 10964, 10965, 10966, 10967, 10968, 10969, 
10970, 10971), class = "Date"), event = c(0, 0, 1, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0), week_following_event = c(0, 0, 0, 1, 
1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0), days_since_event = c(NA, NA, 
NA, 1L, 2L, 3L, 4L, 5L, 6L, 7L, NA, NA, NA, NA, NA)), row.names = c(NA, 
-15L), class = "data.frame")

Any input is much appreciated!

lubridate r date
3 Answers

A base R approach which should also work when you have multiple events

#Initialize values 
df$week_following_event <- 0
df$days_since_event <- NA

#Get index when event occurs
event_days <- which(df$event == 1)

#Get all the index which needs to be changed
week_following_index <- c(sapply(event_days, function(x) (x + 1):(x + 7)))

#Change the values
df$week_following_event[week_following_index] <- 1

# 1:7 would be recycled in case of multiple events
df$days_since_event[week_following_index] <- 1:7

df

#         date event week_following_event days_since_event
#1  2000-01-01     0                    0               NA
#2  2000-01-02     0                    0               NA
#3  2000-01-03     1                    0               NA
#4  2000-01-04     0                    1                1
#5  2000-01-05     0                    1                2
#6  2000-01-06     0                    1                3
#7  2000-01-07     0                    1                4
#8  2000-01-08     0                    1                5
#9  2000-01-09     0                    1                6
#10 2000-01-10     0                    1                7
#11 2000-01-11     0                    0               NA
#12 2000-01-12     0                    0               NA
#13 2000-01-13     0                    0               NA
#14 2000-01-14     0                    0               NA
#15 2000-01-15     0                    0               NA
user_39629140

An alternative approach using dplyr:

df = structure(list(date = structure(c(10957, 10958, 10959, 10960, 10961, 10962, 10963, 10964, 10965, 10966, 10967, 10968, 10969, 10970, 10971), class = "Date"), 
                    event = c(0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), 
                    week_following_event = c(0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0), 
                    days_since_event = c(NA, NA, NA, 1L, 2L, 3L, 4L, 5L, 6L, 7L, NA, NA, NA, NA, NA)), 
               row.names = c(NA, -15L), class = "data.frame")

library(dplyr)

# remove columns (output columns)
df = df %>% select(date, event)

df %>%
  group_by(group = cumsum(event)) %>%                               # group rows based on when event occurs
  mutate(days_since_event = ifelse(group > 0, row_number()-1, NA),  # add days after event only after an event occured
         days_since_event = ifelse(between(days_since_event,1,7), days_since_event, NA),  # keep only up to a week after the event
         week_following_event = ifelse(is.na(days_since_event), 0, 1)) %>%                # add flag for days up to a week after an event
  ungroup() %>%
  select(-group)

Which returns:

# # A tibble: 15 x 4
#   date       event days_since_event week_following_event
#   <date>     <dbl>            <dbl>                <dbl>
# 1 2000-01-01     0               NA                    0
# 2 2000-01-02     0               NA                    0
# 3 2000-01-03     1               NA                    0
# 4 2000-01-04     0                1                    1
# 5 2000-01-05     0                2                    1
# 6 2000-01-06     0                3                    1
# 7 2000-01-07     0                4                    1
# 8 2000-01-08     0                5                    1
# 9 2000-01-09     0                6                    1
#10 2000-01-10     0                7                    1
#11 2000-01-11     0               NA                    0
#12 2000-01-12     0               NA                    0
#13 2000-01-13     0               NA                    0
#14 2000-01-14     0               NA                    0
#15 2000-01-15     0               NA                    0
user_51934850

Here is an option with data.table. Convert the 'data.frame' to 'data.table' (setDT), get the row index for the next 7 rows after the 'event' is 1 ('i1'), using that index, create the 'week_following_event' as 1 (others will be NA by default), grouped by the rleid of non-NA elements in 'week_following_event', create the 'days_since_event' as the sequence of rows

library(data.table)
i1 <- setDT(df)[, sort(unique(pmin(rep(.I[event == 1], each = 7) + 1:7, .N)))]
df[i1, week_following_event := 1
    ][, days_since_event := seq_len(.N) * week_following_event, 
                rleid(!is.na(week_following_event))
     ]#[is.na(week_following_event), week_following_event := 0][] # if needed
#          date event week_following_event days_since_event
# 1: 2000-01-01     0                   NA               NA
# 2: 2000-01-02     0                   NA               NA
# 3: 2000-01-03     1                   NA               NA
# 4: 2000-01-04     0                    1                1
# 5: 2000-01-05     0                    1                2
# 6: 2000-01-06     0                    1                3
# 7: 2000-01-07     0                    1                4
# 8: 2000-01-08     0                    1                5
# 9: 2000-01-09     0                    1                6
#10: 2000-01-10     0                    1                7
#11: 2000-01-11     0                   NA               NA
#12: 2000-01-12     0                   NA               NA
#13: 2000-01-13     0                   NA               NA
#14: 2000-01-14     0                   NA               NA
#15: 2000-01-15     0                   NA               NA

data

df <- structure(list(date = structure(c(10957, 10958, 10959, 10960, 
10961, 10962, 10963, 10964, 10965, 10966, 10967, 10968, 10969, 
10970, 10971), class = "Date"), event = c(0, 0, 1, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0)), row.names = c(NA, -15L), class = "data.frame")
user_37322710
Related