Argthtjtr

So I have a datatable (dt) with the following elements, an id of an Item ,the date and stock on that day.

ID        date             Stock

193sd      2016-03-23        12

238ds      2016-04-20         1

193sd      2017-09-12         1

...

So For example, an ID like 193sd has different dates

ID        date             Stock

193sd    2016-03-23          12

193sd    2017-09-12          1

193sd    2018-09-11          4

193sd    2017-01-12          2

193sd    2016-02-11          1

I need to find from the newest date 10 months ago the mean of the stock, manually I know i should do the following operations:

For that ID 193sd

1. SORT the dt$date from newest to oldest


2. 
   

   get M the number of months between date(i) and date(i+1)

   
   
   

   dates=c(dateNew, dateOld)
dates2 <- strptime(dates, format = "%Y-%m-%d")
dif <- diff(as.numeric(dates2)) # difference in seconds
months = dif/(60 * 60 * 24 * 30) # months

   
   



3. check if M<=10



4. 
   

   if true find mean of the stock for those dates for that ID and finish

   
   
   

   The mean is the sum of the values of dt$stock divided to 10

   
   



5. if false repeat

As a programmer of other languages and new to R I always think for loops but im sure there is a better cleaner why when working with datatables.

With the packages data.table and lubridate you could do the followin'

library(data.table); library(lubridate);



df <- read.table(text = 

"ID      date                Stock

193sd    2016-03-23          12

193sd    2017-09-12          1

193sd    2018-09-11          4

193sd    2018-01-12          NA

193sd    2017-12-11          1", header = T, stringsAsFactors = F)



dt <- as.data.table(df) 

dt[, date := as.Date(date)]

result <- dt[, .SD[date >= max(date) %m-% months(10)], by = ID][, .(customMean = sum(Stock, na.rm = T)/10), by = ID] #calculate the mean stock as you described for entries in date that are within 10 months from the last date for each ID

##      ID customMean

##1: 193sd        0.5