Extract text in between two strings if a sub-string present in between those two strings

user_13587008 0
Viewed 714

I have a raw text in a column named "message" as shown below:

Example dataframe

Date               message
2020-11-01         ['some not required text1', 'Startstring , 
                   'some not required text2', MandatorySubstring , 'some not 
                    required text3', 'ID :AB_CD, 'Stopstring' 'some not 
                    required text4', 'Startstring ID :cd_ab', 'some not 
                    required text5', 'ID :ghed','some not required text6', ID :zyx', 'Stopstring 'some 
                    not required text7']
2020-11-02         ['some not required text8', Startstring 'ID :ABCD', 
                   'some not required text9', 'ID :ED_GH', 'some not 
                    required text10', ID :X_YZ, Stopstring 'some not 
                    required text11', 'Startstring 
                    'some not required text12',MandatorySubstring 
                    'some not required text13', ID :z_yx', 'some not required text14''Stopstring 
                    'some not required text15']

I am looking to extract the string just after ID : in between Startstring and Stopstring if MandatorySubstring exists between Startstring and Stopstring and discarding the IDs if MandatorySubstring doesn't exists between Startstring and Stopstring. There may be multiple such instances in one Date.

Expected Output:

Date               message
2020-11-01         AB_CD 
2020-11-02         z_yx

I tried following pattern:

pattern = StartString\s*((?:(?!StartString).)*?MandatoryString 1.*?)\s*Stopstring

It gives me all text between the startstring and stoptstring. I have no clue how to extract text just after ID: from this text now.

Can Anyone provide me Regex pattern for this scenario? Help in this regard is highly appreciated. Thanks

pyspark apache-spark-sql apache-spark
2 Answers

Given the current input, I'd suggest

Startstring(?:(?!Startstring).)*?MandatorySubstring(?:(?!Startstring).)*?,['\s]*ID\s*:\s*([^',]*).*?Stopstring

See the regex demo. Details:

  • Startstring - a left-hand delimiter
  • (?:(?!Startstring).)*? - any zero or more chars each of which does not start a Startstring char sequence
  • MandatorySubstring - a string that must be present in between left- and right-hand delimiters
  • (?:(?!Startstring).)*? - any zero or more chars each of which does not start a Startstring char sequence
  • , - a comma
  • ['\s]* - zero or more ' or whitespace
  • ID - a string
  • \s*:\s* - colon enclosed with zero or more whitespaces
  • ([^',]*) - Group 1: any zero or more chars other than ' and ,
  • .*? - any zero or more chars other than line break chars, as few as possible
  • Stopstring - right-hand delimiter.
user_38329700

We can do this in three stages. First, use regex to search these strings for the start string, mandatory substring, and stop string. We use negative lookahead assertions to ensure the mandatory substring does indeed lie between two adjacent start and stop strings. Once we have these matches, we extract the IDs present in each one, and finally condense these down into a single list.

def extract_ids(s):
    matches = re.findall(r'Startstring((?:(?!Startstring).)*)MandatorySubstring((?:(?!Stopstring).)*)Stopstring', s)
    all_ids = [re.findall(r'ID\s*:([^,]*)', group) for match in matches for group in match]
    ids_flat = [i for l in all_ids for i in l]
    return ids_flat

s1 = """['some not required text1', 'Startstring , 'some not required text2', MandatorySubstring , 'some not required text3', 'ID :AB_CD, 'Stopstring' 'some not required text4', 'Startstring ID :cd_ab', 'some not required text5', 'ID :ghed','some not required text6', ID :zyx', 'Stopstring 'some not required text7']"""

s2 = """['some not required text8', Startstring 'ID :ABCD', 'some not required text9', 'ID :ED_GH', 'some not required text10', ID :X_YZ, Stopstring 'some not required text11', 'Startstring 'some not required text12',MandatorySubstring 'some not required text13', ID :z_yx', 'some not required text14''Stopstring 'some not required text15']"""

>>> extract_ids(s1)
['AB_CD']

>>> extract_ids(s2)
["z_yx'"]

To apply this to your dataframe, just use df['message'].map(extract_ids).

user_54964330
Related