如何清理文本文件内容？Python CSV输出格式修正求助

问题说明 #

需要将给定的CSV数据按ID分组，生成特定格式的TXT文件，但现有Python代码输出格式混乱，不符合预期。

原始CSV数据 #

"ID","OTHER_FIELDS_2"
"87","25 R160  22  13  E"
"87","25 R165  22  08  E"
"77",""
"18","20 BA-10  12  06  2  30  S"
"18","20 BA-20  12  06  2  30  S"
"88","20 TH-42  02  02  5  30  MT"
"66","20 AD-38  12  06  B"
"66","20 AD-38  30  07  B"
"70","20 OL-45  19  11  B"
"70","20 EM-45  19  08  B"

当前错误输出 #

18,"
","20 BA-10  12  06  2  30  S
20 BA-20  12  06  2  30  S",""
",**********
66,"
","20 AD-38  12  06  B
20 AD-38  30  07  B",""
",**********
70,"
","20 OL-45  19  11  B
20 EM-45  19  08  B",""
",**********
77,"
",,,"
",**********
87,"
","25 R160  22  13  E
25 R165  22  08  E",""
",**********
88,"
",20 TH-42  02  02  5  30  MT,"
",**********

期望输出格式 #

18
20 BA-10  12  06  2  30  S
20 BA-20  12  06  2  30  S
**********
66
20 AD-38  12  06  B
20 AD-38  30  07  B
**********
70
20 OL-45  19  11  B
20 EM-45  19  08  B
**********
77
**********
87
25 R160  22  13  E
25 R165  22  08  E
**********
88
20 TH-42  02  02  5  30  MT
**********

原代码 #

import pandas as pd
import csv

df = pd.read_csv('idDetails.csv')
data_rows = [] 

group_column = 'ID'
selected_columns = ['OTHER_FIELDS_2'] 
grouped_data = df.groupby(group_column)[selected_columns]

for group_name, group_df in grouped_data:
    #print(f"{group_name}")
    other_data = group_df.to_string(header=False,index=False)
    other_data_a = group_df.fillna('').dropna(axis = 0, how = 'all') 
    other_data_b = other_data_a.to_string(header=False,index=False)
    #print(other_data_b) 
    other_data_c = '**********'
    #print(other_data_c)
    data_rows.append([group_name, '\n', other_data_b, '\n', other_data_c])  
dfo = pd.DataFrame(data_rows)
dfo.to_csv('idDetailsoutput.txt', header=False, index=False)

解决方案 #

原代码问题在于使用pandas.DataFrame.to_csv处理自定义格式，该方法会自动添加分隔符、引号，导致格式混乱。应直接通过文件操作拼接内容，完全控制输出格式：

import pandas as pd

df = pd.read_csv('idDetails.csv')
# 按ID分组，sort=False保持原始数据中ID的出现顺序
grouped_data = df.groupby('ID', sort=False)

# 直接写入文件，精确控制每行内容
with open('idDetailsoutput.txt', 'w', encoding='utf-8') as output_file:
    for group_id, group_df in grouped_data:
        # 写入当前分组的ID
        output_file.write(f"{group_id}\n")
        # 遍历分组内的OTHER_FIELDS_2，过滤空值和空字符串后写入
        for value in group_df['OTHER_FIELDS_2'].dropna().tolist():
            if value.strip():
                output_file.write(f"{value}\n")
        # 写入分组分隔线
        output_file.write("**********\n")

代码说明 #

1. 保持顺序：groupby('ID', sort=False)确保输出顺序与原始CSV中ID首次出现的顺序一致，避免默认排序打乱顺序。
2. 直接文件写入：通过文件句柄逐行写入，完全规避to_csv带来的格式化干扰，确保输出格式符合预期。
3. 过滤空值：通过dropna()和strip()过滤空值与空字符串，保证像ID=77这类无有效内容的分组仅输出ID和分隔线。

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