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