Use Previous Row of data.table in R

In this article, we will be looking at different approaches to using the previous row of data.table using the basic functionality in the R programming language.

The data table subsetting can be performed and the new rows can be created and their values are assigned using the shift method in R. The type can be specified as either “lead” or “lag” depending upon the direction in which the elements are to be moved. The shift method takes as an argument the column name to use the values. 

Syntax: DT[, Row:= shift(val, type = )]

Parameters:

• val – The value to shift
• type – Lead/ lag the values

Method 1: Extract Previous Row from data.table Using shift() Function

In this method extract previous rows from the data.table using the shift() function, the user needs to first import and install the data.table package to working console, further the user needs to use the mentioned syntax with the call of the shift function to get the values one row before when doing calculations with a data.table. and create a new column and show the values that come accordingly in the R programming language.

Syntax to install and import the data.table library in the working console:

install.packages("data.table")              
library("data.table")

Example:

In this example, we have created a data table with 3 columns and 5 rows then we have added a column containing the values of the variable x time the values of the previous row in the variable y new to the data table using the above approach mentioned in the R programming language.

R

#import required libraries
library("data.table")

# Create Data table
data <- data.table(x = 1:5,y = 10:6,
                   z = LETTERS[1:5])

# Use the previous rows
data[ , New := x*shift(y)]
data

Output:

   x  y z New
1: 1 10 A  NA
2: 2  9 B  20
3: 3  8 C  27
4: 4  7 D  32
5: 5  6 E  35

Method 2: Extract N Rows Before from data.table Using shift() Function & type Argument

In this method, we have created a data table with 3 columns and 5 rows then call the shift function with the N set to 3 which will be using the 3 previous of the data table created to the multiplication operation and then add a new column to the data table with the value obtained in the calculation and here we have also use the type argument of the shift() function which will be initialized to lag in the R programming language. 

R

# import required libraries
library("data.table")

# Create Data table
data <- data.table(x = 1:5,y = 10:6,
                   z = LETTERS[1:5])

# Use the previous rows
data[ , lag3 := x * shift(
  y, 3, type = "lag")]
data

Output:

   x  y z lag3
1: 1 10 A   NA
2: 2  9 B   NA
3: 3  8 C   NA
4: 4  7 D   40
5: 5  6 E   45