merge list of data frames to single data frame by all rows

Question

I have a list of data frames which looks like this:

df1

col1   col2
house.  10
cat.    5
dog     7
mouse   4

df2

    col1   col2
    house.   6
    apple.   4
    dog      8
    elephant 3

df3

    col1   col2
    horse    1
    banana   1
    dog      8

The desired output would be:

          df1.  df2.  df3
house.     10     6.    NA
cat        5      NA.   NA
dog        7      8     8
mouse.     4.     NA.   NA
apple.     NA     4.    NA
elephant.  NA     3.    NA
horse.     NA.    NA.   1
banana.    NA.    NA.   1

Any suggestion?

I tried to do the following:

list_df<-list(df1,df2,df3)
df_all<-do.call("rbind", list_df)
df_merge<-as.data.frame(unique(df_all$col1))
colnames(df_merge)<-"category"

df_merge$df1 <- with(df_merge, ifelse (category %in% df1$col1,df1$col2,NA))

however, when I add the second data frame I get this error: $ operator is invalid for atomic vectors

yes, that is what I need, thanks a lot – Paolo Lorenzini Jan 17 '20 at 16:30 — Paolo Lorenzini, Jan 17 '20 at 16:30

sm925 · Accepted Answer · 2020-01-17T16:25:12.937

Using dplyr:

library(dplyr)
df <- dplyr::full_join(df1, df2, by = "col1")
df <- dplyr::full_join(df, df3, by = "col1")

df %>% 
  column_to_rownames(var = "col1")

#          col2.x col2.y col2
#house.       10      6   NA
#cat.          5     NA   NA
#dog           7      8    8
#mouse         4     NA   NA
#apple.       NA      4   NA
#elephant     NA      3   NA
#horse        NA     NA    1
#banana       NA     NA    1

update: if you have many data frames. You can use `reduce` from `purrr`:

library(tidyverse)
list(df1, df2, df3) %>% reduce(full_join, by = "col1") ## this would help

data

df1 <- structure(list(col1 = structure(c(3L, 1L, 2L, 4L), .Label = c("cat.", "dog", "house.", "mouse"), class = "factor"), col2 = c(10L, 5L, 7L, 4L)), class = "data.frame", row.names = c(NA, -4L))
df2 <- structure(list(col1 = structure(c(4L, 1L, 2L, 3L), .Label = c("apple.", "dog", "elephant", "house."), class = "factor"), col2 = c(6L, 4L, 8L, 3L)), class = "data.frame", row.names = c(NA, -4L))
df3 <- structure(list(col1 = structure(c(3L, 1L, 2L), .Label = c("banana", "dog", "horse"), class = "factor"), col2 = c(1L, 1L, 8L)), class = "data.frame", row.names = c(NA, -3L))

but what if I have hundreds of data frames in a list? it is troublesome to type 100 times the names of the data frames — Paolo Lorenzini, Jan 17 '20 at 16:10

merge list of data frames to single data frame by all rows

1 Answers1

update: if you have many data frames. You can use reduce from purrr:

data

update: if you have many data frames. You can use `reduce` from `purrr`: