Given the following data table:
Category <- c('A','A','A','A')
Year <- c(0,1,2,3)
Amount1 <- c(0,100,200,300)
Amount2 <- c(0,30,40,0)
dt_1 <- data.table(Category, Year, Amount1, Amount2)
dt_1
how can I shift amount2 by one row obtaining the following table (filling with 0 the last row)
Category <- c('A','A','A','A')
Year <- c(0,1,2,3)
Amount1 <- c(0,100,200,300)
Amount2 <- c(30,40,0,0)
dt_2 <- data.table(Category, Year, Amount1, Amount2)
dt_2
You need data.table::shift():
dt_1[, Amount2 := shift(Amount2, type="lead", fill=0)]
# Category Year Amount1 Amount2
# <char> <num> <num> <num>
# 1: A 0 0 30
# 2: A 1 100 40
# 3: A 2 200 0
# 4: A 3 300 0