Bill Chen
Coder & Composer.
Bill Chen's Blog
使用 Pandas 的 to_excel() 方法来将多个 csv 文件合并到一个 xlsx 的不同 sheets 内

这几天在用 Python3 研究一个爬虫,最后一个需求是把爬下来的20+个csv文件整合到一个excel表里的不同sheets。

https://billc.io/wp-content/uploads/2019/04/image-3-1600x1175.png

初版的核心代码如下:

while year <= 2018:
    csvPath = sys.path[0] + '/result/%d.csv' % year
    excelPath = sys.path[0] + '/result.xlsx'
    csvReader = pandas.read_csv(csvPath, encoding='utf_8_sig')
    excelWriter = pandas.ExcelWriter(excelPath)
    print("正在将 %d 年的 %d 条数据转换为 xlsx..." % (year, countThis))
    csvReader.to_excel(excelWriter, sheet_name=str(year))
    year = year + 1

奇怪的是使用这个方法,每次to_excel之后,result.xlsx中都只会存储一年的数据,只会存在一个sheet,之前的所有数据都会被覆盖。

通过查询官方文档(pandas.DataFrame.to_excel)和一个github上跨越了5年的issue(Allow ExcelWriter() to add sheets to existing workbook)得知pandas库的ExcelWriter缺失了一个mode='a'的append模式,所以在这种情况下每次to_excel()都会直接新建一个文件写入而无视之前的数据。

解决方案是使用openpyxl engine来打开ExcelWriter,用openpyxl的load_workbook方法将之前已经存在的数据加载进ExcelWriter.book里。修改后的核心代码如下:

# 依赖 openpyxl 库
from openpyxl import load_workbook

while year <= 2018:
    csvPath = sys.path[0] + '/result/%d.csv' % year
    excelPath = sys.path[0] + '/result.xlsx'
    csvReader = pandas.read_csv(csvPath, encoding='utf_8_sig')
    # 增加 engine='openpyxl' 一栏
    excelWriter = pandas.ExcelWriter(excelPath, engine='openpyxl')
    # 使用 openpyxl 来把现有数据传递给excelWriter,使其在写入的时候保留原本数据
    book = load_workbook(excelPath)
    excelWriter.book = book

    print("正在将 %d 年的 %d 条数据转换为 xlsx..." % (year, countThis))
    csvReader.to_excel(excelWriter, sheet_name=str(year))
    excelWriter.save()

如此存储的excel文件里就会有多个sheets了,每个sheets里都存储着一个csv里的全部数据。

https://billc.io/wp-content/uploads/2019/04/image-5-1600x204.png

需要注意的是这样做的效率非常低,因为这并不是真正的追加模式,而是在每一次创建ExcelWriter对象之后,先将现有的数据全部传入ExcelWriter,再将新的数据连同旧的数据一同写入一个新的文件并覆盖。这就导致程序作了许多重复而无用的工作,所以我在处理这个任务的时候。最后的几个10+m的csv文件的平均耗时都在300s以上,如果还有后续任务的话,这个数字会一直增长下去。得到一个80m的xlsx总表耗费了接近一个小时的时间,这对于一些更大的任务来说是难以接受的。所以如果你需要处理的任务比较巨大,你可以脱离pandas库而使用xlrd和xlwt里的方法,会使运行效率优雅不少。(也许直接使用excel的vba宏也是个不错的选择?)

0
Last Updated:
本文链接:https://billc.io/2019/04/pandas-append-excel/
若无特殊声明,站点所有文章均遵循 CC-BY-NC-SA 4.0 协议。
首页      理科思维      使用 Pandas 的 to_excel() 方法来将多个 csv 文件合并到一个 xlsx 的不同 sheets 内

发表评论

textsms
account_circle
email

Bill Chen's Blog

使用 Pandas 的 to_excel() 方法来将多个 csv 文件合并到一个 xlsx 的不同 sheets 内
使用 Pandas 来在 xlsx 中追加 sheets 而不覆盖已有数据。
扫描二维码继续阅读
2019-04-11
Tags
文章归档
近期评论
仪表盘
  • 2
  • 34
  • 1,982
  • 117,409
  • 30
  • 2019年12月3日
关注

输入您的电子邮件地址订阅此博客,并通过电子邮件接收博客更新通知。