How to remove these elements from my string in R

user_6387901 0
Viewed 42

One of my columns contains the following strings:

    259333154-Carat Programmatic»FCO»O»EV3»D&B - FCO Prospects ABM DDD 2020 D&B ABM ENDING 1/31/2020»NA+728 x 90»IN»UNV»TP»PM»dCPM»NAT»BTP»RON»NA»DB»N/A»ENG»M»P159WXZ
    238114259-Carat Programmatic»CPO XT5»O»EV2»END DATE 2/28/19 Google Custom Intent - XT5 CPO Google Custom Intent Audience/In Market Audience»NA+160 x 600»IN»DSK»TP»PM»CPM»NAT»BTS»RON»NA»DB»N/A»ENG»M»PX5LV6
    251368220-Carat Programmatic»XT6»O»EV1»END DATE 9/30/19 2019   Cadillac SMRT Always On - CRM - SMRT Segment XT6 Desktop Display»NA+160 x 600»IN»DSK»TP»PM»CPM»NAT»PRS»RON»NA»DB»N/A»ENG»M»P12LQG3
    235105211-Ebay»Silverado 1500»M»CON»ended   3/20 - ROS - eBay Run of Motors Desktop»NA+300 x 250»IN»DSK»TP»PM»CPM»NAT»SCR»ROS»NA»DB»N/A»ENG»M»PW79JH
    234990143-Carat Programmatic»XT4»O»EV2»Endemic - Loyalist|Edmunds&Oracle|N/A|Vehicle|N/A 2»NA+300 x 250»IN»MOB»TP»PM»CPM»NAT»BTO»RON»IMW»DB»N/A»ENG»M»PW7NSN

My task is to remove the following from the strings:

  1. Dates (e.g. 1/31/2020, 3/20)
  2. Strings like Ending, ENDING, END, end, End, END DATE but NOT the "end" from strings that have endemic in them like the last one
  3. Double spaces e.g. " "

I am having a really hard time with this. I am using several lines of code as follows to perform some of the operations, but don't know how to go about doing this more succinctly and completely:

dat = gsub("2017|2018|2019|2020", "" ,dat)
dat = gsub("»»", "»", dat)
dat = gsub("END ", "", dat)
dat = gsub("end ", "", dat)
dat = gsub("Ended ", "", dat)
dat = gsub("ENDED ", "", dat)
dat = gsub("DATE|date|Date", "", dat)

Thanks so much for your help!

gsub str-replace r replace string
1 Answers

Check the following:

  • \b(?:0?[1-9]|1[012])(?:[-/.](?:0?[1-9]|[12][0-9]|3[01]))?[-/.](?:19|20)?\d\d\b should handle the "dates (e.g. 1/31/2020, 3/20)" case
  • (?i)\bEnd(?: DATE|(?:ing)?)\b should handle the "strings like "Ending", "ENDING", "END", "end", "End", "END DATE" but NOT the "end" from strings that have "endemic" in them like the last one" case
  • ([\s»]){2,} should handle the "double spaces e.g. " "" case.

Combining all:

gsub("\\b(?:End(?:\\s+DATE|(?:ing)?)|(?:0?[1-9]|1[012])(?:[-/.](?:0?[1-9]|[12][0-9]|3[01]))?[-/.](?:19|20)?\\d\\d)\\b|([\\s»]){2,}", "\\1", x, perl=TRUE, ignore.case=TRUE)

See proof.

Explanation

--------------------------------------------------------------------------------
  \b                       the boundary between a word char (\w) and
                           something that is not a word char
--------------------------------------------------------------------------------
  (?:                      group, but do not capture:
--------------------------------------------------------------------------------
    End                      'End'
--------------------------------------------------------------------------------
    (?:                      group, but do not capture:
--------------------------------------------------------------------------------
       DATE                    ' DATE'
--------------------------------------------------------------------------------
     |                        OR
--------------------------------------------------------------------------------
      (?:                      group, but do not capture (optional
                               (matching the most amount possible)):
--------------------------------------------------------------------------------
        ing                      'ing'
--------------------------------------------------------------------------------
      )?                       end of grouping
--------------------------------------------------------------------------------
    )                        end of grouping
--------------------------------------------------------------------------------
   |                        OR
--------------------------------------------------------------------------------
    (?:                      group, but do not capture:
--------------------------------------------------------------------------------
      0?                       '0' (optional (matching the most
                               amount possible))
--------------------------------------------------------------------------------
      [1-9]                    any character of: '1' to '9'
--------------------------------------------------------------------------------
     |                        OR
--------------------------------------------------------------------------------
      1                        '1'
--------------------------------------------------------------------------------
      [012]                    any character of: '0', '1', '2'
--------------------------------------------------------------------------------
    )                        end of grouping
--------------------------------------------------------------------------------
    (?:                      group, but do not capture (optional
                             (matching the most amount possible)):
--------------------------------------------------------------------------------
      [-/.]                    any character of: '-', '/', '.'
--------------------------------------------------------------------------------
      (?:                      group, but do not capture:
--------------------------------------------------------------------------------
        0?                       '0' (optional (matching the most
                                 amount possible))
--------------------------------------------------------------------------------
        [1-9]                    any character of: '1' to '9'
--------------------------------------------------------------------------------
       |                        OR
--------------------------------------------------------------------------------
        [12]                     any character of: '1', '2'
--------------------------------------------------------------------------------
        [0-9]                    any character of: '0' to '9'
--------------------------------------------------------------------------------
       |                        OR
--------------------------------------------------------------------------------
        3                        '3'
--------------------------------------------------------------------------------
        [01]                     any character of: '0', '1'
--------------------------------------------------------------------------------
      )                        end of grouping
--------------------------------------------------------------------------------
    )?                       end of grouping
--------------------------------------------------------------------------------
    [-/.]                    any character of: '-', '/', '.'
--------------------------------------------------------------------------------
    (?:                      group, but do not capture (optional
                             (matching the most amount possible)):
--------------------------------------------------------------------------------
      19                       '19'
--------------------------------------------------------------------------------
     |                        OR
--------------------------------------------------------------------------------
      20                       '20'
--------------------------------------------------------------------------------
    )?                       end of grouping
--------------------------------------------------------------------------------
    \d                       digits (0-9)
--------------------------------------------------------------------------------
    \d                       digits (0-9)
--------------------------------------------------------------------------------
  )                        end of grouping
--------------------------------------------------------------------------------
  \b                       the boundary between a word char (\w) and
                           something that is not a word char
--------------------------------------------------------------------------------
 |                        OR
--------------------------------------------------------------------------------
  (                        group and capture to \1 (at least 2 times
                           (matching the most amount possible)):
--------------------------------------------------------------------------------
    [\s»]                    any character of: whitespace (\n, \r,
                             \t, \f, and " "), '»'
--------------------------------------------------------------------------------
  ){2,}                    end of \1 (NOTE: because you are using a
                           quantifier on this capture, only the LAST
                           repetition of the captured pattern will be
                           stored in \1)
user_113298900
Related