Python から Excel を編集し､ピボットテーブルを作成する

Python を使って効率化したい

作業効率化のために､ Pandas で作成した Excel ファイルをピボットテーブルを作成し､フィルターの有効化や日時のグループ化をしたいと考えました｡

日本語での資料があまりなかったため､ご参考になればと思い共有します｡

参考資料に示した URL にピボットテーブルを作成する関数が示されているため､もっと色々なことをしたい場合はそちらを参考にする方が良いと思います｡

サンプルファイルの作成

特に意味のないピボットテーブル用のデータを作成します｡
エクセルファイルとして吐き出すファイル名を編集して下さい｡
指定したファイル名が同一ディレクトリにないよう注意してください｡
上書きされても責任はとれません｡

import pandas as pd 
import random
from datetime import datetime as dt

record = []
for y in range(2015, 2020):
    for m in range(1, 13):
        for shop in ['A', 'B', 'C']:
            date = dt(year=y, month=m, day=1).strftime('%Y-%m-%d')
            price = 200 + random.randint(0,100)    
            record.append([date, shop, price])

df_record = pd.DataFrame(record)
df_record.columns = ['date', 'shop', 'price']
df_record['date'] = pd.to_datetime(df_record['date'])
df_record.to_excel('ここを編集してください.xlsx', index=None)

ピボットテーブル作成

サンプルファイルの作成 で指定したファイル名を､読み込みファイル名にも指定して下さい｡

import win32com.client as win32
import os
win32c = win32.constants

## win32.Dispatch でもできたが､ pyinstaller で exe 化した際にエラーになったため､
## win32.gencache.EnsureDispatch を使用したほうが無難かもしれない
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = True  

## 読み込み時に絶対パスを指定しなければエラーになる
fpath = os.path.join(os.getcwd(),'ここを編集してください.xlsx')
wb = excel.Workbooks.Open(fpath)

## Sheet 1 指定し､フィルターを有効にする
wbs1 = wb.Sheets('Sheet1')
wbs1.Columns.AutoFilter(1)

## ピボットテーブルの作成
wbs2_name = 'pivot'
wb.Sheets.Add().Name = wbs2_name
wbs2 = wb.Sheets(wbs2_name)
pvt_name = 'pvt'
pc = wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=wbs1.UsedRange)
pc.CreatePivotTable(TableDestination='{sheet}!R3C1'.format(sheet=wbs2_name), TableName=pvt_name)

## ピボットテーブルの設定
wbs2.PivotTables(pvt_name).PivotFields('date').Orientation = win32c.xlRowField
wbs2.PivotTables(pvt_name).AddDataField(wbs2.PivotTables(pvt_name).PivotFields('price'), 'Ave/price', win32c.xlAverage).NumberFormat = '0'
wbs2.PivotTables(pvt_name).PivotFields('price').Orientation = win32c.xlPageField

## 日付のグループの選択
## Periods=(秒, 分, 時, 日, 月, 四半期, 年)
wbs2.Cells(4, 1).Select()
excel.Selection.Group(Start=True, End=True, Periods=(False, False, False, False, True, False, True))

## ファイルを閉じる
wb.Close(True)
excel.Quit()

参考資料

How to Create a Pivot Table in Excel with the Python win32com Module