3

I have a dataframe as this 

> df<-data.frame(index=c(1,2,3,4,5,6),value=c(2,3,5,8,11,12))
> df
    index value
1     1     2
2     2     3
3     3     5
4     4     8
5     5    11
6     6    12

I want to create a new column which equals to the the sum of three adjacent values of column value indexed by column index, that is

> df_res
  index value res
1     1     2 NA
2     2     3 10
3     3     5 16
4     4     8 24
5     5    11 31
6     6    12 NA

The second row of res is the sum of (2,3,5), third sum(3,5,8) etc. (the first and last row of res do not matter, and I temporarily set it as NA)

How can I get it done in R?

zx8754
  • 46,390
  • 10
  • 104
  • 180
Ding Li
  • 613
  • 1
  • 7
  • 15
  • Possible duplicate of [R dplyr rolling sum](http://stackoverflow.com/questions/30153835/r-dplyr-rolling-sum) – Aramis7d Apr 21 '17 at 11:02

4 Answers4

1

If you use data.table:

library(data.table)
setDT(df)
df[,res:=value+shift(value,1)+shift(value,1,type="lead")]
Erdem Akkas
  • 2,022
  • 9
  • 15
1

you can use dplyr and roll_sum to do:

df %>% 
  mutate(v2 = roll_sum(value, 3,fill = NA))

which gives:

  index value v2
1     1     2 NA
2     2     3 10
3     3     5 16
4     4     8 24
5     5    11 31
6     6    12 NA
Aramis7d
  • 2,329
  • 19
  • 24
0
df$res <- sapply(df$index, function(index) 
    ifelse(index > 1 & index < nrow(df),sum(df$value[(index - 1):(index + 1)]), NA))

   index value res
1     1     2  NA
2     2     3  10
3     3     5  16
4     4     8  24
5     5    11  31
6     6    12  NA
Jeremy Voisey
  • 1,187
  • 8
  • 13
0

Using head and tail:

df$res <- df$value + c(tail(df$value, -1), NA) + c(NA, head(df$value, -1))

df
#   index value res
# 1     1     2  NA
# 2     2     3  10
# 3     3     5  16
# 4     4     8  24
# 5     5    11  31
# 6     6    12  NA

Or using zoo:

df$res <- zoo::rollsum(df$value, 3, na.pad = TRUE)
zx8754
  • 46,390
  • 10
  • 104
  • 180