[ Joining two dataframes with sqldf results in NA Column ]
I am trying to join two data frames using the sqldf package.
These are reproducible examples of my two dataframes:
a <- c(1,2,3,4,5)
b <- c(1,2,3,4,5)
c <- c(1,2,3,4,5)
d <- c(1,2,3,4,5)
e <- c(1,2,3,4,5)
dataframe1 <- data.frame(a,b,c,d,e)
a <- c(NA,NA,NA,NA,5)
b <- c(NA,NA,NA,4,NA)
c <- c(NA,NA,3,NA,NA)
d <- c(NA,2,NA,NA,NA)
e <- c(1,NA,NA,NA,NA)
f <- c(1,2,3,4,5)
dataframe2 <- data.frame(a,b,c,d,e,f)
This is a reproducible example of the join on the dataframes:
final_data <- sqldf("SELECT *
FROM dataframe1
LEFT OUTER JOIN dataframe2 USING(a,b,c,d,e)")
The resulting f column in final_data which is introduced via the join is filled with NAs. Why? The ideal f column would have the corresponding values for a,b,c,d, and e that exist in dataframe2. How could I solve this?
Answer 1
If you want OR
criteria, the corresponding SQL is
SELECT *
FROM dataframe1 d1
LEFT OUTER JOIN dataframe2 d2
ON (
d1.a = d2.a
OR d1.b = d2.b
OR d1.c = d2.c
OR d1.d = d2.d
OR d1.e = d2.e
)
Since the column values from df1
aren't necessarily equal to those from df2
, you get a copy from each of them. If you only want to retain the values from df1
as well as f
from df2
, then do this:
SELECT d1.*, d2.f
FROM dataframe1 d1
LEFT OUTER JOIN dataframe2 d2
ON (
d1.a = d2.a
OR d1.b = d2.b
OR d1.c = d2.c
OR d1.d = d2.d
OR d1.e = d2.e
)
The R join functions I know about (merge
, plyr::join
and dplyr::left_join
) all require all of the columns in the join to be equal, though you could pretty easily hack together a custom function that joins one at a time and then coalesce
s the results.