import pandas as pd
import ast

# 读取Excel文件
input_file = '视频分析报告1.xlsx'
df = pd.read_excel(input_file)

# 第8列（索引7）为"钩子提取"
def extract_fields(hook_col):
    times, queries, hooks = [], [], []
    try:
        # 兼容字符串形式的列表
        items = ast.literal_eval(hook_col) if isinstance(hook_col, str) else []
        for item in items:
            if isinstance(item, dict):
                times.append(item.get('需求钩子出现时间', ''))
                queries.append(item.get('需求详细query', ''))
                hooks.append(item.get('需求钩子话术', ''))
    except Exception:
        pass
    return '\n'.join(times), '\n'.join(queries), '\n'.join(hooks)

# 新增三列
new_times, new_queries, new_hooks = [], [], []
for val in df.iloc[:, 8]:
    t, q, h = extract_fields(val)
    new_times.append(t)
    new_queries.append(q)
    new_hooks.append(h)

df.insert(11, 'time', new_times)
df.insert(12, 'query', new_queries)
df.insert(13, 'hook', new_hooks)

# 保存为新文件
output_file = '视频分析报告1_拆分钩子.xlsx'
df.to_excel(output_file, index=False)
print(f'已保存到 {output_file}')