mutate with ifelse and grepl in R and create new column with matched string

Question

I am trying to create new column with grepl(partial match) and ifelse using mutate. I have list of drug names with unit and I want to create new column with these units. Here is my drug name samples,

 Description
    ZOLEDRONIC ACID INF 4MG/5ML (5ML)
    CALCICHEW D3 FORTE CHEWABLE TABS
    DAYCASE DRUGS
    AKYNZEO (NETUPITANT/PALONOSETRON) HARD CAPS 300MG/0.5MG
    CYCLOPHOSPHAMIDE INF 1MG/ML
    EPIRUBICIN INJ 1MG/ML
    DAYCASE DRUGS
    DAYCASE DRUGS
    ALOXI (PALONOSETRON) INJ 250MCG
    PACLITAXEL INF 1MG/ML
    DEXAMETHASONE VIAL 6.6MG/2ML - USE PSD742
    PACLITAXEL INF 1MG/ML
    RAMUCIRUMAB INF 0.1MG - 100MG

Here is my tried code,

units <- c("TABS","INF","SYR","CAPS","INJ","VIAL","SOL","POWDER","GEL","CREAM","LOTION","AMP"," SINGLEJECT","PFS")

drug_name <- drug_name %>% mutate(unit =  grepl(paste(units,collapse = "|"),Description))

current output is like this,

Description                                 unit
ZOLEDRONIC ACID INF 4MG/5ML (5ML)           TRUE
    CALCICHEW D3 FORTE CHEWABLE TABS        TRUE
    DAYCASE DRUGS                           FALSE
    AKYNZEO (NETUPITANT/PALONOSETRON) CAPS  TRUE
    CYCLOPHOSPHAMIDE INF 1MG/ML             TRUE
    EPIRUBICIN INJ 1MG/ML                   TRUE
    DAYCASE DRUGS                           FALSE
    DAYCASE DRUGS                           FALSE

I dont want unit in TRUE/FALSE, instead it should specify the exact unit, Like this,

        ZOLEDRONIC ACID INF 4MG/5ML (5ML)       INF
        CALCICHEW D3 FORTE CHEWABLE TABS        TABS

I am trying to recode this one but dont know how to do this. Also, I need to extract metrics in drug name (4MG/5ML) into a separate column. I can do this in another query but need to do it single query with unit. Not all the drugs have this format some of them like this--> 01.MG-100MG, 250MCG.

            ZOLEDRONIC ACID INF 4MG/5ML (5ML)       INF    4MG/5ML
            CALCICHEW D3 FORTE CHEWABLE TABS        TABS   NA
            CYCLOPHOSPHAMIDE INF 1MG/ML             INF    1MG/ML
            ALOXI (PALONOSETRON) INJ 250MCG         INF    250MCG

Can anyone help me to solve this?

Try drug_name <- drug_name %>% mutate(unit = sub(paste0("^(?:.*(", paste(units,collapse = "|"), ").*|.*)$"), "\\1", Description)) — Wiktor Stribiżew
@WiktorStribiżew Thank you this works for me and i am trying to extract the metrics from drug name which i can do as a separate query but I am trying to do it in single one. — Sharmi

r2evans r2evans · Accepted Answer · 2018-09-20T14:49:12

Here's one method using dplyr and purrr.

library(dplyr)
library(purrr)
drug_name <- data_frame(
  Description = trimws(readLines(textConnection('ZOLEDRONIC ACID INF 4MG/5ML (5ML)
    CALCICHEW D3 FORTE CHEWABLE TABS
    DAYCASE DRUGS
    AKYNZEO (NETUPITANT/PALONOSETRON) HARD CAPS 300MG/0.5MG
    CYCLOPHOSPHAMIDE INF 1MG/ML
    EPIRUBICIN INJ 1MG/ML
    DAYCASE DRUGS
    DAYCASE DRUGS
    ALOXI (PALONOSETRON) INJ 250MCG
    PACLITAXEL INF 1MG/ML
    DEXAMETHASONE VIAL 6.6MG/2ML - USE PSD742
    PACLITAXEL INF 1MG/ML
    RAMUCIRUMAB INF 0.1MG - 100MG')))
)
units <- c("TABS","INF","SYR","CAPS","INJ","VIAL","SOL","POWDER",
           "GEL","CREAM","LOTION","AMP","SINGLEJECT","PFS")
units1 <- sprintf("\\b(%s)\\b", paste(units, collapse = "|"))

First, a walk-through and proof-of-concept:

m <- regmatches(drug_name$Description,
                gregexpr(paste0(units1, ".*"), drug_name$Description))
m
# [[1]]
# [1] "INF 4MG/5ML (5ML)"
# [[2]]
# [1] "TABS"
# [[3]]
# character(0)
# [[4]]
# [1] "CAPS 300MG/0.5MG"
# [[5]]
# [1] "INF 1MG/ML"
# [[6]]
# [1] "INJ 1MG/ML"
# [[7]]
# character(0)
# [[8]]
# character(0)
# [[9]]
# [1] "INJ 250MCG"
# [[10]]
# [1] "INF 1MG/ML"
# [[11]]
# [1] "VIAL 6.6MG/2ML - USE PSD742"
# [[12]]
# [1] "INF 1MG/ML"
# [[13]]
# [1] "INF 0.1MG - 100MG"

I "get all following" with .* in order to find the amount of the unit. This will at times get more than we want, but in order to find all value-like words after a unit, we need to grab all and then filter.

We need to guard against the character(0) and ideally convert to a vector, so

fixEmpties <- function(lst, default=NA_character_)
  unlist(replace(lst, lengths(lst) == 0L, default))

though you may prefer to use an empty string "" instead of NA_character_.

To filter, here's a quick function that looks for any number in each of the following "words". It also allows for the solitary "-", but removes it if it is that last match.

extractAmounts <- function(s, default=NA_character_) {
  vec <- strsplit(s, "\\s+")[[1]][-1]
  vec2 <- which(cumall(grepl("[0-9]", vec) | vec == "-"))
  if (isTRUE(length(vec2) > 0)) {
    # remove a trailing "-" that does not result in a range
    if (vec[ tail(vec2, 1) ] == "-") vec2 <- vec2[-length(vec2)]
    return(paste(vec[vec2], collapse = " "))
  } else return(default)
}

And a quick one to get the original unit, always the first word.

extractUnits <- function(s)
  head(strsplit(s, "\\s+")[[1]], 1)

Test:

m <- fixEmpties(m)
sapply(m, extractUnits, USE.NAMES=FALSE)
#  [1] "INF"  "TABS" NA     "CAPS" "INF"  "INJ"  NA     NA     "INJ"  "INF" 
# [11] "VIAL" "INF"  "INF" 
sapply(m, extractAmounts, USE.NAMES=FALSE)
#  [1] "4MG/5ML (5ML)" NA              NA              "300MG/0.5MG"  
#  [5] "1MG/ML"        "1MG/ML"        NA              NA             
#  [9] "250MCG"        "1MG/ML"        "6.6MG/2ML"     "1MG/ML"       
# [13] "0.1MG - 100MG"

Okay, now to put this into a pipe:

drug_name %>%
  mutate(
    full = fixEmpties(regmatches(Description, gregexpr(paste0(units1, ".*"), Description))),
    unit = map_chr(full, extractUnits),
    amt = map_chr(full, extractAmounts)
  ) %>%
  select(-full)
# # A tibble: 13 x 3
#    Description                                             unit  amt          
#    <chr>                                                   <chr> <chr>        
#  1 ZOLEDRONIC ACID INF 4MG/5ML (5ML)                       INF   4MG/5ML (5ML)
#  2 CALCICHEW D3 FORTE CHEWABLE TABS                        TABS  <NA>         
#  3 DAYCASE DRUGS                                           <NA>  <NA>         
#  4 AKYNZEO (NETUPITANT/PALONOSETRON) HARD CAPS 300MG/0.5MG CAPS  300MG/0.5MG  
#  5 CYCLOPHOSPHAMIDE INF 1MG/ML                             INF   1MG/ML       
#  6 EPIRUBICIN INJ 1MG/ML                                   INJ   1MG/ML       
#  7 DAYCASE DRUGS                                           <NA>  <NA>         
#  8 DAYCASE DRUGS                                           <NA>  <NA>         
#  9 ALOXI (PALONOSETRON) INJ 250MCG                         INJ   250MCG       
# 10 PACLITAXEL INF 1MG/ML                                   INF   1MG/ML       
# 11 DEXAMETHASONE VIAL 6.6MG/2ML - USE PSD742               VIAL  6.6MG/2ML    
# 12 PACLITAXEL INF 1MG/ML                                   INF   1MG/ML       
# 13 RAMUCIRUMAB INF 0.1MG - 100MG                           INF   0.1MG - 100MG

mutate with ifelse and grepl in R and create new column with matched string

2 Answers

tidyverse solution