Time since event with multiple events-CodePudding

I have a data set that has event dates at multiple locations:

year<-rep(2010:2021,3)
month<-rep(1:3,12)
loc<-rep(letters[1:3],each=12)
event_date<-as.Date(paste(year,month, "01"), "%Y%m%d")
event_data<-data.frame(loc,year,event_date)

I want to expand the data set so for each month of the year I have a time since event variable for each location (preferably in months but days is fine). I have tried below but there are minus values at the change of the years and I want to continue on with the time since event until the event in the follow year (no minus values)

months<-expand.grid(year=unique(year),month=1:12)
month_data<-left_join(event_data, months, by = "year")
month_data$date<-as.Date(paste(month_data$year,month_data$month, "01"), "%Y%m%d")
month_data$diff<-month_data$date-month_data$event_date

CodePudding user response：

May not be the neatest but this has done the trick. Instead of expand.grid I have written a loop that creates a sequence of dates by month between each event date :

res<-list()
r_list<-list()


for(i in unique(event_data$loc)) {
  
  event_data1<-event_data[event_data$loc==i,] # outer loop splitting dataset by location 
  
  
  for (j in 1:length(event_data1$event_date)) {
    if (is.na(event_data1$event_date[j 1]) == TRUE) {
      dates<-as.Date(seq(event_data1$event_date[j],(event_data1$event_date[j] 365), by = 'month')) # seq[j 1] fails as there is no date after the last one. Adding a years worth of months 
      dates<-head(dates, -1)
      camp<-rep(year(dates)[1], length(dates))
      diff<- 0:(length(camp)-1) 
      loc<-rep(event_data1$loc[j], length(dates))
      r_list[[j]]<-data.frame(dates,camp,diff,loc)
      
    }
    else {dates<-as.Date(seq(event_data1$event_date[j],event_data1$event_date[j 1], by = 'month')) # sequence from one event to another by month 
    dates<-head(dates, -1) # the last date in the seq is in fact the next event so needs removed 
    camp<-rep(year(dates)[1], length(dates)) # year of event  
    diff<- 0:(length(camp)-1)  # Months since event 
    loc<-rep(event_data1$loc[j], length(dates)) # location 
    r_list[[j]]<-data.frame(dates,camp,diff,loc)
   
    }
   
  }
  res<-c(res,r_list) 
}

data_new<-do.call("rbind",(res))