frollmean/frollsum of last non-Na entries in data.table

[heyloism]

Hello Community,

i'm having a little trouble with the rollmean or rather the frollmean function. It almost delivers what I want but not exactly. What I want is the moving average of the last 3 entries that are not NaN. Say, I had

Data <- as.data.table(c(3,6,NaN,9))
names(Data) <- "Column1"

I want

c(NaN,NaN,NaN,6)

But I can't achieve this with neither

Data[, Column2 := frollmean(Column1,3,fill = NaN, na.rm=FALSE)] 

nor

Data[, Column3 := frollmean(Column1,3,fill = NaN, na.rm=TRUE)]

What I need is the frollmean function to SKIP NaN-values, not to ignore them. Similar to as if i would do

Data <- as.data.table(c(3,6,NaN,9))
NAfree <- drop_na(Data)
names(NAfree) <- "Free1"
NAfree[, Free2 := frollmean(Free1,3,fill = NaN)]

And then attach them back together. Which I can't do because the numbers of rows in the two tables are different.

There probably is a really easy way to do this but I don't know how. I'd be thankful for any help!

Btw generally I don't care if it fills in NA or NaN but I'd prefer NaN.

I do hope my question is more or less clear!

[heyloism]

Hi Yarnabrina,

i import the data from an Excel .csv file and there the NA values are marked as NaN, that is why I have them in the first place.

The point is, that I have multiple (around 10000) rows of data and in each column different values are missing. So what I want is like doing a na_drop followed by a frollmean for each column but afterwards assigning each mean value back to it's original row. So at row 4 I want (9 + 6 + "oh there is a NaN so i skip to the next value" 2)/3.

And if the column would look like c(3,6,NaN,NaN,NaN,NaN,NaN,9) the outcome should be c(NaN,NaN,NaN,NaN,NaN,NaN,NaN,6)

Do you know what I mean?