Python处理CSV：移除空行空单元格及对齐Speaker列遇阻

我用以下代码处理CSV文件时，没法移除里面的空单元格和空行，同时希望让「Speaker 1 Text」列与「Speaker 2 Text」列的内容一一对齐。

当前使用的代码 #

import os
path = os.path.abspath('/Users/daneweickert/Library/CloudStorage/GoogleDrive-weickertdane99@gmail.com/My Drive/Business/gpt3/audio/MMA/sean_omalley/interviews/interview_transcript/tester.csv')

if os.path.isfile(path):
    print("File exist")
else:
    print("File does not exist")


import pandas as pd

df = pd.read_csv(path)

# 创建空列"Speaker 1 Text"和"Speaker 2 Text"
df["Speaker 1 Text"] = ""
df["Speaker 2 Text"] = ""

# 遍历每一行数据
for i in range(len(df)):
    # 检查当前行是否包含"SPEAKER 1"
    if "SPEAKER 1" in df.iloc[i][0]:
        # 检查下一行是否为空
        while pd.isna(df.iloc[i+1][0]):
            i+=1
        # 将下一行文本复制到"Speaker 1 Text"列
        df.at[i+1, "Speaker 1 Text"] = df.iloc[i+1][0]
    # 检查当前行是否包含"SPEAKER 2"
    elif "SPEAKER 2" in df.iloc[i][0]:
        # 检查下一行是否为空
        while pd.isna(df.iloc[i+1][0]):
            i+=1
        # 将下一行文本复制到"Speaker 2 Text"列
        df.at[i+1, "Speaker 2 Text"] = df.iloc[i+1][0]
        
# 删除第一列
df = df.drop(columns=df.columns[0])

# 移除两列中都有缺失值的行
df.dropna(subset=["Speaker 1 Text", "Speaker 2 Text"], thresh=1, axis=0, inplace=True)

# 保存修改后的数据到新CSV
destination = os.path.join('/Users/daneweickert/Library/CloudStorage/GoogleDrive-weickertdane99@gmail.com/My Drive/Business/gpt3/test')
df.to_csv(os.path.join(destination, "modified_data.csv"),index=False)

问题分析 #

1. 循环中手动修改i会打乱for循环的迭代逻辑，导致部分对话内容被遗漏或重复处理
2. 空值判断仅针对pd.isna，未覆盖空字符串的情况，导致空单元格清理不彻底
3. 直接在原数据行填充对话内容，无法实现Speaker1与Speaker2的对话一一对应，列对齐失效

修正后的代码 #

import os
import pandas as pd

# 文件路径
path = os.path.abspath('/Users/daneweickert/Library/CloudStorage/GoogleDrive-weickertdane99@gmail.com/My Drive/Business/gpt3/audio/MMA/sean_omalley/interviews/interview_transcript/tester.csv')
destination = os.path.join('/Users/daneweickert/Library/CloudStorage/GoogleDrive-weickertdane99@gmail.com/My Drive/Business/gpt3/test', "modified_data.csv")

if not os.path.isfile(path):
    print("File does not exist")
    exit()

# 读取CSV，自定义列名方便处理
df = pd.read_csv(path, header=None, names=["Content"])

# 初始化对话列表
speaker1_lines = []
speaker2_lines = []
current_speaker = None

# 遍历所有行，提取有效对话内容
for idx, row in df.iterrows():
    content = str(row["Content"]).strip()
    if not content:
        continue  # 直接跳过空行/空单元格
    
    # 判断当前行是否为Speaker标记
    if "SPEAKER 1" in content:
        current_speaker = 1
    elif "SPEAKER 2" in content:
        current_speaker = 2
    elif current_speaker is not None:
        # 将对话内容添加到对应列表
        if current_speaker == 1:
            speaker1_lines.append(content)
        else:
            speaker2_lines.append(content)

# 对齐两个对话列表，长度不足的补空字符串
max_len = max(len(speaker1_lines), len(speaker2_lines))
speaker1_lines += [""] * (max_len - len(speaker1_lines))
speaker2_lines += [""] * (max_len - len(speaker2_lines))

# 创建结果DataFrame
result_df = pd.DataFrame({
    "Speaker 1 Text": speaker1_lines,
    "Speaker 2 Text": speaker2_lines
})

# 移除两行都为空的无效行
result_df = result_df[(result_df["Speaker 1 Text"] != "") | (result_df["Speaker 2 Text"] != "")]

# 保存处理后的文件
result_df.to_csv(destination, index=False)
print("处理完成，文件已保存")

修正说明 #

1. 先遍历所有行，分别提取Speaker1和Speaker2的对话内容到独立列表，确保对话顺序正确
2. 统一处理空字符串和空行，直接跳过无内容的行
3. 通过补空的方式对齐两个对话列表，实现列对齐效果
4. 最后过滤掉两行都为空的无效行，彻底清理空内容

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