One-One join in R

Question

There are two files and the data looks like this:
file1

Col1   Col2  
**A**      10  
**B**      20   
**C**      30   
**D**      40  

file2:

Col1   col2   
**A**   XYZ  
**B**   PQR  
**B**   QES  
**C**   UIY  
**D**   UUI  
**D**   HUI  
**E**   BIG   

I want to join these two files on col1 1 ,but I want only those entries where we have one to one mapping only. The output shoul look like this:
file A (where we have match)
A 10 XYZ
C 30 UIY

file B (Where we don't found a one to one map)

col1 col2
B 20
D 40

AS B and D have multiple entries in file 2, so I don't want these records in my join result.

Answer 1

We can use a inner_join after subsetting the second dataset

library(dplyr)
df2 %>% 
   group_by(Col1) %>% 
   filter(n()==1) %>% 
   inner_join(., df1, by = "Col1")
#     Col1  col2  Col2
#   <chr> <chr> <int>
#1     A   XYZ    10
#2     C   UIY    30

Or to avoid duplicate elements, as @zx8754 suggested

res <- df2 %>%
        inner_join(., df1, by = "Col1") %>%
        group_by(Col1) %>%
        filter(n()==1)

To get the rows that are not in the 'res', do a anti_join

res %>%
    select(Col1, Col2) %>% 
    anti_join(df1, .)
#  Col1 Col2
#1    B   20
#2    D   40

---

Or using data.table

library(data.table)
na.omit(setDT(df2)[df1,  Col2 := Col2[.N==1],on = "Col1", by = .EACHI])
#   Col1 col2 Col2
#1:    A  XYZ   10
#2:    C  UIY   30