[FIXED] Why I cannot merge the two files, with left_join, in R?

Issue

I am trying to link two files with left join, in R. I do not get the output I need.

Here is an example of two files:

here is file_1:

And when I do a left_join in R, I do not get what I want. Here is the code:

minap_piv_na_stemi_nstemi <- left_join(file_1, file_2)


                   

As you can see in the last line, Rochdale Infirmary should be populated with the provider_name and trust_code. This is not happening? Can someone help ?

Solution

Try this. Use left_join to join by hospital_name. Use e.g. coalesce to fill in the missing information for trust_code and provider_name. Get rid of the .x and .y columns:

library(dplyr)

left_join(file_1, file_2, by = "hospital_name") %>% 
  mutate(trust_code = coalesce(trust_code.x, trust_code.y),
         provider_name = coalesce(provider_name.x, provider_name.y)) %>% 
  select(-ends_with(".x"), -ends_with(".y"))
#> # A tibble: 182 x 3
#>    hospital_name           trust_code provider_name                             
#>    <chr>                   <chr>      <chr>                                     
#>  1 Addenbrooke's Hospital  RGT        Cambridge University Hospitals NHS Founda…
#>  2 Royal Albert Edward In… RRF        Wrightington, Wigan and Leigh NHS Foundat…
#>  3 Airedale General Hospi… RCF        Airedale NHS Trust                        
#>  4 Wycombe Hospital        RXQ        Buckinghamshire Healthcare NHS Trust      
#>  5 Barnsley Hospital       RFF        Barnsley Hospital NHS Foundation Trust    
#>  6 Basildon Hospital       RDD        Basildon and Thurrock University Hospital…
#>  7 Royal United Hospital … RD1        Royal United Hospital Bath NHS Trust      
#>  8 Bedford Hospital        RC1        Bedford Hospital NHS Trust                
#>  9 Broomfield Hospital     RQ8        Mid Essex Hospital Services NHS Trust     
#> 10 Rochdale Infirmary      RW6        Pennine Acute Hospitals NHS Trust         
#> # … with 172 more rows

Answered By - stefan

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)