Is there a way to build a pairwise data frame based on shared values in another data frame in R?

user_13985903 0
Viewed 88

For example DF1 is:

Id1 Id2 
1     10
2     10
3     7
4     7
5     10

And want DF2:

Id1 Id2
1     2        
1     5
2     5
3     4

The data frame DF2 is a pairwise set of values from Id1 column in DF1 that shared a common value in Id2 of DF1.

My attempt:

temp <- do.call("cbind", split(DF1, rep(c(1,2), length.out = nrow(DF1))))
(DF2 <- temp %>% select("1.Id1", "2.Id2"))

But this does not generate a pairwise data frame:

Id1 Id2
1     2
3     4
data-wrangling r
4 Answers

You can split Id1 based on values in Id2 and create all possible combinations with combn and bind the results.

do.call(rbind, lapply(split(df$Id1, df$Id2), function(x) t(combn(x, 2))))

#     [,1] [,2]
#[1,]    3    4
#[2,]    1    2
#[3,]    1    5
#[4,]    2    5

We can also use by which is shorter :

do.call(rbind, by(df$Id1, df$Id2, function(x) t(combn(x, 2))))
user_39629140

We can use tidyverse methods where we group by 'Id2', get the combn of 'Id1', unnest to wide format and rename the columns

library(dplyr)
library(tidyr)
library(stringr)
DF1 %>%
    # // group by Id2
    group_by(Id2) %>%
    # // get the combinations in summarise
    summarise(out = combn(Id1, 2, simplify = FALSE)) %>% 
    ungroup %>%
    # // unnest to wide format
    unnest_wider(c(out)) %>% 
    select(-Id2) %>%
    rename_all(~ str_c("V", seq_along(.)))
# A tibble: 4 x 2
#     V1    V2
#  <int> <int>
#1     3     4
#2     1     2
#3     1     5
#4     2     5

data

DF1 <- structure(list(Id1 = 1:5, Id2 = c(10L, 10L, 7L, 7L, 10L)),
class = "data.frame", row.names = c(NA, 
-5L))
user_37322710

Here is another tidyverse method using full_join.

library(dplyr)
library(purrr)

dat2 <- dat %>%
  full_join(dat, by = "Id2") %>%
  filter(Id1.x != Id1.y) %>%
  mutate(Id_sort = map2_chr(Id1.x, Id1.y, ~paste(sort(c(.x, .y)), collapse = ", "))) %>%
  distinct(Id_sort, .keep_all = TRUE) %>%
  select(Id1 = Id1.x, Id2 = Id1.y)
dat2
#   Id1 Id2
# 1   1   2
# 2   1   5
# 3   2   5
# 4   3   4

Data

dat <- read.table(text = "Id1 Id2 
1     10
2     10
3     7
4     7
5     10",
                  header = TRUE)
user_76698090

It could be conceptualised as a network/graph issue too:

df1 <- data.frame(Id1 = 1:5, Id2 = c(10L, 10L, 7L, 7L, 10L))

library(igraph)
g <- graph.data.frame(df1)
g <- connect(g, 2)
g <- induced_subgraph(g, V(g) %in% df1$Id1)
as_edgelist(g)
#     [,1] [,2]
#[1,] "1"  "2" 
#[2,] "1"  "5" 
#[3,] "2"  "5" 
#[4,] "3"  "4"
user_4968030
Related