Python(Google Colabs)从表格Description列提取指定Services内容

核心匹配规则 #

观察表格数据规律，有效服务条目满足两个固定特征：

• 所有Description文本中，服务内容固定出现在最后一个日/月/年格式日期的后方
• 有效服务内容末尾一定携带固定格式的服务编码：2位数字开头，以下划线分隔多段字符，基础格式为XX_XXX_XXXX_X_X，部分编码末尾带单字母后缀
• 不满足上述特征的行直接判定为无效行过滤

依赖准备 #

Colab环境默认已预装所需解析库，若运行提示缺包，执行以下命令安装即可：

!pip install beautifulsoup4 pandas

完整可运行代码 #

from bs4 import BeautifulSoup
import re
import pandas as pd

# 替换为你需要解析的HTML源码
html_content = """
<style type="text/css">
    table.tableizer-table {
        font-size: 12px;
        border: 1px solid #CCC; 
        font-family: Arial, Helvetica, sans-serif;
    } 
    .tableizer-table td {
        padding: 4px;
        margin: 3px;
        border: 1px solid #CCC;
    }
    .tableizer-table th {
        background-color: #104E8B; 
        color: #FFF;
        font-weight: bold;
    }
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>Source</th><th>Description</th><th>Services</th></tr></thead>
<tbody>
 <tr><td>Receivable Invoice</td><td>Peter Jackson - 2/2/2022 - Access Community, Social And Rec Activities - Standard - Weekday Daytime - TTP 9:00AM 12:00PM 04_104_0125_6_1_T</td><td>Access Community, Social And Rec Activities - Standard - Weekday Daytime - TTP 9:00AM 12:00PM 04_104_0125_6_1_T</td></tr>
 <tr><td>Receivable Invoice</td><td>Helen Johnson - 30/06/2021- - Transport 04_590_0125_6_1</td><td>Transport 04_590_0125_6_1</td></tr>
 <tr><td>Receivable Invoice</td><td>Charlotte Jane - Reversal of difference charged Domestic shifts from 22/9/2021 - 31/12/2021</td><td>&nbsp;</td></tr>
 <tr><td>Receivable Invoice</td><td>Mary Jane - Support Coordination - 10/3/2022 - Psychosocial Recovery Coaching - Weekday Daytime 07_101_0106_6_3</td><td>Psychosocial Recovery Coaching - Weekday Daytime 07_101_0106_6_3</td></tr>
</tbody></table>
"""

# 解析目标表格
soup = BeautifulSoup(html_content, 'html.parser')
table = soup.find('table', class_='tableizer-table')
rows = table.find_all('tr')[1:]  # 跳过表头行

# 预编译正则规则
date_pattern = re.compile(r'\d{1,2}/\d{1,2}/\d{4}')
service_code_pattern = re.compile(r'\d{2}_\d{3}_\d{4}_\d_\d(?:_[A-Z])?$')

valid_services = []
for tr in rows:
    tds = tr.find_all('td')
    desc = tds[1].text.strip()
    # 定位最后一个日期的位置
    date_matches = list(date_pattern.finditer(desc))
    if not date_matches:
        continue
    last_date_end_pos = date_matches[-1].end()
    # 截取日期后内容，清理多余分隔符
    service_candidate = desc[last_date_end_pos:].strip(' -')
    # 校验服务编码，合法则保留
    if service_code_pattern.search(service_candidate):
        valid_services.append(service_candidate)

# 格式化输出结果
result_df = pd.DataFrame({'Services': valid_services})
print(result_df)

预期输出结果 #

运行后将自动过滤无效行，得到3条有效服务记录：

• Access Community, Social And Rec Activities - Standard - Weekday Daytime - TTP 9:00AM 12:00PM 04_104_0125_6_1_T
• Transport 04_590_0125_6_1
• Psychosocial Recovery Coaching - Weekday Daytime 07_101_0106_6_3

若后续遇到格式偏差的记录，微调两个正则表达式的匹配规则即可快速适配。

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