left join two data frames, no error codes but not working

user_13631281 0
Viewed 73

I have two df and I would like to do left_join. But for some reason I can not join them together. Could anyone take a look and see where I did wrong? No error codes just the info from cov are all empty.

df<-structure(list(LBCAT = c("CHEMISTRY", "CHEMISTRY", "CHEMISTRY", 
"CHEMISTRY", "CHEMISTRY", "CHEMISTRY"), LBTEST = c("ALANINE AMINOTRANSFERASE", 
"ALANINE AMINOTRANSFERASE", "ALANINE AMINOTRANSFERASE", "ALANINE AMINOTRANSFERASE", 
"ALANINE AMINOTRANSFERASE", "ALANINE AMINOTRANSFERASE"), lbstresn = c(6, 
11, 26, 21, 14, 13), lbstresu = c("U/L", "U/L", "U/L", "U/L", 
"U/L", "U/L"), lbstnrlo = c(6, 6, 6, 6, 6, 6), lbstnrhi = c(37, 
37, 37, 37, 37, 37)), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))

cov<-structure(list(LBCAT = c("CHEMISTRY", "CHEMISTRY", "CHEMISTRY", 
"CHEMISTRY", "CHEMISTRY", "CHEMISTRY", "CHEMISTRY"), LBTESTCD = c("ALT", 
"ALT", "ALT", "ALT", "ALT", "ALT", "ALT"), LBTEST = c("Alanine Aminotransferase", 
"Alanine Aminotransferase", "Alanine Aminotransferase", "Alanine Aminotransferase", 
"Alanine Aminotransferase", "Alanine Aminotransferase", "Alanine Aminotransferase"
), LBORRESU = c("NKAT/L", "IU/L", "MU/ML", "U/L", "UKAT/L", "MCKAT/L", 
"IU/ML"), LBSTRESU = c("ukat/L", "ukat/L", "ukat/L", "ukat/L", 
"ukat/L", "ukat/L", "ukat/L"), LBSICF = c(0.001, 0.0167, 0.0167, 
0.0167, 1, 1, 16.7)), row.names = c(NA, -7L), class = c("tbl_df", 
"tbl", "data.frame"))

test <- left_join(df, cov, by = c('LBCAT'= toupper('LBCAT'), 'LBTEST' = toupper('LBTEST'), 'lbstresu' = toupper('LBORRESU')))

enter image description here

left-join r
3 Answers

We need to mutate first to change the columns to upper case

library(dplyr)
cov %>%
   dplyr::mutate(across(c(LBCAT, LBTEST, LBORRESU), toupper)) %>%
   left_join(df, ., by = c("LBCAT", "LBTEST", 'lbstresu' = 'LBORRESU'))

-output

# A tibble: 6 x 9
  LBCAT     LBTEST                   lbstresn lbstresu lbstnrlo lbstnrhi LBTESTCD LBSTRESU LBSICF
  <chr>     <chr>                       <dbl> <chr>       <dbl>    <dbl> <chr>    <chr>     <dbl>
1 CHEMISTRY ALANINE AMINOTRANSFERASE        6 U/L             6       37 ALT      ukat/L   0.0167
2 CHEMISTRY ALANINE AMINOTRANSFERASE       11 U/L             6       37 ALT      ukat/L   0.0167
3 CHEMISTRY ALANINE AMINOTRANSFERASE       26 U/L             6       37 ALT      ukat/L   0.0167
4 CHEMISTRY ALANINE AMINOTRANSFERASE       21 U/L             6       37 ALT      ukat/L   0.0167
5 CHEMISTRY ALANINE AMINOTRANSFERASE       14 U/L             6       37 ALT      ukat/L   0.0167
6 CHEMISTRY ALANINE AMINOTRANSFERASE       13 U/L             6       37 ALT      ukat/L   0.0167
user_37322710

We could do this with fuzzyjoin argument ignore_case=TRUE

require(fuzzyjoin)
regex_left_join(df,cov,by= c('LBCAT', 'LBTEST', 'lbstresu' = 'LBORRESU'), ignore_case =TRUE)

# A tibble: 6 x 12
  LBCAT.x   LBTEST.x                 lbstresn lbstresu lbstnrlo lbstnrhi LBCAT.y   LBTESTCD LBTEST.y        LBORRESU LBSTRESU LBSICF
  <chr>     <chr>                       <dbl> <chr>       <dbl>    <dbl> <chr>     <chr>    <chr>           <chr>    <chr>     <dbl>
1 CHEMISTRY ALANINE AMINOTRANSFERASE        6 U/L             6       37 CHEMISTRY ALT      Alanine Aminot~ U/L      ukat/L   0.0167
2 CHEMISTRY ALANINE AMINOTRANSFERASE       11 U/L             6       37 CHEMISTRY ALT      Alanine Aminot~ U/L      ukat/L   0.0167
3 CHEMISTRY ALANINE AMINOTRANSFERASE       26 U/L             6       37 CHEMISTRY ALT      Alanine Aminot~ U/L      ukat/L   0.0167
4 CHEMISTRY ALANINE AMINOTRANSFERASE       21 U/L             6       37 CHEMISTRY ALT      Alanine Aminot~ U/L      ukat/L   0.0167
5 CHEMISTRY ALANINE AMINOTRANSFERASE       14 U/L             6       37 CHEMISTRY ALT      Alanine Aminot~ U/L      ukat/L   0.0167
6 CHEMISTRY ALANINE AMINOTRANSFERASE       13 U/L             6       37 CHEMISTRY ALT      Alanine Aminot~ U/L      ukat/L   0.0167
user_133216470

you can use join from plyr package.

library(plyr)

test <- plyr::join(df, cov, type = 'left', by = c('LBCAT', 'LBTEST'))
user_166134640
Related