0

This is directly related to the question asked in Extract all rows containing first value for each unique value of another column

but instead of returning the first year an ID was detected, I want to return all rows with only the second year.

Adapting the answers in the above thread from

test

    ID yr
1  54V  1
2  54V  1
3  54V  1
4  54V  2
5  54V  2
6  56V  2
7  56V  2
8  56V  3
9  59V  1
10 59V  2
11 59V  3

to

    test2 <- test[with(test, as.logical(ave(yr, ID, FUN = function(x) x==x[2L]))),]
         or
    test2 <- setDT(test)[, .SD[yr==yr[2L]], ID]

produces strange results.

   ID yr
1 54V  1
2 54V  1
3 54V  1
5 56V  2
6 56V  2
9 59V  2

My desired result would be

    ID yr
4  54V  2
5  54V  2
8  56V  3
10 59V  2

What am I doing wrong?

Lisarv
  • 87
  • 1
  • 9

1 Answers1

0
 subset(test,as.logical(ave(yr,ID,FUN=function(x)x==unique(x)[2]))) 
  ID yr
1 54V  2
2 54V  2
3 56V  3
4 59V  2

 library(data.table)
 setDT(test)[,.SD[yr==unique(yr)[2]],by=ID]
    ID V1
1: 54V  2
2: 54V  2
3: 56V  3
4: 59V  2

test%>%group_by(ID)%>%filter(yr==unique(yr)[2])
# A tibble: 4 x 2
     ID    yy
  <chr> <int>
1   54V     2
2   54V     2
3   56V     3
4   59V     2
Onyambu
  • 67,392
  • 3
  • 24
  • 53
  • Thank you, but how can I return ALL rows containing the second occurance, and not the unique? Say if 54V would have two years with yr 2. – Lisarv Feb 09 '18 at 17:42
  • In that case the results wont match the desired output – Onyambu Feb 09 '18 at 17:52
  • No, because of the example in this instance, but referring to the first question, it should be clear that I would need all rows, and not unique. I will have to change my example. Big thank you for the answer though. – Lisarv Feb 09 '18 at 17:59
  • Thank you, I can accept this as this answers the direct question in the post. But as my data set has many columns and I need to retain them all, as said in the previous post that I refer to, do you have any ideas how you can adapt the above code for that? – Lisarv Feb 09 '18 at 19:26
  • try the data.table one for your dataset it is eddited – Onyambu Feb 09 '18 at 19:34