I have two DT's (DT1, DT2). DT1 has two columns (V1, V2). DT2 also has two columns (V1, V3).
Each unique value in V1 represents a unit that has one or more values of V2 and one or more values of V3 as attributes.
The goal is to obtain all combinations of V2 values (e.g. 1 and 2) but based on two conditions.
The combination (1, 2) should remain only if there is at least on V1 unit that has 1 but not 2 as V2 value AND a different V1 unit that has 2 but not 1 (regardless of other V2 values)
For each combination of V2 values there may be many combinations of two different V1 units that match the first condition. Now, only those combinations of V2 values should remain where at least one combination of V1 units has no match in V3 values. I.e., if at least one V3 value is attributed to both V1 units, this combination of V1 units is excluded. Only if at least one V1 combination remains, the V2 combination is retained.
In short: A combination of two different V2 values should only be retained if there is at least one combination of two different V1 units where one has the first V2 value but not the second and vice versa AND those two V1 units have not a single V3 "attribute" in common.
Here is an example of code to reproduce the DT's and a solution that worked for me, but leads to computational issues when I use it on 500 different V2 values and thousands of unique V1 and V3 values. I highly appretiate a solution that saves computational power. The problem in the current code is that merging the data tables creates enormously large DTs that my device cannot handle. Maybe there is a solution using lists or matrices.
Thanks for your help!
library(tidyverse)
library(data.table)
DT1 = as.data.table(cbind(c(1,2,3,3,4,5,6,6,6,7,7,8,9,9,10,11,12,12,12,13,14,14,15,15,16,16,17,17,18,18,18,19,19,20,20,21,21,21,21,21,21,22,22,23,24,25,26,26,27,27,28,29,30,30,30,30,30,31,32,32,33,34,35,35,36,37,37,37,38,39,40,41,42,42,43,43,43,44,45,46,46,47,48,49,49,50,51,51,51,52,52,53,54,54,55,56,56,56,57,58,58,59,60,60,61,62,62,62,63,64,64,64,64),
c(6,48,10,13,46,46,1,2,33,16,34,11,18,36,34,37,63,65,72,56,66,70,62,66,18,37,59,60,49,61,64,54,64,61,68,26,30,50,58,64,71,41,67,25,46,62,62,70,62,70,62,3,49,50,64,69,71,66,7,44,27,70,12,53,62,42,51,71,44,66,24,19,40,47,16,28,62,62,52,4,5,64,57,14,15,3,35,36,37,64,70,46,7,8,38,16,17,62,43,9,29,69,36,37,39,22,31,32,20,21,23,45,55),
c(rep(1,113))))
names(DT1)[3] = "count"
DT2 = as.data.table(cbind(c(1,2,3,3,4,5,6,7,8,9,10,11,12,12,13,13,14,15,15,15,16,17,17,18,18,18,18,18,18,18,19,19,19,20,20,20,20,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,22,23,24,25,25,25,26,26,27,27,28,29,30,30,30,30,30,30,30,30,31,32,33,33,33,34,35,35,35,36,37,37,37,37,38,39,40,41,42,43,44,45,46,46,46,46,47,47,47,47,47,47,48,49,50,51,52,52,53,54,55,55,56,56,57,58,58,58,59,60,61,62,62,62,63,64,64),
c(56,43,93,108,43,43,35,31,39,40,40,40,44,45,59,61,60,58,59,67,31,13,62,3,17,37,54,55,92,97,44,46,47,18,48,49,50,3,5,11,12,16,17,24,25,28,32,36,37,38,91,92,94,95,96,102,103,104,106,112,113,107,34,26,51,52,53,33,42,33,41,57,77,5,14,15,16,22,23,98,99,66,101,68,69,70,105,19,20,21,64,6,7,8,9,30,109,61,71,63,41,41,110,72,73,74,75,2,3,29,87,88,100,13,78,77,10,29,87,26,1,81,82,4,27,26,65,79,89,90,86,80,83,84,85,76,111,114)))
names(DT2) = c("V1", "V3")
DT1 <- DT1[DT1, on = "count", allow.cartesian = T] # create all combinations of V2 values with corresponding combinations of V1 values/units
DT1 <- DT1 %>%
filter(V2 != i.V2 & V1 != i.V1) # remove combinations of the same V2 value (not interested in) and remove V2 combinations that are attributed to the same V1 unit
DT3 <- DT1 %>%
select(V1, i.V1) %>%
distinct() %>% # reduce to unique V1 combinations
left_join(., DT2, by = "V1") # add V3 attributes to first V1 unit
DT2 <- DT2 %>%
rename(i.V1 = V1) %>%
rename(i.V3 = V3)
DT3 <- DT3 %>%
left_join(., DT2, by = "i.V1") %>% # add V3 attributed to the respective second V1 unit
filter(V3 == i.V3) %>% # select only those V1 combinations where at leat one V3 attribute matches
select(V1, i.V1) %>%
distinct() # reduce to unique V1 combinations
DT1 <- DT1 %>%
anti_join(., DT3, by = c("V1", "i.V1")) %>% # now anti-join to retain only those V2 combinations that are not in DT3
select(count, V2, i.V2) %>%
distinct()