ChatGPTを使って、出欠情報と課題提出状況をPythonで集計してみる
後期の講義も終了し、成績評価のために出欠情報と課題提出状況を整理する時期になりました。私は 「有機化学 I」(1年生) と 「有機化学 IV」(2年生) の2つの講義を担当していますが、それぞれの講義で以下の方法で出欠確認と課題提出を管理しています。
• 出欠確認:QRコードをスマホで読み取らせ、Microsoft Forms 上で出席登録。
• 課題提出:毎回 A4 1枚の課題を出し、次回の講義前日までに Forms で提出。
これらの情報は、それぞれ Excelファイル として出力でき、ファイルには 学生名・学生メールアドレス・出欠情報or提出ファイル名 などが記載されています。
しかし、ここで問題が発生します。
• 各回のデータを履修者名簿と照合し、出欠・課題提出状況を確認するのは手間がかかる。
• 2つの講義を持っていると、この作業だけで 膨大な時間を取られてしまう。
そこで ChatGPT(有料版) を使い、出欠確認と課題提出状況の集計を自動化するスクリプト を作成してみました。
ChatGPT に出席確認と課題提出状況のスクリプトを作成させるためのプロンプト
「大学の授業における出席管理と課題提出の状況を自動集計する Python スクリプトを作成してください。
出席状況と課題提出状況を Excel ファイル(.xlsx)で管理しており、そのデータを使います」
いろいろと試行錯誤とかあったのですが、何回もトライ&エラーを繰り返しました。またPythonの実行などもChatGPTに教えてもらい。最終的には、次のように集計することができました。
参考までにPythonスクリプトも公開しますので、これをChatGPTに読み込ませて、ご自身の環境にあった自動化スクリプトを作成してみてはいかがでしょうか?
import pandas as pd
import glob
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
# 出席データのあるフォルダを指定
file_paths = sorted(glob.glob("./attendance_data/*.xlsx")) # 出席データの格納フォルダをソート
# 出席情報の統合用リスト
attendance_records = []
# 各ファイル(各講義回)を読み込む
for file in file_paths:
data = pd.read_excel(file)
# 必要な列のみ抽出(メール、名前、出席状況)
if "メール" in data.columns and "名前" in data.columns and "講義に出席しましたか?" in data.columns:
attendance = data[['メール', '名前', '講義に出席しましたか?']].copy()
lecture_name = file.split("/")[-1].replace(".xlsx", "") # ".xlsx" を除去
attendance["講義回"] = lecture_name
attendance["出席"] = attendance["講義に出席しましたか?"].apply(lambda x: "○" if x == "はい" else "×")
attendance_records.append(attendance)
# 全データを統合
attendance_data = pd.concat(attendance_records)
# 学生ごとにデータを整理
students = attendance_data[['メール', '名前']].drop_duplicates()
summary = students.copy()
# 各講義回の出席状況を整理
for file in file_paths:
lecture_name = file.split("/")[-1].replace(".xlsx", "") # ".xlsx" を除去
lecture_data = attendance_data[attendance_data["講義回"] == lecture_name]
summary[lecture_name] = summary["メール"].map(lambda x: "○" if x in lecture_data[lecture_data["出席"] == "○"]["メール"].values else "×")
# 欠席回数を計算(まずデータフレームに追加)
summary["欠席回数"] = (summary.iloc[:, 2:] == "×").sum(axis=1)
# 列を第1回から順にソート
lecture_columns = [col for col in summary.columns if "第" in col]
lecture_columns_sorted = sorted(lecture_columns, key=lambda x: int(x.replace("第", "").replace("回", "")))
# 列の順序を再配置(今度は「欠席回数」も含める)
summary = summary[["メール", "名前"] + lecture_columns_sorted + ["欠席回数"]]
# 結果をExcelに保存
output_path = "./attendance_summary.xlsx"
summary.to_excel(output_path, index=False)
# Excelに背景色を設定
def apply_formatting(file_path):
wb = load_workbook(file_path)
ws = wb.active
yellow_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
red_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
pink_fill = PatternFill(start_color="FFC0CB", end_color="FFC0CB", fill_type="solid")
for row in range(2, ws.max_row + 1): # 2行目からデータが始まる
absence_cell = ws[f"R{row}"] # 欠席回数の列(R列)
try:
absence_count = int(absence_cell.value)
if absence_count > 6:
absence_cell.fill = red_fill
elif absence_count > 3:
absence_cell.fill = yellow_fill
except ValueError:
continue
# C列からQ列の「×」をピンクにする
for col in [chr(i) for i in range(67, 84)]: # C=67, Q=83
cell = ws[f"{col}{row}"]
if cell.value == "×":
cell.fill = pink_fill
wb.save(file_path)
# 背景色を適用
apply_formatting(output_path)
print(f"出席状況の集計が完了しました。結果は {output_path} に保存されています。")
今後もどんどん自動化にチャレンジしてみようと思います。