要約
Pythonで業務ファイルを操作するとき、エクセルファイルはエクセルファイルであることが処理のボトルネックになりうる。できるだけテキストファイルで処理できるようにしよう。
遭遇したトラブル
職場にて2つのエクセルの資料をマージすることになった。
数が少なければ最悪目で確認すればよかったが、2000ファイル以上で、各ファイルのマージする列も1万行を超えていた。
仕方がないからまず差分を取り出すために書きなれているPythonで差分比較用のスクリプトを書いたが、まる二日放置していても1ファイルの差分も完了していなかった。
検証
どうやら自分の想像以上にopenpyxlは遅かったし、特定条件で無限に時間を使ってしまうソフトになっているようだ。
テスト用にまずは次のコードを実行し、これをexcelで開いてxlsx形式で保存し直した。
流石にシンプルすぎるため、中身の解説は省略する。
import random
with open("testdata_csv.csv", "w") as out_file:
for _ in range(50000):
out_file.write(",".join([str(random.randint(0, 1000)) for _ in range(100)]) + "\n")
そしてそれを次のコードで読み込ませた。あまりにも時間がかかるため、適当なところでプログラムを落とすこと。
import openpyxl as xl
import datetime as dt
ROWS = 50000
COLS = 100
CHECK_INTERVAL = 10
def put_interval_to_csv(start: dt.datetime, end: dt.datetime, row_count, target):
with open(f"log_{target}.csv", "a") as log_file:
log_file.write(f"{row_count},{(end - start).seconds}.{(end - start).microseconds:06}\n")
def calc_excel():
book = xl.load_workbook("testdata_csv.xlsx", True)
# VBAと違い、シート番号は0スタート
sheet = book.worksheets[0]
start_check = dt.datetime.now()
for row in range(ROWS):
if not row % CHECK_INTERVAL:
start_check = dt.datetime.now()
row_sum = 0
for col in range(COLS):
row_sum += sheet.cell(row + 1, col + 1).value
print(row_sum)
if not row % CHECK_INTERVAL:
end_check = dt.datetime.now()
put_interval_to_csv(start_check, end_check, row, "excel")
def calc_csv():
with open("testdata_csv.csv") as read_file:
start_check = dt.datetime.now()
for row in range(ROWS):
cells = read_file.readline().rstrip().split(",")
row_sum = 0
for col in range(COLS):
row_sum += int(cells[col])
print(row_sum)
end_check = dt.datetime.now()
put_interval_to_csv(start_check, end_check, row, "csv")
if __name__ == '__main__':
calc_csv()
calc_excel()
csvとxlsx形式の両方で各業の合計数をprintしている。
ただし、xlsxでは10行ごとに速度の計測を行い、csvは全体の実行時間の速度を計測した。
検証結果
テキストファイルの合計の出力は、5000行全てに対して1.297秒かかっているが、csvファイルの出力は下記のような結果になった。
縦軸が1行の処理にかかった秒数、横軸が列である。
青い折れ線が結果で、パット見だと直線に見えるので、excelの機能で1次式の近似式を作り、数式も出力した。
この傾きだと、もし最後までやっていたらおおよそ$4.5 \times 10^7$秒、500日以上かかる計算になった。
また、COLを5に書き換えたところ、上記の処理時間のグラフの傾きが0.016とおおよそ20分の1になった。読み込んだ行が一番影響が大きそうだ。
回避方法
Q. エクセルファイルの操作が遅い。どうしたらしい？
A. エクセルを使わなければいい。
ということで、仕事ではエクセルファイルを一旦csvファイルに保存してから作業を行った。こちらはVBAで自動で変換させた。