Merge 3 dataframes which are different to each other

Question

I want to merge 3 data frames which are different. I have one with few rows and another one with different Key.see the data frames below

df1 <- data.frame(date = c("12/03/2013","12/03/2013","12/03/2013"), f1 = c(189,256,389), f2 = c("NY","MB","LT"))
df2 <- data.frame(date = c("12/03/2013","12/03/2013"), Added_f1 = c(178,49), added_f2 = c("okr","nor"))
df3 <- data.frame(date = c("07/09/2016","07/09/2016","07/09/2016"), f1 = c(190,200,367), f5 = c("so","yo","kl"),f7 = c("sott","yogh","klop"))

I do want to merge them using the date as the key. I have tried these options but it generates an error of mismatch, **must specify a uniquely valid* column*

Reduce(function(x,y) merge(x,y,by="date",all=TRUE) ,list(df1,df2,df3))

Note that column names can be different in each data frame. The second data frame can be merged on the right since columns are totally different but how can a third data frame be merged since the key is different and columns can have those matching and others which don't. I do want one table as a result.

Can you edit your post to show expected output for given example ? — Ronak Shah
df3 has the column nemed dates, not date. That is what the error message tells you: you don't have the column to merge — denis

MrGumble MrGumble · Accepted Answer · 2019-08-16T10:29:53

If you strictly only need to merge 3 data.frames, do it by hand:

merge(
  merge(df1,df2,by="date",all=TRUE),
  df3, by.x='date', by.y='dates', all=TRUE)

With Reduce, there isn't room for append additional arguments to each set. However, with a bit of repacking, we can do the following:

Reduce(function(x,y) {
    res <- merge(x$d, y$d, by.x=x$key, by.y=y$key, all=TRUE)
    list(key=x$key, d=res)
  }, 
  list(list(key='date', d=df1), list(key='date', d=df2), list(key='dates', d=df3))
)

Merge 3 dataframes which are different to each other

1 Answers