title: In R, how do I filter a dataframe based on differences between dates in rows?
body: <p>Let's say I have the following data frame:</p>
  <pre><code>library(tidyverse)
  
  # generate data frame
  id &lt;- c(1,1,1,1,2,2,3,4,5,5,5,5,1,1,1,1) # User ID
  date &lt;- c(&quot;2021-12-26&quot;, &quot;2021-12-19&quot;, &quot;2021-12-15&quot;, &quot;2021-12-07&quot;, &quot;2021-11-11&quot;, &quot;2021-11-05&quot;, &quot;2021-09-17&quot;,&quot;2021-09-17&quot;, &quot;2021-10-08&quot;, &quot;2021-10-06&quot;, &quot;2021-10-01&quot;, &quot;2021-09-30&quot;, &quot;2022-01-30&quot;, &quot;2022-01-24&quot;, &quot;2022-01-18&quot;, &quot;2022-01-13&quot;) # Date the form was submitted
  variable1 &lt;- c(10, NA, NA, NA, 8, NA, 7, 6, 9, NA, NA, NA, 6, 8, NA, NA)
  variable2 &lt;- c(5,2,3,4,6,7,8,9,1,4,3,2,5,6,5,4)
  sample_data &lt;- data.frame(id, date, variable1, variable2)
  sample_data &lt;- sample_data %&gt;%
    mutate(date=as.Date(date, format=&quot;%Y-%m-%d&quot;))
  
  #   id       date variable1 variable2
  #1   1 2021-12-26        10         5
  #2   1 2021-12-19        NA         2
  #3   1 2021-12-15        NA         3
  #4   1 2021-12-07        NA         4
  #5   2 2021-11-11         8         6
  #6   2 2021-11-05        NA         7
  #7   3 2021-09-17         7         8
  #8   4 2021-09-17         6         9
  #9   5 2021-10-08         9         1
  #10  5 2021-10-06        NA         4
  #11  5 2021-10-01        NA         3
  #12  5 2021-09-30        NA         2
  #13  1 2022-01-30         6         5
  #14  1 2022-01-24         8         6
  #15  1 2022-01-18        NA         5
  #16  1 2022-01-13        NA         4
  
  # get the date from entries with variable1
  sample_data &lt;- sample_data %&gt;%
    mutate(date2=as.Date(ifelse(!is.na(variable1),paste(date,sep=&quot;&quot;), NA)))
  </code></pre>
  <p>in which each row is a separate entry (e.g. a form submission), <strong>id</strong> corresponds to the user id (which is unique to the user, but each user may have more than one entry), <strong>date</strong> is the date of the entry, and <strong>variable1</strong> and <strong>variable2</strong> are two different variables — while <strong>variable2</strong> is required (i.e. no missing values), <strong>variable1</strong> isn't.</p>
  <p>I want to compare <strong>variable1</strong> with <strong>variable2</strong> over the 4 weeks prior to <strong>variable1</strong> for each user, <em>as long as there is at least one entry for each week during those 4 weeks</em>. I'm kind of puzzled on how to do that.</p>
  <p>Basically, I think I should filter the data frame for users with at least one valid <strong>variable1</strong> entry, get the date from this entry (<strong>date2</strong> column), and then get all the entries for the 28 days (7 days × 4 weeks) prior to this date. Finally, because I'm only interested in data from users who have <em>at least</em> one entry <strong>each week</strong> during those 28 days, I need to filter those users who have at least one entry for the <em>date2 to date2 - 7 days</em> range, one entry for the <em>date2 - 7 to date2 - 14</em> range, one entry for the <em>date2 - 14 to date2 - 21</em> range, and one entry for the <em>date2 - 21 to date2 - 28</em> range).</p>
  <p>The problem is, I've been using R for a few months &quot;only&quot;, and I have no idea how to approach this problem code-wise.</p>
  <p>Does anyone know of the best way to do this?</p>
  <p>Thanks in advance</p>