title: How can I merge two dataframes based on a condition?
body: <p>This is the followup question of my question <a href="https://stackoverflow.com/questions/63689198/how-can-i-keep-track-of-total-transaction-amount-sent-from-an-account-each-last">here:</a></p>
  <p>This is my transaction data</p>
  <pre><code>data 
  
  id          from    to          date        amount  
  &lt;int&gt;       &lt;fctr&gt;  &lt;fctr&gt;      &lt;date&gt;      &lt;dbl&gt;
  19521       6644    6934        2005-01-01  700.0
  19524       6753    8456        2005-01-01  600.0
  19523       9242    9333        2005-01-01  1000.0
  …           …       …           …           …
  1055597     9866    9736        2010-12-31  278.9
  1053519     9868    8644        2010-12-31  242.8
  1052790     9869    8399        2010-12-31  372.2
  </code></pre>
  <p>Now, for each account in the <code>from</code> column, I want to compute how much transaction amount they received over last 6 month at the time the transaction was made. To do this:</p>
  <pre><code>df &lt;- data # df is just a copy of &quot;data&quot;
  setDT(df)[, total_trx_amount_received_in_last_6month := sapply(date, function(x) 
                           sum(amount[between(date, x-180, x)])), to] 
  
  # since I want to merge &quot;df&quot; and &quot;data&quot; based on the columns &quot;from&quot; and &quot;date&quot;, I change the name of the column &quot;to&quot; and make it &quot;from&quot;
  df &lt;- select(df, to,date,total_trx_amount_received_in_last_6month) %&gt;% rename(from=to)
  
  df
  
  from    date        total_trx_amount_received_in_last_6month
  &lt;fctr&gt;  &lt;date&gt;      &lt;dbl&gt;
  7468    2005-01-04  700.0       
  6213    2005-01-08  12032.0     
  7517    2005-01-10  1000.0      
  6143    2005-01-12  4976.0      
  6254    2005-01-14  200.0       
  6669    2005-01-20  200.0       
  6934    2005-01-24  72160.0     
  9240    2005-01-26  21061.0     
  6374    2005-01-30  1000.0      
  6143    2005-01-31  4989.4  
  </code></pre>
  <p>Now I want to add this new column <code>total_trx_amount_received_in_last_6month</code> into the original <code>data</code>. So, I should merge these two dataframes <code>data</code> and <code>df</code> by the columns <code>from</code> and <code>date</code> but the matching criteria for date is a range of values, not a single value. For example for account <code>7468</code> if the original <code>data</code> contains a transaction <code>7468</code> made and the transaction date falls into interval of <code>&quot;2004-07-08&quot;-&quot;2005-01-04&quot;</code>(which is the period of last 6 months, starting from <code>&quot;2005-01-04&quot;</code>), then corresponding value <code>700.0</code> in <code>df$total_trx_amount_received_in_last_6month</code> should be added to the <code>data$total_trx_amount_received_in_last_6month</code></p>
  <p>How can I do that?</p>