Apply the moving average with a specific condition and a rolling window in R

Question

df <- structure(
  list(
    inv = c("INV_1", "INV_1", "INV_1", "INV_1", "INV_1", "INV_2", "INV_2", "INV_2", "INV_2", "INV_2", "INV_2"),
    ass = c("x", "x", "x", "y", "y", "x", "x", "x", "t", "t", "t"),
    datetime = c("2010-01-01", "2010-01-02", "2010-01-03", "2010-01-08", "2010-01-19", "2010-02-20", "2010-02-22", "2010-02-23", "2010-03-01", "2010-03-02", "2010-03-04"),
    price = c(10, 10, 19, 9, 3 , 5, 1, 4, 4, 5, 1),
    operation = c(10, 0, 2, 2, 0, 5, 5, 5, 3, 0, 2)
  ),
  class = "data.frame", row.names = c(NA, -11L)
)

I have this data frame for which I want to compute the moving average of the "price" column.

In particular I need to apply a minor change before doing that. I want to change the "price" column value if the "operation" value on that row is equal to 0.

Hence I need R to:

df <- df %>% mutate( price = if_else(operation == 0, NA, price)

And then when price == NA to fill the value with the moving average of the price column. Since I could have consecutive NAs in the price column I think that the moving average should be applied with a rolling window.

I'm a new R user so I don't have a real idea of how to do that. Any idea? If possible I would prefer a dplyr solution

Answer 1

if the intention is to mutate the price with the average prices from previous rows where the operation > 0; then here is my dplyr code.

df <- tibble(df)

  
  df %>% 
    mutate( price = ifelse( operation==0, 0 ,price)) %>% 
    mutate(runinngsumPrice = cumsum(price))  %>% 
    mutate(runinngsumNNA = cumsum(ifelse(operation==0,0,1)))  %>% 
    mutate( price = ifelse( operation==0, runinngsumPrice/runinngsumNNA ,price))  %>% 
    select(1:5)
            
# 
#   # A tibble: 11 x 5
#   inv   ass   datetime   price operation
#   <chr> <chr> <chr>      <dbl>     <dbl>
#     1 INV_1 x     2010-01-01 10           10
#   2 INV_1 x     2010-01-02 10            0
#   3 INV_1 x     2010-01-03 19            2
#   4 INV_1 y     2010-01-08  9            2
#   5 INV_1 y     2010-01-19 12.7          0
#   6 INV_2 x     2010-02-20  5            5
#   7 INV_2 x     2010-02-22  1            5
#   8 INV_2 x     2010-02-23  4            5
#   9 INV_2 t     2010-03-01  4            3
#   10 INV_2 t     2010-03-02  7.43         0
#   11 INV_2 t     2010-03-04  1            2