How do I select the first n rows of each group in the specified columns (after concatenation)?
I want to combine functions .SD
with by =
in a non-equi join:
data.table - select the first n rows within a group
Sample data:
tmp_dt1<- data.table(grp = c(1,2), time = c(0.2, 0.6, 0.4, 0.8, 0.25, 0.65))
tmp_dt2 <- data.table(grp = c(1,2), time_from = c(0.1, 0.5))
tmp_dt2 <- tmp_dt2[, time_to := time_from + 0.2]
> tmp_dt1
grp time
1: 1 0.20
2: 2 0.60
3: 1 0.40
4: 2 0.80
5: 1 0.25
6: 2 0.65
> tmp_dt2
grp time_from time_to
1: 1 0.1 0.3
2: 2 0.5 0.7
Now my desired output is the first time in each group that falls between the ranges defined in tmp_dt2
. I can get all moments like this:
> tmp_dt1[tmp_dt2, .(grp, time = x.time, time_from, time_to), on = .(grp, time >= time_from, time <= time_to)]
grp time time_from time_to
1: 1 0.20 0.1 0.3
2: 1 0.25 0.1 0.3
3: 2 0.60 0.5 0.7
4: 2 0.65 0.5 0.7
However, I am having some trouble extracting the first n lines from each grp
with an by
unbound one. As an example, when n = 1
, the desired result is:
tmp_dt1[tmp_dt2, .(grp, time = x.time, time_from, time_to),
on = .(grp, time >= time_from, time <= time_to)][, .SD[1], by = grp]
grp time time_from time_to
1: 1 0.2 0.1 0.3
2: 2 0.6 0.5 0.7
but something like:
> tmp_dt1[tmp_dt2, .(time = x.time[1], time_from[1], time_to[1]), on = .(grp, time >= time_from, time <= time_to), by = grp]
Error in `[.data.table`(tmp_dt1, tmp_dt2, .(time = x.time[1], time_from[1], :
object 'time_from' not found
does not work.
Usage is .SD
getting closer, but gives me a confusing end to the result in terms of the selected columns:
tmp_dt1[tmp_dt2, .SD[1], on = .(grp, time >= time_from, time <= time_to), by = grp]
grp time
1: 1 0.2
2: 2 0.6
The reason I don't want to do this in a chain is due to memory issues . Note that I'm only interested in this package issue data.table
.
source to share
If you want to minimize memory usage , another solution might be more memory efficient than the original chaining approach, although it looks strange to store the temporary result in a variable (but it only contains two columns and only the first n rows per group) and by - still use chaining (but on a smaller subset of the original data):
n = 1 # parameter: first "n" rows per group
selected.rows <- tmp_dt1[tmp_dt2, .(rownum = .I[1:n]), on = .(grp, time >= time_from, time <= time_to), by = grp]
tmp_dt1[selected.rows$rownum][tmp_dt2, .(grp, time = x.time, time_from, time_to), on = .(grp, time >= time_from, time <= time_to)]
Not very elegant and possibly slower (it duplicates the join logic and needs to be joined in half - albeit on a reduced subset in the second case) ...
The temporary result set contains the row number of each "match" in the original data table (using a character .I
data.table
):
selected.rows
grp rownum
1: 1 1
2: 2 2
It would be great to compare this solution to chaining using a real big data table ... (if I have more time I will check it out)
source to share