Finding group with distinct (non-overlapping) elements

user_13223092 0
Viewed 141

I have a simple dataframe with group IDs and elements of each group, like this:

x <- data.frame("ID" = c(1,1,1,2,2,2,3,3,3), "Values" = c(3,5,7,2,4,5,2,4,6))

Each ID may have a different number of elements. Now I want to find all IDs that have distinct elements with other IDs. In this example, ID1 and ID3 will be selected because they have distinct elements (3,5,7 vs 2,4,6). I also want to copy these unique IDs and their elements into a new dataframe, similar to the original.

How would I do that in R? My skills with R is quite limited.

Thank you very much!

Bests,

dataframe r
3 Answers

Seems like a good question for igraph cliques with one edge to another clique but I cant seem to wrap my head on how to use it.

Anyway, here is an option applying join to identify IDs with same Values and then anti-join to remove those IDs using data.table:

library(data.table)
DT <- as.data.table(x)
for (i in DT[, unique(ID)]) {
    dupeID <- DT[DT[ID==i], on=.(Values), .(ID=unique(x.ID[x.ID!=i.ID]))]
    DT <- DT[!dupeID , on=.(ID)]
}

output:

   ID Values
1:  1      3
2:  1      5
3:  1      7
4:  3      2
5:  3      4
6:  3      6
user_19894800

You can try the following code, where the y is the list of data frames (including all data frames that have exclusive Value)

xs <- split(x,x$ID)
id <- names(xs)
y <- list()
ids <- seq_along(xs)
repeat {
  if (length(ids)==0) break;
  y[[length(y)+1]] <- xs[[ids[1]]]
  p <- ids[[1]]
  qs <- p
  for (q in ids[-1]) {
    if (length(intersect(xs[[p]]$Value,xs[[q]]$Value))==0) {
      y[[length(y)]] <- rbind(y[[length(y)]],xs[[q]])
      qs <- c(qs,q)
    }
  }
  ids <- setdiff(ids,qs)
}

Example

x <- data.frame("ID" = c(1,1,1,2,2,2,3,3,3,4,4), 
                "Values" = c(3,5,7,2,4,5,2,4,6,1,3))

> x
   ID Values
1   1      3
2   1      5
3   1      7
4   2      2
5   2      4
6   2      5
7   3      2
8   3      4
9   3      6
10  4      1
11  4      3

then you will get

> y
[[1]]
  ID Values
1  1      3
2  1      5
3  1      7
7  3      2
8  3      4
9  3      6

[[2]]
   ID Values
4   2      2
5   2      4
6   2      5
10  4      1
11  4      3
user_121587570 
x <- data.frame("ID" = c(1,1,1,2,2,2,3,3,3), "Values" = c(3,5,7,2,4,5,2,4,6))
gps = split(x, x$ID)
nGroups = length(gps)

k = 1
results = data.frame(ID = NULL, Values = NULL) 

for(i in 1:(nGroups - 1)){
  j = i + 1
  while(j <= nGroups){
    if(length(intersect(gps[[i]]$Values, gps[[j]]$Values)) == 0){
      print(c(i,j))
      results = rbind(results, gps[[i]], gps[[j]]) 
    }
    j = j + 1
  }
}
results

> results
  ID Values
1  1      3
2  1      5
3  1      7
7  3      2
8  3      4
9  3      6
user_37469920
Related