Why a vector of column names can't be used to filter rows in data.table?

user_16053453 0
Viewed 211

Suppose I have the following data dt and a vector of column names cols

dt <- data.table(id = letters[1:10], amount = 1:10, id2 = c(rep('a',5),rep('b',5)))
cols <- 'id'

Can someone help me to understand why this approach not works

dt[cols=='a']

but this one works?

dt[get(cols)=='a']

Both approachs shouldn't return the same thing in vec?

data.table r
2 Answers

Akrun has already offered a helpful solution to your problem, so I will offer the diagnosis.

Filtering a data.table

To filter dt as you intend, data.table needs an entire vector of ten logical values, corresponding to the ten rows in dt, to indicate which of the rows to keep (TRUE) and which to discard (FALSE). So when you filter dt, you must include such a logical vector (or something that evaluates to it) within the [ brackets. Here is an artificial example:

dt[c(TRUE, FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, FALSE, FALSE, FALSE)]

This would give us the filtered results in the following data.table:

   id amount id2
1:  a      1   a
2:  d      4   a
3:  e      5   a
4:  g      7   b

Why cols Fails

Since cols has a character value of 'id'

cols <- 'id'

then comparing cols == 'a' is the same as comparing 'id' == 'a', which is obviously FALSE:

dt[cols == 'a']
# ...is the same as...
dt['id' == 'a']
# ...is the same as...
dt[FALSE]

This provides only a single logical value (FALSE), rather than the vector of ten logical values that data.table really needs for filtering.

The result is a data.table that omits everything

Empty data.table (0 rows and 3 cols): id,amount,id2

in contrast to dt[TRUE], which includes everything:

    id amount id2
 1:  a      1   a
 2:  b      2   a
 3:  c      3   a
 4:  d      4   a
 5:  e      5   a
 6:  f      6   b
 7:  g      7   b
 8:  h      8   b
 9:  i      9   b
10:  j     10   b

Why get(cols) Works

When you use get('my_variable'), you are telling R to "get" the value of my_variable from the surrounding environment. If you run get() within the [ operation on dt

dt[get(# ...
   ]

R will search within dt for a column by the given name, and it will return that column (a vector) as the value for get(). So using get('id') in this context will give you the column id in dt, which is the vector

c('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j')
# [1] "a" "b" "c" "d" "e" "f" "g" "h" "i" "j"

Now we put it all together:

dt[get(cols) == 'a')
# ...is the same as...
dt[get('id') == 'a')
# ...is the same as...
dt[c('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j') == 'a']
# ...is the same as...
dt[c(TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE)]

This gives us the filtered results in the following data.table:

   id amount id2
1:  a      1   a
user_160289660

We could evaluate on a symbol

 dt[eval(as.name(cols)) == 'a']
   id amount id2
1:  a      1   a

Or specify the .SDcols

dt[dt[, .SD[[1]] == 'a', .SDcols = cols]]
 id amount id2
1:  a      1   a

Or directly subset .SD if there is a single column

dt[dt[, .SD[[cols]] == 'a']]
   id amount id2
1:  a      1   a

The preferred method would be through .SDcols as get can sometimes have env issues, while eval (not so recommended by the community)

user_37322710
Related