Create sequence within aggregate function

215 views Asked by At

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.
1

There are 1 answers

2
akrun On BEST ANSWER

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 ''

pets[, prior:=paste(names[pet!='cat'], collapse= ' '),
  .(group=cumsum(c(0, diff(pet=='cat')) <0), town)][pet!='cat', 
       prior := '', by=town]
#     id pet   town names prior
#  1:  1 dog boston     a      
#  2:  2 cat boston     b     a
#  3:  3 cat boston     c     a
#  4:  4 dog boston     d      
#  5:  5 dog boston     e      
#  6:  6 dog boston     f      
#  7:  7 cat boston     g d e f
#  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   j k