In R, compute time series difference of lagged values

user_19110395 0
Viewed 48

Hello my dataframe looks like this for a very large number of years and products:

product<-c(1,2,3)
yr1<-c(109,213,30)
yr2<-c(613,488,125)
yr3<-c(729,494,127)
df<-data.frame(product,yr1,yr2,yr3)

I need to transform it so that the values for the years after the first are the difference of the current value and the previous (lag) value, so that it looks like this:

yr2<-c(504,275,95)
yr3<-c(116,6,2)
df<-data.frame(product,yr1,yr2,yr3)

With 613-109=504, 729-613=116, etc.

How can I do this?

r
3 Answers

dplyr option with cur_data which makes a copy of the data to substract every column with its previous column:

library(dplyr)
df %>% 
  mutate(cur_data()[,c(-2)] - cur_data()[-ncol(.)]) %>%
  mutate(product = df$product)

Output:

  product yr1 yr2 yr3
1       1 109 504 116
2       2 213 275   6
3       3  30  95   2
user_142827140

Try

df[,tail(grep("yr",colnames(df)),-1)]-df[,head(grep("yr",colnames(df)),-1)]

  yr2 yr3
1 504 116
2 275   6
3  95   2
user_29749510

Another dplyr approach:

library(dplyr)

df %>% 
 mutate(yr1 = NULL, across(-product, ~ .x-df[which(cur_column() == names(df))-1]))

#>   product yr1 yr2
#> 1       1 504 116
#> 2       2 275   6
#> 3       3  95   2
user_115644870
Related