Extract observations that match all possible permutations that match two columns-CodePudding

I have a data frame with 10,000 observations with two paired dataset. In another dataset, I have a few selected paired dataset.

test <- data.frame (iso1=c("A", "B", "C"))

data <- data.frame(hosp1=c("A", "B", "C", "D", "E", "C", "A", "B"),
           hosp2=c("B", "c", "F", "C", "G", "A", "H", "A"),
                   dist= c(12,32,23,12,12,45,13))

Now, I want to extract all observations that forms a possible permutation of data from dataset "data" such as "A" & "B", "B" & "A", "A" & "C", "C" & "A", "B" & "C", "C" & "B" using the test data

I expect to get something like this

hosp1  hosp2   dist
A       B       12
B       C       23
C       A       12
B       A       13

CodePudding user response：

Comparing a concatenated combination against concatenated hosp rows. Then asking... is "A B" in list ....

data <- data.frame(hosp1=c("A", "B", "C", "D", "E", "C", "A", "B"),
                   hosp2=c("B", "C", "F", "C", "G", "A", "H", "A"),
                   dist= c(12, 32,  23,   12,   12  ,45,  13, 13)) ### i added a missing value here

combo<-(expand.grid(test$iso1, test$iso1))

data[paste(data$hosp1, data$hosp2) %in% paste(combo$Var1, combo$Var2),]


  hosp1 hosp2 dist
1     A     B   12
2     B     C   32
6     C     A   45
8     B     A   13

CodePudding user response：

You can just test whether test$iso1 appears in both hosp1 and hosp2:

data[data$hosp1 %in% test$iso1 & data$hosp2 %in% test$iso1, ]

  hosp1 hosp2 dist
1     A     B   12
2     B     C   32
6     C     A   45
8     B     A   13

Note, I fixed an uncapitalized letter and a missing dist value in your example data.

CodePudding user response：

Perhaps we could just filter with if_all to find the if any of the elements in 'iso1' column are found in the 'hosp' columns

library(dplyr)
data %>% 
  filter(if_all(starts_with('hosp'), ~ .x %in% test$iso1))

-output

   hosp1 hosp2 dist
1     A     B   12
2     B     C   23
3     C     A   12
4     B     A   13

Or in base R

subset(data, Reduce(`&`, lapply(data[1:2], `%in%`, test$iso1)))
  hosp1 hosp2 dist
1     A     B   12
2     B     C   23
6     C     A   12
8     B     A   13

data

data <- structure(list(hosp1 = c("A", "B", "C", "D", "E", "C", "A", "B"
), hosp2 = c("B", "C", "F", "C", "G", "A", "H", "A"), dist = c(12, 
23, 32, 12, 45, 12, 10, 13)), class = "data.frame", row.names = c(NA, 
-8L))

CodePudding user response：

Using RcppAlgos::permuteGeneral.

Either displaying empty permutations as NA,

RcppAlgos::permuteGeneral(test$iso1, 2, FUN=\(x) {
  d <- data[with(data, hosp1 == x[1] & hosp2 == x[2]), 'dist']
  data.frame(hosp=t(x), dist=ifelse(is.null(d), NA_real_, d))
}) |> do.call(what=rbind)
#   hosp.1 hosp.2 dist
# 1      A      B   12
# 2      A      C   NA
# 3      B      A   13
# 4      B      C   32
# 5      C      A   45
# 6      C      B   NA

or getting rid of them.

RcppAlgos::permuteGeneral(test$iso1, 2, FUN=\(x) {
  d <- data[with(data, hosp1 == x[1] & hosp2 == x[2]), 'dist']
  if (!length(d) == 0) data.frame(hosp=t(x), dist=d) else NULL
}) |> do.call(what=rbind)
#   hosp.1 hosp.2 dist
# 1      A      B   12
# 2      B      A   13
# 3      B      C   32
# 4      C      A   45

Data

data <- structure(list(hosp1 = c("A", "B", "C", "D", "E", "C", "A", "B"
), hosp2 = c("B", "C", "F", "C", "G", "A", "H", "A"), dist = c(12, 
32, 23, 12, 12, 45, 13, 13)), class = "data.frame", row.names = c(NA, 
-8L))

test <- structure(list(iso1 = c("A", "B", "C")), class = "data.frame", row.names = c(NA, 
-3L))