集計結果をExcelへ出力する方法を考える

# はじめに
この記事ではPandasを用いて集計した結果をExcelファイルに出力する方法を検討します。
特に体裁面がある程度求められる人のお役に立てればと思います。

実行環境はwindowsでpython3を用いています。

# 動機
皆様はExcelをどのように使用していますか？
私の周囲で主だった使用シーンは以下のようなものです。

1. 表計算ソフトとして簡易な計算を行う
2. VBAを用いて簡易システムを作る
3. 共有・プレゼンテーション用に表を装飾する

＃まったく使っていない人　←　裏山です

私は3の目的で使用することが多いです。
データの前処理や分析はpythonやRでおこなっても、
職務上、最終的にはPowerPointかExcelにせざるを得ない人が多いのではないでしょうか？

データ分析は前処理に掛かる工数が殆どだとよく言いますが、
実際のところ、単純な集計の場合には、ドキュメンテーションにかかる工数は無視できません。
このドキュメンテーションをどうすれば改善できるかを考えてみます。

# テストデータの作成
何でもよいのですが、企業によくありそうな日別のデータをランダムウォークで用意してみます。

```python3
import numpy as np
import pandas as pd
import random

# ランダムウォーク生成
def random_walk(length=100, seed=1234):
    random.seed(a=seed)
    x = [0]
    for j in range(length - 1):
        step_x = random.randint(0, 1)
        if step_x == 1:
            x.append(x[j] + 1 + 0.05*np.random.normal())
        else:
            x.append(x[j] - 1 + 0.05*np.random.normal())
    return x

dtidx = pd.date_range(start='2017-10-1', end='2017-12-31', freq='1d')

length = len(dtidx)
X = pd.DataFrame([random_walk(length, seed) for seed in [1, 10, 100]], 
                 columns=dtidx, index=['A', 'B', 'C']).T
```
3か月分のある３つの指標の動きとします。
<img src = "https://qiita-image-store.s3.amazonaws.com/0/189855/4f04b08a-e32a-4ce1-315e-422b12b9cf2f.png" width=80%>
これを月次で平均して出力することを考えます。

```python3
result = X.groupby(pd.Grouper(level=0, freq='1m')).mean() #月次で平均
result.index = result.index.to_series().apply(lambda t : t.strftime('%Y-%m')) #indexを文字列に変換
```
|		|	A	|	B	|	C	|
|	---	|	---	|	---	|	---	|
|	2017-10	|	-0.02	|	2.46	|	0.53	|
|	2017-11	|	0.03	|	5.35	|	-2.27	|
|	2017-12	|	3.81	|	3.50	|	-1.90	|
この表をどう装飾してExcelで出力するかを考えます。
（これだけシンプルだと装飾も何もあったもんではありませんが、例ということで）

# 案 : Excelのテンプレートを事前に作成しておく
先にデザインを作っておいて、集計表を貼り付けるパターン。
アウトプットの体裁が決まっていて、あとはデータを入れるだけという場合には有効です。
ここではopenpyxlを用いて、狙ったセルにデータを貼り付ける方法を紹介します。

下記のように、色や罫線を引いたExcelファイルを
template.xlsx
として用意しておきます。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/189855/4cde2f6b-736d-9582-b50a-2f78137056a6.png)

あとはopenpyxlを使うだけなのですが、
より手軽に扱えるよう、私はopenpyxlを簡単にラップしたモジュールを分析フォルダに置いています。

```python3:excelpaster.py
import pandas as pd
import re
import openpyxl as px
import itertools
from openpyxl.utils.dataframe import dataframe_to_rows

class ExcelPaster:
    def __init__(self, book_name):
        self.wb = px.load_workbook(book_name) #テンプレートの読み出し
        
        # セル番号と列名の対応を作成
        self.r_num = re.compile("([0-9]+)")
        self.r_letters = re.compile("([A-Z]+)")

        letters = [chr(i) for i in range(65, 65+26)]
        col_master = []
        for i in letters:
            col_master.append(i)
        for i, j in itertools.product(letters, letters):
            col_master.append(i + j)
        for i, j, k in itertools.product(letters, letters, letters):
            col_master.append(i + j + k)
        
        self.num2letters = pd.Series(col_master).to_dict()
        self.letters2num = pd.Series(col_master)\
        .rename('letters').reset_index().set_index('letters').to_dict()['index']
        
    def _end_cell_getter(self, df, start_cell, index, header):
        ncols = len(df.columns)
        if index == True:
            ncols  = ncols + df.index.nlevels
        nrows = len(df.index)
        if header == True:
            nrows = nrows + df.columns.nlevels

        start_col = self.r_letters.search(start_cell).group(0)
        end_col = self.num2letters[self.letters2num[start_col] + ncols - 1]
        start_row = int(self.r_num.search(start_cell).group(0))
        end_row = str(start_row + nrows - 1)
        return end_col + end_row
    
    def df_to_cells(self, data, sheet_name, start_cell, index=False, header=False):
        ws = self.wb.get_sheet_by_name(sheet_name)
        df = data.copy()
        if isinstance(df, pd.Series):
            df = pd.DataFrame(df)
        end_cell = self._end_cell_getter(df, start_cell, index, header)
        for cells, r in zip(ws[start_cell:end_cell], 
                            dataframe_to_rows(df, index=index, header=header)):
            for cnt, v in enumerate(r):
                cells[cnt].value = v
    
    def savefile(self, new_book_name):
        self.wb.save(new_book_name)
        
    def sheet_copy(self, original_sheet_name, new_sheet_name):
        ws = self.wb.get_sheet_by_name(original_sheet_name)
        ws = self.wb.copy_worksheet(ws)
        ws.title = new_sheet_name
```
その上で下記を実行します。

```python3
import excelpaster
ep = excelpaster.ExcelPaster('template.xlsx')
ep.df_to_cells(result, 'Sheet1', 'A1', header=True, index=True)
ep.savefile('result.xlsx')
```
![image.png](https://qiita-image-store.s3.amazonaws.com/0/189855/84ea371f-6657-7eae-4c5c-524659108bc2.png)

このようにopenpyxlを使えば、体裁が決まっている場合には、Excelを操作する手間が省けます。
pandasでto_clipboard()を使用してもよいのですが、
大量の表を貼り付ける場合に、ミスを発見することが難しくなります。
分析スクリプトが汚くなるのもいやですよね。

# おわりに
ここではExcelでテンプレートを用意する方法を提案しました。
pandasのstyleを使用する方法も紹介したかったのですが、
indexを装飾する方法が見当たらず、今回は断念しました。
（分析過程でstyleはとても便利なので、outputできると良いのですが）

マークアップ言語でレポートが通じる世の中になれば、こういった手間もなくなるのですけれども。
（Excelも要はXMLファイルですが）

誰かの助けになれば幸いです。