1
data=data.table("Student"=c(1,1,1,1,1,2,2,3,3,3,4,4,5,5,5,5,5),
"X1"=c(0,0,1,1,1,0,2,0,0,2,0,1,0,0,0,1,2))

I wish to count three objects.

First I wish to count how many Student have an 1 in the X1 column.

Second I wish to count how many Student have 2 in the X1 column before a 1.

Three I wish to count how many Student have 2 in the X1 column after a 1.

I seek data.table solution

bvowe
  • 3,004
  • 3
  • 16
  • 33

1 Answers1

1

We can use match to get the index of first occurrence of 1 or 2, check whether this is less than or greater than after grouping by 'Student', then we get the sum of the logical vectors created to return the count

library(data.table)
data[, .(First = any(X1 == 1),
         Second = match(2, X1, nomatch = 0) < match(1, X1, nomatch = 0),
         Third = match(2, X1, nomatch = 0) > match(1, X1, nomatch = 0) & any(X1 == 1)),
        Student][, lapply(.SD, sum), .SDcols = First:Third]
akrun
  • 874,273
  • 37
  • 540
  • 662
  • thank you so much, that helps count all of the different values but I am trying to count them based on the order – bvowe May 04 '20 at 19:02
  • thank you so much it works for First and Second but for Third it counts all '2' instead of just the '2' after the '1' so there should only be a 1 for Third and it is for Student = 5 – bvowe May 04 '20 at 19:09
  • 1
    @bvowe what are the expected numbers. I think it is because some doesn't have 1. So, I added a condition with `any(X1 == 1)` – akrun May 04 '20 at 19:11
  • thank you this is perfect. If you have a chance please see this post if you are available, https://stackoverflow.com/questions/61583036/r-configure-data-with-data-table/61583328?noredirect=1#comment108934474_61583328 – bvowe May 04 '20 at 19:12
  • 1
    yes thank you i was waiting for it to let me, thank you so much – bvowe May 04 '20 at 19:14