59

When using data.table is it possible to return all the columns except one, like in data.frame?

If the answer is no, does anyone have an elegant way to transform a multiple time series data.table to a zoo or other time series object?

Consider the following example:

library(data.table)
library(zoo)

## DEFINE DATA
set.seed(1)
dt = data.table(
    mydates = as.Date("2012-01-01") + 1:9, 
    value1 = sort(rpois(9, 6)),
    value2 = sort(rpois(9, 6)),
    value3 = sort(rpois(9, 6)),
    value4 = sort(rpois(9, 6)),
    value5 = sort(rpois(9, 6)))

## CONVERT TO DATA FRAME
df = as.data.frame(dt)

## CONVERT TO ZOO
zooObj = zoo(df[,-1], df$mydates)

## EXAMPLE OF DESIRED RESULTS
plot(zooObj, col=1:ncol(zooObj))

How would I do that without df = as.data.frame(dt)?

geneorama
  • 3,620
  • 4
  • 30
  • 41

2 Answers2

75

Try with=FALSE :

dt[,-1,with=FALSE]

As an aside, feature request #416 is related :

Add not join DT[-J(...)], and not columns DT[,-"colC",with=FALSE].

Matt Dowle
  • 58,872
  • 22
  • 166
  • 224
  • 47
    As of version 1.8.3, this works: `dt[, !"mydates", with=FALSE]` – GSee Nov 15 '12 at 19:41
  • @ChristopherBrown Thanks for your [suggested edit](http://stackoverflow.com/review/suggested-edits/6097805). Can't see why it was rejected by reviewers so I applied it. – Matt Dowle Oct 31 '14 at 19:57
  • 1
    @GSee, could you make that a full answer? It's the best answer now! – Tripartio Mar 20 '16 at 22:00
  • 31
    `dt[, !"mydates"]` without the `with=` now works as well – Chris Jan 26 '17 at 18:27
  • 1
    That's confusing it works for: `dt[, !"mydates"] `, but `col <-"mydates"` and then: dt[, !col] ` does not work, you need the `with`, for example this works: `dt[, !col, with=F]` – David Leal Mar 22 '17 at 18:32
  • @DavidLeal, that's expected. The `with=FALSE` only has an effect on variables named in the expression. Using a literal `"mydates"` string, there's no variable, so it has no effect. Using a variable means it now has to be told how to resolve that variable, either from the column names or ambient variables in scope. – Ken Williams Dec 12 '17 at 19:40
  • 20
    This answer from 2012 needs an update now that `with=FALSE` isn't needed. – Matt Dowle Dec 13 '17 at 07:51
4

The previous answer works, but here is the answer with the updated methods:

## CONVERT TO ZOO

#### old method
zooObj = zoo(x=dt[,-1,with=FALSE], order.by=dt$mydates)

#### alternatives
zooObj = zoo(x=dt[,!'mydates'], order.by=dt$mydates)
zooObj = zoo(x=dt[,-'mydates'], order.by=dt$mydates)

#### and if column name is a variable
myvar = 'mydates'
zooObj = zoo(x=dt[,!..myvar], order.by=dt[[myvar]])
zooObj = zoo(x=dt[,-..myvar], order.by=dt[[myvar]])

As an extension, if you wanted to remove more than one column from the data.table, try one of the following:

dt[,-c('value1', ..myvar)]
dt[, .SD, .SDcols = !c('value1', (myvar))]
TMo
  • 435
  • 4
  • 11