Create a new column based multiple rows of another column

Question

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?

Possible duplicate of R dplyr rolling sum

Aramis7d
– Aramis7d

2017-04-21 11:02:10 +00:00
Commented Apr 21, 2017 at 11:02 — Aramis7d
– Aramis7d, Commented Apr 21, 2017 at 11:02

Erdem Akkas · Accepted Answer · 2017-04-21 10:28:04Z

1

If you use data.table:

library(data.table)
setDT(df)
df[,res:=value+shift(value,1)+shift(value,1,type="lead")]

answered Apr 21, 2017 at 10:28

Erdem Akkas

2,06012 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aramis7d · Accepted Answer · 2017-04-21 10:59:48Z

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

answered Apr 21, 2017 at 10:59

Aramis7d

2,49620 silver badges27 bronze badges

Comments

zx8754 · Accepted Answer · 2017-04-21 11:01:49Z

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)

edited Apr 21, 2017 at 11:01

answered Apr 21, 2017 at 10:55

zx8754

56.7k12 gold badges131 silver badges229 bronze badges

Comments

Jeremy Voisey · Accepted Answer · 2017-04-21 12:21:49Z

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

edited Apr 21, 2017 at 12:21

answered Apr 21, 2017 at 10:30

Jeremy Voisey

1,29710 silver badges14 bronze badges

3 Comments

Ding Li Over a year ago

Hi, I wonder why df$res <- sapply(df$index, function(index) (sum(df$value[index - 1:index+1]))) does not give the same result? The thing is that I want to simplify the addition of the three df$value items, because I have more than 3 items.

zx8754 Over a year ago

@DingLi It should, compare 2-1:4-2 vs (2-1):(4-2)

Jeremy Voisey Over a year ago

I've edited the code to make it a bit more flexible.

Collectives™ on Stack Overflow

Create a new column based multiple rows of another column

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related