1
$\begingroup$

I am trying to replicate the below sql query in R 

select a.*, b.key from Table1 a 
LEFT OUTER JOIN Table2 b 
on a.key = b.key where b.key is null

I have read through this post however I am still struggling to code my specific case. https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right

I have tried the below but the result doesnot allow me to filter for b.key IS NULL

LoansToInsert_stg1 <- merge(x = Prior_stg2, y = BankLoans_stg2, 
                            by = "Account_ID", all.x = TRUE)

Any insights?

Example: 

Key1 <- c("A1","A2","A3","A4","A5")  
Key2 <- c("A1","A2","A3","B4","B5")  
BV1 <- c(100, 200, 300, 400, 500)  
BV2 <- c(150, 250, 350, 450, 550)  
df1 <- as.data.frame(cbind(Key1, BV1))  
df2 <- as.data.frame(cbind(Key2, BV2))

Expected Output as a new df: 

Key1 BV1 Key2 BV2
A1 100 A1 150
A2 200 A2 250
A3 300 A3 350
A4 400 NA NA
A5 500 NA NA
Improve this question
$\endgroup$
3
  • $\begingroup$ Could you provide some dummy data and the expected result? $\endgroup$
    – Omri374
    Feb 23, 2016 at 19:28
  • $\begingroup$ @omri374 example added $\endgroup$
    – Drj
    Feb 23, 2016 at 21:39
  • $\begingroup$ see my updated answer $\endgroup$
    – Omri374
    Feb 23, 2016 at 21:44

1 Answer 1

Reset to default
2
$\begingroup$

If I understand correctly:

Table1 <- data.frame(key = seq(1,100),a.data = rnorm(100))
Table2 <- data.frame(key = c(seq(1,30),rep(NA,30)), b.data = seq(1,60))


##Assuming this is what you want
library(sqldf)
sql.ans <- sqldf("select a.*, b.key from Table1 a LEFT OUTER JOIN Table2 b on a.key = b.key where b.key is null")

## dplyr version
library(dplyr)

dplyr.ans <- Table1 %>% filter(!key %in% Table2$key)

## Regular R version
R.ans <- Table1[which(!Table1$key %in% Table2$key),]

EDIT after dummy data and expected output

dplyr.ans2 <- left_join(df1,df2, by = c("Key1" = "Key2"))
 Key1 BV1  BV2
 1   A1 100  150
 2   A2 200  250
 3   A3 300  350
 4   A4 400 <NA>
 5   A5 500 <NA>
Improve this answer
$\endgroup$
4
  • $\begingroup$ Yes, however I don't get b.key as part of my resultant. I understand my query has a where clause, however if I want to filter for other keys on Table2, I would not be able to. Basically the df should have key from table 1, key from table 2, other fields. If Key from table 2 (key2) does not exist in Key from table 1, that row could be NA for key2 $\endgroup$
    – Drj
    Feb 23, 2016 at 21:06
  • $\begingroup$ You don't need to add a WHERE clause, since using a LEFT JOIN instead of an INNER JOIN adds all the rows in df1 that don't have matching keys in df2. $\endgroup$
    – Omri374
    Feb 23, 2016 at 21:43
  • $\begingroup$ Correct! That would give me the all records in Table1 and matching records in Table2. Your edit brings me closer to what I am expecting. Is there a way to get the key from table2 in the resultant df? $\endgroup$
    – Drj
    Feb 23, 2016 at 21:46
  • $\begingroup$ on second thought, I can still work through with this result. Even though this is not ideally what would help me, having BV2 as NA can be used as a proxy for Key2 being NA. $\endgroup$
    – Drj
    Feb 23, 2016 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.