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.
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