Python处理CSV与Excel文件实战解决格式兼容性与大数据读取插图

Python处理CSV与Excel文件实战：告别格式陷阱，高效读取海量数据

作为一名和数据打交道的开发者，我几乎每天都要和CSV、Excel文件“搏斗”。从简单的数据导出，到复杂的报表分析，再到机器学习的数据预处理，这些格式无处不在。然而，坑也着实不少：Excel打开CSV乱码、读取大文件内存爆炸、日期格式“神秘”转换、还有那令人头疼的合并单元格……今天，我就结合自己的实战和踩坑经验，系统性地梳理一下如何用Python优雅、高效地处理这些文件，让你告别格式兼容性的烦恼。

一、 基石工具选择：pandas, csv模块与openpyxl/xlrd

工欲善其事，必先利其器。Python生态提供了多个库，我们需要根据场景选择：

  • pandas: 数据分析的瑞士军刀，是处理结构化数据的首选。其read_csv和read_excel、to_csv和to_excel方法功能强大且高度可配置，适合绝大多数情况。
  • csv模块: Python标准库，轻量级，适合处理纯文本CSV，或需要极细粒度控制（如处理不规则分隔符）的场景。对于超大文件，它可以迭代读取，内存友好。
  • openpyxl: 专门处理.xlsx/.xlsm格式，能读写公式、样式、图表等。当需要操作Excel文件的高级特性时，它是pandas底层依赖之一。
  • xlrd (<=1.2.0) / xlrd2: 传统上用于读取.xls旧格式。注意，xlrd 2.0+版本默认不再支持.xls，这是一个经典大坑！处理.xls文件时，要么使用xlrd<=1.2.0，要么用xlrd2，或者让pandas自动处理（它内部会调用合适的引擎）。

我的实战建议：日常开发中，优先使用pandas。它的抽象层次高，代码简洁。只有在pandas无法满足的特殊需求（如处理GB级CSV而内存有限，或操作单元格样式）时，才考虑直接使用底层库。

二、 攻克CSV格式兼容性：编码、分隔符与引号

CSV看似简单，但“标准”不一，是兼容性问题重灾区。

1. 编码问题：告别乱码
中文环境下，最常遇到的是用Excel打开UTF-8编码的CSV文件显示乱码。这是因为Excel在默认情况下（尤其是中文版）期望CSV是GBK/GB2312编码。

解决方案：写入CSV时指定编码为utf-8-sig。这个编码会在文件开头添加BOM（字节顺序标记），Excel能识别并正确解码UTF-8。

import pandas as pd

# 读取时，通常用utf-8即可（pandas自动检测BOM）
df = pd.read_csv('data.csv', encoding='utf-8')

# 写入时，为了Excel友好，使用utf-8-sig
df.to_csv('data_for_excel.csv', index=False, encoding='utf-8-sig')

2. 复杂分隔符与引号
CSV并不总是逗号分隔。可能是制表符t、分号;（常见于欧洲），甚至多个空格。字段内部如果包含分隔符或换行符，需要用引号包裹。

# 读取制表符分隔的文件
df = pd.read_csv('data.tsv', sep='t')

# 读取分号分隔，且使用双引号包裹字段的文件
df = pd.read_csv('data_euro.csv', sep=';', quotechar='"')

# 一个踩坑提示：如果数据质量差，引号不匹配，可以尝试设置 `quoting=csv.QUOTE_NONE` 或 `error_bad_lines=False` (旧版) / `on_bad_lines='skip'` (pandas 1.3+)
import csv
df = pd.read_csv('dirty_data.csv', quoting=csv.QUOTE_NONE, on_bad_lines='skip')

三、 高效读取海量CSV文件：分块与筛选

当CSV文件达到GB级别时，直接用pd.read_csv()读入内存可能会撑爆。这里有两个核心策略：

1. 分块读取 (Chunking)
使用chunksize参数，将文件分割成可管理的块进行迭代处理。这是处理大文件的经典模式。

chunk_size = 100000 # 每次读取10万行
chunk_iter = pd.read_csv('huge_data.csv', chunksize=chunk_size, encoding='utf-8')

for i, chunk in enumerate(chunk_iter):
    # 对每个数据块进行处理，例如过滤、聚合
    filtered_chunk = chunk[chunk['value'] > 100]
    # 可以即时输出结果，或累积到另一个文件/数据库
    print(f"Processing chunk {i}, size: {len(filtered_chunk)}")
    # 避免内存累积，处理完即可释放

2. 仅读取所需列
如果文件有上百列，但你只关心其中几列，在读取时指定usecols参数能极大减少内存占用和处理时间。

# 只读取‘id’，‘name’，‘date’三列
df = pd.read_csv('large_file.csv', usecols=['id', 'name', 'date'])

# 或者通过列索引指定（从0开始）
df = pd.read_csv('large_file.csv', usecols=[0, 1, 4])

四、 应对Excel的“特性”：引擎、日期与合并单元格

Excel文件比CSV更复杂，因为它包含工作表、格式、公式等元信息。

1. 正确选择读取引擎
pandas的read_excel依赖底层引擎。主要选项是openpyxl（用于.xlsx）和xlrd（用于.xls）。通常pandas会根据文件扩展名自动选择，但明确指定可以避免意外。

# 读取 .xlsx 文件，明确指定引擎（通常自动）
df_xlsx = pd.read_excel('data.xlsx', engine='openpyxl')

# 读取旧的 .xls 文件，确保已安装 xlrd<=1.2.0 或 xlrd2
# 如果安装的是新版xlrd，需要： pip install xlrd==1.2.0
df_xls = pd.read_excel('old_data.xls', engine='xlrd')

2. 驯服日期/时间格式
Excel内部将日期存储为数字（从1899-12-30开始的天数），读取时pandas会尝试自动转换。但自动转换可能出错，特别是当列中混合了日期和字符串时。

# 方法一：指定某列为日期类型
df = pd.read_excel('data.xlsx', parse_dates=['order_date'])

# 方法二：完全禁止自动日期解析，全部作为字符串读入，后续手动处理
df = pd.read_excel('data.xlsx', dtype=str) # 所有列作为字符串
# 然后对特定列进行转换
df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce') # 转换失败设为NaT

3. 处理合并单元格
合并单元格是Excel报表的常见“恶习”。pandas读取时，默认只有左上角单元格有值，其他位置为NaN。我们需要进行“前向填充”。

df = pd.read_excel('report_with_merged_cells.xlsx', header=None) # 假设无表头
# 假设第一列是合并的部门信息
df[0] = df[0].ffill() # 使用前向填充补全合并单元格的值
print(df.head())

五、 实战案例：清洗并合并多个来源的销售数据

假设我们有：1个GBK编码的CSV（分号分隔），1个UTF-8带BOM的Excel，需要清洗后合并分析。

import pandas as pd
import os

def process_sales_data(csv_path, excel_path, output_path):
    """清洗合并销售数据"""
    
    # 1. 读取CSV (GBK编码，分号分隔)
    print("正在读取CSV文件...")
    df_csv = pd.read_csv(csv_path, sep=';', encoding='gbk', parse_dates=['销售日期'])
    
    # 2. 读取Excel (引擎自动选择)
    print("正在读取Excel文件...")
    df_excel = pd.read_excel(excel_path, sheet_name=0, parse_dates=['Date'])
    
    # 3. 统一列名（假设两个文件列名不同但含义相同）
    column_mapping = {'销售日期': 'date', '产品ID': 'product_id', '金额': 'amount'}
    df_csv.rename(columns=column_mapping, inplace=True)
    
    # 4. 数据清洗：去除金额为负或为空的行
    df_csv_clean = df_csv[(df_csv['amount'] > 0) & (df_csv['amount'].notna())]
    df_excel_clean = df_excel[(df_excel['amount'] > 0) & (df_excel['amount'].notna())]
    
    # 5. 合并数据
    print("正在合并数据...")
    # 确保列顺序一致
    common_columns = ['date', 'product_id', 'amount', 'region'] # 假设都有region列
    df_combined = pd.concat(
        [df_csv_clean[common_columns], df_excel_clean[common_columns]],
        ignore_index=True
    )
    
    # 6. 输出为Excel友好的CSV
    df_combined.to_csv(output_path, index=False, encoding='utf-8-sig')
    print(f"数据处理完成！结果已保存至：{output_path}")
    return df_combined

# 使用示例
if __name__ == '__main__':
    df_result = process_sales_data('sales_euro.csv', 'sales_data.xlsx', 'combined_sales.csv')

六、 性能优化与踩坑终极提示

  • 数据类型指定：在read_csv中使用dtype参数指定列类型（如{'id': 'int32', 'name': 'string'}），可以节省内存并加速读取。
  • 低内存模式：read_csv的low_memory=False参数在列数很多时可能有用，但会一次性推断数据类型。通常让pandas自动处理（分块推断）更好。
  • Excel写入性能：向Excel写入大量数据（>10万行）非常慢。考虑先输出为CSV，或使用openpyxl的只写模式(write_only=True)。对于纯数据交换，CSV通常是更好的选择。
  • 最大的坑：永远不要相信肉眼所见！用文本编辑器（如VS Code, Sublime）打开CSV检查原始编码和分隔符。用pandas读取后，立刻用df.info()和df.head()检查数据类型和前几行数据，确保解析符合预期。

希望这篇融合了实战经验和无数“踩坑”教训的指南，能帮助你更自信地用Python驾驭CSV和Excel数据。记住，理解数据本身的格式特性，再结合合适的工具和方法，才是高效数据处理的关键。 Happy Coding!

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。