How can I create a sequence of the content of prior rows in an aggregation using data.table? e.g.
pets <- data.table(id = 1 : 12, pet = c(rep("dog", 1), "cat", "cat", rep("dog", 3), "cat", rep("dog", 4), "cat"), town = c(rep("boston", 9), rep("sf", 3)), names = letters[1:12])
id pet town names
1: 1 dog boston a
2: 2 cat boston b
3: 3 cat boston c
4: 4 dog boston d
5: 5 dog boston e
6: 6 dog boston f
7: 7 cat boston g
8: 8 dog boston h
9: 9 dog boston i
10: 10 dog sf j
11: 11 dog sf k
12: 12 cat sf l
would result in,
id pet town names prior
1: 1 dog boston a NA
2: 2 cat boston b a
3: 3 cat boston c a
4: 4 dog boston d NA
5: 5 dog boston e NA
6: 6 dog boston f NA
7: 7 cat boston g d e f
8: 8 dog boston h NA
9: 9 dog boston i NA
10: 10 dog sf j NA
11: 11 dog sf k NA
12: 12 cat sf l j k
Tried,
> pets[, .SD[, prior := paste(names[-.N], collapse = ' '), .(group=cumsum(c(0,diff(pet == "cat")) < 0))][pet != "cat", prior := ''] , by = town]
but that leads to,
Error in `[.data.table`(.SD, , `:=`(prior, paste(names[-.N], collapse = " ")), :
.SD is locked. Using := in .SD's j is reserved for possible future use; a tortuously flexible way to modify by group. Use := in j directly to modify by group by reference.
You don't need
.SD[,
and the grouping should be also based on 'town' from the expected result showed. Also assuming that you want to fill the missing values with''