Find Last Non-Missing Value in Each Column of a Data Table Object

To find the last non-missing value in each column of a data.table object, we can use lapply function along with tail function for NA values.

For example, if we have a data.table object called DT then we can find the last non-missing value in each column of DT by using the below given command −

DT[,lapply(.SD,function(x) tail(x[!is.na(x)],1))]

Example 1

Following snippet creates a data.table object −

library(data.table)
x1<-sample(c(NA,1,2),20,replace=TRUE)
x2<-sample(c(NA,5,10),20,replace=TRUE)
x3<-sample(c(NA,100,200),20,replace=TRUE)
DT1<-data.table(x1,x2,x3)
DT1

The following data.table object is created −

     x1 x2 x3
1:   2  5 200
2:  NA  5 200
3:   1  5 100
4:  NA NA 100
5:   1 10 200
6:  NA 10 100
7:  NA NA 200
8:   2 10 100
9:   1 10 100
10:  1  5 200
11:  1  5  NA
12: NA  5  NA
13:  2  5 100
14: NA  5 100
15: NA 10 100
16:  2  5 200
17: NA  5  NA
18: NA NA  NA
19:  2 10 200
20:  1 NA 100

To find the last non-missing value in each column of DT1, add the following code to the above snippet −

DT1[,lapply(.SD,function(x) tail(x[!is.na(x)],1))]

Output

If you execute all the above given snippets as a single program, it generates the following output −

   x1 x2 x3
1: 1 10 100

Example 2

Following snippet creates a data.table object −

y1<-sample(c(NA,rnorm(2)),20,replace=TRUE)
y2<-sample(c(NA,rnorm(2)),20,replace=TRUE)
y3<-sample(c(NA,rnorm(2)),20,replace=TRUE)
DT2<-data.table(y1,y2,y3)
DT2

The following data.table object is created −

       y1         y2          y3
1:  -0.7819176  0.5451545 -1.7242281
2:  -0.4305358  NA         NA
3:  -0.7819176  0.5451545  NA
4:  -0.4305358  NA         0.3926004
5:   NA         0.2671401 -1.7242281
6:  -0.7819176  0.2671401  0.3926004
7:  -0.7819176  NA         NA
8:  -0.4305358  0.5451545 -1.7242281
9:  -0.4305358  NA        -1.7242281
10: -0.7819176  0.5451545  NA
11: -0.4305358  NA         NA
12:  NA         0.5451545  0.3926004
13: -0.4305358  NA         0.3926004
14: -0.4305358  0.5451545 -1.7242281
15:  NA         0.2671401  0.3926004
16:  NA         0.5451545 -1.7242281
17:  NA         NA         NA
18: -0.4305358  NA         NA
19: -0.7819176  0.2671401  NA
20: -0.7819176  NA        -1.7242281

To find the last non-missing value in each column of DT2, add the following code to the above snippet −

DT2[,lapply(.SD,function(x) tail(x[!is.na(x)],1))]

Output

If you execute all the above given snippets as a single program, it generates the following output −

y1 y2 y3
1: -0.7819176 0.2671401 -1.724228

Example 3

Following snippet creates a data.table object −

z1<-sample(c(NA,round(runif(2,2,10),2)),20,replace=TRUE)
z2<-sample(c(NA,round(runif(2,1,2),2)),20,replace=TRUE)
z3<-sample(c(NA,round(runif(2,10,115),2)),20,replace=TRUE)
DT3<-data.table(z1,z2,z3)
DT3

The following data.table object is created −

     z1    z2    z3
1:  8.94  NA    71.40
2:  NA    NA    NA
3:  8.94  NA    NA
4:  8.94  NA    71.40
5:  4.09  1.48  NA
6:  4.09  NA    NA
7:  NA    1.38  71.40
8:  8.94  NA    NA
9:  4.09  NA    NA
10: 8.94  NA    NA
11: 4.09  NA    71.40
12: 8.94  1.48  107.44
13: 8.94  1.38  NA
14: 4.09  1.38  NA
15: 8.94  1.38  107.44
16: NA    1.48  71.40
17: NA    NA    107.44
18: NA    1.38  71.40
19: 4.09  NA    NA
20: 4.09  1.48  71.40

To find the last non-missing value in each column of DT3, add the following code to the above snippet −

DT3[,lapply(.SD,function(x) tail(x[!is.na(x)],1))]

Output

If you execute all the above given snippets as a single program, it generates the following output −

    z1    z2   z3
1: 4.09 1.48 71.4