Search code examples
rdata.tableposixct

rbindlist: NA into date-time column


I am trying to build a function to insert a row of NAs into a data.table. I am using rbindlist in this way to accomplish it, where x is a data.table:

rbindlist(
  list(
    x,
    as.list(rep(NA, ncol(x)))
  )
)

I am encountering an issue whereby POSIXct columns cannot be bound with NA values, as in:

x <- data.table(
  a=c(1,2),
  t=c(Sys.time(), Sys.time()+100)
)

rbindlist(
  list(
    x,
    as.list(rep(NA, ncol(x)))
  )
)

this results for me in the following error:

    Error in rbindlist(list(x, as.list(rep(NA, ncol(x))))) : 
  Class attributes at column 2 of input list at position 2 does not match with column 2 of input list at position 1. Coercion of objects of class 'factor' alone is handled internally by rbind/rbindlist at the moment.

So it not able to bind the NA I supplied with the POSIXct column x$t.

Question: how can I bind a single-row data.table of all NA values to a data.table with a POSIXct-type column?

I have tried the following, which resulted in the same error:

rbindlist(
  list(
    x,
    as.list(c(NA, as.POSIXct(NA)))
  )
)

Of interest: calling the following on the original data.table

x[2, t:=NA]
x[2, a:=NA]

Returns:

    a                   t
1:  1 2019-04-04 12:38:57
2: NA                <NA>

Solution

  • You can do

    library(data.table)
    x <- data.table(
      a=c(1,2),
      t=c(Sys.time(), Sys.time()+100)
    )
    
    x[c(1:.N, NA)]
    
    #     a                   t
    # 1:  1 2019-04-04 13:01:34
    # 2:  2 2019-04-04 13:03:14
    # 3: NA                <NA>
    
    # or
    
    rbind(x, x[NA])
    

    In base R, you would use NA_integer_ for the latter, but data.table treats NA the same way for convenience. You can see the special treatment, eg, with x[(NA)]. This is documented in vignette("datatable-faq"):

    2.17 What are the smaller syntax differences between data.frame and data.table

    [...]

    DT[NA] returns 1 row of NA, but DF[NA] returns an entire copy of DF containing NA throughout. The symbol NA is type logical in R and is therefore recycled by [.data.frame. The user's intention was probably DF[NA_integer_]. [.data.table diverts to this probable intention automatically, for convenience.