pandasでExcelファイルの書き込み(to_excel)

pandasでExcelファイルの書き込み(to_excel)
スポンサーリンク
スポンサーリンク

外部ライブラリの pandas で DataFrame を Excelファイルに書き込む・出力する方法を解説します。

Excelファイルの読み込みについては以下の記事を参照してください。
・関連記事:pandasでExcelファイルの読み込み(read_excel)

pandasの扱い方についてはこちらの記事も参考にしてください。
・関連記事:pandasでSeries, DataFrameを作成する
・関連記事:pandas DataFrameからデータを抽出

スポンサーリンク

使用ライブラリ

ライブラリ :pandas(公式ドキュメント
インストール:必要
ライブラリ :openpyxl (公式ドキュメント
インストール:必要(.xlsx用)
モジュール :xlwt(公式ドキュメント
インストール:必要(.xls用)

pandas で Excelファイルを書き込む際には内部でopenpyxl, xlwtを使用します。openpyxlについては以下の記事を参照ください。
・関連記事:PythonでExcelファイルを操作する(openpyxl)

インストール

.xlsxを書き込むには openpyxl、.xlsを書き込むには xlwt というライブラリが必要です。
いずれのライブラリもpipでインストールできます。
・関連記事:Pythonライブラリのインストール(pipの使い方)

python -m pip install pandas openpyxl
(.xls利用時のみ)python -m pip install xlwt

Successfully installed ・・・・と表示されればインストールは成功です。(依存関係のある他のライブラリも同時にインストールされます)

サンプルデータ

書き込むデータは以下のdata.xlsxから読み込んだデータを使用します。
ファイルには、ヘッダを含むデータが3シート分保存されています。またファイルはpython実行ディレクトリに保存されているものとします。

ライブラリのインポート

pandas をインポートします。

openpyxl, xlrd のインポートは不要です。

pandasでExcelファイルに書き込む

pandasでExcelに書き込むには to_excel() メソッドを使用します。

DataFrame.to_excel(ファイルパス, sheet_name=書き込むシート名)

第一引数にファイルのパスを指定、第二引数 sheet_name を省略するとシート名は自動で「Sheet1」になります。

同じ名前のファイルが既に存在している場合には、上書き保存されますので注意してください。

次の例では、data.xlsx の シート「202004」をDataFrameとして読み込み、新しいファイル pd_data.xlsx にデータをそのまま書き込みます。
・関連記事:pandasでExcelファイルの読み込み(read_excel)

結果はこのようになります。

pandasでExcelファイルの書き込み2


デフォルトではヘッダとインデックス番号が、太字・枠線有りで書き込まれます。
これを解除したい場合は、pd.io.formats.excel.ExcelFormatter.header_style = None を先に指定してから to_excel で書き込みます。

以下のように標準スタイルで書き込まれます。

列を指定して書き込む

特定の列だけを書き込みたい場合は、引数の columns で列名を指定します。

DataFrame.to_excel(ファイルパス, columns = 書き込む列名のリスト)

ヘッダ(列名)・インデックス番号(行見出し)を書き込まない

ヘッダ(列名)やインデックス番号(行見出し)が不要の場合は、header=False , index=False を指定します。

DataFrame.read_excel(ファイルパス, sheet_name=書き込むシート名, header=False, index=False)

先程読み込んだ DataFrame をヘッダ・インデックス番号無しで書き込みます。

pandasでExcelファイルの書き込み3

ヘッダ(列名)を変更して書き込む

ヘッダ(列名)を変更したい時は、引数 header をリストで指定します。
インデックス列名を変更したい場合は index_label を指定します。

DataFrame.to_excel(ファイル名, header=ヘッダ名のリスト, index_label=インデックス列名)

書き込み開始セルを指定する

書き込む位置を指定するには、引数 startrow, startcol で開始する行・列位置を指定します。
位置の指定はインデックス番号で行うため、1行目:startrow=0 A列: startcol=0 となります。

DataFrame.to_excel(ファイル名, startrow=行インデックス番号, startcol=列インデックス番号)

細かいExcel操作を行いたい場合は openpyxl を使用します。
・関連記事:PythonでExcelファイルを操作する(openpyxl)

欠損値の出力値を指定する

データに欠損がある場合、デフォルトでは空白として書き込まれます。

書き込む値を指定する場合は、引数 na_rep を指定します。

DataFrame.to_excel(ファイル名, na_rep=書き込む値)

次の例では、シート「202005」を読み込み、欠損値のセルに「#NA(値が無効)」を返す関数 ‘=NA()’ を指定しています。
セルに数式を書き込むには、数式を文字列として指定します。
・関連記事:PythonでExcelファイルの読み込みと操作(openpyxl)- 数式を書き込む

日付の書式を設定して書き込む

日時データはデフォルトでは YYYY-MM-DD HH:MM:SS で書き込まれます。
書式を設定したい場合は、ExcelWriterクラスの datetime_format で指定できます。

with pd.ExcelWriter(ファイルパス, datetime_format=書式文字列) as writer:
        df.to_excel(writer, sheet_name=シート名)

複数シートに書き込む

複数のデータを別々のシートに書き出すには、pandas.ExcelWriter() を使用します。
with 文を使用すると、open(), close() が省略できるため便利です。

With pandasオブジェクト.ExcelWriter(ファイル名) as writer:
        DataFrame1.to_excel(writer, sheet_name=シート名1)
        DataFrame2.to_excel(writer, sheet_name=シート名2)
        …

既存Excelファイルに新しいシートを追加

pandas.ExcelWriter() で引数 mode='a' を指定すると追記モードになり、既存のExcelファイルに新しいシートを追加して書き込むことができます。

With pandasオブジェクト.ExcelWriter(ファイル名, engine=’openpyxl’, mode=’a’) as writer:
        DataFrame1.to_excel(writer, sheet_name=シート名1)

デフォルトでは、エクセル操作ライブラリに xlsxwriter が選択されてしまい、追記モードでは Append mode is not supported with xlsxwriter! というエラーが発生してしまいます。これを回避するために engine='openpyxl' を指定する必要があります。


また、指定したシート名が既に存在する場合は、ValueError が発生します。
エラーを回避するには、引数 if_sheet_exists でシートが存在する場合の処理を指定します。
・ 'new'   → 別名で新規シートを作成(シート名は自動付与)
・ 'replace' → 既存シートを削除して新規作成
・ 'overlay' → 既存シートに書き込み

if_sheet_exists=new を指定してみます。

行・列を追加して書き込む

列・行を加工した結果を追加して書き込みたい場合は、pandasで加工を行った上で出力します。

下の例は、行列の合計値をDataFrameに追加して書き込んでいます。

この例のように、DataFrame.sum() で行列の合計を算出できますが、デフォルトでは文字列の行は文字を連結した結果を求めるため、numeric_only=True で数値のみ対象にして計算しています。

pandasでExcelファイルに書き込む方法を解説しました。

PandasでExcelファイルを読み込むにはこちらの記事を参照してください。
・関連記事:pandasでExcelファイルの読み込み(read_excel)

細かいExcel操作を行いたい場合は openpyxl を使用します。
・関連記事:PythonでExcelファイルを操作する(openpyxl)

PandasのSeriesやDataFrameについてはこちらの記事を参照してください。
・参考記事:PandasでSeries, DataFrameを作成する