CODEWITHSUNDEEP
pythonpandassubstring
Peter Lucas
Peter Lucas

Reputation: 1991

Pandas : Find the first occurence of a string then take all characters thereafter up until anther string

I'm looking to update a column with the ISIN or CUSIP portion of a string which is contained in another column:

my_DestSystemNote1_string =  'ISIN=XS1906311763|CUSIP=         |CalTyp=1'
dfDest = [('DestSystemNote1', ['ISIN=XS1906311763|CUSIP=         |CalTyp=1', 
                               'ISIN=XS0736418962|CUSIP=         |CalTyp=1', 
                               'ISIN=XS1533910508|CUSIP=         |CalTyp=1', 
                               'ISIN=US404280AS86|CUSIP=404280AS8|CalTyp=1', 
                               'ISIN=US404280BW89|CUSIP=404280BW8|CalTyp=21',
                               'ISIN=US06738EBC84|CUSIP=06738EBC8|CalTyp=21',
                               'ISIN=XS0736418962|CUSIP=         |CalTyp=1',]),
         ]
# create pandas df
dfDest = pd.DataFrame.from_items(dfDest)
display(dfDest)
print("")

DestSystemNote1 contains the source string from which either ISIN or CUSIP need to be extracted:

DestSystemNote1                                Found_ISIN    Found_CUSIP
ISIN=XS1906311763|CUSIP=         |CalTyp=1     XS1906311763
ISIN=XS0736418962|CUSIP=         |CalTyp=1     XS0736418962
ISIN=XS1533910508|CUSIP=         |CalTyp=1     XS1533910508
ISIN=US404280AS86|CUSIP=404280AS8|CalTyp=1     US404280AS86  404280AS8
ISIN=US404280BW89|CUSIP=404280BW8|CalTyp=21    US404280BW89  404280BW8
ISIN=US06738EBC84|CUSIP=06738EBC8|CalTyp=21    US06738EBC84  06738EBC8
ISIN=XS0736418962|CUSIP=         |CalTyp=1     XS0736418962

ISIN's will always be preceeded by "ISIN=" and end a character before "|"

CUSIPS's will always be preceeded by "CUSIP=" and end a character before "|"

My attempt is as follows:

my_DestSystemNote1_string =  'ISIN=XS1906311763|CUSIP=         |CalTyp=1'
code = my_DestSystemNote1_string.split("ISIN=",1)[1]
code = code[:12]
print(code)

XS1906311763

So I'm getting there but would like to paramaterise it to find the nth occurence of a passed string (strStart) then take all characters after it's end char + 1 and up to, but not including; the nth occurence of another string (strEnd).

Pete

Upvotes: 1

Views: 287

Answers (1)

Karl
Karl

Reputation: 5822

Working from this answer (Find the nth occurrence of substring in a string):

def findnth(haystack, needle, n):
    parts= haystack.split(needle, n+1)
    if len(parts)<=n+1:
        return -1
    return len(haystack)-len(parts[-1])-len(needle)

you could do something along the following lines:

def split_between(input_string, start_str, start_occurence, end_str, end_occurence):
    start_index = findnth(input_string, start_str, start_occurence-1) + len(start_str)
    end_index = findnth(input_string, end_str, end_occurence-1)
    return input_string[start_index:end_index]

input_string="ISIN=111111|ISIN=222222|333333|ISIN=444444"

split_between(input_string, "ISIN=", 2, "|", 2)
# returns "222222"

Upvotes: 1

Related Questions