基于关键词的字符串切片：提取SiteID至新DataFrame的高效代码需求

Got it, let's solve this problem efficiently—handling 500k+ rows means we need to avoid slow loops and leverage pandas' vectorized operations. Here's a robust, fast approach:

Step-by-Step Explanation #

• Regex Pattern: We'll use r'SiteID=(\d{5})' to specifically capture 5-digit numbers immediately following SiteID=. The parentheses ensure we only extract the numeric ID, not the SiteID= prefix.
• Vectorized Extraction: Pandas' str.extractall() is perfect here—it processes all rows at once (no Python-level loops) and captures all SiteIDs per row (since your example has multiple in one string).
• Clean Up Results: Convert the extracted matches into a clean DataFrame, with optional handling for rows that have no SiteIDs.

Full Code Implementation #

import pandas as pd

# Sample data (replace this with your actual DataFrame/series)
data = {
    'raw_str': [
        'FSP10001GFelt Label=G_4201_K1108_SHMAIIGNDA_3, SiteID=32013 Label=G_MUNUNGA_QUARRY_1, SiteID=26241, LogicRNCID=3',
        'Another string with SiteID=12345, some other text',
        'No SiteID here at all'
    ]
}
df = pd.DataFrame(data)

# Define regex pattern to capture 5-digit SiteIDs
siteid_pattern = r'SiteID=(\d{5})'

# Extract all SiteIDs, reshape into a clean DataFrame
siteid_df = df['raw_str'].str.extractall(siteid_pattern) \
    .reset_index() \
    .rename(columns={0: 'SiteID', 'level_0': 'Original_Row_Index'})

# Optional: Drop rows where no SiteID was found (if needed)
siteid_df = siteid_df.dropna(subset=['SiteID'])

# Convert SiteID to integer type for better performance
siteid_df['SiteID'] = siteid_df['SiteID'].astype(int)

print(siteid_df)

Why This Works for Large Datasets #

• Speed: str.extractall() is implemented in C under the hood, so it's orders of magnitude faster than using apply() with a custom function (which loops through each row in Python). For 500k rows, this should process in seconds, not minutes.
• Accuracy: The regex is precise—it only matches 5-digit numbers after SiteID=, so you won't accidentally capture other numeric values in the string.
• Handles Multiple SiteIDs: If a single row has multiple SiteIDs (like your example), this method captures all of them and maps each back to the original row index so you can track where they came from.

Additional Notes #

• If you only want one SiteID per row (e.g., the first occurrence), use str.extract() instead of str.extractall().
• For even more speed, you can pre-compile the regex pattern using re.compile(siteid_pattern) and pass it to str.extractall()—though pandas usually optimizes this automatically.

内容的提问来源于stack exchange，提问作者Pramod Kumar