求助：Python CSV格式化脚本输出空文件问题排查与修复

我编写了一个将input.csv转换为格式规范的output.csv的Python脚本，但目前仅输出空文件，恳请协助排查修复。以下是详细的输入输出格式规则：

输入规则 #

• 输入CSV的每一行对应输出CSV一行中的某一列
• 存在仅含引号的空行
• 每个数据集对应多行输入，部分存在多余行或缺失行

输出规则 #

• 输出CSV每行需包含7列
• 移除输入中仅含空数据（如双引号）的行
• 以Investor focuses:为锚点确定数据集的最后一列
• 不含@符号的数据集，在第4列插入Email:NA
• 不含Phone:的数据集，在第5列插入Phone:NA
• 移除仅含Email:或Placeholder image的行
• 每个单元格内容用引号包裹，列间用逗号分隔
• 固定输入文件为input.csv，输出文件为output.csv

输入数据示例 #

"",
"Username 001",
"",
"Partner",
"London, United Kingdom",
"Emails:",
"",
"user@example.eu",
"Phone:00-000-000-000",
"Investor types: Private Equity Firm, Venture Capital",
"Investment focuses: Financial Services, Artificial Intelligence, Machine Learning, Software, Advice, Professional Services, Semiconductor, Agriculture, AgTech, Biotechnology, Medical, Medical Device, Veterinary, CRM, Information Technology, E-Commerce, Internet, Robotics, Security, Public Safety, Service Industry Show past investments",
"Placeholder image",
"",
"Username 002",
"",
"Co-Founder and General Partner",
"London, United Kingdom",
"Emails:",
"",
"Phone:00-00-0000-0000",
"Investor type: Micro VC",
"Investment focuses: Semiconductor, Software, SaaS, Health Care, CleanTech, Data Visualization, Hardware, Internet of Things, Smart Cities, Financial Services, FinTech, CMS, IaaS, Information Technology, Internet, PaaS, Web Design, Web Development, Web Hosting, Machine Learning, Robotics, Artificial Intelligence, Computer, Software Engineering Show past investments",
"",
"Username 003",
"",
"Chief Executive",
"Cardiff, United Kingdom",
"Emails:",
"",
"user@example.wales",
"Investor types: Government Office, Micro VC",
"Investment focuses: Cosmetics, Men's, Sensor, Building Material, Financial Services, FinTech, Marketing, Manufacturing, Artificial Intelligence, Biotechnology, Health Care, Life Science, Software, Blockchain, Enterprise Applications, Industrial Automation, Industrial Manufacturing, Internet, SaaS, Supply Chain Management, Information Technology, Wellness Show past investments",
"",
"Username 004",
"",
"Partner",
"London, United Kingdom",
"Emails:",
"",
"user@example.com",
"Phone:00-00-0000-0000",
"Investor type: Private Equity Firm",
"Investment focuses: Construction, Aerospace, Financial Services, Hospital, Pharmaceutical, Industrial, Machinery Manufacturing, Coffee, Food and Beverage Show past investments",

期望输出示例 #

"Username 001","Partner","London, United Kingdom","user@example.eu","Phone:00-000-000-000","Investor types: Private Equity Firm, Venture Capital","Investment focuses: Financial Services, Artificial Intelligence, Machine Learning, Software, Advice, Professional Services, Semiconductor, Agriculture, AgTech, Biotechnology, Medical, Medical Device, Veterinary, CRM, Information Technology, E-Commerce, Internet, Robotics, Security, Public Safety, Service Industry Show past investments"
"Username 002","Co-Founder and General Partner","London, United Kingdom","Email:NA","Phone:00-00-0000-0000","Investor type: Micro VC","Investment focuses: Semiconductor, Software, SaaS, Health Care, CleanTech, Data Visualization, Hardware, Internet of Things, Smart Cities, Financial Services, FinTech, CMS, IaaS, Information Technology, Internet, PaaS, Web Design, Web Development, Web Hosting, Machine Learning, Robotics, Artificial Intelligence, Computer, Software Engineering Show past investments"
"Username 003","Chief Executive","Cardiff, United Kingdom","user@example.wales","Phone:NA","Investor types: Government Office, Micro VC","Investment focuses: Cosmetics, Men's, Sensor, Building Material, Financial Services, FinTech, Marketing, Manufacturing, Artificial Intelligence, Biotechnology, Health Care, Life Science, Software, Blockchain, Enterprise Applications, Industrial Automation, Industrial Manufacturing, Internet, SaaS, Supply Chain Management, Information Technology, Wellness Show past investments"
"Username 004","Partner","London, United Kingdom","user@example.com","Phone:00-00-0000-0000","Investor type: Private Equity Firm","Investment focuses: Construction, Aerospace, Financial Services, Hospital, Pharmaceutical, Industrial, Machinery Manufacturing, Coffee, Food and Beverage Show past investments"

当前脚本 #

import csv

# 打开输入文件并创建reader对象
with open('input.csv', newline='') as f_input:
    reader = csv.reader(f_input)

    # 打开输出文件并创建writer对象
    with open('output.csv', 'w', newline='') as f_output:
        writer = csv.writer(f_output)

        # 初始化变量存储当前行数据
        current_name = ""
        current_title = ""
        current_location = ""
        current_email = ""
        current_phone = ""
        current_investor_types = ""
        current_investment_focuses = ""

        # 遍历输入文件的每一行
        for row_number, row in enumerate(reader):

            # 跳过仅含空数据的行
            if not any(row):
                continue

            # 检查每行的列数是否符合要求
            if len(row) != 1 and len(row) != 3 and len(row) != 4 and len(row) != 7:
                print(f"Error: Row {row_number} has {len(row)} columns. Skipping row.")
                continue

            # 提取行数据并存储到对应变量
            if row[0] != "":
                current_name = row[0]
            elif row[2] != "":
                current_title = row[2]
            elif row[3] != "":
                current_location = row[3]
            elif "@" in row[4]:
                current_email = row[4]
            elif "Phone:" in row[4]:
                current_phone = row[4]
            elif row[5] == "Investor types:":
                current_investor_types = row[6]
            elif row[5] == "Investment focuses:":
                current_investment_focuses = row[6]

                # 检查当前数据集是否包含必要数据
                if current_name != "" and current_title != "" and current_location != "":
                    # 若无邮箱则添加"Email:NA"
                    if current_email == "":
                        current_email = "Email:NA"

                    # 若无电话则添加"Phone:NA"
                    if current_phone == "":
                        current_phone = "Phone:NA"

                    # 移除仅含"Email:"或"Placeholder image"的行
                    if current_email == "Email:" or current_email == "Placeholder image":
                        current_email = ""

                    # 将当前行数据写入输出文件
                    writer.writerow([current_name, current_title, current_location, current_email, current_phone, current_investor_types, current_investment_focuses])

                    # 重置变量以存储下一个数据集
                    current_name = ""
                    current_title = ""
                    current_location = ""
                    current_email = ""
                    current_phone = ""
                    current_investor_types = ""
                    current_investment_focuses = ""

        # 检查输入文件最后一行是否包含必要数据
        if current_name != "" and current_title != "" and current_location != "":
            # 若无邮箱则添加"Email:NA"
            if current_email == "":
                current_email = "Email:NA"

            # 若无电话则添加"Phone:NA"
            if current_phone == "":
                current_phone = "Phone:NA"

            # 移除仅含"Email:"或"Placeholder image"的行
            if current_email == "Email:" or current_email:
                current_email = ""

            # 将最后一行数据写入输出文件
            writer.writerow([current_name, current_title, current_location, current_email, current_phone, current_investor_types, current_investment_focuses])

问题分析与修复脚本 #

核心问题 #

1. 列数判断错误：输入CSV每行仅1列，但原脚本错误判断列数范围，导致所有有效行被跳过
2. 字段提取逻辑错误：原脚本试图访问row[2]/row[3]/row[4]等不存在的索引，完全不符合输入格式
3. 锚点识别错误：原脚本错误判断Investor types:的位置，实际该内容是整行文本而非某一列值

修复后的脚本 #

import csv

def process_csv():
    # 读取输入并过滤无效行
    with open('input.csv', newline='', encoding='utf-8') as f_input:
        reader = csv.reader(f_input)
        # 提取每行内容，过滤空行和仅含引号的行
        rows = [row[0].strip() for row in reader if row and row[0].strip() not in ('', '"')]
    
    # 写入输出文件
    with open('output.csv', 'w', newline='', encoding='utf-8') as f_output:
        # 自动给所有单元格加引号，符合输出规则
        writer = csv.writer(f_output, quoting=csv.QUOTE_ALL)
        # 用字典存储当前数据集，结构更清晰
        current_record = {
            'name': '',
            'title': '',
            'location': '',
            'email': '',
            'phone': '',
            'investor_types': '',
            'investment_focuses': ''
        }
        in_email_section = False  # 标记是否处于邮箱提取阶段
        
        for content in rows:
            # 跳过无效行
            if content in ('Email:', 'Placeholder image'):
                continue
            
            # 触发数据集结束：找到锚点则写入并重置
            if content.startswith('Investment focuses:'):
                current_record['investment_focuses'] = content
                # 填充缺失字段
                if not current_record['email']:
                    current_record['email'] = 'Email:NA'
                if not current_record['phone']:
                    current_record['phone'] = 'Phone:NA'
                # 写入当前数据集
                writer.writerow([
                    current_record['name'],
                    current_record['title'],
                    current_record['location'],
                    current_record['email'],
                    current_record['phone'],
                    current_record['investor_types'],
                    current_record['investment_focuses']
                ])
                # 重置记录，准备下一个数据集
                current_record = {k: '' for k in current_record}
                in_email_section = False
                continue
            
            # 标记进入邮箱提取阶段
            if content == 'Emails:':
                in_email_section = True
                continue
            
            # 提取邮箱内容
            if in_email_section and '@' in content:
                current_record['email'] = content
                in_email_section = False
                continue
            
            # 提取电话内容
            if content.startswith('Phone:'):
                current_record['phone'] = content
                continue
            
            # 提取投资者类型（兼容两种写法）
            if content.startswith(('Investor types:', 'Investor type:')):
                current_record['investor_types'] = content
                continue
            
            # 按顺序填充基本信息：用户名→职位→地点
            if not current_record['name']:
                current_record['name'] = content
            elif not current_record['title']:
                current_record['title'] = content
            elif not current_record['location']:
                current_record['location'] = content

if __name__ == '__main__':
    process_csv()

修复说明 #

• 修正输入读取逻辑：直接提取每行唯一列的内容，过滤无效行
• 用字典管理当前数据集，更易维护
• 增加邮箱阶段标记，正确识别邮箱内容
• 按输入顺序自动填充用户名、职位、地点
• 兼容Investor types:和Investor type:两种写法
• 使用csv.QUOTE_ALL自动给单元格加引号，符合输出格式要求

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