Change data.table column classes to integer64, double respectively

Question

Given data.table dt:

dt <- structure(list(V1 = c("1544018118438041139", "1544018118466235879", 
"1544018118586849680", "1544018118601169211", "1544018118612947335", 
"1544018118614422179"), V2 = c("162", "162", "161.05167", "158.01309", 
"157", "157"), V3 = c("38", "38", "36.051697", "33.01306", "32", 
"32"), V4 = c("0.023529414", "0.025490198", "0.023529414", "0.027450982", 
"0.03137255", "0.03137255"), V5 = c("1", "1", "1", "1", "1", 
"1"), V6 = c("2131230815", "2131230815", "2131230815", "2131230815", 
"2131230815", "2131230815"), V7 = c("1", "0", "0", "0", "0", 
"-1")), class = c("data.table", "data.frame"), row.names = c(NA, 
-6L), .internal.selfref = <pointer: 0x2715f60>)

I want the first column to be bit64::as.integer64() and the rest of the columns as.numeric()

I am trying to do this:

dt <- dt[ ,V1 := bit64::as.integer64(V1)]

dt[, lapply(.SD, as.numeric), .SDcols = -c("V1")]

But it doesn't seem to do what I want, please advise how to change specific columns to class A(integer64) and the rest to another class B (say as.numeric())?

Answer 1

From the comments above it seems like you want to be able to do this all in one step rather than convert the first to integer64 and then the rest to double. One way you can do this is with:

dt[, names(dt) := Map(function(fun, x) fun(x), rep(list(bit64::as.integer64, as.numeric), times = c(1,length(.SD) - 1)), .SD), .SDcols = names(dt)]

The Map function iterates through your inputs together. That is, it takes the first elements of your first and second vectors and pass them as arguments to our function. Then it takes the second elements of both vectors and passes those to the function.

In our Map call we have:

• A main function to apply. This is an anonymous function which takes two arugments (1) fun, and (2) x. The result of our function is the result of applying fun to x or fun(x). For a concrete example try:

  myfun <- function(fun, x){
    fun(x)
  }
  res<-myfun(as.numeric, c("1","1")); class(res)
  

• A list of functions to pass to our main function. These will be used as fun in our main function. In this case its list(as.integer64, as.numeric, as.numeric,...)

• A list of vectors to pass to our main function. These will be used as x in our main function. In this case each column of our dt.

A quick and dirty visual aid of how this works is (assuming custom_function takes two arguments):

Answer 2

It looks to me that you have a data.table object with integer64 nanosecond timestamps since the epoch. I use the same at work to represent high-resolution timestamps.

The good news is that data.table supports this -- by relying on our package nanotime which itself uses bit64 for the integer64 type. However, I create my timestamps differently, typically from compiled code where I retrieve the data.

I described this in some detail at the Rcpp Gallery in this post . So some good news: this can be done. Some bad news: I don't think you can do it the way you want it because we can only go via double which has only 16 decimals precision, not 19. But maybe I am missing trick so if simpler solution exists I'd be all ears. (And I keep forgetting if there is a 'parse int64 from string approach'. I never went that route because you can't do that at scale -- I deal with pretty sizeable data sets too.)

Answer 3

Thanks guys, @dirk_eddelbuettel I managed to do this:

1) Load all the JSON files (in my case) and use

bigint_as_char=TRUE

within fromJSON command.

2) Now you have a big table with all columns as characters.

3) Convert timestamp column to bit64::as.integer64() - you get the numbers I want.

4) Convert the rest to desired types.

5) When I want to perform calculations, for example timestamp - lag(timestamp) I am adding the lag_timestamp = lag(timestamp) (with dplyr::mutate) as new column and add diff_column = storing it as.character()

6) You are almost done - the new diff column stores the value I want as string / character and now you can convert it to as.numeric() where needed or apply ifelse() to deal with non relevant values.

7) That's all, it works perfectly for me and don't crash R Studio.

Before applying my solution R Studio crashed.