How to find difference between values in two rows in an R dataframe using dplyr
In dplyr:
require(dplyr)
df %>%
group_by(farm) %>%
mutate(volume = cumVol - lag(cumVol, default = cumVol[1]))
Source: local data frame [8 x 5]
Groups: farm
period farm cumVol other volume
1 1 A 1 1 0
2 2 A 5 2 4
3 3 A 15 3 10
4 4 A 31 4 16
5 1 B 10 5 0
6 2 B 12 6 2
7 3 B 16 7 4
8 4 B 24 8 8
Perhaps the desired output should actually be as follows?
df %>%
group_by(farm) %>%
mutate(volume = cumVol - lag(cumVol, default = 0))
period farm cumVol other volume
1 1 A 1 1 1
2 2 A 5 2 4
3 3 A 15 3 10
4 4 A 31 4 16
5 1 B 10 5 10
6 2 B 12 6 2
7 3 B 16 7 4
8 4 B 24 8 8
Edit: Following up on your comments I think you are looking for arrange(). It that is not the case it might be best to start a new question.
df1 <- data.frame(period=rep(1:4,4), farm=rep(c(rep('A',4),rep('B',4)),2), crop=(c(rep('apple',8), rep('pear',8))), cumCropVol=c(1,5,15,31,10,12,16,24,11,15,25,31,20,22,26,34), other = rep(1:8,2) );
df1 %>%
arrange(desc(period), desc(farm)) %>%
group_by(period, farm) %>%
summarise(cumVol=sum(cumCropVol))
Edit: Follow up #2
df1 <- data.frame(period=rep(1:4,4), farm=rep(c(rep('A',4),rep('B',4)),2), crop=(c(rep('apple',8), rep('pear',8))), cumCropVol=c(1,5,15,31,10,12,16,24,11,15,25,31,20,22,26,34), other = rep(1:8,2) );
df <- df1 %>%
arrange(desc(period), desc(farm)) %>%
group_by(period, farm) %>%
summarise(cumVol=sum(cumCropVol))
ungroup(df) %>%
arrange(farm) %>%
group_by(farm) %>%
mutate(volume = cumVol - lag(cumVol, default = 0))
Source: local data frame [8 x 4]
Groups: farm
period farm cumVol volume
1 1 A 12 12
2 2 A 20 8
3 3 A 40 20
4 4 A 62 22
5 1 B 30 30
6 2 B 34 4
7 3 B 42 8
8 4 B 58 16
In dplyr -- so you don't have to replace NAs
library(dplyr)
df %>%
group_by(farm)%>%
mutate(volume = c(0,diff(cumVol)))
period farm cumVol other volume
1 1 A 1 1 0
2 2 A 5 2 4
3 3 A 15 3 10
4 4 A 31 4 16
5 1 B 10 5 0
6 2 B 12 6 2
7 3 B 16 7 4
8 4 B 24 8 8
Would creating a new column in your original dataset be an option?
Here is an option using the data.table operator :=.
require("data.table")
DT <- data.table(df)
DT[, volume := c(0,diff(cumVol)), by="farm"]
or
diff_2 <- function(x) c(0,diff(x))
DT[, volume := diff_2(cumVol), by="farm"]
Output:
# > DT
# period farm cumVol other volume
# 1: 1 A 1 1 0
# 2: 2 A 5 2 4
# 3: 3 A 15 3 10
# 4: 4 A 31 4 16
# 5: 1 B 10 5 0
# 6: 2 B 12 6 2
# 7: 3 B 16 7 4
# 8: 4 B 24 8 8
tapply and transform?
> transform(df, volumen=unlist(tapply(cumVol, farm, function(x) c(0, diff(x)))))
period farm cumVol other volumen
A1 1 A 1 1 0
A2 2 A 5 2 4
A3 3 A 15 3 10
A4 4 A 31 4 16
B1 1 B 10 5 0
B2 2 B 12 6 2
B3 3 B 16 7 4
B4 4 B 24 8 8
ave is a better option, see @ thelatemail's comment
with(df, ave(cumVol,farm,FUN=function(x) c(0,diff(x))) )