CODEWITHSUNDEEP
rdatabasedataframe
datanovice
datanovice

Reputation: 47

How can I populate a new column with averages of the previous 3 rows of another column? in R

Say I have a dataframe where each row represents a different day. I have a column with the date, and a column with a value of interest for each day. For example, I might create a dataframe like this:

df<-data.frame(date=c("9/1","9/2","9/3","9/4","9/5","9/6"),value=c(2,3,5,8,11,12))

I would like to create an additional column (named "avg" for the sake of convenience) which averages the values for the past 3 days, including that day. In other words, I'd like the data.frame created above to add an additional column that looks roughly like this: 

avg

NA

NA

3.33

5.33

8

10.33

How can I do this in R? I'd also like to do this with the 7 days prior, if possible, but I figure that code would be very similar to the code for this question.

Upvotes: 1

Views: 119

Answers (3)

Parfait
Parfait

Reputation: 107652

Consider a running mean with sapply using base library.

df$avg_last_three <- c(NA, NA, sapply(3:nrow(df), function(i) mean(df$value[(i-2):i])))

df    
#   date value avg_last_three
# 1  9/1     2             NA
# 2  9/2     3             NA
# 3  9/3     5       3.333333
# 4  9/4     8       5.333333
# 5  9/5    11       8.000000
# 6  9/6    12      10.333333

Upvotes: 0

Uwe
Uwe

Reputation: 42544

For the sake of completeness, version 1.12.0 of the data.table package (on CRAN 13 Jan 2019) has gained a fast rolling mean function:

library(data.table)   # version 1.12.0+
setDT(df)[, avg := frollmean(value, 3L)]
df

   date value       avg
1:  9/1     2        NA
2:  9/2     3        NA
3:  9/3     5  3.333333
4:  9/4     8  5.333333
5:  9/5    11  8.000000
6:  9/6    12 10.333333

Upvotes: 0

Jilber Urbina
Jilber Urbina

Reputation: 61164

> library(zoo)
> DF$avg <- c(NA, NA, rollmean(DF$value, 3))
> DF
  date value       avg
1  9/1     2        NA
2  9/2     3        NA
3  9/3     5  3.333333
4  9/4     8  5.333333
5  9/5    11  8.000000
6  9/6    12 10.333333

you can also use fill and align arguments

rollmean(DF$value, 3, fill = NA, align = "right")

or taking into account @H 1's comment use rollmeanr(df$value, 3, fill = NA)

Upvotes: 1

Related Questions