R Create a new column with all possible pairs of strings from a column by group ID in another column, then split a 3rd col. into 2 col. by those pairs

user_18507223 0
Viewed 58

Title might be a little confusing but I think I can explain it here pretty well.

So I've looked for solutions to this today, but there was nothing similar to mine that worked.

I need to create all possible pairs of strings in a column, based on a grouping variable (the "ID" column in my case), and then also take the values from another column ("X" column), and separate those values into 2 new columns based on the string pair that was created.

The "Tissue" column is made up of strings, everything else is numeric.

X being the value for the first string in the pair, X2 being the second

My data frame is like this

Tissue ID X
A 1 2
B 1 3
C 1 7
D 2 4
A 2 2
B 2 3
C 2 7

And I want it to end up like this

Tissue Pairs ID X X2
A B 1 2 3
A C 1 2 7
B C 1 3 7
A D 2 2 4
B D 2 3 4
C D 2 7 4

So there will be copies of the pairs throughout the "Tissue Pairs" column, but only because they belong to different IDs

structure(list(Tissue = c("Testis", "Prostate", "Colon - Transverse", 
"Whole Blood", "Kidney - Cortex", "Prostate", "Kidney - Cortex", 
"Colon - Transverse", "Testis", "Kidney - Cortex"), ID = c("1", 
"1", "1", "2", "2", "2", "3", "3", "3", "4"), X = c(-0.0423342169666184, 
-0.0159575980119219, 0.0653906499828914, 0.0509419093203423, 
-0.0164593204483257, 0.0128395479305178, -0.0214528156331502, 
0.139151567409083, -0.038963750292625, -0.0182265229147926)), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -10L), groups = structure(list(
    ID = c("1", "2", "3", "4"), .rows = structure(list(1:3, 4:6, 
        7:9, 10L), ptype = integer(0), class = c("vctrs_list_of", 
    "vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -4L), .drop = TRUE))

Here is a reproducible portion of the stuff I am working with, all identifiable information removed of course.

Thank you!

r
1 Answers

Here is one way using slice() and combn() to expand the data and from there it's just a matter of creating some grouping variables and concatenating Tissue and reshaping to wide format.

library(dplyr)
library(tidyr)

dat %>%
  group_by(ID) %>%
  slice(c(combn(seq(n()), min(n(), 2)))) %>%
  mutate(ID2 = (row_number()-1) %/% 2,
         lab = paste0("X", rep(1:2, length.out = n()))) %>%
  group_by(ID, ID2) %>%
  mutate(Tissue = paste(Tissue, collapse = " / ")) %>%
  pivot_wider(id_cols = -ID2, values_from = X, names_from = lab) %>%
  ungroup()

# A tibble: 10 × 4
   Tissue                               ID         X1      X2
   <chr>                                <chr>   <dbl>   <dbl>
 1 Testis / Prostate                    1     -0.0423 -0.0160
 2 Testis / Colon - Transverse          1     -0.0423  0.0654
 3 Prostate / Colon - Transverse        1     -0.0160  0.0654
 4 Whole Blood / Kidney - Cortex        2      0.0509 -0.0165
 5 Whole Blood / Prostate               2      0.0509  0.0128
 6 Kidney - Cortex / Prostate           2     -0.0165  0.0128
 7 Kidney - Cortex / Colon - Transverse 3     -0.0215  0.139 
 8 Kidney - Cortex / Testis             3     -0.0215 -0.0390
 9 Colon - Transverse / Testis          3      0.139  -0.0390
10 Kidney - Cortex                      4     -0.0182 NA

Good first question btw, explained well and sample data and expected output provided.

user_28352610
Related