IT業務効率化

pandasをopenpyxlとxlwtを比較して使ってみる

pandas openpyxl xlwt

ゴール:速く美しくエクセルに出力したい

pandasのDataFrameインスタンスのto_excel()を使ってそのまんまファイルに出力すると、下の画像のようになります。

図1. to_excelでそのまま出力したexcelファイル

列幅は一定ですし、インデックスがA列に入っていて、ちょっと見にくいです。

私の会社ではもう少し整形してほしいと思うユーザがいます。下の画像のようなイメージです。

pandas excel 最速図2. 出力されるexcelの理想形

これは私の調べた限りだと、to_excel()メソッドだけではできません。

これを実現するための手段をいくつか調べたのですが、pythonとexcelのライブラリはいくつかあるようで、どれを使うべきか非常に悩みます。

また私の業務上、100行くらいのexcelではなく、10,000行とか100,000行のexcelデータを出力することもあります。なので速度も求められています。

今回は図2のようなファイル出力を如何に速く綺麗に出力できるか、検証していきたいと思います。

結論

コードはこうしたら良さそう

output_file_name = "pandas_output.xlsx"
sample_csv_df = pd.read_csv("50000_Sales_Records.csv")
sample_csv_df.to_excel(output_file_name, index=False, startrow=1, startcol=1)

# 色付け、列の幅を整えるためにopenpyxlで読み直す
book = openpyxl.load_workbook(output_file_name)
ws = book.worksheets[0]

fill_pattern = openpyxl.styles.PatternFill(patternType='solid', fgColor='2ecc71')
header = ws[ 2]  # 2行目を左から順に見ていき、データが最後に存在するカラムまで取得する

min_length = 3
for header_cell in header:
    header_cell.fill = fill_pattern
    
    column = header_cell.column
    column_cells = ws[column]
    length = max(len(str(cell.value)) for cell in column_cells)
    if min_length > length:
        length = min_length
    ws.column_dimensions[column].width = length

book.save(output_file_name)

わかったこと

  • pandasはエクセル出力する時にopenpyxlとxlwtを利用しているため、このライブラリを用いた高速化は意味がない(できない)。
  • xlwtはopenpyxlよりも書き込み速度が速いが、xls拡張子しか扱えない。

出力結果

python excel pandas openpyxl

Pythonコードだけでここまで整形して出力することができました。

git

https://github.com/hirayuki32/excel_python/

ここまでにどういうことを試したか、書いていきます。

※本サイトのテーマJINの不具合により、一部コードの中括弧の後にスペースがあります。

環境

スペック

  • macOS Mojava10.14.5
  • mac book air(13-inch, 2017)
  • CPU 1.8 GHz Intel Core i5
  • メモリ 8 GB 1600 MHz DDR3
  • python3.6
  • jupyter notebook 5.5.0

利用するデータ

今回利用するデータはこちらのページで提供してくれているSampleデータを利用します。50,000行のデータセットを選択しました。

まずはpandasを使ってみる

pandasはきっと速いと思う

pandasは大量のデータを高速に処理できるように作られています。ドキュメントには以下のように書かれています。

pandas is fast. Many of the low-level algorithmic bits have been extensively tweaked in Cythoncode. However, as with anything else generalization usually sacrifices performance. So if you focus on one feature for your application you may be able to create a faster specialized tool.

low-levelは何がlow-levelなのか、まだlow-levelな僕にははっきりいえませんが、簡素な計算だと推測しています。注目したいのは、少なくともアルゴリズムの一部をcython(C言語を呼び出すpythonの拡張)を使って書かれているため、高速化の工夫がなされていることです。並列処理もできるため、excelに出力するときもきっと速いんじゃないか?と思っています。

まずはやってみましょう。基本的に時間の計測はjupyter notebookの%%timeitを使います。

jupyter notebook はセルの先頭に%%timeitと書くだけで、そのセルの実行を複数回行い、平均とばらつき(分散)をとってくれます。

%%timeit
sample_csv_df = pd.read_csv("50000_Sales_Records.csv")
sample_csv_df.to_excel("pandas_output.xlsx", index=False, startrow=1, startcol=1)

ファイルの読み込みも計測時間に含めています。結果はこの通り。

15.2 s ± 763 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

なるほど、15秒くらい平均かかるようですね。

引数に書かれているindex=False, startrow=1, startcol=1ですが、左から順にインデックス書かない、(エクセルの)2行目から始める、(エクセルの)2列目から始める、という意味です。

openpyxlを使ってみる

%%time
with open("50000_Sales_Records.csv", "r") as file:
    data = csv.reader(file)
    wb = Workbook()
    ws = wb.active
    row_num = 1
    for record in data:
        col_num = 1
        for val in record:
            ws.cell(row=row_num, column=col_num, value=val)
            col_num += 1
        row_num += 1
    wb.save("openpyxl_output.xlsx")

こんな感じでコードを書いてみました。これでほぼ同様のデータの入れ方が可能です。

ws.cell(row=row_num, column=col_num, value=val)

この書き方でセルにデータを入れることが可能です。

%%timeitで時間を計測してみたところ、

16.7 s ± 1.29 s per loop (mean ± std. dev. of 7 runs, 1 loop each)

でした。

時間を比較する前に、ふと思いました。セルに一個一個書いていかなきゃいけないなんて、そんなことあるかな?for文が2つもあることがダサいような・・・と思っていたら、もっとすっりした書き方がありました。下のコードです。

%%timeit
with open("50000_Sales_Records.csv", "r") as file:
    data = csv.reader(file)
    wb = Workbook()
    ws = wb.active
    for record in data:
        ws.append(record)  # レコードごとにエクセルに挿入することが可能
    wb.save("openpyxl_output.xlsx")

ws.append(record)を繰り返すとレコード単位でデータを入れていくことが可能

これでエクセル完成までの時間を計測した場合、

15.4 s ± 1.15 s per loop (mean ± std. dev. of 7 runs, 1 loop each)

でした。for文2つ書いた方に比べて、そこまで速くならないです。意外です。

・・・

えっ?pandasがopenpyxlに比べてそんなに速くないっ!?

すっごく気になりますが、一旦xlwtも使ってみたいと思います。

xlwtを使ってみる

%%timeit
with open("50000_Sales_Records.csv", "r") as file:
    data = csv.reader(file)
    wb = xlwt.Workbook()
    ws = wb.add_sheet('A Test Sheet')

    row_num = 1
    for record in data:
        col_num = 1
        for val in record:
            ws.write(r=row_num, c=col_num, label=val)
            col_num += 1
        row_num += 1

    wb.save('xlwt_output.xlsx')  # 注意、拡張子は自由に指定できるが、実際は全てxls形式となっている

ざっと調べた感じだとxlwtだとこのようにfor文を2回回す書き方のようです。早速時間を計測してみましょう!

7.62 s ± 510 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

速っ!!xlwtの方がpandasより速っ!?

xlwtはxls形式にしか対応していません。

あれ?でもxlsxで出力できるじゃん?

と勘違いするのですが、xlwtのwb.save(‘xlwt_output.xlsx’)は拡張子込みで自由に命名できます。実際の中身はxlsファイルなのでご注意ください。

3つのライブラリの速度を比較した結果

比較表

DataFrame.to_excel 15.2s
openpyxl ws.cell 16.4s
openpyxl ws.append 15.4s
xlwt 7.62s

なぜpandasは速くないのか?

その答えはソースコードの中にありました。

pandasもエクセルの読み書きのためのopenpyxlとxlwtを使っているのです。そのため計測結果はほぼ一緒です。290行目にはデフォルトで拡張子がxlsxもしくはxlsmだとopenpyxlを使う記述があります。拡張子がxlsだと自動でxlwtを使うようです。

# 290行目から抜粋(2019/07/06)
def _get_default_writer(ext):
    _default_writers = {'xlsx': 'openpyxl', 'xlsm': 'openpyxl', 'xls': 'xlwt'}

しかもfor文の回し方は一緒です。(1970行目

# 1970行目から抜粋(2019/07/06)
        for cell in cells:
            val, fmt = self._value_with_fmt(cell.val)

            stylekey = json.dumps(cell.style)
            if fmt:
                stylekey += fmt

            if stylekey in style_dict:
                style = style_dict[stylekey]
            else:
                style = self.book.add_format(
                    _XlsxStyler.convert(cell.style, fmt))
                style_dict[stylekey] = style

            if cell.mergestart is not None and cell.mergeend is not None:
                wks.merge_range(startrow + cell.row,
                                startcol + cell.col,
                                startrow + cell.mergestart,
                                startcol + cell.mergeend,
                                cell.val, style)
            else:
                wks.write(startrow + cell.row,
                          startcol + cell.col,
                          val, style)

高速化のために

pandasは結局openpyxlとxlwtを使っていることが判明しました。

そして今回のベンチマークの結果を考慮すると、xlwtが速いようです。なのでpandasにxlwtを使うことを明示すると、速度が上がるのではないかと思います。やってみます。

%%timeit
sample_csv_df = pd.read_csv("50000_Sales_Records.csv")
# engine="xlwt"を追記
sample_csv_df.to_excel("pandas_output.xlsx", index=False, startrow=1, startcol=1, engine="xlwt")

どん!

pandas excel ベンチマーク

ぎゃあ!

Invalid extension for engine ‘xlwt’: ‘xlsx’

だそうです。ext = ‘xls’ if engine == ‘xlwt’ else ‘xlsx’というpandasの中のコードで拡張子がxlsでないと対応できないようにしているようですね。

この時にやっとxlwtは全てxls形式で出す仕様に気づきました。

となるとxlsx形式にこだわらず、xls形式にすると速いと思います。

%%timeit
sample_csv_df = pd.read_csv("50000_Sales_Records.csv")
sample_csv_df.to_excel("pandas_output.xls", index=False, startrow=1, startcol=1, engine="xlwt")
11.1 s ± 472 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

速くなりました!!

しかし、知らなかったのですが、、、xlsxファイルの方がデータ容量が小さく済むようです。

-rw-r--r--  1 yuki  staff   9149952  7  6 09:58 pandas_output.xls
-rw-r--r--@ 1 yuki  staff   4178923  7  6 09:31 pandas_output.xlsx

xlsはxlsxに比べて古いバージョンの時の拡張子なので、xlsxの方が容量的に最適化されているようです。(ここでは深く追いません)

再度結論

自分はpandasのto_excelを利用していきます。

  • pandasでできる限り完結させたい
  • xlwt, openpyxlとの組み合わせで速度向上できるわけではない
  • xlwtの方が速いかと思ったが、xls形式のexcelはファイル容量が大きい

またデータ整形に関してはopenpyxlで操作をしようと思います。

xlwtはあくまでxlsにしか対応しておらず、さらに列の挿入する手段など、私の調べた範囲では見つけられませんでした。

今回速度測定に関して話が長くなってしまったので、整形に関してはソースコードからご確認ください。

おわりに

業務の自動化についてコードをよりも大切だと思うことをまとめました。合わせてぜひご覧ください。

python 自動化の課題
Pythonで業務自動化においてコードよりも大切なこと退屈なことをPythonにやらせてみて気づいたコードより大切なこと こちらの有名なPythonの本があるように、たくさんことが...

公式ドキュメント

pandas

openpyxl

xlwt

次回

PyExcelerateという面白いライブラリを見つけたので記事にします。

読んでいただきありがとうございました。
ABOUT ME
hirayuki
今年で社会人3年目になります。 日々体当たりで仕事を覚えています。 テーマはIT・教育です。 少しでも技術に親しんでもらえるよう、noteで4コマ漫画も書いています。 https://note.mu/hirayuki