Example TERR script to "fill down" into sequences of missing values (NAs) in a column, using a while() loop

Example TERR script to "fill down" into sequences of missing values (NAs) in a column, using a while() loop

This article presents an example TERR script that can be used in a Spotfire data function to replace each sequence of one or more missing values (NAs) in a column with the meaningful value provided in the row just above that missing-value sequence.

The script uses subscripting in combination with calls to any(), diff(), is.na() and seq(), inside a while() loop that stops when all of the missing values have been replaced.

It expects an input data frame (Spotfire data table) named 'InputDF', and builds an output data frame (Spotfire data table) named 'OutputDF'.

# TERR commands that create and display
# a small test data frame named 'InputDF':

InputDF <-
  data.frame( 
    ID = c(356L, NA, NA, NA, 404L, NA, NA, 123L, NA),
    OtherColumn = LETTERS[1:9],
    stringsAsFactors = FALSE )

InputDF


# Example TERR script for such a "FillDown" data function,
# expecting a data frame named 'InputDF' as the input parameter,
# generating a data frame named 'OutputDF' as the output parameter,
# and using a while() loop in TERR
# to replace each sequence of missing values in the "ID" column 
# with the meaningful value just above that sequence:

#-----
ColumnOfInterest <- "ID"

OutputDF <- InputDF

testB <- OutputDF[, ColumnOfInterest]

while(any(diff(is.na(testB)) == 1L))
{
  testC <- diff(is.na(testB)) == 1L
  testD <- seq(along = testC)[testC]
  testB[testD + 1L] <- testB[testD]
}

OutputDF[, ColumnOfInterest] <- testB
#-----


# TERR command that displays the result:

OutputDF

Test of the TERR script in a TERR Console session:

TIBCO Software Inc. Confidential Information
Copyright (C) 2011-2018 TIBCO Software Inc. ALL RIGHTS RESERVED
TIBCO Enterprise Runtime for R version 4.5.0 for Microsoft Windows 64-bit

Type 'help()' for help.
Type 'q()' to quit.
>
>
> # TERR commands that create and display a small test data frame named 'InputDF':
>
> InputDF <-
+   data.frame(
+     ID = c(356L, NA, NA, NA, 404L, NA, NA, 123L, NA),
+     OtherColumn = LETTERS[1:9],
+     stringsAsFactors = FALSE )
>
> InputDF
   ID OtherColumn
1 356           A
2  NA           B
3  NA           C
4  NA           D
5 404           E
6  NA           F
7  NA           G
8 123           H
9  NA           I
>
>
> # Example TERR script for such a "FillDown" data function,
> # expecting a data frame named 'InputDF' as the input parameter,
> # generating a data frame named 'OutputDF' as the output parameter,
> # and using a while() loop in TERR
> # to replace each sequence of missing values in the "ID" column
> # with the meaningful value above that sequence:
>
> #-----
> ColumnOfInterest <- "ID"
>
> OutputDF <- InputDF
>
> testB <- OutputDF[, ColumnOfInterest]
>
> while(any(diff(is.na(testB)) == 1L))
+ {
+   testC <- diff(is.na(testB)) == 1L
+   testD <- seq(along = testC)[testC]
+   testB[testD + 1L] <- testB[testD]
+ }
>
> OutputDF[, ColumnOfInterest] <- testB
> #-----
>
>
> # TERR command that displays the result:
>
> OutputDF
   ID OtherColumn
1 356           A
2 356           B
3 356           C
4 356           D
5 404           E
6 404           F
7 404           G
8 123           H
9 123           I
>
>

Disclaimer:
The code in this article is only a sample to be used as a reference. It is not intended to be used "As Is" in a Production environment. Always test in a Development environment. Make modifications to the code in accordance with your implementation specifications that best suit your business requirements. Refer to the reference(s) cited in this article for usage of the functions and methods used in the code.