Filter to keep only one-to-one matches using data.table

user_4876794 0
Viewed 61

I am working with a large table, containing two ID columns. e.g.

ID1    ID2
01A    XY1
01B    XY4
...

There are duplicate values in both ID columns, and a decision has been made to only keep cases where there is a one-to-one match between the two IDs. i.e. if any of ID1 values match to more than one ID2 value, then all of the rows with that value of ID1 are removed. And vice versa.

I have written some code using dplyr that works:

df %>%
  group_by(ID1) %>%
  filter(n_distinct(ID2)==1) %>%
  distinct() %>%
  filter(!is.na(ID2)) %>%
  group_by(ID2) %>%
  filter(n_distinct(ID1)==1) %>%
  distinct() %>%
  filter(!is.na(ID1))

However, this is quite slow to run due to the size of the table. I am trying to figure out how to do the same thing using data.table. I figure it must use some combination of unique and uniqueN, but can't seem to get it quite right.

Any help much appreciated.

data-wrangling data.table matching r
2 Answers

In data.table, you can do -

library(data.table)

setDT(df)

na.omit(unique(df[, .SD[uniqueN(ID2) == 1], ID1][, 
            .SD[uniqueN(ID1) == 1], ID2]))
user_39629140

We can do

library(data.table)
tmp <- setDT(df)[df[, .I[uniqueN(ID2)== 1], ID1]$V1]
na.omit(unique(tmp[tmp[, .I[uniqueN(ID1) == 1], ID2]$V1]))
user_37322710
Related