I want to improve efficiency using Python

To improve work efficiency, I wanted to create a pivot table for an Excel file created in Pandas, enable filters, and group dates and times.

I didn't have much material in Japanese, so I would like to share it as a reference.

The URL shown in Reference shows a function to create a pivot table, so if you want to do more things, you should refer to that.

Creating a sample file

Create data for a pivot table that doesn't make any sense. Edit the file name to be output as an Excel file. Make sure that the specified file name is not in the same directory. We cannot take responsibility even if it is overwritten.

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('Please edit here.xlsx', index=None)

Pivot table creation

Specify the file name specified in [Create sample file](#Create sample file) as the read file name.

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

## win32.I could do it with Dispatch, but I got an error when I made it into an exe with pyinstaller, so
## win32.gencache.It may be safer to use EnsureDispatch
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = True  

##If you do not specify the absolute path when reading, an error will occur
fpath = os.path.join(os.getcwd(),'Please edit here.xlsx')
wb = excel.Workbooks.Open(fpath)

##Specify Sheet 1 and enable the filter
wbs1 = wb.Sheets('Sheet1')
wbs1.Columns.AutoFilter(1)

##Creating a pivot table
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)

##Pivot table settings
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

##Date group selection
## Periods=(Seconds,Minutes,Time,Day,Month,quarter,Year)
wbs2.Cells(4, 1).Select()
excel.Selection.Group(Start=True, End=True, Periods=(False, False, False, False, True, False, True))

##Close file
wb.Close(True)
excel.Quit()