如何用正则表达式清理格式错误TSV并提取数据至DataFrame？

问题描述 #

需要将格式错误的TSV文件数据清理后转换为包含pid、uid、text、prediction、datetime字段的DataFrame。参考错误数据如下：

ps8trw17rlo16s  dh7r1wjixjse72  Theoretical movements expensive. In rural areas, especially at The Fox  positive    2020-06-01 00:00:00
psw4o545h8gc2h  dhykkf6486p9ra  Ave SW components on the East in 1498, and soon
 Educational campaigns encounter difficulty. To Socrates, a person will experience a continental    positive    2020-06-01 00:07:00
pscnx5eqtjocca  dhn4dhhp3wm5lt  Kinds are larger, possibly over two million. The country is the city's average. Hollywood    Fril Functional four national
 Pp. Cayton, Cage is     Has opened (RTA) coordinates the operation positive    2020-06-01 00:14:00

尝试了以下代码，但无法完整捕获text字段：

# Sample file path
input_file_path = 'logs.tsv'
output_file_path = 'cleaned_logs.txt'

# Read the malformed TSV file and write it to a text file
with open(input_file_path, 'r', encoding='utf-8') as infile, open(output_file_path, 'w', encoding='utf-8') as outfile:
    for line in infile:
        outfile.write(line)
import pandas as pd
import re

# Define regex patterns for each column
patterns = {
    'pid': r'[A-Za-z0-9]+',  # Matches a word (alphanumeric characters)
    'uid': r'[A-Za-z0-9]+',  # Matches a word (alphanumeric characters)
    'text': r'([^\t]+)\t([^\t]+)\t([^\t]+)',  # Matches the entire text (paragraph)
    'prediction': r'(positive|negative)',  # Matches 'positive' or 'negative'
    'datetime': r'[0-9]{4}-[0-9]{2}-[0-9]{2}\s[0-9]{2}:[0-9]{2}:[0-9]{2}(\.[0-9]{1,3})?'  # Matches datetime format 'YYYY-MM-DD HH:MM:SS'
}

# Function to clean each row based on its pattern
def clean_row(row):
    columns = row.split('\t')
    if len(columns) != 5:
        return [None, None, None, None, None]  # Return a list of Nones if the row is malformed
    
    cleaned_data = []
    cleaned_data.append(re.match(patterns['pid'], columns[0]).group(0) if re.match(patterns['pid'], columns[0]) else None)
    cleaned_data.append(re.match(patterns['uid'], columns[1]).group(0) if re.match(patterns['uid'], columns[1]) else None)
    cleaned_data.append(re.match(patterns['text'], columns[2]).group(0) if re.match(patterns['text'], columns[2]) else None)
    cleaned_data.append(re.match(patterns['prediction'], columns[3]).group(0) if re.match(patterns['prediction'], columns[3]) else None)
    cleaned_data.append(re.match(patterns['datetime'], columns[4]).group(0) if re.match(patterns['datetime'], columns[4]) else None)
    return cleaned_data

# Read the cleaned text file
with open(output_file_path, 'r', encoding='utf-8') as file:
    rows = file.readlines()

# Initialize a list to collect cleaned rows
cleaned_rows = []

# Process each row with its corresponding pattern
for row in rows:
    row = row.strip()  # Remove leading/trailing whitespace
    cleaned_row = clean_row(row)
    cleaned_rows.append(cleaned_row)

# Convert the cleaned rows to a DataFrame
df = pd.DataFrame(cleaned_rows, columns=['pid', 'uid', 'text', 'prediction', 'datetime'])

# Display the DataFrame
print(df)

# Optionally, save the DataFrame to a CSV file
df.to_csv('cleaned_logs.csv', index=False)

问题根源 #

1. 数据并非标准TSV（无制表符分隔），实际用多个空格分隔字段
2. text字段内容跨行，导致单条记录被拆分成多行
3. 原代码用split('\t')拆分字段完全错误，且text字段的正则逻辑完全不匹配实际内容

解决方案 #

步骤1：预处理合并跨行记录 #

先将拆分的行合并为完整记录（每个完整记录以YYYY-MM-DD HH:MM:SS格式的时间结尾）

步骤2：用整行正则提取字段 #

使用以下正则表达式匹配每条完整记录，精准捕获所有字段：

^([a-zA-Z0-9]+)\s+([a-zA-Z0-9]+)\s+(.*?)\s+(positive|negative)\s+(\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})$

各部分说明：

• ^：匹配行起始
• ([a-zA-Z0-9]+)：捕获pid（纯字母数字串）
• \s+：匹配一个或多个空格分隔符
• ([a-zA-Z0-9]+)：捕获uid（纯字母数字串）
• \s+：空格分隔符
• (.*?)：非贪婪模式捕获text（匹配所有内容，直到遇到后面的prediction字段）
• \s+：空格分隔符
• (positive|negative)：捕获预测标签（仅匹配指定值）
• \s+：空格分隔符
• (\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})：捕获日期时间
• $：匹配行结束

修改后的完整代码 #

import pandas as pd
import re

input_file_path = 'logs.tsv'

# 预处理：合并跨行的不完整记录
with open(input_file_path, 'r', encoding='utf-8') as file:
    # 过滤空行并去除每行首尾空格
    lines = [line.strip() for line in file if line.strip()]

merged_rows = []
current_row = ''
# 用于判断是否为完整记录的datetime正则
datetime_checker = re.compile(r'\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}$')

for line in lines:
    current_row += ' ' + line
    # 如果当前行包含结尾的datetime，说明是完整记录
    if datetime_checker.search(current_row):
        merged_rows.append(current_row.strip())
        current_row = ''

# 用正则提取每个字段
row_parser = re.compile(r'^([a-zA-Z0-9]+)\s+([a-zA-Z0-9]+)\s+(.*?)\s+(positive|negative)\s+(\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})$')

cleaned_data = []
for row in merged_rows:
    match = row_parser.match(row)
    if match:
        pid, uid, text, prediction, datetime_val = match.groups()
        # 清理text中的多余连续空格
        text = re.sub(r'\s+', ' ', text).strip()
        cleaned_data.append([pid, uid, text, prediction, datetime_val])
    else:
        # 匹配失败的记录返回空值
        cleaned_data.append([None, None, None, None, None])

# 转换为DataFrame
df = pd.DataFrame(cleaned_data, columns=['pid', 'uid', 'text', 'prediction', 'datetime'])

# 打印结果
print(df)
# 保存为CSV
df.to_csv('cleaned_logs.csv', index=False)

代码关键点 #

• 预处理合并跨行：通过检查datetime结尾来识别完整记录，解决text字段跨行问题
• 非贪婪正则捕获text：确保只提取到prediction字段前的内容，避免字段混淆
• 清理多余空格：合并后text可能有连续空格，用正则替换为单个空格，保证内容整洁

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