1

I've a large grouped data I would like to filter. The sample data is shown below.

 Data <- data.frame (ID1 = c(1,1,1,2,2,2,3,3,3), Score1 = c(360,360,360,250,250,250,195,195,195), ID2 = c(7,8,9,7,225,98,7,225,174), Score2 = c(330,150,100,330,275,180,330,275,210))

Edit: Pasting an alternate example that has an edge case not in the original:

Data <- data.frame (ID1 = c(1,1,1,2,2,2,3,3,3), Score1 = c(360,360,360,250,250,250,195,195,195), ID2 = c(7,8,9,7,8,98,7,225,174), Score2 = c(330,275,100,330,275,180,330,275,210))

The data is grouped by ID1 and I would like to filter the first row of each group but if ID2 is selected by the previous group, it'll no longer be a candidate for the next group.

The expected outcome for the alternate example is:

Data_Filtered <- data.frame (ID1 = c(1,2,3), Score1 = c(360,250,195), ID2 = c(7,8,225), Score2 = c(330,275,275))
BMM
  • 63
  • 7
  • Does this answer your question? [Select first and last row from grouped data](https://stackoverflow.com/questions/31528981/select-first-and-last-row-from-grouped-data) – cardinal40 Feb 10 '20 at 20:03

1 Answers1

1

We can group by 'ID1' and slice the first row

library(dplyr)
Data %>%         
    distinct(ID2, .keep_all = TRUE) %>%
    group_by(ID1) %>% 
    slice(1)

Update

With the updated dataset, one option is

lst1 <- split(Data, Data$ID1)
out <- lst1[[1]][1,]
for(i in 2:length(lst1)) {
    out <- rbind(out, lst1[[i]][!lst1[[i]]$ID2 %in% out$ID2,][1,])
 }
out
#  ID1 Score1 ID2 Score2
#1   1    360   7    330
#5   2    250   8    275
#8   3    195 225    275
akrun
  • 874,273
  • 37
  • 540
  • 662
  • It works for the case offered but doesn't work if the next item was in any order within the previous group. So it doesn't actually keep track properly the previous selections. – IRTFM Feb 10 '20 at 20:22
  • @akrun Do you have a better idea to address the alternate example - 42 included in the question? Thank you. – BMM Feb 12 '20 at 14:45
  • @user9899857 Can you please update your question with the expected output for that as I find some inconsistencies in that example – akrun Feb 12 '20 at 15:21
  • @akrun I just updated the expected outcome. Thanks. – BMM Feb 12 '20 at 15:30
  • @BMM Updated the post. Sorry, I forgot about this. – akrun Feb 13 '20 at 02:59