日経ソフトウェア2022年5月号の「PythonでExcel自動化」を試してみた その2

データの準備

必要なデータは以下のURLからダウンロードします。(t22205.zip）

info.nikkeibp.co.jp

本の中ではAnacondaを使用していますが、VSCでやってみます。

anacondaは

https://www.anaconda.com/ からdownloadボタンで入手できます。

ケース2：結合編　複数のファイルを結合して数値データを集計し、グラフをExcelファイルに張り付けてレポート化

０．各支店の予定管理表が「rawdata」フォルダに保存されている。

１．各支店の売上目標と売上実績の合計値を算出する。

２．データから各支店の達成率を算出する。

３．達成率を降順に並び替える。

４．データからグラフを作成し、outputフォルダに保存する。

１．各支店の売上目標と売上実績の合計値を算出する。

ライブラリとモジュールのimportは以下の通りです。

Excelを操作するため、openpyxlをimportします。

グラフ画像ファイルをExcelに張り付けるため、openpyxl.drawing.imageモジュールからImageメソッドをimportします。こうすることでモジュール名を記入しなくて使用できます。

globはpythonの標準ライブラリでファイル名を取得する場合に使用します。

文字化けしないように、japanize_mathplotlibをimportします。

import pandas as pd

import openpyxl

from openpyxl.drawing.image import Image

import glob

import japanize_matplotlib

次にパスの設定と、rawdata内のファイル名をbranch_name変数に取得します。

globモジュールのglobメソッドで取得します。

import_folder_path = 'lesson/Case02/rawdata'

graph_name = 'lesson/Case02/output/graph.png'

file_path_name = 'lesson/Case02/output/達成率.xlsx'

branch_name = glob.glob(import_folder_path + '/*.xlsx')

変数の内容を確認します。5つの支店のファイルが確認できます。

print(branch_name)

実行結果

['lesson/Case02/rawdata\\大阪支店.xlsx', 'lesson/Case02/rawdata\\札幌支店.xlsx', 'lesson/Case02/rawdata\\東京支店.xlsx', 'lesson/Case02/rawdata\\横浜支店.xlsx', 'lesson/Case02/rawdata\\福岡支店.xlsx']

DataFrameを作成し、concatですべてのファイルデータを結合し一つのデータ(df_all)にします。rawdataフォルダ内のExcelファイルを開いたまま実行するとPermissionErrorになるかもしれませんのでExcelファイルは閉じておきます。

「index_col=0で0列目をインデックスに使用します。」という意味になります。

index_colを省略すると一番左の列にインデックス(0,1,2,…)が割り振られます。(default)

df_all  = pd.DataFrame()

for iii in branch_name:

    df = pd.read_excel(iii,index_col=0)

    df_all = pd.concat([df_all,df])

groupbyメソッドを使用して支店ごとに売上目標と売上実績を集計します。

branch_sum = df_all[['支店','売上目標','売上実績']].groupby('支店')

sumメソッドで支店ごとの売上目標と売上実績の合計値を集計します。

branch_sum = df_all[['支店','売上目標','売上実績']].groupby('支店').sum()

print(branch_sum)

実行結果

 　　売上目標    売上実績
支店
大阪  455000  433791
札幌  264000  262444
東京  415000  419145
横浜  387000  384040
福岡  198000  191967

２．データから各支店の達成率を算出する。

支店ごとの達成率を計算します。

branch_sum = branch_sum['売上実績'] / branch_sum['売上目標']

３．達成率を降順に並び替える。

sortを利用して降順に並び替えます。ascending=Trueで昇順、Falseで降順にsortします。

branch_sum['達成率'] = branch_sum['売上実績'] / branch_sum['売上目標']

branch_sum = branch_sum.sort_values('達成率',ascending=False)

print(branch_sum)

実行結果

     売上目標    売上実績       達成率
支店
東京  415000  419145  1.009988
札幌  264000  262444  0.994106
横浜  387000  384040  0.992351
福岡  198000  191967  0.969530
大阪  455000  433791  0.953387

４．データからグラフを作成し、outputフォルダに保存する。

outputフォルダを作成し、作成したグラフを保存します。

import os

export_file_path = 'lesson/Case02/output'

if os.path.isdir(export_file_path)==False:

    os.mkdir(export_file_path)

 

達成率を金色の〇でプロットします。線は無しです。linestyle='solid'と指定すると〇が戦で結ばれて表示されます。secondary_yでグラフの右側に表示するメモリの単位を指定します。

plotメソッドでグラフを作成します。

kind='bar'で棒グラフを示しています。

color='bm'で　blueとmagentaを使用することを表しています。

axはサブプロットとして合わせるグラフを指定します。１つのプロットに複数のグラフを表示させることができます。

get_figureメソッドでオブジェクトに変換し、df_graph変数に格納しています。

savefigメソッドで画像を保存します。

 

 

ax = branch_sum[['達成率']].plot(linestyle='',marker='o',color='gold',secondary_y='達成率')

df_graph = branch_sum[['売上目標','売上実績']].plot(kind='bar',color='bm',ax=ax).get_figure()

df_graph.savefig(graph_name)

実行結果(graph.png)

作成したグラフ(graph.png)

４．データからグラフを作成し、outputフォルダに保存する。

達成率.xlsxファイルをoutputフォルダ下に作成します。

branch_sum.to_excel(file_path_name)

グラフをエクセルに張り付けます。

達成率.xlsxファイルをload_workbookメソッドで読み込み、workbook変数に格納します。次に使用するワークシートを指定します。0は一番最初のシートを意味します。

workbook = openpyxl.load_workbook(file_path_name)

worksheet = workbook.worksheets[0]

Imageメソッドで画像のオブジェクトを生成しています。

add_imageで画像を追加します。追加する位置はエクセルの「F1」からとしています。

saveメソッドでworkbookをエクセルとして保存しています。

img1 = Image(graph_name)

worksheet.add_image(img1,'F1')

workbook.save(file_path_name)

実行結果

グラフを追加したExcelファイル