【キノカレッジ|業務効率化クラス|22年10月(2)】Pythonでエクセルの業務を自動化しよう|Python×Excel

課題内容

あなたは、キノコード株式会社の業務効率化担当です。
営業部からExcel業務をPythonにより効率化をしていると噂を聞いた人事部長から相談を受けました。

話によると、人事部でも売上データをエクセルのツールを使って毎回手集計をしているようです。
同じ操作を毎回行なっており、それで時間を使うのは勿体無いと感じています。
属人化しなければいけないと思い、ptyhonを使って自動化できないかという内容で
あなたへ相談がきました。

以下が人事部長からの相談内容です。
コードを用いて自動化し課題を解決できるようにしましょう。

データセット

キノカレッジ業務効率化2210第2回
【上記zipファイルの中身】
売上データ.xlsx
所属支店別データ.xlsx
支店ファイル
-東京.xlsx
-横浜.xlsx
-大阪.xlsx
-札幌.xlsx
-福岡.xlsx

※保存方法
Mac:右クリック⇒「リンク先を別名で保存」
Windows:右クリック⇒「名前を付けてリンク先を保存」

[問1] 各支店ごとのシートを一枚のデータで出力

人事部長:

「各支店から社員ごとの売上報告(所属支店別データ.xlsx)が上がっています。
その資料をまとめてほしい。
まず、第4四半期(1月、2月、3月)のトータル売上の列も追加各シートごとに上書きしてもらいたい。
各支店ごとに分かれているシートを1枚のシートにしてほしい。
その上で、データは第4四半期の売上数値が高い順に並べてほしい。
もしかすると、同じ売上数値の社員がいる場合もあるので、その場合は1月売上の高い方の順位を上にして。
それが出来たら、"第4四半期各社員売上データ.xlsx"というファイル名で私に提出してください。」

取り組み方の例

  1. 『所属支店別データ.xlsx』を開く
  2. 列名"第4四半期売上"とい新しいカラムを作成し、社員ごとの'1月売上''2月売上''3月売上'の合計値を入力する
  3. 列"第4四半期売上"を残した状態で上書き保存をする
  4. それぞれのシートのデータフレームを、一つのデータフレームにする
  5. 列名"第4四半期売上"で降順にソートをする。その時、'1月売上'も引数に含める
  6. 『第4四半期各社員売上データ.xlsx』というファイル名で出力する。indexはFalse

[問2] ファイルの集計自動化コードを作成

人事部長:

「この業務を実は四半期ごとに行っているんだけれども。本当は毎月行いたい。
月毎に頑張っている社員へ還元できる仕組みを整えたい。
現状、『私の手元に支店フォルダといった各支店のxlsxファイルが送られてきます。
最新月の売上の列が追加された状態で届きます。
ファイルを1つに集計して、最新月の売上が高い順に並び替え。
上位5名に業績賞与をしたいので対象者は備考欄に"賞与あり"と記述し、このデータをファイル保存』したい。
(できたら他の地域の京都や静岡などのデータも今後入ってくる予定があるのでxlsxファイルの都道府県名は固定したくないなぁ。)
送られてきたファイルを処理したかどうか各支店のファイル名も"処理済み"と付け加えれくれる?
そんなところで、コードを全て実行すると、複数のxlsxファイルをまとめ、最新月の売上降順になったファイルを作成するプログラムを書いてほしい。」

取り組み方の例

  1. globモジュールを使い対象となる全てのxlsxファイル名を取得
  2. read_excelを用い、フォルダ名'支店ファイル'内にある全てのxlsxファイルを読み込み一つのデータフレームにする
  3. 最新月(今回は3月)で値が高い順に並び替え
  4. applymapや関数を用いて、列名"備考"の上位5名に賞与ありと記述
  5. 『最新月社員別売上データ.xlsx』というファイル名で出力をする。indexはFalse
  6. os.renameメソッドを使い、フォルダ名'支店ファイル'内にある全てのファイルに"処理済み"とファイル名を書き換える

[問3] mergeを使ってデータを結合しよう

人事部長:

「ありがとう。せっかく作ってもらったのだけれど、
実は『売上データ.xlsx』というもの全体の売上データが既にあることをすっかり忘れてしまっていた。
ごめんなさい💦。
本部へこの売上データに'氏名'の列を追加した提出用ファイルを作成してくれないだろうか?
ついでと言ってはだけれど、"3月報告ファイル"という名前の新規フォルダを作り、
そこに提出用ファイルを置いてほしい。
それから、『売上データ.xlsxのファイル』は必要ないので削除しておいてほしい」

取り組み方の例

  1. 『売上データ.xlsx』と問1で作成した『第4四半期各社員売上データ.xlsx』を開く
  2. 『第4四半期各社員売上データ.xlsx』のカラム名'社員番号'を'社員ID'にrenameする
  3. 二つのデータフレームを'社員ID'でmergeする
  4. mergeしたデータから余分な列を削除して列名は['社員ID', '氏名','1月売上', '2月売上', '3月売上', '備考欄']だけにreindexする
  5. 『氏名入り売上データ.xlsx』というファイル名で出力する。indexはFalse
  6. os.mkdirメソッドで新しいフォルダを作成し、'3月報告ファイル'という名前にする
  7. shutil.moveメソッドで『氏名入り売上データ.xlsx』ファイルを'3月報告ファイル'フォルダに移動させる
  8. os.removeメソッドを使い既存の『売上データ.xlsx』を削除する

[問4] チャレンジ

人事部長:

「ちょっと難しいのだけれども人の手で行うのはとても大変なので、もしトライできるならプログラミングでやってみてほしい。
先ほどの月売上の賞与について、全社員を売上top5でランキングをつけるのは少し違和感も覚える。当然、地域性も関わるし、個人の頑張り具合も異なるだろう。
なのでもう少しデータを加工して評価項目を増やしたいと思うんだ。そこで下記が自分の考えた指標でデータに取り込んでほしい。

1. 各支店ごとの売上データで、3月売上上位5%に入る者に +5 点
1. 各支店ごとの売上データで、3月売上平均より高い者に +3 点
1. 全体売上データで、3月売上上位5%に入る者に +5 点
1. 全体売上データで3月売上平均より高い者に +3 点
1. 全体売上データで 1月,2月,3月それぞれの売上平均より毎回高い売上を上げている者 +3点
1. 所属支店が"東京"と"大阪"のものに +1点

この6つの指標の合計で降順に並び替えをし、同得点の場合は3月の売上が高い順に並び替えてもらいたい。
そして、この結果を各支店の支店長にも配布をしたいので、各所属支店ごとにxlsx形式のファイルへ分割してもらいたい。
そのファイルらは新しいフォルダを1つ用意してそこに置いといてほしい。

取り組み方の例

これまでの学習を用いてのチャレンジ問題です。『第4四半期各社員売上データ.xlsx』のファイルを読み込むところからスタートし、コードを書くプロセスを考えて最後まで自分で書いてみましょう。

この課題に使われる関数メソッド

問1
・Excelファイルの読み込み・・・pandas.read_excelメソッド
・データフレームのカラムの要素で並び替え・・・pandas.DataFrame.sort_valuesメソッド
・重複しないカラムの抽出・・・pandas.DataFrame.uniqueメソッド
・データフレームをExcelファイルに書き出し・・・pandas.DataFrame.to_excel

問2
・Excelファイルの読み込み・・・pandas.read_excelメソッド
・データフレームのカラムの要素で並び替え・・・pandas.DataFrame.sort_valuesメソッド
・データフレームのコピーを作成・・・pandas.DataFrame.appendメソッド
・データフレームのコピーを作成・・・pandas.DataFrame.concatメソッド
・データフレームの要素に関数を適用させる・・・pandas.DataFrame.applaymapメソッド
・データフレームをExcelファイルに書き出し・・・pandas.DataFrame.to_excel

問3
・Excelファイルの読み込み・・・pandas.read_excelメソッド
・ファイル名を変更する方法・・・pandas.renameメソッド
・データフレームのマージ・・・pandas.Dataframe.mergeメソッド
・データフレームのカラムやインデックスを削除・・・pandas.DataFrame.dropメソッド
・データフレームをExcelファイルに書き出し・・・pandas.DataFrame.to_excel

この課題に対応するキノクエストのレッスン

Pandas講座 LESSON1
データフレーム (DataFrame) とは
・四則演算

Pandas講座 LESSON3
CSV・Excelファイルの読み込み・書き出し、データベースとの接続方法
・Excelファイルの読み込み・・・pandas.read_excelメソッド
・データフレームをExcelファイルに書き出し・・・pandas.DataFrame.to_excel

Pandas講座 LESSON4
データ抽出の方法
・カラムの抽出

Pandas講座 LESSON5
データの並び替えの方法
・データフレームのカラムの要素で並び替え・・・pandas.DataFrame.sort_valuesメソッド

Python超入門講座 LESSON5
演算子
・演算子(==, &, >=, /)

Python超入門講座 LESSON7
繰り返し
・同じ処理を繰り返し実行・・・for文

Pandas講座 LESSON8
データフレームの結合方法(merge)
・同じ名前のカラムをキーに結合
・重複カラム名の指定方法

Pandas講座 LESSON9
データフレームの結合方法(concat)
・2つのデータフレームを結合