LoginSignup
12
13

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@sunadandy

pythonでoutlookのメールからデータを抽出し、Excelでグラフ化するスクリプトを作成した。

Last updated at Posted at 2019-10-14

タイトルだけ読むと「は？」ってなると思いますが、どうかお付き合いくださいm(_ _)m

動機

私は、Redmineを利用してチームのベロシティを測定し、それをメンバーにメールで展開しているのだが、1回の測定期間の結果だけでなく、過去からの推移をグラフ化して展開したいと考えていた。
また、マネージャ層に報告する時のことを考えると、Excelでデータを分析・蓄積しておく方が何かと都合がいい。
ただ、そうなると分析の度にRedmineからExcelにデータを取り込み、結果を更新しなければならず、ちょっと面倒くさい。
そこで、メールの内容をそのままExcelに流し、結果を更新する仕組みを作ってしまおうと思い至った。

環境(制約事項)

  • メーラー：Outlook 2016
  • Office：Excel 2016

仕様

メールの内容

データ抽出を容易にするため、ベロシティ分析に使用するデータは下記のようにラベルで囲って示すこととした。

～本文適当～

///START///
人数:3
期間(week):1
計画sp:34
消化sp:25
残sp:178
///END///

Excelデータの内容

  • Excelデータはpandasで出力するので基本的に無加工。
  • 過去4回分の結果をグラフ化する。(調整可能)

※下表の内容は適当に作ったサンプルです。
image.png

処理フロー

大まかな流れは以下の通りである。

  1. メール本文の特定箇所から必要な情報を抽出し、数値データに置き換える。
  2. Excelファイルから過去データをデータフレーム形式で取得
  3. 1の数値データを2のデータフレームに結合
  4. グラフで出力

１．Outlookのメール取得

「送信トレイ」にアクセスし、件名検索で対象メールを探し出す。
そのあとは力業でデータ取得。

object = win32com.client.Dispatch('Outlook.Application')
mapi = object.GetNamespace('MAPI')
# 送信トレイ選択
inbox = mapi.GetDefaultFolder(5)
# メール取得
mails = inbox.items
for mail in mails:
    # 特定の件名のメールを検索
    if SUBJECT in mail.subject:
        # メール本文から分析に使用する箇所を特定
        body = mail.body.split('\r\n\r\n')
        startindex = body.index("///START///")
        endindex = body.index("///END///")
        # データ抽出
        member = int(body[startindex+1][body[startindex+1].index(':')+1:])
        period = int(body[startindex+2][body[startindex+2].index(':')+1:])
        plan = int(body[startindex+3][body[startindex+3].index(':')+1:])
        done = int(body[startindex+4][body[startindex+4].index(':')+1:])
        remind = int(body[startindex+5][body[startindex+5].index(':')+1:])
        break

２．データフレームの結合

pandasを利用する。

# 過去情報の取得(ベースデータフレーム)
base_df = pd.read_excel(FILE)
# メールから取得したデータからデータフレーム作成
add_df = pd.DataFrame([
    [member, period, plan, done, remind],],
    columns = ["人数", "期間(week)", "計画sp", "消化sp", "残sp"])
# ベースデータフレームに追加(結合)
df = base_df.append(add_df, ignore_index=True, sort=False)

但し、この時点ではExcelデータは下表のような状態になっており、肝心のメトリクスがまだ空欄になっている。(5行目のデータを追加したイメージ)

image.png

なので、メトリクスを算出し、データフレームを更新する。

# 計画spと消化spの中央値を求める
plan = df['計画sp'].tolist()
actual = df['消化sp'].tolist()
planMedian = statistics.median(plan)
actualMedian = statistics.median(actual)
# データフレームを更新（NuN部分に値を代入）
df.loc[len(plan)-1, "計画sp(中央値)"] = planMedian
df.loc[len(plan)-1, "消化sp(中央値)"] = actualMedian

３．Excelグラフの作成

  • openpyxlを利用
  • ベロシティの推移が直感的に捉えられるように棒グラフと折れ線グラフを重ねてグラフ化する。
  • サンプルコードでは計画と実績の中央値をメトリクスとし、グラフ化する。
wb = openpyxl.load_workbook(FILE)
ws = wb.worksheets[0]
# グラフ選択、パラメータ設定
cht1 = openpyxl.chart.BarChart() # 棒グラフ
cht2 = openpyxl.chart.LineChart() # 折れ線グラフ
cht1.title = "velocity"
cht1.height = 12
cht1.width = 24
# グラフデータの参照先指定
values = openpyxl.chart.Reference(ws, min_col=9, min_row=len(plan)-lowerOffset, max_col=10, max_row=len(plan)+upperOffset)
cht1.add_data(values)
cht2.add_data(values)
cht1.legend = None
cht2.legend = None
# 2種類のグラフを重ねる
cht1 += cht2
# グラフを保存
ws.add_chart(cht1,"L2")
wb.save(FILE)

image.png

最終コード

毎度のことながら、個人利用なのでマジックナンバー多め。

import win32com.client
import pandas as pd
import openpyxl
import statistics

FILE = 'velocity.xlsx'
SUBJECT = ""

# 棒グラフ化するデータを制御するパラメータ。(upperOffset + lowerOffset + 1)個分のデータがグラフ化される
upperOffset = 1
lowerOffset = 2

# -----------------------------------------------------------------------
# メール本文からデータ取得
# -----------------------------------------------------------------------
object = win32com.client.Dispatch('Outlook.Application')
mapi = object.GetNamespace('MAPI')
# 送信トレイ選択
inbox = mapi.GetDefaultFolder(5)
# メール取得
mails = inbox.items
for mail in mails:
    # 特定の件名のメールを検索
    if SUBJECT in mail.subject:
        # メール本文から分析に使用する箇所を特定
        body = mail.body.split('\r\n\r\n')
        startindex = body.index("///START///")
        endindex = body.index("///END///")
        # データ抽出
        member = int(body[startindex+1][body[startindex+1].index(':')+1:])
        period = int(body[startindex+2][body[startindex+2].index(':')+1:])
        plan = int(body[startindex+3][body[startindex+3].index(':')+1:])
        done = int(body[startindex+4][body[startindex+4].index(':')+1:])
        remind = int(body[startindex+5][body[startindex+5].index(':')+1:])
        break

# -----------------------------------------------------------------------
# データフレーム更新
# -----------------------------------------------------------------------
# 過去情報の取得(ベースデータフレーム)
base_df = pd.read_excel(FILE)
# メールから取得したデータからデータフレーム作成
add_df = pd.DataFrame([
    [member, period, plan, done, remind],],
    columns = ["人数", "期間(week)", "計画sp", "消化sp", "残sp"])
# ベースデータフレームに追加(結合)
df = base_df.append(add_df, ignore_index=True, sort=False)
# 計画spと消化spの中央値を求める
plan = df['計画sp'].tolist()
actual = df['消化sp'].tolist()
planMedian = statistics.median(plan)
actualMedian = statistics.median(actual)
# データフレームを更新（NuN部分に値を代入）
df.loc[len(plan)-1, "計画sp(中央値)"] = planMedian
df.loc[len(plan)-1, "消化sp(中央値)"] = actualMedian
# ファイルに出力
df.to_excel(FILE)

# -----------------------------------------------------------------------
# グラフ作成
# -----------------------------------------------------------------------
wb = openpyxl.load_workbook(FILE)
ws = wb.worksheets[0]
# グラフ選択、パラメータ設定
cht1 = openpyxl.chart.BarChart() # 棒グラフ
cht2 = openpyxl.chart.LineChart() # 折れ線グラフ
cht1.title = "velocity"
cht1.height = 12
cht1.width = 24
# グラフデータの参照先指定
values = openpyxl.chart.Reference(ws, min_col=9, min_row=len(plan)-lowerOffset, max_col=10, max_row=len(plan)+upperOffset)
cht1.add_data(values)
cht2.add_data(values)
cht1.legend = None
cht2.legend = None
# 2種類のグラフを重ねる
cht1 += cht2
# グラフを保存
ws.add_chart(cht1,"L2")
wb.save(FILE)

改善の余地について

  • 分析のために一度メールを送り、スクリプトの実行が完了したら結果通知のためにもう一度メールを送る、というワークフローになっており無駄と言えば無駄である。
    • RedmineのデータをそのままExcelに流せばいいという意見があるかもしれないが、プラグイン依存のため実現可能性が不透明。
    • Excelデータの内容を読み取ってメールを自動送信する仕組みはアリ。
  • 同じファイルに対して、pandasでRead/Writeして、次にopenpyxlでRead/Writeという操作を行っており、非効率に感じる。
  • 系列情報やy軸の値をグラフに表記したい
    • matplotlibを使えば可能だが、できればopenpyxlで完結させたい。

おわりに

メトリクスについてはご意見が多々あると思いますが、本稿のテーマから逸れるためコメントはご遠慮ください。
実際に、メトリクスはノウハウに関わる部分なので、内容を変更して投稿しています。

12
13
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
12
13

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?