LoginSignup
83
81

More than 1 year has passed since last update.

@NasuPanda

ChatGPTを使用したExcel自動化のチュートリアル by Python

Posted at

流行りのChatGPTで記事を書いてみたかったので、自動化ネタの定番「Excel操作」をChatGPT君にやってもらうことにしました。

使用するExcelファイル

こちらから拝借しました。1000行のデータが含まれています。

Pasted image 20230114164956.png

複数のExcelファイルを結合してみる

下のような5つのExcelファイルがあると仮定して、中身を結合してもらいます。

image.png

ChatGPTとの対話は --- で囲み、入力内容はコードブロックで示すようにします。

input
〇〇して

ChatGPTの返答
︙

input
5つのExcelファイルがあります。
名前は "sampleDB_1" "sampleDB_2"..."sampleDB_5" です。
Pythonを使って、5つのExcelファイルに含まれるデータを結合して下さい。

パッケージ管理には、pipenvを使用しています。

PythonでExcelファイルを扱うには、「pandas」と「openpyxl」の2つのパッケージを使用することができます。

まず、pipenvを使って、pandasとopenpyxlをインストールします。

pipenv install pandas
pipenv install openpyxl

次に、以下のようにPythonスクリプトを書きます。

import pandas as pd

# 5つのExcelファイルを読み込む
df1 = pd.read_excel("sampleDB_1.xlsx")
df2 = pd.read_excel("sampleDB_2.xlsx")
df3 = pd.read_excel("sampleDB_3.xlsx")
df4 = pd.read_excel("sampleDB_4.xlsx")
df5 = pd.read_excel("sampleDB_5.xlsx")

# 5つのデータフレームを結合する
result = pd.concat([df1, df2, df3, df4, df5])

# 結合したデータをExcelファイルに書き出す
result.to_excel("result.xlsx", index=False)

上記のスクリプトでは、5つのExcelファイルを読み込んで、pandasのDataFrameに変換し、それらを結合し、最終的にresult.xlsxという名前で保存しています。

  • "sampleDB_1" "sampleDB_2"..."sampleDB_5" という書き方でも正しく指定出来ていますね。命名規則があって、ファイル数が多い時なんかは重宝しそう。
  • パッケージ管理に pipenv を使っているのでそのことを書いてみたら、当然のように対応してきました。怖い。

狙い通りのコードがでてきましたが、Excelファイルを1つずつ読み込むのは流石に冗長です。
配列を使って書き直してもらいましょう。

input
ファイル数が変わっても対応出来るように、配列を使った形に書き直して下さい。

以下のように、配列を使って、複数のExcelファイルを結合するスクリプトを書くことができます。

import pandas as pd

# Excelファイルのリスト
file_list = ["sampleDB_1.xlsx", "sampleDB_2.xlsx", "sampleDB_3.xlsx", "sampleDB_4.xlsx", "sampleDB_5.xlsx"]

# Excelファイルを読み込む
df_list = [pd.read_excel(f) for f in file_list]

# Excelファイルを結合する
result = pd.concat(df_list)

# 結合したデータをExcelファイルに書き出す
result.to_excel("result.xlsx", index=False)

上記のスクリプトでは、file_listという配列にExcelファイル名を格納して、それを使って、複数のExcelファイルを読み込んで結合しています。
そのため、Excelファイルの数が増えた場合は、file_list配列に追加するだけで対応することができます。

対応してきますね。もう少し色々注文してみます。

input
ファイル名のルールに変更が入った時に備えて、フォーマット文字列を使う形に変更して下さい。

フォーマット文字列を使用することで、Excelファイル名のルールが変わった場合にも対応することができます。 以下に、フォーマット文字列を使用して、複数のExcelファイルを結合するスクリプトを示します。

import pandas as pd

# Excelファイルのフォーマット文字列
file_format = "sampleDB_{}.xlsx"

# Excelファイルのリスト
file_list = [file_format.format(i) for i in range(1, 6)]

# Excelファイルを読み込む
df_list = [pd.read_excel(f) for f in file_list]

# Excelファイルを結合する
result = pd.concat(df_list)

# 結合したデータをExcelファイルに書き出す
result.to_excel("result.xlsx", index=False)

上記のスクリプトでは、file_formatという変数にExcelファイル名のフォーマット文字列を格納し、それを使って、file_list配列にExcelファイル名を生成しています。
これにより、Excelファイル名のルールが変わった場合は、file_format変数を変更するだけで対応することができます。

ただし、上記の例では1から5までの5つのファイルを対象にしているため、もしファイル数が変わった場合には、file_list配列の中身を修正する必要がある。

input
file_list への代入時にf文字列を直接使用するようにしてください。

f文字列を直接file_list配列に格納することで、Excelファイル名のフォーマット文字列を一度変数に格納する必要がなくなります。
以下に、f文字列を直接使用して、複数のExcelファイルを結合するスクリプトを示します。

import pandas as pd

# Excelファイルのリスト
file_list = [f"sampleDB_{i}.xlsx" for i in range(1, 6)]

# Excelファイルを読み込む
df_list = [pd.read_excel(f) for f in file_list]

# Excelファイルを結合する
result = pd.concat(df_list)

# 結合したデータをExcelファイルに書き出す
result.to_excel("result.xlsx", index=False)

上記のスクリプトでは、file_list配列にf文字列を使用してExcelファイル名を直接生成しています。
これにより、Excelファイル名のルールが変わった場合は、file_list配列を修正するだけで対応することができます。

ただし、上記の例では1から5までの5つのファイルを対象にしているため、もしファイル数が変わった場合には、file_list配列の中身を修正する必要がある。

input
ファイル数や出力するファイルの名前に変更があった場合に備えて、コード内での変更箇所を集中させることが望ましいです。

可能な限り、コード内での変更箇所を集中させることで、変更に対応する際の修正箇所を絞り込むことができます。
そのため、以下のように、変数を使用して、ファイル数や出力するファイルの名前を変更することができます。

import pandas as pd

# Excelファイル数
file_num = 5

# Excelファイルのフォーマット文字列
file_format = "sampleDB_{}.xlsx"

# Excelファイルのリスト
file_list = [f"{file_format.format(i)}" for i in range(1, file_num+1)]

# Excelファイルを読み込む
df_list = [pd.read_excel(f) for f in file_list]

# Excelファイルを結合する
result = pd.concat(df_list)

# 結合したデータをExcelファイルに書き出す
output_file = "result.xlsx"
result.to_excel(output_file, index=False)

上記のスクリプトでは、file_num変数にExcelファイル数、output_file変数に出力するExcelファイル名を格納しています。 これにより、ファイル数や出力するファイルの名前が変更された場合は、それらの変数を修正するだけで対応することができます。

input
コードを関数化してください

以下のように、上記のスクリプトを関数化することができます。

import pandas as pd

def combine_excel_files(file_num, file_format, output_file):
    # Excelファイルのリスト
    file_list = [f"{file_format.format(i)}" for i in range(1, file_num+1)]
    
    # Excelファイルを読み込む
    df_list = [pd.read_excel(f) for f in file_list]
    
    # Excelファイルを結合する
    result = pd.concat(df_list)
    
    # 結合したデータをExcelファイルに書き出す
    result.to_excel(output_file, index=False)

# sample usage
combine_excel_files(5, "sampleDB_{}.xlsx", "result.xlsx")

上記のスクリプトでは、combine_excel_files関数に3つの引数を取ります。 1つ目はExcelファイルの数、2つ目はExcelファイル名のフォーマット文字列、3つ目は出力するExcelファイル名です。
この関数を呼び出すことで、複数のExcelファイルを結合し、結合したデータを指定した名前で書き出すことができます。
これにより、ファイル数や出力するファイルの名前が変更された場合は、関数を呼び出す際の引数を修正するだけで対応することができます。

input
この関数のドキュメントを Google style で出力して下さい

Google形式のドキュメントでは、関数やクラスの説明、引数、戻り値、副作用などについて記述することが求められます。
以下に、combine_excel_files関数についてのGoogle形式のドキュメントを示します。

def combine_excel_files(file_num: int, file_format: str, output_file: str) -> None:
    """
    複数のExcelファイルを結合し、結合したデータを指定した名前で書き出します。

    Args:
        file_num (int): Excelファイルの数。
        file_format (str): Excelファイル名のフォーマット文字列。
        output_file (str): 結合したデータを書き出すExcelファイル名。

    Returns:
        None
    """

関数化、ドキュメントの出力まで出来ました。すごい。
f文字列のような特定機能にまで対応してくれるとは予想外でした。

最終的に生成されたコードは以下です。

import pandas as pd

def combine_excel_files(file_num: int, file_format: str, output_file: str) -> None:
    """
    複数のExcelファイルを結合し、結合したデータを指定した名前で書き出します。

    Args:
        file_num (int): Excelファイルの数。
        file_format (str): Excelファイル名のフォーマット文字列。
        output_file (str): 結合したデータを書き出すExcelファイル名。

    Returns:
        None
    """
    # Excelファイルのリスト
    file_list = [f"{file_format.format(i)}" for i in range(1, file_num+1)]
    
    # Excelファイルを読み込む
    df_list = [pd.read_excel(f) for f in file_list]
    
    # Excelファイルを結合する
    result = pd.concat(df_list)
    
    # 結合したデータをExcelファイルに書き出す
    result.to_excel(output_file, index=False)

combine_excel_files(5, "sampleDB_{}.xlsx", "result.xlsx")

実行してみると、次のようなExcelファイルが出力されます。

image.png

合計5001行になっています。
1000行データがあるファイル × 5 だったので、結合出来ていそうです。

Excel関数を書き込む

Excelといえば関数ですよね。

Pasted image 20230114164956.png

F列の利用金額の平均・合計を求める関数でも書いてもらいましょう。

input
ディレクトリ配下に複数のExcelファイルが置かれています。 

openpyxlを使って、全てのExcelファイルに以下の数式を適用してください。
- F3セルからF1001セルまでの合計をF1002セルに表示する
- F3セルからF1001セルまでの平均をF1003セルに表示する

openpyxlを使用してExcelファイルに対して数式を適用することができます。

import os
from openpyxl import load_workbook

# Excelファイルが存在するディレクトリ
directory = "path/to/directory"

# Excelファイルのリストを取得
file_list = [f for f in os.listdir(directory) if f.endswith(".xlsx")]

for file in file_list:
    # Excelファイルを読み込む
    wb = load_workbook(os.path.join(directory, file))
    ws = wb.active
    
    # F3からF1001までの合計をF1002セルに表示
    ws.cell(row=3, column=6, value='=SUM(F3:F1001)')
    
    # F3からF1001までの平均をF1003セルに表示
    ws.cell(row=4, column=6, value='=AVERAGE(F3:F1001)')
    
    # Excelファイルに書き出す
    wb.save(os.path.join(directory, file))

上記のスクリプトでは、openpyxlを使用してExcelファイルを読み込み、F3からF1001までの合計、平均を算出し、それぞれF3,F4に表示するようにします。

コメントには # F3からF1001までの合計をF1002セルに表示 と書いてあるのに、出力先のセルがおかしなことになってますね。なぜ。

修正をかけます。

input
ws.cell(row=3, column=6)の引数が想定していないものになっています。
合計、平均を出力したいセルはそれぞれF1002、F1003です。

そうですね、F1002、F1003セルに出力するように修正します。

import os
from openpyxl import load_workbook

# Excelファイルが存在するディレクトリ
directory = "path/to/directory"

# Excelファイルのリストを取得
file_list = [f for f in os.listdir(directory) if f.endswith(".xlsx")]

for file in file_list:
    # Excelファイルを読み込む
    wb = load_workbook(os.path.join(directory, file))
    ws = wb.active
    
    # F3からF1001までの合計をF1002セルに表示
    ws.cell(row=1002, column=6, value='=SUM(F3:F1001)')
    
    # F3からF1001までの平均をF1003セルに表示
    ws.cell(row=1003, column=6, value='=AVERAGE(F3:F1001)')
    
    # Excelファイルに書き出す
    wb.save(os.path.join(directory, file))

上記のスクリプトでは、F1002、F1003セルにそれぞれの合計、平均を出力するように修正しました。

input
このスクリプトの処理について、Markdown形式で説明してください。

以下に、上記のスクリプトの処理について説明します。

  1. import os 、 from openpyxl import load_workbook ライブラリをインポートします。
  2. 指定したディレクトリ配下に存在するExcelファイルのリストを取得します。
  3. 取得したExcelファイルのリストを順に処理します。
  4. 処理対象のExcelファイルを読み込みます。
  5. F3からF1001までの合計をF1002セルに出力するために、'=SUM(F3:F1001)'の計算式をF1002セルに入力します。
  6. F3からF1001までの平均をF1003セルに出力するために、'=AVERAGE(F3:F1001)'の計算式をF1003セルに入力します。
  7. 処理を行ったExcelファイルを保存します。
  8. 次のExcelファイルに対して処理を繰り返します。

上記の処理は、指定したディレクトリ配下に存在するExcelファイルに対して、F3からF1001までの合計、平均をF1002、F1003に出力するものです。

出来上がったコードを実行してみました。
Pasted image 20230114195955.png
OKですね。

日付の書式を変える

同じサイトから、今度は別のファイルを拝借してきました。

Pasted image 20230114201342.png

birthday 列の書式を変えてみようと思います。

input
- ディレクトリの配下に複数のcsvファイルが置かれています。 
- `birthday` 列には日付が書かれています。
- 現在の書式は `yyyy/mm/dd` です。 

`yyyy/mm/dd(aaa)`に変更するコードを生成して下さい。

次のように、Pythonでcsvファイルのbirthday列を新しい日付形式に変更することができます。

import os
import pandas as pd

# csvファイルが存在するディレクトリ
directory = "path/to/directory"

# csvファイルのリストを取得
file_list = [f for f in os.listdir(directory) if f.endswith(".csv")]

for file in file_list:
    # csvファイルを読み込む
    df = pd.read_csv(os.path.join(directory, file))
    df['birthday'] = df['birthday'].apply(lambda x: x.strftime('%Y/%m/%d(%a)'))
    df.to_csv(os.path.join(directory, file), index=False)

上記のスクリプトでは、指定したディレクトリ配下に存在するcsvファイルを取得し、それらに対してbirthday列の日付形式を 'yy/mm/dd(aaa)'に変更するようにしています。

df['birthday'] = df['birthday'].apply(lambda x: x.strftime('%Y/%m/%d(%a)')) の部分で、 birthday列の値をそれぞれに対して lambda関数を適用し、'yyyy/mm/dd(aaa)'の形式で変更しています。
df.to_csv(os.path.join(directory, file), index=False)でcsvファイルに出力しています。

日付型への変換を忘れているので、そのまま実行するとエラーが出ます。
指摘してみましょう。

input
そのまま実行したところ、「AttributeError: 'str' object has no attribute 'strftime'」というエラーメッセージが表示されました。 型変換を忘れていませんか？

申し訳ありません。そうですね、'str'型のオブジェクトには'strftime'属性がありません。そのため、日付型に変換する必要があります。
次のように、日付型に変換した上で、新しい日付形式に変更することができます。

import os
import pandas as pd

# csvファイルが存在するディレクトリ
directory = "path/to/directory"

# csvファイルのリストを取得
file_list = [f for f in os.listdir(directory) if f.endswith(".csv")]

for file in file_list:
    # csvファイルを読み込む
    df = pd.read_csv(os.path.join(directory, file))
    df['birthday'] = pd.to_datetime(df['birthday']).dt.strftime('%Y/%m/%d(%a)')
    df.to_csv(os.path.join(directory, file), index=False)

pd.to_datetime(df['birthday'])でbirthday列をdatetime型に変換し、.dt.strftime('%Y/%m/%d(%a)')で新しい形式に変換しています。
再度申し訳ありません。ご迷惑をおかけしました。

謝罪がすごい…
間違いがあったとしても、適切に指摘すれば修正してくれるようです。優秀ですね。

ついでに以下2点について、どんな回答が来るのか気になったので聞いてみます。

  1. エラーの解決方法
  2. 特定のメソッドの使い方
input
そのまま実行したところ、「FileNotFoundError: [Errno 2] No such file or directory: 'path/to/directory'」というエラーメッセージが表示されました。 
どうすれば解決しますか。

それは、'path/to/directory'が存在しないために発生しているエラーです。 そのため、実際に存在するファイルパスに変更してください。 もしくは、実行する際に、そのパスを指定することで解決することができます。

directory = input("Enter the directory path: ")

上記のように、input()関数を使用して、実行時にファイルパスを入力することでも解決することができます。 また、もしも存在するのにも関わらず、エラーが発生する場合は、アクセス権の問題かもしれませんので、その点も確認してみてください。

input
Pythonのdatetimeの `strftime` メソッドで利用可能なフォーマットを全て表示して下さい。

Pythonのdatetimeのstrftimeメソッドで利用可能なフォーマットには次のようなものがあります。

  • %a : 曜日の省略形 (Sun, Mon, Tue, …)
  • %A : 曜日の名前 (Sunday, Monday, Tuesday, …)
  • %w : 曜日番号 (0: 日曜日, 1: 月曜日, …, 6: 土曜日)
  • %d : 日 (01, 02, …, 31)
  • %b : 月の省略形 (Jan, Feb, …, Dec)
  • %B : 月の名前 (January, February, …, December)
  • %m : 月 (01, 02, …, 12)
  • %y : 2桁の年 (00, 01, …, 99)
  • %Y : 4桁の年 (1900, 1901, …, 9999)
  • %H : 時 (00, 01, …, 23)
  • %I : 12時間制の時 (01, 02, …, 12)
  • %M : 分 (00, 01, …, 59)
  • %S : 秒 (00, 01, …, 59)
  • %f : マイクロ秒 (000000, …, 999999)
  • %z : タイムゾーンのオフセット (例: +0900)
  • %Z : タイムゾーンの名前 (例: JST)
  • %j : 年中の経過日 (001, …, 365)
  • %U : 週番号 (00, 01, …, 53) (日曜日を最初の日としてカウント)
  • %W : 週番号 (00, 01, …, 53) (月曜日を最初の日としてカウント)
  • %u : 曜日番号 (1: 月曜日, 2: 火曜日, …, 7: 日曜日)
  • %x : 日付のローカル表記 (例: 01/02/06)
  • %X : 時刻のローカル表記 (例: 14:30:00)
  • %% : %

これらがPythonのdatetimeのstrftimeメソッドで利用可能なフォーマットです。 例えば、datetime.now().strftime('%Y/%m/%d %H:%M:%S') は、現在の日時を "yyyy/mm/dd hh:flag_mm:ss" の形式で出力することができます。

うーん、完璧ですね。

最後に、先程出力されたコードを実行してみます。
結果がこちら。言うこと無しですね。

Pasted image 20230114204548.png

まとめ

やはりChatGPTは凄まじいですね。
「Pythonで業務を自動化してラクしよう」というレベルのタスクであれば、1行もコードを書くこと無くこなせてしまいました。

とはいえ、過信は禁物ですね。この記事でも数カ所のミスが見られましたし、「非プログラマがChatGPTを使って業務自動化」というのはまだ少し厳しい気がします。

83
81
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
83
81