CODEWITHSUNDEEP
rdata.table
Misha
Misha

Reputation: 3126

Get first instance of condition in longitudinal dataset otherwise last instance

ll <- data.table(ID=rep(1:3,each=3),
    num=c(1:9),
    var=c("a","b","c","a","b","b","a","c","c"))
setorder(ll,ID,num,var)

Now I´d like to retrieve the first instance of "c" if present, but if not present I´s like to retrieve the last instance of not "c".

  ll[,.SD[var=="c"][1],by=.(ID)]

gives me the first instance of "c", but how can I also get the last instance of not "c"?

Desired result:

      ID   num    var
   <int> <int> <char>
1:     1     3      c
2:     2     6      b
3:     3     8      c

Upvotes: 2

Views: 62

Answers (1)

arg0naut91
arg0naut91

Reputation: 14764

One way could be:

ll[, .SD[if (any(var == 'c')) var == 'c' & rowid(var) == 1L else .N], by = .(ID)]

Output:

   ID num var
1:  1   3   c
2:  2   6   b
3:  3   8   c

As @Henrik pointed out, you may prefer to use the below approach if speed is an issue:

ll[ll[, .I[if(any(var == 'c')) which.max(var == "c") else .N], by = .(ID)]$V1]

Upvotes: 3

Related Questions