使用正则表达式提取文本表格：输出异常的修正请求

样本字典数据 #

dic = {
    'ID': ':  ID\nRespondent ID\nBased upon 3,302 valid cases out of 3,302 total cases.\n•Mean: 54361.66\n•Minimum: 10005.00\n•Maximum:99992.00\n•Standard Deviation: 25723.74\nLocation: 1-5 (width: 5; decimal: 0)\nVariable Type:  numeric \n',
    'VIS': ':  visit\nSwan study visit number\nValue Label Unweighted\nFrequency%\n00- 3302 100.0 %\n Total 3,302 100%\nBased upon 3,302 valid cases out of 3,302 total cases.\nLocation: 6-7 (width: 2; decimal: 0)\nVariable Type: character \n',
    'INT': ':  day\nDate form completed\nValue Label Unweighted\nFrequency%\n0- 3302 100.0 %\n Total 3,302 100%\nBased upon3,302 valid cases out of 3,302 total cases.\n•Mean: 0.00\n•Median:0.00\n•Mode: 0.00\n•Minimum: 0.00\n•Maximum: 0.00\n•Standard Deviation: 0.00\nLocation: 8-8 (width: 1; decimal: 0)\nVariable Type:numeric \n',
    'AG0': ': Age in months\n- 2 -Value Label Unweighted\nFrequency%\n42- 367 11.1 %\n43- 421 12.7 %\n44- 416 12.6 %\n45- 389 11.8 %\n46- 400 12.1 %\n47- 392 11.9 %\n48- 299 9.1 %\n49-255 7.7 %\n50- 168 5.1 %\n51- 115 3.5 %\n52- 71 2.2 %\n53- 40.1 %\n Missing Data   \n.- 50.2 %\n Total 3,302 100%\nBased upon 3,297 valid cases out of 3,302 total cases.\n•Mean: 45.85\n•Median: 46.00\n•Mode:43.00\n•Minimum: 42.00\n•Maximum: 53.00\n•Standard Deviation: 2.69\nLocation: 9-10 (width: 2; decimal: 0)\nVariable Type:  numeric \n',
    'PRE0': ': Currently preant?\nAre you currently pnant?\nValue Label Unweighted\nFrequency%\n1No 3295 99.8 %\n2Yes 00.0 %\n Missing Data   \n-9Missing 70.2 %\n Total 3,302 100%\nBased upon 3,295 valid cases out of 3,302 total cases.\n•Minimum: 1.00\n•Maximum:1.00\nLocation: 11-12 (width: 2; decimal: 0)\nVariable Type:  numeric \n- 3 -(Range of) Missing Values:  -9 , -8 , -7 , -1\n',
}

第一次尝试代码与问题 #

代码 #

import re
import pandas as pd

data = {}
for key, value in dic.items():
    regex = r'(Value\s+Label\s+Unweighted\s+Frequency%\s+(?:Missing\s+Data\s+)?[\s\S]+?)\n(?=\S)'
    match = re.search(regex, value, flags=re.DOTALL)
    if match:
        rows = [re.split(r"\s+", row.strip()) for row in match.group(1).strip().split("\n")]
        df = pd.DataFrame(rows, columns=["Value", "Label", "Unweighted"," Frequency%"])
        df["Variable"] = key
        data[key] = df

data["VISIT"]

当前输出 #

Value       Label   Unweighted  Frequency%  Variable
0   Value       Label   Unweighted  None        VISIT
1   Frequency%  None    None        None        VISIT
2   00-         3302    100.0       %           VISIT
3   Total      3,302    100%        None        VISIT

问题 #

• 表头被误当作数据行导入
• 带空格的百分比值被错误拆分

第二次尝试代码与问题 #

代码 #

import re
import pandas as pd

# Define the regular expression pattern to match the table rows
pattern = r'(?P<Value>[\w.-]+)\s+(?P<Label>.+?)\s+(?P<Unweighted_Frequency>.+%)'

# Initialize an empty list to store the rows
rows = []

# Loop through the dictionary
for key, text in dic.items():
    # Find all the matches of the pattern in the text
    matches = re.findall(pattern, text)
    if matches:
        # Add the matches to the rows list with the key as the first column
        rows.extend([(key,) + match for match in matches])

# Create a pandas DataFrame from the rows list with the desired column names
df = pd.DataFrame(rows, columns=['Variable', 'Value', 'Label', 'Unweighted Frequency%'])

当前问题 #

1. 列错位：Unweighted Frequency%列内容归属错误，Label列内容被错误放到Unweighted Frequency列
2. 值拆分错误：类似1No的内容未拆分为Value列的1和Label列的No
3. 缺失行：部分数据行未被匹配到

样本错误输出 #

7           AG0     42-     367      11.1 %
8           AG0     43-     421      12.7 %
9           AG0     44-     416      12.6 %
10          AG0     45-     389      11.8 %
18          AG0 Missing Data    .-   50.2 %
19          AG0     Total   3,302    100%
20          PRE0    Value   Label   Unweighted  Frequency%
21          PRE0    1No     3295     99.8 %
22          PRE0    Missing Data    -9Missing 70.2 %
23          PRE0    Total   3,302    100%

期望输出示例 #

result = {
    'ID': 'no_table',
    'VIS': pd.DataFrame([
        ['00', '-', 3302, '100.0 %'],
        ['Total', '', 3302, '100%']
    ], columns=['Value', 'Label', 'Unweighted', 'Frequency%']),
    'INT': pd.DataFrame([
        ['0', '-', 3302, '100.0 %'],
        ['Total', '', 3302, '100%']
    ], columns=['Value', 'Label', 'Unweighted', 'Frequency%']),
    # 其他变量的表格省略
}

修正后的代码与说明 #

import re
import pandas as pd

def extract_table(text):
    # 定位表格核心区域：从表头到Total行
    table_header_match = re.search(r'Value\s+Label\s+Unweighted\s+Frequency%', text)
    total_row_match = re.search(r'Total\s+\d+,\d+\s+\d+%', text)
    if not table_header_match or not total_row_match:
        return None
    
    start_idx = table_header_match.end()
    end_idx = total_row_match.end()
    table_content = text[start_idx:end_idx].strip() + "\n" + total_row_match.group()
    
    rows = []
    
    # 匹配普通数据行（含1No、2Yes这类组合值）
    data_pattern = r'((?:\d+[-]|[\.-]|\d+(?:Yes|No)))\s+(\d+(?:,\d+)?)\s+(\d+\.\d+ %|\d+%)'
    for match in re.finditer(data_pattern, table_content):
        value_raw, unweighted, freq = match.groups()
        # 拆分1No、2Yes这类值
        combo_match = re.match(r'(\d+)(Yes|No)', value_raw)
        if combo_match:
            value = combo_match.group(1)
            label = combo_match.group(2)
        else:
            # 拆分带-的值，如42-
            value, label = value_raw.split('-', 1) if '-' in value_raw else (value_raw, '')
        rows.append([value.strip(), label.strip(), int(unweighted.replace(',', '')), freq.strip()])
    
    # 匹配缺失数据行
    missing_pattern = r'Missing Data\s+([\.-]|\-\d+Missing)\s+(\d+\.\d+ %)'
    for match in re.finditer(missing_pattern, table_content):
        value, freq = match.groups()
        rows.append([value.strip(), 'Missing Data', '', freq.strip()])
    
    # 匹配Total行
    total_pattern = r'Total\s+(\d+,\d+)\s+(\d+%)'
    for match in re.finditer(total_pattern, table_content):
        unweighted, freq = match.groups()
        rows.append(['Total', '', int(unweighted.replace(',', '')), freq.strip()])
    
    return pd.DataFrame(rows, columns=['Value', 'Label', 'Unweighted', 'Frequency%'])

# 遍历字典提取所有表格
result = {}
for key, text in dic.items():
    df = extract_table(text)
    result[key] = df if df is not None else 'no_table'

# 查看VIS的提取结果
print(result['VIS'])

代码说明 #

1. 精准区域定位：通过表头和Total行锁定表格范围，避免无关内容干扰
2. 多模式匹配：分别处理普通数据行、缺失数据行和总计行，解决列错位问题
3. 组合值拆分：专门处理1No这类格式，拆分出对应的Value和Label
4. 数据清洗：转换带逗号的数字为整数，保证数据格式统一

验证输出（以VIS为例） #

Value Label  Unweighted Frequency%
0     00          3302    100.0 %
1  Total          3302        100%

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