Removing regular expressions from text string in a data-frame in R

Question

I have a data-set with 1000 rows with text containing the order description of lamps. The data is full of inconsistent regex patterns and after referring to the few solutions, I got some help, but its not solving the issue. R remove multiple text strings in data frame remove multiple patterns from text vector r

I want to remove all delimiters and also keep only the words present in the wordstoreplace vector.

I have tried removing the delimiters using lapply and post that I have created 2 vectors- "wordstoremove" and "wordstoreplace"

I am trying to apply "str_remove_all()" and the "str_replace_all()". The the first function worked but the second did not.

Initially I had tried using a very naive approach but it was too clumsy.

mydata_sample=data.frame(x=c("LAMP, FLUORESCENT;TYPE TUBE LIGHT, POWER 8 W, POTENTIAL 230 V, COLORWHITE, BASE G5, LENGTH 302.5 MM; P/N: 37755,Mnfr:SuryaREF:  MODEL: FW/T5/33 GE 1/25,",
                        "LAMP, INCANDESCENT;TYPE HALOGEN, POWER 1 KW, POTENTIAL 230 V, COLORWHITE, BASE R7S; Make: Surya",
                        "BALLAST, LAMP; TYPE: ELECTROMAGNETIC, LAMP TYPE: TUBELIGHT/FLUORESCENT, POWER: 36/40 W, POTENTIAL: 240VAC 50HZ; LEGACY NO:22038  Make :Havells , Cat Ref No : LHB7904025",
                        "SWITCH,ELECTRICAL,TYPE:1 WCR   WAY,VOLTAGE:230V,CURRENT RATED:10A,NUMBEROFPOLES:1P,ADDITIONAL INFORMATION:FOR SNAPMODULESWITCH",
                 "Brief Desc:HIGH PRES. SODIUM VAPOUR LAMP 250W/400WDetailed Desc:Purchase order text :Short Description :HIGH PRES. SODIUM VAPOURLAMP 250W/400W===============================Part No :SON-T 250W/400W===============================Additional Specification :HIGH PRESSURE SODIUM VAPOUR LAMPSON-T 250W/400W USED  IN SURFACE INS SYSTEM TOP LIGHT"))

delimiters1=c('"',"\r\n",'-','=',';')
delimiters2=c('*',',',':')

library(dplyr)
library(stringr)
dat <- mydata_sample %>%   
  mutate(x1 = str_remove_all(x1, regex(str_c("\\b",delimiters1, "\\b", collapse = '|'), ignore_case = T)))

dat <- mydata_sample %>%   
  mutate(x1 = str_remove_all(x1, regex(str_c("\\b",delimiters2, "\\b", collapse = '|'), ignore_case = T)))
####Error in stri_replace_all_regex(string, pattern, fix_replacement(replacement),  : 
  Syntax error in regexp pattern. (U_REGEX_RULE_SYNTAX)

wordstoremove=c('Mnfr','MNFR',"VAPOURTYPEHIGH",'LHZZ07133099MNFR',"BJHF","BJOS",
                "BGEMF","BJIR","LIGHTING","FFT","FOR","ACCOMMODATIONQUANTITY","Cat",
                "Ref","No","Type","TYPE","QUANTITY","P/N")

wordstoreplace=c('HAVELLS','Havells','Bajaj','BAJAJGrade A','PHILIPS',
                 'Philips',"MAKEBAJAJ/CG","philips","Philips/Grade A/Grade A/CG/GEPurchase","CG","Bajaj",
                 "BAJAJ")

dat1 <- dat%>%   
  mutate(x1 = str_remove_all(x1, regex(str_c("\\b",wordstoremove, "\\b", collapse = '|'), ignore_case = T)))

dat1=dat1 %>%
  mutate(x1=str_replace_all(x1, wordstoreplace, 'Grade A'),ignore_case = T)
###Warning message:
In stri_replace_all_regex(string, pattern, fix_replacement(replacement),  :
  longer object length is not a multiple of shorter object length

score 1 · Accepted Answer · answered May 18 '19 at 01:07

The regex is failing because you need to escape all special characters. See the differences here:

# orig delimiters1=c('"', "\r\n", '-', '=', ';')
delimiters1=c('\\"', "\r\n", '-', '\\=', ';')

# orig delimiters2=c('*', ',', ':')
delimiters2=c('\\*', ',', '\\:')

For the str_replace_all() you need the words to be a single string separated by a | rather than a vector of 12

wordstoreplace <-
  c('HAVELLS','Havells','Bajaj','BAJAJGrade A','PHILIPS',
    'Philips',"MAKEBAJAJ/CG","philips","Philips/Grade A/Grade A/CG/GEPurchase","CG","Bajaj",
    "BAJAJ") %>% 
  paste0(collapse = "|")
# "HAVELLS|Havells|Bajaj|BAJAJGrade A|PHILIPS|Philips|MAKEBAJAJ/CG|philips|Philips/Grade A/Grade A/CG/GEPurchase|CG|Bajaj|BAJAJ"

This then runs without throwing an error

dat1 <-
  dat %>%
  mutate(
    x1 = 
      str_remove_all(x1, regex(str_c("\\b", wordstoremove, "\\b", collapse = "|"), ignore_case = T)),
    x1 = str_replace_all(x1, wordstoreplace, "Grade A")
  )

Thanks so much @yake84. The delimiter and the error correction were very helpful. — Ami, May 20 '19 at 06:11
That's great to hear. Can you please mark the question as answered since it worked for you? — yake84, May 20 '19 at 12:02

Removing regular expressions from text string in a data-frame in R

1 Answers1