Using For Loop with Paste to replace value in df with value from another df

user_13856837 0
Viewed 75

I have a question about replacing misspellings in one dataframe with the standardised spelling from another dataframe. To be specific, I have a huge file containing multiple columns of antibiotic names (that have been misspelled) and their corresponding result (either resistant(-) or sensitive(+)) in the adjacent column. I have made a new df containing the standardised version of each antibiotic name, but I am unsure how I can replace the many misspellings across multiple columns in the first dataframe with the standardised version, while keeping it associated with the original result. Here is an example of my df containing 3 columns of misspelled antibiotics and their lab results

Antibiotics.1 <- tibble(Sample = c('1','2','3'),
              A1_Name = c('AMOXCILLIN','AMOXCILLIN','CHLORAMHENICOL'),
              A1_Result = c('+','-','-'),
              A2_Name = c('CHLORAMPHENICOL ','APRMYCIN ','APRMYCIN '),
              A2_Result = c('-','+','-'),
              A3_Name = c('FLORFENICO','FLORFENICO','AMOXCILLIN'),
              A3_Result = c('+','+','-'))

Here is an example df containing the standardised antibiotic names (that I want to replace the misspelling with in the previous df)

standardised_antibiotics.1 <- tibble(A_Name = c('AMOXCILLIN','CHLORAMHENICOL','APRMYCIN','FLORFENICO'),
              A_Name_Standardised = c('AMOXICILLIN','CHLORAMPHENICOL','APRAMYCIN','FLORFENICOL'))

I have too many misspellings to type them all by hand, so ideally I need something that will work row by row. Where we match the misspelling in one df with the identical misspelling in the standardised df, and then replace it with the standardised version in the adjacent column. I have considered writing a function, using 'for' loop or the 'across' function with 'case_when'. I'm not sure what the best approach is here. Any help would be much appreciated!

paste dataframe r loops replace
1 Answers

First add the correct spellings to the lookup data frame:

standardised_antibiotics.1 <- tibble(A_Name = c('AMOXCILLIN','CHLORAMHENICOL','APRMYCIN','FLORFENICO', 'AMOXICILLIN','CHLORAMPHENICOL','APRAMYCIN','FLORFENICOL'),
                                     A_Name_Standardised = c('AMOXICILLIN','CHLORAMPHENICOL','APRAMYCIN','FLORFENICOL', 'AMOXICILLIN','CHLORAMPHENICOL','APRAMYCIN','FLORFENICOL'))

Then you can match() using a scoped approach with dplyr:

library(dplyr)

Antibiotics.1 %>%
  mutate(across(ends_with("Name"), ~ standardised_antibiotics.1$A_Name_Standardised[match(trimws(.x), standardised_antibiotics.1$A_Name)]))

# A tibble: 3 x 7
  Sample A1_Name         A1_Result A2_Name         A2_Result A3_Name     A3_Result
  <chr>  <chr>           <chr>     <chr>           <chr>     <chr>       <chr>    
1 1      AMOXICILLIN     +         CHLORAMPHENICOL -         FLORFENICOL +        
2 2      AMOXICILLIN     -         APRAMYCIN       +         FLORFENICOL +        
3 3      CHLORAMPHENICOL -         APRAMYCIN       -         AMOXICILLIN -

Or without adding the correct names, you can use stringr::str_replace_all():

library(stringr)

Antibiotics.1 %>%
  mutate(across(ends_with("Name"), ~ str_replace_all(trimws(.x), setNames(standardised_antibiotics.1$A_Name_Standardised, standardised_antibiotics.1$A_Name))))
user_28352610
Related