エクセルの複数シートにデータフレームを出力・追記する

こんにちは、ミナピピン(@python_mllover)です。

今回は案件でスクレイピングしたデータをエクセル(.xlsx) のシートにまとめるということを行ったのですが、まずPythonではpandasのdf.to_excel()という関数とopenpyxlを用いることでデータフレームの中身をエクセルのシートに出力することができます。

ですが、これをシート名を変えつつfor文で回して同一エクセルファイル内に新しくシートを作成して追記しようとすると、最初のシートが一生上書きされ続け、シートの追加ができません。

Contents

エクセルファイルの複数のシートにデータフレームを出力する
- 前準備
- サンプルコード
既存のエクセルファイルのシートにデータフレームを追記する

エクセルファイルの複数のシートにデータフレームを出力する

これで小一時間悩んでいたのですが、解決法としてはpd.ExcelWriter()というメソッドを用いてエクセルファイルを開いた状態で、df.to_excel()を実行するというやり方で解決しました。

前準備

pd.ExcelWriterは内部でopenpyxlとxlwtを使用しているので、事前にこれらをインストールする必要があります。(pandasはインストールしている前提で進めます)

$ pip install openpyxl
$ pip install xlwt

openpyxlをインストールせずにコードを実行するとValueError: Append mode is not supported with xlsxwriter!というエラーが発生するので必要になります。

サンプルコード

この処理を簡単なコードにすると以下のようになります。

import pandas as pd
df  = pd.DataFrame(['2021-10-13 03:58:06','mariko kitai@ベルリンスタイル', '@masaru_ushijima 家族旅行って思いのほか、難しいですよね。'])
with pd.ExcelWriter('test.xlsx',engine='openpyxl', mode='w') as writer:
    df.to_excel(writer, sheet_name='test1', index=False, encoding='utf-8')
    df.to_excel(writer, sheet_name='test2', index=False, encoding='utf-8')
    df.to_excel(writer, sheet_name='test3', index=False, encoding='utf-8')

あと自分がハマったケースですがウェブサイトのスクレイピングでfor文で1ページずつスクレイピングして情報を抜き出す、みたいな場合は都度with pd.ExcelWriter()～～で書き出すのではなく、リストかなんかにデータをまとめて最後にまとめて出力した方が良いと思います。

というのもfor文の中で何回も同じファイルをwith pd.ExcelWriter()で開くと、エクセルファイル自体は出力できていますが実際にエクセルで開くとファイルが破損していて修復フェイズに入るので見栄え的によろしくないです。

<悪い例>

import pandas as pd
df = pd.DataFrame(['2021-10-13 03:58:06','mariko kitai@ベルリンスタイル', '@masaru_ushijima 家族旅行って思いのほか、難しいですよね。'])
for i in range(3):
    with pd.ExcelWriter('test.xlsx',engine='openpyxl', mode='w') as writer:
        df.to_excel(writer, sheet_name='test1', index=False, encoding='utf-8')

既存のエクセルファイルのシートにデータフレームを追記する

既存のエクセルファイルに追記する場合はpd.ExcelWriter()の引数をmode=’a’とする必要があります

import pandas as pd
df = pd.DataFrame(['2021-10-13 03:58:06','mariko kitai@ベルリンスタイル', '@masaru_ushijima 家族旅行って思いのほか、難しいですよね。'])
with pd.ExcelWriter('test.xlsx',engine='openpyxl', mode='a') as writer:
    df.to_excel(writer, sheet_name='test11', index=False, encoding='utf-8')

参考：https://bunsekikobako.com/to-excel-append-mode/