V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
推荐学习书目
Learn Python the Hard Way
Python Sites
PyPI - Python Package Index
http://diveintopython.org/toc/index.html
Pocoo
值得关注的项目
PyPy
Celery
Jinja2
Read the Docs
gevent
pyenv
virtualenv
Stackless Python
Beautiful Soup
结巴中文分词
Green Unicorn
Sentry
Shovel
Pyflakes
pytest
Python 编程
pep8 Checker
Styles
PEP 8
Google Python Style Guide
Code Style from The Hitchhiker's Guide
joson1205
V2EX  ›  Python

Python 读取 Excel 超大数据文件问题

  •  
  •   joson1205 · 2019-08-09 10:10:31 +08:00 · 9298 次点击
    这是一个创建于 1932 天前的主题,其中的信息可能已经有所发展或是发生改变。
    一个交易记录数据 xlsx 文件,存在两个 Sheet,行数分别是 2w,15w,列数 45+左右,需要自动化生成一份报表,报表的数据由这两个 Sheet 交叉运算得出,目前做法是 xlrd 读取到内存然后 xlsxwriter 写入一个新的文件中.
    因为某些原因,该交易记录的数据不能存入数据库里面,暂时只能以表格形式存放,每次读取的时候都很费时间,考虑分块读取,好像库并不支持,pandas 类的 chunksize,基本都是针对 CSV 文件,有没有更好的办法解决读取速度慢的问题??
    36 条回复    2021-04-29 15:09:06 +08:00
    sikariba
        1
    sikariba  
       2019-08-09 10:27:56 +08:00
    xlsx 的 2 个 sheet 转成 2 个 csv ? xlsx 是二进制格式,没办法分块读
    joson1205
        2
    joson1205  
    OP
       2019-08-09 10:30:47 +08:00
    @sikariba 我转 CSV 过程中数据都要过一遍内存吧
    The1D
        3
    The1D  
       2019-08-09 10:38:10 +08:00
    解压之后读 xml ?
    misaka19000
        4
    misaka19000  
       2019-08-09 10:38:32 +08:00
    为什么不能保存到其它地方
    1462326016
        5
    1462326016  
       2019-08-09 10:45:58 +08:00
    试试 openpyxl ?
    vest8
        6
    vest8  
       2019-08-09 10:51:50 +08:00
    pywin32 试下
    joson1205
        7
    joson1205  
    OP
       2019-08-09 10:56:59 +08:00
    @1462326016 我认为 xlrd 单纯在读取速度上是比 openpyxl 快不少的.
    @vest8 pywin32 没有用过,我查查资料,谢谢
    PEIENYKYK
        8
    PEIENYKYK  
       2019-08-09 11:15:53 +08:00
    插眼,看解决办法。
    wqzjk393
        9
    wqzjk393  
       2019-08-09 11:19:15 +08:00
    df = pd.read_excel(sheetname=None)
    df1 = df[‘ Sheet1 ’]
    df2 = df[‘ Sheet2 ’]
    1 分钟之内就能读完了,不慢了吧。。
    joson1205
        10
    joson1205  
    OP
       2019-08-09 11:26:20 +08:00
    @wqzjk393 试过了,比较慢,我正常 xlrd 读取要 85 秒,pandas 将近 100 秒
    joson1205
        11
    joson1205  
    OP
       2019-08-09 11:35:33 +08:00
    @wqzjk393 纠正一下,刚检查测试代码的时候发现有点异常,重新对比了一下,没到 100 秒,不过也是和 xlrd 速度差不多的,在 85 秒左右
    alexfu
        12
    alexfu  
       2019-08-09 11:37:08 +08:00
    交易记录的话已有的 row 应该是不变的? 计算能不能改成增量式的?根据新加的行算出结果的变化量,记录上次的结果和两个文件的行数?
    read_excel 可以选 backend 为 xlrd,另外有 skip_row 参数 应该能省去一些 parsing 的时间?
    joson1205
        13
    joson1205  
    OP
       2019-08-09 11:49:40 +08:00
    @alexfu 我拿到数据的时候属于一个核算节点,row 是固定的,基本不会有增删,没办法改成增量式计算,而且数据都是连续性的,skip_row 并不适用...
    joson1205
        14
    joson1205  
    OP
       2019-08-09 12:36:48 +08:00
    目前找到最快的办法是 xlwings,预先打开了 xlsx 文件,在几秒钟内就能读取完成!

    import xlwings as xw
    # 事先打开了 xlsx 文件,会直接返回这个文件对象
    wb = xw.Book("demo.xlsx")
    sht1 = wb.sheets["sht111"]
    sht2 = wb.sheets["sht222"]

    sht1_value = sht1.range('A2:AU140000').value
    sht2_value = sht2.range('A2:AQ19000').value
    snw
        15
    snw  
       2019-08-09 12:44:07 +08:00 via Android
    直接用 VBA 读写呢?在文件打开的情况下,VBA 对 Range 与数组之间的读写操作非常快。
    tzigone
        16
    tzigone  
       2019-08-09 12:45:52 +08:00 via Android
    5 万记录以上,用数据库模式吧,其他的都会崩溃。不清楚 python 下的数据读取引擎,在 window 下,装个 ace 就很稳了,速度绝对第一,也不崩。
    joson1205
        17
    joson1205  
    OP
       2019-08-09 12:59:40 +08:00
    @snw vba 在运算方面实在太慢了
    youthfire
        18
    youthfire  
       2019-08-09 13:13:59 +08:00 via iPhone
    不能存入数据库,那肯定影响速度,否则 pandas+sqlite 是不错的选择。pandas 远快于 openpyxl 及 xlrd,另外 pyExcelerate 写入 xlsx 速度远快于 xlsxwriter,比 pandas 还快。
    luban
        19
    luban  
       2019-08-09 14:20:56 +08:00 via iPhone
    没有太好的办法,只能曲折,Excel 只是压缩格式的一种,可以解压开看看里面的结构
    一是不用 excel,改用 csv,txt 等文本格式
    二是解压后解析 xml,2007 及后的 xml 格式较规范

    为什么要转换成解析文本的方式,因为文本可以逐行读取处理,再大的文件也不怕
    大文件几十上百 M 的你自己用高配电脑打开都很慢的
    joson1205
        20
    joson1205  
    OP
       2019-08-09 14:32:07 +08:00
    @youthfire 写入的代码目前比较完善,暂时不打算做改动,谢谢你的建议
    leavic
        21
    leavic  
       2019-08-09 15:19:59 +08:00
    xlrd 已经挺快了,但是我印象中 xls 格式比 xlsx 格式快 N 倍。
    leavic
        22
    leavic  
       2019-08-09 15:20:40 +08:00
    之前写过一个分析从长生产数据的脚本,大概就是 2~3 万行的样子,我就直接要求他们把 excel 转成 2003 格式,否则太慢了。
    joson1205
        23
    joson1205  
    OP
       2019-08-09 15:21:56 +08:00
    @leavic 限制行数太少了,也满足不了日常业务需求
    joson1205
        24
    joson1205  
    OP
       2019-08-09 16:42:43 +08:00
    @youthfire 简单搜索一下关于 pyExcelerate 的资料,这个库好像不支持添加单元格批注的,是我没找到方法吗?资料实在太少了,知道的可以告诉我一下,谢谢
    chong3397
        25
    chong3397  
       2019-08-09 16:44:19 +08:00
    插个眼,感觉将来会遇到类似的问题
    omph
        26
    omph  
       2019-08-09 16:49:00 +08:00
    xlsx 转 csv,可以用其它程序做,go、rust 应该都有
    wuwukai007
        27
    wuwukai007  
       2019-08-09 17:06:51 +08:00
    xlrd 真的很慢,之前开多个进程,勉强用着
    snw
        28
    snw  
       2019-08-09 17:32:41 +08:00 via Android
    @joson1205
    VBA 对基本数据格式的加减乘除运算不会慢到哪里去。对于 Black-Scholes 涉及的正态分布之类可以用数值计算自己写,比自带公式快。还有些写法上的注意点(比如 a*a*a 比 a^3 快)。

    VBA 的速度劣势是 Object 的 GC 慢(打包在 Collection 或 Dictionary 时能感觉到),时间复杂度是平方关系,10 万个 Object 的 GC 大概在 10 秒左右。
    另外 VBA 现成的操作 Array 轮子几乎没有,要快的话需要直接调用库。

    VBA 的优势在于可以读写 xlsb 格式,体积和打开保存速度比 xls, xlsx 好得多。
    youthfire
        29
    youthfire  
       2019-08-09 17:33:26 +08:00
    @joson1205 印象中是没有的,或者我自己也没找到。其实这个库可以说有点简陋,更新频率也低,但对于不复杂的大批量 excel 文本写入,速度惊人,当初也是从 stack overflow 看到别人讨论 xlsx/xlsm 写入速度优化知道的。另外一个我自己遇到的问题是对于日期的支持不理想。
    youthfire
        30
    youthfire  
       2019-08-09 17:36:02 +08:00
    @snw xlsb 确实是好东西,然而支持他的库太少了,相对 xlsx/xlsm
    joson1205
        31
    joson1205  
    OP
       2019-08-09 17:43:41 +08:00
    @snw 其实刚开始拿到报表的时候就是用的 VBA 实现的,后面经过一系列的调整,运算已经是越来越复杂,而且又涉及到拆分,样式,单元格的合并等等这些,优化过一次,电脑还是都直接卡死,没办法,才考虑用的 Python.
    可能是我水平不够....见笑..
    deorth
        32
    deorth  
       2019-08-10 00:34:22 +08:00
    我现在每天拿 openpyxl 写入 100w 行的数据到 xlsx,要跑 3 分钟😅
    jiang123574
        33
    jiang123574  
       2019-08-13 11:31:02 +08:00
    xlwings 可以看看这个 读取超快
    joson1205
        34
    joson1205  
    OP
       2019-08-13 12:14:51 +08:00
    @jiang123574 嗯,已经用上了,目前用的就是它
    wangweiggsn
        35
    wangweiggsn  
       2020-04-20 23:20:20 +08:00
    @snw 我也用了很多 xlsb 格式文件,用 pyxlsb 处理很快。慢是由于 office 的 com 接口引起的,只要能绕开 com 接口,当文本处理 excel 文件,都很快。
    d5d
        36
    d5d  
       2021-04-29 15:09:06 +08:00
    前来挖坟

    遇到同样的问题,pandas to_excel 用 openpyxl 执行起来循环写入多个 execel 的 sheet.模式用 append 效率比较慢.


    大佬们用什么办法?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2657 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 22ms · UTC 15:02 · PVG 23:02 · LAX 07:02 · JFK 10:02
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.