Use dataframe name as suffix when joining several data frames

user_4486614 0
Viewed 449

I need to merge several data frames with duplicated column names. I am using dplyr::left_join, with purrr::reduce to iterate the function over the files. The standard behavior of dplyr::left_join is to add a suffix to duplicated col names, but that doesn't help to figure out which files the variable comes from unless you have the code handy. Below is a simplified example of my situation:

a <- tibble(id = 1:5, var=letters[1:5])
b <- tibble(var=letters[11:15], id=1:5)
c <- tibble(id = 1:5, var=letters[16:20], var2="a")
reduce(list(a,b,c), left_join, by="id")

# A tibble: 5 x 5
     id var.x var.y var   var2 
  <int> <chr> <chr> <chr> <chr>
1     1 a     k     p     a    
2     2 b     l     q     a    
3     3 c     m     r     a    
4     4 d     n     s     a    
5     5 e     o     t     a

Is there a way to use the dataframe name as suffix, and to force the suffix to all the duplicated columns? What I'd like to have is:

# A tibble: 5 x 5
     id var.a var.b var.c   var2 
  <int> <chr> <chr> <chr>  <chr>
1     1 a     k     p      a    
2     2 b     l     q      a    
3     3 c     m     r      a    
4     4 d     n     s      a    
5     5 e     o     t      a

There a suffix option in left_join, but how can I use it within reduce?

dplyr r join
1 Answers

the suffix in left_join cannot work since it does not know which columns are unique or not unique across all input data frames when acting on only two at a time.

1) Instead suffix all non-by columns in all data frames prior to the joins. The by = "id" in the left_join line could be omitted since it will default to that.

At the end we remove the suffix on columns which uniquely appear without the suffix; however, that may not really be needed in which case omit the rmSuffix (which removes any suffix) and uniq_stem (which returns only those components of its vector argument whose stem is unique) and the rename_with line at the end of the pipeline.

This approach would likely be ok if you don't need the final rename_with and associated functions it uses since then it is quite simple; otherwise, probably best to go with (2) to avoid certain edge cases that remain in (1) and the ugliness of setting the column names prior to the left_join only to have to fix them up again after the left_join.

library(dplyr)
library(purrr)
library(tibble)

rmSuffix <- function(x) sub("\\.[^.]*", "", x)
uniq_stem <- function(x) x[ave(x, rmSuffix(x), FUN = length) == 1]

lst(a, b, c) %>%
  map2(names(.), ~ rename_with(.x, function(x) paste(x, .y, sep = "."), -id)) %>%
  reduce(left_join, by = "id") %>%
  rename_with(rmSuffix, .cols = uniq_stem(names(.)))

giving:

# A tibble: 5 x 5
     id var.a var.b var.c var2 
  <int> <chr> <chr> <chr> <chr>
1     1 a     k     p     a    
2     2 b     l     q     a    
3     3 c     m     r     a    
4     4 d     n     s     a    
5     5 e     o     t     a

2) A second approach is to entirely process the list prior to the left_join's. This eliminates all regular expressions and problems associated with fixing up the initial renames in (1). add_suffix has data.frame and list methods. In the latter case the list must be a named list of data frames and exclude is a vector of column names for which a suffix is not to be added in addition to uniquely occurring column names in the data frames of the list.

add_suffix <- function(x, ...) UseMethod("add_suffix")

add_suffix.data.frame <- function(x, suffix, exclude = NULL, ...) {
  ok <- !names(x) %in% exclude
  names(x)[ok] <- paste(names(x)[ok], suffix, sep = ".")
  x
}

add_suffix.list <- function(x, exclude = NULL, ...) {
  nms <- unlist(lapply(x, names))
  exclude2 <- unname(c(nms[ave(nms, nms, FUN = length) == 1], exclude))
  Map(add_suffix, x, names(x), MoreArgs = list(exclude = exclude2))
}


lst(a, b, c) %>%
  add_suffix(exclude = "id") %>%
  reduce(left_join, by = "id")
user_5165480
Related