pythonでexcelデータを読み込むときの下準備【技術的負債対応用】

14530 ワード
高速化 Openpyxl Python pyhton3 Python テキストリンク

要約

エクセルファイルをpythonで処理するときは、余計な情報を削除しよう。
エクセルマクロを作るときは書式の作り込みはできるだけ避けよう。

きっかけ

数千行、十列程度のエクセルファイルに対して作業を行っていたのだが、異様にファイル操作が重かった。
デバッグ中にエクセルファイルを読み込むだけで10分以上かかり、事実上デバッグ不可能となっていた。
調査した結果、またブラックボックス化されたマクロが悪さをしていたたことがわかった。

出来事

まずはこのコードと出力結果を見てほしい。
誤差レベルのサイズのマクロと、ある細工を除けば、格納されているデータは全く同じ3つのエクセルファイルに対して、全セルの数値を合計した値を出力しただけのプログラムだ。

import openpyxl as pyxl
import datetime as dt


def read_and_sum(path: str):
    book = pyxl.open(path)
    ans = 0
    sheet = book.worksheets[0]
    for row in range(500):
        for col in range(10):
            ans += sheet.cell(row+1, col+1).value
    book.close()
    return ans


if __name__ == '__main__':
    start = dt.datetime.now()
    print(read_and_sum("testcase1.xlsx"))
    check1 = dt.datetime.now()
    print(read_and_sum("testcase2.xlsm"))
    check2 = dt.datetime.now()
    print(read_and_sum("testcase3.xlsm"))
    end = dt.datetime.now()

    print(f"例1:{(check1 - start).total_seconds()}sec")
    print(f"例2:{(check2 - check1).total_seconds()}sec")
    print(f"例3:{(end - check2).total_seconds()}sec")

出力結果

2494558
2494558
2494558
例1:0.034934sec
例2:0.045877sec
例3:12.923414sec

原因

ファイルの中身は同じでも、ファイル容量はこれほどの差ができている。

この時点でピンときている人も多いと思うけど、3つのエクセルファイルの差は書式設定にある。
testcase1のエクセルファイルはこのように、
0～1000の数値を500行10列の範囲に書いただけのデータだ。

case2は、1セル目のフォント、サイズを変更し、文字太字にして、
次のマクロを適用した。

Sub create_testcase()
    Dim src As Range
    Dim row As Long
    Dim col As Long

    Set src = ActiveSheet.Cells(1, 1)

    src.Copy

    For row = 1 To 500
        For col = 1 To 10
            If (row + col) Mod 2 = 1 Then
                ThisWorkbook.Sheets(1).Cells(row, col).PasteSpecial Paste:=xlPasteFormats
            End If
        Next
    Next


End Sub

石松模様に書式をコピーした結果が下の通り。

case3は、上記のfor文のrowの上限を5000,colの上限を1000に変更しただけだ。見た目はcase2のものと全く同じだが、不要な書式データを大量に埋め込んである。
これが極端な速度のからくりだった。

余談

私の遭遇したExcelファイルは、特定シートの書式を消すだけでファイルサイズが10MB以上減る、素晴らしい出来栄えになっていた。
先人がマクロを作ったとき、セルの書式ごとコピーしていたのだと予想している。
書式を消したところ10分以上読み込みがかかり、諦めたエクセルファイルが100秒で読み込めるようになった。
このクソマクロを作ったのは誰だぁっ！

詳細検証

書式の多さがどこに影響しているかを調べるために、先程の速度計測用のソースを次のように書き換えた。

import openpyxl as pyxl
import datetime as dt


def read_and_sum(path: str):
    start = dt.datetime.now()
    book = pyxl.open(path)
    ans = 0
    sheet = book.worksheets[0]
    check1 = dt.datetime.now()
    for row in range(500):
        for col in range(10):
            ans += sheet.cell(row+1, col+1).value
    check2 = dt.datetime.now()
    book.close()
    end = dt.datetime.now()
    print(f"ファイル読み込み時間:{(check1 - start).total_seconds()}")
    print(f"セル操作時間:{(check2 - check1).total_seconds()}")
    print(f"後処理時間:{(end - check2).total_seconds()}")
    return ans


if __name__ == '__main__':
    print("例1：")
    print(read_and_sum("testcase1.xlsx"))
    print("例2：")
    print(read_and_sum("testcase2.xlsm"))
    print("例3：")
    print(read_and_sum("testcase3.xlsm"))

出力結果は下記のようになる。

例1：
ファイル読み込み時間:0.03194
セル操作時間:0.003001
後処理時間:0.0
2494558
例2：
ファイル読み込み時間:0.040884
セル操作時間:0.001993
後処理時間:0.0
2494558
例3：
ファイル読み込み時間:13.107952
セル操作時間:0.003989
後処理時間:0.0
2494558

どのファイルでも読み込み時間がほとんどであるため、
書式がセルの操作時間に影響を及ぼしているかどうかはよくわからなかった。

まとめ

  • Pythonでexcelファイルを操作するときは、高速化のために余計な書式はなるべく削除しよう。
  • VBAのマクロも技術的負債になるかどうかを意識して作成しよう。
TypeScript Genericsの究極の説明:関数
Reduxを使用してステータスCh 1を管理します.Redux Basic (1) ~ (4)