Merging 3 dataframes Left join

user_6445985 0
Viewed 442

I have 3 dataframes with unequal rows 

df1-
T1      T2     T3
1       Joe    TTT
2       PP     YYY
3       JJ     QQQ
5       UU     OOO
6       OO     GGG

df2
X1      X2 
1       09/20/2017
2       08/02/2015
3       05/02/2000
8       06/03/1999

df3
L1       L2
1        New
6        Notsure
9        Also

The final dataframe should be like a left join of all 3 only retaining rows of df1. The matching rows are T1, X1 and L1 but with different header names. The number of rows are different in each dataframe. I couldn't find a solution for this situation. On SO, what i found was available for 2 dataframes or 3 dataframes with equal rows or same column name

    T1      T2     T3         X2            L2 
    1       Joe    TTT        09/20/2017    New
    2       PP     YYY        08/02/2015    NA
    3       JJ     QQQ        05/02/2000    NA
    5       UU     OOO        NA            NA
    6       OO     GGG        NA            NotSure

I am comparatively new in R, and couldn't find a R code for this

r
4 Answers

The idea is to put your data frames in a list, change the name of the first column, and use Reduce to merge, i.e.

Reduce(function(...) merge(..., by = 'Var1', all.x = TRUE), 
    lapply( mget(ls(pattern = 'df[0-9]+')), function(i) {names(i)[1] <- 'Var1'; i}))

which gives,

  Var1  T2  T3         X2      L2
1    1 Joe TTT 09/20/2017     New
2    2  PP YYY 08/02/2015     Old
3    3  JJ QQQ 05/02/2000    <NA>
4    5  UU OOO       <NA>    <NA>
5    6  OO GGG       <NA> Notsure
user_56355800

using tidyverse functions, you can try:

df1 %>%
  left_join(df2, by = c("T1" = "X1")) %>%
  left_join(df3, by = c("T1" = "L1"))

which gives:

  T1  T2  T3         X2      L2
1  1 Joe TTT 09/20/2017     New
2  2  PP YYY 08/02/2015    <NA>
3  3  JJ QQQ 05/02/2000    <NA>
4  5  UU OOO       <NA>    <NA>
5  6  OO GGG       <NA> Notsure
user_10322920

1) sqldf 

library(sqldf)
sqldf("select df1.*, X2, L2 
       from df1 
       left join df2 on T1 = X1 
       left join df3 on T1 = L1")

1a) Although slightly longer this variation can make it easier later when reviewing the code by making it explicit as to which source each column came from. If the data frame names were long you might want to use aliases, e.g. from df1 as a, but here we don't bother since they are short.

sqldf("select df1.*, df2.X2, df3.L2 
       from df1 
       left join df2 on df1.T1 = df2.X1 
       left join df3 on df1.T1 = df3.L1")

2) merge Using repeated merge. No packages used.

Merge <- function(x, y) merge(x, y, by = 1, all.x = TRUE)
Merge(Merge(df1, df2), df3)

2a) This could also be written using a magrittr pipeline like this:

library(magrittr)
df1 %>% Merge(df2) %>% Merge(df3)

2b) Using Reduce we can do the repeated merges like this:

Reduce(Merge, list(df1, df2, df3))

Note: The inputs in reproducible form are:

Lines1 <- "
T1      T2     T3
1       Joe    TTT
2       PP     YYY
3       JJ     QQQ
5       UU     OOO
6       OO     GGG"

Lines2 <- "
X1      X2 
1       09/20/2017
2       08/02/2015
3       05/02/2000
8       06/03/1999"

Lines3 <- "
L1       L2
1        New
6        Notsure
9        Also"

df1 <- read.table(text = Lines1, header = TRUE)
df2 <- read.table(text = Lines2, header = TRUE)
df3 <- read.table(text = Lines3, header = TRUE)
user_5165480

With left_join() it would be something like this

  df1 = data.frame(X = c("a", "b", "c"), var1 = c(1,2, 3))

  df2 = data.frame(V = c("a", "b", "c"), var2 =c(5,NA, NA) )

  df3 = data.frame(Y = c("a", "b", "c"), var3 =c("name", NA, "age") )

# rename   
df2 = df2 %>% rename(X = V)
df3 = df3 %>% rename(X = Y)

df = left_join(df1, df2, by = "X") %>% 
    left_join(., df3, by = "X")

> df
  X var1 var2 var3
1 a    1    5 name
2 b    2   NA <NA>
3 c    3   NA  age
user_58231260
Related