Select first unique match for each ID in data.table (r)

user_11619734 0
Viewed 214

I have a data.table that consists of 3 columns. The first 2 are key IDs and the 3rd column represents all possible values that an ID can have.

Here is an example:

DT <- data.table( 
            Pol_No = c('a','b','b','c','c','c','c','c','c','c')  
          , Veh_No = c(1,1,2,1,1,1,2,3,3,3)
          , Value = c(1,1,2,3,4,5,6,3,4,5)
          )
DT
    Pol_No Veh_No Value
 1:      a      1     1
 2:      b      1     1
 3:      b      2     2
 4:      c      1     3
 5:      c      1     4
 6:      c      1     5
 7:      c      2     6
 8:      c      3     3
 9:      c      3     4
10:      c      3     5

I need to filter this table such that Value is unique for each Policy & Vehicle. So Row 4 would stay, but Row 9 would be filtered because the value of 4 would have already been assigned for [Pol_No:c , Veh_No:1]

The expected result is:

   Pol_No Veh_No Value
1:      a      1     1
2:      b      1     1
3:      b      2     2
4:      c      1     3
5:      c      2     6
6:      c      3     4

I've tried a lot of possibilities but the best I can come up with is:

Flt <- 
  DT[DT
     , .(Value)
     , on = .(Pol_No , Veh_No )
     , mult = 'first']
DT[ Value == Flt$Value,]

   Pol_No Veh_No Value
1:      a      1     1
2:      b      1     1
3:      b      2     2
4:      c      1     3
5:      c      2     6
6:      c      3     3

This is almost correct, but the Value for [c,3] has already been used in [c,1] so its still wrong.

Any idea on how to filter out a row if its already been used in the same key set?

data.table filter r join
3 Answers

How about something like this?

DT[,Count := sequence(.N),by = .(Pol_No,Value)][,.SD[min(Count),],by = .(Pol_No,Veh_No)]
   Pol_No Veh_No Value Count
1:      a      1     1     1
2:      b      1     1     1
3:      b      2     2     1
4:      c      1     3     1
5:      c      2     6     1
6:      c      3     4     2

You can get rid of the Count column later.

user_130953260

Thanks to Ian Campbell for solving the hardest part! I didn't mention this in my original post, but in cases where all acceptable values were used in previous vehicles, the table should filter the row as well (rather than return an already used Value)

e.g.

DT <- data.table(
            Pol_No = c('a','b','b','c','c','c','c','c','c','c','c','c','c','c')
          , Veh_No = c(1,1,2,1,1,1,2,3,3,3,4,4,5,5)
          , Value = c(1,1,2,3,4,5,6,3,4,5,2,3,2,3)
          )

Using Ian's code:

DT2 <- DT[,Count := sequence(.N),by = .(Pol_No,Value)][,.SD[min(Count),],by = .(Pol_No,Veh_No)][,Count:=NULL][]

And then adding the next filter seems to do the trick

DT2 <- DT2[, Dup :=  seq(.N) , by = c('Pol_No', 'Value') ]
DT2[Dup == 1,][,Dup:=NULL]

1:      a      1     1
2:      b      1     1
3:      b      2     2
4:      c      1     3
5:      c      2     6
6:      c      3     4
7:      c      4     2
user_116197340

Here is a recursive function that may help

f <- function(dt) {
  if (nrow(dt) == 1) {
    return(dt)
  }
  u <- f(dt[-.N])
  v <- dt[.N]
  if (all(is.na(u[v, Value, on = .(Pol_No, Veh_No)]) & !v[, Value] %in% u[Pol_No == v[, Pol_No], Value])) {
    return(rbind(u, v))
  }
  return(u)
}

such that

> f(DT)
   Pol_No Veh_No Value
1:      a      1     1
2:      b      1     1
3:      b      2     2
4:      c      1     3
5:      c      2     6
6:      c      3     4
user_121587570
Related