ranti-join

How to add values from other column if conditional join does not execute?

I have two tables this one is old names

Last Name|First Name|ID
Clay      Cassius    1
Alcindor  Lou        2
Artest    Ron        3
Jordan    Michael    4
Scottie   Pippen     5
Kanter    Enes       6

New Names

Last Name|   First Name|   ID
Ali          Muhammad       1
Abdul Jabbar Kareem         2
World Peace  Metta          3
Jordan       Michael        4
Pippen       Scottie        5
Freedom      Enes Kanter    6

Basically I want to do a join to the first table (old names) where it will show the new last name if there has been a name change otherwise blank

Last Name|First Name|ID|Discrepancies
Clay      Cassius    1  Ali
Alcindor  Lou        2  Abdul Jabbar
Artest    Ron        3  World Peace
Jordan    Michael    4  
Pippen   Scottie     5  
Kanter    Enes       6  Freedom

Note that Michael and Scottie's name did not change so in Discrepancies there is a blank.

Solution

  • You could use

    library(dplyr)

df1 %>% 
  left_join(df2, by = "ID", suffix = c("", ".y")) %>% 
  mutate(Discrepancies = ifelse(Last_Name.y == Last_Name, "", Last_Name.y)) %>% 
  select(-ends_with(".y"))

    to get

    # A tibble: 6 x 4
  Last_Name First_Name    ID Discrepancies 
  <chr>     <chr>      <dbl> <chr>         
1 Clay      Cassius        1 "Ali"         
2 Alcindor  Lou            2 "Abdul Jabbar"
3 Artest    Ron            3 "World Peace" 
4 Jordan    Michael        4 ""            
5 Scottie   Pippen         5 "Pippen"      
6 Kanter    Enes           6 "Freedom"

    Note: