CODEWITHSUNDEEP
rpurrr
pd321
pd321

Reputation: 649

How to merge list of dataframes with same column names?

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.

Upvotes: 1

Views: 2825

Answers (3)

moodymudskipper
moodymudskipper

Reputation: 47350

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

Upvotes: 0

AntoniosK
AntoniosK

Reputation: 16121

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.

Upvotes: 3

Chris Littler
Chris Littler

Reputation: 191

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?

Upvotes: 1

Related Questions