Google 商品カテゴリごとにタイトル（商品名）のベストな構成をGPT-4oで自動設計した記録

システム要件

入力データ（TSVファイル）

※ 初回実行では「title_attribute_order」と「status」は空欄または存在しなくてもOK。
※ 2回目以降は categories_with_titles.tsv か google_product_category.tsv など、前回処理済のファイルを使う。

カラム例：

import openai
import pandas as pd
from tqdm import tqdm
import time
from concurrent.futures import ThreadPoolExecutor, as_completed

# === ご自身の OpenAI API キーをここに記入 ===
api_key = "YOUR_OPENAI_API_KEY"

client = openai.OpenAI(api_key=api_key)
MODEL = "gpt-4o"

# === 英語出力版プロンプト ===
PROMPT_TEMPLATE = """
Google product category: {category}

For this category, identify the key attributes that customers would compare when choosing a product.
Propose the recommended order of attributes for the product title, arranged by importance.
Follow these best practices:
- Use as many of the 150 characters as possible.
- Place the most important details at the beginning.
- Include keywords such as product name, brand, and specific product details (like “maternity” or “waterproof”) if applicable.
- If some attributes are not relevant for this category, omit them.

IMPORTANT:
Output ONLY the attribute order in English.
Do NOT include any explanations, reasons, or additional text.
Output example:
Brand-Product Type-Material-Size-Mounting Method-Color-Model Number
"""

# === ファイル名（読み込むデータ） ===
input_file = "google_product_category.tsv"  # ★前回実行して生成された最新版TSV（ファイル名を変えるだけ）

# === データ読み込み ===
df = pd.read_csv(input_file, sep="\t", encoding="utf-8")

if "title_attribute_order" not in df.columns:
    df["title_attribute_order"] = ""

# === status列の更新関数 ===
def update_status(row):
    val = str(row["title_attribute_order"]).strip()
    if val == "" or pd.isna(val):
        return "pending"
    if val.startswith("Unhandled error") or val.startswith("Error after"):
        return "pending"
    return "completed"

# === status列（無ければ作成＆更新） ===
df["status"] = df.apply(update_status, axis=1)

# === 実行対象行（pending）抽出 ===
pending_rows = df[df["status"] == "pending"]

print(f"\n【今回の実行対象：{len(pending_rows)}件】\n")

for i, (idx, row) in enumerate(pending_rows.iterrows()):
    if i < 20:
        print(f"{idx}: {row['google_product_category']}")
    elif i == 20:
        print("（以降省略...）")

pending_rows.to_csv("今回実行対象カテゴリ一覧.tsv", sep="\t", index=False, encoding="utf-8-sig")
print("\n→ '今回実行対象カテゴリ一覧.tsv' に出力しました。\n")

# === 差分レポート（開始時点） ===
df[["google_product_category", "title_attribute_order", "status"]].to_csv(
    "差分レポート_今回の状況.tsv", sep="\t", index=False, encoding="utf-8-sig"
)

# === 重複カテゴリチェック ===
completed_categories = df[df["status"] == "completed"]["google_product_category"].unique()
pending_categories = pending_rows["google_product_category"].unique()

overlap = set(completed_categories).intersection(set(pending_categories))

print(f"✅ pending と completed の重複カテゴリ数：{len(overlap)}")
if overlap:
    print("重複カテゴリ（最大10件表示）:")
    for c in list(overlap)[:10]:
        print("-", c)

pd.DataFrame({"duplicate_categories": list(overlap)}).to_csv(
    "重複カテゴリ一覧.tsv", sep="\t", index=False, encoding="utf-8-sig"
)
print("→ 重複カテゴリ一覧.tsv を出力しました。\n")

# === ChatGPT API 呼び出し関数（リトライ付き） ===
def get_title_structure(category, max_retries=3):
    prompt = PROMPT_TEMPLATE.format(category=category)
    retries = 0
    last_exception = None

    while retries < max_retries:
        try:
            response = client.chat.completions.create(
                model=MODEL,
                messages=[
                    {"role": "system", "content": "You are a product title structuring assistant."},
                    {"role": "user", "content": prompt}
                ],
                temperature=0.2
            )
            full_text = response.choices[0].message.content.strip()
            first_line = full_text.split("\n")[0].strip()
            return first_line

        except Exception as e:
            last_exception = e
            retries += 1
            print(f"エラー（{category}）リトライ {retries}/{max_retries}: {str(e)}")
            if "rate limit" in str(e).lower() or "429" in str(e):
                time.sleep(30)
            else:
                time.sleep(5)

    return f"Error after {max_retries} retries: {str(last_exception) if last_exception else 'Unknown error'}"

# === 並列実行 ===
THREADS = 10

with ThreadPoolExecutor(max_workers=THREADS) as executor:
    futures = {
        executor.submit(get_title_structure, row["google_product_category"]): idx
        for idx, row in pending_rows.iterrows()
    }

    for future in tqdm(as_completed(futures), total=len(futures)):
        idx = futures[future]
        try:
            result = future.result()
        except Exception as exc:
            result = f"Unhandled error: {str(exc)}"
        df.at[idx, "title_attribute_order"] = result

# === 処理後 status 再更新 ===
df["status"] = df.apply(update_status, axis=1)

# === 進捗レポート ===
total = len(df)
completed = (df["status"] == "completed").sum()
errors = total - completed
success_rate = completed / total * 100 if total > 0 else 0

print("\n【進捗レポート】")
print(f"全体カテゴリ数：{total}")
print(f"成功件数：{completed}")
print(f"失敗または未処理件数：{errors}")
print(f"成功率：{success_rate:.2f}%")

# === 差分レポート（実行後） ===
df[["google_product_category", "title_attribute_order", "status"]].to_csv(
    "差分レポート_今回の状況（実行後）.tsv", sep="\t", index=False, encoding="utf-8-sig"
)
print("\n→ 差分レポート（実行後）を '差分レポート_今回の状況（実行後）.tsv' に出力しました。")

# === 最終保存（次回実行用） ===
df.to_csv("categories_with_titles.tsv", sep="\t", index=False, encoding="utf-8-sig")
print("\n完了：categories_with_titles.tsv を出力しました。")