Pandasでよくあるエクセル処理を自動化したい

エクセル業務を自動化する時に、Pandasで使うレシピです。
想定手順は

• ファイルの読み込み
• 各シートの結合
• 集計
• 見た目を整える
• 画像で出力

です

ファイルを読み込む

sheet_name = Noneにすることで、
全てのシートをdictで取得することができます。

import pandas as pd

dfs_dict = pd.read_excel("/file/path",sheet_name = None)

# シート名を特に必要としないなら
dfs = [dfs_dict[sheet_name] for sheet_name in dfs_dict]

▼複数ファイルを自動化する時に、対象ディレクトリを掘る場合

esu-ko.hatenablog.com

vlookupで対象を集める

pd.mergeを使います。
いい感じにキーを決定してくれるので便利ですが、キーを指定する場合も書いておきます。

#結合ルールを用意しておく
lookup_set = [('sheet1','sheet2'),('sheet3','sheet4')]
lookup_keys = [('sheet_1_key','sheet_2_key'),('sheet_3_key','sheet_4_key')]

lookups = []
#順に処理していく
for lookup_sheets,lookup_keys in zip(lookup_set,lookup_keys):
    res = pd.merge(lookup_sheets[0],lookup_sheets[1],left_on = lookup_keys[0],right_on=lookup_keys[1])
    lookups.append(res)

▼強引に複数joinする場合、再帰関数を作っておくと便利です。

esu-ko.hatenablog.com

集計を行う

エクセルで実施する集計はpivotが多いと思うのでpivot_tableで再現します。
総計も欲しがられることが多いので足しておきます。

agg_target = [('col'1,'row1'),('col1','row1')]

aggs = []
for df,agg_t in zip(lookups,agg_target):
    res = df.pivot_table(index=agg_t[0],columns=agg_t[1],agg_func='sum',margins=True, margins_name='total')
    aggs.append(res)

細かい処理をする

エクセルで行われるような報告は、月次成長率なんかが多そうなのでpct_changeを使います。
また、時系列を横方向に見たがる人も多いので、そちらに変更したりします。

fins = []

for df in aggs:
    tmp = df.pct_change()
    # 成長率を重ねる
    tmp.columns = [ col + '_pct' for col in tmp.columns]
    res = pd.concat(df,tmp,axis=1)

    #該当カラムの後ろに成長率が着くようにする
    sorts_col = sorted(res.columns)

    #列を並び変えて、（このデータが縦が時系列だったとして)、時間を列に転置する
    fins.append(res[sorts_col].T)

集計結果を画像で出力する

画像でくれ、と言われた場合、心を無にしてコードを書きます。

import matplotlib.pyplot as plt

for df,file_name in zip(fins,['image1.png','image2.png'])
    fig, ax = plt.subplots(figsize=(1,1))

    #表に関係ない部分を取り除く
    ax.axis('off')
    ax.axis('tight')

#テーブルの画像を生成
    ax.table(
        cellText=df.values, #値の設定
        colLabels=df.columns,#カラム名の設定
         rowLabels=df.index,#列名の設定
         loc='center')

    plt.savefig(file_name)

▼画像としてさらに弄りたい場合

esu-ko.hatenablog.com

以上です！