improvement on programmatically substituting columns in expressions

[arunsrinivasan]

require(data.table) # devel compiled on 3rd March 2018
dt <- data.table(x=1:5, y=6:10, z=11:15)
#    x  y  z
# 1: 1  6 11
# 2: 2  7 12
# 3: 3  8 13
# 4: 4  9 14
# 5: 5 10 15

Now we can subset cols using the .. notation conveniently as follows:

cols <- "z"
dt[, ..cols]
#     z
# 1: 11
# 2: 12
# 3: 13
# 4: 14
# 5: 15

Since ..var has a very special meaning, we could also allow this?

dt[, c(..cols, "x")]

Currently, it errors as follows:

# Error in eval(jsub, SDenv, parent.frame()) : object '..cols' not found

Haven't really given it a lot of thought.. Just came across it and wanted to file it as an issue so that it doesn't get lost.

[MichaelChirico]

of course dt[ , c(cols, 'x'), with = FALSE] still works... raises the question again of whether or not we're trying to phase out with = FALSE altogether

[franknarf1]

I like it. Here's a similar idea from #633 by Synergist:

dt <- data.table(x = 1:10, y = rnorm(10), z = runif(10))
cols <- c('y', 'z')
dt[, !..cols] # should return col x

[mattdowle]

Yes -- absolutely. The original news item in 1.10.2 (Jan 2017)

When j is a symbol prefixed with .. it will be looked up in calling scope and its value taken to be column names or numbers.

myCols = c("colA","colB")
DT[, myCols, with=FALSE]
DT[, ..myCols] # same
When you see the .. prefix think one-level-up like the directory .. in all operating systems meaning the parent directory. In future the .. prefix could be made to work on all symbols apearing anywhere inside DT[...]. It is intended to be a convenient way to protect your code from accidentally picking up a column name. Similar to how x. and i. prefixes (analogous to SQL table aliases) can already be used to disambiguate the same column name present in both x and i. A symbol prefix rather than a ..() function will be easier for us to optimize internally and more convenient if you have many variables in calling scope that you wish to use in your expressions safely. This feature was first raised in 2012 and long wished for, #633. It is experimental.

I've bolded the relevant sentence. I wasn't sure about it at the time and I hoped for feedback, before continuing. Seems like it's a go, then. IIRC, without looking at the code yet, it's not that hard as there's already a substitution of all appearances of get() and/or perhaps eval() to look in calling scope. At one time I thought of eval() as a good wrapper to convey "eval in calling scope" and I think [...] may already do that. Now, I prefer .. prefix as it's simpler and more robust. Since .. is a prefix on a symbol, we know it must be a symbol (unlike eval() function which could potentially be passed paste(...) or something.)

My recent tweet here seems to have yielded a positive response, too.
https://twitter.com/MattDowle/status/967290562725359617

[arunsrinivasan]

Nice! I like it too. It'd also, when implemented, take care of issues like:

dt[x > x]

which could be then done as:

dt[x > ..x]

[arunsrinivasan]

@MichaelChirico I assumed that's the case based on #2620

[mattdowle]

At one stage I think there was a suggestion to use a single . prefix to mean 'this scope for sure' and not in calling scope if it's not a column name (again, similar to single . directory). However, one character is a bit easier to miss when reading code. Easier and more robust to say .. must be used to get to calling scope and without that prefix, the symbol must be a column name. It would be a change with potential breakage but managed in the usual way e.g. options(datatable.strict.scope) FALSE to start with and then gradually changed to TRUE with notices and warnings over several years. The warnings could be quite advanced in this case saying exactly which symbols should be prefixed with .. as it would detect at runtime which ones it was finding in calling scope. I think users would like it as the move would be towards robustness and readability, and the changes can be made by user in their own time over the years.

[franknarf1]

.. must be used to get to calling scope and without that prefix, the symbol must be a column name.

Looking at #697 (comment), I guess the exception for a single symbol in i will be kept (interpreted as a join and so looking in calling scope even without ..)?

[mattdowle]

Hm, yes. The difference there is that a single symbol in i doesn't make sense if that were to mean a column name. Other than, if that column is type logical. Personally I prefer to write and read DT[someCol==TRUE] anyway and leave it to optimization to do that efficiently.

[msummersgill]

Looking forward to this one! Seems like this has potential to replace a boatload of eval/parse/quote/get that litters most of the code I've written since going full data.table.

One pipe dream of mine might look like the following, where both expressions would give the same output. (As I started to type this out I did start to question if this is even realistic)

set.seed(1234)
DT <- data.table(foo = rep(LETTERS[1:2],8),
                 bar = rep(letters[17:20],4),
                 month = rep(month.name[1:4],4),
                 day = seq_len(16),
                 yyy = rnorm(16,mean = 0.5,1.5))

## Expression 1: with hard coded columns
DT[yyy > 0,.(NewCol = paste(month, day),
             bar,
             yyy), by = foo]
## Expression 2: everything passed by reference
A = "yyy"
B = 0
C = "NewCol"
D = "month"
E = "day"
G = "foo"
H = c("bar","yyy")

DT[..A > ..B , .(..C = paste(..D, ..E),
                 ..H), by = ..G]

   foo      NewCol bar       yyy
1:   B  February 2   r 0.9161439
2:   B  February 6   r 1.2590838
3:   B February 14   r 0.5966882
4:   B    April 16   t 0.3345718
5:   A     March 3   s 2.1266618
6:   A   January 5   q 1.1436870
7:   A    March 15   s 1.9392411

However, even as I write this I'm starting to see some potential hang-ups, in particular with how the i part of the expression is evaluated.

In the case below, it does seem like there is potential ambiguity in whether ..B would be treated as a column name if a column xyz existed in DT. Is my interpretation anywhere close to what you're currently planning on implementing, and should I perhaps be using a single . notation in some places?

A = "foo" ## intended column name
B = "xyz" ## intended literal character string for comparison

DT[..A == ..B]

Whatever gets implemented, I'm looking forward to it!

---

Some relevant stack overflow questions (with lots of responses from contributors here):

• How can one work fully generically in data.table in R with column names in variables
• get(x) does not work in R data.table when x is also a column in the data table
• Subsetting data.table using variables with same name as column
• Keyed lookup on data.table without 'with'
• data.table := assignments when variable has same name as a column

[mattdowle]

Ok, now in master. Just for symbols used in j= for now.
Please test it out all.
See the NEWS item and the new tests in PR #2704.

Looking at @msummersgill's comment more closely just now, there seems to be two different ideas :

1. Fetching the value of a variable in calling scope to use in a j expression that uses column names too; e.g.beta=10L; DT[, sum(colB)*..beta]. This works fine already (without the .. prefix on beta) so long as there is never any chance of DT having a column called beta. The .. makes that safer.

2. Use a column name in a j= expression, where the column name to use is defined by a variable in calling scope. With the .. prefix now implemented, this could now be done reliably with get(..whichCol) where whichCol is in calling scope and will now work even if that DT ever contains a column called whichCol, too. However, using get() is a bit onerous and it's a bit harder to optimize internally (currently get invokes all columns to be placed in .SD, for example). Further, it's possible that whichCol in calling scope could contain a name that isn't actually a column name. In that case get() would itself look in calling scope and, if unlucky, could pick up an unintended value in calling scope. A syntax to for-sure get-that-column, and error otherwise, would be nice.

I had been thinking only of case 1 so far, and that's what @arunsrinivasan raised this issue about and @franknarf1 and @MichaelChirico agreed with further examples on that. That's what the now-merged PR does.

But @msummersgill is suggesting something completely different (case 2), I hadn't considered before. That's a very neat idea and I like it. Do we need a different prefix for case 2 then and do that too?

If I've understood correctly so far, which case does !!var in tidyeval do?

Since DT[, ..cols] (i.e. where j= is a single symbol) was the only case where .. worked before, I see how the view that .. means to get was formed. But it's just that ..cols happens to be used there where the value of ..cols means in that context to select that column, or more often a set of columns.

And now I see, @msummersgill already detailed these two cases at the end of his comment, but I didn't digest that before.

[mattdowle]

Looks like we can't have _ or __ prefix as that doesn't parse. We could have _ or __ postfix. But I somehow really like prefix rather than postfix.
Then I thought, ok, maybe @whichCol. This would mean get(..whichCol) but where the value of whichCol must be a column name, otherwise error. But that doesn't parse either.
So it's pointing back towards the old EVAL = parse(text=paste())) which I don't mind at all, but can get a bit ugly, even when using the EVAL wrapper. Again, as @msummersgill already alluded to.

So, taking Matt S's example, how about :

DT[ " @A > @B , .(@C = paste(@D, @E),  @H), by = @G " ]

The rule would be (a little like the first argument of fread) that if i= was a string containing one or more @ then it would switch to token replacement mode. In this mode, all other arguments (j=, by=, etc) must be missing. The internals would replace the tokens and then re-parse. It might not be that hard to implement.

More examples :

col = "x"
DT[, ..col]               # select column "x" (even if "cols" is a column name too)
DT[, c(..col, "y")]       # select columns "x" and "y"
thresh = 6.5
DT[ x > ..thresh ]        # select rows where "x" column > 6.5
DT[ "@col > y" ]          #  same as DT [ x > y ]  i.e. comparison of two columns
anyString = "..thresh"
DT[ "@col > @anyString" ] # same as DT[ x > ..thresh ]

It's now actually an advantage that @ prefix doesn't parse, because visually, it is distinguishable from other regular R code. The restriction would be you couldn't directly use S4's obj@slot when in token replacement mode. You'd have to move the "obj@slot" into a token's value. It doesn't have to be @. It could be anything. Maybe $ or $$ prefix, conveying $ for string.