Merging two dataframes to paste an id Variable in r

user_5933306 0
Viewed 64

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!

r merge
3 Answers

We may use crossing on the two datasets and then paste

library(dplyr)
library(tidyr)
library(stringr)
crossing(ids.data, dims) %>% 
  transmute(across(starts_with('id'), ~ str_c(.x, dim, sep = '_')))

-output

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

Or using base R - loop over the columns of 'ids.data', paste by expanding with 'dim' column from dims using outer and convert the list to data.frame

data.frame(lapply(ids.data, \(x) c(t(outer(x, dims$dim, 
   FUN = paste, sep = "_")))))
  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
user_37322710

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))
user_65740380

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
user_115644870
Related