英文:
Extract Value between backslashes from string column
问题
使用Python 3.10，在一个包含以URL字符串存储的值的列中，我想提取两个反斜杠之间的数字值，以及一个结构化列中的行ID。
在反斜杠之前的值是有条件的，可能是"DomainId"、"DomainainSiteId"等等，URL的长度和字符也可能略有不同。最后，反斜杠之间的数字值的长度可能在5到9个字节之间变化，但始终位于反斜杠之间。
import pandas as pd
# 示例数据
data = {'id': [1, 2, 3],
'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']}
df = pd.DataFrame(data)
# 使用正则表达式提取数字值
df['DomainId'] = df['url'].str.extract(r'/(\d+)/', expand=False)
df['DomainSiteId'] = df['url'].str.extract(r'/DomainainSiteId/(\d+)/', expand=False)
# 填充NaN
df = df.fillna('')
# 选择需要的列
result_df = df[['id', 'DomainId', 'DomainSiteId']]
print(result_df)
期望输出：
id DomainId DomainSiteId
0 1 123456789
1 2 123456 123456
2 3 1234567
当前输出：
id DomainId DomainSiteId
0 1 123456789
1 2 123456 123456
2 3 1234567
英文:
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<DomainId>.+)\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
答案1
得分: 1
你可以使用两个命名捕获组，使用 (?P<groupname>...) 标记，并使用 | 进行交替匹配以捕获这两种变体：
/DomainId/(?P<DomainId>\d{5,9})\b|/DomainainSiteId/(?P<DomainainSiteId>\d{5,9})\b
正则表达式示例：
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)
输出：
DomainId DomainainSiteId
0 123456789
1 123456
2 1234567
英文:
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
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
答案2
得分: 0
你可以尝试使用.str.extract然后对数据框进行透视：
x = df['url'].str.extract(r'([^/]+)/(\d{5,9})')
print(x.pivot(columns=0, values=1).fillna(''))
输出：
0 DomainId DomainainSiteId
0 123456789
1 123456
2 1234567
初始数据框：
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
英文:
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
答案3
得分: 0
你可以使用以下模式来捕获两个值。
//.+?/(.+?)/(\d+)/
输出
DomainId, 123456789
DomainainSiteId, 123456
DomainId, 1234567
英文:
You can use the following pattern to capture both values.
//.+?/(.+?)/(\d+)/
Output
DomainId, 123456789
DomainainSiteId, 123456
DomainId, 1234567
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论