Geek Answers Handbook

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

For a data table. DT, grouped by site, sorted by time t, I need to change the last value of the variable in each group. I assume that this can be done using the link: =, but I have not yet found a way that works.

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 ta # 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, by 999, so the result is as follows:

 # site ta # 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 that .I and / or .N should be used, but I have not found a form that works. Using: = in the same expression as .I [.N] gives an error. The following are the line numbers on which the job should run:

 DT[, .I[.N], by=site] # site V1 # 1: A 5 # 2: B 9

but I can't seem to use this with assignment: =. The following are the 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 it better to do it the other way in R?

+9
r data.table
source share
1 answer

You can currently 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 transfer the replacement to RHS wrapped in {} and return the full vector

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

or use mult='last' and use by-without-by . For this, it is necessary that data.table be set by the groups of interest.

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

There is a feature request # 2793 that allows

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

but it remains to be realized

+18
source share

More articles:


All Articles