21

I am working with an extremely large dataset in R and have been operating with data frames and have decided to switch to data.tables to help speed up with operations. I am having trouble understanding the J operations, in particular I'm trying to generate dummy variables but I can't figure out how to code conditional operations within data.tables[].

MWE:

test <- data.table("index"=rep(letters[1:10],100),"var1"=rnorm(1000,0,1))

What I would like to do is to add columns a through j as dummy variables such that column a would have a value 1 when the index == "a" and 0 otherwise. In the data.frame environment it would look something like:

test$a <- 0

test$a[test$index=='a'] <- 1
smci
  • 32,567
  • 20
  • 113
  • 146
user2792957
  • 319
  • 2
  • 5
  • Here's an example with dummies that may help: http://stackoverflow.com/questions/18871614/r-datatable-join-and-constrain-rows/18874811#18874811 – Frank Sep 18 '13 at 19:53
  • 2
    What is "extremely large"? Is `model.matrix(~var1+index-1, test)` too slow? – Roland Sep 18 '13 at 19:58
  • 1
    With my dataset (9mln rows), Frank's solution below is the only one that works. With `model.matrix` I run out of memory. – Riccardo Dec 20 '13 at 10:17

1 Answers1

36

This seems to do what you're looking for:

inds <- unique(test$index)
test[, (inds) := lapply(inds, function(x) index == x)]

which gives

      index        var1     a     b     c     d     e     f     g     h     i     j
   1:     a  0.25331851  TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
   2:     b -0.02854676 FALSE  TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
   3:     c -0.04287046 FALSE FALSE  TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
   4:     d  1.36860228 FALSE FALSE FALSE  TRUE FALSE FALSE FALSE FALSE FALSE FALSE
   5:     e -0.22577099 FALSE FALSE FALSE FALSE  TRUE FALSE FALSE FALSE FALSE FALSE
  ---                                                                              
 996:     f -1.02040059 FALSE FALSE FALSE FALSE FALSE  TRUE FALSE FALSE FALSE FALSE
 997:     g -1.31345092 FALSE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE FALSE FALSE
 998:     h -0.49448088 FALSE FALSE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE FALSE
 999:     i  1.75175715 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE
1000:     j  0.05576477 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE  TRUE

Here's another way:

dcast(test, index + var1 ~ index, fun = length)
# or, if you want to preserve row order
dcast(test[, r := .I], r + index + var1 ~ index, fun = length)[, r := NULL]

And another:

rs = split(seq(nrow(test)), test$index)
test[, names(rs) := FALSE ]
for (n in names(rs)) set(test, i = rs[[n]], j = n, v = TRUE )
Frank
  • 66,179
  • 8
  • 96
  • 180
  • Frank, is there a way of making this flexible so that you could create multiple dummies across multiple columns? Basically, what if you don't have a column called index but want to call a specific column number in the lapply function and then put a loop around it so that it can execute this for multiple columns? – SJDS Feb 18 '15 at 04:11
  • @simon_icl Yes, a loop should work. If you aren't sure how to do it, post it as a question and I can show how I would do it (and someone else might have a better/more efficient approach). I'll have a look tomorrow. – Frank Feb 18 '15 at 05:09