title: Transferring an Excel Moving Average Calculator to R
body: <p>so I've been trying to teach myself R and have a calculator I created in excel that I'm trying to get working in R.</p>
  <p>In R, I'd like to calculate a 45 day moving average based on loan prices I have. The data sample in excel is here:</p>
  <p><a href="https://i.stack.imgur.com/Lx5P7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Lx5P7.png" alt="enter image description here" /></a></p>
  <p>dput output in R is here:</p>
  <pre><code>    &gt; dput(data)
  structure(list(`usd price` = c(50000, 60000, 40000, 35000, 1e+05, 
  95000), `cad price` = c(62500, 75000, 50000, 43750, 125000, 118750
  ), day = structure(c(1642118400, 1641772800, 1639958400, 1639785600, 
  1638316800, 1640995200), class = c(&quot;POSIXct&quot;, &quot;POSIXt&quot;), tzone = &quot;UTC&quot;), 
      Loan = c(TRUE, TRUE, TRUE, TRUE, TRUE, FALSE)), class = c(&quot;tbl_df&quot;, 
  &quot;tbl&quot;, &quot;data.frame&quot;), row.names = c(NA, -6L))
  </code></pre>
  <p>In excel, I can do the following formulas (in yellow) to get the correct 45 day moving average result (in blue):
  <a href="https://i.stack.imgur.com/l2fzx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/l2fzx.png" alt="excel formulas" /></a></p>
  <p>The written out logic is:</p>
  <ol>
  <li>Filter out &quot;Loan&quot; = FALSE values</li>
  <li>Filter out the outliers defined as the top 25% of the previous 45 day values of &quot;usd price&quot; (including the day of)</li>
  <li>Calculate the 45 day average of USD price for the remaining values</li>
  </ol>
  <p>Hopefully this makes sense so far. I am having trouble filtering out the top 25% of values in the 45 day set for each row. Here's my R code so far:</p>
  <pre><code>data &lt;- data %&gt;% mutate(day=lubridate::parse_date_time(day, &quot;ymd&quot;))
  
  data = data %&gt;%
    filter(Loan==&quot;TRUE&quot;)%&gt;%
    mutate(fourtyfive_avg= rollmean('usd_price',45,
                               align=&quot;right&quot;,
                               fill=0)) %&gt;%
    relocate(fourtyfive_avg)
  </code></pre>
  <p>This gives me MA's for each record which I want, but I need to filter out the top quartile before making the MA calculation.</p>