数据处理：将小数点逗号转点并修正异常行（Python场景）

问题背景 #

原始CSV数据存在格式错误：逗号同时被用作列分隔符和小数点符号，实际每行应包含6列，但直接按逗号拆分时会得到12个元素（正常行），部分行还出现行粘连、标点混用的情况（比如点和逗号错误替换），导致拆分后列数异常，无法正常处理。

原始数据示例 #

5,960464,6,65430376927058E-10,-91,7689743041992,-89,5235061645508,6,71407200919707E-05,0,00869479635485696
11,92093,5,23110578457515E-10,-92,8140640258789,-90,5685958862305,8,97218165598724E-05,0,011619072933792

目标格式 #

x,y1,y2,y3,y4,y5
5.960464,6.65430376927058E-10,-91.7689743041992,-89.5235061645508,6.71407200919707E-05,0.00869479635485696
11.92093,5.23110578457515E-10,-92.8140640258789,-90.5685958862305,8.97218165598724E-05,0.011619072933792

错误行示例 #

16433.7,08114511022965E-12.-111,498962402344.-109,253494262695.0,000461181910941812.016438.96,5.80785796046257E-12,-112.359840393066,-110.114372253418,0.000461223557779535,0.0597289529135575

正常行示例 #

17476.08,6.52454250181715E-12,-111.854499816895,-109.609031677246,0.00046727035202924,0.0605120193526557
17482.04,3.88578861020505E-12,-114.105209350586,-111.859741210938,0.000467303551829094,0.0605163187628847

解决方案 #

以下Python代码可处理正常行与错误行，核心逻辑是先修复标点错误、拆分粘连行，再合并字段还原正确小数格式：

import re

def process_single_line(line):
    # 修复错误标点：将 .- 替换为 ,-（还原负数字段的分隔）
    line = re.sub(r'(\d)\.-', r'\1,-', line)
    # 拆分粘连的行：将数字后的 .(数字) 替换为 ,(数字)（比如 0123.4567 拆为 0123,4567）
    line = re.sub(r'(\d)\.(\d{2,})', r'\1,\2', line)
    # 按逗号拆分所有片段
    segments = [s.strip() for s in line.split(',') if s.strip()]
    
    # 每两个片段合并为一个字段（逗号转小数点）
    merged_fields = []
    for i in range(0, len(segments), 2):
        part_a = segments[i]
        part_b = segments[i+1] if i+1 < len(segments) else ''
        merged_fields.append(f"{part_a}.{part_b}" if part_b else part_a)
    
    return merged_fields

# 处理文件
input_path = "input_data.txt"
output_path = "fixed_data.txt"

with open(input_path, 'r', encoding='utf-8') as infile, open(output_path, 'w', encoding='utf-8') as outfile:
    # 写入表头
    outfile.write("x,y1,y2,y3,y4,y5\n")
    
    for raw_line in infile:
        raw_line = raw_line.strip()
        if not raw_line:
            continue
        
        processed_fields = process_single_line(raw_line)
        # 拆分粘连的完整行（若字段数超过6，说明是两行合并）
        while len(processed_fields) >= 6:
            outfile.write(','.join(processed_fields[:6]) + '\n')
            processed_fields = processed_fields[6:]

代码说明 #

1. 标点修复：用正则表达式修正错误的点号，将导致字段粘连的标点还原为逗号分隔结构。
2. 字段合并：将每两个逗号分隔的片段合并为一个字段，用小数点替换逗号，还原正确的小数格式。
3. 粘连行拆分：若处理后字段数超过6，自动拆分为多行，确保每行都是完整的6列数据。

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