Extract Value between backslashes from string column

Question

Using Python 3.10 have a column that has values stored in a URL string, I'm looking to extract the numeric value between two backslashes as well as the row id which is in a structured column.

The value that proceeds the backslashes is conditional and could be, "DomainId", "DomainainSiteId" etc. and the url may may also vary slightly in length and characters. Lastly, the length of the numeric value between the backslashes may vary 5 - 9 bytes, but will always be between slashes.

id url
https://company.db.abcd.com/DomainId/123456789/Other https://company.db.abcd.com/DomainainSiteId/123456/Other https://companyaddedwords.db.abcd.com/DomainId/1234567/Other

Work in process df.url.str.extract('\w/(?P.+)\Z', expand=True)

Can't seem to figure out the terminators to pull the numeric value only with Regex wondering if findall is a better option

Expected Output
id  DomainId  DomainSiteId  
1   123456789  
2              123456  
3   1234567

Current Output
DomainId
DomainId/123456789/Other
DomainSightId/123456/Other
DomainId/1234567/Other

Answer 1

You can use 2 named capture groups denoted with (?P<groupname>...) and use an alternation with | to capture both variants:

/DomainId/(?P<DomainId>\d{5,9})\b|/DomainainSiteId/(?P<DomainainSiteId>\d{5,9})\b

Regex demo

pattern = r'/DomainId/(?P<DomainId>\d{5,9})\b|/DomainainSiteId/(?P<DomainainSiteId>\d{5,9})\b'
df = df.url.str\
    .extract(pattern)\
    .fillna('')
print(df)

Output

    DomainId DomainainSiteId
0  123456789                
1                     123456
2    1234567 

Answer 2

You can try to use .str.extract and then pivot the dataframe:

x = df['url'].str.extract(r'([^/]+)/(\d{5,9})')
print(x.pivot(columns=0, values=1).fillna(''))

Prints:

0   DomainId DomainainSiteId
0  123456789                
1                     123456
2    1234567                

---

Initial dataframe:

                                                            url
0          https://company.db.abcd.com/DomainId/123456789/Other
1      https://company.db.abcd.com/DomainainSiteId/123456/Other
2  https://companyaddedwords.db.abcd.com/DomainId/1234567/Other

Answer 3

You can use the following pattern to capture both values.

//.+?/(.+?)/(\d+)/

Output

DomainId, 123456789
DomainainSiteId, 123456
DomainId, 1234567