見出し画像

便利ライブラリ openpyxl④ グラフの挿入

万事屋ゆきさん

これまでの投稿でDataFrameをただ出力したExcelファイルに手を加えて、体裁よく加工してきました。

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

# ファイルの読み込み
wb = load_workbook("コロナ重症者数2022-12.xlsx")
ws = wb.active
# 行・列の挿入
ws.insert_rows(1, 2)
ws.insert_cols(idx=1, amount=1)
# 行・列幅の調節
cols = ["A", "B", "C", "D", "E", "F"]
widths = [3, 11, 10, 8, 8, 9]
for i in range(len(cols)):
    ws.column_dimensions[cols[i]].width = widths[i]
ws.row_dimensions[2].height = 5
# 任意のセルに文字を入力
ws["A1"] = 'COVID-19重症者数の推移（2022年12月）'
#フォントのスタイルを設定（太字、イタリック、フォントサイズ、フォント名など）
title_font = Font(name='游ゴシック', size=12, bold=True, color='1F497D')
table_font = Font(name='游ゴシック', size=10, color='000000')
# セルにフォントを適用
ws['A1'].font = title_font
# セル配置
alignment_style = Alignment(horizontal='center', vertical='center')
# 表全体に適用
last_row = ws.max_row
last_col = ws.max_column
rows = ws.iter_rows(3, last_row, 2, last_col)
for row in rows:
    for cell in row:
        cell.border = Border()  #罫線設定をリセット
        cell.font = table_font
        cell.alignment = alignment_style
# 塗りつぶし
titleRow_fill = PatternFill(start_color='1F497D', end_color='1F497D', fill_type='solid')
titleRow_font = Font(name='游ゴシック', size=12, bold=True, color='FFFFFF')
titleRow = ws.iter_rows(3, 3, 2, last_col)
for row in titleRow:
    for cell in row:
        cell.fill = titleRow_fill
        cell.font = titleRow_font
records_fill = PatternFill(start_color='C5D9F1', end_color='C5D9F1', fill_type='solid')
records = ws.iter_rows(5, last_row, 2, last_col)
for row in records:
    for cell in row:
        if cell.row % 2 == 1:
            cell.fill = records_fill
# ブックを別名で保存
wb.save('コロナ重症者数2022-12【openpyxl】.xlsx')

最終回となる今回は、グラフを挿入してみたいと思います。



グラフの作成

openpyxlの解説シリーズでは、コロナ重症患者数のオープンデータをDataFrameにしたものをサンプルデータとして使用してきました。このデータをグラフにして挿入すると良さげです。
地域ごとの患者数の推移を折れ線グラフにしても良いですが、Excelでグラフを作成しても変わり映えしないので、Excelでは作成が難しいヒートマップを作ってみたいと思います。

DataFrameの準備

オープンデータからダウンロードしたcsvファイルをpandasで読み込んで使用します（既出コード）。

import pandas as pd

df = pd.read_csv('severe_cases_daily.csv')
df = df[['Date', 'Hokkaido', 'Tokyo', 'Osaka', 'Okinawa']]
df = df.query('Date.str.contains("2022/12")')


Heatmap作成

seabornを用いてヒートマップを作成します。詳細については過去の投稿をご参照ください。

必要なライブラリをimportします。

import matplotlib.pyplot as plt
import seaborn as sns

DataFrameのindexがヒートマップの縦軸になるので、'Date'列をindexに設定します。

df.set_index('Date', inplace=True)

ヒートマップは数字の大きさを色の濃淡で可視化するので、DataFrameに含まれる数字の範囲が分かると、数字の変化を反映しやすいヒートマップにすることができます。
患者数の推移なので最小値は0で固定するとして、最大値を求めます。
DataFrameのカラムごとの最大値は以下のようにして求めることができます。

df.max()

DataFrameをNumpy配列に変換して、その中の最大値を取得すれば、データ全体の最大値を求めることができます。

max_value = df.to_numpy().max()

max_valueという変数の中にはOsakaの67.0が最大値として格納されているハズです。
この値を用いてヒートマップを作成します。

plt.figure(figsize=(8, 8))
sns.heatmap(df, annot=True, fmt='g', cmap='Greens', vmax=max_value, vmin=0).xaxis.tick_top()
plt.savefig("重症例の推移.png", dpi=100, bbox_inches="tight")

0～67を色の濃淡で示したヒートマップをpng形式で作成することができました。

グラフの挿入

ヒートマップはグラフなのかは微妙ですが（笑）、png形式やjpeg形式で保存された図を挿入することができます。

画像を扱うため、Pillowというライブラリが必要となります。初めて使用する方はisntallする必要があります。

pip install Pillow

画像の挿入はとても簡単で、ws.add_image(画像, セル番地)で挿入することができます。

from openpyxl.drawing.image import Image

# 画像を読み込む
img = Image("重症例の推移.png")  # 画像ファイルのパスを指定

# 指定したセルに画像を挿入
ws.add_image(img, "H3")
wb.save('コロナ重症者数2022-12【openpyxl】.xlsx')

意図したように図を挿入できたようです！

一般化して汎用性を高める

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.drawing.image import Image

date1 = input('yyyy/mm')
date2 = date1.replace('/', '-')
df = pd.read_csv('severe_cases_daily.csv')
df = df[['Date', 'Hokkaido', 'Tokyo', 'Osaka', 'Okinawa']]
df = df.query(f'Date.str.contains("{date1}")')
df.to_excel(f'コロナ重症者数{date2}.xlsx', index=False)

# ヒートマップの作製
df.set_index('Date', inplace=True)
max_value = df.to_numpy().max()
plt.figure(figsize=(8, 8))
sns.heatmap(df, annot=True, fmt='g', cmap='Greens', vmax=max_value, vmin=0).xaxis.tick_top()
plt.savefig("重症例の推移.png", dpi=100, bbox_inches="tight")

# ファイルの読み込み
wb = load_workbook(f'コロナ重症者数{date2}.xlsx')
ws = wb.active
# 行・列の挿入
ws.insert_rows(1, 2)
ws.insert_cols(idx=1, amount=1)
# 行・列幅の調節
cols = ["A", "B", "C", "D", "E", "F"]
widths = [3, 11, 10, 8, 8, 9]
for i in range(len(cols)):
    ws.column_dimensions[cols[i]].width = widths[i]
ws.row_dimensions[2].height = 5
# 任意のセルに文字を入力
ws["A1"] = f'COVID-19重症者数の推移（{date1}）'
#フォントのスタイルを設定（太字、イタリック、フォントサイズ、フォント名など）
title_font = Font(name='游ゴシック', size=12, bold=True, color='1F497D')
table_font = Font(name='游ゴシック', size=10, color='000000')
# セルにフォントを適用
ws['A1'].font = title_font
# セル配置
alignment_style = Alignment(horizontal='center', vertical='center')
# 表全体に適用
last_row = ws.max_row
last_col = ws.max_column
rows = ws.iter_rows(3, last_row, 2, last_col)
for row in rows:
    for cell in row:
        cell.border = Border()  #罫線設定をリセット
        cell.font = table_font
        cell.alignment = alignment_style
# 塗りつぶし
titleRow_fill = PatternFill(start_color='1F497D', end_color='1F497D', fill_type='solid')
titleRow_font = Font(name='游ゴシック', size=12, bold=True, color='FFFFFF')
titleRow = ws.iter_rows(3, 3, 2, last_col)
for row in titleRow:
    for cell in row:
        cell.fill = titleRow_fill
        cell.font = titleRow_font
records_fill = PatternFill(start_color='C5D9F1', end_color='C5D9F1', fill_type='solid')
records = ws.iter_rows(5, last_row, 2, last_col)
for row in records:
    for cell in row:
        if cell.row % 2 == 1:
            cell.fill = records_fill

# 画像を読み込む
img = Image("重症例の推移.png")
# 指定したセルに画像を挿入
ws.add_image(img, "H3")
# ブックを同名で上書き保存
wb.save(f'コロナ重症者数{date2}.xlsx')

これまでのコードを全て繋げてみました。
ただし、これまでは2022年12月のデータを対象としてきましたが、input()を使用して任意の年月のデータを取得して、ヒートマップ付きのExcelファイルとしてまとめられるように汎用性を持たせました。
具体的には、dfのqueryにinput()で入力した文字列を使用できるように変更し、ファイル名やA1セルへ入力する文字列も指定した年月が入るようにしています。
※DataFrameのDateを最初にDate型に変換しておいた方がスマートなコードになりそうですが、ベースのコードをなるべく変えずに使用しました。ファイル名に'/'は使用できないので'-'に置換する方法をとっています。

試しに、「2020/8」、「2021/8」および「2022/8」を入力してデータを比較してみます。
※月ごとで比較するならヒートマップのmax_valueを固定値にしておいた方が大小が明確になります。用途に応じて調節してください。

2020年8月
2021年8月
2022年8月

それぞれのデータを取得し、作図やファイル作成まで一瞬で行うことができました。
データを加工する手順は同じなので、このような自動化をすることもできます。仕事でExcelはよく使用されているので、Excelファイルを加工できるopenpyxlは実務でかなり役立つライブラリですね！

いいなと思ったら応援しよう！