1

I have the following dataframes

df1 <- tibble::as.tibble(list(a = c(1,2,3), d = c(10,11,12) ,id = c("a","b","c")))
df2 <- tibble::as.tibble(list(a = c(4,5,6), e = c(13,14,15) ,id = c("a","b","c")))
df3 <- tibble::as.tibble(list(a = c(7,8,9), f = c(16,17,18) ,id = c("a","b","c")))

I want to merge together these dataframes. Since the column name a occours in all of them I will have use the suffix argument while merging.

The desired result I am looking for is

| id | a.df1 | d  | a.df2 | e  | a.df3 | f  |
|----|-------|----|-------|----|-------|----|
| a  | 1     | 10 | 4     | 13 | 7     | 16 |
| b  | 2     | 11 | 5     | 14 | 8     | 17 |
| c  | 3     | 12 | 6     | 15 | 9     | 18 |

Below is the code I tried

test_list <- list(df1, df2, df3)
names(test_list) <- c("df1", "df2", "df3")
seq_along(temp) %>% 
      purrr::reduce(
      ~merge(
      temp[[.x]], 
      temp[[.y]], 
      suffix = c(names(test_list[.x]), names(test_list[.y])))

However this results in an error stating Error in temp[[.x]] : invalid subscript type 'list. Why am I not able to subset to a dataframe in the merge function

Also is there a better way to combine a list of multiple dataframes with same column names.

Improve this question
0

3 Answers 3

Reset to default
3 
library(tidyverse)

df1 <- tibble::as.tibble(list(a = c(1,2,3), d = c(10,11,12) ,id = c("a","b","c")))
df2 <- tibble::as.tibble(list(a = c(4,5,6), e = c(13,14,15) ,id = c("a","b","c")))
df3 <- tibble::as.tibble(list(a = c(7,8,9), f = c(16,17,18) ,id = c("a","b","c")))

# create your list and the names
test_list <- list(df1, df2, df3)
names(test_list) <- c("df1", "df2", "df3")

# spot overlapping columns
test_list %>%
  map_df(names) %>%
  gather() %>%
  count(value) %>%
  filter(n > 1 & value != "id") %>%
  pull(value) -> overlaps

map2(test_list, names(test_list), ~{names(.x)[names(.x) %in% overlaps] = paste0(names(.x)[names(.x) %in% overlaps],".",.y); .x}) %>% 
  reduce(function(x,y) left_join(x,y, by="id")) %>%
  select(id, everything())

# # A tibble: 3 x 7
#   id    a.df1     d a.df2     e a.df3     f
#   <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
# 1 a         1    10     4    13     7    16
# 2 b         2    11     5    14     8    17
# 3 c         3    12     6    15     9    18

Given a list and a its names we use map2 to update each element's name in position 1 (i.e. column a).

Then we use reduce to join dataframes sequentially and we use select to arrange columns.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the reply. But the concern is that I have more columns which are not common between the dataframes and ideally I would not want to add a suffix i.e. df* to all the columns and only do it for overlapping columns.
1

How does this look? 

t <- merge(df1,df2, by = "id" )
df <- merge(t,df3, by = "id" )
names(df) <- c("id", "a.df1", "a.df2", "a.df3")

or am i right in guessing you actually have a lot more columns, and don't want to have to go through merging all like this?

Improve this answer

1 Comment

Thanks for your answer. Yes, I have many more columns and this was just a representative example showing an overlapping column. I will not be able to hard code the names unfortunately.
0

The function eat of my package safejoin has such feature, if you give it a list of data.frames as a second input it will join them recursively to the first input. We can rename all "a" columns and use it.

# devtools::install_github("moodymudskipper/safejoin")
library(safejoin)
dfs <- imap(lst(df1,df2,df3), ~rename_at(.x, "a",paste, .y, sep="."), .y) %>%
  unname()
eat(dfs[[1]], dfs[-1], .by = "id")
# # A tibble: 3 x 7
#   id    a.df1     d a.df2     e a.df3     f
#   <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
# 1 a         1    10     4    13     7    16
# 2 b         2    11     5    14     8    17
# 3 c         3    12     6    15     9    18
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.