Shift values in single column of dataframe UP with lag specified in another column

Question

I want to adjust the prices from different countries to one time zone, which means shifting a column up by lag declared in another column (shift is the time difference).

Using example data like this:

example=data.frame(country=c("IT","IT","GR","GR","GR","TR","TR","TR","TR"), 
                   price=c(200,150,300,480,590,638,237,438,555), 
                   shift=c(0,0,1,1,1,2,2,2,2))

which looks like this:

        country   price   shift
        IT        200     0
        IT        150     0
        GR        300     1
        GR        480     1
        GR        590     1
        TR        638     2
        TR        237     2
        TR        438     2
        TR        555     2

I want to get the following result:

        country   price   shift
        IT        200     0
        IT        150     0
        GR        480     1
        GR        590     1
        GR        NA      1
        TR        438     2
        TR        555     2
        TR        NA      2
        TR        NA      2

I tried to use a solution from this thread: R: Shift values in single column of dataframe UP but since it uses a one value of lag, is not fully applicable.

M-- · Accepted Answer · 2019-10-29T16:27:23.013

Using dplyr::lead

library(dplyr)

example %>% 
  group_by(shift) %>% 
  mutate(price = lead(price, unique(shift)))

or using data.table::shift

library(data.table)

setDT(example)[, price := shift(.(price), type = "lead", n = shift), .(shift)][]

giving

#>   country price shift
#> 1 IT        200     0
#> 2 IT        150     0
#> 3 GR        480     1
#> 4 GR        590     1
#> 5 GR         NA     1
#> 6 TR        438     2
#> 7 TR        555     2
#> 8 TR         NA     2
#> 9 TR         NA     2

score 0 · Answer 2 · answered Oct 29 '19 at 15:21

Here is an idea relying solely on base R functions,

example$new_price <- unlist(lapply(split(example, factor(example$country, levels = unique(example$country))), 
                                   function(i) { i1 <- unique(i$shift); 
                                                 i2 <- c(tail(i$price, (nrow(i) - i1))); 
                                                 c(i2, rep(NA, i1)) }))

which gives,

  country price shift new_price
1      IT   200     0       200
2      IT   150     0       150
3      GR   300     1       480
4      GR   480     1       590
5      GR   590     1        NA
6      TR   638     2       438
7      TR   237     2       555
8      TR   438     2        NA
9      TR   555     2        NA

Shift values in single column of dataframe UP with lag specified in another column

2 Answers2