Select subset of columns in data.table R

Question

I have a data table with a bunch of columns, e.g.:

dt<-data.table(matrix(runif(10*10),10,10))

I want to perform some operation on the data table, such as producing a correlation matrix (cor(dt)). In order to do this, I want to remove a few columns that contain non-numeric values or values outside a certain range.

Let's say I want to find the correlation matrix excluding V1, V2, V3 and V5.

Here is my current approach:

cols<-!(colnames(dt)=="V1" | colnames(dt)=="V2" | colnames(dt)=="V3" | colnames(dt)=="V5")
new_dt<-subset(dt,,cols)
cor(new_dt)

I find this pretty cumbersome, considering data.table syntax is usually so elegant. Is there a better method of doing this?

Instead of `==`, you could use `%in%`. `!colnames(dt) %in% paste0('V', c(1:2,3,5))` — akrun, Jan 22 '15 at 17:11

Arun · Accepted Answer · 2017-02-24T22:51:20.880

100

Use with=FALSE:

cols = paste("V", c(1,2,3,5), sep="")

dt[, !cols, with=FALSE]

I suggest going through the "Introduction to data.table" vignette.

Update: From v1.10.2 onwards, you can also do:

dt[, ..cols]

See the first NEWS item under v1.10.2 here for additional explanation.

edited Feb 24 '17 at 22:51

answered Jan 22 '15 at 17:12

Arun

116,683
26
284
387

15

to avoid `with = FALSE` , you can also use .SD like this: `dt[, .SD, .SDcols = cols]` which will return a subset of your data defined by the columns you put in `cols`. I find this to be easier to implement programmatically. – Mark Danese Jan 22 '15 at 17:47
1

Great, but why avoid `with=FALSE`? How is it difficult to program with? You pass a character vector in both `.SDcols` and `with=FALSE`. – Arun Jan 22 '15 at 17:52
7

Sorry -- meant to clarify that this is simpler for me to remember. I can never remember when things have to be quoted and when they do not, and consequently when to use `with=FALSE` and when not to. I find this code more readable than when using `with=FALSE` because I don't have to remember why I am "turning off" one of the nice features of data.table -- not having to quote column names. – Mark Danese Jan 22 '15 at 18:04
2

I see. It's very similar to `with()` in base R, if that helps. I understand that `.SDcols` is convenient for you, and that's fine. Although I can't help but feel that the issue of *readability*, for some reason, is seen through a magnifying lens since recently in R programming, especially when it comes to data.table and dplyr syntax. – Arun Jan 22 '15 at 18:12

score 21 · Answer 2 · edited Apr 18 '20 at 02:15

You can do

dt[, !c("V1","V2","V3","V5")]

to get

            V4         V6         V7        V8         V9        V10
 1: 0.88612076 0.94727825 0.50502208 0.6702523 0.24186706 0.96263313
 2: 0.11121752 0.13969145 0.19092645 0.9589867 0.27968190 0.07796870
 3: 0.50179822 0.10641301 0.08540322 0.3297847 0.03643195 0.18082180
 4: 0.09787517 0.07312777 0.88077548 0.3218041 0.75826099 0.55847774
 5: 0.73475574 0.96644484 0.58261312 0.9921499 0.78962675 0.04976212
 6: 0.88861117 0.85690337 0.27723130 0.3662264 0.50881663 0.67402625
 7: 0.33933983 0.83392047 0.30701697 0.6138122 0.85107176 0.58609504
 8: 0.89907094 0.61389815 0.19957386 0.3968331 0.78876682 0.90546328
 9: 0.54136123 0.08274569 0.25190790 0.1920462 0.15142604 0.12134807
10: 0.36511064 0.88117171 0.05730210 0.9441072 0.40125023 0.62828674

score 15 · Answer 3 · edited Apr 18 '20 at 02:16

15

Another alternative is to use .SDcols

cols = paste0("V", c(1,2,3,5))
dt[, .SD, .SDcols=-cols]

edited Apr 18 '20 at 02:16

MichaelChirico

33,841
14
113
198

answered Jan 25 '15 at 21:32

Jeff

12,147
10
51
87

IRTFM · Answer 4 · 2015-01-22T17:34:00.460

This seems an improvement:

> cols<-!(colnames(dt) %in% c("V1","V2","V3","V5"))
> new_dt<-subset(dt,,cols)
> cor(new_dt)
            V4          V6          V7          V8         V9         V10
V4   1.0000000  0.14141578 -0.44466832  0.23697216 -0.1020074  0.48171747
V6   0.1414158  1.00000000 -0.21356218 -0.08510977 -0.1884202 -0.22242274
V7  -0.4446683 -0.21356218  1.00000000 -0.02050846  0.3209454 -0.15021528
V8   0.2369722 -0.08510977 -0.02050846  1.00000000  0.4627034 -0.07020571
V9  -0.1020074 -0.18842023  0.32094540  0.46270335  1.0000000 -0.19224973
V10  0.4817175 -0.22242274 -0.15021528 -0.07020571 -0.1922497  1.00000000

This one is not quite as easy to grasp but might have use for situations there there were a need to specify columns by a numeric vector:

subset(dt, , !grepl(paste0("V", c(1:3,5),collapse="|"),colnames(dt) ))

score 4 · Answer 5 · edited Apr 24 '20 at 06:43

To subset by column index (to avoid typing their names) you can do

dt[, .SD, .SDcols = -c(1:3, 5L)]

result seems ok

            V4          V6         V7         V8         V9       V10
 1: 0.51500037 0.919066234 0.49447244 0.19564261 0.51945102 0.7238604
 2: 0.36477648 0.828889808 0.04564637 0.20265215 0.32255945 0.4483778
 3: 0.10853112 0.601278633 0.58363636 0.47807015 0.58061000 0.2584015
 4: 0.57569100 0.228642846 0.25734995 0.79528506 0.52067802 0.6644448
 5: 0.07873759 0.840349039 0.77798153 0.48699653 0.98281006 0.4480908
 6: 0.31347303 0.670762371 0.04591664 0.03428055 0.35916057 0.1297684
 7: 0.45374290 0.957848949 0.99383496 0.43939774 0.33470618 0.9429592
 8: 0.99403107 0.009750809 0.78816609 0.34713435 0.57937680 0.9227709
 9: 0.62776909 0.400467655 0.49433474 0.81536420 0.01637135 0.4942351
10: 0.10318372 0.177712847 0.27678497 0.59554454 0.29532020 0.7117959

This is also useful when the columns come from a vector which includes variables, e.g. `dt[, .SD, .SDcols = -c("Acol", "Bcol", colVar1, colVar2)]` because the following does *not* work: `dt[, !c("Acol", "Bcol", colVar1, colVar2)]` — benimwolfspelz, Apr 20 '23 at 14:48

George Shimanovsky · Answer 6 · 2017-09-06T20:46:07.183

If it's not mandatory to specify column names:

> cor(dt[, !c(1:3, 5)])
             V4          V6          V7         V8          V9         V10
V4   1.00000000 -0.50472635 -0.07123705  0.9089868 -0.17232607 -0.77988709
V6  -0.50472635  1.00000000  0.05757776 -0.2374420  0.67334474  0.29476983
V7  -0.07123705  0.05757776  1.00000000 -0.1812176 -0.36093750  0.01102428
V8   0.90898683 -0.23744196 -0.18121755  1.0000000  0.21372140 -0.75798418
V9  -0.17232607  0.67334474 -0.36093750  0.2137214  1.00000000 -0.01179544
V10 -0.77988709  0.29476983  0.01102428 -0.7579842 -0.01179544  1.00000000

score 2 · Answer 7 · answered Jan 22 '15 at 17:19

2

Option using dplyr

require(dplyr)
dt<-as.data.frame(matrix(runif(10*10),10,10))
dt <- select(dt, -V1, -V2, -V3, -V4)
cor(dt)

answered Jan 22 '15 at 17:19

Feargal Ryan

454
3
9

Or `select(dt, -(V1:V4))`, since they're sequential in column order. Though I think OP is looking for `select(dt, -(V1:V3), -V5)` – Gregor Thomas Jan 22 '15 at 17:42

Select subset of columns in data.table R

7 Answers7

Linked

Related