CODEWITHSUNDEEP
rrowsexpand
T Smith
T Smith

Reputation: 51

Create rows in R, based on pair combinations of existing rows

I have

col_a col_c
1 A 01001
2 B 01002
3 C 01003
4 D 01004
5 E 01005
6 F 01006

I want

col_a col_b col_c
1 A A 01001
2 A B 01002
3 A C 01003
4 A D 01004
5 A E 01005
6 A F 01006
7 B A 01001
8 B B 01002
9 B C 01003
10 B D 01004
11 B E 01005
.. .. ..
24 F E 01005
25 F F 01006

I feel like this is simple but its 4:30 am and my brain is no longer working please help.

I have tried

#Set up a fake key to join on (just a constant)
df <- df %>% mutate(k = 1) 

 #Perform the join, remove the key
main_df %>% 
  full_join(df, by = "k") %>%
 select(-k)

I've also tried

df <-
merge(
  x = df,
  y = df[, c("col_a",
             "col_c")],
     by.x = "a",
     by.y = "a",
     all = TRUE
   )

  df <-
   merge(
     x = df[, c("col_a",
                     "col_c")],
     y = main_df,
     by.x = "col_a",
     by.y = "col_a",
     all.x = TRUE
   )

I have tried MANY combinations of the above solutions, not just the ones listed here. No avail.

Upvotes: 0

Views: 24

Answers (1)

ThomasIsCoding
ThomasIsCoding

Reputation: 102469

Try the code below

out <- merge(data.frame(col_b = df$col_a), df, all = TRUE)[c("col_a", "col_b", "col_c")]

or

out <- with(
  df,
  cbind(
    setNames(rev(expand.grid(col_a, col_a)), c("col_a","col_b")),
    col_c = rep(col_c, by = nrow(df))
  )
)

and you will see

> out
   col_a col_b col_c
1      A     A  1001
2      A     B  1002
3      A     C  1003
4      A     D  1004
5      A     E  1005
6      A     F  1006
7      B     A  1001
8      B     B  1002
9      B     C  1003
10     B     D  1004
11     B     E  1005
12     B     F  1006
13     C     A  1001
14     C     B  1002
15     C     C  1003
16     C     D  1004
17     C     E  1005
18     C     F  1006
19     D     A  1001
20     D     B  1002
21     D     C  1003
22     D     D  1004
23     D     E  1005
24     D     F  1006
25     E     A  1001
26     E     B  1002
27     E     C  1003
28     E     D  1004
29     E     E  1005
30     E     F  1006
31     F     A  1001
32     F     B  1002
33     F     C  1003
34     F     D  1004
35     F     E  1005
36     F     F  1006

Upvotes: 1

Related Questions