Calculate running average based on date range-CodePudding

I have a dataset which consists of client id, date in which he/she ordered something and an his/her invoice value. Reproducible example below:

client_id_ex<-c("0001","0001","0001","0001","0002","0002","0002","0002","0002","0002","0002")
order_date_ex<-as.Date(c("12-05-2000","02-01-2001","11-11-2020","03-05-2021","12-05-2000","16-05-2000","12-06-2000","13-08-2000","19-05-2004","12-09-2007","08-12-2008"),format="%d-%m-%Y")
invoice_ex<-c(450,100,200,330,543,665,334,753,234,541,1000)
df<-data.frame(client_id_ex,order_date_ex,invoice_ex)

I want to calculate a running average of a invoice for each client separately and for orders made not earlier than 5 years before each order I am calculating the average for.

The result would look like this:

client_id_ex   order_date_ex   invoice_ex   avg_invoice_5
1              12.05.2000      450          450
1              02.01.2001      100          275
1              11.11.2020      200          200
1              03.05.2021      330          265
2              12.05.2000      543          543
2              16.05.2000      665          604
2              12.06.2000      334          514
2              13.08.2000      753          574
2              19.05.2004      234          506
2              12.09.2007      541          388
2              08.12.2008      999          591

Does anyone know how to do this? I tried to make a use of: Calculate average based on date range in R , but since I have to calculate something more like moving average and do this for each client separately, I didn't derive much from this example.

CodePudding user response：

Here is one way of doing it using the tidyverse. It uses purrr::map to take the average of each client's invoices between each date and the date five years (5*365.25 days) earlier.

library(tidyverse)

df %>%
    group_by(client_id_ex) %>% 
    mutate(roll_mean = map_dbl(order_date_ex, 
                               ~mean(invoice_ex[(order_date_ex >= (. - 5 * 365.25)) & 
                                                  (order_date_ex <= .)])))
# A tibble: 11 x 4
# Groups:   client_id_ex [2]
   client_id_ex order_date_ex invoice_ex roll_mean
   <chr>        <date>             <dbl>     <dbl>
 1 0001         2000-05-12           450      450 
 2 0001         2001-01-02           100      275 
 3 0001         2020-11-11           200      200 
 4 0001         2021-05-03           330      265 
 5 0002         2000-05-12           543      543 
 6 0002         2000-05-16           665      604 
 7 0002         2000-06-12           334      514 
 8 0002         2000-08-13           753      574.
 9 0002         2004-05-19           234      506.
10 0002         2007-09-12           541      388.
11 0002         2008-12-08          1000      592.

CodePudding user response：

I think you're after a cumulative mean/average rather than a rolling mean/average.

Here is one option:

df %>%
    group_by(client_id_ex) %>%
    mutate(grp = cumsum(c(TRUE, (diff(order_date_ex) > 5 * 365)))) %>%
    group_by(client_id_ex, grp) %>%
    mutate(avg_invoice_5 = cummean(invoice_ex)) %>%
    ungroup() %>%
    select(-grp)
## A tibble: 11 x 4
#  client_id_ex order_date_ex invoice_ex avg_invoice_5
#  <chr>        <date>             <dbl>         <dbl>
# 1 0001         2000-05-12           450          450 
# 2 0001         2001-01-02           100          275 
# 3 0001         2020-11-11           200          200 
# 4 0001         2021-05-03           330          265 
# 5 0002         2000-05-12           543          543 
# 6 0002         2000-05-16           665          604 
# 7 0002         2000-06-12           334          514 
# 8 0002         2000-08-13           753          574.
# 9 0002         2004-05-19           234          506.
#10 0002         2007-09-12           541          512.
#11 0002         2008-12-08          1000          581.

I admit that I don't understand (nor can I reproduce) the output for your last two rows. I assume this is an error? All of the invoice dates for client_id_ex = 0002 are within 5 years of each other.