Writing efficient functions for data.tables that replace by reference

Question

I have a data.table which contains several binary columns with the same values that I would like to recode in one operation. I have modified a function that was originally written for data.frames, but am not sure if I am really capitalizing on the speed of data.table with the way I have modified it: specifically I suspect the function might still be copying values.

How can I make sure that the function replaces values by reference?

Here is a toy data set:

# Example data:
id <- c(1,2,3,4,5)
fruit <- c("apple", "orange", "banana", "strawbery", "rasberry")
mydate <- c("2015-09-01", "2015-09-02", "2015-11-15", "2016-02-24", "2016-03-08")
eaten <- c("y", "y", "n", "y", "u")
present <- c("n", "n", "y", "y", "y")

dt <- data.table(id, fruit, mydate, eaten, present)
dt[, mydate := as.Date(mydate, format = "%Y-%m-%d")]
dt[, sex := c("m", "f", "f", "m", "f")]

# Columns to update:
bincols <- c("eaten", "present")

Before recoding, the data looks like this:

> dt
   id     fruit     mydate eaten present sex
1:  1     apple 2015-09-01     y       n   m
2:  2    orange 2015-09-02     y       n   f
3:  3    banana 2015-11-15     n       y   f
4:  4 strawbery 2016-02-24     y       y   m
5:  5  rasberry 2016-03-08     u       y   f

Here is the function:

recode.multi <- function(datacols, oldval, newval) {
  for (i in 1:length(datacols)) {
    datacols[datacols == oldval[i]] = newval[i]
  }
  datacols
}

... applied to the data:

dt[, (bincols) := lapply(.SD, recode.multi, oldval = c("u", "n", "y"), newval = c(NA_real_, 0, 1)), .SDcols = bincols]

... and the output, which updates the values as desired but not sure if it is copying the columns during this process?

> dt
   id     fruit     mydate eaten present sex
1:  1     apple 2015-09-01     1       0   m
2:  2    orange 2015-09-02     1       0   f
3:  3    banana 2015-11-15     0       1   f
4:  4 strawbery 2016-02-24     1       1   m
5:  5  rasberry 2016-03-08    NA       1   f

I tried changing the last '=' in the function to ':=' but got an error re checking whether 'datacols' was a data.table. Adding a clause to the function to check if is.data.table == TRUE didn't solve the problem (same error returned).

Any thoughts on the most data.table appropriate way to approach this function would be much appreciated.

Unsure about the copying, but this is a fine bit of code. I am unsure what you mean here: "I tried changing the last = in the function to :=." Which = were you trying to change? — lmo
– lmo, Commented Apr 13, 2016 at 17:21
I tried: recode.multi <- function(datacols, oldval, newval) { for (i in 1:length(datacols)) { datacols[datacols == oldval[i]] := newval[i] } datacols } — Amy M
– Amy M, Commented Apr 13, 2016 at 17:40

IRTFM · Accepted Answer · 2016-04-13 18:29:27Z

2

This is similar to Frank's but lets the arguments be passed to a function that builds the translation vector and returns the translation. You don't need to do the loop inside the function since the lapply , the :=, and the .SDcols functions are doing the looping inside [.data.table.

recode_dt <- function(datacol, oldval, newval) 
    { trans <- setNames(newval, oldval)
     trans[ datacol ]   }

dt[, (bincols) := lapply(.SD, recode_dt, oldval = c("u", "n", "y"), 
                                         newval = c(NA_real_, 0, 1)), 
     .SDcols = bincols]
dt
#===============
   id     fruit     mydate eaten present sex
1:  1     apple 2015-09-01     1       0   m
2:  2    orange 2015-09-02     1       0   f
3:  3    banana 2015-11-15     0       1   f
4:  4 strawbery 2016-02-24     1       1   m
5:  5  rasberry 2016-03-08    NA       1   f

Note that your columns were not actually factors as it appeared you thought from one of your comments. They might have been had you built a data.frame as an intermediate step.

answered Apr 13, 2016 at 18:29

IRTFM

264k22 gold badges381 silver badges503 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Frank Over a year ago

Interesting, the columns have named elements inside the RHS of LHS := RHS, but not after assignment. I mean

dt[, str(lapply(.SD, recode_dt, oldval = c("u", "n", "y"),                                           newval = c(NA_real_, 0, 1))),       .SDcols = bincols]

IRTFM Over a year ago

That's not really much different than data.frame behavior. Names usually get thrown away, except for names of the first column that then get turned into rownames.

Amy M Over a year ago

+1 for the use of setNames, I was thinking there might be a way to use 'set'. I also like that it automatically converted the updated vectors from character to numeric as well.

IRTFM Over a year ago

It will return a vector of the same class as newval. Frank had a good point that supplying integer might be better. Also setNames is not a data.table function, despite its first three letters.

Frank · Accepted Answer · 2016-04-13 17:22:34Z

2

I would do...

recodeDT = data.table(old = c("u", "n", "y"), new = c(NA_integer_, 0L, 1L), key = "old")

dt[, (bincols) := lapply(.SD, function(x) recodeDT[.(x), new]), .SDcols = bincols]

I think it's better to store any finite remapping in a table for clarity, but I don't know if it is more efficient. If you were storing your variables as factors, you could simply tweak the levels, which should be very fast. You could use setattr(x, "levels", z), maybe.

Side note: You probably want to code these as integers instead of floats.

answered Apr 13, 2016 at 17:22

Frank

66.9k8 gold badges104 silver badges190 bronze badges

1 Comment

Amy M Over a year ago

Thanks - hadn't thought of using a keyed lookup table approach. I have avoided converting character vectors to factors, because of the difficulties encountered if adding extra levels, but can see how this would be efficient for this situation.

Amy M · Accepted Answer · 2016-04-14 09:35:47Z

1

In order to determine if my original solution was copying values or not, I applied mine, Frank and 42's solutions to my real data set which has 8933 observations and 150 columns to update with the function. Results from system.time below:

@Amy M: 332.82 seconds

@Frank: 0.15 seconds

@42 (original): 4.13 seconds

@42 (with @Frank's modification): 0.05 seconds

Both Frank and 42's solutions are far faster than mine (so mine must be copying).

I have reprinted the fastest solution (42 with Frank's modification) below:

recode.multi <- function(datacol, oldval, newval) {
  trans <- setNames(newval, oldval)
  trans[ match(datacol, names(trans)) ]
}

edited Apr 14, 2016 at 9:35

answered Apr 13, 2016 at 19:40

Amy M

1,1031 gold badge12 silver badges28 bronze badges

3 Comments

Frank Over a year ago

That's an interesting result. Maybe if you switch the last line of -42's function to trans[ match(datacol, names(trans)) ] it will be faster. I guess the number of distinct values and the number of times each distinct value repeats in your data might matter.

Frank Over a year ago

Or just rewrite the entire function as newvals[ match(datacol, oldvals) ]

Amy M Over a year ago

I couldn't get your second suggestion to work, but modifying the last line of 42's function does result in a gain in speed: time elapsed is 0.05 seconds.

Collectives™ on Stack Overflow

Writing efficient functions for data.tables that replace by reference

3 Answers 3

4 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related