Extracting string between multiple occurrence of same delimiter in python pandas

With your shown samples, please try following. We could use str.extract function pf Pandas here. Applying str.extract function on Test column and creating new column named Fetch in DataFrame.

df['Fetch'] = df['Test'].str.extract(r'^\|+([^|]*)\|.*',expand=False)

DataFrame's will be as follows:

    Test                            Fetch
0   |||||CHNBAD||POC-RM0EP7-01-A    CHNBAD

Explanation of regex:

^\|+     ##Matching 1 or more matches of | from starting of value.
([^|]*)  ##Creating 1st capturing group which has everything till next | comes.
\|.*     ##Matching | and everything till last of value.

I think regex is the solution:

import re

def clean_text(text):
    match = re.search(r'[|]+([A-Z]+)[|]+', text)
    if match:
        return match.group(1)
    else:
        print(f'WARNING: {text} does not follow the pattern')
        return ''

df["Fetch"]=df["Test"].apply(clean_text)

regex explanation: [|]+ consumes all pipes chars, then a group for upper case A-Z chars, ([A-Z])+, and finally make sure that some (at least one) pipe is present with [|]+

However, it may be a bad workaround if you have a bigger problem, maybe you can provide more details on how you arrive to this situation.

(1) If you just need to extract the first occurrence of such string:

You can use .str.extract(), as follows:

df['Fetch'] = df['Test'].str.extract(r'\|([^|]+)\|')

Regex \|([^|]+)\| is the simplest form you can use:

\| matches the delimiter | before the target string

( opening parenthesis for capturing group

[^|] character class for any character other than the delimiter |

+ quantifier for one or more occurrence(s)

) opening parenthesis for capturing group

\| matches the delimiter | after the target string

Result:

Added 2 more rows for more test cases

print(df)

                            Test   Fetch
0   |||||CHNBAD||POC-RM0EP7-01-A  CHNBAD
1  |||||CHNBAD||POC-RM0EP7-01-A|  CHNBAD
2           ||ABC||DEF|GHI|||JKL     ABC

(2) If you want to extract ALL the occurrences of such strings

You can use .str.extractall(), as follows:

df = df[['Test']].join(df['Test'].str.extractall(r'(?<=\|)([^|]+)(?=\|)').unstack().droplevel(0, axis=1).rename(lambda x: 'Fetch_' + str(x+1), axis=1))

Result:

print(df)

                            Test Fetch_1          Fetch_2 Fetch_3
0   |||||CHNBAD||POC-RM0EP7-01-A  CHNBAD              NaN     NaN
1  |||||CHNBAD||POC-RM0EP7-01-A|  CHNBAD  POC-RM0EP7-01-A     NaN
2           ||ABC||DEF|GHI|||JKL     ABC              DEF     GHI

Here, we need a more complex regex so as to extract 3 matches from the last row. If we use the previous regex, only 2 matches will be extracted.

If you want more explanation of the regex and codes, I can do it later, upon your request.