title: Determine overlapping ranges - R
body: <p>I have two dataframes. One with purchases during a month, one with the advertisements (ads) that were broadcassted in that month. To understand whether a purchase can be credibly linked to an ad – I want to understand how many of the purchase dates come within 4 days after an advertisement. I created some (cumbersome) code to do this – which is based on expansion of each row of the advertisement database to cover the relevant 4 day period – and then use a merge construct to see where is a (lack of) overlap. This feels like a very cumbersome ways to do things. Ideally – I would have liked to do this in dplyr in an elegant manner. let me know if anyone has any suggestions </p>
  
  <pre><code>library(dplyr)
  library(lubridate)
  require(data.table)
  
  # set start and end dates to sample between
  day.start &lt;- "2007/01/01"
  day.end &lt;- "2007/01/30"
  
  set.seed(1) # define a random date/time selection function
  rand.day.time &lt;- function(day.start,day.end,size) {
    dayseq &lt;- seq.Date(as.Date(day.start),as.Date(day.end),by="day")
    dayselect &lt;- sample(dayseq,size,replace=TRUE)
    as.POSIXlt(paste(dayselect) )
  }
  
  dateval=rand.day.time(day.start,day.end,size=20)
  
  ###create initial dataframes
  action=rep(c("ad","purchase"),10)
  id=rep(c(1,1,2,2),5)
  df=data.frame(customer=id,date=dateval,action=action)
  df_pur=filter(df,action=="purchase");(df_pur=df_pur[order(df_pur$date),])
  df_ad=filter(df,action=="ad");(df_ad=df_ad[order(df_ad$date),])
  
  #expand data-frame to include all the ranges for which the ad might trigger purchases
  df_ad_exp = df_ad %&gt;%
    group_by(customer,date) %&gt;%
    summarize(start=min(date),end=min(date+days(4))) 
  df_ad_exp=as.data.frame(df_ad_exp)
  df_ad_exp2=setDT(df_ad_exp)[, list(customer=customer, range=seq(start,end,by="day")), by=1:nrow(df_ad_exp)]
  
  ###merge the dataframe, use NA values to identify those dates in which purchase was made but no ad was "active"
  df_ad_exp2=as.data.frame(df_ad_exp2)
  (df_ad_exp2=df_ad_exp2[,c("customer","range")])
  df_ad_exp2$helpercol=0
  (df_pur_m=merge(df_pur,df_ad_exp2,by.x=c("date","customer"),by.y=c("range","customer"),all.x=TRUE))
  
  df_pur_m$ad_in_range=df_pur_m$helpercol;df_pur_m$helpercol=NULL
  df_pur_m$ad_in_range[!is.na(df_pur_m$ad_in_range)]=1;df_pur_m$ad_in_range[is.na(df_pur_m$ad_in_range)]=0
  
  #outcomes
  df_pur
  df_ad
  df_pur_m
  
  &gt; df_ad
     customer       date action
  3         1 2007-01-07     ad
  6         2 2007-01-07     ad
  1         1 2007-01-08     ad
  10        2 2007-01-12     ad
  2         2 2007-01-18     ad
  5         1 2007-01-19     ad
  7         1 2007-01-21     ad
  9         1 2007-01-22     ad
  8         2 2007-01-24     ad
  4         2 2007-01-29     ad
  &gt; df_pur_m
           date customer   action ad_in_range
  1  2007-01-02        1 purchase           0
  2  2007-01-06        2 purchase           0
  3  2007-01-12        1 purchase           1
  4  2007-01-12        1 purchase           1
  5  2007-01-15        2 purchase           1
  6  2007-01-20        2 purchase           1
  7  2007-01-24        2 purchase           1
  8  2007-01-27        1 purchase           0
  9  2007-01-28        2 purchase           1
  10 2007-01-30        1 purchase           0
  </code></pre>