How to change the last value in each group by reference, in data.table

data.table, r

Solution

Currently you can use:

DT[DT[, .I[.N], by = site][['V1']], a := 999]
# or, avoiding the overhead of a second call to `[.data.table`
set(DT, i = DT[,.I[.N],by='site'][['V1']], j = 'a', value = 999L)

alternative approaches:

use `replace`...

DT[, a := replace(a, .N, 999), by = site]

or shift the replacement to the RHS, wrapped by `{}` and return the full vector

DT[, a := {a[.N] <- 999L; a}, by = site]

or use `mult='last'` and take advantage of `by-without-by`. This requires the data.table to be keyed by the groups of interest.

 DT[unique(site), a := 999, mult = 'last']

There is a feature request #2793 that would allow

DT[, a[.N] := 999]

but this is yet to be implemented

Problem

For a data.table DT grouped by site, sorted by time t, I need to change the last value of a variable in each group. I assume it should be possible to do this by reference using :=, but I haven't found a way that works yet. Sample data: ``` require(data.table) # using 1.8.11 DT <- data.table(site=c(rep("A",5), rep("B",4)),t=c(1:5,1:4),a=as.double(c(11:15,21:24))) setkey(DT, site, t) DT # site t a # 1: A 1 11 # 2: A 2 12 # 3: A 3 13 # 4: A 4 14 # 5: A 5 15 # 6: B 1 21 # 7: B 2 22 # 8: B 3 23 # 9: B 4 24 ``` The desired result is to change the last value of a in each group, for example to 999, so the result looks like: ``` # site t a # 1: A 1 11 # 2: A 2 12 # 3: A 3 13 # 4: A 4 14 # 5: A 5 999 # 6: B 1 21 # 7: B 2 22 # 8: B 3 23 # 9: B 4 999 ``` It seems like .I and/or .N should be used, but I haven't found a form that works. The use of := in the same statement as .I[.N] gives an error. The following gives me the row numbers where the assignment is to be made: ``` DT[, .I[.N], by=site] # site V1 # 1: A 5 # 2: B 9 ``` but I don't seem to be able to use this with a := assignment. The following give errors: ``` DT[.N, a:=999, by=site] # Null data.table (0 rows and 0 cols) DT[, .I[.N, a:=999], by=site] # Error in `:=`(a, 999) : # := and `:=`(...) are defined for use in j, once only and in particular ways. # See help(":="). Check is.data.table(DT) is TRUE. DT[.I[.N], a:=999, by=site] # Null data.table (0 rows and 0 cols) ``` Is there a way to do this by reference in data.table? Or is this better done another way in R?

Original source