CODEWITHSUNDEEP
rdplyr
David Kelley
David Kelley

Reputation: 1448

Lags with nonconsecutive index

I have data with a missing index, for example: 

df <- data.frame(year = c(2000:2004, 2006), value = c(0:4,6) ^ 2)
#   year value
# 1 2000     0
# 2 2001     1
# 3 2002     4
# 4 2003     9
# 5 2004    16
# 6 2006    36

I would like to compute the lagged value for each year. If I use the lag function, 

library(dplyr)
wrong <- mutate(df, prev = lag(value, order_by = year))
#   year value prev
# 1 2000     0   NA
# 2 2001     1    0
# 3 2002     4    1
# 4 2003     9    4
# 5 2004    16    9
# 6 2006    36   16

it gives a lagged value for 2006 despite not having data on 2005. Can I get the previous year's value with the lag function?

Currently, I know I can do the following, but it's inefficient and messy: 

right <- df %>% group_by(year) %>% 
    mutate(prev = ifelse(sum(df$year == year) == 1, df$value[df$year == year-1], NA))
# # A tibble: 6 x 3
# # Groups: year [6]
#     year value  prev
#   <dbl> <dbl> <dbl>
# 1  2000  0    NA   
# 2  2001  1.00  0   
# 3  2002  4.00  1.00
# 4  2003  9.00  4.00
# 5  2004 16.0   9.00
# 6  2006 36.0  NA

Upvotes: 1

Views: 34

Answers (1)

Julius Vainora
Julius Vainora

Reputation: 48241

Here's one simple approach:

mutate(df, prev = value[match(year - 1, year)])
#   year value prev
# 1 2000     0   NA
# 2 2001     1    0
# 3 2002     4    1
# 4 2003     9    4
# 5 2004    16    9
# 6 2006    36   NA

Upvotes: 3

Related Questions