代码无报错但生成的Excel无数据，多CSV读取问题求助

问题描述 #

需要读取多个列格式一致的CSV文件，运行下方Python代码后，生成的Excel文件已创建“Duplicates”“DOB”“Address1”“Gender”“Association”五个标签页，但所有标签页均无数据，且代码执行无报错。

原代码：

import pandas as pd

# Read csv file using Pandas
data = pd.read_csv("data.csv", engine="python", header=None)

# Add column headers
df.columns = ["EmpID", "SSN", "Status", "LastName", "FirstName", "Middle", "DOB", "Address1", "Address2", "City", "State", "Zip", "Phone", "Email", "Gender", "GroupStatus", "Salary", "PayPeriods", "DOH", "MedEligibility", "Association", "Company Number", "TermDate", "TermReasonCode", "LOADate", "LOAReasonCode", "SalaryDateChange", "HourlyRate", "EIN", "SupplementalEligDate", "RecordTerminator"]

# Process dataframes for duplicates and missing data
df0 = df[df.duplicated(subset=["EmpID", "SSN", "LastName", "FirstName", "DOB"])]
df1 = df.loc[df["DOB"].isna(), ["EmpID", "SSN", "LastName", "FirstName", "DOB"]]
df2 = df.loc[df["Address1"].isna(), ["EmpID", "SSN", "LastName", "FirstName", "Address1"]]
df3 = df.loc[df["Gender"].isna(), ["EmpID", "SSN", "LastName", "FirstName", "Gender"]]
df4 = df.loc[df["Association"].isna(), ["EmpID", "SSN", "LastName", "FirstName", "Association"]]

# Print dataframe output to separate tabs in one Excel file
with pd.ExcelWriter("output.xlsx") as writer:
    df0.to_excel(writer, sheet_name="Duplicates", engine='xlsxwriter', index=True)
    df1.to_excel(writer, sheet_name="DOB", index=False)`
    df2.to_excel(writer, sheet_name="Address1", index=False)
    df3.to_excel(writer, sheet_name="Gender", index=False) 
    df4.to_excel(writer, sheet_name="Association", index=False)

问题根源 #

1. 变量名不匹配：读取CSV后将数据赋值给了data，但后续所有数据处理都使用未定义的df变量，相当于基于空DataFrame操作，自然没有数据输出。
2. 语法错误：df1.to_excel行末尾多了一个多余的反引号，正常会触发语法报错，属于代码粘贴失误。
3. 未实现多文件读取：原代码仅读取了单个data.csv，未完成“读取多个CSV文件”的需求。

修复后的代码 #

import pandas as pd
import glob

# 读取当前目录下所有CSV文件（可修改路径匹配规则）
csv_files = glob.glob("*.csv")
# 合并所有CSV为单个DataFrame（假设所有文件列格式完全一致）
df = pd.concat([pd.read_csv(file, engine="python", header=None) for file in csv_files], ignore_index=True)

# 添加列标题
df.columns = ["EmpID", "SSN", "Status", "LastName", "FirstName", "Middle", "DOB", "Address1", "Address2", "City", "State", "Zip", "Phone", "Email", "Gender", "GroupStatus", "Salary", "PayPeriods", "DOH", "MedEligibility", "Association", "Company Number", "TermDate", "TermReasonCode", "LOADate", "LOAReasonCode", "SalaryDateChange", "HourlyRate", "EIN", "SupplementalEligDate", "RecordTerminator"]

# 可选：校验读取到的数据量
print(f"共读取到 {len(df)} 条数据")

# 处理重复值和缺失数据
df0 = df[df.duplicated(subset=["EmpID", "SSN", "LastName", "FirstName", "DOB"])]
df1 = df.loc[df["DOB"].isna(), ["EmpID", "SSN", "LastName", "FirstName", "DOB"]]
df2 = df.loc[df["Address1"].isna(), ["EmpID", "SSN", "LastName", "FirstName", "Address1"]]
df3 = df.loc[df["Gender"].isna(), ["EmpID", "SSN", "LastName", "FirstName", "Gender"]]
df4 = df.loc[df["Association"].isna(), ["EmpID", "SSN", "LastName", "FirstName", "Association"]]

# 写入Excel多标签页
with pd.ExcelWriter("output.xlsx") as writer:
    df0.to_excel(writer, sheet_name="Duplicates", engine='xlsxwriter', index=True)
    df1.to_excel(writer, sheet_name="DOB", index=False)
    df2.to_excel(writer, sheet_name="Address1", index=False)
    df3.to_excel(writer, sheet_name="Gender", index=False) 
    df4.to_excel(writer, sheet_name="Association", index=False)

额外说明 #

• 若CSV文件不在当前工作目录，修改glob.glob的路径参数（例如"path/to/your/files/*.csv"）
• 如果CSV自带表头，删除header=None参数；无表头则保留该参数避免第一行被识别为表头
• 可添加异常处理逻辑，防止单个损坏的CSV文件导致程序中断

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