Simon Jackson Simon Jackson - 2 months ago 12
R Question

Nesting duplicate variables when joining with dplyr in R

I'm joining data frames (tibbles) that have duplicated columns that I do not want to join. Example below is what I would usually do (joining by

i
, but not
a
or
b
):

library(dplyr)

df1 <- tibble(i = letters[1:3], a = 1:3, b = 4:6)
df2 <- tibble(i = letters[1:3], a = 11:13, b = 14:16)

d <- full_join(df1, df2, by ="i")
d
#> # A tibble: 3 × 5
#> i a.x b.x a.y b.y
#> <chr> <int> <int> <int> <int>
#> 1 a 1 4 11 14
#> 2 b 2 5 12 15
#> 3 c 3 6 13 16


I want these duplicated variables to be returned as nested lists such as the output created below:

tibble(
i = letters[1:3],
a = list(c(1, 11), c(2, 12), c(3, 13)),
b = list(c(4, 14), c(5, 15), c(6, 16))
)
#> # A tibble: 3 × 3
#> i a b
#> <chr> <list> <list>
#> 1 a <dbl [2]> <dbl [2]>
#> 2 b <dbl [2]> <dbl [2]>
#> 3 c <dbl [2]> <dbl [2]>


Is there a simple way to do such a thing?

Aside, I've been playing around (unsuccessfully) with various stringr and tidyr methods. Here's an example that throws an error:

library(stringr)
library(tidyr)

# Find any variables with .x or .y
dup_var <- d %>% select(matches("\\.[xy]")) %>% names()

# Condense to the stems (original names) of these variables
dup_var_stems <- dup_var %>% str_replace("(\\.[x|y])+", "") %>% unique()

# For each stem, try to nest relevant data into a single variable
for (stem in dup_var_stems) {
d <- d %>% nest_(key_col = stem, nest_cols = names(d)[str_detect(names(d), paste0(stem, "[$|\\.]"))])
}


UPDATE

After answers from @Sotos and @conor, I'll mention that the solution needs to generalise to multiple joining and duplicated columns over many data frames. Below is an example where joining is done on five data frames by two columns (
i
and
j
). This creates five duplicated versions of columns
a
and
b
, with plenty of unique columns too
c
:
g
. One problem is that duplicating over so many data frames results in duplicated versions having no suffix,
.x
,
.x.x
, and so on. So simple regex match for
.x|.y
will miss the no-suffix version of the column.

library(dplyr)
library(purrr)


id_cols <- tibble(i = c("x", "x", "y", "y"),
j = c(1, 2, 1, 2))

df1 <- id_cols %>% cbind(tibble(a = 1:4, b = 5:8, c = 21:24))
df2 <- id_cols %>% cbind(tibble(a = 2:5, b = 6:9, d = 31:34))
df3 <- id_cols %>% cbind(tibble(a = 2:5, b = 6:9, e = 31:34))
df4 <- id_cols %>% cbind(tibble(a = 2:5, b = 6:9, f = 31:34))
df5 <- id_cols %>% cbind(tibble(a = 2:5, b = 6:9, g = 31:34))
datalist <- list(df1, df2, df3, df4, df5)

d <- reduce(datalist, full_join, by = c("i", "j"))
d
#> i j a.x b.x c a.y b.y d a.x.x b.x.x e a.y.y b.y.y f a b g
#> 1 x 1 1 5 21 2 6 31 2 6 31 2 6 31 2 6 31
#> 2 x 2 2 6 22 3 7 32 3 7 32 3 7 32 3 7 32
#> 3 y 1 3 7 23 4 8 33 4 8 33 4 8 33 4 8 33
#> 4 y 2 4 8 24 5 9 34 5 9 34 5 9 34 5 9 34

Answer

Here is one attempt,

library(dplyr)
library(tidyr)

melt(d, id.vars = 'i') %>% 
   group_by(a = sub('\\..*', '', variable), i) %>% 
   summarise(new = list(value)) %>% 
   spread(a, new)

# A tibble: 3 × 3
#      i         a         b
#* <chr>    <list>    <list>
#1     a <int [2]> <int [2]>
#2     b <int [2]> <int [2]>
#3     c <int [2]> <int [2]>

#With structure
Classes ‘tbl_df’, ‘tbl’ and 'data.frame':   3 obs. of  3 variables:
 $ i: chr  "a" "b" "c"
 $ a:List of 3
  ..$ : int  1 11
  ..$ : int  2 12
  ..$ : int  3 13
 $ b:List of 3
  ..$ : int  4 14
  ..$ : int  5 15
  ..$ : int  6 16

#Or via reshape2 package

library(dplyr)
library(reshape2)

d1 <- melt(d, id.vars = 'i') %>% 
         group_by(a = sub('\\..*', '', variable), i) %>% 
         summarise(new = list(value))

d2 <- dcast(d1, i ~ a, value.var = 'new')
#d2
#  i     a     b
#1 a 1, 11 4, 14
#2 b 2, 12 5, 15
#3 c 3, 13 6, 16

#with structure:
str(d2)
'data.frame':   3 obs. of  3 variables:
 $ i: chr  "a" "b" "c"
 $ a:List of 3
  ..$ : int  1 11
  ..$ : int  2 12
  ..$ : int  3 13
 $ b:List of 3
  ..$ : int  4 14
  ..$ : int  5 15
  ..$ : int  6 16

EDIT

To follow your thought,

df <- melt(d, id.vars = c(names(d)[!grepl('a|b', names(d))]))

dots <- names(df)[!grepl('value', names(df))] %>% map(as.symbol)

df %>% mutate(variable = sub('\\..*', '', variable)) %>%
     group_by_(.dots = dots) %>%
     summarise(new = list(value)) %>%
     spread(variable, new) %>%
     ungroup()
# A tibble: 4 × 9
#      i     j     c     d     e     f     g         a         b
#* <chr> <dbl> <int> <int> <int> <int> <int>    <list>    <list>
#1     x     1    21    31    31    31    31 <int [5]> <int [5]>
#2     x     2    22    32    32    32    32 <int [5]> <int [5]>
#3     y     1    23    33    33    33    33 <int [5]> <int [5]>
#4     y     2    24    34    34    34    34 <int [5]> <int [5]>