Merging two dataframes to paste an id Variable in r

Question

I have an id dataset and a dimension dataset. I need to merge these two to create a new set of ids.

Here my sample dataset looks like:

ids.data <- data.frame(id.1=c(1,2),
                       id.2=c(11,12))
> ids.data
  id.1 id.2
1    1   11
2    2   12
dims <- data.frame(dim=c("C","E","D"))

> dims
  dim
1   C
2   E
3   D

I need to combine these two in a way that each id will have three ids below:

> ids.data.2
  id.1 id.2
1  1_C 11_C
2  1_E 11_E
3  1_D 11_D
4  2_C 12_C
5  2_E 12_E
6  2_D 12_D

Any ideas? Thanks!

Answer 1

Using outer.

f <- Vectorize(\(j, i) paste(ids.data[i, ], dims[j,,drop=F], sep='_'), SIMPLIFY=F)
do.call(c, outer(seq_len(nrow(dims)), seq_len(nrow(ids.data)), f)) |>
  matrix(nrow=6, byrow=TRUE) |> 
  as.data.frame()  ## optional
#    V1   V2
# 1 1_C 11_C
# 2 1_E 11_E
# 3 1_D 11_D
# 4 2_C 12_C
# 5 2_E 12_E
# 6 2_D 12_D

Note: R >= 4.1 used.

---

Data:

ids.data <- structure(list(id.1 = c(1, 2), id.2 = c(11, 12)), class = "data.frame", row.names = c(NA, 
-2L))

dims <- structure(list(dim = c("C", "E", "D")), class = "data.frame", row.names = c(NA, 
-3L))

Answer 2

Another possible solution, based on purrr::map_dfc and tidyr::expand_grid:

library(tidyverse)

map_dfc(ids.data, ~ expand_grid(.x, dims$dim) %>% apply(1, str_c, collapse="_"))

#> # A tibble: 6 × 2
#>   id.1  id.2 
#>   <chr> <chr>
#> 1 1_C   11_C 
#> 2 1_E   11_E 
#> 3 1_D   11_D 
#> 4 2_C   12_C 
#> 5 2_E   12_E 
#> 6 2_D   12_D