最近は朝型にシフトしてウォーキングを始めました。菅野です。

皆さんは日々の業務でどれぐらいExcelを用いているでしょうか？
表計算ソフトであるExcelですが、計算のみならず、グラフ描画や、文章を表形式でまとめたり、マニアックな使い方ではアニメーションの作成までできてしまいます。
エンジニア以外の方も業務で使用することが多いのではないでしょうか？

しかしながら、業務上でExcelを用いると、日々の煩雑な作業が多くなりやすい印象です。
エンジニアであればVBA等を調べてマクロを作るといったことも可能ですが、一般の人にはハードルが高くなってしまいがちです。
今回はそんなExcelを用いた業務をChatGPTにPython スクリプトを作ってもらうことで効率化してみましょう。

今回のテーマではGPT-4のモデルを使用します。
また、CodeInterpreterで対象のExcelファイルを読み込ませていますがChatGPTに直接分析をしてもらうのではなく、ローカルで日常的に動作させ、日々のExcel集計作業を効率化するPython スクリプトを作ってもらうことを主題とします。

今回利用するサンプルデータ

今回利用するサンプルデータはWebサイトのアクセスデータをイメージしたものになっています。
時間ごとのアクセス数などが記載されたファイルが1日1ファイル作成される想定です。

日ごと出力されるExcelファイルを一つのExcelにマージする。

まずはバラバラに出力されているExcelファイルを1ファイルにマージしてみましょう。

ChatGPTのCodeInterpreterを用いることで、対象のExcelファイルを渡してマージするように依頼をするだけで
Python スクリプトを作成してくれました。
また、単純マージだけでなく、合計、平均をとるなど、集計方法の提案までしてくれていますね。

ChatGPTが出力してくれたコードがこちら。

import pandas as pd
import os
import argparse

def merge_excel_files(directory, output_file):
    # Get a list of all Excel files in the directory
    files = [f for f in os.listdir(directory) if f.endswith(".xlsx") or f.endswith(".xls")]

    # Read each file and append it to the list
    dataframes = []
    for file in files:
        df = pd.read_excel(os.path.join(directory, file))
        dataframes.append(df)

    # Concatenate all dataframes in the list
    merged_df = pd.concat(dataframes)

    # Write the merged DataFrame to an Excel file
    merged_df.to_excel(output_file, index=False)

    print(f"Merged Excel file has been saved to: {output_file}")

def main():
    parser = argparse.ArgumentParser(description='Merge Excel files in a directory.')
    parser.add_argument('directory', type=str, help='The directory where the Excel files are located.')
    parser.add_argument('output_file', type=str, help='The path where the merged file will be saved.')
    args = parser.parse_args()

    merge_excel_files(args.directory, args.output_file)

if __name__ == "__main__":
    main()

以下のコマンドで動作します

python merge_excel.py ./access_data ./merge_result.xlsx

無事意図通りのファイルが出力されました。

集計シートも作成する

1ページ目には生データを保存しつつ、２シート目には集計結果も保存してもらいましょう。

ChatGPTが出力してくれたコードがこちら。

import pandas as pd
import os
import argparse

def merge_excel_files(directory, output_file):
    # Get a list of all Excel files in the directory
    files = [f for f in os.listdir(directory) if f.endswith(".xlsx") or f.endswith(".xls")]

    # Read each file and append it to the list
    dataframes = []
    for file in files:
        df = pd.read_excel(os.path.join(directory, file))
        dataframes.append(df)

    # Concatenate all dataframes in the list
    merged_df = pd.concat(dataframes)

    # Write the merged DataFrame to an Excel file
    with pd.ExcelWriter(output_file) as writer:
        merged_df.to_excel(writer, sheet_name='Merged Data', index=False)
        
        # Group by date and calculate the aggregate values
        merged_df['日時'] = pd.to_datetime(merged_df['日時']).dt.date
        grouped_df = merged_df.groupby('日時').agg({
            '訪問者数': 'sum',
            '新規訪問者数': 'sum',
            'ページビュー数': 'sum',
            '平均滞在時間': 'mean',
            'コンバージョン数': 'sum'
        })

        # Recalculate bounce rate
        grouped_df['直帰率'] = merged_df.groupby('日時').apply(lambda x: (x['直帰率'] * x['訪問者数']).sum() / x['訪問者数'].sum())
        
        # Write the grouped DataFrame to the second sheet of the Excel file
        grouped_df.to_excel(writer, sheet_name='Daily Summary')

    print(f"Merged and summarized Excel file has been saved to: {output_file}")

def main():
    parser = argparse.ArgumentParser(description='Merge Excel files in a directory.')
    parser.add_argument('directory', type=str, help='The directory where the Excel files are located.')
    parser.add_argument('output_file', type=str, help='The path where the merged file will be saved.')
    args = parser.parse_args()

    merge_excel_files(args.directory, args.output_file)

if __name__ == "__main__":
    main()

指定した内容ですぐにスクリプトを修正してくれました。
単純な合計、平均だけでなく、直帰率と訪問者数から合計直帰数を算出、合計訪問者数で割って集計データの日ごと直帰率も正しく再計算してくれているのはポイントが高いですね。

以下のコマンドで実行します。

python merge_excel_aggregate.py ./access_data ./merge_result_aggregated.xlsx

このように集計結果も出力することができました。

グラフを作成する

レポートとして報告する場合、グラフを作成して視覚的にした方がより相手に伝わりやすくなります。
グラフ作成も自動で行うように変更してもらいましょう。

以下のスクリプトを作成してくれました。

import pandas as pd
import os
import argparse
from openpyxl import load_workbook
from openpyxl.chart import LineChart, Reference

def merge_excel_files(directory, output_file):
    # Get a list of all Excel files in the directory
    files = [f for f in os.listdir(directory) if f.endswith(".xlsx") or f.endswith(".xls")]

    # Read each file and append it to the list
    dataframes = []
    for file in files:
        df = pd.read_excel(os.path.join(directory, file))
        dataframes.append(df)

    # Concatenate all dataframes in the list
    merged_df = pd.concat(dataframes)

    # Write the merged DataFrame to an Excel file
    with pd.ExcelWriter(output_file) as writer:
        merged_df.to_excel(writer, sheet_name='Merged Data', index=False)
        
        # Group by date and calculate the aggregate values
        merged_df['日時'] = pd.to_datetime(merged_df['日時']).dt.date
        grouped_df = merged_df.groupby('日時').agg({
            '訪問者数': 'sum',
            '新規訪問者数': 'sum',
            'ページビュー数': 'sum',
            '平均滞在時間': 'mean',
            'コンバージョン数': 'sum'
        })

        # Recalculate bounce rate
        grouped_df['直帰率'] = merged_df.groupby('日時').apply(lambda x: (x['直帰率'] * x['訪問者数']).sum() / x['訪問者数'].sum())
        
        # Write the grouped DataFrame to the second sheet of the Excel file
        grouped_df.to_excel(writer, sheet_name='Daily Summary')

    print(f"Merged and summarized Excel file has been saved to: {output_file}")

    # Load the workbook and select the summary sheet
    wb = load_workbook(output_file)
    ws = wb['Daily Summary']

    # Create a line chart for visitors and conversions
    chart = LineChart()
    dates = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
    visitors = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
    conversions = Reference(ws, min_col=6, min_row=1, max_row=ws.max_row)
    chart.add_data(visitors, titles_from_data=True)
    chart.add_data(conversions, titles_from_data=True)
    chart.set_categories(dates)
    chart.title = "Daily Visitors and Conversions"
    chart.y_axis.title = "Count"
    chart.x_axis.title = "Date"

    # Add the chart to the worksheet
    ws.add_chart(chart, "H2")

    # Save the workbook
    wb.save(output_file)

def main():
    parser = argparse.ArgumentParser(description='Merge Excel files in a directory.')
    parser.add_argument('directory', type=str, help='The directory where the Excel files are located.')
    parser.add_argument('output_file', type=str, help='The path where the merged file will be saved.')
    args = parser.parse_args()

    merge_excel_files(args.directory, args.output_file)

if __name__ == "__main__":
    main()

openpyxlというライブラリを使って、折れ線グラフを描画するコードが追加されています。

以下のコマンドで実行します。

python merge_excel_aggregate_graph.py ./access_data ./merge_result_aggregated_graph.xlsx

このようにグラフも追加することができるようになりました。

まとめ

ChatGPTを用いることでExcelファイルを利用する日々の業務を効率化するPython スクリプトを作成してもらいました。
簡単な指示を入れるだけで意図通りのマージ、集計処理をしてくれるスクリプトができるのはありがたいです。
今回は単純なマージや集計、グラフ作成を実施しましたが、複数のやり取りを重ねることでより複雑なエクセルを作成することもできそうです。
単純な作業を効率化するツール作成はChatGPTにお任せして、よりクリエイティブな作業にリソースを割いていきましょう。